Language: English | Size: 7.71 GB | Duration: 15h 7m
The SELECT statement, functions and more. Oracle Database 19c, 21c, 23c, 23ai using Oracle SQL Developer. 1Z0-171 exam.
What you'll learn
Relational Database Concepts
Retrieving Data using the SQL SELECT Statement and Restricting and Sorting Data
Using Functions
Displaying Data from Multiple Tables, including using SET operators
Using Subqueries to Solve Queries
Managing Indexes Synonyms and Sequences
Manage tables and their relationships using DDL and DML statements
Control User Access
Managing Objects with Data Dictionary Views and Data in Different Time Zones
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!
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-171 certification. It covers the SELECT statement in detail, with additional requirements for controlling user access.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.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.We'll then 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.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.We'll add 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. We'll also look at Oracle SQL self-joins, which are useful when you have hierarchies.Finally, 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 Curriculum
Lecture 6 Resources
Section 2: Install Oracle Express Edition and SQL Developer
Lecture 7 Download and Install Oracle Express Edition
Lecture 8 Download and Install Oracle SQL Developer, and connect to our database
Lecture 9 Solving connection errors
Section 3: The SELECT statement - an overview
Lecture 10 5. The SELECT and FROM clauses
Lecture 11 4. Renaming fields (using column aliases), including using the AS keyword
Lecture 12 5. Semicolons and comments
Lecture 13 5, 9. The WHERE clause
Lecture 14 5, 6d. The GROUP BY clause and the DISTINCT keyword
Lecture 15 5, 21. The HAVING clause, and information about error messages.
Lecture 16 13a, 13c. The ORDER BY clause
Lecture 17 Remembering the order of the clauses
Lecture 18 Practice Activity Number 1
Lecture 19 Practice Activity Number 1 - The Solution
Section 4: 8b. Session 1 - Creating tables - First pass
Lecture 20 Creating a table using the GUI and using DDL
Lecture 21 35a. Inserting values
Lecture 22 35c, 44. Deleting the data, then the table
Lecture 23 Practice Activity Number 2
Lecture 24 Practice Activity Number 2 - The Solution
Section 5: Session 1 - The DUAL table and Number types
Lecture 25 7, 8. Using the DUAL table, and rules of precedence for arithmetic expressions
Lecture 26 Practice Activity Number 3 - Writing mathematical queries
Lecture 27 Practice Activity Number 3 - The Solution
Lecture 28 40. Creating sample table; Run Script v Run Statement
Lecture 29 41. The NUMBER data type
Lecture 30 Do you want to use the comma as a decimal point?
Lecture 31 8a. FLOATing data types
Lecture 32 Practice Activity Number 4
Lecture 33 Practice Activity Number 4 - The Solution
Section 6: Session 1 - Number functions
Lecture 34 Numeric functions: POWER, SQRT, EXP, LN, LOG, ABS, SIGN and trigonometric
Lecture 35 15. Rounding functions, including ROUND, TRUNC and MOD functions
Lecture 36 Practice Activity Number 5
Lecture 37 Practice Activity Number 5 - The Solution
Section 7: Session 1 - String data types and functions
Lecture 38 6. Using CHAR and VCHAR with concatenation operator, literal character strings
Lecture 39 NCHAR and NVARCHAR Character Data Types
Lecture 40 13. String Functions
Lecture 41 7b. NULL - an introduction
Lecture 42 7b, 17. Useful NULL functions
Lecture 43 Practice Activity Number 5
Lecture 44 Practice Activity Number 5 - The Solution
Section 8: Session 1 - Converting numbers to strings, and strings to numbers
Lecture 45 18. Joining a string to a number using implicit conversions
Lecture 46 19a, 20. Converting a number to a string - Part 1
Lecture 47 19a. Converting a number to a string - Part 2
Lecture 48 19b. Converting a string to a number, including the CAST function
Lecture 49 5a, 14b. International conversions
Lecture 50 Practice Activity Number 6
Lecture 51 Practice Activity Number 6 - The Solution
Section 9: Session 1 - Date data types and functions
Lecture 52 DATE and TIMESTAMP() data types
Lecture 53 14, 16. Date extraction functions
Lecture 54 19a. Converting dates to strings
Lecture 55 19a. Converting times to strings
Lecture 56 19c. Converting strings to timestamps using the TO_DATE conversion function
Lecture 57 Practice Activity Number 7
Lecture 58 Practice Activity Number 7 - The Solution
Section 10: Creating and querying part of a table
Lecture 59 42. Creating the TBL_EMPLOYEE table
Lecture 60 40. Adding and modifying additional columns
