SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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' as tablename; SELECT * FROM employee; SELECT 'bonus' as tablename; SELECT * FROM bonus; /*employee's name, supervisor's name and bonus of everyone who got a bonus greater than 1000.*/ select 'A', sup.empName, 'B', sup.empName, bonus.nBonus from employee sub left outer join employee sup on sub.supervisor = sup.empId join bonus on sub.empId = bonus.empId where bonus.nBonus > 1000;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear