SQL: Basics to Advanced in a Nutshell
Tags: sql, database, postgres
Published: 2021-03-10 10:51:49

_Cover Photo by [Christina Morillo](https://www.pexels.com/@divinetechygirl?utm_content=attributionCopyText&utm_medium=referral&utm_source=pexels) from [Pexels](https://www.pexels.com/photo/man-standing-infront-of-white-board-1181345/?utm_content=attributionCopyText&utm_medium=referral&utm_source=pexels)._

Databases are the containers/repository of data. They hold the data in the form of a spreadsheet(not the same but for the sake of imagination). There are many databases viz. Relational and Non-Relational. Let us not dwell on the latter part but stick with the relational databases. Watch this video to know more about databases.

{% youtube Tk1t3WKK-ZY %}

This post will focus primarily on writing SQL queries. SQL queries are common across all the databases but there are some flavors and they may vary according to different databases. There are various vendors like IBM, MySQL, PostgreSQL, Oracle, etc. Every vendor has its own style of SQL to communicate with its database. The one I would suggest is [PostgreSQL](https://www.postgresql.org/) which is open source and has got wider adoption.

The primary way to communicate with a relational database is through SQL. Structured Query Language (SQL) is a language to communicate with the database. Without further ado, let us jump into learning SQL.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j8bxhnwu3odr8sge7ue1.png)

_Image Source: [The Data Labs](https://thedatalabs.org/spreadsheet-structure/)_
 
A table looks like the above spreadsheet with rows and columns. Columns are the headers for the category of the values that we input. A column that is defined as a name will have only names in it and a column defined as a currency would certainly have numbers in the entire column. So, a column defines what kind of values it should comprise. Rows are the list of entries made in different columns. Rows define the number of entries in a spreadsheet.

Now, to get all the columns from the customer table, we use the keyword `SELECT` and specify the column names that we are interested in. We can use the `*` symbol to fetch all the columns from the table. To connect to a table, the keyword `FROM` is used which comes after the `SELECT` keyword.

```sql 
SELECT * FROM customer;
```
To get specific columns from the customer table.

```sql 
SELECT first_name, last_name FROM customer;
```
In spreadsheets, we would have used filters similar to that in SQL there are filters to query. For instance, if you want to look only for emails that have the first name as 'Sai', the keyword that should be used is `WHERE`. `WHERE` should be used after the `FROM` command.

```sql 
SELECT email FROM customer WHERE first_name='Sai';
```
To get all the emails of the customer whose first name is 'Sai' and the last name is 'Kumar', we use a keyword called `AND` which is a logical operator similar to `OR`, and `NOT`. You can learn more about [logical operators](https://www.w3resource.com/sql/boolean-operator/sql-boolean-operators.php) in SQL.

```sql 
SELECT email FROM customer WHERE first_name='Sai' AND last_name='Kumar';
```

To get all the emails of the customer whose first name is 'Sai' or the last name is 'Kumar'.

```sql 
SELECT email FROM customer WHERE first_name='Sai' OR last_name='Kumar';
```
To arrange the data in ascending or descending order use the `ORDER BY` keyword followed by the column name based on which the entire table would be ordered. To arrange is ascending use `ASC` and for descending `DESC`. By default if you don't specify the order, it will sort the column in ascending order. `ORDER BY` always comes at the tail end of the query.

To get the list of customer ids and arrange them in ascending order of their transaction date.

```sql 
SELECT customer_id FROM customer ORDER BY transaction_date ASC;
```

To get the list of customer ids and arrange them in descending order of their transaction date.

```sql 
SELECT customer_id FROM customer ORDER BY transaction_date DESC;
```
We can also limit the number of records displayed by using `LIMIT` with a positive integer number to specify the limit.

To get the list of the first 10 customer ids.

```sql 
SELECT customer_id FROM customer ORDER BY transaction_date ASC LIMIT 10;
```

`COUNT` is a type of function that is used in columns and it should come after the `SELECT`. `COUNT` returns the number of data in the specified column. In general, passing `*` as an argument to the `COUNT` will also return the same result as the column name.

To get the count of some customers whose age is not more than 50.

```sql 
SELECT COUNT(*) FROM customer WHERE age<=50;
```
The use of regular expressions is ubiquitous in all programming languages similarly we use regex in SQL to match the strings. `LIKE` is used in conjunction with `WHERE` and followed by the regex. 
* `%String` - grabs all the words that end with `String`
* `String%` - grabs all the words that start with `String` 
* `%String%` - grabs all the words that comprise
 the `String` in between them
Note: `LIKE` is case sensitive and looks only for the string that is mentioned. `ILIKE` is an alternative that is case insensitive and can match across various cases of the string.

To get all the names of the customer whose first letter in their first name starts with 'P'

```sql 
SELECT first_name FROM customer WHERE first_name LIKE 'P%';
```

To get all the customers whose last name has 'siva' in between and should be case insensitive.
```sql 
SELECT * FROM customer WHERE last_name ILIKE '%siva%';
```

At times the entries in the table might have duplicate data. To filter out unique data, `DISTINCT` is used. `DISTINCT` applies only to the column and comes next to `SELECT` and even as an argument to the `COUNT` function.

To get the count of distinct areas where the customers are from.
```sql 
SELECT COUNT(DISTINCT area) FROM customer;
```
To get distinct areas where the customers are from.
```sql 
SELECT DISTINCT area FROM customer;
```
`IN` is used to specify the list of items against which a column is compared. For example, we can check if a column consists of (Orange, Mango, Banana) and the function filters and display only the entries with orange, mango, and banana.

To get the count of customers who are located in the area with Pincode '82' between the age group 60 to 70.

```sql 
SELECT COUNT(*) FROM customer WHERE pincode IN(82) and age BETWEEN 60 AND 70;
```

### AGGREGATION

Aggregation means a cluster of things that are brought together. Likewise aggregation in SQL clusters the data into a homogeneous category and groups it. For example,

To get the total number of transactions made by all customers.

```sql 
SELECT customer_id, COUNT(transaction_date) FROM customer GROUP BY customer_id;
```
Here `COUNT` is the aggregation function that has been performed on the transaction_date column concerning the customer_id. So a group is formed for every customer_id where you will get a matching count of the number of transactions. A normal `COUNT` would have resulted in the total number of transactions whereas in aggregation with customer_id by using `GROUP BY`, it produces a table with individual customer ids and their corresponding count of transactions.

`GROUP BY` should always have the column names that are mentioned in the `SELECT` criteria except for the aggregation function.

To get an average amount that a customer has spent on purchases.
```sql 
SELECT customer_id, AVG(amount) from customer GROUP BY customer_id;
```
To get a rounded value of the average calculated.
```sql 
SELECT customer_id, round(AVG(amount),2) from customer GROUP BY customer_id;
```
To arrange the SUM(amount) in descending order and display only 10 rows.
```sql 
SELECT customer_id, SUM(amount) FROM customer GROUP BY customer_id ORDER BY SUM(amount) DESC LIMIT 10;
```
It is not possible to use the `WHERE` condition on the aggregate function and that is where we use `HAVING` after the `GROUP BY` and specify the condition.

To display the customers who have spent more than 100 INR.
```sql 
SELECT customer_id, COUNT(amount) FROM customer GROUP BY customer_id HAVING COUNT(amount)>=100;
```
To display the customers who were attended by the staff with id '2' and their total spending is more than 1000 INR.
```sql 
SELECT customer_id, staff_id, SUM(amount) FROM customer WHERE staff_id=2  GROUP BY customer_id, staff_id HAVING SUM(amount)>1000;
```
### JOINS 

`JOINS` play an important role while analyzing data by combining multiple tables. There are four types of `JOINS`
* INNER JOIN
* LEFT OUTER JOIN
* RIGHT OUTER JOIN
* FULL OUTER JOIN

`INNER JOIN` - Compares between two tables on the specified columns and returns the data that matches in both the columns.

`LEFT OUTER JOIN` - Compares between two tables on the specified columns and returns the data that matches in the left table. If no value is present in the right table matching the left, then it returns the data as `null`.

`RIGHT OUTER JOIN` - Compares between two tables on the specified columns and returns the data that matches in the right table. If no value is present in the left table matching the right, then it returns the data as `null`.

`FULL OUTER JOIN` - Compares between two tables on the specified columns and returns the data that matches in both the columns along with mismatches with a value as `null`.

You can read more about JOINS [here](https://www.diffen.com/difference/Inner_Join_vs_Outer_Join)

`INNER JOIN` to merge two tables according to matching values of customer_id.
```sql 
SELECT payment_id, customer.customer_id, first_name, last_name FROM customer INNER JOIN payment ON customer.customer_id = payment.customer_id;
```
`FULL OUTER JOIN` to merge two tables according to matching values of customer_id.
```sql
SELECT payment_id, customer.customer_id, first_name, last_name FROM customer FULL OUTER JOIN payment ON customer.customer_id = payment.customer_id WHERE payment.customer_id IS null OR customer.customer_id IS null;
```
`LEFT OUTER JOIN` to merge two tables according to matching values of customer_id based on the left table.
```sql 
SELECT payment_id, customer.customer_id FROM payment LEFT OUTER JOIN customer on payment.customer_id = customer.customer_id WHERE payment.customer_id IS NULL;
```
`RIGHT OUTER JOIN` to merge two tables according to matching values of customer_id based on the right table.
```sql 
SELECT payment.customer_id,customer_id FROM customer RIGHT OUTER JOIN payment on customer.customer_id = payment.customer_id WHERE payment.customer_id IS NULL;
```
You can also use nested joins to query from multiple tables.
```sql 
SELECT customer_id, first_name, last_name FROM customer INNER JOIN payment ON customer.customer_id = payment.customer_id INNER JOIN billing ON payment.customer_id = billing.customer_id WHERE first_name='Sai' AND last_name='Kumar';
```
## Other SQL Commands
To display the timezone of your current location.
```sql 
SHOW TIMEZONE;
```
To get today's timestamp.
```sql 
SELECT NOW();
```
To get the current time of the day.
```sql 
SELECT TIMEOFDAY();
```
To extract month, day, year from the timestamp use the function `EXTRACT` which will return a numeric month, date, or year.
```sql 
SELECT EXTRACT(MONTH FROM transaction_date) FROM customer;
```
To convert a timestamp into a specific string format like 'DD/MM/YYYY', the TO_CHAR function is used where you specify the timestamp column name as the first argument and followed by the format to be returned.
```sql 
SELECT DISTINCT (TO_CHAR(payment_date,'DD/MM/YYYY')) FROM payment;
```
A sub query returns either a single data or a list of data which can be asserted with its parent query.
-- SUB QUERY
```sql 
SELECT first_name || ' ' || last_name AS Full_Name FROM customer WHERE address_id IN (SELECT address_id FROM address WHERE district = 'Chennai');
```
Similar to conditionals in programming languages, SQL has a `CASE` statement that runs a check against the selected column and performs specified operations on it.
```sql 
SELECT 
SUM(CASE citizen
	WHEN 'INDIAN' THEN 1
	ELSE 0
	END)as IN,
SUM(CASE rating
	WHEN 'NRI' THEN 1
	ELSE 0
	END)as NRI
FROM customer;
```
That will be all and there are few more concepts like views, procedures etc. which is much more advanced and I will cover those is my future posts.