Fundamental SQL Concepts in a Nutshell.

5 Basic Types of SQL Queries

• DDL – Data Definition Language: Collection of commands which defines the database structure.
  • CREATE
  • DROP
  • ALTER
  • TRUNCATE
• DML – Data Manipulation Language: Collection of main commands which manipulates data.
  • INSERT
  • UPDATE
  • DELETE
• DQL – Data Query Language: Collection of commands to query and retrieve data.
  • SELECT
  • DISTINCT
  • AS
  • WHERE, AND / OR, LIKE,
  • LIMIT
  • ORDER BY
  • GROUP BY, HAVING
  • INNER/OUTER JOIN
  • WITH
  • SUM(), AVG(), … aggregate functions
• DCL – Data Control Language: Collection of commands used for access control.
  • GRANT
  • REVOKE
• TCL – Transaction Control Language: Collection of commands used to ensure data consistency.
  • COMMIT
  • ROLLBACK
  • SAVEPOINT

About query execution order

Important points to note related to above query execution order:

• If a “JOIN” is used without the “ON” condition, it will defaults to a Cross JOIN which produces the cartesian product of two tables. This can produce a huge number of rows if the tables are large.

Let’s understand SQL using a few Queries

Query #1

The following single query gives the order details, total amounts, a running total per customer, and average order value all in a structured reference query.

Concepts Used in This Query:

1. Common Table Expression (CTE) – WITH ordersummary AS (…)
2. A nested SELECT query
3. Aggregation – SUM(o.total) OVER (…) AS runningTotal
4. Joins – JOIN tb1 ON condition
5. Where filtering – WHERE condition
6. SELECT query.
7. A nested SELECT query used with aggregation
8. Nested query is used in filtering
9. Grouping – GROUP BY col1, col2, …
10. Having Clause affect for Grouped results – HAVING COUNT (os.orderId) > 0
11. Sorting – ORDER BY os.runningTotal DESC
12. Limiting the final results – LIMIT 1