SQL Warehouse Management Exercises

CREATE TABLE House (house_id varchar), City varchar (ten), area int) insert into house values (' wh1 ', ' Beijing ', 370); INSERT INTO House values (' wh2 ', ' Shanghai ', ' n '), insert into house values (' Wh3 ', ' Guangzhou ', ' + '), insert into house values (' Wh4 ', ' Wuhan ', ' n '); create Table employee (house_id varchar), employee_id varchar (ten), salary int) insert into employee values (' wh2 ', ' E1 ', 1220); INSERT into employee values (' Wh1 ', ' E3 ', 1210); INSERT into employee values (' Wh2 ', ' E4 ', 1250); INSERT into employee values (' Wh3 ', ' E6 ', 1230); INSERT into employee values (' wh1 ', ' E7 ', 1250); CREATE table purchase (employee_id varchar), Provider_ ID varchar (TEN), purchase_id varchar (ten), Purchase_date datetime) insert into purchase values (' E3 ', ' S7 ', ' or67 ', ' 2001-6-23 '); INSERT into purchase values (' E1 ', ' S4 ', ' or73 ', ' 2001-7-28 '); INSERT into purchase values (' E7 ', ' S4 ', ' or76 ', ' 2001-5-25 '); INSERT into purchase values (' E6 ', null, ' or77 ', null); INSERT into purchase values (' E3 ', ' S4 ', ' or79 ', ' 2001-6-13 '); INSERT into purchase values (' E1 ', null, ' Or80 ', null); INSERT into purchase values (' E3 ', null, ' or90 ', null); INSERT into purchase values (' E3 ', ' S3 ', ' or91 ', ' 2001-7-13 '); CREATE TABLE provider (provider_id varchar), provider_name varchar (max), [address] varchar (+)) insert INTO provider VALUES (' S3 ', ' Zhenhua electronics factory ', ' Xian '); INSERT into provider values (' S4 ', ' Huadian subsidiaries ', ' Beijing '); INSERT into provider values (' S6 ', ' 607 plant ', ' (Zhengzhou '); INSERT into provider values (' S7 ', ' ai Hua Electronics Factory ', ' Beijing '); TRUNCATE TABLE provider--1. Retrieves all wage values from the employee relationship. Select salary from Employee;--2. Retrieving all records in a warehouse relationship select * from house;--3. Search for workers with a salary of more than 1230 Yuan Select employee_id from employee wher E salary>1230;--4. Retrieve which warehouses have workers with a salary of more than $1210. Select distinct house_id from employee where salary>1210;--5. The employee who works in the warehouse "WH1" or "WH2" and has a salary of less than 1250 yuan. Select employee_id from the employee where house_id in (' Wh1 ', ' wh2 ') and salary<1250;--6. Find out the number of employees with more than $1230 in wages and the city where they are located. Select Employee_id,city from Employee E, house h where e.house_id = h.house_id and salary>1230;--7. Find out the number of employees working in warehouses with an area greater than 400 And the cities where these workers work. Select Employee_id,city from employee e join HousE h on e.house_id=h.house_id and area>400; --8. Which cities have at least one warehouse staff wages of 1250 yuan. Select city from house where house_id in (select house_id from Employee where salary=1250),--9. Information about warehouses where all employees are paid more than $1210. SELECT * from house where house_id in (select house_id from Employee where salary>1210);  _id in (select house_id from Employee E1 where 1210 < All (select salary from employee E2 where e1.house_id=e2.house_id )--10. Find all employees who earn the same salary as E4. SELECT * from the employee where salary = (select salary from employee where employee_id = ' E4 ') and employee_id! = ' E4 ';--11. Check The employee information is paid in the range of 1220 yuan to 1240 yuan. SELECT * FROM employee where salary between 1220 and 1240;--12. Retrieve all company information from supplier relationships, not factory or other supplier information. SELECT * from provider--13. Find out all supplier information not in Beijing. SELECT * from provider where address! = ' Beijing '--14. All employee information is retrieved in ascending order by employee's salary value. SELECT * FROM employee order by salary;--15. Sort by warehouse number, then sort by salary and export all employee information. SELECT * from the employee ORDER by house_id, salary;--16. Find out the number of supplier locations. Select COUNT (*), address from provider Group by ADDRESS;--17. Total Payroll for Payment Select SUM (Salary) from employee;--18. The total wage of the warehouse workers in Beijing and Shanghai Select SUM (Salary) from employee where  house_id in (select house_id from house where city in (' Beijing ', ' Shanghai '))--19 the average area of the warehouse where all employees pay more than $1210 Select Avg. where house_id in (select house_id from Employee E1 where 1210 < All (select salary from employee E2 where e1.house_id= e2.house_id);--20. For the maximum wage value of the staff working in the WH2 warehouse select MAX (Salary) from employee where house_id= ' wh2 ';--21. Average wage for workers in each warehouse select AVG (Salary), house_id from the employee group by house_id--22. The average wage for each warehouse of at least two employees. Select AVG (Salary), house_id from employee GROUP by HOUSE_ID have COUNT (house_id) >1--23. Find a purchase order form for a vendor that has not been identified select purchase_id from purchase where provider_id is null--24. Lists the purchase order information for the vendor that has been identified select * from purchase where provider_id are not nu ll;--25. Query Vendor Name Select provider_name from Provider--26. Add a new field total amount in the order form to indicate the total amount that should be paid to complete the purchase order. ALTER TABLE purchase add Sum_money varchar (max);--27. Lists the order form information for each employee with the highest total amount. SELECT * from purchase where Sum_money in(select MAX (Sum_money) from purchase group by employee_id)--28. Retrieve information about warehouses in which warehouses are not yet workers select * from house where house_id not I N (select house_id from Employee)--29. Retrieves information about which warehouses have at least one worker's warehouse select* from house where house_id in (select house_id from EM Ployee)--30. Retrieves the warehouse number of a worker's salary greater than or equal to the wages of any worker in the WH1 warehouse select *, house_id from employee where salary >=any (select salary from emp Loyee where house_id= ' wh1 ') and house_id!= ' Wh1 '--31. Retrieve the warehouse number where the employee's salary is greater than or equal to the wages of all the employees in the warehouse of WH1. Select *,house_id from Employee where salary >=all (select salary from employee where house_id= ' wh1 ') and house_id!= ' WH 1 '

SQL Warehouse Management Exercises