SQL-from-freecodecamp

Youtube Tutorial Link(freeCodeCamp) - https://www.youtube.com/watch?v=HXV3zeQKqGY

SQL tutorial for beginners from freecodecamp YT.

SQL

What is a Database (DB)?

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
1. On paper
2. In your mind
3. On a computer
4. This Notion note on SQL itself
5. Comment section

Database Management System (DBMS) :

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

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.

Two Types of Databases

Relational databases (SQL)
- Organize data into one or more tables
  - Each table has columns and rows
  - A unique key identifies each row
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

1. Relational Databases (SQL)

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

2. Non-Relational Databases (noSQL, not just SQL)

Non-Relational Database management System (NRDBMS)
- Helps users to create and maintain a non-relational database
  - Example: MongoDB, DynamoDB, Apache Cassandra, Firebase, etc.
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) -

Database Queries

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

Tables & Keys

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

Primary Key

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.

Types of Primary key

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 .

What is SQL ?

SQL or Structured Query Language is a standard language for relational database management system.

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
1. Data Query Language (DQL)
  - Used to query the database for information.
  - Get information that is already stored there
2. Data Definition Language (DDL)
  - Used for defining databases schemas.
3. Data Control Language (DCL)
  - Used for controlling access to the data in the database.
  - User & permission management
4. Data Manipulation Language (DML)
  - Used for inserting, updating and deleting data from the database.
Queries
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;
```

Installation

Install MySQL community server OR Xampp Server
Install PopSQL visualizing queries.

Datatypes (CORE)

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.

Creating Database

You can either create database using MySQL Community Server CLI
- Syntax: create database databaseName;

or using phpmyadmin panel.

Creating table

CREATE TABLE student(

student_id INT PRIMARY KEY,

names VARCHAR(20),

major VARCHAR(20)

-- PRIMARY KEY(student_id)  // comment in SQL

);

Describing The Table

Syntax: DESCRIBE tableName;
```
 DESCRIBE student;
```

Delete The Table

Syntax: DROP Table tableName;
```
DROP TABLE student;
```

SELECT Statement:

Grab all the information from the table.
Syntax: SELECT * FROM table_name;
```
SELECT * FROM student;
```

ALTER The Table

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;
```

Inserting Data

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)

You can specify what you want to insert specifically into the table. See below example,

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

Constraints

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

Update & Delete

UPDATE

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

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;

LIMIT

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;

COMPANY DATABASE :

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;
);

Inserting information into tables now:

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);

Now to check whether we have perfectly added information into table or not

-- 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;

More Basic Queries

-- 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;

Function

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;

Wildcards

- A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator is used in a WHERE clause 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%';

Files

SQL.MD

Latest commit

History