SQL Brush-up

SQL refers to Structured Query Language.
It is primarily used in Relational Database Management Systems including MS Access, Oracle, Postgres, MySQL and SQL Server.
We will be using MySQL.

SQL Commands

• Creating a Database

CREATE DATABASE mydb;

• Using the created database mydb

USE mydb;

• Creating a table ```sql

CREATE TABLE mytable
(
id int unsigned NOT NULL auto_increment,
username varchar(100) NOT NULL,
email varchar(100) NOT NULL,
PRIMARY KEY (id)
);

- Inserting into table

```sql


INSERT INTO mytable ( username, email )
VALUES ( "myuser", "[e-mail]" );

• Updating a row in the table

UPDATE mytable SET username="myuser" WHERE id=5

• Deleting a row in a table

DELETE FROM mytable WHERE id=5

• Select rows based on conditions.

SELECT * FROM mytable WHERE username = "myuser";

• returns all the columns in the table.

The power of SQL lies in its ability to use nested complex queries to provide the desired output.
Let us look at some commands that make SQL queries more robust.
AND,OR,NOT

These commands work similarly to how they are used as logical operators in programming languages.
They are combined with the WHERE clause to specify the conditions that must be met.

• AND returns only the records that meet all the conditions.
• OR returns a record if any of the conditions in the query are met.
• NOT displays a record if the conditions in the query are NOT TRUE.

IN

IN returns the records based on the values supplied.
It is used together with the WHERE clause and acts as a shorthand for multiple OR conditions.
We can also use **NOT IN **to get records that are not in the specified values supplied to the clause.

BETWEEN
BETWEEN is useful when extracting records in a given range. For instance between dates or prices.

Note: BETWEEN is inclusive; it includes the beginning and end values.

It is used with the WHERE clause and can be combined with AND,OR, NOT to specify results further.

EXISTS
TRUE if the subquery returns one or more records that satisfy the condition.

LIKE

It returns TRUE if the operand matches the specified pattern.

ORDER BY

This command is used to define the order in which the results of a query should be displayed.

The default is ascending order thus to display in descending order we specify using the DESC keyword.

Aggregate Functions

Aggregate functions help us retrieve data by performing different operations on columns.

These functions are AVG,COUNT,MIN,MAX, SUM

• AVG returns the average of the selected values.
• COUNT counts how many rows meet the specified conditions
• MIN returns the lowest value in a particular column.
• MAX returns the highest value in a column.
• SUM adds all the values in a particular column. Here's and example of how we can use COUNT,BETWEEN and AND with WHERE clause.

SELECT COUNT(*) FROM sales.transactions WHERE order_date BETWEEN '2017-01-01' AND '2017-12-31';

This returns the number of transactions that were recorded between January and December 2017 from the transactions table.

GROUP BY

The command is used to group rows that have the same values such that the query returns a single row for every grouped item.

GROUP BY can be used with aggregate functions as shown below.

SELECT COUNT(zone),zone  FROM sales.markets
GROUP BY(zone)

Output

The query returns the number of markets in each zone.
We can use AS to rename column names. This is known as aliasing.

SELECT COUNT(zone) AS market_nums,zone  FROM sales.markets
GROUP BY(zone)

GROUP BY is also used with the HAVING clause which filters the record from the groups based on the specified condition.

The HAVING clause is used because the WHERE keyword cannot be used with aggregate functions.

SELECT COUNT(zone) AS market_nums,zone  FROM sales.markets
GROUP BY(zone)
HAVING(count(zone)>3)

Output:

The query returns zones with more than 3 markets.

SQL JOINS

JOINs horizontally combine results from different tables.

A JOIN is used to combine rows from two or more tables, based on a related column between them.

There are several types of joins.

• INNER JOIN: Returns records that have matching values in both tables. Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name

Example:

SELECT  markets.markets_name, transactions.product_code,transactions.sales_qty
FROM sales.markets
INNER JOIN sales.transactions
ON markets.market_code = sales.transactions.market_code

Output:

• LEFT JOIN: Returns all records from the left table, and the matched records from the right table.

The "left" refers to the table that appears before the JOIN in the query and the "Right" refers to the table that is after the JOIN.

Example

SELECT  markets.markets_name, transactions.product_code,transactions.sales_qty
FROM sales.markets 
LEFT JOIN sales.transactions
ON markets.market_code = sales.transactions.market_code

Output:

• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
• FULL JOIN: Returns all records when there is a match in either left or right table. Any row that does not have a match in both tables will have NULL entries for the missing values.

SQL UNIONS

SQL UNION is used to vertically concatenate columns.
It combines the results of two or more SELECT statements.

• The columns must also have similar data types.
• Every SELECT statement within UNION must have the same number of columns
• The columns in every SELECT statement must be in the same order.

UNION Syntax

SELECT *column_name(s)* 
FROM *table1*
UNION
SELECT *column_name(s)* FROM *table2*;

The UNION operator selects only distinct values by default.

We use UNION ALL to include duplicate values:

SELECT *column_name(s)* FROM *table1*
UNION ALL
SELECT *column_name(s)* FROM *table2*;

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.

Use UNION DISTINCT to drop duplicate values.