SQL forms the cornerstone of all relational database operations. The ability to write the SQL
language is essential for those who develop database applications. This course provides a
solid foundation of the SQL programming language that enables students to build, query and
manipulate databases. Working in Oracle database throughout this course, students compare
the ANSI/ISO standard with the SQL implementation of this database product.
Students will learn how to:
• Understand the basics of Relational Databases
• Write SQL code based on ANSI/ISO standards to build and maintain database
• Update database content with SQL and transaction handling
• Retrieve data from single or multiple tables
• Process data with row and aggregate functions
• Manipulate data with correlated and non correlated sub queries
• Apply views to break down problems and enhance security
This training course is valuable for anyone who needs to learn SQL programming. The
course is designed for students new to writing SQL queries or having insufficient practice
experience. An understanding of relational database and basic programming concepts is
What is SQL? What is ANSI SQL? Define and understand the Basics of the RDBMS:
relational databases, database normalization (briefly). SQL sessions. Types of SQL
commands: DDL, DML, DQL, DCL…
What is Data? What are database objects? What is a schema? Create a simple table: naming
a table or other object, The SQL statement CREATE TABLE. Review the table structure.
List the data types that are available for columns: character, numeric, date, large objects
(LOBs). Explain how constraints are created at the time of table creation: Creating
CONSTRAINTS in the CREATE TABLE statement, The types of CONSTRAINTS.
ALTER TABLE and DROP TABLE commands. Creating a table from an existing table.
Overview of data manipulation. Insert rows into a table: default column list, enumerated
column list. Update rows in a table. Delete rows from a table. Control transactions:
COMMIT, ROLLBACK, SAVEPOINT. Inserting data from another table.
Execute a basic SELECT statement. The SELECT statement—an example. List the
capabilities of SQL SELECT statements.
Limit the rows that are retrieved by a query: The WHERE clause, boolean logic, additional
WHERE clause features (IN, BETWEEN, IS NULL/IS NOT NULL). Sort the rows that are
retrieved by a query: reference by name, reference by position.
Describe various types of functions that are available in SQL: character functions, number
functions, date functions, conversion functions, other functions. Use character, number, and
date functions in SELECT statements. Nesting functions. Describe the use of conversion
functions: conversion functions, automatic datatype conversions.
Identify the available group functions. Describe the use of group functions: COUNT, SUM,
Group data by using the GROUP BY clause: multiple columns, ORDER BY revisited,
nesting functions. Include or exclude grouped rows by using the HAVING clause.
Write SELECT statements to access data from more than one. Table using equijoins and
non-equijoins. Using table aliases. Types of joins: inner joins, outer joins, NATURAL joins.
Multitable joins. Join a table to itself by using a self-join.
Define subqueries. Describe the types of problems that subqueries can solve. List the types
of subqueries: single-row subqueries, multiple-row subqueries, multiple-column subquery,
scalar subqueries. Solve problems with correlated subqueries.
Describe set operators. Use a set operator to combine multiple queries into a single query:
UNION, UNION ALL, INTERSECT, EXCEPT (MINUS). Control the order of rows
Utilizing views. Create and use simple and complex views. The purpose of WITH CHECK
OPTION. Performance impact of using nested views. Create and maintain indexes: implicit
index creation, single column, composite, unique, dropping. When should indexes be
considered/avoided? Create private and public synonyms.
Database tuning vs. SQL statement tuning. Formatting SQL statements: the format of SQL
statements for readability, the order of tables in the FROM clause the placement of the most
restrictive conditions in the WHERE clause, the placement of join conditions in the WHERE
Differentiate system privileges from object privileges. Controlling user access:
GRANT/REVOKE command. Controlling privileges through roles. Distinguish between
privileges and roles.
Database models: flat, hierarchical, network, relational. A relational database design.
Business process modeling. Types of database design: conceptual schema, logical schema,
physical schema. Relationships: One-to-One, One-to-Many, Many-to-Many. Normalization:
before the first normal form, the first normal form, the second normal form, the third normal
form, higher normal forms. Integrity rules: general integrity rules, database-specific integrity
rules. Breaking the rules: when to denormalize.