Oracle SQL Mastery Guide

Learn Oracle SQL and Gain Data Management Skills

Programming Basics & Databases4.8(83)5h 5m23 lectures5 sections

What you'll learn

  • Get to know the working environment and the basics of SELECT
  • Use WHERE conditions and sort results with ORDER BY
  • Use single-row functions and data conversion functions
  • Write logical conditions and aggregation techniques
  • Join tables and use subqueries
  • Use Set Operators
  • Run DML and DDL commands
  • Create sequences, indexes and views
  • Control user access

About this course

This course teaches the Oracle SQL language, writing queries and managing data in Oracle databases, from the introduction to advanced topics. It starts with getting to know the working environment and the basics of SELECT queries, WHERE conditions and sorting with ORDER BY. Next you learn single-row functions, data conversion functions, writing logical conditions and advanced aggregation techniques, then joining tables with Joins, subqueries and Set Operators. Then you move to the data manipulation language (DML) and the data definition language (DDL), the data dictionary, creating sequences, indexes and synonyms, and managing Views. At the end, the course covers advanced subqueries, controlling user access and advanced data manipulation techniques. It suits database beginners, programmers and data analysts, and no previous experience is needed. Afterwards, you will be able to write SQL queries, create tables and indexes and manage their data.

Expected outcomes

  • Familiarity with basic and advanced SQL concepts.
  • Ability to write complex queries for data management.
  • Understanding aggregation techniques, table joins, and using Set Operators.
  • Creating and managing professional databases using Oracle SQL.
  • Developing skills that qualify you to work as a database developer.
  • Implementing practical database projects that simulate real needs.

Learner reviews

These reviews are from learners who bought the content and completed at least 50% of it — there are no reviews from anyone who hasn't gone through the material.
4.8
★★★★★
83 reviews
5★
72
4★
3
3★
8
2★
0
1★
0

    Course content

    1

    Basics

    5 lectures
    1. Introduction to Oracle SQLIntroduces Oracle SQL and the course topics: writing statements, functions, type conversion, aggregate functions and joins, the command types DML, DDL, DCL and TCL, and relationships between tables.12:01
    2. Working with the Work EnvironmentExplains setting up the working environment for writing Oracle SQL commands, the tools that connect to the database such as SQL*Plus, and the first commands to list tables, show their structure and view employee data.15:02
    3. Basics of SELECT QueriesCovers SELECT basics: choosing columns, arithmetic and parentheses precedence, the NULL problem and fixing it with NVL, column aliases, concatenating text, DISTINCT, and DESCRIBE.9:39
    4. Using WHERE Query ConditionsExplains the WHERE clause for filtering rows: comparison operators, NOT, IN for specific values, wildcard searching, handling NULL, and condition precedence with parentheses.15:48
    5. Sorting Results Using ORDER BYExplains sorting results with ORDER BY by a column or a calculated expression, ascending and descending order, sorting by more than one column, and limiting the number of rows returned.12:50
    2

    Functions and Queries

    7 lectures
    1. Single Row FunctionsExplains single row functions in Oracle SQL that run once for each row, with examples of character functions such as concat, substr, length, upper, lower and initcap, and number functions such as round and mod.13:01
    2. Single Row FunctionsContinues single-row functions with date handling: subtracting dates to get days and converting them to weeks, months and years, adding months, finding the first Friday after a date, and rounding dates.10:21
    3. Data Conversion FunctionsExplains Oracle data conversion functions, especially TO_CHAR for displaying dates and numbers in the desired format, implicit conversion between types, and replacing NULL with a suitable value in commission.10:34
    4. Writing Conditional StatementsExplains writing conditional logic inside a query using CASE and DECODE, such as classifying employees by salary and returning a different value for each case.6:15
    5. Advanced Grouping TechniquesExplains aggregate functions that take many values and return one, such as SUM, COUNT, AVG, MAX and MIN, how NULL affects them, and using GROUP BY to summarize data by department or job.19:12
    6. Understanding Table JoinsExplains table Joins to fetch data from several tables through shared columns, joining three tables, the ON syntax, and outer joins to show employees who have no department.19:55
    7. Basic SubqueriesExplains basic subqueries: a main query containing a subquery that fetches a value not readily known, such as the average salary, comparing results against it, and watching out for NULL.12:57
    3

    Grouping and Joins

    3 lectures
    1. Working with Set OperatorsExplains set operators in SQL: UNION, UNION ALL, INTERSECT and MINUS for combining the results of two queries, the requirement that columns match in number and type, with department and employee examples.13:56
    2. Data Manipulation Language (DML) - Part 1Explains Data Manipulation Language (DML) in SQL: insert, update and delete commands, updating with a where condition, inserting data from one table into another, with a mention of commit and rollback.16:29
    3. Data Manipulation Language (DML) - Part 2Continues DML with the DELETE statement: removing one or more rows using a WHERE condition or all rows without it, along with controlling the changes through COMMIT and undoing them.6:22
    4

    Data Management

    4 lectures
    1. Introduction to Data Definition Language DDLIntroduces Data Definition Language (DDL) in Oracle SQL: creating tables with create table, default values, constraints such as a condition on salary, and removing a table with drop.25:03
    2. Exploring the Database DictionaryExplains Oracle's built-in dictionary views that expose information about a user's tables, columns and constraints, and how to query them with SELECT and DESCRIBE to learn any table's structure.12:52
    3. Creating Sequences in OracleExplains Oracle Sequences: what they are and how they generate sequential numbers for keys, how to create one and set its start value, increment, maximum and cache, and how to use and drop it.15:38
    4. Creating Indexes and SynonymsExplains Synonyms as shorter names for database objects, and Indexes that organize data on a chosen column to speed up searches, including how to create them and see which columns are indexed.8:54
    5

    Creation and Administration

    4 lectures
    1. Managing ViewsExplains what a View is and why it is useful: saving a SQL statement under a name that can be reused many times without rewriting it, with examples of creating a View and querying it like a table.15:48
    2. Advanced SubqueriesExplains advanced subqueries and placing a subquery in three places, SELECT, FROM and WHERE, with examples combining GROUP BY and fetching a value that is not readily available from another table.10:31
    3. User Access ManagementExplains controlling user access: an Oracle user is a schema, how to create one with CREATE USER, grant privileges with GRANT and remove them with REVOKE, use roles, and change a password.15:49
    4. Advanced Data Manipulation TechniquesCovers advanced DML techniques: inserting with DEFAULT, inserting data from one table into another with SELECT, conditional inserts, combining data with MERGE, and restoring a dropped table.7:00

    Instructor

    Eng. Azab Mohamed Azab

    Eng. Azab Mohamed Azab

    Lecturer at the Arab Academy for Science, Technology and Maritime Transport, and the Egyptian Ministry of Interior, specializing in technology and information.
    68.8K views28 courses

    Related courses