SQL

 DROP TABLE IF EXISTS Sales -- Step 1: Create the Sales Table CREATE TABLE Sales (     Product VARCHAR(50),     Region VARCHAR(50),     Sales INT ); -- Step 2: Insert Sample Data into the Sales Table INSERT INTO Sales (Product, Region, Sales) VALUES ('A', 'East', 100); INSERT INTO Sales (Product, Region, Sales) VALUES ('A', 'West', 150); INSERT INTO Sales (Product, Region, Sales) VALUES ('B', 'East', 200); INSERT INTO Sales (Product, Region, Sales) VALUES ('B', 'West', 250); select * from sales

DROP TABLE IF EXISTS salaries  -- Create the salaries table CREATE TABLE salaries (     employee_id INT,     department VARCHAR(1),     salary INT ); -- Insert data into the salaries table INSERT INTO salaries (employee_id, department, salary) VALUES (1, 'A', 3000), (2, 'A', 5000), (3, 'A', 7000), (4, 'B', 2000), (5, 'B', 4000), (6, 'B', 6000), (7, 'C', 1000), (8, 'C', 3000), (9, 'C', 5000);

 -- Create the employees table DROP TABLE IF EXISTS employees; CREATE TABLE employees (     department_id INT,     employee_id INT,     name VARCHAR(50) ); -- Insert sample data into the employees table INSERT INTO employees (department_id, employee_id, name) VALUES (1, 1, 'Anna'), (1, 2, 'Ben'), (2, 3, 'Charlie'), (2, 4, 'David'), (1, 5, 'Eva'), (3, 6, 'George'), (3, 7, 'John'); SELECT * FROM employees

--query to join three tables and filter the results to show only records that exist in exactly two of the tables.  CREATE TABLE TableA (     ID INT,     ValueA VARCHAR(50) ); CREATE TABLE TableB (     ID INT,     ValueB VARCHAR(50) );   CREATE TABLE TableC (     ID INT,     ValueC VARCHAR(50) );  TRUNCATE TABLE TableA  TRUNCATE TABLE TableB  TRUNCATE TABLE TableC INSERT INTO TableA (ID, ValueA) VALUES (1, 'CommonValue'), (2, 'CommonValue'), (3, 'UniqueA'),(5,'CommonValue'); INSERT INTO TableB (ID, ValueB) VALUES (2, 'CommonValue'), (3, 'CommonValue'), (4, 'UniqueB'),(5,'CommonValue'); INSERT INTO TableC (ID, ValueC) VALUES (3, 'CommonValue'), (4, 'CommonValue'), (2, 'UniqueC'),(5,'CommonValue');

 DROP TABLE IF EXISTS Sales CREATE TABLE Sales (       Region VARCHAR(50),     Revenue DECIMAL(10, 2) ); INSERT INTO Sales ( Region, Revenue) VALUES   (  'North', 1000.00), (  'North', 800.00), (  'North', 1200.00), (  'South', 1500.00), ( 'South', 2000.00), ( 'South', 1700.00),   ( 'East', 1100.00), (  'East', 900.00), ( 'East', 1300.00), (  'East', 1150.00),   ( 'West', 1400.00), (  'West', 1600.00), (  'West', 1350.00), ( 'West', 1500.00) ; --Number of rows (n) = 4 --Desired percentile (P) = 0.9 --Position in the ordered set (k) = (n - 1) * P + 1 = (4 - 1) * 0.9 + 1 = 3.7 --The position k falls between the 3rd and 4th values (1150 and 1300). --Using linear interpolation: --Percentile Value = prerow+(k-mod(k))*(currentrow-prerow) --Percentile Value = 1150+(3.7−3)×(1300−1150) --Percentile Value=1255

 -- Drop the table if it already exists DROP TABLE IF EXISTS sales; -- Create the sales table CREATE TABLE sales (     salesperson VARCHAR(50),     amount INT ); -- Insert data into the sales table INSERT INTO sales (salesperson, amount) VALUES ('Alice', 200), ('Alice', 300), ('Alice', 400), ('Alice', 500), ('Alice', 600), ('Bob', 150), ('Bob', 250), ('Bob', 350), ('Bob', 450), ('Bob', 550); Select * from sales

 -- Create the employees table DROP TABLE IF EXISTS employees CREATE TABLE employees (     employee_id INT PRIMARY KEY,     salary INT ); -- Insert data into the employees table INSERT INTO employees (employee_id, salary) VALUES (1, 3000); INSERT INTO employees (employee_id, salary) VALUES (2, 2500); INSERT INTO employees (employee_id, salary) VALUES (3, 4000); INSERT INTO employees (employee_id, salary) VALUES (4, 5000); INSERT INTO employees (employee_id, salary) VALUES (5, 3500); INSERT INTO employees (employee_id, salary) VALUES (6, 2000); INSERT INTO employees (employee_id, salary) VALUES (7, 4500); INSERT INTO employees (employee_id, salary) VALUES (8, 6000); INSERT INTO employees (employee_id, salary) VALUES (9, 7000);  SELECT * FROM employees

 DML Script: DROP TABLE IF EXISTS Grades CREATE TABLE Grades (     Subject VARCHAR(50),     Score INT ); INSERT INTO Grades (Subject, Score) VALUES ('Math', 95), ('Science', 88), ('History', 92), ('English', 85), ('Geography', 90), ('Biology', 87), ('Chemistry', 91), ('Physics', 89), ('Social',95) DROP TABLE IF EXISTS SubjectMapping CREATE TABLE SubjectMapping (     OriginalSubject VARCHAR(50),     MappedSubject VARCHAR(50),     Weight DECIMAL(5, 2) ); INSERT INTO SubjectMapping (OriginalSubject, MappedSubject, Weight) VALUES ('Biology', 'Science', 0.33), ('Chemistry', 'Science', 0.33), ('Physics', 'Science', 0.34), ('Geography', 'Social', 0.5), ('History', 'Social', 0.5); SELECT * FROM Grades SELECT * FROM SubjectMapping

 DROP TABLE IF EXISTS Friends DROP TABLE IF EXISTS Activity CREATE TABLE Friends ( id INT, [name] varchar(255), activityid INT ) CREATE TABLE Activity ( id INT, [name] varchar(255) ) INSERT INTO friends VALUES  (1,'Jon',1), (2,'Ravi',2), (3,'Rahul',2), (4,'Christie',3), (5,'Jade',3), (6,'Victor',1), (7,'Dan',1), (8,'Bob',4) INSERT INTO Activity VALUES (1,'Swimming'), (2,'Dancing'), (3,'Running'), (4,'Drawing')

 DROP TABLE IF EXISTS orders CREATE TABLE orders (     order_id INT PRIMARY KEY,     customer_id INT NOT NULL,     order_status VARCHAR(50) NOT NULL,     order_date DATE NOT NULL,     required_date DATE NOT NULL,     shipped_date DATE,     store_id INT NOT NULL,     staff_id INT NOT NULL ); DROP TABLE IF EXISTS order_items CREATE TABLE order_items (     order_id INT,     item_id INT,     product_id INT NOT NULL,     quantity INT NOT NULL,     list_price DECIMAL(10, 2) NOT NULL,     discount DECIMAL(5, 2) NOT NULL,     PRIMARY KEY (order_id, item_id),     FOREIGN KEY (order_id) REFERENCES orders(order_id) ); INSERT INTO orders (order_id, customer_id, order_status, order_date, required_date, shipped_date, store_id, staff_id) VALUES (1, 1001, 'Pending', '2024-05-01', '2024-05-05', NULL, 1, 101), (2, 1002, 'Shipped', '2024-05-02...

 Create table If Not Exists Drivers (driver_id int, join_date date) Create table If Not Exists Rides (ride_id int, user_id int, requested_at date) Create table If Not Exists AcceptedRides (ride_id int, driver_id int, ride_distance int, ride_duration int) Truncate table Drivers insert into Drivers (driver_id, join_date) values ('10', '2019-12-10') insert into Drivers (driver_id, join_date) values ('8', '2020-1-13') insert into Drivers (driver_id, join_date) values ('5', '2020-2-16') insert into Drivers (driver_id, join_date) values ('7', '2020-3-8') insert into Drivers (driver_id, join_date) values ('4', '2020-5-17') insert into Drivers (driver_id, join_date) values ('1', '2020-10-24') insert into Drivers (driver_id, join_date) values ('6', '2021-1-5') Truncate table Rides insert into Rides (ride_id, user_id, requested_at) values ('6', '75', '2019-12-9'...

 Question 1: Given two tables, Employees and Departments, where Employees has columns EmployeeID, EmployeeName, and DepartmentID, and  Departments has columns DepartmentID and DepartmentName, write a query to find all employees along with their department names . Also, include employees who do not belong to any department. SELECT e.EmployeeName, d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID; Question 2: Assume you have a Customers table with CustomerID and CustomerName columns, and an Orders table with OrderID, CustomerID, and OrderDate. How would you write a query to find customers who have placed more than 5 orders?  SELECT c.CustomerName, COUNT(o.OrderID) AS OrderCount FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY  c.CustomerName HAVING COUNT(o.OrderID) > 5; Question 3: Write a query to list all products that have never been ordered. Assume you have a Products table with ProductID and Produ...

 DROP TABLE IF EXISTS Students DROP TABLE IF EXISTS Packages DROP TABLE IF EXISTS Friends CREATE TABLE Students ( ID INT, [Name] varchar(100) ) CREATE TABLE Packages ( ID INT, [Salary] decimal(18,2) ) CREATE TABLE Friends ( ID INT, Friend_ID INT ) INSERT INTO Students VALUES (1,'Ravi'), (2,'Shyam'), (3,'Jacob'), (4,'Martin')  INSERT INTO Friends VALUES (1,2), (2,3), (3,4), (4,1) INSERT INTO Packages VALUES (1,1500), (2,1000), (3,1100), (4,1200)

 DML Script: DROP TABLE IF EXISTS StudentDetails CREATE TABLE StudentDetails( [Student_Name] varchar(30), [Total_Marks] int , [Year] int) INSERT INTO StudentDetails VALUES('Rahul',90,2010) ,('Sanjay',80,2010) ,('Mohan',70,2010) ,('Rahul',90,2011) ,('Sanjay',85,2011) ,('Mohan',65,2011) ,('Rahul',80,2012) ,('Sanjay',80,2012) ,('Mohan',90,2012)

DROP TABLE IF EXISTS Schedule CREATE TABLE Schedule ( Task_ID INT, [Start_Date] DATETIME, [End_Date] DATETIME ) INSERT INTO Schedule VALUES  (1,'2023-10-01','2023-10-02'), (2,'2023-10-02','2023-10-03'), (3,'2023-10-03','2023-10-04'), (4,'2023-10-13','2023-10-14'), (5,'2023-10-14','2023-10-15'), (6,'2023-10-28','2023-10-29'), (7,'2023-10-30','2023-10-31')  SELECT * FROM Schedule Expected output:  --Output --2015-10-28 2015-10-29 --2015-10-30 2015-10-31 --2015-10-13 2015-10-15 --2015-10-01 2015-10-04