1Z0-071 Oracle Sql Developer: Certified Associate (Database)

The SELECT statement, functions and more. Oracle Database 11g, 12c, 19c, 21c using Oracle SQL Developer. 1Z0-071 exam.

What you'll learn

Create tables in a database and ALTER columns in the table.

Know what data type to use in various situations, and use functions to manipulate date, number and string data values.

Retrieve data using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.

JOIN two or more tables together, finding missing data.

INSERT new data, UPDATE and DELETE existing data, and export data into a new table.

Create constraints, views and sequences, subqueries and CTEs

Use UNION, CASE, MERGE and error checking

Apply ranking and analytic functions, grouping

Learn about transactions, indexes, users, privileges, roles and more

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!

There is a 30-day money back guarantee of this Udemy course.

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-071 certification. It covers the SELECT statement in detail, with additional requirements for controlling user access. It is divided into 6 sessions, each of which should take a morning or afternoon to complete.What do people like you say about this course?Prashant says: "Course has been designed in way that a person with no knowledge of Oracle can understand it. Good learning and thanks a lot for making such nice course."Shubho says: "Awesome course. The instructor explains the concepts very thorougly and in a easy-to-grasp way. Also, the practice exercises are super helpful. If you want to master Oracle SQL, this is the course for you."Henry says: "I want to thank Phillip for this formidable course. This course along with other materials helped me to pass the 1Z0-071 Exam last Saturday."Session 1We'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.Session 2We'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.Session 3In session 3, we'll 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.Session 4We'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. Then we'll look at analytical functions using the OVER clause.Session 5Session 5 starts off by adding 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. Finally, we'll look at Oracle SQL self-joins, which are useful when you have hierarchies.Session 6Session 6 starts off with two additional data types, 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 Resources

Lecture 6 Curriculum

Section 2: Install Oracle Express Edition and SQL Developer

Lecture 7 Do you have Windows Home?

Lecture 8 Download and Install Oracle Express Edition

Lecture 9 Download and Install Oracle SQL Developer

Section 3: 3a. The SELECT statement - an overview

Lecture 10 Connect to Database

Lecture 11 Solving "The network adaptor cannot establish the connection" error

Lecture 12 8e. The SELECT and FROM clauses

Lecture 13 Renaming fields

Lecture 14 Using the AS keyword

Lecture 15 Semicolons and comments

Lecture 16 3b, 13b. The WHERE clause

Lecture 17 15b. The GROUP BY clause

Lecture 18 15c. The HAVING clause, and information about error messages.

Lecture 19 13a, 13c. The ORDER BY clause

Lecture 20 Remembering the order of the clauses

Lecture 21 Practice Activity Number 1

Lecture 22 Practice Activity Number 1 - The Solution

Section 4: 8b. Session 1 - Creating tables - First pass

Lecture 23 Creating a table using the GUI

Lecture 24 Creating our first table using SQL

Lecture 25 17a. Inserting values

Lecture 26 Deleting the data, then the table

Lecture 27 Trying to create an Employee table

Section 5: Session 1 - The DUAL table and Number types

Lecture 28 Using the DUAL table

Lecture 29 Practice Activity Number 2 - Writing mathematical queries

Lecture 30 Practice Activity Number 2 - The Solution

Lecture 31 8c. Creating sample table; Run Script v Run Statement

Lecture 32 8a. The NUMBER data type

Lecture 33 Do you want to use the comma as a decimal point?

Lecture 34 8a. FLOATing data types

Lecture 35 Practice Activity Number 3

Lecture 36 Practice Activity Number 3 - The Solution

Section 6: Session 1 - Number functions

Lecture 37 5b, 14a. Numeric functions Part 1

Lecture 38 Rounding functions

Lecture 39 Practice Activity Number 4

Lecture 40 Practice Activity Number 4 - The Solution

Section 7: Session 1 - String data types and functions

Lecture 41 Character Encoding Systems

Lecture 42 8a. Character Data Types

Lecture 43 5b, 14a. String Functions

Lecture 44 NULL - an introduction

Lecture 45 Useful NULL functions

Lecture 46 Adding quotation marks and Alternative Quoting Mechanism in string literals

Lecture 47 Practice Activity Number 5

Lecture 48 Practice Activity Number 5 - The Solution

Section 8: Session 1 - Converting numbers to strings, and strings to numbers

Lecture 49 5b, 14a. Joining a string to a number using implicit conversions

Lecture 50 5a, 14b. Converting a number to a string

Lecture 51 5a, 14b. Converting a string to a number, including the CAST function

Lecture 52 5a, 14b. International conversions

Lecture 53 Practice Activity Number 6

Lecture 54 Practice Activity Number 6 - The Solution

Section 9: Session 1 - Date data types and functions

Lecture 55 8a. DATE and TIMESTAMP() data types

Lecture 56 5b, 14a. Date extraction functions

Lecture 57 5a, 14b. Converting dates to strings

Lecture 58 5a, 14b. Converting times to strings

Lecture 59 5a, 14b. Converting strings to timestamps, and international considerations

Lecture 60 Practice Activity Number 7

Lecture 61 Practice Activity Number 7 - The Solution

Section 10: End of Session 1, Start of Session 2

Lecture 62 End of Session 1

Lecture 63 Welcome to Session 2

Section 11: Session 2 - Creating and querying part of a table

Lecture 64 Creation of tblEmployee table

Lecture 65 Adding and modifying additional columns

Lecture 66 3b, 13b. SELECTing only part of a table - strings

Lecture 67 3b, 13b. SELECTing only part of a table - numbers

Lecture 68 3b, 13b. SELECTing only part of a table - dates

Section 12: Session 2 - Practice Activities

Lecture 69 Practice Activity Number 8 - Creating Tables

Lecture 70 Practice Activity Number 8 - The Solution

Lecture 71 Populating the Practice Activity tables

Lecture 72 Populating the Practice Activity tables - The Solution

Lecture 73 Practice Activity Number 9

Section 13: Session 2 - Summarising and ordering data

Lecture 74 13c, 15b. Summarising and ordering data

Lecture 75 15c. Criteria on summarised data

Lecture 76 Exercise - Part 1

Lecture 77 13a, 13c. Exercise - Part 2, and ORDER BY NULLS FIRST/LAST

Lecture 78 Practice Activity Number 10

Lecture 79 Practice Activity Number 10 - The Solution

Section 14: Session 2 - Adding a second table

Lecture 80 Adding a second table

Lecture 81 Designing a connection

Lecture 82 12a. Importing data and showing tables graphically

Lecture 83 Writing a JOIN query

Lecture 84 4a. Different types of JOIN

Lecture 85 4b. Using NATURAL JOINs

Lecture 86 4b. Old notation joins

Lecture 87 Practice Activity Number 11

Lecture 88 Practice Activity Number 11 - The Solution

Section 15: Session 2 - Adding a third table

Lecture 89 Creating a third table

Lecture 90 6a, 6c. JOINing three tables

Lecture 91 Practice Activity Number 12

Lecture 92 Practice Activity Number 12 - The Solution

Section 16: End of Session 2, Start of Session 3

Lecture 93 End of Session 2

Lecture 94 Welcome to Session 3

Section 17: Session 3 - Find missing data, and delete and update data

Lecture 95 2b. Missing data

Lecture 96 2b. Deleting data

Lecture 97 2b. Updating data

Lecture 98 Practice Activity Number 13

Lecture 99 Practice Activity Number 13 - The Solution

Section 18: Session 3 - Database terminology

Lecture 100 1a, 11c. The relationship of a database and SQL

Lecture 101 2a, 2b, 11a, 11b. DML, DDL, DCL and TCL

Lecture 102 17d. What are transactions?

Lecture 103 17d. Implicit transactions

Lecture 104 2c, Explicit Transactions - Start and end transactions

Lecture 105 2c, Savepoints and roolback to savepoints

Lecture 106 Formatting in Oracle SQL Developer

Section 19: 8c, 10a. Session 3 - Data integrity, including Create and modify constraints

Lecture 107 Problems with our existing database

Lecture 108 What are constraints?

Lecture 109 Unique constraints - what are they?

Lecture 110 Unique constraints in action

Lecture 111 Default constraints - what are they?

Lecture 112 Default constraints in action

Lecture 113 Check constraint - what are they?

Lecture 114 Check constraints - in practice

Lecture 115 Primary key

Lecture 116 Primary key - in practice

Lecture 117 Foreign key - what is it?

Lecture 118 Foreign key - in practice

Lecture 119 12a. How are constraints shown in ERDs?

Lecture 120 Practice Activity Number 14

Lecture 121 Practice Activity Number 14 - The Solution

Section 20: End of Session 3, Start of Session 4

Lecture 122 Well done for getting half way through the course

Lecture 123 Welcome to Session 4

Section 21: 18a. Session 4 - Views

Lecture 124 Creating views

Lecture 125 Altering and dropping views

Lecture 126 Adding new rows to views

Lecture 127 Hiding/Unhiding Columns In Views

Lecture 128 Practice Activity Number 15

Lecture 129 Practice Activity Number 15 - The Solution

Section 22: Session 4 - Combining sets

Lecture 130 7a. UNION and UNION ALL

Lecture 131 7a. INTERSECT and MINUS

Lecture 132 13d. Use ampersand substitution to restrict and sort output as runtime

Lecture 133 CASE statement

Lecture 134 NVL, NVL2 and Coalesce

Lecture 135 Practice Activity Number 16

Lecture 136 Practice Activity Number 16 - The Solution

Section 23: Session 4 - the MERGE statement

Lecture 137 20b. MERGE statement - in theory

Lecture 138 20b. Let's Build our MERGE statement

Lecture 139 20b. Let's expand our MERGE statement

Lecture 140 20b. Merge with additional columns

Lecture 141 Practice Activity Number 17

Lecture 142 Practice Activity Number 17 - The Solution

Section 24: Session 4 - The OVER Clause

Lecture 143 Introduction

Lecture 144 OVER()

Lecture 145 PARTITION BY and ORDER BY

Lecture 146 RANGE

Lecture 147 CURRENT ROW and UNBOUNDED

Lecture 148 RANGE versus ROWS

Lecture 149 Omitting RANGE/ROW?

Lecture 150 Practice Activity Number 18

Lecture 151 Practice Activity Number 18 - The Solution

Section 25: 14c. Session 4 - Analytical Functions

Lecture 152 ROW_NUMBER, RANK and DENSE_RANK

Lecture 153 NTILE

Lecture 154 FIRST_VALUE and LAST_VALUE

Lecture 155 LAG and LEAD

Lecture 156 CUME_DIST and PERCENT_RANK

Lecture 157 PERCENTILE_CONT and PERCENTILE_DISC

Lecture 158 Other Aggregation functions

Lecture 159 Practice Activity Number 19

Lecture 160 Practice Activity Number 19 - The Solution

Section 26: End of Session 4, Start of Session 5

Lecture 161 You are two-thirds of the way through the course

Lecture 162 Start of Session 5

Section 27: 15a. Session 5 - Group functions

Lecture 163 Adding Totals

Lecture 164 ROLLUP, GROUPING and GROUPING_ID

Lecture 165 GROUPING SETS

Lecture 166 Practice Activity Number 20

Lecture 167 Practice Activity Number 20 - The Solution

Section 28: 16. Session 5 - Sub-queries

Lecture 168 The WHERE clause

Lecture 169 WHERE and NOT

Lecture 170 ANY, SOME and ALL

Lecture 171 The FROM clause

Lecture 172 The SELECT clause

Lecture 173 16d. Correlated subquery - WHERE EXISTS

Lecture 174 Practice Activity Number 21

Lecture 175 Practice Activity Number 21 - The Solution

Section 29: 16g. Session 5 - WITH clause, and getting the top rows

Lecture 176 Top 5 from various categories

Lecture 177 WITH statement

Lecture 178 Generating a list of numbers

Lecture 179 Grouping numbers

Lecture 180 Selecting the third row using rownum and OFFSET and FETCH

Lecture 181 Deleting the second row of results

Lecture 182 Practice Activity Number 22

Lecture 183 Practice Activity Number 22 - The Solution

Lecture 184 Practice Activity Number 23

Lecture 185 Practice Activity Number 23 - The Solution

Section 30: Session 5: CTE statement

Lecture 186 4c, 6b.Self-Join

Lecture 187 Recursive CTE

Section 31: End of Session 5, Start of Session 6

Lecture 188 Almost there - you can do it!

Lecture 189 Welcome to Session 6

Section 32: Session 6 - Time Zone and Interval data types

Lecture 190 Time Zone data types

Lecture 191 Time Zone functions

Lecture 192 Interval data types

Lecture 193 Interval functions

Section 33: Session 6: Sequences and Data Dictionary

Lecture 194 Data Dictionary

Lecture 195 Defining Sequences

Lecture 196 Using Sequences

Lecture 197 Practice Activity Number 24

Lecture 198 Practice Activity Number 24 - The Solution

Section 34: Session 6: Indexes

Lecture 199 Heaps

Lecture 200 B-Tree

Lecture 201 Indexes

Lecture 202 Practice Activity Number 25

Lecture 203 Practice Activity Number 25 - The Solution

Section 35: Session 6: Users, Privileges and Roles

Lecture 204 Users and Schema

Lecture 205 What are System and Object Privileges

Lecture 206 Granting system and object privileges to users and roles

Lecture 207 Namespaces

Lecture 208 Privileges Data Dictionary

Section 36: Session 6: Miscellaneous

Lecture 209 Dropping columns and making them UNUSED

Lecture 210 Flashback Tables

Lecture 211 Create and use External Tables

Lecture 212 Non-Equi Joins

Lecture 213 Multi-Table INSERT statements

Section 37: Session 6: Congratulations

Lecture 214 Taking the exam

Lecture 215 Well done!

Lecture 216 Bonus Lecture

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.