Title: Introduction to SQL

Abstract: SQL was, is and always will be the database language for relational database systems such as Oracle, DB2, Microsoft SQL Server, MySQL, PostgreSQL. Even for the new Hadoop environment several SQL-on-Hadoop engines have been developed, such as Apache Hive, Apache Impala, and Spark SQL. Introduction to SQL teaches in detail the full capacity of SQL as it is implemented in most of the SQL products, without neglecting the new query features. The workshop starts with teaching how to develop new tables, how to insert data to tables and how to change and delete existing tables. All the aspects of querying the data is explained, from simple one-table queries all the way up to complex joins. Subqueries are discussed as well as group by constructs. The data security aspects of SQL are explained.

The workshop teaches a SQL dialect that is supported by almost all the SQL products, so the acquired knowledge can be applied with every product. If needed, the workshop can be adapted for a specific SQL product.

The workshop follows the structure and setup of the popular and successful Introduction to SQL book, which has been translated into several languages and has been available since 1987.

 Topics:

1. The Basic Building Block of SQL: The Table

• Rows, columns, and data types
• Creating and dropping tables
• Inserting rows into tables
• Specifying primary keys and foreign keys
• The Null value
• Changing the structure of tables; adding columns and changing data types

2. Simple SQL Queries

• The key clauses of a SQL query: Select, From, Where, Group By, Having, and Order By
• Selecting columns in a query result
• Overview of the various conditions, such as between, in, and like
• Sorting rows based on column values
• Aggregating data by grouping data on one or more columns

3. Subqueries in Queries

• Subqueries with in, any, all, and exists
• Correlated subqueries
• Subqueries in the Select clause
• Subqueries in the From clause

4. Scalar Functions and Aggregate Functions

• System variables and scalar functions
• Data types and casting of values
• Functions for date and time manipulations
• Determining coming Sunday, last day of the year, or the first day of the next quarter
• Overview of aggregate functions, such as Count, Min, Max, Avg, and Sum

5. Group by and Having Clauses in Detail

• Grouping on expressions
• Grouping of Null values - one or different groups?
• General Rules for the Group By clause
• Using the Having clause to filter groups

6. The From Clause and Joins

• Combining data from two or more tables using joins
• The difference between inner and outer joins
• The role of relationships between tables on joins

7. Updating and Deleting Data

• Inserting new rows
• Coyping data from one table to another
• Updating values in rows
• Deleting rows from tables
• The effect of primary and foreign keys

8. Creating views

• The column names of views
• Updating views - with check option
• Views and privileges
• Restrictions on querying and updating views

9. Specifying Indexes

• Why are indexes needed?
• Creating indexes
• Recommendations for creating indexes

10. Securing data

• The Grant and Revoke statements
• Granting privileges to users
• Passing on privileges: the Grant option
• Working with roles
• Assigning users to groups

11. Closing Remarks

Related Articles and Blogs:

 The SQL-fication of NoSQL Continues

 Ted Codd and Twelve Rules for Relational Databases

 The Battle of the SQL-on-Hadoop Engines

 An Overlooked Difference Between SQL-on-Hadoop Engines and Classic SQL Database Servers

 Selecting the Right SQL-on-Hadoop Engine to Access Big Data