SQL

Question: Return 2 columns, Number and lag_num. Lag_num should be the prev value as per the way the data is inserted DML Script:  DROP TABLE IF EXISTS Numbers; CREATE TABLE Numbers (number INT); INSERT INTO Numbers (number) VALUES (1), (2), (3), (7), (6), (8), (9), (13), (10);  SELECT * FROM Numbers

 --You are presented with a dataset of home listings, each with a unique Home ID and a Status. Your --objective is to assign a grouping key to each record based on specific conditions. A new grouping key --should be initiated every time a record has the status “New Listing” or “Relisted”. Each subsequent --record, following either of these statuses, should inherit the same grouping key until the next --occurrence of “New Listing” or “Relisted”. DROP TABLE IF EXISTS HomeListings; GO CREATE TABLE HomeListings ( ListingID  INTEGER PRIMARY KEY, HomeID     VARCHAR(100), [Status]     VARCHAR(100) ); GO INSERT INTO HomeListings (ListingID, HomeID, [Status]) VALUES  (1, 'Home A', 'New Listing'), (2, 'Home A', 'Pending'), (3, 'Home A', 'Relisted'), (4, 'Home B', 'New Listing'), (5, 'Home B', 'Under Contract'), (6, 'Home B', 'Relisted'), (7, 'Home C', 'New Listing'), (8, 'Home C...

 -- Creating the Products table DROP TABLE IF EXISTS Products; CREATE TABLE Products (     ProductID INT PRIMARY KEY,     ProductName NVARCHAR(50),     UnitPrice DECIMAL(18, 2),     CategoryID INT ); -- Inserting data into the Products table INSERT INTO Products (ProductID, ProductName, UnitPrice, CategoryID) VALUES  (1, 'Laptop', 1500.00, 1), (2, 'Mouse', 20.00, 1), (3, 'Keyboard', 50.00, 1), (4, 'Monitor', 300.00, 2), (5, 'Desk', 200.00, 2), (6, 'Chair', 100.00, 2), (7, 'Pen', 2.00, 3), (8, 'Notebook', 5.00, 3), (9, 'Binder', 10.00, 3); Question:  1. Find products in Category 1 (e.g., Electronics) that are more expensive than any product in Category 3 (e.g., Stationery). 2. Find products in Category 2 (e.g., Furniture) that are more expensive than all products in Category 3 (e.g., Stationery).

DROP TABLE IF EXISTS Orders; GO CREATE TABLE Orders ( InvoiceID   INTEGER PRIMARY KEY, SalesRepID  INTEGER NOT NULL, Amount      MONEY NOT NULL, SalesType   VARCHAR(100) NOT NULL ); GO INSERT INTO Orders (InvoiceId, SalesRepID, Amount, SalesType) VALUES (1,1001,13454,'International'), (2,1001,3434,'International'), (3,2002,54645,'International'), (4,3003,234345,'International'), (5,4004,776,'International'), (6,1001,4564,'Domestic'), (7,2002,34534,'Domestic'), (8,2002,345,'Domestic'), (9,5005,6543,'Domestic'), (10,6006,67,'Domestic'); SELECT * FROM Orders

 DML Script: --Write an SQL statement that returns a list of states where customers have an average monthly sales --value that is consistently greater than $100. DROP TABLE IF EXISTS Orders; GO CREATE TABLE Orders ( OrderID     INTEGER PRIMARY KEY, CustomerID  INTEGER NOT NULL, OrderDate   DATE NOT NULL, Amount      MONEY NOT NULL, [State]     VARCHAR(2) NOT NULL ); GO INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount, [State]) VALUES (1,1001,'1/1/2018',100,'TX'), (2,1001,'1/1/2018',150,'TX'), (3,1001,'1/1/2018',75,'TX'), (4,1001,'2/1/2018',100,'TX'), (5,1001,'3/1/2018',100,'TX'), (6,2002,'2/1/2018',75,'TX'), (7,2002,'2/1/2018',150,'TX'), (8,3003,'1/1/2018',100,'IA'), (9,3003,'2/1/2018',100,'IA'), (10,3003,'3/1/2018',100,'IA'), (11,4004,'4/1/2018',100,'IA'), (12,4004,'5/1/2018',50,'IA'), ...

 DML Script:  --Identify all orders linked to a single product with a "PROMO" discount value.  --If an order is associated with multiple products or multiple discounts, it should not be included in the result. DROP TABLE IF EXISTS Promotions; GO CREATE TABLE Promotions ( OrderID   INTEGER NOT NULL, Product   VARCHAR(255) NOT NULL, Discount  VARCHAR(255) ); GO INSERT INTO Promotions (OrderID, Product, Discount) VALUES  (1, 'Item1', 'PROMO'), (1, 'Item1', 'PROMO'), (1, 'Item1', 'PROMO'), (1, 'Item2', 'MARKDOWN'), (2, 'Item2', NULL), (2, 'Item3', 'MARKDOWN'), (2, 'Item3', NULL), (3, 'Item1', 'PROMO'), (3, 'Item1', 'PROMO'), (3, 'Item1', 'PROMO'), (4, 'Item5', 'MARKDOWN'), (4, 'Item5', 'MARKDOWN'), (4, 'Item5', 'MARKDOWN'), (5, 'Item8', 'MARKDOWN'), (5, 'Item8', '...

 DROP TABLE IF EXISTS Students; DROP TABLE IF EXISTS Enrollments; CREATE TABLE Students (     StudentID INT NOT NULL PRIMARY KEY,     School VARCHAR(100) NOT NULL,     Class VARCHAR(50) NOT NULL ); CREATE TABLE Enrollments (     StudentID INT NOT NULL,     Subject VARCHAR(100) NOT NULL  ); INSERT INTO Students (StudentID, School, Class) VALUES (1, 'Greenfield High', '10A'), (2, 'Greenfield High', '10A'), (3, 'Blue River School', '10A'), (4, 'Blue River School', '10A'), (5, 'Sunshine Academy', '8C'), (6, 'Sunshine Academy', '8C'), (7, 'Greenfield High', '10A'), (8, 'Blue River School', '9B'), (9, 'Sunshine Academy', '8C'), (10, 'Greenfield High', '11A'); INSERT INTO Enrollments (StudentID, Subject) VALUES (1, 'Maths'), (1, 'Physics'), (2, 'Maths'), (2, 'Physics'), (3, 'Maths'), (3, 'Che...

 DML Script: DROP TABLE IF EXISTS Subjects; CREATE TABLE Subjects (     name VARCHAR(100),     age INT,     Subject1 VARCHAR(100),     Subject2 VARCHAR(100),     Subject3 VARCHAR(100),     Subject4 VARCHAR(100),     Subject5 VARCHAR(100) ); INSERT INTO Subjects (name, age, Subject1, Subject2, Subject3, Subject4, Subject5) VALUES  ('John Doe', 25, 'Mathematics', 'Physics', 'Chemistry', 'Biology', 'English'), ('Jane Smith', 22, 'History', 'Chemistry', 'Political Science', 'Physics', 'Mathematics'), ('Alice Johnson', 30, 'Chemistry', 'Sociology', 'Anthropology', 'Mathematics', 'Literature'), ('Bob Brown', 28, 'Computer Science', 'Chemistry', 'Artificial Intelligence', 'Machine Learning', 'Mathematics'), ('Charlie Black', 35, 'Law', 'Mathematics', 'Forensic Science', ...

 DML Script: CREATE TABLE employee_attendance (  Employee_Name varchar(10),  Date DATE);   INSERT INTO employee_attendance (Employee_Name, Date) VALUES ('Adam', '2024-01-01'), ('Adam', '2024-01-03'), ('Adam', '2024-01-04'), ('Adam', '2024-01-05'), ('Adam', '2024-01-06'), ('Andrew', '2024-01-03'), ('Andrew', '2024-01-04'), ('Andrew', '2024-01-05'), ('Andrew', '2024-01-06'), ('Andrew', '2024-01-07'), ('Stephen', '2024-01-02'), ('Stephen', '2024-01-03'), ('Stephen', '2024-01-04'), ('Stephen', '2024-01-05'), ('Stephen', '2024-01-06'), ('Phillip', '2024-01-01'), ('Phillip', '2024-01-02'), ('Phillip', '2024-01-03'), ('Phillip', '2024-01-04'), ('Phillip', '2024-01-05'), ('Ph...