Introduction to SQL language
- What is SQL language, history and uses
- division of SQL statements into categories
- How the SQL query is processed
Retrieve data using the SELECT statement
- Select all columns
- Selection of specific columns
- Working with arithmetic expressions
- NULL values ??and SELECT
- Defining Aliases (naming columns and tables)
- Joining columns and working with texts
- Suppress duplicates with DISTICT
- SELECT constraints using conditions and alphabetical sorting
- WHERE condition
- Conditions for equality, greater, smaller and unequal
- Conditions for the range of values ??using BETWEEN
- Multiple conditions using OR or AND
- Multiple conditions using IN
- Searches for text expressions using LIKE
- Search for NULL values
- Priority multiple conditions
- Sort the output alphabetically
- Limit selection to a certain number of rows
- Interactive query writing - Substitution variables
Single line functions
- Explanation of how to use them in SQL statements
- functions for working with text
- case sensitivity suppression function
- nesting functions
- functions for rounding and trimming decimal places
- MOD function to check the correctness of numbers
- date function
Conversion function
- Text conversion function
- Number conversion function
- Date conversion function
- How to understand date and number formats
- Functions for working and conversion with NULL values
- CASE and DECORE functions
Data merging and aggregation functions
- How to merge data using GROUP BY
- WHERE and HAVING conditions
- How to calculate Average, Minimum, Maximum, Sum and Count
Retrieving data from multiple tables
- Introduction - why data is obtained from multiple tables
- Queries over multiple INNER JOIN tables
- Queries over multiple LEFT, RIGHT, and FULL JOIN tables
- How to retrieve data from more than two tables
- Queries over multiple CROSS JOIN tables
Use of SUBQUIRIES
- What are subqueries good for (nested queries)
- Creation of subqueries in WHERE conditions
- Multiple rows returning subqueries - IN, ANY, ALL
- Multi-column subqueries
Use of SET operators
- How to combine the outputs of multiple queries into one result
- UNION
- UNION ALL
- INTERSECT
- MINUS
Inserting, modifying and deleting data in tables using DML commands
- Data entry - INSERT
- Data change - UPDATE
- Deleting data - DELETE
- Delete the contents of the table using TRUNCATE TABLE
- Transactions and their termination using COMMIT or ROLLBACK
- Defining transactional CHECKPOINTS
- Consistent reading
- SELECT FOR UPDATE
Creating tables and introduction to DDL commands
- How to name columns, tables, and other objects
- How to create a table
- Data types in tables
- Defining DEFAULT values
- Restrictions - Primary key, Foreign key, UNIQUE, Not Null, CHECK
- Create a table using a subquery
- Editing tables (ALTER TABLE)
- DROP TABLE
Data dictionary views
- Explanation of what a data dictionary is
- Division of views into V $ and DBA views
- Views DBA, ALL, USER
- How to get the necessary information from the data dictionary
Creating sequences, synonyms and indexes
- Explanation of what a sequence is
- Creating sequences
- How to use sequences to generate key values ??during INSERT
- How to get sequence information
- An explanation of what a synonym is
- Creating and deleting synonyms
- What is a good index for
- Creating indexes
- Cancel indexes
VIEW
- Introduction what VIEW is and what it is used for
- Creating views
- Changing views
- Constraints in views
- How to create a VIEW that allows data editing
- Read-only views
Management and changes of objects in schemas
- How to add and remove constraints in tables
- Constraint Removal Online
- Cascading possibilities of constraints
- Renaming columns, constraints and tables
- Switching constraints on and off
- Constraint states
- Delayable execution of constraints
- Delete a table with the PURGE option
- Working with temporary tables
- External tables
Data acquisition using subqueries
- Jednoduché subdotazy (opakování)
- Více sloupcové subtodaty
- Correlované subdotazy
- EXISTS a NOT EXISTS v subdotazech
- Subdotazy pomocí WITH klauzule
Manipulace dat pomcí subdotazů
- Vkládání dat pomocí subdotazů
- WITH CHECK constraint v subdotazech
- UPDATE v correlovaných subdotazech
- DELETE v correlovaných subdotazech
User access and rights control
- Introduction to system and object rights
- System rights
- Object rights
- Role
- Change user password
- Assigning and removing rights to users
Data manipulation using advanced queries
- Use DEFAULT values when entering data
- Insert data into multiple tables with a single command
- Conditional insertion of data into tables according to conditions
- MERGE statement for inserting or changing data
- Introduction to FLASHBACK technologies
Working with date and time in different time zones
- Introduction - time zones
- Different types of date and time acquisition with time zones and offsets
- Advanced data types for working with data
