nishantbadhautiya · October 15, 2024 11:33
diff --git a/loveBubber.sql b/loveBubber.sql
 --     ******************* PART 1 ******************************

 CREATE DATABASE temp;
 USE temp; 
 CREATE TABLE student(
 	id INT PRIMARY KEY, 
    	name VARCHAR(255)
 );
 INSERT INTO student values(1, "Nishant");
 INSERT INTO student values(2, "Aman");
 SELECT * FROM student; 
 SHOW tables;
 drop database if exists temp;


 --     ******************* PART 2 ******************************

 select WORKER_REF_ID, BONUS_AMOUNT FROM Bonus; 

 select 34 + 12;
 select now();
 select ucase("Nishant");
 select lcase("Nishant");

 select * from Worker;
 select * from Worker where SALARY > 100000;
 select * from Worker where DEPARTMENT = "HR";
 select * from Worker where SALARY between 80000 and 100000;    -- inclusive both case 

 select * from Worker where DEPARTMENT = 'HR' or DEPARTMENT = 'Account' or DEPARTMENT = 'Admin';
 select * from Worker where DEPARTMENT in ('HR', 'Admin');
 select * from Worker where DEPARTMENT not in ('HR','Admin');

 select * from Worker where FIRST_NAME like '_i%';
 select * from Worker where FIRST_NAME like '___i%';

 select * from Worker order by SALARY;
 select * from Worker order by SALARY asc;
 select * from Worker order by SALARY desc;

 -- DISTINCT 
 select DEPARTMENT from Worker;
 select distinct DEPARTMENT from Worker;

 -- GROUP BY 
 select DEPARTMENT from Worker group by DEPARTMENT;  -- DEPARTMENT name should be same at both place now by default same as distinct clause so used with aggregiate function 
 select DEPARTMENT, count(DEPARTMENT) from Worker group by DEPARTMENT;select DEPARTMENT from Worker group by DEPARTMENT;  -- DEPARTMENT name should be same at both place now by default same as distinct clause so used with
 
 -- AVG salary per department 
 select DEPARTMENT, avg(SALARY) from  Worker group by DEPARTMENT;
 -- Max Salary per department 
 select DEPARTMENT, max(SALARY) from Worker group by DEPARTMENT;
 -- Min Salary per department 
 select DEPARTMENT, min(SALARY) from Worker group by DEPARTMENT;
 -- Total salary per department 
 select DEPARTMENT, sum(SALARY) from Worker group by DEPARTMENT;

 -- group by having 
 select DEPARTMENT, count(DEPARTMENT) from Worker group by DEPARTMENT having count(DEPARTMENT) > 2;  -- having and agrregiate functions are used with group by 


 --     ******************* PART 3 ******************************

 create table account(
 	id int primary key, 
    name varchar(255) unique,
    -- balance int,
    -- constraint acc_balance_chk check(balance > 1000)
 	balance int not null default 0
 );

 -- insert into account(id, name, balance) values(1, "A", 10000);
 -- insert into account(id, name, balance) values(2, 'B', 4000);

 insert into account(id, name) values (1, "A");
 insert into account(id, name) values(2, "B");
 select * from account;

 --  *********************************************************************

 -- Add new Column 
 alter table account add interest float not null default 0;
 -- Modify the data type 
 alter table account modify interest double not null default 0;
 -- describe the table
 desc account;
 -- change column / rename the column 
 alter table account change column interest saving_interest float not null default 0; 
 -- drop column 
 alter table account drop column saving_interest;
 -- rename the table 
 alter table account rename to account_details;


 --     ******************* PART 4 ******************************

 -- Create a new database named 'temp'
 CREATE DATABASE temp;

 -- Switch to the newly created database
 USE temp;

 -- Create a table named 'Customer'
 CREATE TABLE Customer (
    id INTEGER PRIMARY KEY,
    cname VARCHAR(225),
    Address VARCHAR(225),
    Gender CHAR(2),
    City VARCHAR(225),
    Pincode INTEGER
 );

 select * from Customer;
 insert into Customer (id, cname, Address, Gender, City, Pincode) values(1, 'Ram Kumar', 'Dilbagh Nagar', 'M', 'Jalandhar', 144002);
 insert into Customer values(1, "CodeHelp", "Delhi", "M", "Delhi",110000);
 insert into Customer(id, cname) values(121, "Bob");

 -- update
 update Customer set Address = "Mumbai", Gender = "M" where id = 121;
 -- update multiple rows
 set sql_safe_updates = 1; -- we have to turn to 0 to update multiple rows 
 update Customer set pincode = 110000;
 update Customer set pincode = pincode + 1;

 -- delete 
 delete from Customer where id = 121;
 -- delete multiple rows
 delete from Customer;

 -- Replace
 select * from Customer;
 replace into Customer (id, City) values (500, "Agra"); -- set City = Agra where id = 500 and other attrubutes will set to NULL
 replace into Customer (id, cname, City) values(500, "Nishant", "Agra");
 replace into Customer set id = 500 , cname = "Nishant Badhautiya", city = "Hathras";
 replace into Customer (id, cname, City) select id, cname, City from Customer where id = 500; -- except cname and City all attributes sets to NULL


 INSERT INTO Customer VALUES 
    (1251, 'Ram Kumar', 'Dilbagh Nagar', 'M', 'Jalandhar', 144002),
    (1300, 'Shayam Singh', 'Ludhiana H.O', 'M', 'Ludhiana', 141001),
    (245, 'Neelabh Shukla', 'Ashok Nagar', 'M', 'Jalandhar', 144003),
    (210, 'Barkha Singh', 'Dilbagh Nagar', 'F', 'Jalandhar', 144002),
    (500, 'Rohan Arora', 'Ludhiana H.O', 'M', 'Ludhiana', 141001);

 -- Insert additional data into the 'Customer' table
 INSERT INTO Customer VALUES 
    (1252, 'Ram Kumar3', 'Dilbagh Nagar', 'M', 'Jalandhar', NULL);
    
 -- Create a table named 'Order_details'
 CREATE TABLE Order_details (
    Order_id INTEGER PRIMARY KEY,
    delivery_date DATE,
    cust_id INT,
    -- FOREIGN KEY(cust_id) REFERENCES Customer(id)
    -- FOREIGN KEY(cust_id) REFERENCES Customer(id) on delete cascade
    FOREIGN KEY(cust_id) REFERENCES Customer(id) on delete set null
 );

 -- Insert data into the 'Order_details' table
 INSERT INTO Order_details VALUES (3, '2019-03-11', 1);
 insert into Order_details values(4, '2019-03-12', 1);

 select * from Order_details;
 delete from Customer where id = 1;

 -- Delete a record from the 'Customer' table
 DELETE FROM Customer WHERE id = 245;


 --     ******************* PART 5 ******************************

 create database Company; 
 use Company; 
 CREATE TABLE Employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(255),
    lname VARCHAR(255),
    age INT,
    emailID VARCHAR(255) UNIQUE,
    PhoneNo INT UNIQUE,
    City VARCHAR(255)
 );


 insert into Employee(fname, lname, age, emailID, PhoneNo, City) values
 	("Aman", "Proto", 32, "[email protected]", 898, "Delhi"),
    ("Yagya", "Narayan", 44, "[email protected]", 222, "Palam"),
    ("Rahul", "BD", 22, "[email protected]", 444, "Kolkata"),
    ("Jatin", "Hermit", 31, "[email protected]", 666, "Raipur"),
    ("PK", "Pandey", 21, "[email protected]", 555, "Jaipur");

 select * from Employee;

 -- **************************************************

 CREATE TABLE Project (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empID INT,
    FOREIGN KEY (empID) REFERENCES Employee(id),
    name VARCHAR(255),
    startdate DATE,
    clientsID INT,
    FOREIGN KEY (clientsID) REFERENCES Clients(id)
 );

 insert into Project(empID, name, startdate, ClientsID) values
 	(1, "A", "2021-04-21", 3),
    (2, "B", "2021-03-12", 1),
    (3, "C", "2021-01-16", 5), 
    (3, "D", "2021-04-27", 2), 
    (5, "E", "2021-05-01", 4);
    
 select * from Project; 

 -- ****************************************************

 CREATE TABLE Clients (
 	id int primary key auto_increment, 
    first_name varchar(255), 
    last_name varchar(255), 
    age int, 
    emailID varchar(255) unique, 
    PhoneNo varchar(255) unique,
    City varchar(255), 
    empID int,
    foreign key(empID) references Employee(id)
 );

 INSERT INTO Clients (first_name, last_name, age, emailID, PhoneNo, City, empID) VALUES
    ("Mac", "Rogers", 47, "[email protected]", "333", "Kolkata", 3),
    ("Max", "Poirier", 27, "[email protected]", "222", "Kolkata", 3),
    ("Peter", "Jain", 24, "[email protected]", "111", "Delhi", 1),
    ("Sushant", "Aggarwal", 23, "[email protected]", "45454", "Hyderabad", 5),
    ("Pratap", "Singh", 36, "[email protected]", "77767", "Mumbai", 2);

 SELECT * FROM Clients;

 -- **************************************************

 select * from Employee;
 select * from Clients;
 select * from Project;

 select e.id, e.fname, e.lname, p.id, p.name from Employee as e inner join Project as p ON e.id = p.empID;  -- at the end there is a condition 
 select * from Employee as e inner join Project as p on e.id = p.empID;
 select * from Employee inner join Project on Employee.id = Project.empID;
 select e.id,e.City, e.emailID, e.PhoneNo, c.first_name, c.last_name, c.id, c.City from Employee as e inner join Clients as c on e.id = c.empID where e.City = "Jaipur" and c.City = "Hyderabad";

 -- LEFT JOIN
 -- Fetch out  project allocated to each employee 
 select * from Employee as e left join Project as p on e.id = p.empID;

 -- RIGHT JOIN
 -- List out all the project details with employee name and employee email
 select p.id, p.name, e.fname, e.lname, e.emailID from Employee as e right join Project as p on e.id = p.empID; 

 -- CROSS JOIN 
 -- List out all the combinations possible for the employee's name and project that can exist
 select e.fname, e.lname, p.id ,p.name from Employee as e CROSS JOIN Project as p; 

 -- Join without using the JOIN Keyword
 select * from Employee, Project where Employee.id = Project.empID;
 select * from Employee as e, Project as p where e.id = p.empID;
 select e.id, e.fname, e.lname, p.id, p.name from Employee as e, Project as p where e.id = p.empID;



 -- **************************************************************************************************************************
 create database if not exists ABC;
 use ABC;

 create table Dept1 (
 	empID int primary key, 
 	name varchar(255), 
    role varchar(255)
 );

 insert into Dept1(empID, name, role) values 
 	(1, 'A', "Engineer"),
    (2, 'B', 'Salesman'),
    (3, 'C', 'Manager'), 
    (4, 'D', 'Salesman'),
    (5, 'E', 'Engineer');

 select * from Dept1;

 -- *************************************************

 create table Dept2 (
 	empID int primary key, 
 	name varchar(255), 
    role varchar(255)
 );

 insert into Dept2(empID, name, role) values 
    (3, 'C', 'Manager'), 
    (6, 'F', 'Marketing'),
    (7, 'G', 'Salesman');

 select * from Dept2;

 -- ***************************************************

 -- SET OPERATIONS 
 -- List out all the employee in the company 
 select * from Dept1 UNION select * from Dept2;

 -- List out all the employee who works in both department as salesman 
 select * from Dept1 where role = 'Salesman' UNION select * from Dept2 where role = 'Salesman';

 -- List out all the employee who works in both department (intersection)
 select * from Dept1 INNER JOIN Dept2 using(empID);   -- using means Dept1.empID = Dept2.empID
 select d1.* from Dept1 as d1 INNER JOIN Dept2 as d2 on d1.empID = d2.empID;

 -- List out all the employee who are working in dept1 not in dept2 (MINUS)
 select Dept1.* from Dept1 LEFT JOIN Dept2 using(empID) where Dept2.empID is NULL;
 select * from Dept1 LEFT JOIN Dept2 using(empID);

 -- *************************************************************************************************************************
 use Company;
 show tables;

 -- Sub Queries 
 -- WHERE Clause same table 
 -- Employee with age > 30
 select * from Employee where age in (select age from Employee where age > 30);

 -- WHERE Clause different Table 
 -- emp details working in more than 1 table 
 select * from Employee where id in (
 	select empID from Project group by empID having count(empID) > 1
 );
 select empID from Project group by empID having count(empID) > 1;

 -- Single Value SubQuery 
 -- emp details having age > avg(age)
 select avg(age) from Employee;
 select * from Employee where age > (select avg(age) from Employee);

 -- FROM Clause -- Derived Table 
 -- select Maximum age person whose first name contains letter 'a'
 -- every derived table must have their Alias name 

 select * from Employee where fname like "%a%";
 select max(age) from (
 	select * from Employee where fname like "%a%"
 ) as temp;


 -- Corelated subquery 
 -- find 3rd oldest employee 
 select * from Employee e1 
 where 3 = (
 select count(e2.age) from Employee e2 where e2.age >= e1.age
 );


 -- VIEW 
 select * from Employee;

 -- Creating a view 
 create view custom_view as select fname, age from Employee;

 -- viewing the view 
 select * from custom_view;

 -- Altering the view 
 alter view custom_view as select fname, lname, age from Employee;

 -- droping the view 
 drop view if exists custom_view;



 --     ******************* PART 6 ******************************
 --     ******************* 50 INTERVIEW QUESTIONS ******************************

 -- Q-1. Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as <WORKER_NAME>.
 select first_name AS WORKER_NAME from worker;

 -- Q-2. Write an SQL query to fetch “FIRST_NAME” from Worker table in upper case.
 select UPPER(first_name) from worker;

 -- Q-3. Write an SQL query to fetch unique values of DEPARTMENT from Worker table.
 SELECT distinct department from worker;

 -- Q-4. Write an SQL query to print the first three characters of  FIRST_NAME from Worker table.
 select substring(first_name, 1, 3) from worker;

 -- Q-5. Write an SQL query to find the position of the alphabet (‘b’) in the first name column ‘Amitabh’ from Worker table.
 select INSTR(first_name, 'B') from worker where first_name = 'Amitabh';

 -- Q-6. Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.
 select RTRIM(first_name) from worker;

 -- Q-7. Write an SQL query to print the DEPARTMENT from Worker table after removing white spaces from the left side.
 select LTRIM(first_name) from worker;

 -- Q-8. Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.
 select distinct department, LENGTH(department) from worker;

 -- Q-9. Write an SQL query to print the FIRST_NAME from Worker table after replacing ‘a’ with ‘A’.
 select REPLACE(first_name, 'a', 'A')  from worker;

 -- Q-10. Write an SQL query to print the FIRST_NAME and LAST_NAME from Worker table into a single column COMPLETE_NAME.
 -- A space char should separate them.
 select CONCAT(first_name, ' ', last_name) AS COMPLETE_NAME from worker;

 -- Q-11. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.
 select * from worker ORDER by first_name;

 -- Q-12. Write an SQL query to print all Worker details from the Worker table order by 
 -- FIRST_NAME Ascending and DEPARTMENT Descending.
 select * from worker order by first_name, department DESC;

 -- Q-13. Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish” from Worker table.
 select * from worker where first_name IN ('Vipul', 'Satish');

 -- Q-14. Write an SQL query to print details of workers excluding first names, “Vipul” and “Satish” from Worker table.
 select * from worker where first_name NOT IN ('Vipul', 'Satish');

 -- Q-15. Write an SQL query to print details of Workers with DEPARTMENT name as “Admin*”.
 select * from worker where department LIKE 'Admin%';

 -- Q-16. Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’.
 select * from worker where first_name LIKE '%a%';

 -- Q-17. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘a’.
 select * from worker where first_name LIKE '%a';

 -- Q-18. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets.
 select * from worker where first_name LIKE '_____h';

 -- Q-19. Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.
 select * from worker where salary between 100000 AND 500000;

 -- Q-20. Write an SQL query to print details of the Workers who have joined in Feb’2014.
 select * from worker where YEAR(joining_date) = 2014 AND MONTH(joining_date) = 02;

 -- Q-21. Write an SQL query to fetch the count of employees working in the department ‘Admin’.
 select department, count(*) from worker where department = 'Admin';

 -- Q-22. Write an SQL query to fetch worker full names with salaries >= 50000 and <= 100000.
 select concat(first_name, ' ', last_name) from worker
 where salary between 50000 and 100000;

 -- Q-23. Write an SQL query to fetch the no. of workers for each department in the descending order.
 select department, count(worker_id) AS no_of_worker from worker group by department
 ORDER BY no_of_worker desc;

 -- Q-24. Write an SQL query to print details of the Workers who are also Managers.
 select w.* from worker as w inner join title as t on w.worker_id = t.worker_ref_id where t.worker_title = 'Manager';

 -- Q-25. Write an SQL query to fetch number (more than 1) of same titles in the ORG of different types.
 select worker_title, count(*) as count from title group by worker_title having count > 1;

 -- Q-26. Write an SQL query to show only odd rows from a table.
 -- select * from worker where MOD (WORKER_ID, 2) != 0; 
 select * from worker where MOD (WORKER_ID, 2) <> 0;

 -- Q-27. Write an SQL query to show only even rows from a table. 
 select * from worker where MOD (WORKER_ID, 2) = 0;

 -- Q-28. Write an SQL query to clone a new table from another table.
 CREATE TABLE worker_clone LIKE worker;
 INSERT INTO worker_clone select * from worker;
 select * from worker_clone;

 -- Q-29. Write an SQL query to fetch intersecting records of two tables.
 select worker.* from worker inner join worker_clone using(worker_id);

 -- Q-30. Write an SQL query to show records from one table that another table does not have.
 -- MINUS
 select worker.* from worker left join worker_clone using(worker_id) WHERE worker_clone.worker_id is NULL;

 -- Q-31. Write an SQL query to show the current date and time.
 -- DUAL
 select curdate();
 select now();

 -- Q-32. Write an SQL query to show the top n (say 5) records of a table order by descending salary.
 select * from worker order by salary desc LIMIT 5;

 -- Q-33. Write an SQL query to determine the nth (say n=5) highest salary from a table.
 select * from worker order by salary desc LIMIT 4,1;

 -- Q-34. Write an SQL query to determine the 5th highest salary without using LIMIT keyword.
 select salary from worker w1
 WHERE 4 = (
 SELECT COUNT(DISTINCT (w2.salary))
 from worker w2
 where w2.salary >= w1.salary
 );
 
 -- Q-35. Write an SQL query to fetch the list of employees with the same salary.
 select w1.* from worker w1, worker w2 where w1.salary = w2.salary and w1.worker_id != w2.worker_id;

 -- Q-36. Write an SQL query to show the second highest salary from a table using sub-query.
 select max(salary) from worker
 where salary not in (select max(salary) from worker);

 -- Q-37. Write an SQL query to show one row twice in results from a table.
 select * from worker
 UNION ALL
 select * from worker ORDER BY worker_id;

 -- Q-38. Write an SQL query to list worker_id who does not get bonus.
 select worker_id from worker where worker_id not in (select worker_ref_id from bonus);

 -- Q-39. Write an SQL query to fetch the first 50% records from a table.
 select * from worker where worker_id <= ( select count(worker_id)/2 from worker);

 -- Q-40. Write an SQL query to fetch the departments that have less than 4 people in it.
 select department, count(department) as depCount from worker group by department having depCount < 4;

 -- Q-41. Write an SQL query to show all departments along with the number of people in there.
 select department, count(department) as depCount from worker group by department;

 -- Q-42. Write an SQL query to show the last record from a table.
 select * from worker where worker_id = (select max(worker_id) from worker);

 -- Q-43. Write an SQL query to fetch the first row of a table.
 select * from worker where worker_id = (select min(worker_id) from worker);

 -- Q-44. Write an SQL query to fetch the last five records from a table.
 (select * from worker order by worker_id desc limit 5) order by worker_id;

 -- Q-45. Write an SQL query to print the name of employees having the highest salary in each department.
 select w.department, w.first_name, w.salary from
 (select max(salary) as maxsal, department from worker group by department) temp
 inner join worker w on temp.department = w.department and temp.maxsal = w.salary;

 -- Q-46. Write an SQL query to fetch three max salaries from a table using co-related subquery
 select distinct salary from worker w1
 where 3 >= (select count(distinct salary) from worker w2 where w1.salary <= w2.salary) order by w1.salary desc;
 -- DRY RUN AFTER REVISING THE CORELATED SUBQUERY CONCEPT FROM LEC-9.
 select distinct salary from worker order by salary desc limit 3;

 -- Q-47. Write an SQL query to fetch three min salaries from a table using co-related subquery
 select distinct salary from worker w1
 where 3 >= (select count(distinct salary) from worker w2 where w1.salary >= w2.salary) order by w1.salary desc;

 -- Q-48. Write an SQL query to fetch nth max salaries from a table.
 select distinct salary from worker w1
 where n >= (select count(distinct salary) from worker w2 where w1.salary <= w2.salary) order by w1.salary desc;

 -- Q-49. Write an SQL query to fetch departments along with the total salaries paid for each of them.
 select department , sum(salary) as depSal from worker group by department order by depSal desc;

 -- Q-50. Write an SQL query to fetch the names of workers who earn the highest salary.
 select first_name, salary from worker where salary = (select max(Salary) from worker);
No results found