SQL

 DROP TABLE IF EXISTS [Event]; CREATE TABLE [Event] (     session_id INT,     event_type VARCHAR(50),     event_time DATETIME );  Dataset 1:  -- Insert expanded event data INSERT INTO [Event] (session_id, event_type, event_time) VALUES (1, 'login', '2024-10-01 08:00:00'), (1, 'view', '2024-10-01 08:05:00'), (1, 'purchase', '2024-10-01 08:10:00'), (2, 'login', '2024-10-01 09:00:00'), (2, 'add_to_cart', '2024-10-01 09:05:00'), (2, 'purchase', '2024-10-01 09:10:00'), (3, 'login', '2024-10-01 10:00:00'), (3, 'view', '2024-10-01 10:05:00'), (3, 'purchase', '2024-10-01 10:10:00'), (4, 'login', '2024-10-01 10:30:00'), (4, 'search', '2024-10-01 10:35:00'), (4, 'view', '2024-10-01 10:40:00'), (4, 'add_to_cart', '2024-10-01 10:45:00'), (4, 'purchase', '2024-10-01 10:50:00'), ...

- DROP TABLE IF EXISTS [Event]; CREATE TABLE [Event] (     session_id INT,     event_type VARCHAR(50),     event_time DATETIME ); -- Dataset 3: Customers frequently search for items before viewing or adding to cart INSERT INTO Events (session_id, event_type, event_time) VALUES (1, 'login', '2024-10-03 08:00:00'), (1, 'search', '2024-10-03 08:05:00'), (1, 'view', '2024-10-03 08:10:00'), (1, 'add_to_cart', '2024-10-03 08:15:00'), (1, 'purchase', '2024-10-03 08:20:00'), (2, 'login', '2024-10-03 09:00:00'), (2, 'search', '2024-10-03 09:05:00'), (2, 'view', '2024-10-03 09:10:00'), (2, 'purchase', '2024-10-03 09:15:00'), (3, 'login', '2024-10-03 10:00:00'), (3, 'search', '2024-10-03 10:05:00'), (3, 'view', '2024-10-03 10:10:00'), (3, 'add_to_cart', '2024-10-03 10:15:00'), (3, 'purcha...

- DROP TABLE IF EXISTS [Event]; CREATE TABLE [Event] (     session_id INT,     event_type VARCHAR(50),     event_time DATETIME ); -- Dataset 2  INSERT INTO Events (session_id, event_type, event_time) VALUES (1, 'login', '2024-10-05 08:00:00'), (1, 'view', '2024-10-05 08:05:00'), (1, 'view', '2024-10-05 08:10:00'), (1, 'add_to_cart', '2024-10-05 08:15:00'), (2, 'login', '2024-10-05 09:00:00'), (2, 'view', '2024-10-05 09:05:00'), (2, 'view', '2024-10-05 09:10:00'), (2, 'add_to_cart', '2024-10-05 09:15:00'), (2, 'purchase', '2024-10-05 09:20:00'), (3, 'login', '2024-10-05 10:00:00'), (3, 'view', '2024-10-05 10:05:00'), (3, 'view', '2024-10-05 10:10:00'), (3, 'add_to_cart', '2024-10-05 10:15:00');  

 DROP TABLE IF EXISTS Employees; CREATE TABLE Employees (     id INT PRIMARY KEY,     name VARCHAR(50),     joiningdate DATE ); INSERT INTO Employees (id, name, joiningdate) VALUES (1, 'Bob', '2020-03-22'), (2, 'Alice', '2021-01-15'), (3, 'Charlie', '2019-11-30'), (4, 'David', '2022-07-19'), (5, 'Eve', '2023-02-01'), (6,'Chris', '2023-02-02'); SELECT * FROM Employees

 DROP TABLE IF EXISTS Employee; CREATE TABLE Employee (     EmpID INT PRIMARY KEY,     Name VARCHAR(100),     Department VARCHAR(50),     Salary DECIMAL(10, 2),     HireDate DATE,     YearsInCompany INT ); INSERT INTO Employee (EmpID, Name, Department, Salary, HireDate, YearsInCompany) VALUES (1, 'Alice', 'IT', 70000, '2015-05-10', 9), (2, 'Bob', 'HR', 50000, '2020-02-15', 4), (3, 'Charlie', 'IT', 65000, '2017-07-22', 7), (4, 'David', 'Sales', 55000, '2019-09-30', 5), (5, 'Eva', 'HR', 72000, '2016-03-12', 8), (6, 'Frank', 'Finance', 80000, '2014-01-05', 10), (7, 'Grace', 'IT', 67000, '2018-11-19', 6), (8, 'Henry', 'Sales', 59000, '2021-05-08', 3), (9, 'Irene', 'HR', 75000, '2016-12-10', 8), (10, 'Jack', 'IT', 78000, '2013-06-25', 11), (11, 'Kate...

Rules 1. Sec 80C - 150000 or the value which ever is lower 2. Sec 80D - 25000 or the value which ever is lower 3. Sec 80G - The value in the table 4. Standard Deduction - 50000 Income Range Tax Rate Up to ₹2,50,000 Nil ₹2,50,001 – ₹5,00,000 5% ₹5,00,001 – ₹10,00,000 20% Above ₹10,00,000 30%  DROP TABLE IF EXISTS EmployeeTaxDetails; CREATE TABLE EmployeeTaxDetails (     emp_id INT PRIMARY KEY,     Sec80C INT,     Sec80D INT,     Sec80G INT,     Total_Salary INT ); -- Insert the 10 rows of data INSERT INTO EmployeeTaxDetails (emp_id, Sec80C, Sec80D, Sec80G, Total_Salary) VALUES (1, 100000.00, 20000.00, 5000.00, 250000.00), (2, 250000.00, 25000.00, 10000.00, 300000.00), (3, 80000.00, 40000.00, 15000.00, 350000.00), (4, 120000.00, 30000.00, 8000.00, 500000.00), (5, 170000.00, 35000.00, 12000.00, 650000.00), (6, 175000.00, 40000.00, 18000.00, 900000.00), (7, 100000.00, 45000.00, 25000.00, 1250000.00), (8, 150000.00, 50...

 DROP TABLE IF EXISTS UserActivity ; CREATE TABLE UserActivity (     id INT,     activity_time DATETIME,     type VARCHAR(20) ); INSERT INTO UserActivity (id, activity_time, type) VALUES -- Day 1 (1, '2024-10-01 08:00:00', 'log in'), (1, '2024-10-01 08:15:30', 'scroll'), (1, '2024-10-01 08:30:00', 'log out'), (1, '2024-10-01 12:00:00', 'log in'), (1, '2024-10-01 12:10:30', 'scroll'), (1, '2024-10-01 12:30:00', 'log out'), (2, '2024-10-01 09:00:00', 'log in'), (2, '2024-10-01 09:15:45', 'scroll'), (2, '2024-10-01 09:45:00', 'log out'), (2, '2024-10-01 14:30:00', 'log in'), (2, '2024-10-01 14:50:00', 'scroll'), (2, '2024-10-01 15:30:00', 'log out'), (3, '2024-10-01 07:45:00', 'log in'), (3, '2024-10-01 08:00:00', 'scroll'), (3, '2024-10-01 08:30:00', 'log ...

 DROP TABLE IF EXISTS SequenceNumbers; CREATE TABLE dbo.SequenceNumbers ( [ID] INT, [Number] INT ) INSERT INTO SequenceNumbers VALUES (1,20),(2,20),(3,20),(4,20),(5,22),(6,24),(7,24),(8,24),(9,20) SELECT * FROM SequenceNumbers

 DROP TABLE IF EXISTS king; DROP TABLE IF EXISTS battle; CREATE TABLE king (  k_no INT PRIMARY KEY,  king VARCHAR(50),  house VARCHAR(50) ); -- Create the 'battle' table CREATE TABLE battle (  battle_number INT PRIMARY KEY,  name VARCHAR(100),  attacker_king INT,  defender_king INT,  attacker_outcome INT,  region VARCHAR(50)  );   INSERT INTO king (k_no, king, house) VALUES (1, 'Robb Stark', 'House Stark'), (2, 'Joffrey Baratheon', 'House Lannister'), (3, 'Stannis Baratheon', 'House Baratheon'), (4, 'Balon Greyjoy', 'House Greyjoy'), (5, 'Mace Tyrell', 'House Tyrell'), (6, 'Doran Martell', 'House Martell');   INSERT INTO battle (battle_number, name, attacker_king, defender_king, attacker_outcome, region) VALUES (1, 'Battle of Oxcross', 1, 2, 1, 'The North'), (2, 'Battle of Blackwater', 3, 4, 0, 'The North'), (3, 'Battle of the Fords', 1, 5, 1, ...

 CREATE TABLE worker (     first_name VARCHAR(50),     last_name VARCHAR(50),     department VARCHAR(50),     salary DECIMAL(10, 2),     joining_date DATE ); INSERT INTO worker (first_name, last_name, department, salary, joining_date) VALUES  ('John', 'Doe', 'HR', 55000, '2020-01-15'), ('Jane', 'Smith', 'Finance', 67000, '2019-03-22'), ('Robert', 'Johnson', 'IT', 72000, '2018-06-10'), ('Emily', 'Davis', 'Marketing', 59000, '2021-07-01'), ('Michael', 'Wilson', 'Sales', 63000, '2020-09-15'), ('Sarah', 'Brown', 'HR', 56000, '2022-02-19'), ('David', 'Miller', 'Finance', 68000, '2019-10-05'), ('Laura', 'Garcia', 'IT', 75000, '2020-11-22'), ('James', 'Martinez', 'Marketing', 60000, '2021-03-18'), ('Olivia...

 DROP TABLE IF EXISTS Bookings; CREATE TABLE Bookings (     BookingID INT PRIMARY KEY,     BookingDate DATE,     RoomNumber INT ); -- Sample data INSERT INTO Bookings VALUES (1, '2024-01-01', 101), (2, '2024-01-02', 101), (3, '2024-01-04', 101), (4, '2024-01-01', 102), (5, '2024-01-05', 102); SELECT * FROM Bookings

  drop table if exists workspace; create table workspace (workspaceId int, FolderPath varchar(max)); insert into workspace values (1,'a\b\c') insert into workspace values (1,'a\d') insert into workspace values (1,'a\e') insert into workspace values (1,'a\b\a') insert into workspace values (1,'a\a\a') insert into workspace values (1,'b') insert into workspace values (1,'c') insert into workspace values (1,'c\d\e') insert into workspace values (1,'a\b\c\d') select * from workspace;

 DROP TABLE IF EXISTS rides;  CREATE TABLE rides (     driverid INT,     name VARCHAR(50),     rideid INT PRIMARY KEY,     starttime DATETIME,     endtime DATETIME,     ETA INT,     rating DECIMAL(2, 1) ); INSERT INTO rides (driverid, name, rideid, starttime, endtime, ETA, rating) VALUES (1, 'John', 101, '2024-09-01 08:00:00', '2024-09-01 08:30:00', 35, 4.8), (2, 'Emily', 102, '2024-09-01 09:00:00', '2024-09-01 09:45:00', 50, 4.7), (3, 'David', 103, '2024-09-01 10:00:00', '2024-09-01 10:50:00', 55, 4.5), (4, 'Jessica', 104, '2024-09-01 11:00:00', '2024-09-01 11:30:00', 40, 4.9), (5, 'Michael', 105, '2024-09-01 12:00:00', '2024-09-01 12:25:00', 15, 4.6), (1, 'John', 106, '2024-09-02 08:15:00', '2024-09-02 08:45:00', 40, 4.8), (2, 'Emily', 107, '2024-09-02 09:30:00', '2024-09-02 10:15:00', 50, 4.7), (3, 'David', 10...

 DROP TABLE IF EXISTS [employee] CREATE TABLE [employee] ( [ID] int, [Name] varchar(100), [Manager_ID] int ) INSERT INTO [employee] VALUES (1,'Rohit',10), (2,'Rahul',10), (3,'Ravi',10), (4,'Shiva',20), (5,'Sai',20), (6,'Sumit',20), (10,'Priya',100), (11,'Riyaz',100), (12,'Nitin',100), (20,'Yash',100), (22,'Arjun',100), (100,'Naveen',NULL), (50,'Nikhil',1), (60,'Akhil',1) SELECT * FROM employee

 DROP TABLE IF EXISTS Products; CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Product VARCHAR(255),     Category VARCHAR(100) ); INSERT INTO Products (ProductID, Product, Category) VALUES     (1, 'Laptop', 'Electronics'),     (2, 'Smartphone', 'Electronics'),     (3, 'Tablet', 'Electronics'),     (4, 'Headphones', 'Accessories'),     (5, 'Smartwatch', 'Accessories'),     (6, 'Keyboard', 'Accessories'),     (7, 'Mouse', 'Accessories'),     (8, 'Monitor', 'Accessories'),     (9, 'Printer', 'Electronics');

 DROP TABLE IF EXISTS CustomerPurchases;  CREATE TABLE CustomerPurchases (     CustomerID INT,     DateOfPurchase DATE,     Revenue DECIMAL(10, 2),     ModeOfPurchase VARCHAR(50),     Product VARCHAR(100) ); INSERT INTO CustomerPurchases (CustomerID, DateOfPurchase, Revenue, ModeOfPurchase, Product) VALUES  (1, '2024-03-01', 800.00, 'Online', 'Laptop'), (1, '2024-04-01', 750.00, 'Online', 'Tablet'), (1, '2024-05-01', 1200.00, 'In-Store', 'Smartphone'), (1, '2024-06-01', 900.00, 'Online', 'Television'), (1, '2024-07-01', 1300.00, 'In-Store', 'Gaming Console'), (1, '2024-08-01', 1050.00, 'Online', 'Smartwatch'), (2, '2024-01-15', 1500.00, 'In-Store', 'Refrigerator'), (2, '2024-02-20', 2000.00, 'Online', 'Home Theater System'), (2, '2024-03-25', 1200.00, 'In-Store', 'Washing Machi...

 DROP TABLE IF EXISTS #OrganizationChart; GO CREATE TABLE #OrganizationChart ( ManagerID   VARCHAR(1), EmployeeID  VARCHAR(1) NOT NULL PRIMARY KEY ); GO INSERT INTO #OrganizationChart (ManagerID, EmployeeID) VALUES (NULL, 'A'), ('A', 'B'), ('A', 'C'), ('B', 'D'), ('B', 'E'), ('D', 'G'), ('C', 'F'); GO     SELECT * FROM #OrganizationChart

-- Table: Product DROP TABLE IF EXISTS Product; CREATE TABLE Product (     ProductID INT PRIMARY KEY,     ProductName VARCHAR(100),     Category VARCHAR(50),     Price DECIMAL(10, 2) ); -- Table: Stores DROP TABLE IF EXISTS Stores; CREATE TABLE Stores (     StoreID INT PRIMARY KEY,     StoreName VARCHAR(100),     Location VARCHAR(100),     StoreType VARCHAR(50) ); -- Table: Customers DROP TABLE IF EXISTS Customers; CREATE TABLE Customers (     CustomerID INT PRIMARY KEY,     FirstName VARCHAR(50),     LastName VARCHAR(50),     Age INT,     Gender VARCHAR(10),     City VARCHAR(100) ); -- Table: Sales DROP TABLE IF EXISTS Sales; CREATE TABLE Sales (     SaleID INT PRIMARY KEY,     ProductID INT,     StoreID INT,     CustomerID INT,     SaleDate DATE,     QuantitySold INT,     TotalR...

 DROP TABLE IF EXISTS employee CREATE TABLE employee (     id INT ,     name VARCHAR(100),     department VARCHAR(100),     managerid INT  );   INSERT INTO employee (id,name, department, managerid) VALUES  (1,'John Smith', 'Engineering', NULL), (2,'Sarah Johnson', 'Sales', NULL), (3,'Michael Brown', 'HR', NULL), (4,'Emily Davis', 'Engineering', 1), (5,'James Wilson', 'Engineering', 1), (6,'Linda Martinez', 'Engineering', 1), (7,'David Lee', 'Sales', 2), (8,'Karen Taylor', 'HR', 3), (9,'Charles Anderson', 'HR', 3);    SELECT * FROM employee

 DROP TABLE IF EXISTS Products -- Create the table CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Name VARCHAR(50),     Sales DECIMAL(10, 2) ); -- Insert data into the table INSERT INTO Products (ProductID, Name, Sales) VALUES (1, 'Laptop', 8000.00), (2, 'Smartphone', 7000.00), (3, 'Tablet', 1500.00), (4, 'Headphones', 800.00), (5, 'Smartwatch', 700.00), (6, 'Monitor', 500.00), (7, 'Keyboard', 300.00), (8, 'Mouse', 200.00), (9, 'Charger', 100.00), (10, 'USB Cable', 100.00);  SELECT * FROM Products