This repository includes SQL Tutorial & Notes from freecodecamp YouTube video & pdf notes.
All the below mentioned are prominent element of this project and without them it wouldn't be possible. SQL tutorial for beginners from freecodecamp YT.
Any collection of related information 1. Phone Book 2. Shopping list 3. Todo list 4. Your 5 best friends 5. Facebook's user base, etc..
- Database can be stored in different ways
- On paper
- In your mind
- On a computer
- This Notion note on SQL itself
- Comment section
- A special software program that helps users to create and manage a database
- makes its easy to manage large amount of information
- handles security
- backups
- importing/exporting data
- concurrency - In a database management system (DBMS), concurrency control manages simultaneous access to a database.
- Interacts with software application
- Programming languages
- CRUD (CREATE, READ/RETRIEVE, UPDATE, DELETE) is use to perform following functionalities - to create a database, read or retrieve informations from database, update the existing information database and delete the information in database.
- These are core 4 operations or functionalities in DBMS.
-
Relational databases (SQL)
- Organize data into one or more tables
- Each table has columns and rows
- A unique key identifies each row
- Organize data into one or more tables
-
Non-Relational (no-SQL / not just SQL)
- Organize data in anything but a traditional table
- Key-value stores
- Documents(JSON, XML, etc.)
- Graphs
- Flexible table
- Relational Database Management System (RDBMS)
- Example: MySQL, Oracle, PostgreSQL, MariaDB, etc.
- Structured Query Language
- Standardized language for interacting with RDBMS
- Used to perform 4 core operations i.e. CRUD operations, as well as other administrative tasks(user management, security, backups, etc.).
- Used to define tables and structures
- SQL code used on one RDBMS is not always portable to another without modification
- Example of Relational Database i.e. SQL
- Below ID and Username is unique
- Non-Relational Database management System (NRDBMS)
- Helps users to create and maintain a non-relational database
- Example: MongoDB, DynamoDB, Apache Cassandra, Firebase, etc.
- Helps users to create and maintain a non-relational database
- Implementation specific
- Any non-relational databases falls under this category, so there is no set of language standard.
- Most NRDBMS will implement their own language for performing CRUD and administrative operations on databases.
- Example of Non-Relational Databases (noSQL, not just SQL) -
- Queries are requests made to the database management system for specific information
- As database's structure become more and more complex, it becomes more difficult to get the specific pieces of information we want.
- A google search is a query
All tables in Relational Databases we have 2 things Rows & Columns
- Row or horizontal entry represents individual entry i.e. row represents single student (in above example) eg. 1(student id), Jack(student name), biology(major)
- Column or vertical entry represents single attribute eg. major, name, student_id
- Whenever we are creating table in Relational Databases we always have 1 very special column which is called primary key.
- Primary key is a attribute which uniquely defines row in database.
- Primary key will always unique for each rows in table even if all of attribute will same in row.
- Primary key can be anything it can be a number or it can be string of text but it has uniquely identify the specific row.
- Surrogate key - Surrogate key is a key which has no mapping to anything in real world.
- In below table just a random number assign to employee and that necessarily mean anything.
- Natural Key - Natural key is a key which has mapping to real world.
- In below table emp_ssn ( social sequrity number is the number used in USA to identify each citizens uniquely)
- Foreign Key - Foreign key is an attribute in table that links us to another table in database. Or Foreign key is primary key of linked table.
- In below table i.e. Employee table, branch_id is foreign key which linked with another table i.e. Branch table. And mgr_is also a foreign key.
- One table can have more than one foreign key.
- In below table super_id (supervisor id) is also a foreign key that links employee table itself. (eg. Jan Levinson is supervisor of Michael Scott & Josh Porter)
Composite Key - Composite key is a candidate key that consists of two or more attributes(table columns) that together identify an entity occurrence(table row).
- In below table, there are 2 primary keys (branch_id, supplier_name) and that are composite keys.
- We need composite key because here, supplier_name or branch_id doesn't uniquely identify row by itslef and they are repeating. Only together they can identify row.
- Eg. Hammer Mill supply Paper to branch_id 2 & 3. Uni-ball supply Writing Utensils to branch_id 2 & 3.
- In below example, in Works_With table, both emp_id & client_id are composite key and also foreign key helps us to link relationship between Employee table and Client table.
- Eg, emp_id 101 (i.e. Michael Scott) sold paper(suppose) to client_id 401 (i.e. Lackawana Country) in branch_id 2 (i.e. Scranton Branch) worth $2,67,000 .
-
You can use SQL to get the RDBMS to do things for you
- Create, retrieve/read, update & delete data
- Create & manage databases
- Design & create database tables
- Perform administration tasks(security, user management, import/export, etc.
-
SQL implementations vary between systems
- Not all RDBMS' follow the SQL standard to a 'T'
- The concepts are the same but the implementation may vary
-
SQL is usually a hybrid language, it's basically 4 types of language in one
-
Data Query Language (DQL)
- Used to query the database for information.
- Get information that is already stored there
-
Data Definition Language (DDL)
- Used for defining databases schemas.
-
Data Control Language (DCL)
- Used for controlling access to the data in the database.
- User & permission management
-
Data Manipulation Language (DML)
- Used for inserting, updating and deleting data from the database.
-
-
A query is a set of instructions given to the RDBMS (written is SQL) that tell the RDBMS what information you want it to retrieve for you
- Tons of data in a DB
- Often hidden i a complex schema
- Goal is to only get the data you need
- And any Query in SQL ends with ;
SELECT employee.name, employee.age FROM employee WHERE employee.salary > 300000;
- Install MySQL community server OR Xampp Server
- Install PopSQL visualizing queries.
- INT - Whole Numbers
- DECIMAL(M,N) - Decimal Numbers - Exact value Eg., DECIMAL(10,4)
- VARCHAR(100) - String of text of length 100 here.
- BLOB - Binary Large Object, Stores large data
- DATE - 'YYYY-MM-DD'
- TIMESTAMP - 'YYYY-MM-DD HH:MM:SS' - used for recording.
- You can either create database using MySQL Community Server CLI
- Syntax: create database databaseName;
- or using phpmyadmin panel.
CREATE TABLE student(
student_id INT PRIMARY KEY,
names VARCHAR(20),
major VARCHAR(20)
-- PRIMARY KEY(student_id) // comment in SQL
);-
Syntax: DESCRIBE tableName;
DESCRIBE student;
-
Syntax: DROP Table tableName;
DROP TABLE student;
-
Grab all the information from the table.
-
Syntax: SELECT * FROM table_name;
SELECT * FROM student;
-
Syntax: ALTER Table tableName ADD column_name datatype( ); (Suppose we want to add cgpa column)
ALTER TABLE student ADD cgpa DECIMAL(3,2);
- OR you can also drop table
ALTER TABLE student DROP COLUMN cgpa;
-
Syntax: INSERT INTO tab_name VALUES();
INSERT INTO student VALUES(1, 'Jack', 'Biology'); INSERT INTO student VALUES(2, 'Kate', 'Sociology');
Rows affected 1 (You'll see this message after inserting above information)
-
Suppose a student don't have major then we can modify our INSERT Statement as follow:
INSERT INTO student(student_id, name) VALUES(3, 'Claire');
Now If we see the Claire's major, then it'll show NULL
- SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table.
- NOT NULL, UNIQUE
CREATE TABLE student(
student_id INT,
names VARCHAR(20) NOT NULL, -- i.e. you cannot insert for **name** column
major VARCHAR(20) UNIQUE, -- i.e. major coloumn must be unique for each row in this table
PRIMARY KEY(student_id) // comment in SQL
);
SELECT * FROM student;
INSERT INTO student VALUES(1, 'Jack', 'Biology');
INSERT INTO student VALUES(2, 'Kate', 'Sociology');
INSERT INTO student VALUES(3, NULL, 'Chemistry'); -- it'll throw error as we've mentioned that name van't be NULL
INSERT INTO student VALUES(4, 'Jack', 'Biology'); -- it'll throw error - **Duplicate entry 'Biology' for key 'major'** as we've mentioned major column must be Unique for each row
INSERT INTO student VALUES(5, 'Mike', 'Computer Science');- DEFAULT
CREATE TABLE student(
student_id INT,
names VARCHAR(20) ,
major VARCHAR(20) DEFAULT 'undecided',
PRIMARY KEY(student_id)
);
SELECT * FROM student;
INSERT INTO student(student_id, name) VALUES(1, 'Jack'); -- i.e now if you'll see Jack's major for student_id:1, it'll show **undecided**
INSERT INTO student VALUES(2, 'Kate', 'Sociology');
INSERT INTO student VALUES(3, 'Claire', 'Chemistry');
INSERT INTO student VALUES(4, 'Jack', 'Biology');
INSERT INTO student VALUES(5, 'Mike', 'Computer Science');- AUTO_INCREMENT (increment primary key automatically in this case student_id)
CREATE TABLE student(
student_id INT,
names VARCHAR(20) AUTO_INCREMENT,
major VARCHAR(20),
PRIMARY KEY(student_id)
);
SELECT * FROM student;
INSERT INTO student(name, major) VALUES('Jack', 'Biology');
INSERT INTO student(name, major) VALUES('Claire', 'Chemistry');
INSERT INTO student(name, major) VALUES('Jack', 'Biology');
INSERT INTO student(name, major) VALUES('Mike', 'Computer Science');
> you'll see auto-increment of student-id- Suppose we have to set same major i.e. 'bio' to all students :
SELECT * FROM student;
UPDATE student
SET major = 'Bio';- Suppose we have to set major i.e. 'bio' where major is 'biology':
SELECT * FROM student;
UPDATE student
SET major = 'Bio'
WHERE major = 'biology';
-- OR
UPDATE student
SET major = 'Comp Sci.'
WHERE major = 'Computer Science';- SQL Comparison Operators
- Updating at specific row :
SELECT * FROM student;
UPDATE student
SET major = 'Bio'
WHERE student_id = 4;- Suppose we have to set major to 'Biochemistry' where major is 'bio' or 'chemistry'
SELECT * FROM student;
UPDATE student
SET major = 'Bio'
WHERE major = 'biology';
-- OR
UPDATE student
SET major = 'Comp Sci.'
WHERE major = 'Computer Science';- SQL Comparison Operators
- Updating at specific row :
SELECT * FROM student;
UPDATE student
SET major = 'Bio'
WHERE student_id = 4;- Suppose we have to set major to 'Biochemistry' where major is 'bio' or 'chemistry'
SELECT * FROM student;
UPDATE student
SET major = 'Biochemistry'
WHERE major = 'bio' OR major = 'chemistry';- SQL Logical Operators
- Suppose we have to change both name and major to name = 'Tom' and major = 'undecided' at student_id = 1 :
SELECT * FROM student;
UPDATE student
SET name = 'Tom', major = 'undecided'
WHERE student_id = 1;- Delete all the rows:
SELECT * FROM student;
DELETE FROM student;- Delete specific row:
SELECT * FROM student;
DELETE FROM student
WHERE student_id = 5;- Suppose we have to delete specifically:
SELECT * FROM student;
DELETE FROM student
WHERE name = 'Tom' AND major = 'undecided'- Suppose you want to grab name and major both from table:
SELECT name, major FROM student;
-- OR (If we have many tables then,)
SELECT student.name, student.major
FROM student;- Suppose you want to grab name and major both from table and order by alphabetically(name):
SELECT student.name, student.major
FROM student
ORDER BY name;- Suppose you want to grab all information from table in descending or ascending order of student_id:
-- DESCENDING ORDER
SELECT *
FROM student
ORDER BY student_id DESC;
-- ASCENDING ORDER
SELECT *
FROM student
ORDER BY student_id ASC;- Suppose you want to grab all information from table in alphabetical order of major and student_id:
SELECT *
FROM student
ORDER BY major, student_id;5.1. Suppose you want to grab all information from table in alphabetical order of major and student_id (in descending order):
SELECT *
FROM student
ORDER BY major, student_id DESC;- If you want to get limited number of rows then you can use LIMIT keyword.
- Suppose you want to grab all information till 2 rows:
SELECT *
FROM student
LIMIT 2;6.1. Suppose you want to grab all information till 2 rows and also by descending order of student_id:
SELECT *
FROM student
ORDER BY student_id DESC
LIMIT 2;- Suppose you want to grab name and major from table where major = 'Chemistry' OR major='Biology':
SELECT name, major
FROM student
WHERE major = 'Chemistry' OR major = 'Biology';- Suppose you want to grab all information where major is not equal (<>) to 'Chemistry':
SELECT name, major
FROM student
WHERE major <> 'Chemistry';- Suppose you want to grab all information where student_id is less than (<) 3:
SELECT *
FROM student
WHERE student_id < 3;- Suppose you want to grab all information where student_id is less than (<) 3 and name is not equal (<>) to 'Jack':
SELECT *
FROM student
WHERE student_id < 3 AND name <> 'Jack';- Suppose you want to grab all information where names are equal to Claire, Kate & Mike:
SELECT *
FROM student
WHERE name IN ('Claire','Kate','Mike');- Suppose you want to grab all information where majors are equal to Biology & Chemistry and student_id > 2:
SELECT *
FROM student
WHERE major IN ('Biology', 'Chemistry') AND student_id > 2;
- Employee Table -
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
first_name VARCHAR(40),
last_name VARCHAR(40),
bith_day DATE,
sex VARCHAR(1),
salary INT,
super_id INT,
branch_id INT
);- Branch Table -
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
);- As we need to set the super_id and branch_id of the employee table as Foreign key in the employee table & branch table and employee have not been created yet. We can achieve it by following queries
ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;
ALTER TABLE employee
ADD FOREIGN KEY(super_id),
REFERENCES employee(emp_id)
ON DELETE SET NULL; - Client Table -
CREATE TABLE client (
client_id INT PRIMARY KEY,
client_name VARCHAR (40),
branch_id VARCHAR(40),
FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL;
);- Works_With Table -
CREATE TABLE client (
client_id INT PRIMARY KEY,
client_name VARCHAR (40),
branch_id VARCHAR(40),
FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL;
);- Branch Supplier Table -
CREATE TABLE branch_supplier (
branch_id INT,
supplier_name VARCHAR(40),
supply_type VARCHAR(40),
FOREIGN KEY(branch_id, supplier_name), // supplier_name is not foreign key but it is composite key
FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE;
);- Employee and Branch Table
-- Corporate
INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M',250000,ES9 NULL, NULL);
// We've set branch_id as NULL because corporate branch have not been creaated yet
// So, we're inserting values into branch table
INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');
UPDATE employee
SET branch_id = 1
WHERE emp_id = 100;
// Now, inserting last row in employee table
INSERT INTO employee VALUES(101,'Jan','Levinson','1961-05-11','F',110000,100,1);
-- Scranton
INSERT INTO employee VALUES(102,'Michael','Scott','1964-03-15','M',75000, 100, NULL);
// We've set branch_id as NULL because corporate branch have not been creaated yet
// So, we're inserting values into branch table
INSERT INTO branch VALUES(2, 'Scranton', 102, '1971-06-25');
UPDATE employee
SET branch_id = 2
WHERE emp_id = 102;
INSERT INTO employee VALUES(103,'Angela','Martin','1971-06-25','F',63000, 102, 2);
INSERT INTO employee VALUES(104,'Kelly','Kappoor','1980-02-05','F',55000, 102, 2);
INSERT INTO employee VALUES(105,'Stanley','Hudson','1958-02-19','M',69000, 102, 2);
-- Stamford
INSERT INTO employee VALUES(106,'Josh','Porter','1969-09-05','M',78000,100, NULL);
// We've set branch_id as NULL because corporate branch have not been creaated yet
// So, we're inserting values into branch table
INSERT INTO branch VALUES(3,'Stanford',106,'1998-02-13');
UPDATE employee
SET branch_id = 3
WHERE emp_id = 106;
INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);
INSERT IN TO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);- Branch Supplier
-- BRANCH_SUPPLIER
INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'Uni-Ball', 'Writing Materials');
INSERT INTO branch_supplier VALUES(3, 'Patriot Paper & Labels', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'J.T. Forms ', 'Custom Forms');
INSERT INTO branch_supplier VALUES(3, 'Uni-Ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(3, 'Stamford Labels', 'Custom Forms');- Client Table
-- CLIENT
INSERT INTO client VALUES(400, 'Dunmore Hishchool',2);
INSERT INTO client VALUES(401, 'Lackwana Country',2);
INSERT INTO client VALUES(402, 'FedEx',3);
INSERT INTO client VALUES(403, 'John Dally Law, LLC',3);
INSERT INTO client VALUES(404, 'Scrantin Whitepapers',2);
INSERT INTO client VALUES(405, 'Times Newspaper',3);
INSERT INTO client VALUES(406, 'FedEx',2);- Works_With table
-- WORKS_WITH
INSERT INTO works_with VALUES(105, 400, 55000);
INSERT INTO works_with VALUES(102, 401, 267000);
INSERT INTO works_with VALUES(108, 402, 22500);
INSERT INTO works_with VALUES(107, 403, 5000);
INSERT INTO works_with VALUES(108, 403, 12000);
INSERT INTO works_with VALUES(105, 404, 33000);
INSERT INTO works_with VALUES(107, 405, 26000);
INSERT INTO works_with VALUES(102, 406, 150000);
INSERT INTO works_with VALUES(105, 406, 130000);-- Employee table
Select * from employee;
-- Branch table
Select * from branch;
-- Client table
Select * from client;
-- Works_With table
Select * from works_with;
-- Branch Supplier table
Select * from branch_supplier; -- Find all employees
SELECT * FROM EMPLOYEE;
-- Find all clients
SELECT * FROM client;-- Find all employees order by salary
SELECT *
FROM employee
ORDER BY salary;
-- Find all employees order by salary in descending order
SELECT *
FROM employee
ORDER BY salary desc;-- Find all employee ordered by sex then name
SELECT *
FROM employee
ORDER BY sex, first_name, last_name;-- Find first 5 employee in the table
SELECT *
FROM employee
LIMIT 5;--- Find the first and last names of all employees
SELECT first_name, last_name
FROM employee;
--- Find the forename and surnames of all employees
SELECT first_name AS forename, last_name AS surname
FROM employee;-- Find out all different genders
SELECT DISTINCT sex
FROM employee;
-- Find out all different branches
SELECT DISTINCT branch_id
FROM employee;- COUNT
-- Find the number of employee
SELECT COUNT(emp_id)
FROM employee; // output as 9 (It also counts null)
// or
SELECT COUNT(super_id)
FROM empoyee; // output as 8 (It won't count null)-- Find the number of female employees born after 1970
SELECT COUNT(emp_id)
FROM employee
WHERE sex = 'F' AND birth_date > '1971-01-01';- AVG
-- Find the average of all employee's salary
SELECT AVG(salary)
FROM employee;
-- Find the average of Male's salary
SELECT AVG(salary)
FROM employee
WHERE sex = 'M';
-- Find the sum of all employee's salary
SELECT SUM(salary)
FROM employee;- Aggregation (GROUP BY)
-- Find out how many males and females there are
SELECT COUNT(sex), sex
FROM employee
GROUP BY sex;-- Find the total sales of each salesman
SELECT SUM(total_sales), emp_id
FROM works_with
GROUP BY emp_id;
-- Find the total money spent by each client
SELECT COUNT(total_sales), client_id
FROM works_with
GROUP BY client_id;-
- A wildcard character is used to substitute one or more characters in a string.
- Wildcard characters are used with the
LIKEoperator is used in aWHEREclause to search for a specified pattern in a column.
-- % = any # characters,
-- _ = one character-- Find any cleint's who are an LLC
SELECT *
FROM client
WHERE client_name LIKE '%LLC';-- Find any branch suppliers who are in the label busines
SELECT *
FROM branch_supplier
WHERE supplier_name LIKE '% Label%;-- Find any employee born in october
SELECT *
FROM employee
WHERE birth_date LIKE '____-10'; // YEAR-10
-- Find any employee born in october
SELECT *
FROM employee
WHERE birth_date LIKE '____-02';--- Find any clients who are schools :
SELECT *
FROM employee
WHERE client_name LIKE '%school%';