SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE employee ( empId INT, empName VARCHAR(20), supervisor INT, location VARCHAR(20), salary INT ); CREATE TABLE bonus (empId INT, nBonus INT); CREATE TABLE orderSold ( orderDate DATE, region VARCHAR(20), rep INT, item VARCHAR(20), unit INT, unitCost NUMERIC(5, 2) ); INSERT INTO employee (empId, empName, location, salary) VALUES (34, 'Amy', 'NY', 110000); INSERT INTO employee ( empId, empName, supervisor, location, salary ) VALUES (17, 'Ben', 34, 'TN', 75000); INSERT INTO employee ( empId, empName, supervisor, location, salary ) VALUES (5, 'Chris', 34, 'TN', 80000); INSERT INTO employee ( empId, empName, supervisor, location, salary ) VALUES (10, 'Don', 5, 'HI', 100000); INSERT INTO bonus(empId, nBonus) VALUES (17, 500); INSERT INTO bonus(empId, nBonus) VALUES (10, 2000); INSERT INTO bonus(empId, nBonus) VALUES (34, 5000); INSERT INTO orderSold ( orderDate, region, rep, item, unit, unitCost ) VALUES ( '2021-06-01', 'North', 34, 'Pencil', 95, 1.99 ); INSERT INTO orderSold ( orderDate, region, rep, item, unit, unitCost ) VALUES ( '2021-05-01', 'Central', 17, 'Binder', 50, 1.99 ); INSERT INTO orderSold ( orderDate, region, rep, item, unit, unitCost ) VALUES ( '2021-05-01', 'Central', 5, 'Binder', 36, 1.99 ); INSERT INTO orderSold ( orderDate, region, rep, item, unit, unitCost ) VALUES ( '2021-04-01', 'Central', 5, 'Pen', 27, 1.99 ); INSERT INTO orderSold ( orderDate, region, rep, item, unit, unitCost ) VALUES ( '2021-03-01', 'West', 34, 'Pencil', 56, 4.99 ); INSERT INTO orderSold ( orderDate, region, rep, item, unit, unitCost ) VALUES ( '2021-02-01', 'Central', 5, 'Pen', 1, 1.99 ); SELECT 'employee'; SELECT * FROM employee; SELECT 'bonus'; SELECT * FROM bonus; SELECT 'orderSold'; SELECT * FROM orderSold; -- Questions -- Employee and Bonus -- 1a. Write a SQL statement to get the names of all of Amy’s subordinates, knowing Amy's EmpId -- 1b. Write a SQL statement to get the names of all of Amy’s subordinates, without knowing Amy's EmpId -- 2. Write a SQL statement to find the total bonuses being paid out in a given location. -- 3. Write a SQL statement to return the employee's name, supervisor's name and bonus -- of everyone who got a bonus greater than 1000. -- Employee and Orders -- 1. Number of orders this month -- 2. Number of orders for a given item -- 3. Total number of each item sold along with total money made per item -- 4. All reps names who made sales