Basic SQL Concepts
Tags: sql, postgres, database
Published: 2021-08-25 06:09:20

## Pre-requisites
### 1. Install PostgreSQL and psql-CLI to your system

If you haven't installed PostgreSQL and psql-CLI in your system, follow the steps in this [link](https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart).

### 2. Create a psql role

I have created a role named `fahid` in the psql. Follow the instructions in the same [link](https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart) to create a role (preferably a role with your computer-name).
### 3. Access the Postgre prompt

Use either of these commands to access Postgre Prompt. Replace `fahid` with `your_username`.

```shell
psql
```

OR

```shell
psql -U fahid
```

Before we start, create a database `sample` in the psql shell. 
### 4. Create an empty database `sample`

To create an empty database, run the following command in the postgre prompt. (Note: Do not miss the `;`)

```sql
CREATE DATABASE sample;
```

To confirm whether the database `sample` is created, list the databases using the following command in the postgre shell.

```shell
\list
```

You should be able to see the database `ipl` in the list of databases with your username as the owner.

### 5. Connect to `sample` database
To connect to the database, run
```shell
\c sample
```

### 6. Copy the contents to the database
Make sure you download [`sample.sql`](https://gitlab.com/FahidLatheefA/sql_concepts/-/blob/main/sample.sql) to your computer.
Now to copy/import the contents to the database, run
```shell
\i /path/to/the/file/sample.sql

```

### 6. Display the tables in the database
Run this to display the tables in the database.
```shell
\dt
```
You will be able to see all available tables in the `sample` database.

## SELECT STATEMENT
The most basic statement is SELECT statement. It allows us to **retreive records** from the table.

- To retreive all the columns:

```sql
SELECT * FROM customer;
```
- To specifically select one or more columns:
```sql
SELECT cust_code, cust_name, cust_city, working_area FROM customer;
```
### WHERE  CONDITION
To have a condition in your SELECT statement, we can use WHERE condition.
```sql
SELECT cust_code, cust_name, cust_city FROM customer
WHERE cust_city = 'London';
```
We will get the following output.
```output
 cust_code | cust_name | cust_city 
-----------+-----------+-----------
 C00013    | Holmes    | London
 C00024    | Cook      | London
 C00015    | Stuart    | London
 C00023    | Karl      | London
```
### AND and OR operators
We can add additional conditions and filter the table using `AND` or `OR` operators.

```sql
SELECT cust_code, cust_name, cust_city FROM customer
WHERE cust_city = 'London' OR cust_city = 'New York';
```
>Selecting Grade 1 customers in India
```sql
SELECT cust_code, cust_name, cust_city, grade FROM customer
WHERE cust_country = 'India' AND grade = 1;
```
###  ORDER BY

To order the table by a specific column(s), we can use ORDER BY clause.
```sql
SELECT cust_code, cust_name, cust_city, payment_amt
FROM customer
ORDER BY payment_amt;
```
To arrange the result in descending order, we can use `DESC`  keyword.
```sql
SELECT cust_code, cust_name, cust_city, payment_amt
FROM customer
ORDER BY payment_amt DESC;
```

###  DISTINCT
To remove duplicates and select the distinct elements use DISTINCT keyword.

```sql
SELECT DISTINCT cust_city
FROM customer;
```

### BETWEEN

To select data within a range, we can use BETWEEN with AND.
```sql
SELECT cust_code, cust_name, payment_amt
FROM customer
WHERE payment_amt BETWEEN 6000 AND 8000;
```

### LIKE
To match one or more characters, LIKE is used.
`_` is used to match exactly one character and `%` is used to match zero or more characters.
> Customers starting with the name 'Ra'
```sql
SELECT cust_code, cust_name, cust_city
FROM customer
WHERE cust_name LIKE 'Ra%';
```

### COUNT
To count number of results, we can use COUNT
```sql
SELECT COUNT(cust_name)
FROM customer
WHERE cust_name LIKE 'Ra%';
```
### OTHER AGGREGATE FUNCTIONS
 
|  KEYWORD| USE |
|--|--|
|  SUM|To calculate the sum  |
|  AVG|To calculate the average  |
|  MIN|To find the minimum of the column  |
|  MAX|To find the maximum of the column  |



### AS
We can use AS as alias in SQL
```sql
SELECT COUNT(cust_name) AS total
FROM customer
WHERE cust_name LIKE 'Ra%';
```

## GROUP BY

GROUP BY is used to group rows that have the same values into summary rows

```sql
SELECT DISTINCT cust_country, COUNT(*) AS count
FROM customer
GROUP BY cust_country;
```
### HAVING
>To  select countries having count greater than 4

```sql
SELECT DISTINCT cust_country, COUNT(*) AS count
FROM customer
GROUP BY cust_country
HAVING COUNT(*) > 4;
```

## JOIN
A `JOIN` clause is used to combine rows from two or more tables.
`ON` keyword is used to specify the coommon columns


![SQL JOINS](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/21txypr85fhohordbnjd.png)


## SUBQUERIES
We can use a query inside a query to further restrict the output data
```sql
SELECT * FROM customer 
WHERE cust_code IN 
(SELECT cust_code 
FROM customer 
WHERE payment_amt <  6000);
```

## DELETE
To delete complete table
```sql
DELETE FROM customer;
```

We may also use other conditions to specifically delete rows.

```sql
DELETE FROM customer
WHERE cust_name = 'Steven';
```
## DROP
To completely drop the table, we may use
```sql
DELETE TABLE customer;
```

## Conclusion

In the next [Episode](https://dev.to/fahidlatheef/databases-important-theoretical-concepts-4nll) of this Series, we will discuss few Database Theoretical Concepts.