SQL

 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

  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

 DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS posts; CREATE TABLE users (     user_id INT PRIMARY KEY,     first_name VARCHAR(50),     last_name VARCHAR(50),     join_date DATE ); CREATE TABLE posts (     post_id INT PRIMARY KEY,     user_id INT,     content TEXT,     creation_time DATETIME  ); INSERT INTO users (user_id, first_name, last_name, join_date) VALUES (1, 'Alice', 'Smith', '2021-01-10'), (2, 'Bob', 'Johnson', '2021-02-15'), (3, 'Carol', 'Williams', '2021-03-20'), (4, 'David', 'Brown', '2021-04-25'), (5, 'Eve', 'Davis', '2021-05-30'), (6, 'Frank', 'Miller', '2021-06-15'), (7, 'Rob', 'David', '2021-08-15'); INSERT INTO posts (post_id, user_id, content, creation_time) VALUES (1, 1, 'Excited to join SocialSphere!', '2021-01-11 08:30:00'), (2, 2, 'Beautiful day for a walk!', '2021-02...

 DROP TABLE IF EXISTS Friends CREATE TABLE Friends (     id INT,     friend_id INT  ); DROP TABLE IF EXISTS Ratings CREATE TABLE Ratings (     id INT PRIMARY KEY,     rating INT ); INSERT INTO Friends (id, friend_id) VALUES (1, 2), (1, 3), (2, 3), (3, 4), (4, 1), (4, 2), (5,NULL), (6,NULL); INSERT INTO Ratings (id, rating) VALUES (1, 85), (2, 90), (3, 75), (4, 88), (5, 82), (6, 91) SELECT * FROM Friends SELECT * FROM Ratings -- Retrieve all Ids of a person whose rating is greater than friend's id -- If person does not have any friend, retrieve only their id only if rating greater than 85

DROP TABLE IF EXISTS EmployeeJobHistory; CREATE TABLE EmployeeJobHistory (     EmployeeID INT,     EmployeeName VARCHAR(50),     CompanyName VARCHAR(100),     StartDate DATE,     EndDate DATE,     IsCurrentOrganization BIT  ); INSERT INTO EmployeeJobHistory (EmployeeID, EmployeeName, CompanyName, StartDate, EndDate, IsCurrentOrganization) VALUES (1, 'Alice', 'Company A', '2020-01-01', '2021-12-31', 0), (1, 'Alice', 'Company B', '2022-01-01', NULL, 1),  -- Alice is currently working at Company B (2, 'Bob', 'Company C', '2018-06-01', '2019-05-31', 0), (2, 'Bob', 'Company D', '2019-06-01', '2022-11-30', 0), (2, 'Bob', 'Company E', '2023-01-01', NULL, 1),    -- Bob is currently working at Company E (3, 'Charlie', 'Company F', '2018-03-01', NULL, 1), -- Charlie is currently working at Company F (4, 'Diana', 'Compa...

 DROP TABLE IF EXISTS Company; CREATE TABLE Company (     id INT PRIMARY KEY,     name VARCHAR(255) ); INSERT INTO Company (id, name) VALUES (1, 'Alpha Corp'), (2, 'Beta LLC'), (3, 'Gamma Inc'), (4, 'Delta Ltd'), (5, 'Epsilon Plc'), (6, 'Zeta GmbH'), (7, 'Eta SA'), (8, 'Theta Srl'), (9, 'Iota Partners'), (10, 'Kappa Co'); DROP TABLE IF EXISTS Sales; CREATE TABLE Sales (     company_id INT,     expenses INT,     revenue INT  );

 --determine the set of unique orders that contain at least one quantity of product IDs 100 and 200. DROP TABLE IF EXISTS OrderDetails; CREATE TABLE OrderDetails (  order_id INT,  line_item_id INT,  product_id INT,  quantity INT ); INSERT INTO OrderDetails (order_id, line_item_id, product_id, quantity) VALUES (1, 1, 100, 1), (1, 2, 200, 1), (1, 3, 300, 1), (2, 1, 200, 1), (2, 2, 500, 1), (3, 1, 100, 1), (3, 2, 300, 1), (3, 3, 400, 1), (4, 1, 100, 1), (4, 2, 200, 1), (4, 3, 300, 1), (5, 1, 100, 1), (5, 2, 400, 1), (6, 1, 200, 1), (6, 2, 200, 1), (6, 3, 500, 1), (7, 1, 200, 1), (7, 2, 300, 1), (7, 3, 100, 1), (7, 4, 100, 1); select * from OrderDetails

 DROP TABLE IF EXISTS PhoneDirectory; GO CREATE TABLE PhoneDirectory ( CustomerID   INTEGER, [Type]       VARCHAR(100), PhoneNumber  VARCHAR(12) NOT NULL  ); GO INSERT INTO PhoneDirectory (CustomerID, [Type], PhoneNumber) VALUES (1001,'Cellular','555-897-5421'), (1001,'Work','555-897-6542'), (1001,'Home','555-698-9874'), (2002,'Cellular','555-963-6544'), (2002,'Work','555-812-9856'), (3003,'Cellular','555-987-6541'); GO   SELECT * FROM PhoneDirectory

  DROP TABLE IF EXISTS Students; CREATE TABLE Students ( Name VARCHAR(50), Class VARCHAR(10), Subject VARCHAR(50), Marks INT ); INSERT INTO Students (Name, Class, Subject, Marks) VALUES ('John Doe', '10A', 'Math - 1A', 85), ('John Doe', '10A', 'Science - 1A', 90), ('Jane Smith', '10A', 'Math - 1A', 78), ('Jane Smith', '10A', 'Science - 1A', 90), ('Jane Smith', '10A', 'Science - 1B', 88), ('Sam Brown', '10B', 'Math - 1B', 76), ('Sam Brown', '10B', 'History - 1A', 92), ('Sam Brown', '10B', 'History - 1B', 90), ('Emma Davis', '10A', 'Math - 1A', 81), ('Emma Davis', '10A', 'Math - 1B', 85), ('Emma Davis', '10A', 'History - 1B', 85); DROP TABLE IF EXISTS Grades; CREATE TABLE Grades ( ...

-Question: Rank employees based on max salary of the department. If the difference between the emp's salary and max salary is less than 10% then Average performer else worst performer.  DROP TABLE IF EXISTS employees CREATE TABLE employees (     EmployeeID INT PRIMARY KEY,     Name VARCHAR(50),     Department VARCHAR(50),     Salary DECIMAL(10, 2) ); INSERT INTO employees (EmployeeID, Name, Department, Salary) VALUES  (1, 'Alice', 'Sales', 5000.00), (2, 'Bob', 'Sales', 6000.00), (3, 'Charlie', 'Sales', 5500.00), (4, 'David', 'Marketing', 6500.00), (5, 'Eva', 'Marketing', 7000.00), (6, 'Frank', 'Marketing', 6200.00), (7, 'Grace', 'IT', 7500.00), (8, 'Hank', 'IT', 7200.00); SELECT * FROM employees