1Z0-071 Oracle Database SQL Developer: Certified Associate
4.7 (290 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,892 students enrolled

1Z0-071 Oracle Database SQL Developer: Certified Associate

Learn about the SELECT statement, DDL, DML, functions + more. Oracle Database 11g, 12c, 19c using Oracle SQL Developer.
Bestseller
4.7 (290 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,892 students enrolled
Created by Phillip Burton
Last updated 7/2020
English
English
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 19 hours on-demand video
  • 28 articles
  • 15 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
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
Course content
Expand all 213 lectures 19:17:14
+ 3a. The SELECT statement - an overview
13 lectures 01:00:15
8e. The SELECT and FROM clauses
08:59
Renaming fields
06:30
Using the AS keyword
02:18
3b, 13b. The WHERE clause
05:56
15c. The HAVING clause, and information about error messages.
05:29
13a, 13c. The ORDER BY clause
04:12
Remembering the order of the clauses
02:28
Practice Activity Number 1
00:22
Practice Activity Number 1 - The Solution
04:56
+ 8b. Session 1 - Creating tables - First pass
5 lectures 14:21
Creating a table using the GUI
03:04
Creating our first table using SQL
02:36
17a. Inserting values
02:04
Deleting the data, then the table
02:52
Trying to create an Employee table
03:45
+ Session 1 - The DUAL table and Number types
9 lectures 36:10
Using the DUAL table
03:53

Now it's your turn. 

This exercise is to practice writing a query, and creating simple mathematical expressions.

Practice Activity Number 2 - Writing mathematical queries
00:19
Practice Activity Number 2 - The Solution
02:34
8c. Creating sample table; Run Script v Run Statement
06:45
8a. The NUMBER data type
08:35
Do you want to use the comma as a decimal point?
04:25
8a. FLOATing data types
04:54
Practice Activity Number 3
00:18
Practice Activity Number 3 - The Solution
04:26
+ Session 1 - Number functions
4 lectures 18:24
5b, 14a. Numeric functions Part 1
06:13
Rounding functions
08:18
Practice Activity Number 4
00:27
Practice Activity Number 4 - The Solution
03:26
+ Session 1 - String data types and functions
8 lectures 46:05
Character Encoding Systems
04:14
8a. Character Data Types
11:04
5b, 14a. String Functions
07:12
NULL - an introduction
05:52

NVL, NVL2, NULLIF and COALESCE

Useful NULL functions
07:20
Adding quotation marks and Alternative Quoting Mechanism in string literals
04:15
Practice Activity Number 5
00:42
Practice Activity Number 5 - The Solution
05:26
+ Session 1 - Converting numbers to strings, and strings to numbers
6 lectures 29:33
5b, 14a. Joining a string to a number using implicit conversions
04:18
5a, 14b. Converting a number to a string
09:46
5a, 14b. Converting a string to a number, including the CAST function
04:58
5a, 14b. International conversions
06:55
Practice Activity Number 6
00:14
Practice Activity Number 6 - The Solution
03:22
+ Session 1 - Date data types and functions
7 lectures 50:27
8a. DATE and TIMESTAMP() data types
07:45
5b, 14a. Date extraction functions
07:43
5a, 14b. Converting dates to strings
12:12
5a, 14b. Converting times to strings
06:17
5a, 14b. Converting strings to timestamps, and international considerations
09:30
Practice Activity Number 7
00:47
Practice Activity Number 7 - The Solution
06:13
+ End of Session 1, Start of Session 2
2 lectures 04:14
End of Session 1
02:41
Welcome to Session 2
01:33
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.
  • 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.

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.

Who this course is for:
  • 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.