Learning SQL Part 2

Here are more notes from my SQL course on youtube.

I’m going to disect the below query:

SELECT *
FROM student
ORDER BY student_id DESC
LIMIT 2;

In this query, we are selecting everything in the student table. We are ordering it by student id in descending order, and we are asking for only 2 rows.

When you use the SELECT keyword we are getting data returned to us from the database

Filtering

Can use the WHERE keyword again to filter our data being returned from the database, like below:

SELECT *
FROM student
WHERE major = 'Biology' OR major = 'Chemistry';

Note that you can also use comparison operators in your SQL queries:

<, >, <=, >=, =, <>, AND, OR

Where <> in this case means “not equal to”.

The IN keyword is used here:

SELECT *
FROM student
WHERE name IN ('Claire', 'Kate', 'Mike');

This statement is basically saying “where the name is one of these values” that we have in the parentheses.

Adding a foreign key

When creating the employee table for a new company database, we couldn’t make any foreign keys since the table didn’t exist yet. Now we are going back to add the foreign keys:

/*just including this query for reference*/
CREATE TABLE employee(
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    birth_day DATE,
    sex VARCHAR(1),
    salary INT,
    super_id INT,
    branch_id INT
);


ALTER TABLE employee
ADD FOREIGN kEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

In the above query, we are altering the employee table. We are adding a foreign key to branch_id. It is referencing the branch table (which hasn’t been created at this point)

AS keyword

To get data from your database but under a different column name, do this. In this example we want to get first name, but we want it to be called forename, and last name, but we want it to be called surname. We use the AS keyword.

SELECT first_name AS forename, last_name AS surname
FROM employee;

DISTINCT keyword

If we wanted to find all the different genders that employees are stored as,

SELECT DISTINCT sex
FROM employee;

In this case it just returns M and F.

SQL functions

There are functions in SQL as well.

COUNT: returns the count of something - we could count the number of employees in our company by using:

SELECT COUNT(emp_id) FROM employee;

AVG: find the average of something, like:

SELECT AVG(salary) FROM employee;

SUM: finds the sum

Wildcards

LIKE: put an expression inside, defining a pattern that matches whatever you’re searching in

Special characters: % means “any number of characters”, _ means “one character”

Use the special characters to define patterns.

The below query is saying “return any client name that has any number of characters and then LLC”

SELECT * 
FROM client 
WHERE client_name LIKE '%LLC';

To get all birthdays in October, use _ four times to give any four digit year and include 10 for the month of October.

SELECT *
FROM employee 
WHERE birth_day LIKE '____-10%';

UNION

Use UNION to combine results of multiple SELECT statements into one result. Everything will be returned in one table.

Here we are grabbing all names and branches. Both need to be the same data type and same number of columns.

SELECT first_name
FROM employee
UNION
SELECT branch_name
FROM branch;

JOIN

JOIN is used to combine rows from two or more tables. There are four different types of JOIN. The below is an “inner” join.

SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id;

Here when we say JOIN it means join the employee table and the branch table together into one table, on a specific column that the tables have in common. Combine them when the branch manager id is equal to the employee id.

LEFT JOIN: we include all rows from the “left” table, or the table we are selecting “from”. A left join would look like…

SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
LEFT JOIN branch
ON employee.emp_id = branch.mgr_id;

…and it would return all the rows from the employee table, where we are selecting from.

RIGHT JOIN: does the opposite of above, it would include all the rows from the branch table no matter what.

Full outer join: can’t do it in MySQL, but it is a left and right join combined.

What happens when data is deleted?

ON DELETE SET NULL: if data is deleted in one table, set another related value to null

In our CREATE TABLE code…

CREATE TABLE branch(
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(40),
    mgr_id INT,
    mgr_start_date DATE,
    FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

…we are saying “if the branch manager employee id is deleted, set the manager id in this table to NULL”. So if we go to the employee table and delete a branch manager, their id in the branch table will be set to NULL. We’re able to do this because it is a foreign key and not totally essential to the table.

ON DELETE CASCADE: if data is deleted, delete the whole row

CREATE TABLE branch_supplier(
    branch_id INT,
    supplier_name VARCHAR(40),
    supply_type VARCHAR(40),
    PRIMARY KEY(branch_id, supplier_name),
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);

In our example, there are suppliers that supply product to different branches of the company. In this situation, if we deleted a branch, all rows in the branch_supplier table associated with the branch would be deleted.

This is important for primary keys, because a primary key cannot be null.