1Z0–171 – Oracle Database 23Ai Sql Associate

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
Requirements
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?
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.
Overview
Section 1: Introduction
Lecture 1 Introduction
Lecture 2 Welcome to Udemy
Lecture 3 The Udemy Interface
Lecture 4 Do you want auto-translated subtitles in more languages?
Lecture 5 Curriculum
Lecture 6 Resources
Section 2: Install Oracle Express Edition and SQL Developer
Lecture 7 Download and Install Oracle Express Edition
Lecture 8 Download and Install Oracle SQL Developer, and connect to our database
Lecture 9 Solving connection errors
Section 3: The SELECT statement - an overview
Lecture 10 5. The SELECT and FROM clauses
Lecture 11 4. Renaming fields (using column aliases), including using the AS keyword
Lecture 12 5. Semicolons and comments
Lecture 13 5, 9. The WHERE clause
Lecture 14 5, 6d. The GROUP BY clause and the DISTINCT keyword
Lecture 15 5, 21. The HAVING clause, and information about error messages.
Lecture 16 13a, 13c. The ORDER BY clause
Lecture 17 Remembering the order of the clauses
Lecture 18 Practice Activity Number 1
Lecture 19 Practice Activity Number 1 - The Solution
Section 4: 8b. Session 1 - Creating tables - First pass
Lecture 20 Creating a table using the GUI and using DDL
Lecture 21 35a. Inserting values
Lecture 22 35c, 44. Deleting the data, then the table
Lecture 23 Practice Activity Number 2
Lecture 24 Practice Activity Number 2 - The Solution
Section 5: Session 1 - The DUAL table and Number types
Lecture 25 7, 8. Using the DUAL table, and rules of precedence for arithmetic expressions
Lecture 26 Practice Activity Number 3 - Writing mathematical queries
Lecture 27 Practice Activity Number 3 - The Solution
Lecture 28 40. Creating sample table; Run Script v Run Statement
Lecture 29 41. The NUMBER data type
Lecture 30 Do you want to use the comma as a decimal point?
Lecture 31 8a. FLOATing data types
Lecture 32 Practice Activity Number 4
Lecture 33 Practice Activity Number 4 - The Solution
Section 6: Session 1 - Number functions
Lecture 34 Numeric functions: POWER, SQRT, EXP, LN, LOG, ABS, SIGN and trigonometric
Lecture 35 15. Rounding functions, including ROUND, TRUNC and MOD functions
Lecture 36 Practice Activity Number 5
Lecture 37 Practice Activity Number 5 - The Solution
Section 7: Session 1 - String data types and functions
Lecture 38 6. Using CHAR and VCHAR with concatenation operator, literal character strings
Lecture 39 NCHAR and NVARCHAR Character Data Types
Lecture 40 13. String Functions
Lecture 41 7b. NULL - an introduction
Lecture 42 7b, 17. Useful NULL functions
Lecture 43 Practice Activity Number 5
Lecture 44 Practice Activity Number 5 - The Solution
Section 8: Session 1 - Converting numbers to strings, and strings to numbers
Lecture 45 18. Joining a string to a number using implicit conversions
Lecture 46 19a, 20. Converting a number to a string - Part 1
Lecture 47 19a. Converting a number to a string - Part 2
Lecture 48 19b. Converting a string to a number, including the CAST function
Lecture 49 5a, 14b. International conversions
Lecture 50 Practice Activity Number 6
Lecture 51 Practice Activity Number 6 - The Solution
Section 9: Session 1 - Date data types and functions
Lecture 52 DATE and TIMESTAMP() data types
Lecture 53 14, 16. Date extraction functions
Lecture 54 19a. Converting dates to strings
Lecture 55 19a. Converting times to strings
Lecture 56 19c. Converting strings to timestamps using the TO_DATE conversion function
Lecture 57 Practice Activity Number 7
Lecture 58 Practice Activity Number 7 - The Solution
Section 10: Creating and querying part of a table
Lecture 59 42. Creating the TBL_EMPLOYEE table
Lecture 60 40. Adding and modifying additional columns
Lecture 61 13, 35a. SELECTing only part of a table - strings
Lecture 62 9. SELECTing only part of a table - numbers
Lecture 63 16. SELECTing only part of a table - dates
Section 11: Practice Activities
Lecture 64 40, 41. Practice Activity Number 8 - Creating Tables
Lecture 65 40, 41. Practice Activity Number 8 - The Solution
Lecture 66 Populating the Practice Activity tables
Lecture 67 Populating the Practice Activity tables - The Solution
Lecture 68 Practice Activity Number 9
Lecture 69 Practice Activity Number 9 - The Solution
Section 12: Summarising and ordering data
Lecture 70 5. Summarising and ordering data
Lecture 71 5, 21. Revising all 6 clauses, including adding Criteria on summarised data
Lecture 72 Practice Activity 10
Lecture 73 Practice Activity 10 - The Solution
Lecture 74 12. More about COUNT, and using ORDER BY NULLS FIRST/LAST
Lecture 75 Practice Activity Number 11
Lecture 76 Practice Activity Number 11 - The Solution
Section 13: Adding a second table
Lecture 77 Adding a second table
Lecture 78 2. Importing data and showing tables graphically
Lecture 79 Writing a JOIN query
Lecture 80 24, 26, 27. Different types of JOIN
Lecture 81 24, 26. Using NATURAL JOINs
Lecture 82 24, 26. Old notation joins
Lecture 83 Practice Activity Number 12
Lecture 84 Practice Activity Number 12 - The Solution
Section 14: Adding a third table
Lecture 85 DISTINCT, subqueries and creating a third table
Lecture 86 6a, 6c. JOINing three tables
Lecture 87 Practice Activity Number 13
Lecture 88 Practice Activity Number 13 - The Solution
Section 15: Find missing data, and delete and update data
Lecture 89 26. Missing data
Lecture 90 35c. Deleting data
Lecture 91 35b. Updating data
Lecture 92 Practice Activity Number 14
Lecture 93 Practice Activity Number 14 - The Solution
Section 16: Database terminology
Lecture 94 1, 3. The relationship of a database and SQL
Lecture 95 34. What are transactions?
Lecture 96 34. Implicit and explicit transactions
Lecture 97 34. Savepoints and rollback to savepoints
Lecture 98 Formatting in Oracle SQL Developer
Section 17: 47. Apply how to manage constraints - Part 1
Lecture 99 47. Problems with our existing database, and introducing constraints
Lecture 100 47. Unique constraints - what are they?
Lecture 101 47. Unique constraints in action using a single column
Lecture 102 47. Unique constraints in action using multiple columns
Lecture 103 The Default clause
Lecture 104 2, 47. Check constraint, and how constraints are represented in ERD
Lecture 105 Practice Activity Number 15
Lecture 106 Practice Activity Number 15 - The Solution
Section 18: 47. Apply how to manage constraints - Part 2
Lecture 107 Primary key
Lecture 108 Primary key - in practice
Lecture 109 Adding IDENTITY to our Primary Key
Lecture 110 Foreign key - what is it?
Lecture 111 Foreign key - in practice
Lecture 112 2. How are constraints shown in ERDs?
Lecture 113 Practice Activity Number 16
Lecture 114 Practice Activity Number 16 - The Solution
Section 19: 48. Explain managing views
Lecture 115 Creating views
Lecture 116 Altering and dropping views
Lecture 117 Adding new rows to views
Lecture 118 Hiding/Unhiding Columns In Views
Lecture 119 Practice Activity Number 17
Lecture 120 Practice Activity Number 17 - The Solution
Section 20: Combining sets
Lecture 121 31, 33c, 33d. UNION and UNION ALL
Lecture 122 32, 33a, 33b. INTERSECT and MINUS
Lecture 123 10, 11. Substitution variables and DEFINE and VERIFY commands
Lecture 124 CASE statement
Lecture 125 NVL, NVL2 and Coalesce
Lecture 126 Practice Activity Number 18
Lecture 127 Practice Activity Number 18 - The Solution
Section 21: The MERGE statement
Lecture 128 20b. MERGE statement - in theory
Lecture 129 20b. Let's Build our MERGE statement
Lecture 130 20b. Let's expand our MERGE statement
Lecture 131 20b. Merge with additional columns
Lecture 132 Practice Activity Number 19
Lecture 133 Practice Activity Number 19 - The Solution
Section 22: Group functions
Lecture 134 Adding Totals
Lecture 135 ROLLUP, GROUPING and GROUPING_ID
Lecture 136 GROUPING SETS
Lecture 137 Practice Activity Number 20
Lecture 138 Practice Activity Number 20 - The Solution
Section 23: 16. Sub-queries
Lecture 139 The WHERE clause
Lecture 140 WHERE and NOT
Lecture 141 ANY, SOME and ALL
Lecture 142 The FROM clause
Lecture 143 The SELECT clause
Lecture 144 16d. Correlated subquery - WHERE EXISTS
Lecture 145 Practice Activity Number 21
Lecture 146 Practice Activity Number 21 - The Solution
Section 24: 16g. Session 5 - WITH clause, and getting the top rows
Lecture 147 Top 5 from various categories
Lecture 148 WITH statement
Lecture 149 Generating a list of numbers
Lecture 150 Grouping numbers
Lecture 151 Selecting the third row using rownum and OFFSET and FETCH
Lecture 152 Deleting the second row of results
Lecture 153 Practice Activity Number 22
Lecture 154 Practice Activity Number 22 - The Solution
Section 25: CTE statement
Lecture 155 4c, 6b.Self-Join
Lecture 156 Recursive CTE
Section 26: Time Zone and Interval data types
Lecture 157 Time Zone data types
Lecture 158 Time Zone functions
Lecture 159 Interval data types
Lecture 160 Interval functions
Section 27: Sequences and Data Dictionary
Lecture 161 Data Dictionary
Lecture 162 Defining Sequences
Lecture 163 Using Sequences
Lecture 164 Practice Activity Number 23
Lecture 165 Practice Activity Number 23 - The Solution
Section 28: Indexes
Lecture 166 Heaps
Lecture 167 B-Tree
Lecture 168 Indexes
Lecture 169 Practice Activity Number 24
Lecture 170 Practice Activity Number 24 - The Solution
Section 29: Users, Privileges and Roles
Lecture 171 Users and Schema
Lecture 172 What are System and Object Privileges
Lecture 173 Granting system and object privileges to users and roles
Lecture 174 Namespaces
Lecture 175 Privileges Data Dictionary
Section 30: Miscellaneous
Lecture 176 Dropping columns and making them UNUSED
Lecture 177 Flashback Tables
Lecture 178 Create and use External Tables
Lecture 179 Non-Equi Joins
Lecture 180 Multi-Table INSERT statements
Section 31: Congratulations
Lecture 181 Taking the exam
Lecture 182 Well done!
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.

Homepage

https://www.udemy.com/course/1z0-171-oracle-database-23ai-sql-associate/