Structure Query Language (SQL) is programming language used to query relational databases. This article is going to cover some basic contracts in SQL, which would help you in career path to SQL developer using relational databases such as SQL server or any other database management systems (DBMS).

Nested Queries

Nested or sub-queries are the queries used within another top-level query. The results of parent or top query depend upon the outcome of nested query. SQL clauses like IN, NOT IN, ANY and ALL can be used with sub-queries. However, are certain constrains on sub-queries like, you can’t use ORDER BY within sub-query; and if result of your sub-query expects single value then you have to make sure it returns single value from sub-query by using TOP within or IN, NOT IN etc. in outer query. Query in 6.10 ‘c’ can be re-written as sub-queries, to fetch employees working under supervision of ‘Franklin Wong’.

Nested query example
Nested query example

Joined Tables and Outer Joins

Joins are SQL constructs used to fetch data from two or more tables based on specified conditions. There are four commonly used types of joins INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN or simple OUTER JOIN.

INNER JOIN is used to fetch matching records from two or more tables, while OUTER join is exact opposite of inner join; and return un-matching rows or records. LEFT JOIN gives back matching rows from LEFT side table and for right side null values retuned for any un-matching rows. RIGHT JOIN is opposite of LEFT join as name is pretty much self-explanatory.

Aggregate Functions and Grouping

Aggregation and group are analytics clauses which operated on multiple rows and aggregate and group data as desired. Common aggregation functions are SUM, AVG, MIN, MAX. The GROUP BY clause is used in conjunction with aforementioned aggregate functions to create groups of result set.

For example, in 6.10 database schema if you want to know total hours worked by each employee you can achieve with aggregation and grouping as following query.

Groupping with aggregate example
Grouping with aggregate example

Triggers

A Procedure or Store Procedure is term used in databases to refer peace of SQL code sitting within database, and that can be invoked or scheduled to invoked on certain events. Trigger is special kind of Procedure that is executed or invoked automatically in case of certain events in within DBMS.

There are two main kinds of trigger used in modern DBMSs; Database Manipulation Language (DML) trigger which is executed when in case when you perform DELET, UPDATE or INSERT to any database table. Database Definition Language (DDL) triggers; this is another form of trigger which is invoked when you CREATE, ALTER or DROP table from database.

Assertions and how they differ from Triggers

Assertions are checks or precisely CHECK CONSTRAINTS in relational database management systems which evaluates certain conditions and makes sure it satisfies rules specified for that table.

For example, in certain related tables you specified some values to be NOT NULL, this condition is evaluated at time of insertion or update to validate the data integrity. While triggers are special Procedures consist of SQL code executed on certain events in Database.

The SQL WITH clause

WITH clause in SQL lets to create named query blocks which simplifies large SQL query nesting multiple levels and including multiple joining tables. For example, query in 6.10 ‘c’ can be simplified using WITH clause as follows. This query looks simpler and easier to read. Plus, in case of very large queries where you need to be used same sub-queries multiple times WITH clause significantly simplifies the query to look cleaner.

WITH clause example
WITH clause example

SQL Case Construct

SQL CASE example
SQL CASE example

SQL CASE clause used in quires to format the result based on conditions in from of case statements, which evaluates to one or more values as specified. CASE clause used in conjunction with WHEN and THEN clause. Here is an example if you need to see if employee has any dependents.

Views and Update capability

Views in SQL are just like tables, but the data represented in views is collected from one or more physical or actual database tables, so in other words view is like virtual table which generates data from multiple tables.

Views are very useful in abstracting complex queries into simpler data representations, and for read-only data access. CREATE VIEW statement is used to create new view in database and CREATE OR REPLACE VIEW is the statement used to update any existing views in database. View also provide layer of security over tables as exposing them as read-only, which can’t be targeted in SQL Injection attacks.

Schema Change Commands

The schema command in SQL are used to modify schema of database. The actual syntax for these commands may vary between DBMS providers but the most commonly used schema commands are:

1. CREATE/DROP DATABASE – Used to create or drop database.
2. ALTER SCHEMA – Alters schema to add, remove or modify objects.
3. CREATE TABLE – Creates news table.
4. DROP TABLE – Drop existing table.
5. ALTER TABLE – Alter existing table to add, remove, or modify columns, constraints etc. This command is also used in conjunction with renaming column, adding new column, drop column, add or modify constraints etc.
6. CREATE/DROP VIEW – Create or drop views from database.
7. GRANT/REVOKE – Various permissions related activities can be performed using GRANT, DENY etc.