1Z0-171: Oracle Database 23Ai Sql Associate

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

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.