SQL

SQL is the universal language of databases, empowering individuals to manage, manipulate, and extract valuable insights from vast data repositories.

Whether you are a novice eager to understand the basics or an experienced user aiming to sharpen your SQL skills, this course is tailored to accommodate all levels of proficiency. SQL is not just a tool; it’s a key that unlocks the potential to interact with databases efficiently, making it an essential skill for anyone working with data.

In this course, we will start by laying the foundation, exploring the fundamental concepts of databases, understanding the structure of tables, and learning how to retrieve data using basic queries. As we progress, we’ll delve into more advanced topics such as data manipulation, joining tables, and optimizing queries for performance.

The practical application of SQL is paramount, and hands-on exercises throughout the course will provide you with the opportunity to implement your newfound knowledge. Whether you’re a student aspiring to grasp database fundamentals or a professional aiming to enhance your data management skills, the SQL Syllabus Course is designed to empower you with the tools to navigate and command relational databases confidently.

Get ready to unravel the mysteries of SQL, from crafting simple queries to orchestrating complex database operations. By the end of this course, you’ll be equipped to harness the power of SQL for data analysis, reporting, and database management. Let’s dive into the world of relational databases and unleash the full potential of SQL.

SQL Syllabus

  • Introduction to Oracle Database
    List the features of Oracle Database 11g
    Discuss the basic design, theoretical, and physical aspects of a relational database
    Categorize the different types of SQL statements
    Describe the data set used by the course
    Log on to the database using SQL Developer environment
    Save queries to files and use script files in SQL Developer
    Retrieve Data using the SQL SELECT Statement
    List the capabilities of SQL SELECT statements
    Generate a report of data from the output of a basic SELECT statement
    Select All Columns
    Select Specific Columns
    Use Column Heading Defaults
    Use Arithmetic Operators
    Understand Operator Precedence
    Learn the DESCRIBE command to display the table structure
    Learn to Restrict and Sort Data
    Write queries that contain a WHERE clause to limit the output retrieved
    List the comparison operators and logical operators that are used in a WHERE clause
    Describe the rules of precedence for comparison and logical operators
    Use character string literals in the WHERE clause
    Write queries that contain an ORDER BY clause to sort the output of a SELECT statement
    Sort output in descending and ascending order
    Usage of Single-Row Functions to Customize Output
    Describe the differences between single row and multiple row functions
    Manipulate strings with character function in the SELECT and WHERE clauses
    Manipulate numbers with the ROUND, TRUNC, and MOD functions
    Perform arithmetic with date data
    Manipulate dates with the DATE functions
    Invoke Conversion Functions and Conditional Expressions
    Describe implicit and explicit data type conversion
    Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
    Nest multiple functions
    Apply the NVL, NULLIF, and COALESCE functions to data
    Use conditional IF THEN ELSE logic in a SELECT statement
    Aggregate Data Using the Group Functions
    Use the aggregation functions in SELECT statements to produce meaningful reports
    Divide the data in groups by using the GROUP BY clause
    Exclude groups of date by using the HAVING clause
    Display Data From Multiple Tables Using Joins
    Write SELECT statements to access data from more than one table
    View data that generally does not meet a join condition by using outer joins
    Join a table by using a self join
    Use Sub-queries to Solve Queries
    Describe the types of problem that sub-queries can solve
    Define sub-queries
    List the types of sub-queries
    Write single-row and multiple-row sub-queries
    The SET Operators
    Describe the SET operators
    Use a SET operator to combine multiple queries into a single query
    Control the order of rows returned
    Data Manipulation Statements
    Describe each DML statement
    Insert rows into a table
    Change rows in a table by the UPDATE statement
    Delete rows from a table with the DELETE statement
    Save and discard changes with the COMMIT and ROLLBACK statements
    Explain read consistency
    Use of DDL Statements to Create and Manage Tables
    Categorize the main database objects
    Review the table structure
    List the data types available for columns
    Create a simple table
    Decipher how constraints can be created at table creation
    Describe how schema objects work
    Other Schema Objects
    Create a simple and complex view
    Retrieve data from views
    Create, maintain, and use sequences
    Create and maintain indexes
    Create private and public synonyms
    Control User Access
    Differentiate system privileges from object privileges
    Create Users
    Grant System Privileges
    Create and Grant Privileges to a Role
    Change Your Password
    Grant Object Privileges
    How to pass on privileges?
    Revoke Object Privileges
    Management of Schema Objects
    Add, Modify and Drop a Column
    Add, Drop and Defer a Constraint
    How to enable and disable a Constraint?
    Create and Remove Indexes
    Create a Function-Based Index
    Perform Flashback Operations
    Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
    Query External Tables
    Manage Objects with Data Dictionary Views
    Explain the data dictionary
    Use the Dictionary Views
    USER_OBJECTS and ALL_OBJECTS Views
    Table and Column Information
    Query the dictionary views for constraint information
    Query the dictionary views for view, sequence, index and synonym information
    Add a comment to a table
    Query the dictionary views for comment information
    Manipulate Large Data Sets
    Use Subqueries to Manipulate Data
    Retrieve Data Using a Subquery as Source
    Insert Using a Subquery as a Target
    Usage of the WITH CHECK OPTION Keyword on DML Statements
    List the types of Multitable INSERT Statements
    Use Multitable INSERT Statements
    Merge rows in a table
    Track Changes in Data over a period of time
    Data Management in Different Time Zones
    Time Zones
    CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
    þÿCompare Date and Time in a Session  s Time Zone
    DBTIMEZONE and SESSIONTIMEZONE
    Difference between DATE and TIMESTAMP
    INTERVAL Data Types
    Use EXTRACT, TZ_OFFSET and FROM_TZ
    Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
    Retrieve Data Using Sub-queries
    Multiple-Column Subqueries
    Pairwise and Nonpairwise Comparison
    Scalar Subquery Expressions
    Solve problems with Correlated Subqueries
    Update and Delete Rows Using Correlated Subqueries
    The EXISTS and NOT EXISTS operators
    Invoke the WITH clause
    The Recursive WITH clause
    Regular Expression Support
    Use the Regular Expressions Functions and Conditions in SQL
    Use Meta Characters with Regular Expressions
    Perform a Basic Search using the REGEXP_LIKE function
    Find patterns using the REGEXP_INSTR function
    Extract Substrings using the REGEXP_SUBSTR function
    Replace Patterns Using the REGEXP_REPLACE function
    Usage of Sub-Expressions with Regular Expression Support
    Implement the REGEXP_COUNT function