Objective

Nowadays the IT industry uses numerous different kinds of database technologies. The most common database systems are the RDBMS databases. Many corporate systems store their data on such databases. With the help of the structured query language (SQL) we can gather all the stored information. The SQL is a standard language, therefore can be used for any RDBMS database.

Participants

Software developers, database engineers and users

Content

On this course, participants get an insight into the different uses of SQL.

Modules

• Introduction
  • Theory of relational databases
  • Different types of SQL statements
• SQL SELECT statement
  • Basic SELECT statement
  • Columns in SELECT statement
  • Operators in SELECT statement
• Filtering and sorting data
  • Using WHERE clause to limit the data
  • Comparison and logical operators  in the WHERE clause
  • Precedence rules of operators
  • String literals in the WHERE clause
  • ORDER BY clause to sort the data
• Functions to customize the output
  • String manipulation functions in SELECT statement
  • Manipulate numbers with  ROUND, TRUNC, and MOD functions
  • Manipulate dates with  DATE functions
• Conversion functions and conditional expressions
  • \nTO_CHAR, \nTO_NUMBER, and \nTO_DATE
  • NVL, NULLIF, and COALESCE
  • IF THEN ELSE  in a SELECT statement
• Aggregating data
  • Divide data in groups by  GROUP BY
  • Exclude groups of data by \nHAVING
• Display data from multiple tables using joins
• Using sub-queries
  • The EXISTS and NOT EXISTS operators
  • Invoke the WITH clause
• The SET operator
• Data manipulation statements
  • UPDATE statement
  • DELETE statement
  • COMMIT and ROLLBACK statements
• Use of DDL statements to create and manage tables
  • Other schema objects
  • Simple and complex view
  • Retrieve data from views
  • Create, maintain, and use sequences
  • Create and maintain indexes
• Management of schema objects
  • Add, modify, and drop a column
  • Add, drop, and defer a constraint
  • Create and remove indexes
  • Use the dictionary views
• Manipulate large data sets
  • Use subqueries to manipulate data
  • WITH CHECK OPTION keyword
  • Use multitable INSERT statements
• Different time zones
  • CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
  • DBTIMEZONE and SESSIONTIMEZONE
  • DATE and TIMESTAMP and INTERVAL Data
  • EXTRACT, TZ_OFFSET and FROM_TZ
  • TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
• Regular expression
  • Using the REGEXP_LIKE function
  • Using the REGEXP_INSTR function
  • Using the REGEXP_SUBSTR function
  • Using the REGEXP_REPLACE function