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.

4.68 (1062 reviews)
Udemy
platform
English
language
IT Certification
category
instructor
6,983
students
19.5 hours
content
Jul 2022
last update
$99.99
regular price

What you will 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

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 1

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.

Session 2

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.

Session 3

In 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 4

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. Then we'll look at analytical functions using the OVER clause.

Session 5

Session 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 6

Session 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.

Screenshots

1Z0-071 Oracle SQL Developer: Certified Associate (Database) - Screenshot_011Z0-071 Oracle SQL Developer: Certified Associate (Database) - Screenshot_021Z0-071 Oracle SQL Developer: Certified Associate (Database) - Screenshot_031Z0-071 Oracle SQL Developer: Certified Associate (Database) - Screenshot_04

Content

Introduction

Introduction
Welcome to Udemy
Curriculum

Install Oracle Express Edition and SQL Developer

Resources
Download Oracle Express Edition
Install Oracle Express Edition
Download and Install Oracle SQL Developer

3a. The SELECT statement - an overview

Connect to Database
Solving "The network adaptor cannot establish the connection" error
8e. The SELECT and FROM clauses
Renaming fields
Using the AS keyword
Semicolons and comments
3b, 13b. The WHERE clause
15b. The GROUP BY clause
15c. The HAVING clause, and information about error messages.
13a, 13c. The ORDER BY clause
Remembering the order of the clauses
Practice Activity Number 1
Practice Activity Number 1 - The Solution

8b. Session 1 - Creating tables - First pass

Creating a table using the GUI
Creating our first table using SQL
17a. Inserting values
Deleting the data, then the table
Trying to create an Employee table

Session 1 - The DUAL table and Number types

Using the DUAL table
Practice Activity Number 2 - Writing mathematical queries
Practice Activity Number 2 - The Solution
8c. Creating sample table; Run Script v Run Statement
8a. The NUMBER data type
Do you want to use the comma as a decimal point?
8a. FLOATing data types
Practice Activity Number 3
Practice Activity Number 3 - The Solution

Session 1 - Number functions

5b, 14a. Numeric functions Part 1
Rounding functions
Practice Activity Number 4
Practice Activity Number 4 - The Solution

Session 1 - String data types and functions

Character Encoding Systems
8a. Character Data Types
5b, 14a. String Functions
NULL - an introduction
Useful NULL functions
Adding quotation marks and Alternative Quoting Mechanism in string literals
Practice Activity Number 5
Practice Activity Number 5 - The Solution

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

5b, 14a. Joining a string to a number using implicit conversions
5a, 14b. Converting a number to a string
5a, 14b. Converting a string to a number, including the CAST function
5a, 14b. International conversions
Practice Activity Number 6
Practice Activity Number 6 - The Solution

Session 1 - Date data types and functions

8a. DATE and TIMESTAMP() data types
5b, 14a. Date extraction functions
5a, 14b. Converting dates to strings
5a, 14b. Converting times to strings
5a, 14b. Converting strings to timestamps, and international considerations
Practice Activity Number 7
Practice Activity Number 7 - The Solution

End of Session 1, Start of Session 2

End of Session 1
Welcome to Session 2

Session 2 - Creating and querying part of a table

Creation of tblEmployee table
Adding and modifying additional columns
3b, 13b. SELECTing only part of a table - strings
3b, 13b. SELECTing only part of a table - numbers
3b, 13b. SELECTing only part of a table - dates

Session 2 - Practice Activities

Practice Activity Number 8 - Creating Tables
Practice Activity Number 8 - The Solution
Populating the Practice Activity tables
Practice Activity Number 9
Practice Activity Number 9 - The Solution

Session 2 - Summarising and ordering data

13c, 15b. Summarising and ordering data
15c. Criteria on summarised data
Exercise - Part 1
13a, 13c. Exercise - Part 2, and ORDER BY NULLS FIRST/LAST
Practice Activity Number 10
Practice Activity Number 10 - The Solution

Session 2 - Adding a second table

Adding a second table
Designing a connection
12a. Importing data and showing tables graphically
Writing a JOIN query
4a. Different types of JOIN
4b. Using NATURAL JOINs
4b. Old notation joins
Practice Activity Number 11
Practice Activity Number 11 - The Solution

Session 2 - Adding a third table

Creating a third table
6a, 6c. JOINing three tables
Practice Activity Number 12
Practice Activity Number 12 - The Solution

End of Session 2, Start of Session 3

End of Session 2
Welcome to Session 3

Session 3 - Find missing data, and delete and update data

2b. Missing data
2b. Deleting data
2b. Updating data
Practice Activity Number 13
Practice Activity Number 13 - The Solution

Session 3 - Database terminology

1a, 11c. The relationship of a database and SQL
2a, 2b, 11a, 11b. DML, DDL, DCL and TCL
17d. What are transactions?
17d. Implicit transactions
2c, Explicit Transactions - Start and end transactions
2c, Savepoints and roolback to savepoints
Formatting in Oracle SQL Developer

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

Problems with our existing database
What are constraints?
Unique constraints - what are they?
Unique constraints in action
Default constraints - what are they?
Default constraints in action
Check constraint - what are they?
Check constraints - in practice
Primary key
Primary key - in practice
Foreign key - what is it?
Foreign key - in practice
12a. How are constraints shown in ERDs?
Practice Activity Number 14
Practice Activity Number 14 - The Solution

End of Session 3, Start of Session 4

Well done for getting half way through the course
Welcome to Session 4

18a. Session 4 - Views

Creating views
Altering and dropping views
Adding new rows to views
Hiding/Unhiding Columns In Views
Practice Activity Number 15
Practice Activity Number 15 - The Solution

Session 4 - Combining sets

7a. UNION and UNION ALL
7a. INTERSECT and MINUS
13d. Use ampersand substitution to restrict and sort output as runtime
CASE statement
NVL, NVL2 and Coalesce
Practice Activity Number 16
Practice Activity Number 16 - The Solution

Session 4 - the MERGE statement

20b. MERGE statement - in theory
20b. Let's Build our MERGE statement
20b. Let's expand our MERGE statement
20b. Merge with additional columns
Practice Activity Number 17
Practice Activity Number 17 - The Solution

Session 4 - The OVER Clause

Introduction
OVER()
PARTITION BY and ORDER BY
RANGE
CURRENT ROW and UNBOUNDED
RANGE versus ROWS
Omitting RANGE/ROW?
Practice Activity Number 18
Practice Activity Number 18 - The Solution

14c. Session 4 - Analytical Functions

ROW_NUMBER, RANK and DENSE_RANK
NTILE
FIRST_VALUE and LAST_VALUE
LAG and LEAD
CUME_DIST and PERCENT_RANK
PERCENTILE_CONT and PERCENTILE_DISC
Other Aggregation functions
Practice Activity Number 19
Practice Activity Number 19 - The Solution

End of Session 4, Start of Session 5

You are two-thirds of the way through the course
Start of Session 5

15a. Session 5 - Group functions

Adding Totals
ROLLUP, GROUPING and GROUPING_ID
GROUPING SETS
Practice Activity Number 20
Practice Activity Number 20 - The Solution

16. Session 5 - Sub-queries

The WHERE clause
WHERE and NOT
ANY, SOME and ALL
The FROM clause
The SELECT clause
16d. Correlated subquery - WHERE EXISTS
Practice Activity Number 21
Practice Activity Number 21 - The Solution

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

Top 5 from various categories
WITH statement
Generating a list of numbers
Grouping numbers
Selecting the third row using rownum and OFFSET and FETCH
Deleting the second row of results
Practice Activity Number 22
Practice Activity Number 22 - The Solution
Practice Activity Number 23
Practice Activity Number 23 - The Solution

Session 5: CTE statement

4c, 6b.Self-Join
Recursive CTE

End of Session 5, Start of Session 6

Almost there - you can do it!
Welcome to Session 6

Session 6 - Time Zone and Interval data types

Time Zone data types
Time Zone functions
Interval functions
Interval data types

Session 6: Sequences and Data Dictionary

Data Dictionary
Defining Sequences
Using Sequences
Practice Activity Number 24
Practice Activity Number 24 - The Solution

Session 6: Indexes

Heaps
B-Tree
Indexes
Practice Activity Number 25
Practice Activity Number 25 - The Solution

Session 6: Users, Privileges and Roles

Users and Schema
What are System and Object Privileges
Granting system and object privileges to users and roles
Namespaces
Privileges Data Dictionary

Session 6: Miscellaneous

Dropping columns and making them UNUSED
Flashback Tables
Create and use External Tables
Non-Equi Joins
Multi-Table INSERT statements

Session 6: Congratulations

Taking the exam
Well done!

Reviews

Gloria
February 17, 2022
This course is simply speechless! It covers a wide range of topics in very detailed explanations, and by just watching one time per video, I can understand the concepts and apply them to practical problems! I highly recommend this video for anyone who wants to be an expert in Oracle but has no background (at all, in my case) just yet. :)
Amanda
January 5, 2022
Phillip is a great instructor. He works through examples as a beginner might discovering the errors and then debugging them live. The examples between lessons are on point and helped me to remember the topic at hand. Great instructor and recommended course for anyone working with Oracle SQL Developer.
Adnan
January 5, 2022
The rating I gave was for session 1 and and I will update the rating as I get through the remaining sessions in the days that lie ahead. Most likely it will get ever better.
Kevin
January 5, 2022
I love the explanations, a bit simplistic sometimes (explaining basic obvious things) but I can understand why
Amithvishal
December 8, 2021
Even ppl without prior exposure to SQL would be able to learn. Thank you for organizing contents in small understandable bytes.
Fan
December 1, 2021
The instructor has very solid knowledge and skills with Oracle SQL, to an extent that he can't refrain self from showing off his capabilities. The result is, I believe a substantial portion of the course content is irrelevant to 1Z0-071 exam, but a stage for the instructor to say 'see how good I am at mastering these tiny and useless features'!
Aram
November 26, 2021
I love the in-depth and thorough explanation. The subjects are presented in a way that increases complexity slowly and steadily which results in a really good understanding.
Timothy
October 25, 2021
I'm here to understand the technology behind SQL to pass the 1z0-071 exam; so I'll wait to see if this will help me to
Prashant
August 25, 2021
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
August 14, 2021
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.
Shubham
June 21, 2021
Its is good. I am more like a "make a notes" type person. So far I understood the concept but couldn't make notes. I hope in future classes it will get better.
Vinci
May 2, 2021
I love the presentation in Excel and SQL Developer instead of using Slides or the image of the teacher.
Mohammed
March 5, 2021
It start at this level to become interesting. The explanation is very clear and methodologically done. Thx :)
T
February 21, 2021
If you don't know a lot about SQL, this course will certainly increase your functional knowledge of the language. The instructor inspires you to be creative and tactical in the use of SQL. There are some exam-related specifics that I feel were not properly elaborated on (i.e. HR schema).
Federico
January 28, 2021
The course is really thorough and covers all the arguments required by the certification exam in a logical and beginner-friendly way. However, sometimes, I got lost in the coding with the continuous back and forth and rollbacks in the videos. A clearer, and more sequentially structured, step by step, code, presented in the videos or in the resources (which I would give in .sql format openable/editable by one of the numerous pads existing) would help to understand all the operations better.

Charts

Price

1Z0-071 Oracle SQL Developer: Certified Associate (Database) - Price chart

Rating

1Z0-071 Oracle SQL Developer: Certified Associate (Database) - Ratings chart

Enrollment distribution

1Z0-071 Oracle SQL Developer: Certified Associate (Database) - Distribution chart
2347206
udemy ID
5/1/2019
course created date
11/21/2019
course indexed date
Bot
course submited by