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.54 (1878 reviews)
Udemy
platform
English
language
IT Certification
category
instructor
11,432
students
19.5 hours
content
Dec 2023
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.

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!

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

Reviews

Floyd
November 14, 2023
I am at the end of session 14 on this day (11/13/2023). This has definitely been a great course so far. I am learning a lot.
Molapo
August 20, 2023
With this course i have learnt so many new faculties of Oracle SQL through SQL developer that i never knew existed. Greate course.
Ritika
June 29, 2023
1. When the section starts it should with little bit of introduction and definitions. 2. Videos end abruptly. 3. I loose interest in videos and have o put extra efforts to keep my focus.
Ramona
June 28, 2023
Explanations are clear, there are practice activities after each session (could be more though). And this is the best SQL 1Z0-071 course I could find on Udemy -> I bought 3, but two of them returned (after few lessons). Stayed with it as the instructors teaching style seemed most appropriate. I learnt a lot! Why not 5 stars? Because this course will not be enough to pass 1Z0-071 . It does go over almost all topics necessary (except GTT and some aspects of privileges), but exam is very, very detailed - so you have to practice exam questions separately. (I have to say that this course will be much more suitable for work as it includes more analytical functions than needed for the exam). Passed the exam today with 88%. Good luck and keep studying!
Adam
April 26, 2023
I have experience but only using the Toad application. As a functional, learning SQL Developer is essential to understand data you analyze and this course allows me to know how to use SQL Developer.
Petru
February 24, 2023
At first, it seems a good course with a warm voice and you get to understand things. Then you start to realize different points like: the order of the lectures, which is totally different from other courses and it's not such a good thing in the end (s. primary key & others that are taught very late), then the fact that all practices are based on the same!! %$#^ ptbl tables and if you not feel like spending additional time creating them, you can't do any practice which sucks - it shouldn't be based on some random created tables and not for eg. the main ones, like tbl_employee/department/transaction. And last but not least, your explanations, I found to be not very helpful with a lot of wasteful & not useful lectures AND TOTAL LACK OF FOCUS on the exam, even though the name of your course contains it. Sorry, but very disappointed. (after failing the exam I feel like this didn't help at all, but wasted my time).
M
February 11, 2023
The instructors examples and knowledge were great, however two things would make this course 5 stars: 1. More practice problems 2. Suggestions in actual ways or post course problems sets to practice This instructor cops out with, "Dive in on the job or practice this on the job" (Paraphrasing). For those of us without a job with Oracle to practice, you have to find some other way to practice.
Mikhail
December 23, 2022
I am halfway through the course. It's a very good course. I feel that I am learning and starting to get to grips with Oracle SQL. I use PL/SQL dayily as I have been recently hired as a Bank Risk Analytic. No prior expereience in neither SQL nor Python before. I hope I will not bankrupt the place before I finish the course. If I bankrupt my employer after I finish the course at least I can blaim Phillip. (:
Alice
December 3, 2022
I'm using Oracle SQL every day at work, but I learned a lot of new useful things with this course. Thank you!
Vincent
October 24, 2022
Phillip Burton is an outstanding teacher. He is very articulate, and I plan on taking more of his courses going forward!
Norbert
September 22, 2022
Phillip, with all the respect.... This course will not get you certified. I did this one and also the course from Imtiaz (having similar title). I still felt something is missing, so I also bought the book from Steve O'Hearn, red all the 600 pages 5 times. I also did the practice exam 4 times. As you can see I did a ton of preparation, and still failed the exam with 51.7% I feel a bit frustrated because I invested aprox 350 usd in this project, including months of learning. I felt doing extra miles to get certified, and still failed, because my perception was wrong about my progress. This course is setting wrong perception on the chances passing the course, and the students end up wasting time and money. Love your other course on Tableaus though. That one got me a cert for Tableau Desktop. Thanks for that!
Brian
September 13, 2022
I'm a C# developer, familiar with Microsoft SQL, finding myself having to suddenly learn Oracle. I'm about midway through this course and the pace/instructor are great. I feel like the material is being presented in a very clear and constructive manor, with very particular snags being mentioned, as applicable. I think this course will end up being a great resource, in the future, should I have to visit a very specific topic. I'm learning a lot, and I'm participating in all the practices, which I would recommend to everyone. Don't just watch... build the keyboard muscle memory. Fantastic course, fantastic instructor!
Casey
August 19, 2022
I just completed an SQL Nanodegree from Udacity prior to trying Udemy, and this guy's teaching style is much more clear and relatable. He is filling in all of the gaps. I wish I hadn't spent so much money on the Nanodegree.
Rohit
August 10, 2022
I am very happy and confident as I am going further wit each session. The Professor make it work so easy for the beginners like me to understand things easily without getting confused. His guidance, his way of teaching and the helping guide make things very easy for me. Thank you for everything. I will always be taking guidance and help from you. Whenever needed.
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.

Coupons

DateDiscountStatus
8/19/202284% OFF
expired

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