Create Tables

Any command in SQL is ended with a semi-colon.

SQL code of this:

CREATE TABLE student (

student_id INT PRIMARY KEY,
name VARCHAR(20),
major VARCHAR(20)
);

Or you can define primary key down below too like this:

CREATE TABLE student (

student_id INT,
name VARCHAR(20),
major VARCHAR (20),
PRIMARY KEY(student_id)
);

You can describe the table you have created and see the content in it. By:

DESCRIBE student;

Output:
Delete table by:

DROP TABLE student;

Using this you can delete the table you have created!

Modify table by:

Let's say after the table has created, you wanna add a column.

ALTER TABLE student ADD gpa DECIMAL (3, 2);

As you can see there is a extra column added of gpa and its decimal (3,2), which basically means 3 are total digits in which 2 digits are
after decimal!

You can aslo use it to delete like:

ALTER TABLE student DROP COLUMN gpa;

This will remove the table gpa!

Add Values to the table:

 INSERT INTO student VALUES(1, 'Jack', 'Biology');

Using this you add the value on your table and to check the value content in the table you can type:

SELECT * FROM student;

This will tell you whatever the content is there in the table right now!

Sometimes, you dont have the value to be entered in a specific column of the table. In that case, we can write:

INSERT INTO student(student_id, name) VALUES (3, 'Claire');

SELECT *FROM student;

and in output, the value you havent mentioned, will be types as NULL

A full based table:

SELECT * FROM student;

INSERT INTO student VALUES (1, 'Jack', 'Biology');

INSERT INTO student VALUES (2, 'Kate', 'Sociology');
INSERT INTO student(student_id, name) VALUES (3, 'Claire');
INSERT INTO student VALUES (4, 'Jack', 'Biology');
INSERT INTO student VALUES (5, 'Mike', 'Computer Science')

Output:
Constraints in SQL:

SQL constraints are used to specify rules for data in a table.

(1) NOT NULL

NOT NULL will basically allow us to define that a particular column in the table cannot be NULL.

Let' say that when we are storing our students, we dont want the student to have a NULL name, WE WANT IT TO HAVE A NAME, NO
MATTER WHAT. And NULL is basically a value that represent no value. You can write:

CREATE TABLE student (

student_id INT,
name VARCHAR (20) NOT NULL,
major VARCHAR ( 20),
PRIMARY KEY(student_id)
);

By typing NOT NULL with your specific row, you can make it mandatory to be typed like try excecuting this:

INSERT INTO student VALUES (3, NULL, "Chemistry");

Output:

(2) UNIQUE

Sometimes, you want the entry to be unique in the database for everyone. Like Username in Gmail. You can type:
 CREATE TABLE student (
student_id INT,
name VARCHAR ( 20),
major VARCHAR (20) UNIQUE,
PRIMARY KEY(student_id)
);

By typing UNIQUE in major column, now this row values have to be unique means they cant repeat. So if you try excecuting it twice, you
would get:

INSERT INTO student VALUES (1, 'Jack', 'Biology');

INSERT INTO student VALUES(2, 'Kate', 'Sociology');
INSERT INTO student VALUES (3, NULL, "Chemistry");
INSERT INTO student VALUES (4, 'Jack', 'Biology');

OUTPUT:

Also you can use primary key too for this:

PRIMARY KEY = NOT NULL + UNIQUE

(3) DEFAULT 'undecided'

Sometimes, if the person dont provide us with a specific column which isnt necessary, we can give it a default value.

Like if somebody doesnt provide us with a major, why dont we jusr say that they are UNDECIDED. We can write it like this:

CREATE TABLE student (

student_id INT,
name VARCHAR (20),
major VARCHAR (20) DEFAULT 'undecided',
PRIMARY KEY(student_id)
);

You can try this out with running this:

INSERT INTO student (student_id, name) VALUES(1, 'Jack');

Ouput:
(4) AUTO_INCREMENT

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the
primary key field that we would like to be created automatically every time a new record is inserted. Try this:

CREATE TABLE student (

student_id INT AUTO_INCREMENT,
name VARCHAR (20),
major VARCHAR (20),
PRIMARY KEY(student_id)
);

Output:

Update & Delete Rows

(1) Update

Lets say I am the database administrator for my school. And we decided that instead of calling the major biology, we wanted to call it a
bio. So, official name for major is no longer biology, its bio. We can change in database by:

UPDATE student
SET major = 'Bio'
WHERE major ! 'Biology';

In here, if we dont write the WHERE, all the major value will change to Bio, so its necessary to mention WHERE. This now is one SQL
statement or query.

Ouput:
Other comparision ops:
= : equals
<> : not equals
> : greater than
< : less than
>= : greater than or equal
<= : less than or equal

You can use this with any column and row value.

UPDATE student
SET major = 'Comp Sci'
WHERE student_id 4;

Ouput:
We can also use OR logic if we want to change value of two other values into one. Like this:

UPDATE student
SET major = 'Biochemistry'
WHERE major = 'Bio' OR major = "Chemistry';

Some other things you can do:

UPDATE student
SET major = 'undecided';

UPDATE student
SET name = 'Tom', major - 'undecided'
WHERE student_id = 1;

(2) Delete Rows

DELETE FROM student

WHERE student_id = 5;

Output:
Just like update you can use different and different opertors and ways here through like:

DELETE FROM student

WHERE name = 'Tom' AND major = 'undecided';

DELETE FROM students;

WHERE student_id > 5;

Basic Queries

A query is a request for data or information from a database table or combination of tables.

So, Imagine if you wanna grab a bunch of students from a million students stored in that table. I might just wanna grab students who
meet a certain condition. We can use SQL queries to specify those things.

(1) SELECT

SELECT keyword is basically going to tell the RDMS that we wanna get some information from it.

SELECT *
FROM student;

* (Asterisk) means that we want to grab all of the information.

SELECT name
FROM student;

By replacing the asterisk, with name would give us all the value in the name category.

Output:
We can also do like:

SELECT name, major

FROM student;

We can also pre-pen these with the name of the table. So, I could say like [Link] & [Link]. And sometimes, people will do
this just because [Link], its clear which table the name is coming from. And as we write more and more complex queries, that can
come in handy more.

SELECT [Link], [Link]

FROM student;

We can arrange the values alphabetically too. Using:

SELECT [Link], [Link]

FROM student
ORDER BY name;

What ORDER BY do, is it makes the name values arrnaged alphabatically.

Output:
By deafult, when you write ORDER, its excetued in ascending (ASC) order but you can also put them in descending order by simply adding
DESC which stands for descending.

SELECT [Link], [Link]

FROM student
ORDER BY name DESC;

Output:
You can order by anything, even if you dont have the thing up in the SELECT, you can still order it.

SELECT [Link], [Link]

FROM student
ORDER BY student_id DESC;

You can also order by different sub-coloumn.

SELECT *
FROM student
ORDER BY major, student_id;

So, its going to measure them by major first. And then id there's any of them that have the same major, it will order them by student_id
further.
By default, its gonna be ordered by ascending order. But, we can also do it in Descending order as we know.

SELECT *
FROM student
ORDER BY major, student_id DESC;

This will order the student_id in descending order, but major would still be the first priorty and if majors are same then student_id will be
ordered by descending order.

You can also limit the amount of the results you want

SELECT *
FROM student
LIMIT 2;
This will limit the results I get back to 2.

Output:

We can use multiple statements together. Like:

SELECT *
FROM student
ORDER BY student_id DESC
LIMIT 2;

This will order the student_id in descending and then limit the results to first two.

We can use this with variety of commands:

SELECT *
FROM student
WHERE major = 'Biology';

We will get all the results where major is Biology.

SELECT name, major

FROM student
WHERE major = 'Chemistry';

We will get the name and major back where major is Biology.

Some examples:
 SELECT name, major
FROM student
WHERE major = "Chemistry' OR major = 'Biology';

SELECT name, major

FROM student
WHERE major = 'Chemistry' OR name = "Kate"
ORDER BY major DESC;

Other operators in SQL:

-- : Comment

< : Less than

> : Greater than

<= : Less than or equal to

>= : Greater than or equal to

<> : Not equal to

AND : Displays a record if all the conditions separated by AND are TRUE

OR : Displays a record if any of the conditions separated by OR is TRUE

For eg:

SELECT *
FROM students
WHERE major <> 'Sociology' AND student_id <=8
ORDER BY major DESC;

Output:
 SELECT *
FROM students
WHERE name IN ('Claire', 'Kate', 'Mike')
ORDER BY student_id ASC;

Here, the results will be shown id the name is Claire, Kate or Mike! and in ascending order.

Foreign KEY

In here, we are making mgr_id a foreign key and its a reference of emp_id column in employee table.

You can type both ON DELETE SET NULL or ON DELETE CASCADE

CREATING A COMPANY DATABASE:

 SQL Dark

1 CREATE TABLE employee (

2 emp_id INT PRIMARY KEY,
3 first_name VARCHAR(40),
4 last_name VARCHAR(40),
5 birth_date DATE,
6 sex VARCHAR(1),
7 salary INT ,
8 super_id INT,
9 branch_id INT
10 );
11
12 CREATE TABLE branch (

AS command in SQL QUERY

SELECT first_name AS forename, last_name AS surname

FROM employee;

What this is going to do is return the first name and last names but instead of naming the columns first_name and last_name, its going to
name them forename and surname respectively.
DISTINCT commad in SQL QUERY

-- Find out all the different genders

SELECT DISTINCT sex
FROM employee;

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. Like in
above code, we want all different kind of values stored in sex table.
All the queries applied in company database eg-

SQL Dark

1 --Find all the employee

2 SELECT *
3 FROM employee;
4
5 --Find all the client
6 SELECT *
7 FROM client;
8
9 --Find all the employee ordered by salary
10 SELECT *
11 FROM employee
12 ORDER BY salary DESC;
13

Functions
Function is bascially a small line or block of code which can do things for us. Like count things, give us average or add things together.

1. COUNT()
 -- Find the number of employees
SELECT COUNT (emp_id)
FROM employee;

What this will do is count the number of emp_id inside of the employee table.

Other eg:

-- Find the number of female employees born after 1970

SELECT COUNT(emp_id)
FROM employee
WHERE sex = 'F' AND birth_date > '1971-01-01';

Output:-
2. AVG()

The AVG() function returns the average value of a numeric column.

-- Find the average of all employee's salaries

SELECT AVG(salary)
FROM employee;

Output:

Other eg:

Find the average of all male employee's salaries

SELECT AVG(salary)
FROM employee
WHERE sex = 'M';
3. SUM()

The SUM() function returns the total sum of a numeric column.

-- Find the sum of all employee's salaries

SELECT SUM(salary)
FROM employee;

Output:

Unique eg:

Aggregation
 -- Find out how many males and females there are
SELECT COUNT(sex), sex
FROM employee
GROUP BY sex;

It is counting how many employees in the sex column and whats grouping is doing is, its printing this data out alongside of whether
they're male or female.

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more
columns.

Output:

-- Find the total sales of each salesman

SELECT SUM(total_sales), emp_id
FROM works_with
GROUP BY emp_id;

So, what this is going to do is it's going to tell us how much each employee has sold.

Output:
 -- Find out how much money each client actually spent with the branch
SELECT SUM(total_sales), client_id
FROM works with
GROUP BY Client_id;

Output:
Wildcards
A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a
column.

% = any no. of characters

_ = one character

I can use this in order to define certian patterns that can be used by database in order to find what we need.

For eg:-

-- Find any client's who are an LLC

SELECT
FROM client
WHERE client_name LIKE '%LLC';

Basically, what this pattern is saying is if the client's name is LIKE the pattern in the single quote, then we want to return it.

So, in other words, if its any (% symbol) number of characters and then an LLC at the end, then we want to return it.

Output:
Another eg:

-- Find any branch suppliers who are in the label business

SELECT *
FROM branch_supplier
WHERE supplier_name LIKE '%Label%';

What this will do is if the supplier name has the word Label in it somewhere. It will return as result.

Output:

Another eg:

-- Find any employee born in October

SELECT *
FROM employee
WHERE birth_date LIKE '%-10-%';

Or, you can also type:

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

Here we have added 4 underscores (_) before -10, as we know that the basic layout of date have first four digits of year and then month. _
means one character.

Output:

Another eg:
 Find any clients who are schools
SELECT *
FROM client
WHERE client_name LIKE '%school%;

Output:

Union

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order

We usually use two SELECT statements to get the results from two different tables. Like this:

SELECT first_name
FROM employee

SELECT branch_name
FROM branch;

But we can do it together like this:

-- Find a list of employee and branch names

SELECT first_name
FROM employee
UNION
SELECT branch_name
FROM branch;

Now, we have one single SQL query which is going to ask the RDBMS to return not only the employee first names, but also the branch
names in a single column

Output:
If you see in the result, the name of the column is the first_name even when its holding the data of branch_name. And the reason is
first_name is the column of first select statement and the column name of first select statement is the column name of the result always.
But you can change it to any another name by using AS.

SELECT first_name AS Company_Names

FROM employee
UNION
SELECT branch_name
FROM branch
UNION
SELECT client_name

Output:
Another eg:

-- Find a list of all clients & branch suppliers' names with branch_id
SELECT client_name AS Client_supplier, branch_id
FROM client
UNION
SELECT supplier_name, branch_id
FROM branch_supplier;

Output:
Sometimes, these queries get a lot more complex, so they will prefix these column names with the table name. Like:

SELECT client_name, client.branch_id

FROM client
UNION
SELECT supplier_name, branch_supplier.branch_id
FROM branch_supplier;

What this does, is make the query more readable as you can identify with table column is which one.

Another eg:

-- Find a sum of all money spent or earned by the company

SELECT SUM(salary) AS money_spent
FROM employee
UNION
SELECT sum(total_sales)
FROM works_with;

Output:
Joins

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

-- Find all branches and the names of their managers

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

Output:
Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

LEFT JOIN eg:

Find all branches and the names of their managers

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

Whenever we use this LEFT JOIN, that means all of the rows in the Employee table (left table) are gonna get included in the results. But
only the rows in the Branch table that matched are going to get inculded because the branch table is the right table.

RIGHT JOIN eg:

Find all branches and the names of their managers

SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
RIGHT JOIN branch
ON employee.emp_id = branch. mgr_id;
Whenever we use this RIGHT JOIN, that means all of the rows in the Branch table (right table) are gonna get included in the results. But
only the rows in the Employee table that matched are going to get inculded because the employee table is the left table.

NESTED QUERIES
Nested query is basically a SQL where we're going to be using multiple select statements in order to get the specific piece of information.

-- Find names of all employees who have sold over 30,000 to a single client
SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.emp_id IN (
SELECT works_with.emp_id
FROM works_with
WHERE works_with.total_sales > 30000 I

Output:
Another eg:

-- Find all clients who are handled by the branch that

-- Michael Scott manages assume you know Michael's id
SELECT client.client_name
FROM client
WHERE client.branch_id = (
SELECT branch.branch_id
FROM branch

Output:

So, lets say Michael Scott was the manager at like multiple branches, its possible that this would return multiple values. So, what we can do
is:
 SELECT client.client_name
FROM client
WHERE client.branch_id = (
SELECT branch.branch_id
FROM branch
WHERE branch.mgr_id = 102
LIMIT 1

Type LIMIT 1, after the query in brackets so the output shown is of one table.

ON DELETE

So, imagine you have a database and you delete one person from it, but that person was linked to other tables too in the database. So, we
can manage the things that will happen with the content in those tables.

ON DELETE SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to
NULL.

• If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL .

eg: Basically if we delee one of these employees, that means the manager ID that was associated to the employee is going to get set to
NULL.

ON DELETE CASCADE: Its used to automatically remove the matching records from the child table when we delete the rows from the
parent table.

eg: If we delete the employee whose ID is stored in the manager ID column, then we're just going to delete that entire row in manager ID

SQL Dark

1 CREATE TABLE employee (

2 emp_id INT PRIMARY KEY,
 3 first_name VARCHAR(40),
4 last_name VARCHAR(40),
5 birth_date DATE,
6 sex VARCHAR(1),
7 salary INT ,
8 super_id INT,
9 branch_id INT
10 );
11
12 CREATE TABLE branch (

Triggers
A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a
trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

You can't use triggers on popsql, you have to execute it on command prompt.

DELIMITER $$
CREATE
TRIGGER my_trigger BEFORE INSERT
ON employee
FOR EACH ROW BEGIN
INSERT INTO trigger_test VALUES('added new employee');
END$$

You define a DELIMITER to tell the mysql client to treat the statements, functions, stored procedures or triggers as an entire statement.
Normally in a .sql file you set a different DELIMITER like $$. The DELIMITER command is used to change the standard delimiter of MySQL
commands (i.e. ;)

Output:

Another eg:

DELIMITER $$
CREATE
TRIGGER mE_trigger BEFORE INSERT
ON employee
FOR EACH ROW BEGIN
INSERT INTO trigger_test VALUES(NEW.first_name);
END$$

What this will give as result is the first_name of whoever is entered into the row.

Output:
Another eg:

DELIMITER $$
CREATE
TRIGGER my_trigger BEFORE INSERT
ON employee
FOR EACH ROW BEGIN
IF [Link] = 'M' THEN
INSERT INTO trigger test VALUES('added male employee');

Output:
 • Before triggers are used to update or validate record values before they’re saved to the database.
• After triggers are used to access field values that are set by the system (such as a record's Id or LastModifiedDate field), and to affect
changes in other records. The records that fire the after trigger are read-only.

You can create trigger for multiple commands like:

• BEFORE DELETE
• BEFORE UPDATE
• BEFORE EXECUTING

You can also DROP the trigger by just typing this on command prompt:

DROP TRIGGER my_trigger;

ER Diagrams
ER = Enitity Relationship

Entity- An object we want to model & store information about.

It is a diagram that displays the relationship of entity sets stored in a database. In other words, ER diagrams help to explain the logical
structure of databases.

Primary Key- An attribute(s) that uniquely identify an entry in the database table.

Composite Attribute- An attribute that can be broken up into sub-attributes.

Multi-value Attribute- An attribute that can have more than one value.

Derived Attribute- An attribute that can be derived from the other attributes.

Multiple Entities- You can define more than one enitity in the diagram.

Relationships- Defines a relationship between two entities.

Total Participation- All members must participate in the relationship.

Partial Participation here means that not all students need to take class.

Total Participation here means that all of the classes need to be taken by at least a single student.

Relationship Atrribute- An attribute about the relationship.

Relationship Cardinality- The number of instance of a enitity from a relation that can be associated with the relation.

Explanation- Here we have a student and a student can take a class. Basically what this means is that a student can take any number of
classes. So when we day M, it refers to any number. We can also say that a class is taken by any number of students. Thats basically what
N would define. So, this would be an NM cardinality relationship.

We can also describe other cardinality relationship like:

• 1 : 1 - A student can take one class and a class can be taken by one student.
 • 1 : N - A student can take one class and a class could be taken by many students.
• N : M - Student can take any number of class and a class can be taken by any number of students.

Weak Entity- An entity that cannont be uniquely identified by it's attribute alone. So, basically, a weak entity is going to rely on or depend
on another entity.

For eg: An exam can't exist without a class. In other words, for an exam to exist, it has to be associated with a class.

Identifying Relatiosnhip- A relationship that serves to uniquly identify the weak entity.

For eg: An exam can be uniquely identified when its paired with a class.

Whenever we have a weak entity and identifying a relationship, the weak entity always has to have total participation in the
identifying relationship. In other words, all exams must have a class, but not all classes need to have an exam.

Designing an ER diagram!
The Output ER diagram from data requirements:

Converting ER diagram to Schema

Step 1: Mapping of Regular Entity Types

For each regular entity type create a relation(table) that includes all the simple attributes of that entity.
Step 2: Mapping of Weak Entity Types

For each weak entity type create a relation (table) that includes all simple attrivutes of the weak entity. The primary key of the
new relation should be the partial key of the weak entity plus the primary key of its owner.

Step 3: Mapping of Binary 1:1 Relationship Types

Include one side of the relationship as a foreign key in the other Favor total participation.
Step 4: Mapping of Binary 1:N Relationship Types

Include the one side's primary key as a foreign key on the N side relation (table).

Step 4: Mapping of Binary M:N Relationship Types

Create a new relation (table) who's [rimary key is a combination of both entities' primary key's. Also include any relationship
attributes
Connections between primary and foreign key:

Output Database Table or Schema: