SQL Basic and Intermediate Bespoke

Relationaldatabasemodels

• The structure of a relational database
• Connection types of tables
• The normalization and denormalization database
• Relational Operators

Download the data

• Rules for writing SQL queries
• The syntax for the SELECT
• Selecting all columns
• Inquiries from arithmetic operations
• Aliases columns
• Literals
• Concatenation operator

Limitingresults

• The WHERE clause
• Comparison operators
• LIKE Condition
• Prerequisite BETWEEN ... AND
• IS NULL condition
• Condition IN
• Boolean operators AND, OR and NOT
• Many of the conditions in the WHERE clause
• The order of the operators.
• DISTINCT clause

Sorting Data

• The ORDER BY clause
• Sorting by multiple columns or expressions

SQL functions

• The differences between the functions of one and multilines
• Features text, numeric, date,
• Explicit and implicit conversion
• Conversion functions
• Nesting functions
• Viewing the performance of the functions - dual table
• Getting the current date function SYSDATE
• Handling of NULL values

Aggregating data using the grouping

• Grouping functions
• How grouping functions treat NULL values
• Create groups of data - the GROUP BY clause
• Grouping multiple columns
• Limiting the function result grouping - the HAVING clause

Retrieving data from multipletables

• Types of connectors
• The use NATURAL JOIN
• Aliases tables
• Joins in the WHERE clause
• INNER JOIN Inner join
• External Merge LEFT, RIGHT, FULL OUTER JOIN
• Cartesian product

Subqueries

• Place subqueries in the SELECT command
• Subqueries single and multi-lineage
• Operators Subqueries single-line
• Features grouping in subquery
• Operators Subqueries multi-IN, ALL, ANY
• How NULL values ​​are treated in subqueries

Operatorscollective

• UNION operator
• UNION ALL operator
• INTERSECT operator
• MINUS operator

Insert, update, and delete data

• INSERT command
• Copy data from another table
• UPDATE command
• DELETE command
• TRUNCATE command

Transactions

• Commands COMMIT, ROLLBACK, and SAVEPOINT

DDL commands

• The main database objects
• Rules for naming objects
• Creating tables
• The data types available for columns
• DEFAULT option
• Option NULL and NOT NULL

Managingtables

• Referential integrity CHECK, PRIMARY KEY, FOREIGN KEY, UNIQUE
• Create a table by the query
• Delete a table DROP TABLE
• DESCRIBE command

Otherschemaobjects

• Sequences
• Synonyms
• Views

Selecting data from database

• Syntax rules
• Selecting all columns
• Projection
• Arithmetical operations in SQL
• Columns aliases
• Literals
• Concatenation

Filteringoutcometables

• WHERE clause
• Comparison operators
• Condition LIKE
• Condition BETWEEN...AND
• Condition IS NULL
• Condition IN
• AND, OR, NOT operators
• Several conditions in WHERE clause
• Operators order
• DISTINCT clause

Sortingoutcometables

• ORDER BY clause
• Sort by multiple columns or expressions

SQL Functions

• Differences between single-row and multi-row functions
• Character, numeric, DateTime functions
• Explicit and implicit conversion
• Conversion functions
• Nested functions
• Dual table (Oracle vs other databases)
• Getting current date and time with different functions

Aggregate data usingaggregatefunctions

• Aggregate functions
• Aggregate functions vs NULL value
• GROUP BY clause
• Grouping using different columns
• Filtering aggregated data - HAVING clause
• Multidimensional Data Grouping - ROLLUP and CUBE operators
• Identifying summaries - GROUPING
• GROUPING SETS operator

Retrieving data from multipletables

• Different types of joints
• NATURAL JOIN
• Table aliases
• Oracle syntax - join conditions in WHERE clause
• SQL99 syntax - INNER JOIN
• SQL99 syntax - LEFT, RIGHT, FULL OUTER JOINS
• Cartesian product - Oracle and SQL99 syntax

Otherschemaobjects

• Sequences
• Synonyms
• Views

Hierarchicalqueries and samples

• Tree construction (CONNECT BY PRIOR and START WITH clauses)
• SYS_CONNECT_BY_PATH function

Conditionalexpressions

• CASE expression
• DECODE expression

Data management in different time zones

• Time zones
• TIMESTAMP data types
• Differences between DATE and TIMESTAMP
• Conversion operations

Analyticfunctions

• Use of
• Partitions
• Windows
• Rank functions
• Reporting functions
• LAG/LEAD functions
• FIRST/LAST functions
• Reverse percentile functions
• hypothetical rank functions
• WIDTH_BUCKET functions
• Statistical functions