SELECT

Selects everything (all rows and columns) from Employees table.

Limits selection to particular columns from a table.

No such column like '1'. The number appears as a result of the query. There are nine rows which corresponds to number of rows in Employees table.

Column or table names containing spaces should be in brackets.

Aliases

Gives alias to a column.

Gives alias to a table.

LEFT

Returns 25 characters counting from the left.

Adding DISTINCT to above example will reduce number of results to distinct (different) records.

RIGHT

Returns 25 characters counting from the right.

SUBSTRING

Extracts a substring from a string (start at position 6, extract 9 characters).

LTRIM removes unwanted space(s) from the left, which appeared in the above example.

LTRIM

Removes space(s) from the left.

RTRIM

Removes space(s) from the right.

CONCAT

Joins character strings.

Another way of joining character strings.

Operators +, -, *, /

Calculations using arithemetic operators.

UPPER

Returns a character expression with lowercase character data converted to uppercase.

LOWER

Returns a character expression with uppercase character data converted to lowercase.

GETDATE

Returns current date and time.

CURRENT_TIMESTAMP

Returns current date and time.

DATEPART

Returns year, month, week, day, etc. from the date. In the example refers to a month.

Returns year from a date.

In the example query returns age of an employee using GETDATE and DATEPART.

DATEDIFF

Find the difference in years, months, weeks, days, etc. between two dates.

In the example query returns tenure of an employee using GETDATE and DATEDIFF for employee with tenure over 24 years.

CASE, WHEN, THEN, ELSE, END

Evaluates a list of conditions. Next, returns one of possible result expressions.

ORDER BY

Sorts data in either ascending or descending order. In the example sorted in ascending order by last name.

! No need to type ASC. By default, data is be sorted in ascending order.

In the example data is sorted in descending order by employee's birth of date.

Data sorted by several columns.

Again, data sorted by several columns.

WHERE

The WHERE clause filters for rows that meet certain criteria.

In the example city equals 'London'.

In the example quantity is greater than 110.

In the example quantity is greater or equal to 120.

In the example quantity is smaller or equal to 10.

In the example Title is different than 'Sales Representative'.

BETWEEN

The BETWEEN operator selects values within a given range.

In the example Unit Price value is between 10 and 15.

! BETWEEN is inclusive.

SELECT TOP

Limits selection to a specified number of records.

In the example top 10 rows are returned.

In the example top 5% of rows is returned.

In the example top 10 Unit Price rows are returned and sorted by Unit Price.

AND, OR, NOT

Comparisons with NULL and the three-valued logic (3VL). SQL query can result in True, False or Unknown (NULL). The following table presents possible combinations:

In the example companies starting with the letter "L" and located in France are returned.

For more information about the "Like" operator scroll down.

In the example companies starting with the letter "L" or located in Poland are returned.

In the example companies starting with the letter "L" and located in France or Germany are returned.

In the example companies starting with the letter "L" and not located in France are returned.

IN

IN operator in the WHERE clause is useful to filter data by a list of values. It is similar to multiple OR conditions.

In the example companies operating in Argentina or Mexico are returned.

In the example companies operating in countries other than Argentina or Mexico are returned.

The following SQL statement selects all customers that are from the same countries as the suppliers.

The following SQL statement selects all Suppliers that are from the same countries as the customers.

LIKE

LIKE operator determines if a character string matches a pattern. Wildcard characters are % (percent) and _ (underscore).

A % matches any string with zero or more characters. While an _ matches any single character.

In the example companies starting with the letter "S" are returned.

! It doesn't matter whether the letter is in upper or lower case.

Companies which names end with "ssen".

Customers which name consists of 8 signs (8 x underscore). Including space(s) and signs such as apostrophe.

Customers where second letter in the name is "a".

Customers where first letter in the name is "c" and the last one is "r".

Null Values

A field that contains a NULL value has no value. NULL value is different from a zero value or a field that contains spaces. A field with a NULL value has been left blank when a record has been created.

It is not possible to compare NULL values against operators, such as =, <, or <>. Instead, WHERE IS NULL or WHERE IS NOT NULL are used as shown in examples below.

No results are shown since WHERE IS NULL clause has to be used to return NULL records.

Similarly, no results are shown since WHERE IS NULL clause has to be used to return NULL records.

Error, no records returned.

Returns records where region is not NULL.

In the example region's NULL values are replaced with "Unknown".

SELECT DISTINCT

DISTINCT statement returns only distinct (unique) values.

In the example distinct countries are returned.

In the example distinct customer IDs are returned from the Orders table.

COUNT, AVG, SUM

COUNT function returns a count of the number of data values. SUM function returns the sum of the data values. AVG function returns the average of the data values.

The example shows number of customer IDs from Customers table.

The example shows number of countries from Customers table.

! The query doesn't remove duplicates. Countries are counted multiple times.

The example shows number of countries from Customers table.

! Duplicates are removed thanks to DISTINCT statement. Query counts unique countries.

Average value of discount granted on condition that any discount was granted.

! There is no limit in terms of decimal places.

Average value of discount granted on condition that any discount was granted.

Limited to two decimal places.

Ordered quantity in total.

MIN, MAX

Min and Max functions show the smallest value and the largest value respectively.

In the example the product with the highest price.

In the example the product with the lowest price.

GROUP BY

GROUP BY is used to collect data across multiple records and group the results by one or more columns. The clause typically also involves aggregates, such as COUNT, MAX, SUM, AVG, etc.

In the example grouping by country.

In the example grouping by title and aggregating (counting) by employee ID.

HAVING

HAVING clause filters records that work on summarized GROUP BY results.

In the example Having clause is used as a filter of grouped results.

SQL Joins

A SQL JOIN combines records from two or more tables. Possible joins in the picture below.

INNER JOIN

Returns rows when the joining key between both tables matches.

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table A), and the matched records from the right table (table B). If there is no match, the result is NULL from the right side.

! If we use RIGHT JOIN and switch the order of tables in query the same results will be obtained which will be shown in the next example.

! Similar results were obtained in above INNER JOIN query which means there are records in table A (EmployeeTerritories) which don't match table B (Territories).

Using RIGHT JOIN to obtain the same result as above. Order of tables in query was altered.

Three-table left join example.

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table B), and the matched records from the left table (table A). If there is no match, the result is NULL from the left side.

! If we use LEFT JOIN and switch the order of tables in query the same results will be obtained which will be shown in the next example.

! There are 4 more records than in case of similar LEFT JOIN example. This is because in 4 cases there are no matches which results in NULL values.

Using RIGHT JOIN to obtain the same result as above. Order of tables in query was altered.

Three-table right join example.

SELF JOIN

A self join is used to join a table to itself. It can be useful to compare rows within the same table.

In the example we will query employees and their managers. Null means that the eployee is on the top of the structure and has no boss.

In the example we are looking for customers from the same city, Buenos Aires.

FULL JOIN

Returns all records from both Table A and Table B regardless there are matches or not.

Example shows combination of full and left table joins.

UNION

The UNION operator combines the results of two or more SELECT statements. In order to use UNION columns in tables have to match. Duplicated pairs are removed.

The UNION ALL operator combines the results of two or more SELECT statements, however duplicated pairs are not removed. London, UK appears multiple times

INSERT INTO

Inserts a new record into a table.

UPDATE

Updates a record in a table.

DELETE

Deletes a record from a table.

VIEW

A view is a virtual table that is based on the result-set of an SQL statement. It is stored in the database catalog that allows you to refer to it later.

The example shows creation of a view that includes top 10 products by the ordered quantity.

It is possible to query against a view like against a regular table.

SUBQUERIES SELECT, FROM, WHERE

Subqueries are nested queries that provide data to the enclosing query. A subquery is often called inner query while the statement containing the subquery - outer query. Let's go through a number of examples of subqueries after SELECT, FROM, WHERE statements.

Example of a subquery after SELECT that returns average freight values for all the orders.

Example of a subquery after SELECT that returns average freight values per employee.

Example of a subquery after FROM that returns average of a freight summed up by employee.

Example of a subquery after FROM that returns a client from Poland.

Example of a subquery after FROM that returns clients from Germany.

Example of a subquery after FROM that returns a second from the top highest freight's value.

SUBQUERIES EXISTS, NOT EXISTS

The EXISTS operator allows you to check the existence of any record in a subquery. It returns true or false depending on the result of the operation.

Example of a subquery after EXISTS that returns true if there are customers who placed orders in both July and August 1996.

Example of a subquery after NOT EXISTS that returns true if there are customers who placed orders in August 1996 but not in July 1996.

SUBQUERIES ANY, ALL

If any of the subquery values meet the condition the ANY operator returns true. For the All operator ro rerutn true - all of the subquery values must meet the condition. Both operators are used with a WHERE or HAVING clause.

Example of a subquery after ANY that returns all the customers where freight is above 750.

Example of a subquery after ALL that returns the five recent orders which are done by one shipping company United Package.

INDEX

Following the Microsoft definitions:

"Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order."

"The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap."

"Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value."

"The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key."

To see the benefits from applying a nonclustered index we need a big dataset. Let's download a movie rating dataset that contains over 20 million rows. Link to the dataset on Kaggle.

Next, let's create a database and a table, and let's upload the data using BULK INSERT.

In my case the data upload took around 10 mins.

The results.

At this point the table has no primary key. Let's measure the execution time of the following query.

SQL Server Execution Times:

CPU time = 10328 ms, elapsed time = 2990 ms.

Let's add a column that will hold the primary key for the table. In my case the process took around 30 mins.

The table after adding the column.

Let's measure the execution time of the earlier query.

SQL Server Execution Times:

CPU time = 6423 ms, elapsed time = 1794 ms.

Finally, let's create a nonclustered index.

Let's measure the execution time of the earlier query.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

The differences may not seem significant but imagine the time savings if there are hundreds of millions of rows. Just rememeber that the indexes are memory consuming so use them for queries that occur frequently.

FUNCTION

In SQL Server you can create your own function. They are programs that accept one or more parameter and return a single value.

Example of a creation of a function that returns value of net sales.

Let's use the function in a query to get some results. In this case value of net sales by a product id.

STORED PROCDEDURE

Stored Procedure in SQL Server can be defined as a batch of statements grouped as a logical unit and stored in the database. The stored procedure accepts the parameters and executes the T-SQL statements in the procedure. Returns results if there are any.

Example of a creation of a stored procedure that returns orders per provided client id.

Let's run the procedure for the VINET customer.

or

Something a bit more advanced. Example of a craetion of a stored procedure that accepts three parameters (sales' year, month and a product name), refers to earlier created function Net_Sales_udf, and returns one output parameter (net sales value).

Let's run the procedure by giving all the required parameters.

IF ELSE

The If Else statement allows you to control the flow of program.

Example of a query where we check if product sales increased in comparison to the same month last year. Let's use the stored procedure sp_product_sale and the If Else statement.

Target not achieved

CREATE TABLE

For most shipping carriers, payment options include credit cards, debit cards, electronic funds transfer, letters of credit or cash (accepted in limited circumstances).

Let's create a Payment Method table that contains two columns: PaymentID, PaymentName. The id will increase incrtementally by 1 starting from 1.

Next, let's insert the values.

Finally, let's create a column named PaymentID in the Orders table. Let's define it as a foreign key of the PaymentID column from the Payment Method table.

The exercise continues with the While description in the next chapter.

WHILE

The If Else statement is loop that allows you to control the flow of program.

In the example query loops through all the order ids in the Orders table and assigns random payment id value (from 1 to 5) to each of them.

DROP KEY, COLUMN, TABLE

Drop should be used to remove objects. We are going to remove the foreign key, the column and the table created in the CREATE TABLE and WHILE chgapters. Remember you have to remove constraints before you can are allowed a table.

Check the foreign key's exact name under Keys in order to remove it. If you wish to you can rename it. Drop the foreign key constraint in the PaymentID column in the Orders table:

Drop the PaymentID column in the Orders table.

Drop the Payment Method table.

TRIGGER

DML triggers are executed automatically when a data manipulation language (DML) event takes place. DML events include INSERT, UPDATE, or DELETE statements.

DML trigger statements use two temporary, memory-resident tables: the deleted table and the inserted tables. The tables can be referenced to in order to set conditions for DML trigger actions.

Triggers can be used to store audit information, e.g. what record whas added or deleted from a database.

In the example we will log in a separate table any action of inserting od deleting a record. To start with, let's create a table to store change logs in the Customers table.

The AFTER trigger fires after SQL Server completes the execution of the action successfully that fired it.

Let's insert a new customer to the Customers table.

Action of inserting data runs the trigger. The record was automatically added by the trigger to the CustomersAuditLog table.

Let's remove the new customer from the Customers table. The change will be recorded in the CustomersAuditLog table.

The INSTEAD OF trigger fires before SQL Server starts the execution of the action that fired it.

Let's create a trigger that will block the possibility to delete a client originating from Poland. In case of such attempt there should be a message that such action is not allowed and there should be a row added in the CustomersAuditLog table containing Reject message in the ActionTaken column. If client is from other country than Poland, it can be deleted, but this should be reflected in the CustomersAuditLog table.

The trigger creation.

Let's again insert a new customer to the Customers table. The same as previosuly.

The action of inserting data run again the trg_customer_audit_log trigger. The record was automatically added by the trigger to the CustomersAuditLog table.

Next, let's try to delete the recently inserted client.

In the Messages tab the error will be comunicated.

Customers from Poland cannot be deleted

Let's check the CustomersAuditLog table. Instead of Delete we received Reject in the ActionTaken column.

Notice that the trg_cannot_delete_customer_audit_log trigger had priority over the trg_customer_audit_log trigger. This is because if there are AFTER triggers on the table, they will fire after constraint processing. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

Eventually, let's double check if the 'WAWAC' CustomerID was not removed from the customers table.

If you would like to remove a client from Poland you have to either disable or drop the trigger.