SQL Brush-up
Published: 2022-03-30 09:35:07

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

```sql
CREATE DATABASE mydb;
```
- Using the created database mydb

```sql
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", "myuser@example.com" );
```

- Updating a row in the table

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

- Deleting a row in a table

```sql
DELETE FROM mytable WHERE id=5
```

- Select rows based on conditions.

```sql
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.

```sql
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.

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

```
Output

![sql group by](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4inyi41qm3r6vsjcl1ww.png)
The query returns the number of markets in each zone.
We can use AS to rename column names. This is known as aliasing.

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

![group by](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7g6szei91djc68s0q6h3.png)

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.

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

![Having](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zz1qhb2dh0gm507dfcv3.png)
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: 

```sql
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name
```
Example:

```sql
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:

![inner join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sgsr3pyqd7t9i4cnaesm.png)
- **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

```sql
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:

![left join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ce6qf0s2xlpug5gvd6o4.png)
- **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

```sql
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:

```sql
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.