1Z0-171: Oracle Database 23ai SQL Associate

Focused View

Phillip Burton

14:56:33

0 View

1 - Introduction

1 - Introduction.mp4

02:24

2 - Welcome to Udemy.html

3 - The Udemy Interface.mp4

02:00

4 - Do you want autotranslated subtitles in more languages.html

5 - Curriculum.mp4

04:19

6 - DateFormats190520.pdf

6 - NumberFormats190514.pdf

6 - Resources.html

2 - Install Oracle Express Edition and SQL Developer

7 - Download and Install Oracle Express Edition.mp4

08:40

8 - Download and Install Oracle SQL Developer and connect to our database.mp4

04:36

9 - Solving connection errors.mp4

04:30

3 - The SELECT statement an overview

10 - 5 The SELECT and FROM clauses.mp4

06:31

11 - 4 Renaming fields using column aliases including using the AS keyword.mp4

06:20

12 - 5 Semicolons and comments.mp4

03:20

13 - 5 9 The WHERE clause.mp4

06:10

14 - 5 6d The GROUP BY clause and the DISTINCT keyword.mp4

06:19

15 - 5 21 The HAVING clause and information about error messages.mp4

04:19

16 - 13a 13c The ORDER BY clause.mp4

05:18

17 - Remembering the order of the clauses.mp4

02:08

18 - Practice Activity Number 1.html

19 - Practice Activity Number 1 The Solution.mp4

03:14

4 - 8b Session 1 Creating tables First pass

20 - Creating a table using the GUI and using DDL.mp4

04:10

21 - 35a Inserting values.mp4

02:51

22 - 35c 44 Deleting the data then the table.mp4

03:56

23 - Practice Activity Number 2.html

24 - Practice Activity Number 2 The Solution.mp4

02:03

5 - Session 1 The DUAL table and Number types

25 - 7 8 Using the DUAL table and rules of precedence for arithmetic expressions.mp4

05:22

26 - Practice Activity Number 3 Writing mathematical queries.html

27 - Practice Activity Number 3 The Solution.mp4

02:15

28 - 40 Creating sample table Run Script v Run Statement.mp4

04:16

29 - 41 The NUMBER data type.mp4

07:53

30 - Do you want to use the comma as a decimal point.mp4

03:24

31 - 8a FLOATing data types.mp4

04:16

32 - Practice Activity Number 4.html

33 - Practice Activity Number 4 The Solution.mp4

03:13

6 - Session 1 Number functions

34 - Numeric functions POWER SQRT EXP LN LOG ABS SIGN and trigonometric.mp4

06:30

35 - 15 Rounding functions including ROUND TRUNC and MOD functions.mp4

07:42

36 - Practice Activity Number 5.html

37 - Practice Activity Number 5 The Solution.mp4

02:45

7 - Session 1 String data types and functions

38 - 6 Using CHAR and VCHAR with concatenation operator literal character strings.mp4

08:17

39 - NCHAR and NVARCHAR Character Data Types.mp4

08:57

40 - 13 String Functions.mp4

08:12

41 - 7b NULL an introduction.mp4

05:57

42 - 7b 17 Useful NULL functions.mp4

06:11

43 - Practice Activity Number 5.html

44 - Practice Activity Number 5 The Solution.mp4

04:31

8 - Session 1 Converting numbers to strings and strings to numbers

45 - 18 Joining a string to a number using implicit conversions.mp4

04:06

46 - 19a 20 Converting a number to a string Part 1.mp4

06:05

47 - 19a Converting a number to a string Part 2.mp4

04:59

48 - 19b Converting a string to a number including the CAST function.mp4

04:51

49 - 5a 14b International conversions.mp4

06:28

50 - Practice Activity Number 6.html

51 - Practice Activity Number 6 The Solution.mp4

02:45

9 - Session 1 Date data types and functions

52 - DATE and TIMESTAMP data types.mp4

07:04

53 - 14 16 Date extraction functions.mp4

08:03

54 - 19a Converting dates to strings.mp4

07:20

55 - 19a Converting times to strings.mp4

04:37

56 - 19c Converting strings to timestamps using the TODATE conversion function.mp4

06:58

57 - Practice Activity Number 7.html

58 - Practice Activity Number 7 The Solution.mp4

03:58

10 - Creating and querying part of a table

59 - 42 Creating the TBLEMPLOYEE table.mp4

09:15

60 - 40 Adding and modifying additional columns.mp4

07:21

61 - 13 35a SELECTing only part of a table strings.mp4

08:54

62 - 9 SELECTing only part of a table numbers.mp4

08:38

63 - 16 SELECTing only part of a table dates.mp4

05:30

11 - Practice Activities

64 - 40 41 Practice Activity Number 8 Creating Tables.html

65 - 40 41 Practice Activity Number 8 The Solution.mp4

07:33

66 - InsertEmployeeDataScript.txt

66 - Populating the Practice Activity tables.html

67 - Populating the Practice Activity tables The Solution.mp4

02:37

68 - Practice Activity Number 9.html

69 - Practice Activity Number 9 The Solution.mp4

04:08

12 - Summarising and ordering data

70 - 5 Summarising and ordering data.mp4

05:54

71 - 5 21 Revising all 6 clauses including adding Criteria on summarised data.mp4

05:51

72 - Practice Activity 10.html

73 - Practice Activity 10 The Solution.mp4

02:41

74 - 12 More about COUNT and using ORDER BY NULLS FIRSTLAST.mp4

05:51

75 - Practice Activity Number 11.html

76 - Practice Activity Number 11 The Solution.mp4

02:57

13 - Adding a second table

77 - Adding a second table.mp4

06:47

78 - 2 Importing data and showing tables graphically.mp4

04:48

78 - InsertTransactionDataScript.txt

79 - Writing a JOIN query.mp4

06:39

80 - 24 26 27 Different types of JOIN.mp4

09:25

81 - 24 26 Using NATURAL JOINs.mp4

03:30

82 - 24 26 Old notation joins.mp4

02:19

83 - Practice Activity Number 12.html

84 - Practice Activity Number 12 The Solution.mp4

07:19

14 - Adding a third table

85 - DISTINCT subqueries and creating a third table.mp4

05:50

86 - 6a 6c JOINing three tables.mp4

06:52

86 - InsertDepartmentDataScript.txt

87 - Practice Activity Number 13.html

88 - Practice Activity Number 13 The Solution.mp4

05:34

15 - Find missing data and delete and update data

89 - 26 Missing data.mp4

07:55

90 - 35c Deleting data.mp4

06:20

91 - 35b Updating data.mp4

06:39

92 - Practice Activity Number 14.html

93 - Practice Activity Number 14 The Solution.mp4

06:47

16 - Database terminology

94 - 1 3 The relationship of a database and SQL.mp4

05:30

95 - 34 What are transactions.mp4

04:53

96 - 34 Implicit and explicit transactions.mp4

04:54

97 - 34 Savepoints and rollback to savepoints.mp4

05:31

98 - Formatting in Oracle SQL Developer.mp4

02:39

17 - 47 Apply how to manage constraints Part 1

99 - 47 Problems with our existing database and introducing constraints.mp4

06:08

100 - 47 Unique constraints what are they.mp4

02:21

101 - 47 Unique constraints in action using a single column.mp4

06:55

102 - 47 Unique constraints in action using multiple columns.mp4

05:51

103 - The Default clause.mp4

07:15

104 - 2 47 Check constraint and how constraints are represented in ERD.mp4

08:28

105 - Practice Activity Number 15.html

106 - Practice Activity Number 15 The Solution.mp4

05:15

18 - 47 Apply how to manage constraints Part 2

107 - Primary key.mp4

04:58

108 - Primary key in practice.mp4

05:03

109 - Adding IDENTITY to our Primary Key.mp4

06:51

110 - Foreign key what is it.mp4

03:20

111 - Foreign key in practice.mp4

09:02

112 - 2 How are constraints shown in ERDs.mp4

03:06

113 - Practice Activity Number 16.html

114 - Practice Activity Number 16 The Solution.mp4

03:08

19 - 48 Explain managing views

115 - Creating views.mp4

05:24

116 - Altering and dropping views.mp4

03:38

117 - Adding new rows to views.mp4

09:19

118 - HidingUnhiding Columns In Views.mp4

03:22

119 - Practice Activity Number 17.html

120 - Practice Activity Number 17 The Solution.mp4

02:44

20 - Combining sets

121 - 31 33c 33d UNION and UNION ALL.mp4

08:06

122 - 32 33a 33b INTERSECT and MINUS.mp4

09:50

123 - 10 11 Substitution variables and DEFINE and VERIFY commands.mp4

08:39

124 - CASE statement.mp4

09:30

125 - NVL NVL2 and Coalesce.mp4

08:25

126 - Practice Activity Number 18.html

127 - Practice Activity Number 18 The Solution.mp4

09:23

21 - The MERGE statement

128 - 20b MERGE statement in theory.mp4

04:56

129 - 20b Lets Build our MERGE statement.mp4

06:38

130 - 20b Lets expand our MERGE statement.mp4

07:00

131 - 20b Merge with additional columns.mp4

06:07

132 - Practice Activity Number 19.html

133 - Practice Activity Number 19 The Solution.mp4

05:34

22 - Group functions

134 - Adding Totals.mp4

06:14

135 - ROLLUP GROUPING and GROUPINGID.mp4

04:19

136 - GROUPING SETS.mp4

05:40

137 - Practice Activity Number 20.html

138 - Practice Activity Number 20 The Solution.mp4

03:17

23 - 16 Subqueries

139 - The WHERE clause.mp4

05:05

140 - WHERE and NOT.mp4

03:50

141 - ANY SOME and ALL.mp4

06:19

142 - The FROM clause.mp4

08:15

143 - The SELECT clause.mp4

09:16

144 - 16d Correlated subquery WHERE EXISTS.mp4

08:33

145 - Practice Activity Number 21.html

146 - Practice Activity Number 21 The Solution.mp4

08:28

24 - 16g Session 5 WITH clause and getting the top rows

147 - Top 5 from various categories.mp4

05:51

148 - WITH statement.mp4

05:37

149 - Generating a list of numbers.mp4

11:19

150 - Grouping numbers.mp4

09:20

151 - Selecting the third row using rownum and OFFSET and FETCH.mp4

08:48

152 - Deleting the second row of results.mp4

05:08

153 - Practice Activity Number 22.html

154 - Practice Activity Number 22 The Solution.mp4

06:17

25 - CTE statement

155 - 4c 6bSelfJoin.mp4

09:00

156 - Recursive CTE.mp4

08:37

26 - Time Zone and Interval data types

157 - Time Zone data types.mp4

06:03

158 - Time Zone functions.mp4

05:44

159 - Interval data types.mp4

05:30

160 - Interval functions.mp4

02:28

27 - Sequences and Data Dictionary

161 - Data Dictionary.mp4

09:52

162 - Defining Sequences.mp4

05:52

163 - Using Sequences.mp4

07:08

164 - Practice Activity Number 23.html

165 - Practice Activity Number 23 The Solution.mp4

04:34

28 - Indexes

166 - Heaps.mp4

04:33

167 - BTree.mp4

08:57

168 - Indexes.mp4

09:21

169 - Practice Activity Number 24.html

170 - Practice Activity Number 24 The Solution.mp4

02:50

29 - Users Privileges and Roles

171 - Users and Schema.mp4

07:45

172 - What are System and Object Privileges.mp4

08:07

173 - Granting system and object privileges to users and roles.mp4

06:21

174 - Namespaces.mp4

05:33

175 - Privileges Data Dictionary.mp4

03:53

30 - Miscellaneous

176 - Dropping columns and making them UNUSED.mp4

04:32

177 - Flashback Tables.mp4

07:32

178 - Create and use External Tables.mp4

06:17

179 - NonEqui Joins.mp4

06:12

180 - MultiTable INSERT statements.mp4

06:01

31 - Congratulations

181 - Taking the exam.mp4

07:24

182 - Well done.mp4

01:11

Description

The SELECT statement, functions and more. Oracle Database 19c, 21c, 23c, 23ai using Oracle SQL Developer. 1Z0-171 exam.

What You'll Learn?

Relational Database Concepts
Retrieving Data using the SQL SELECT Statement and Restricting and Sorting Data
Using Functions
Displaying Data from Multiple Tables, including using SET operators
Using Subqueries to Solve Queries
Managing Indexes Synonyms and Sequences
Manage tables and their relationships using DDL and DML statements
Control User Access
Managing Objects with Data Dictionary Views and Data in Different Time Zones

Who is this for?

This SQL course is meant for you, if you have not used Oracle SQL Database much (or at all), and want to learn SQL.

This course is also for you if you want a refresher on SQL. However, no prior Oracle SQL Database knowledge is required.

What You Need to Know?

You need to know how to use a computer, and hopefully know how to use a spreadsheet.

No prior knowledge of Oracle SQL Database or any other database required - although the more knowledge you do have, the better.

To install Oracle SQL Database on your computer, you will need a 64-bit machine.

Oracle SQL Database cannot easily be installed on the Mac OS. If you wish to install it on a Mac, you will need either to dual boot into Windows or be running Parallel Desktop.

You don't even need Oracle SQL Database installed - I'll show you have to install it on your computer for free!

Why not have a look at the curriculum below and see what you can learn?

More details

Description
This course is the foundation for the Oracle Database SQLÂ 1Z0-171 certification. It covers the SELECTÂ statement in detail, with additional requirements for controlling user access.

We'll install for free Oracle Express Edition and Oracle SQLÂ Developer. Then we'll take a look at the 6 principal clauses of the SELECTÂ statement:Â SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
Then we'll start creating tables, but find that we can't get very far without understanding data types. We'll then look at string, date and number types and functions, together with looking at functions converting between them.
We'll revisit the SELECT statement and go into more detail. We'll create tables and JOIN them together. Once you have finished this session, you should be secure in your knowledge of the 6 principal clauses of the SELECT statement, the most important part of Oracle SQL.
We'll then looking for missing data, and find out how to delete and update data. We'll look at the difference between implicit and explicit transactions, and various constraints, including primary and foreign keys.
We'll start off by saving our queries in views. Then we'll combine tables, not by adding additional columns using JOINS, but adding additional rows such Oracle SQLÂ set operators such as UNION, INTERSECT and MERGE.
We'll add totals to our SELECT queries. We'll then look at subqueries, and how you can add them into the FROM, SELECT and WHEREÂ clauses, and also into the WITH clause. We'll also look at Oracle SQLÂ self-joins, which are useful when you have hierarchies.
Finally, TIMEÂ ZONE and INTERVAL data types and functions. We'll then have a look at SEQUENCEs and INDEXes, together with how these are shown in the Data Dictionary. We'll then create new users, and assign privileges and roles to them, and we'll finish with the last few requirements for the exam.
No prior knowledge is required - we'll even install Oracle Database and Oracle SQL Developer on your computer for free!
Once finished, you will know what how to manipulate numbers, strings and dates, and create database and tables, create tables, insert data and create analyses, and have an appreciation of how they can all be used in Oracle SQL.
Who this course is for:
This SQL course is meant for you, if you have not used Oracle SQL Database much (or at all), and want to learn SQL.
This course is also for you if you want a refresher on SQL. However, no prior Oracle SQL Database knowledge is required.

User Reviews

Rating

average 0

Total votes0

Focused display

Oracle SQL

Phillip Burton

Instructor's Courses

Hi. I'm Phillip, and I'm proud to Teach with Udemy as a Udemy Instructor Partner. I have been teaching on Udemy since 2015, and I have over 500,000 students and more than 40 video courses, including over a dozen Bestseller courses.My courses include Power BI, Tableau, SQL Server, Power Platform (Power Apps, Power Automate, etc.), Azure, Microsoft Office, and more. Many of my courses follow requirements for official Microsoft certifications - so with a bit of practice, you could enter the exam and put it on your CV or resume.I have been a Computing Consultant providing expert services in the development of computer systems and data analysis. I am one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards. My interests are working with data, including Microsoft Power BI, Excel, Access, SQL Server, and the Power Platform.

Udemy

View courses Udemy

Students take courses primarily to improve job-related skills.Some courses generate credit toward technical certification. Udemy has made a special effort to attract corporate trainers seeking to create coursework for employees of their company.