day4SQLQuery4.sql

--- DATABASE CREATION
CREATE DATABASE SQL_ANJALI_SESSION;

USE SQL_ANJALI_SESSION;


--- SET OPERATORS (UNION, UNION ALL, INTERSECT, EXCEPT)

--- TABLE 1 : ANJALI
CREATE TABLE ANJALI
(
    BID INT,
    Bname VARCHAR(25),
    Blocation VARCHAR(25)
);

INSERT INTO ANJALI VALUES
(10, 'SBI',  'Akota'),
(40, 'Axis', 'Varacha'),
(50, 'BOB',  'Udhna');

--- TABLE 2 : ANJU
CREATE TABLE ANJU
(
    BID INT,
    Bname VARCHAR(25),
    Blocation VARCHAR(25)
);

INSERT INTO ANJU VALUES
(40, 'Axis', 'Varacha'),
(60, 'ICICI', 'Alkapuri');


--- UNION
--- Combines records and removes duplicate rows
SELECT * FROM ANJU
UNION
SELECT * FROM ANJALI;

--- UNION ALL
--- Combines records and keeps duplicate rows
SELECT * FROM ANJU
UNION ALL
SELECT * FROM ANJALI;

--- INTERSECT
--- Returns only common records from both tables
SELECT * FROM ANJU
INTERSECT
SELECT * FROM ANJALI;

--- EXCEPT
--- Records present in ANJU but not in ANJALI
SELECT * FROM ANJU
EXCEPT
SELECT * FROM ANJALI;

--- REVERSE EXCEPT
--- Records present in ANJALI but not in ANJU
SELECT * FROM ANJALI
EXCEPT
SELECT * FROM ANJU;

--- UNION with selected columns
--- Column order and datatype must match
SELECT BID, Bname, Blocation FROM ANJALI
UNION
SELECT BID, Bname, Blocation FROM ANJU;


--- LIMITATIONS OF SET OPERATORS
--- 1. Number of columns must be SAME
--- 2. Column order must be SAME
--- 3. Data types must be SAME
--- 4. SELECT list count must be SAME


--- EMPLOYEE TABLE FOR AGGREGATE FUNCTIONS & GROUP BY
CREATE TABLE ANJALI_EMP
(
    EID INT,
    Ename VARCHAR(25),
    Edepartment VARCHAR(25),
    Esalary INT
);

INSERT INTO ANJALI_EMP VALUES
(1,'Amit','IT',25000),
(2,'Neha','IT',30000),
(3,'Ravi','HR',20000),
(4,'Pooja','HR',22000),
(5,'Arjun','Sales',28000),
(6,'Anju','Sales',32000);


--- AGGREGATE FUNCTIONS

--- COUNT : counts total rows
SELECT COUNT(*) AS Total_Employees FROM ANJALI_EMP;

--- MAX : highest salary
SELECT MAX(Esalary) AS Highest_Salary FROM ANJALI_EMP;

--- MIN : lowest salary
SELECT MIN(Esalary) AS Lowest_Salary FROM ANJALI_EMP;

--- AVG : average salary
SELECT AVG(Esalary) AS Average_Salary FROM ANJALI_EMP;

--- SUM : total salary
SELECT SUM(Esalary) AS Total_Salary FROM ANJALI_EMP;

--- Multiple aggregates together
SELECT 
    MAX(Esalary) AS Highest,
    MIN(Esalary) AS Lowest,
    AVG(Esalary) AS Average
FROM ANJALI_EMP;


--- GROUP BY & HAVING

--- Sum of salary department-wise
SELECT Edepartment, SUM(Esalary) AS Total_Salary
FROM ANJALI_EMP
GROUP BY Edepartment;

--- Count of employees per department
SELECT Edepartment, COUNT(*) AS Emp_Count
FROM ANJALI_EMP
GROUP BY Edepartment;

--- Departments having max salary greater than 21000
SELECT Edepartment, MAX(Esalary) AS Highest
FROM ANJALI_EMP
GROUP BY Edepartment
HAVING MAX(Esalary) > 21000
ORDER BY Edepartment;


--- INTERVIEW TYPE QUERIES

--- Highest salary in company
SELECT MAX(Esalary) FROM ANJALI_EMP;

--- Number of employees in each department
SELECT Edepartment, COUNT(*)
FROM ANJALI_EMP
GROUP BY Edepartment;

--- Average salary of employees
SELECT AVG(Esalary) FROM ANJALI_EMP;


--- PRODUCT TABLE (GROUP BY PRACTICE)
CREATE TABLE PRODUCT
(
    PID INT,
    ProductName VARCHAR(25),
    Quantity INT,
    Price INT,
    Region VARCHAR(25)
);

INSERT INTO PRODUCT VALUES
(1,'Laptop',5,40000,'West'),
(2,'Mouse',20,500,'West'),
(3,'Keyboard',10,800,'East'),
(4,'Monitor',3,12000,'East');

--- Total quantity per product
SELECT ProductName, SUM(Quantity)
FROM PRODUCT
GROUP BY ProductName;

--- Max price per region
SELECT Region, MAX(Price)
FROM PRODUCT
GROUP BY Region;

--- Total sales value per region
SELECT Region, SUM(Quantity * Price) AS Total_Sales
FROM PRODUCT
GROUP BY Region
HAVING SUM(Quantity * Price) <= 50000
ORDER BY Region DESC;


--- GROUP BY AND HAVING PRACTICE
CREATE TABLE Employee
(
    EID INT PRIMARY KEY,
    Ename VARCHAR(50),
    Department VARCHAR(30),
    City VARCHAR(30),
    Age INT,
    Salary INT
);

INSERT INTO Employee VALUES
(1, 'Amit', 'HR', 'Vadodara', 22, 18000),
(2, 'Neha', 'IT', 'Ahmedabad', 24, 25000),
(3, 'Ravi', 'IT', 'Vadodara', 23, 22000),
(4, 'Anjali', 'HR', 'Surat', 21, 20000),
(5, 'Karan', 'Finance', 'Ahmedabad', 26, 30000),
(6, 'Pooja', 'Finance', 'Vadodara', 24, 28000),
(7, 'Rahul', 'IT', 'Surat', 22, 24000),
(8, 'Sneha', 'HR', 'Vadodara', 23, 21000);

SELECT * FROM Employee;


--- Departments with average salary > 20000
SELECT Department, AVG(Salary) AS Avg_Salary
FROM Employee
GROUP BY Department
HAVING AVG(Salary) > 20000;

--- Cities where average age > 20
SELECT City, AVG(Age) AS Avg_Age
FROM Employee
GROUP BY City
HAVING AVG(Age) > 20;

--- Departments with more than 5 employees
SELECT Department, COUNT(EID) AS Total_Employees
FROM Employee
GROUP BY Department
HAVING COUNT(EID) > 5;

--- Cities where total salary exceeds 30000
SELECT City, SUM(Salary) AS Total_Salary
FROM Employee
GROUP BY City
HAVING SUM(Salary) > 30000;

--- Departments where average age is between 20 and 25
SELECT Department, AVG(Age) AS Avg_Age
FROM Employee
GROUP BY Department
HAVING AVG(Age) BETWEEN 20 AND 25;


--- CONSTRAINTS

--- NOT NULL : column must have a value
--- UNIQUE : column must have unique values
--- CHECK : validates condition
--- DEFAULT : assigns default value

CREATE TABLE EMP1
(
    EID INT,
    Ename VARCHAR(20),
    Eage INT NOT NULL
);

INSERT INTO EMP1 VALUES (1,'Suresh',20);

--- NOT NULL will throw error if value is missing
INSERT INTO EMP1 (EID,Ename,Eage) VALUES (2,'Ramesh',20);

SELECT * FROM EMP1;
DROP TABLE EMP1;


--- CHECK CONSTRAINT
CREATE TABLE EMP1
(
    EID INT,
    Ename VARCHAR(20),
    Eage INT,
    Salary INT CHECK (Salary >= 18000)
);

INSERT INTO EMP1 VALUES (1,'Mahesh',22,18000);
--- Below insert will fail due to CHECK constraint
INSERT INTO EMP1 VALUES (2,'Suresh',22,17999);

DROP TABLE EMP1;


--- DEFAULT CONSTRAINT
CREATE TABLE EMP1
(
    EID INT,
    Ename VARCHAR(20),
    Eage INT DEFAULT 18
);

INSERT INTO EMP1 VALUES (1,'Suresh',15);
INSERT INTO EMP1 (EID,Ename) VALUES (2,'Mahesh');

SELECT * FROM EMP1;
DROP TABLE EMP1;


--- PRIMARY KEY (UNIQUE + NOT NULL)
CREATE TABLE EMP1
(
    EID INT PRIMARY KEY,
    Ename VARCHAR(20)
);

INSERT INTO EMP1 VALUES
(2,'Suresh'),
(1,'Mahesh'),
(3,'Ramesh');

SELECT * FROM EMP1;
DROP TABLE EMP1;


--- FOREIGN KEY & REFERENTIAL INTEGRITY
CREATE TABLE Courses
(
    CID INT PRIMARY KEY,
    Cname VARCHAR(20),
    Cfee INT
);

INSERT INTO Courses VALUES
(10,'Full Stack',1000),
(20,'Cloud',2000),
(30,'GenAI',3000);

CREATE TABLE Student
(
    SID INT,
    Sname VARCHAR(20),
    CID INT FOREIGN KEY REFERENCES Courses(CID)
);

INSERT INTO Student VALUES
(1,'Suresh',10),
(2,'Mahesh',20);

SELECT * FROM Courses;
SELECT * FROM Student;


--- CASCADE OPERATIONS
CREATE TABLE Students
(
    SID INT,
    Sname VARCHAR(20),
    CID INT FOREIGN KEY REFERENCES Courses(CID)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

INSERT INTO Students VALUES
(1,'Ramesh',10),
(2,'Mahesh',10),
(3,'Suresh',10);

--- Updating parent table updates child table automatically
UPDATE Courses SET CID = 50 WHERE CID = 10;

SELECT * FROM Courses;
SELECT * FROM Students;


--- IMPORTANT NOTES
--- Parent table must be created before child table
--- Primary key is mandatory in parent table
--- Datatype matters more than data size

--- SECOND SESSION

--- In a PRIMARY KEY, up to 16 columns are allowed
--- But it is advisable to use only 1–2 columns, not beyond that

--- The PRIMARY KEY column of the parent table
--- must be referenced in the child table as a FOREIGN KEY

--- FOREIGN KEY can be multiple in a table
--- But PRIMARY KEY should be only one per table

--- Whatever data exists in the child table
--- must already exist in the parent table

--- Can we insert NULL in a child table?
--- NULL is allowed in a FOREIGN KEY
--- But NULL is NOT allowed in a PRIMARY KEY


--- DIFFERENCE BETWEEN PRIMARY KEY AND FOREIGN KEY

--- PRIMARY KEY
--- 1. Only one primary key per table
--- 2. Does not allow NULL values
--- 3. Does not allow duplicate values
--- 4. Defined in parent table
--- 5. Ensures uniqueness of records

--- FOREIGN KEY
--- 1. Can be multiple in a table
--- 2. Allows NULL values
--- 3. Allows duplicate values
--- 4. Defined in child table
--- 5. Refers to primary key of parent table


--- DROPPING TABLES
DROP TABLE Student;
DROP TABLE Courses;


--- SIMPLE TABLE WITHOUT PRIMARY KEY
CREATE TABLE Courses
(
    CID INT,
    Cname VARCHAR(20),
    Cfee MONEY
);

INSERT INTO Courses VALUES
(10,'QA',1000);

INSERT INTO Courses VALUES
(20,'Full Stack',2000);

SELECT * FROM Courses;


--- ALTER TABLE TO ADD PRIMARY KEY

--- First make CID NOT NULL
ALTER TABLE Courses
ALTER COLUMN CID INT NOT NULL;

--- Then add PRIMARY KEY constraint
ALTER TABLE Courses
ADD CONSTRAINT PK_Course PRIMARY KEY (CID);


--- Example: You can have multiple products or courses
--- but the PRIMARY KEY value must always be unique


--- DELETE OPERATION EXAMPLE
--- This deletes records where course name matches condition
--- In the WHERE clause, we must specify the exact value
--- of the course name that we want to delete

--- Example: if you want to delete the course named 'QA'
DELETE FROM Courses
WHERE Cname = 'QA';

--- Example: if you want to delete the course named 'Full Stack'
DELETE FROM Courses
WHERE Cname = 'Full Stack';


--- FOREIGN KEY CONSTRAINT ADDITION

--- This constraint links Student table (child)
--- with Courses table (parent) using CID column

--- CID in Student must already exist in Courses
--- Otherwise insertion or update will fail

ALTER TABLE Student
ADD CONSTRAINT FK_Student
FOREIGN KEY (CID)
REFERENCES Courses (CID);


drop tables courses
drop table students

create table courses1 
(cid int,
sid int,
cname varchar(20),
primary key(cid,sid))

create table student1
(cid int ,
sid int,
Sname varchar(20),
foreign key (cid,sid)
references courses1 (cid,sid))