SQL for Beginners

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

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	[e-mail]
Bubble	[e-mail]
Rainbow	[e-mail]

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	[e-mail]
Bubble	[e-mail]
Rainbow	[e-mail]
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	[e-mail]
Bubble	[e-mail]
Rainbow	[e-mail]
NULL	[e-mail]

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

first_name	email
Candy	[e-mail]
Bubble	[e-mail]
Rainbow	[e-mail]
Sparkle	NULL
NULL	[e-mail]

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;