SQL for Beginners
Tags: sql, database, beginners
Published: 2021-01-14 16:37:31

#Understaing SQL Syntax

`SELECT` - retrieves one or more rows from one or more tables
`INSERT` - Adds one or more rows into a table
`UPDATE` - Modifies one or more rows in a table
`DELETE` - Removes one or more rows from one table

Examples:
```
SELECT first_name FROM person; 
INSERT INTO contacts (first_name, last_name) VALUES ('Bubble', 'Gum');
UPDATE contacts SET last_name = 'Pop' WHERE id=1;
DELETE FROM contacts WHERE id=2;
```
(Don't forget the semi colon)

#Querying Data with the SELECT Statement

```
SELECT <column_name> FROM <table_name>;
```
```
SELECT * FROM <table_name>;
``` 
This gives all the result!
You can also qualify column name with table name. This is helpful with multi-table queries and is a good practice! For example:
`SELECT person.first_name FROM person;`
To shorten your work, you can alias the table name. For example:
`SELECT p.first_name as Name FROM person p;` 
Using `DISTINCT` to constrain the result set:
`SELECT DISTINCT p.first_name FROM person p;`
This will give all the unique set of results with no repetition.

#Filtering Results with WHERE
```
SELECT p.last_name 
FROM person p;
WHERE p.first_name = 'Candy';
```

You can use Boolean Operators for complex queries. For example: 
Using `AND` operator
```
SELECT p.first_name 
FROM person p 
WHERE p.first_name = 'Candy'
AND p.age > 25;
```

Using `OR` operator
```
SELECT p.first_name
FROM person p
WHERE p.first_name = 'Candy'
OR p.last_name = 'Pink';
```

Using `BETWEEN` operator
```
SELECT p.first_name 
FROM person p
WHERE p.age
BETWEEN 20 AND 35;
```

Using `LIKE` operator: A fuzzy equal sign
```
SELECT p.first_name
FROM person p
WHERE p.first_name
LIKE 'J%';
``` 
So names beginning with J. `%a%` would give all names that contain a.

Using `IN` operator: More than 1 equal sign
```
SELECT p.last_name
FROM person p
WHERE p.first_name
IN ('Bubble', 'Candy');
```

Using `IS` operator: Equal operator for values that are null
```
SELECT p.first_name
FROM person p
WHERE p.last_name 
IS NULL;
```

Using `IS NOT` operator: 
```
SELECT p.first_name
FROM person p
WHERE p.last_name
IS NOT NULL;
```

#Shaping Results with ORDER BY and GROUP BY

```
SELECT p.last_name, p.first_name
FROM person p
ORDER BY p.last_name;
``` 
This gives you the results in alphabetical order in the last name.

Set Functions:

Function|Definition|
--------|:-----|
COUNT|Count of the column specified (includes NULL values if * is used|
MAX|Maximum value of the column (does not include NULL values)|
MIN|Minimum value of the column (does not include NULL values)|
AVG|Average of all values of the column (does not include NULL values, only numeric column)|
SUM|Sum of all values of the column (does not include NULL values, only numeric column)|

```
SELECT COUNT (p.first_name)
FROM person p
WHERE p.first_name = 'Candy';
``` 
This gives you the total number of names that are Cindy

```
SELECT MAX(p.age)
FROM person p;
```
This gives you the oldest age in the contact list

```
SELECT MIN(p.age)
FROM person p;
``` 
This gives you the youngest age in the contact list

```
SELECT AVG(p.age)
FROM person p;
```
This gives you the average age

```
SELECT 
SUM (p.age)
FROM person p;
``` 
This gives you the total sum of age

You can also add qualifiers in your set function. For example:
```
SELECT 
COUNT (DISTINCT p.first_name)
FROM person p;
```

Using `GROUP BY`
```SELECT 
COUNT (p.first_name), p.first_name
FROM person p
GROUP BY p.first_name;
```
This will give you a table with a column for count and a column for first name.

Using `HAVING`
```
SELECT 
COUNT(DISTINCT p.first_name), p.first_name
FROM person p
GROUP BY p.first_name
HAVING p.first_name = 'Candy';
```

```
SELECT 
COUNT(DISTINCT p.first_name), p.first_name
FROM person p
GROUP BY p.first_name
HAVING COUNT(p.first_name) > 1;
```

#Merging multiple tables using JOINs 

Cross Join: Simple
```
SELECT p.first_name, e.email
FROM person p, email e;
```

Inner Join: Doesn't deal with NULL values
```
SELECT p.first_name, e.email
FROM person p
INNER JOIN email e
ON
p.id = e.email_id;
```

first_name|email|
--------|:-----|
Candy|candyland@gmail.com|
Bubble|bubblegum@gmail.com|
Rainbow|rainbow@hotmail.co.uk|

Outer Join: Deals with NULL values

Left Outer Join: Deals with NULL values- All rows from left is returned even if right contains NULL
```
SELECT p.first_name, e.email
FROM person p
LEFT OUTER JOIN email e
ON
p.id = e.email_id;
```

first_name|email|
--------|:-----|
Candy|candyland@gmail.com|
Bubble|bubblegum@gmail.com|
Rainbow|rainbow@hotmail.co.uk|
Sparkle|NULL|

Right Outer Join: Deals with NULL values- All rows from right is returned even if left contains NULL
```
SELECT p.first_name, e.email
FROM person p
RIGHT OUTER JOIN email e
ON
p.id = e.email_id;
```

first_name|email|
--------|:-----|
Candy|candyland@gmail.com|
Bubble|bubblegum@gmail.com|
Rainbow|rainbow@hotmail.co.uk|
NULL|moonlight@gmail.com

Full Outer Join: Gives all NULL values on left and right columns.

first_name|email|
--------|:-----|
Candy|candyland@gmail.com|
Bubble|bubblegum@gmail.com|
Rainbow|rainbow@hotmail.co.uk|
Sparkle|NULL|
NULL|moonlight@gmail.com

If full outer join doesn't work in your SQL, use left and right outer join together with the addition of `UNION DISTINCT`, like this:
```
SELECT p.first_name, e.email
FROM person p
RIGHT OUTER JOIN email e
ON
p.id = e.email_id;
UNION DISTINCT
SELECT p.first_name, e.email
FROM person p
LEFT OUTER JOIN email e
ON
p.id = e.email_id;
```

#Add, Update and Remove Data

To add- Make sure the values match the list on:
```
INSERT INTO person (id, first_name, last_name)
VALUES(1, 'Fairy', 'Tale');
```
In this case, person is the table.

For Bulk insert:
```
INSERT INTO person p
SELECT * FROM old_person op
WHERE op.id > 300;
```

To update: 
```
UPDATE person (the name of the table is person)
SET person.first_name ='fairy' (new value)
WHERE person.id = 5;
```

To delete:
```
DELETE FROM person p 
WHERE p.id = 5;
```

#Create Database Tables

To create a database and a table:

```
CREATE DATABASE Netflix;
```
This creates a database. Now, to use the database:
```
USE DATABASE Netflix;
```

Next, to create a table:
```
CREATE TABLE tvshows 
(
id INTEGER,
name VARCHAR(1000),
seasons INTEGER
);
```
This creates a table called tvshows with id, name and seasons column.

NULL Values: Using `NOT NULL` means it's required:
```
CREATE TABLE tvshows 
(
id INTEGER NOT NULL,
name VARCHAR(1000) NOT NULL,
seasons INTEGER
);
```

Primary Keys: unique value per row, NOT NULL
```
CREATE TABLE tvshows 
(
id INTEGER PRIMARY KEY,
name VARCHAR(1000) NOT NULL,
seasons INTEGER
);
```

Drop Table:
```
DROP TABLE tvshows;
```