This course is the foundation of the Oracle Database SQL 1Z0-071 certification. It details the SELECT statement and the additional requirements for controlling user access. It is divided into 6 sessions, and each session requires one morning or afternoon to complete.
What do people like you think of this course?
Jimmy said: “The course is designed in a way that people who don’t know Oracle can understand it. It’s a good study. Thank you very much for making such a good course.”
Henry said: “I want to thank the teacher for this amazing course. This course and other materials helped me pass the 1Z0-071 exam last Saturday.”
We will install Oracle Express Edition and Oracle SQL Developer for free. Then we take a look at the 6 main clauses of the SELECT statement: SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
Then we will start to create tables, but found that if we don’t understand the data types, we can’t go very far. Then we will study string, date, and number types and functions, as well as functions to convert between them.
We will revisit the SELECT statement and introduce it in more detail. We will create tables and join them together. After completing this course, you should have a safe understanding of the 6 main clauses of the SELECT statement (the most important part of Oracle SQL).
In Session 3, we will find the missing data and learn how to delete and update the data. We will study the difference between implicit and explicit transactions, as well as various constraints, including primary keys and foreign keys.
We will start by saving our query in the view. Then we will combine the tables, instead of adding extra columns by using JOINS, but adding extra rows, such as Oracle SQL set operators such as UNION, INTERSECT, and MERGE. Then we will look at analysis functions that use the OVER clause.
Add the total to our SELECT query at the beginning of Session 5. Then we will study subqueries and how to add them to the FROM, SELECT, and WHERE clauses, as well as to the WITH clause. Finally, we will look at Oracle SQL self-joins, which are useful when you have a hierarchy.
Session 6 starts with two additional data types, TIME ZONE and INTERVAL data types and functions. Then we will look at SEQUENCE and INDEX and how they are displayed in the data dictionary. Then we will create new users and assign permissions and roles to them, and we will complete the last few requirements of the exam.
No prior knowledge is required-we will even install Oracle Database and Oracle SQL Developer on your computer for free!
Upon completion, you will learn how to manipulate numbers, strings, and dates, and how to create databases and tables, create tables, insert data, and create analyses, and learn how to use them in Oracle SQL.
- You need to know how to use a computer and want to know how to use spreadsheets.
- No prior knowledge of Oracle SQL database or any other database is required-although the more knowledge you have, the better.
- The Oracle SQL database cannot be easily installed on Mac OS. If you want to install it on a Mac, you need to dual boot into Windows or run Parallel Desktop.
- You don’t even need to install Oracle SQL Database-I will tell you you must install it on your computer for free!
- Why not take a look at the following courses and see what you can learn?
Install Oracle Express Edition and SQL Developer
- Download Oracle Express Edition
- Install Oracle Express Edition
- Download and Install Oracle SQL Developer
The SELECT statement – an overview
- Connect to Database
- Solving The network adaptor cannot establish the connection error
- The SELECT and FROM clauses
- Renaming fields
- Using the AS keyword
- Semicolons and comments
- The WHERE clause
- The GROUP BY clause
- The HAVING clause, and information about error messages
- The ORDER BY clause
- Remembering the order of the clauses
- Practice Activity Number 1 – The Solution
Session 1 – Creating tables – First pass
- Creating a table using the GUI
- Creating our first table using SQL
- 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 – The Solution
- Creating sample table; Run Script v Run Statement
- The NUMBER data type
- Do you want to use the comma as a decimal point
- FLOATing data types
- Practice Activity Number 3 – The Solution
Session 1 – Number functions
- Numeric functions Part 1
- Rounding functions
- Practice Activity Number 4 – The Solution
Session 1 – String data types and functions
- Character Encoding Systems
- Character Data Types
- String Functions
- NULL – an introduction
- Useful NULL functions
- Adding quotation marks and Alternative Quoting Mechanism in string literals
- Practice Activity Number 5 – The Solution
Session 1 – Converting numbers to strings, and strings to numbers
- Joining a string to a number using implicit conversions
- Converting a number to a string
- Converting a string to a number, including the CAST function
- International conversions
- Practice Activity Number 6 – The Solution
Session 1 – Date data types and functions
- DATE and TIMESTAMP() data types
- Date extraction functions
- Converting dates to strings
- Converting times to strings
- Converting strings to timestamps, and international considerations
- Practice Activity Number 7 – The Solution.
Session 2 – Creating and querying part of a table
- Creation of tblEmployee table
- Adding and modifying additional columns
- SELECTing only part of a table – strings
- SELECTing only part of a table – numbers
- SELECTing only part of a table – dates
Session 2 – Summarising and ordering data
- Summarising and ordering data
- Criteria on summarised data
- Exercise – Part 1
- Exercise – Part 2, and ORDER BY NULLS FIRSTLAST
- Practice Activity Number 10 – The Solution
Session 2 – Adding a second table
- Adding a second table
- Designing a connection
- Importing data and showing tables graphically
- Writing a JOIN query
- Different types of JOIN
- Using NATURAL JOINs
- Old notation joins
- Practice Activity Number 11 – Solution
Session 2 – Adding a third table
- Creating a third table
- JOINing three tables
- Practice Activity Number 12 – The Solution
Session 3 – Find missing data, and delete and update data
- Missing data
- Deleting data
- Updating data
- Practice Activity Number 13 – The Solution
- The relationship of a database and SQL
- DML, DDL, DCL and TCL
- What are transactions
- Implicit transactions
- Explicit Transactions – Start and end transactions
- Savepoints and roolback to savepoints
- Formatting in Oracle SQL Developer
And more other sections ….