Practice

# MongoDB-SQL
# Till February 5th

-- Database Creation
CREATE DATABASE ORG123;
SHOW DATABASES;
USE ORG123;

-- Table Creation
CREATE TABLE Worker (
    WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY INT(15),
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
);

-- Data Insertion
INSERT INTO Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
    (001, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'),
    (002, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'),
    (003, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'),
    (004, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'),
    (005, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin'),
    (006, 'Vipul', 'Diwan', 200000, '2014-06-11 09:00:00', 'Account'),
    (007, 'Satish', 'Kumar', 75000, '2014-01-20 09:00:00', 'Account'),
    (008, 'Geetika', 'Chauhan', 90000, '2014-04-11 09:00:00', 'Admin');

-- Queries
SELECT * FROM Worker WHERE SALARY > 100000 AND DEPARTMENT = 'HR';
SELECT * FROM Worker WHERE SALARY > 200000;
SELECT * FROM Worker WHERE SALARY BETWEEN 100000 AND 200000;
SELECT * FROM Worker WHERE WORKER_ID IN (2,4,6,8);
SELECT * FROM Worker WHERE SALARY BETWEEN 200000 AND 400000 AND WORKER_ID IN (1, 2, 3, 4, 5);
SELECT * FROM Worker WHERE DEPARTMENT = 'HR' ORDER BY SALARY ASC;
SELECT MIN(SALARY) FROM Worker WHERE DEPARTMENT = 'HR';
SELECT FIRST_NAME, LAST_NAME FROM Worker WHERE DEPARTMENT = 'HR' AND SALARY = (SELECT MIN(SALARY) FROM Worker WHERE DEPARTMENT = 'HR');
SELECT DEPARTMENT, FIRST_NAME, LAST_NAME, SALARY FROM Worker w WHERE SALARY = (SELECT MAX(SALARY) FROM Worker WHERE DEPARTMENT = w.DEPARTMENT);
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT FROM Worker WHERE (DEPARTMENT = 'Admin' OR DEPARTMENT = 'Account') AND SALARY = (SELECT MIN(SALARY) FROM Worker WHERE DEPARTMENT IN ('Admin', 'Account'));
SELECT * FROM Worker;
SELECT DISTINCT DEPARTMENT FROM Worker;

-- Alias Usage
SELECT WORKER_ID AS emp_code FROM Worker;

-- UNION and UNION ALL
SELECT WORKER_ID FROM Worker UNION SELECT FIRST_NAME FROM Worker;
SELECT DEPARTMENT, SALARY FROM Worker WHERE DEPARTMENT = 'HR' AND SALARY > 100000 UNION SELECT DEPARTMENT, SALARY FROM Worker WHERE DEPARTMENT = 'Admin' AND SALARY > 100000 ORDER BY SALARY;
SELECT DEPARTMENT, WORKER_ID FROM Worker WHERE DEPARTMENT = 'HR' UNION SELECT DEPARTMENT, WORKER_ID FROM Worker WHERE DEPARTMENT = 'Account' ORDER BY WORKER_ID;

-- CASE Statement
SELECT WORKER_ID, FIRST_NAME, DEPARTMENT,
    CASE 
        WHEN SALARY > 300000 THEN 'Rich people'
        WHEN SALARY BETWEEN 100000 AND 300000 THEN 'Middle stage'
        ELSE 'Poor people'
    END AS People_stage_wise
FROM Worker;

-- Grouping and Aggregations
SELECT DEPARTMENT, COUNT(WORKER_ID) AS Total_Employees FROM Worker GROUP BY DEPARTMENT ORDER BY DEPARTMENT;
SELECT DISTINCT DEPARTMENT, (SELECT COUNT(*) FROM Worker AS W2 WHERE W2.DEPARTMENT = W1.DEPARTMENT) AS Total_Employees FROM Worker AS W1 ORDER BY DEPARTMENT;
SELECT 'HR' AS DEPARTMENT, COUNT(WORKER_ID) AS Total_Employees FROM Worker WHERE DEPARTMENT = 'HR' UNION SELECT 'Admin' AS DEPARTMENT, COUNT(WORKER_ID) AS Total_Employees FROM Worker WHERE DEPARTMENT = 'Admin' UNION SELECT 'Account' AS DEPARTMENT, COUNT(WORKER_ID) AS Total_Employees FROM Worker WHERE DEPARTMENT = 'Account' ORDER BY DEPARTMENT;

#Feb 13th:

--- Get all Worker details ordered by FIRST_NAME in ascending order
SELECT * FROM Worker ORDER BY FIRST_NAME ASC;

--- Get all Worker details ordered by FIRST_NAME (Ascending) and DEPARTMENT (Descending)
SELECT * FROM Worker ORDER BY FIRST_NAME ASC, DEPARTMENT DESC;

--- Get Worker details excluding first names "Vipul" and "Satish"
SELECT * FROM Worker WHERE FIRST_NAME NOT IN ('Vipul', 'Satish');

--- Get Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets
SELECT * FROM Worker WHERE FIRST_NAME LIKE '_h';

--- Count of employees working in the 'Admin' department
SELECT COUNT(*) FROM Worker WHERE DEPARTMENT = 'Admin';

--- Get Worker names with salaries between 50000 and 100000
SELECT FIRST_NAME, LAST_NAME FROM Worker WHERE SALARY BETWEEN 50000 AND 100000;

--- Get the number of workers for each department in descending order
SELECT DEPARTMENT, COUNT(*) AS Worker_Count FROM Worker GROUP BY DEPARTMENT ORDER BY Worker_Count DESC;

--- Get the 5th highest salary without using TOP or LIMIT
SELECT DISTINCT SALARY 
FROM Worker W1 
WHERE 4 = (SELECT COUNT(DISTINCT SALARY) FROM Worker W2 WHERE W2.SALARY > W1.SALARY);

--- Get the list of employees with the same salary
SELECT SALARY, GROUP_CONCAT(FIRST_NAME, ' ', LAST_NAME) AS Employees 
FROM Worker 
GROUP BY SALARY 
HAVING COUNT(*) > 1;


#March 3rd

-- Creating tables for university student info
CREATE TABLE Students_Bhopal (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    department VARCHAR(25) NOT NULL
);

INSERT INTO Students_Bhopal VALUES
    (104, 'Karthik', 'CS'),
    (103, 'Arun', 'CS');

CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    college VARCHAR(25) NOT NULL
);

INSERT INTO Students VALUES
    (104, 'Karthik', 'Chennai'),
    (103, 'Arun', 'Bhopal');

-- Fetching all records
SELECT * FROM Students_Bhopal;
SELECT * FROM Students;

-- Nested query: Who is from Bhopal?
SELECT name AS WinnerOfTheYear
FROM Students_Bhopal
WHERE id = (
    SELECT id FROM Students WHERE college = 'Bhopal'
);

-- Organization database and employee table
CREATE DATABASE IF NOT EXISTS OrgData;
USE OrgData;

CREATE TABLE Employees (
    EmpID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FirstName CHAR(25),
    LastName CHAR(25),
    Salary INT(10),
    JoiningDate DATETIME,
    Department CHAR(25)
);

-- Insert employee records
INSERT INTO Employees (EmpID, FirstName, LastName, Salary, JoiningDate, Department) VALUES
(1, 'Monika', 'Arora', 100000, '2020-02-14 09:00:00', 'HR'),
(2, 'Niharika', 'Verma', 80000, '2011-06-14 09:00:00', 'Admin'),
(3, 'Vishal', 'Singhal', 300000, '2020-02-14 09:00:00', 'HR'),
(4, 'Amitabh', 'Singh', 500000, '2020-02-14 09:00:00', 'Admin'),
(5, 'Vivek', 'Bhati', 500000, '2011-06-14 09:00:00', 'Admin'),
(6, 'Vipul', 'Diwan', 200000, '2011-06-14 09:00:00', 'Account'),
(7, 'Satish', 'Kumar', 75000, '2020-01-14 09:00:00', 'Account'),
(8, 'Geetika', 'Chauhan', 90000, '2011-04-14 09:00:00', 'Admin');

-- Bonuses
CREATE TABLE Bonuses (
    EmpRefID INT,
    BonusAmount INT,
    BonusDate DATETIME,
    FOREIGN KEY (EmpRefID) REFERENCES Employees(EmpID) ON DELETE CASCADE
);

INSERT INTO Bonuses VALUES
(1, 5000, '2020-02-16'),
(2, 3000, '2011-06-16'),
(3, 4000, '2020-02-16'),
(1, 4500, '2020-02-16'),
(2, 3500, '2011-06-16');

-- Titles
CREATE TABLE JobTitles (
    EmpRefID INT,
    Title CHAR(25),
    EffectiveFrom DATETIME,
    FOREIGN KEY (EmpRefID) REFERENCES Employees(EmpID) ON DELETE CASCADE
);

INSERT INTO JobTitles VALUES
(1, 'Manager', '2016-02-20 00:00:00'),
(2, 'Executive', '2016-06-11 00:00:00'),
(8, 'Executive', '2016-06-11 00:00:00'),
(5, 'Manager', '2016-06-11 00:00:00'),
(4, 'Asst. Manager', '2016-06-11 00:00:00'),
(7, 'Executive', '2016-06-11 00:00:00'),
(6, 'Lead', '2016-06-11 00:00:00'),
(3, 'Lead', '2016-06-11 00:00:00');

-- Department with max workers
SELECT Department, COUNT(*) AS DeptCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) = (
    SELECT MAX(DeptCount) FROM (
        SELECT COUNT(*) AS DeptCount FROM Employees GROUP BY Department
    ) AS DeptStats
);

-- Departments with fewer than 5 employees
SELECT Department
FROM Employees
GROUP BY Department
HAVING COUNT(*) < 5;

-- Top departments
SELECT Department, COUNT(*) AS Total
FROM Employees
GROUP BY Department
ORDER BY Total DESC
LIMIT 5;

-- Student-related tables
CREATE TABLE StudentInfo (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(25) NOT NULL,
    Dept VARCHAR(25) NOT NULL
);

INSERT INTO StudentInfo VALUES
(1001, 'Jayanth', 'ECE'),
(1002, 'Praveen', 'CSE'),
(1003, 'Logesh', 'Mech'),
(1006, 'Karthick', 'Aero'),
(1007, 'Mahesh', 'Civil');

CREATE TABLE AcademicInfo (
    StudentID INT PRIMARY KEY,
    CGPA VARCHAR(5) NOT NULL
);

INSERT INTO AcademicInfo VALUES
(1001, '7.2'),
(1002, '8.6'),
(1007, '9.25');

-- Joins
SELECT * FROM StudentInfo CROSS JOIN AcademicInfo;
SELECT * FROM StudentInfo INNER JOIN AcademicInfo ON StudentInfo.StudentID = AcademicInfo.StudentID;
SELECT * FROM StudentInfo NATURAL JOIN AcademicInfo;
SELECT * FROM StudentInfo LEFT JOIN AcademicInfo ON StudentInfo.StudentID = AcademicInfo.StudentID;
SELECT * FROM StudentInfo RIGHT JOIN AcademicInfo ON StudentInfo.StudentID = AcademicInfo.StudentID;

-- Merging Employee tables with UNION
SELECT * FROM Employees INNER JOIN Bonuses ON EmpID = EmpRefID
UNION
SELECT * FROM Employees INNER JOIN JobTitles ON EmpID = EmpRefID;

-- All data with LEFT JOIN
SELECT e.*, b.BonusAmount, b.BonusDate, t.Title, t.EffectiveFrom
FROM Employees e
LEFT JOIN Bonuses b ON e.EmpID = b.EmpRefID
LEFT JOIN JobTitles t ON e.EmpID = t.EmpRefID;

-- Managers only
SELECT e.*, b.BonusAmount, b.BonusDate, t.Title, t.EffectiveFrom
FROM Employees e
JOIN Bonuses b ON e.EmpID = b.EmpRefID
JOIN JobTitles t ON e.EmpID = t.EmpRefID
WHERE t.Title = 'Manager';

-- Customer & product tables
CREATE DATABASE IF NOT EXISTS ShopDB;
USE ShopDB;

CREATE TABLE Customer (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(25),
    LastName VARCHAR(25),
    Age INT,
    City VARCHAR(50)
);

INSERT INTO Customer (FirstName, LastName, Age, City) VALUES
('Joey', 'Tribiani', 40, 'NewYork'),
('Chandler', 'Bing', 41, 'NewYork'),
('Ross', 'Geller', 42, 'NewYork'),
('Phoebe', 'Buffet', 43, 'NewYork'),
('Rachel', 'Something', 44, 'NewYork'),
('Monica', 'Geller', 45, 'NewYork');

-- Filtering customers using LIKE
SELECT * FROM Customer WHERE FirstName LIKE '%o%' AND LastName LIKE '%e%';

-- Update city (already exists so won't fail)
UPDATE Customer SET City = 'NewYork';

-- Dropping and recreating tables
DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(50),
    Price FLOAT
);

INSERT INTO Products (ProductName, Price) VALUES
('Baseball', 69.96),
('Football', 69.96);

SELECT * FROM Products;

--- Get all Worker details ordered by FIRST_NAME in ascending order
SELECT * FROM Worker ORDER BY FIRST_NAME ASC;

--- Get all Worker details ordered by FIRST_NAME (Ascending) and DEPARTMENT (Descending)
SELECT * FROM Worker ORDER BY FIRST_NAME ASC, DEPARTMENT DESC;

--- Get Worker details excluding first names "Vipul" and "Satish"
SELECT * FROM Worker WHERE FIRST_NAME NOT IN ('Vipul', 'Satish');

--- Get Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets
SELECT * FROM Worker WHERE FIRST_NAME LIKE '_h';

--- Count of employees working in the 'Admin' department
SELECT COUNT(*) FROM Worker WHERE DEPARTMENT = 'Admin';

--- Get Worker names with salaries between 50000 and 100000
SELECT FIRST_NAME, LAST_NAME FROM Worker WHERE SALARY BETWEEN 50000 AND 100000;

--- Get the number of workers for each department in descending order
SELECT DEPARTMENT, COUNT(*) AS Worker_Count FROM Worker GROUP BY DEPARTMENT ORDER BY Worker_Count DESC;

--- Get the 5th highest salary without using TOP or LIMIT
SELECT DISTINCT SALARY 
FROM Worker W1 
WHERE 4 = (SELECT COUNT(DISTINCT SALARY) FROM Worker W2 WHERE W2.SALARY > W1.SALARY);

--- Get the list of employees with the same salary
SELECT SALARY, GROUP_CONCAT(FIRST_NAME, ' ', LAST_NAME) AS Employees 
FROM Worker 
GROUP BY SALARY 
HAVING COUNT(*) > 1;


-- Creating tables for university student info
CREATE TABLE Students_Bhopal (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    department VARCHAR(25) NOT NULL
);

INSERT INTO Students_Bhopal VALUES
    (104, 'Karthik', 'CS'),
    (103, 'Arun', 'CS');

CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    college VARCHAR(25) NOT NULL
);

INSERT INTO Students VALUES
    (104, 'Karthik', 'Chennai'),
    (103, 'Arun', 'Bhopal');

-- Fetching all records
SELECT * FROM Students_Bhopal;
SELECT * FROM Students;

-- Nested query: Who is from Bhopal?
SELECT name AS WinnerOfTheYear
FROM Students_Bhopal
WHERE id = (
    SELECT id FROM Students WHERE college = 'Bhopal'
);

-- Organization database and employee table