-- SQL COMMANDS

-- DDL - CREATE, ALTER, RENAME, DROP, TRUNCATE

-- DML - INSERT, UPDATE, DELETE
-- DCL - GRANT, REVOKE
-- DQL - SELECT
-- TCL - SUCCESSFULL, ROLLBACK

-- CREATING DATABASE
CREATE DATABASE college;

USE college;

-- CREATING TABLE
CREATE TABLE student (
rollno INT PRIMARY KEY,
name VARCHAR(50),
marks INT NOT NULL,
grade VARCHAR(1),
city VARCHAR(50)
);

-- INSERTING VALUES
INSERT INTO student
(rollno, name, marks, grade, city)
VALUES
(101, "anil", 78, "C", "Pune"),
(102, "bhumika", 93, "A", "Mumbai"),
(103, "chetan", 85, "B", "Mumbai"),
(104, "dhruv", 96, "A", "Delhi"),
(105, "emanuel", 12, "F", "Delhi"),
(106, "farah", 82, "B", "Delhi");

-- SELECT OPERATIONS
SELECT * FROM student; -- SELECTS ALL COLUMNS FROM TABLE
SELECT rollno, name FROM student; -- SELECTS SPECIFIED SELECTED COLUMNS FROM
TABLE
SELECT DISTINCT city FROM student; -- SELECTS DISTINCT VALUES FROM TABLES

-- WHERE CLAUSE
SELECT * FROM student WHERE marks > 80;
SELECT * FROM student WHERE city = "Pune";
SELECT * FROM student WHERE marks > 80 AND city = "Delhi";

-- OPERATORS

-- ARITHMETIC
SELECT * FROM student WHERE marks + 10 > 100;
SELECT * FROM student WHERE marks - 10 > 80;

-- COMPARISION
SELECT * FROM student WHERE marks > 80;
SELECT * FROM student WHERE marks < 80;
SELECT * FROM student WHERE city = "Pune";

-- LOGICAL
SELECT * FROM student WHERE marks > 80 AND city = "Delhi";
SELECT * FROM student WHERE marks > 80 OR city = "Hyderabad";

-- BETWEEN
SELECT * FROM student WHERE marks BETWEEN 80 AND 90;

-- IN
SELECT * FROM student WHERE city IN ("DELHI", "MUMBAI");
SELECT * FROM student WHERE marks IN (82, 90);
-- NOT IN
SELECT * FROM student WHERE city NOT IN ("DELHI", "MUMBAI");

-- LIMIT
SELECT * FROM student LIMIT 3;

-- ORDER BY - ASCENDING OR DESCENDING

SELECT * FROM student ORDER BY marks ASC;
SELECT * FROM student WHERE marks > 80 ORDER BY marks DESC LIMIT 2;

-- AGGREGATE FUNCTIONS
SELECT COUNT(city) FROM student; -- RETURN COUNT
SELECT MAX(marks) FROM student; -- RETURNS MAX VALUE
SELECT MIN(marks) FROM student; -- RETURNS MIN VALUE
SELECT SUM(marks) FROM student; -- RETURNS SUM
SELECT AVG(marks) FROM student; -- RETURNS AVERAGE

-- GROUP BY CLAUSE
SELECT city, COUNT(rollno) FROM student GROUP BY city;

-- HAVING CLAUSE
-- used same as WHERE CLAUSE but only after ORDER BY CLAUSE
SELECT city FROM student GROUP BY city HAVING MAX(marks) > 90;

-- GENERAL ORDER
-- SELECT column_name S
-- FROM table_name F
-- WHERE condtion W
-- GROUP BY column_name G
-- HAVING condition H
-- ORDER BY value O

SELECT name
FROM student
WHERE grade = "A"
GROUP BY name
HAVING MAX(marks) > 90
ORDER BY name ASC;

-- UPDATE OPERATION
SET SQL_SAFE_UPDATES = 0; -- TURNING OFF SAFE MODE IN SQL
UPDATE student SET grade = "A" WHERE grade = "O";
UPDATE student SET marks = 32 WHERE rollno = 105;
UPDATE student SET grade = "B" WHERE marks BETWEEN 80 AND 90;
UPDATE student SET marks = marks + 1

-- DELETE OPERATION
DELETE FROM student
WHERE marks = 32;

-- FOREIGN KEY
CREATE TABLE dept ( -- PARENT TABLE
id INT PRIMARY KEY,
name VARCHAR(50)
);

-- CASCADING - Changing in child table if any changes done in parent table

-- ON UPDATE CASCADE - Updates in child table if any updatation happends in
parent table
-- ON DELETE CASCADE - Deletes in child table if any deletion happens in parent
table

CREATE TABLE teacher ( -- CHILD TABLE

 id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

INSERT INTO dept VALUES

(1, 'Computer Science'),
(2, 'Mathematics'),
(3, 'Physics');

INSERT INTO teacher (id, name, dept_id) VALUES

(101, 'Alice', 1),
(102, 'Bob', 2),
(103, 'Charlie', 1),
(104, 'David', 3);

DELETE FROM dept WHERE id = 3;

SELECT * FROM dept;

SELECT * FROM teacher;

-- TABLE RELATED QUERIES

-- ALTER - changing schema of table

-- ADD COLUMN
ALTER TABLE student ADD COLUMN age INT;
-- DROP COLUMN
ALTER TABLE student DROP COLUMN age;
-- RENAME TABLE
ALTER TABLE old_table_name RENAME TO new_table_name;
-- CHANGE COLUMN
ALTER TABLE student CHANGE COLUMN age age_new INT;
-- MODIFY COLUMN -- can change old column's datatype and constraint
ALTER TABLE student MODIFY COLUMN age VARCHAR(2);

-- TRUNCATE
TRUNCATE TABLE table_name ;

-- PRACTICE QUESTION
-- Qs: In the student table :
-- a. Change the name of column name" to "full_name".
-- b. Delete all the students who scored marks less than 80.
-- c. Delete the column for grades.

ALTER TABLE student CHANGE COLUMN full_names name VARCHAR(50); -- a

DELETE FROM student WHERE marks<80;
ALTER TABLE student DROP COLUMN age;

-- JOINS
CREATE TABLE emp_dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

INSERT INTO emp_dept VALUES

(1, 'HR'),
(2, 'IT'),
(3, 'Finance'),
(4, 'Marketing');

CREATE TABLE employee (

 emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES emp_dept(dept_id)
);

INSERT INTO employee VALUES

(101, 'Alice', 1),
(102, 'Bob', 2),
(103, 'Charlie', 2),
(104, 'David', 3),
(105, 'Eva', NULL);

SELECT * FROM emp_dept;

SELECT * FROM employee;

-- INNER JOIN - BOTH COMMON IN A AND B - A ∩ B

SELECT *
FROM emp_dept
INNER JOIN employee
ON emp_dept.dept_id = employee.dept_id;

-- OUTER JOIN
-- LEFT JOIN - TOTAL COMPONENTS OF A AND COMMON OF BOTH A AND B - A + A
∩ B
SELECT *
FROM emp_dept
LEFT JOIN employee
ON emp_dept.dept_id = employee.dept_id;
-- RIGHT HOIN - TOTAL COMPONENTS OF B AND COMMON OF BOTH A AND B - B + A
∩ B
SELECT *
FROM emp_dept
RIGHT JOIN employee
ON emp_dept.dept_id = employee.dept_id;
-- FULL JOIN - ALL THE COMPONETS OF BOTH A AND B - A ∪ B
SELECT *
FROM emp_dept
LEFT JOIN employee
ON emp_dept.dept_id = employee.dept_id

UNION

SELECT *
FROM emp_dept
RIGHT JOIN employee
ON emp_dept.dept_id = employee.dept_id;

-- EXCLUIVE JOIN
-- LEFT EJ
SELECT *
FROM emp_dept
LEFT JOIN employee
ON emp_dept.dept_id = employee.dept_id
WHERE employee.dept_id IS NULL;

-- RIGHT EJ
SELECT *
FROM emp_dept
RIGHT JOIN employee
ON emp_dept.dept_id = employee.dept_id
WHERE emp_dept.dept_id IS NULL;
-- FULL EJ
-- LEFT EJ UNION RIGHT EJ

-- SELF JOIN - SAME TABLE JOINS WITH SAME TABLE

SELECT *
FROM emp_dept as a
JOIN employee as b
ON a.dept_id = b.dept_id;

-- UNION should have same no. columns, same data types

SELECT * FROM table_a
UNION
SELECT * FROM table_b;

-- SQL SUB QUERIES

-- Query inside a query - nested queries
SELECT *
FROM student
WHERE marks > (SELECT AVG(marks) FROM STUDENT);

-- VIEWS
CREATE VIEW student_view AS
SELECT name, rollno FROM student;

SELECT * FROM student_view;