SQL

 DML Script:  -- Create Departments table CREATE TABLE Departments (     DepartmentID VARCHAR(10) PRIMARY KEY,     DepartmentName VARCHAR(50) ); -- Create Employees table CREATE TABLE Employees (     EmployeeID INT PRIMARY KEY,     Name VARCHAR(50),     DepartmentID VARCHAR(10)  ); -- Create Projects table CREATE TABLE Projects (     ProjectID VARCHAR(10) PRIMARY KEY,     ProjectName VARCHAR(50),     DepartmentID VARCHAR(10)  ); -- Insert data into Departments table INSERT INTO Departments (DepartmentID, DepartmentName) VALUES ('D001', 'HR'), ('D002', 'Finance'), ('D003', 'IT'); -- Insert data into Employees table INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (1, 'John Smith', 'D001'), (2, 'Jane Doe', 'D002'), (3, 'Sam Brown', 'D001'), (4, 'Linda White', 'D003'); -- Insert data into Projects table INSERT INTO Projects (ProjectID, ProjectName, DepartmentID) VAL...

 DML Script: DROP TABLE IF EXISTS ElevatorOrder; GO CREATE TABLE  ElevatorOrder ( LineOrder  INTEGER PRIMARY KEY, [Name]     VARCHAR(100) NOT NULL, [Weight]   INTEGER NOT NULL ); GO INSERT INTO ElevatorOrder ([Name], [Weight], LineOrder) VALUES ('Haruto',611,1),('Minato',533,2),('Haruki',623,3), ('Sota',569,4),('Aoto',610,5),('Hinata',525,6); GO SELECT * FROM ElevatorOrder

 DML Script: DROP TABLE IF EXISTS Balances; GO CREATE TABLE Balances ( CustomerID   INTEGER, BalanceDate  DATE, Amount       MONEY NOT NULL, PRIMARY KEY (CustomerID, BalanceDate) ); GO INSERT INTO  Balances (CustomerID, BalanceDate, Amount) VALUES (1001,'10/11/2021',54.32), (1001,'10/10/2021',17.65), (1001,'9/18/2021',65.56), (1001,'9/12/2021',56.23), (1001,'9/1/2021',42.12), (2002,'10/15/2021',46.52), (2002,'10/13/2021',7.65), (2002,'9/15/2021',75.12), (2002,'9/10/2021',47.34), (2002,'9/2/2021',11.11); GO SELECT * FROM Balances

 DML Script: DROP TABLE IF EXISTS Trips; DROP TABLE IF EXISTS Users; Create table Trips (id int, client_id int, driver_id int, city_id int, status varchar(50), request_at varchar(50)) Create table Users (users_id int, banned varchar(50), role varchar(50))   insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01') insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01') insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01') insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01') insert int...

Logic:  If all steps of a workflow are of the same status (Error, Complete, or Running), then return the  distinct status.   If any steps of a workflow have an Error status along with a status of Complete or Running, set  the overall status to Indeterminate.   If the workflow steps have a combination of Complete and Running (without any Errors), set the  overall status to Running.  DML Script: DROP TABLE IF EXISTS  ProcessLog; GO CREATE TABLE  ProcessLog ( Workflow    VARCHAR(100), StepNumber  INTEGER, RunStatus   VARCHAR(100) NOT NULL  ); GO INSERT INTO  ProcessLog (Workflow, StepNumber, RunStatus) VALUES ('Alpha',1,'Error'),('Alpha',2,'Complete'),('Alpha',3,'Running'), ('Bravo',1,'Complete'),('Bravo',2,'Complete'), ('Charlie',1,'Running'),('Charlie',2,'Running'), ('Delta',1,'Error'),('Delta',2,'Error'), ('Echo',1,'Running'),(...

 DML Script: DROP TABLE IF EXISTS  ProcessLog; GO CREATE TABLE ProcessLog ( Workflow       VARCHAR(100), ExecutionDate  DATE  ); GO INSERT INTO ProcessLog (Workflow, ExecutionDate) VALUES ('Alpha','6/01/2018'),('Alpha','6/14/2018'),('Alpha','6/15/2018'), ('Bravo','6/1/2018'),('Bravo','6/2/2018'),('Bravo','6/19/2018'), ('Charlie','6/1/2018'),('Charlie','6/15/2018'),('Charlie','6/30/2018'); GO

 DML Script: drop table If Exists Stadium Create table  Stadium (id int, visit_date DATE NULL, people int)   insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', '10') insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', '109') insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', '150') insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', '99') insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', '145') insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', '14') insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', '199') insert into Stadium (id, visit_date, people) values ('8', '2017-01-08', '188') insert into Stadium (id, visit_date, people) values ('9', ...

 DML Script:  DROP TABLE IF EXISTS ProcessLog; GO CREATE TABLE ProcessLog ( Workflow       VARCHAR(100), ExecutionDate  DATE  ); GO INSERT INTO ProcessLog (Workflow, ExecutionDate) VALUES ('Alpha','6/01/2018'),('Alpha','6/14/2018'),('Alpha','6/15/2018'), ('Bravo','6/1/2018'),('Bravo','6/2/2018'),('Bravo','6/19/2018'), ('Charlie','6/1/2018'),('Charlie','6/15/2018'),('Charlie','6/30/2018'); GO SELECT * FROM ProcessLog Detailed explanation:  https://youtu.be/AjTiiGuehRU

 DML Script: DROP TABLE IF EXISTS SampleData  CREATE TABLE SampleData ( IntegerValue INTEGER ); INSERT INTO SampleData VALUES(5),(6),(10),(10),(13), (14) ,(17)  ,(20),(81),(90),(76); Detailed explanation:  https://youtu.be/m7VK05Hxgpg

 DML Script: CREATE TABLE MatchDetails ( Innings INT, Overs INT, BallNumber INT, Batter varchar(100), Bowler varchar(100), Nonstriker varchar(100), ExtraType varchar(100), Batsmanrun INT, extrasrun INT, TotalRun INT, IsWicketDelivery INT, PlayerOut varchar(100), kind varchar(100), fielder varchar(100), BattingTeam varchar(1000) ) Excel: https://github.com/datasculptor2895/SQLInterviewQuestions/blob/main/RR%20vs%20RCB.xlsx Import this excel into the table using the below link https://sqlspreads.com/blog/how-to-insert-data-in-excel-to-sql-server/#:~:text=Step%2Dby%2Dstep%20Instructions&text=Right%2Dclick%20the%20table%20and,selected%20cells%20and%20select%20Copy.

 DML Script: DROP TABLE IF EXISTS IPLResults CREATE TABLE IPLResults ( [MatchN] INT, [TeamA] varchar(100), [TeamB] varchar(100), [WinningTeam] varchar(100) ) INSERT INTO IPLResults VALUES (1, 'Chennai Super Kings' ,'Royal Challengers Bangalore' ,'Chennai Super Kings'), (2, 'Delhi Capitals' ,'Punjab Kings' ,'Punjab Kings'), (3, 'Kolkata Knight Riders' ,'Sunrisers Hyderabad' ,'Kolkata Knight Riders'), (4, 'Rajasthan Royals' ,'Lucknow Super Giants' ,'Rajasthan Royals'), (5, 'Gujarat Titans' ,'Mumbai Indians' ,'Gujarat Titans'), (6, 'Punjab Kings' ,'Royal Challengers Bangalore' ,'Royal Challengers Bangalore'), (7, 'Chennai Super Kings' ,'Gujarat Titans' ,'Chennai Super Kings'), (8, 'Sunrisers Hyderabad' ,'Mumbai Indians' ,'Sunrisers Hyderabad'), ...

 DML Script:  DROP TABLE IF EXISTS IPLGroups CREATE TABLE IPLGroups ( [GroupA] varchar(100), [GroupB] varchar(100) ) INSERT INTO IPLGroups VALUES ('Mumbai Indians','Chennai Super Kings'), ('Kolkata Knight Riders','Sunrisers Hyderabad'), ('Rajasthan Royals','Royal Challengers Bengaluru'), ('Delhi Capitals','Punjab Kings'), ('Lucknow Super Giants','Gujarat Titans') SELECT * FROM IPLGroups Detailed Explanation:  https://youtu.be/4HaSo38BCQM

 DML Script: DROP TABLE IF EXISTS user_actions; CREATE TABLE user_actions ( [user_id] int, [event_id] int, [event_type] varchar(100), [event_date] datetime ) INSERT INTO user_actions VALUES (445,7765,'like','05/31/2022'), (742,7766,'sign-in','06/03/2022'), (445,7767,'comment','06/05/2022'), (742,7768,'comment','06/05/2022'), (648,7769,'like','06/18/2022'), (742,7770,'sign-in','05/22/2022'), (648,7771,'sign-in','05/18/2022'), (648,7772,'like','05/19/2022') , (445,7872,'sign-in','05/19/2022') , (445,7972,'sign-in','06/08/2022') ,  (745,7770,'sign-in','06/22/2022'), (648,8771,'sign-in','07/18/2022'), (648,8772,'like','07/18/2022') , (745,8872,'sign-in','07/19/2022') , (745,8972,'comment','07/08/2022')    SELECT * FROM user_actions Detailed expla...

 You must provide a report of all distributors and their sales by region. If a distributor did not have any sales for a region, provide a zero-dollar value for that day. Assume there is at least one sale for each region. DML Script:  DROP TABLE IF EXISTS RegionSales; GO CREATE TABLE RegionSales ( Region       VARCHAR(100), Distributor  VARCHAR(100), Sales        INTEGER NOT NULL  ); GO INSERT INTO RegionSales (Region, Distributor, Sales) VALUES ('North','ACE',10), ('South','ACE',67), ('East','ACE',54), ('North','ACME',65), ('South','ACME',9), ('East','ACME',1), ('West','ACME',7), ('North','Direct Parts',8), ('South','Direct Parts',7), ('West','Direct Parts',12); GO SElect * FROM RegionSales Detailed Explanation:  https://youtu.be/9wyqnTklfCs

 DML Script: CREATE TABLE ReverseNumbers ( A INT, B INT ) INSERT INTO ReverseNumbers VALUES (1,2), (2,1), (4,5), (5,4), (1,6), (6,2), (6,1) SELECT * FROM ReverseNumbers Detailed explanation: https://youtu.be/n3MxK9ftRNo

 DML Script:   DROP TABLE IF EXISTS Products; GO CREATE TABLE Products ( Product VARCHAR(100), Quantity INTEGER NOT NULL ) INSERT INTO Products VALUES ('Mobile',3), ('TV',5), ('Tablet',4) SELECT * FROM Products Detailed explanation: https://youtu.be/RAoLKyL4Dxk

  DROP TABLE IF EXISTS SequenceData;; CREATE TABLE SequenceData ( Name varchar(10), [Sequence] int ) INSERT INTO SequenceData VALUES ('A',1), ('A',2), ('A',3), ('A',4), ('A',7), ('A',8), ('A',10), ('B',11), ('B',13), ('C',15), ('C',16), ('C',17), ('C',18) SELECT * FROM SequenceData Detailed Explanation: https://youtu.be/vsA0NjsGiqQ

 DML Script: DROP TABLE IF EXISTS Gaps; GO CREATE TABLE Gaps ( RowNumber  INTEGER PRIMARY KEY, TestCase   VARCHAR(100) NULL ); GO INSERT INTO Gaps (RowNumber, TestCase) VALUES (1,'Alpha'),(2,NULL),(3,NULL),(4,NULL), (5,'Bravo'),(6,NULL),(7,'Charlie'),(8,NULL),(9,NULL); GO SELECT * FROM Gaps ORDER BY RowNumber Detailed Explanation:  https://youtu.be/mXb1_5rX7PA

 DML Script: DROP TABLE IF EXISTS AccountBalances; GO CREATE TABLE AccountBalances ( AccountID  INTEGER, Balance    MONEY, PRIMARY KEY (AccountID, Balance) ); GO INSERT INTO AccountBalances (AccountID, Balance)  VALUES (1001,-234.45), (1001,23.12), (2002,-93.01), (2002,-120.19), (3003,186.76),  (3003,-90.23),  (3003,-10.11); GO  SELECT * FROM AccountBalances Detailed Explanation:  https://youtu.be/7niFpj-lXP0

 --Off all the products available in the Books category, what percentage  -- did not have any sales in Jan 2024 DROP TABLE IF EXISTS Orders; DROP TABLE IF EXISTS Items; CREATE TABLE Orders ( [Order_day] datetime, [Order_ID] INT, [Customer_ID] INT, [Product_ID] INT, [Quantity] INT ) CREATE TABLE Items ( [Product_ID] INT, [Product_Name] varchar(max), [Category] varchar(max), [Price] INT ) INSERT INTO Items VALUES (100,'Maths','Books',1000), (200,'Science','Books',800), (300,'Biology','Books',900), (400,'Social','Books',700), (500,'English','Books',500), (600,'Pen','Stationary',10), (700,'Pencil','Stationary',10), (800,'Brush','Daily Use',25), (900,'Paste','Daily Use',80)  INSERT INTO Orders VALUES ('01-01-2024',1562,589,100,3), ('01-02-2024',1563,590,200,3), ('01-03-2024',1564,591,900,3), ('02-19-2024...

 DML Script: DROP TABLE IF EXISTS Schedule; DROP TABLE IF EXISTS Activity;   CREATE TABLE Schedule ( ScheduleId  CHAR(1) PRIMARY KEY, StartTime   DATETIME NOT NULL, EndTime     DATETIME NOT NULL ); GO CREATE TABLE Activity ( ScheduleID   CHAR(1) , ActivityName VARCHAR(100), StartTime    DATETIME, EndTime      DATETIME  ); GO INSERT INTO  Schedule (ScheduleID, StartTime, EndTime) VALUES ('A',CAST('2021-10-01 10:00:00' AS DATETIME),CAST('2021-10-01 15:00:00' AS DATETIME)), ('B',CAST('2021-10-01 10:15:00' AS DATETIME),CAST('2021-10-01 12:15:00' AS DATETIME)); GO INSERT INTO  Activity (ScheduleID, ActivityName, StartTime, EndTime) VALUES ('A','Meeting',CAST('2021-10-01 10:00:00' AS DATETIME),CAST('2021-10-01 10:30:00' AS DATETIME)), ('A','Break',CAST('2021-10-01 12:00:00' AS DATETIME),CAST('2021-10-01 12:30:00' AS DATETIME)), ('A','Meeting',CAST('2021-10-01 13...

 DML Script: DROP TABLE IF EXISTS Timings CREATE TABLE Timings (     [timestamp] DATETIME2,     [Employee] INT ); INSERT INTO Timings ([timestamp], [Employee]) VALUES ('2024-01-13 09:25:00', 10),        ('2024-01-13 19:35:00', 10),        ('2024-01-16 09:10:00', 10),        ('2024-01-16 18:10:00', 10),        ('2024-02-11 09:07:00', 10),        ('2024-02-11 19:20:00', 10),        ('2024-02-17 08:40:00', 17),        ('2024-02-17 18:04:00', 17),        ('2024-03-23 09:20:00', 10),        ('2024-03-23 18:30:00', 10),    ('2024-03-31 18:20:00', 15),        ('2024-04-01 06:30:00', 15),    ('2024-04-26 21:25:00', 19),        ('2024-04-27 06:30:00', 19)     select * from Timings Detailed Explanation:  https:...

 DDL Script: DROP TABLE IF EXISTS Orders CREATE TABLE Orders ( [orderid] INT, [productid] INT, [timestamp] DATETIME2 ) INSERT INTO Orders VALUES (10300,1005,'2024-03-04 16:55:00'), (10303,1005,'2024-03-05 08:25:00'), (10302,1009,'2024-03-04 13:37:00'), (10304,1006,'2024-03-04 11:10:00'), (10301,1007,'2024-03-04 15:26:00'), (10305,1008,'2024-03-04 17:15:00'), (10306,1007,'2024-03-04 19:05:00'), (10340,1008,'2024-03-04 17:17:00'), (10307,1009,'2024-03-04 17:09:00'), (10341,1010,'2024-03-04 18:17:00'), (10347,1011,'2024-03-04 19:09:00') Detailed Explanation:  https://youtu.be/twfYTx5b-5Y