[mysql] 期中考

作業一

1.        選取部門資料表中的部門編號及部門名稱 (Departments)

select DepartmentID, name from Departments;

2.        選取部門資料表中所有欄位(Departments)

select * from Departments;

3.        將DepartmentID欄位命名其別名為Department(Departments)

Select DepartmentID As Department from Departments;

4.        將DepartmentID欄位命名其別名為部門代號，Name命名為部門名稱(Departments)

Select DepartmentID As '部門代號', Name As '部門名稱'from Departments;

5.        選取部門資料表中的位置資訊，重複只出現一次(Departments)

Select distinct location from departments;

6.        列出部門代號為10的資料(Departments)

select * from departments where departmentid=10;

7.        列出部門代號為20的所有員工姓名資料(Employees)

select Name from Employees where departmentid=20;

8.        列出佣金未知的業務員姓名資料(Employees)

Select name, commission from employees where commission is Null;

9.        要列出職務為業務且稱呼為先生的男性經理之員工編號、姓名、年薪和頭銜(Employees)

select employeeID, name, Salary, title from employees where title='業務' and titleofcourtesy='先生';

10.     要列出職務為業務或佣金不是未知的員工的員工編號、姓名、年薪、頭銜和佣金(Employees)

select employeeID, name, Salary, commission, title from employees where title='業務' or commission is not null;

11.     列出非業務的員工姓名和頭銜(Employees)

Select name, title from employees where title != '業務';

12.     列出頭銜有經理的員工姓名和頭銜(Employees)\

Select name, title from employees where title like '%經理';

13.     列出姓氏為’張’的所有員工的員工姓名和頭銜(Employees)

Select name, title from employees where name like '張%';

14.     列出員工英文姓名為M開頭且長度為四的員工編號、姓名及英文姓名(Employees)

select EmployeeID ,name ,engname title from employees where engname like 'M___';

15.     列出員工英文姓名中含有’ar’這兩個字元的員工編號、姓名及英文姓名(Employees)

select EmployeeID ,name ,engname from employees where engname like '%ar%';

16.     列出員工的年薪資介於420000~520000之間的員工編號、姓名及年薪資(Employees)

select employeeid ,name,salary from employees where salary between 420000 and 520000;

17.     請用in的方式列出員工代號為1002及1005的部屬烈表，包括：員工編號、姓名、上司的員工編號(Employees)

select SupervisorID,employeeid ,name,salary from employees where SupervisorID in ('1002','1005');

18.     請用in的方式列出員工姓名’Mary’、’Marua’、’Mike’和’Linda’等人的員工編號、姓名及英文姓名(Employees)

Select SupervisorID,employeeid ,engname,name,salary from employees where engname in ('Mary', 'marua', 'mike', 'linda')

19.     選取部門資料表中所有欄位，並依照DepartmentID作由大到小的排列(Departments)

select * from departments order by 'departmentid' desc;

20.     列出部門代號、員工編號、姓名及英文姓名，並依照部門代號由小到大及員工編號由小到大的方式排列(Employees)

select departmentid , employeeid, name,engname from employees order by departmentid , employeeid asc;

作業二

2.      列出部門編號10的所有員工姓名、生日、生年、生月與日子部份等資料(Employees)

select name, birthdate,  left(birthdate,4) as year, mid(birthdate,6,2) as month, right(birthdate,2) as day from employees where departmentid=10;

3.      列出部門編號10的員工編號、員工姓名、英文名字、中英姓名組合名稱(Employees)

select employeeid, name, engname, concat(name,'-',engname) as name_eng from employees where departmentid=10;

7.      取得部門編號為10的員工總人數、最低/最高/總計/平均薪資及標準差(Employees)

select count(*) as '員工數' , min(salary) as '最低薪資', max(salary) as '最高薪資', sum(salary) as '總計', avg(salary) as '平均薪資', stddev(salary) as '標準差' from employees where departmentid=10;

9.      取得各部門員工的總人數、最低/最高/總計/平均薪資及標準差(Employees)

select departmentid as '編號', count(*) as '員工數', min(salary) as '最低薪資', max(salary) as '最高薪資', sum(salary) as '總計', avg(salary) as '平均薪資', stddev(salary) as '標準差' from employees group by departmentid;

10.取得各部門員工的總人數、最低/最高/總計/平均薪資及標準差，且限制部門員工總人數需達5人以上(Employees)

select departmentid as '編號', count(*) as '員工數', min(salary) as '最低薪資', max(salary) as '最高薪資', sum(salary) as '總計', avg(salary) as '平均薪資', stddev(salary) as '標準差'  from employees group by departmentid having count(*)>5;

11.計算員工的薪資(需將薪資與佣金合算) (Employees)

select employeeid, name, salary, ifnull(commission,0) as comm, salary+ifnull(commission,0) as sum from employees;

12.統計各部門的薪資(需將薪資與佣金合算) (Employees)

select departmentid, sum(salary), sum(ifnull(commission,0)) as comm, sum(salary+ifnull(commission,0)) as sum from employees group by departmentid;

 13.將Orders與Orderdetails兩資料表作natural join，並只顯示訂單編號小於等於10250(顯示訂單編號，客戶編號，員工編號，訂單日期)

select orders.orderid, customerid, employeeid, orderdate from orders natural join orderdetails where orders.orderid<=10250;

14.將Orders與Orderdetails兩資料表作inner join，並只顯示訂單編號小於等於10250(顯示訂單編號，客戶編號，員工編號，訂單日期)

select orders.orderid, customerid, employeeid,orderdate from orders inner join orderdetails on orders.orderid=orderdetails.orderid where orders.orderid<=10250;

15.使用inner join選取部門的所有欄位及Employees中的EmployeeID、Name、EngName (Departments, Employees)

select departments.*, employees.employeeid, employees.name, engname from departments inner join employees on departments.departmentid = employees.departmentid;

16.使用inner join選取部門的所有欄位及Employees中的EmployeeID、Name、EngName，且只顯示部門編號為10及依員工的英文名字作排序 (Departments, Employees)

select departments.*, employees.employeeid, employees.name, engname from departments inner join employees on departments.departmentid = employees.departmentid where departments.departmentid=10 order by engname;

17.同上題，但Departments資料表用D表示，Employees資料表用E表示(Departments, Employees)

select D.*, E.employeeid, E.name, engname from departments D inner join employees E on D.departmentid = E.departmentid where D.departmentid =10 order by engname;

18.使用inner join選取Orders資料表中的訂單編號、訂單日期，OrderDetails資料表中的產品編號、產品單價、數量及Products資料表中的產品名稱，並只顯示訂單編號小於等於10250 (Orders, OrderDetails, Products)

select O.orderid, O.orderdate, D.productid,P.productname, D.unitprice, D.quantity from products P inner join (orders O inner join orderdetails D on O.orderid=D.orderid) on P.productid=D.productid where O.orderid<=10250;

19.列出各部門員工資料(不論是否有員工均要列出)(部門欄位全選，員工編號、員工姓名及英語名字)(Departments, Employees)

Select D.*, E.employeeid, E.name, engname from departments D left outer join employees E on D.departmentid=E.departmentid;

20.請用cross join作第17題(Departments, Employees)

Select D.*, E.employeeid, E.name, engname from departments D, employees E where D.departmentid=E.departmentid and D.departmentid=10 order by engname;

21.請用cross join作第18題(Orders, OrderDetails, Products)

select O.orderid, O.orderdate, D.productid, P.productname, D.unitprice, D.quantity from products P, orders O, orderdetails D where (O.orderid=D.orderid and P.productid=D.productid) and O.orderid<=10250;

22.查詢和員工王明亮同部門的其他員工編號和姓名(使用cross join)(Employees, Employees)

Select E1.departmentid, E1.employeeid, E1.name from employees E1, employees E2 where E1.departmentid=E2.departmentid and E2.name='王明亮' and E1.employeeid<>E2.employeeid;

23.查詢薪資比王明亮高的員工之員工編號、員工姓名和薪資(使用cross join)(Employees, Employees)

select E1.departmentid, E1.employeeid, E1.name, E1.salary from employees E1, employees E2 where E1.salary>E2.salary and E2.name='王明亮';

作業三

1.      查詢居住於台北市而且是位於同一個行政區的員工(列出員工編號、員工姓名及行政區)(Employees, Employees)

方法一：

Select Employeeid, Name, Region from Employees where City=”台北市” order by Region, Employeeid;

2.      查詢產品名稱為”牛奶”的訂單明細資料(Orderdetails, Products)

Select D.* from Orderdetails D, Products P where D.Productid = P.Productid and P.ProductName=”牛奶”;

3.      查詢訂單編號小於等於10250之出貨貨品編號或貨品名稱(Orderdetails, Products)

Select ProductID, ProductName from Products where ProductID IN (Select ProductID from Orderdetails where OrderID<=10250);

4.      查詢訂單編號<=10260且產品名稱含有”起司”字眼的產品訂單明細資料，並將資料依照產品編號排序(Orderdetails, Products)

Select * from Orderdetails where OrderID<=10260 and ProductID IN (Select ProductID from Products where ProductName like ‘%起司%’) order by ProductID;

5.      查詢訂單編號<=10260且產品名稱含有”起司”字眼的產品訂單明細資料，並將資料依照產品編號排序(訂單編號、產品編號、產品名稱、單價、數量)(Orderdetails, Products)

Select D.OrderID, D.ProductID, P.ProductName, D.UnitPrice, D.Quantity from Orderdetails D, Products P where D.ProductID = P.ProductID and D.OrderID<=10260 and D.ProductID IN (Select ProductID from Products where ProductName like ‘%起司%’) order by D.ProductID;

6.      查詢訂單明細中產品出貨次數恰為產品編號1067、1068、1069、1070、1072及1073出貨次數的兩倍之所有產品編號、產品名稱及出貨次數。(Orderdetails, Products)

Select D.ProductID, Productname, count(*) from Orderdetails D, Products P where D.ProductID=P.ProductID group by D.ProductID, Productname Having count(*) in (Select 2*count(*) from Orderdetails where ProductID in (1067,1068, 1069, 1070, 1072, 1073) group by ProductID) ;

7.      查詢任何員工薪資大於任何職稱為”業務”的非業務員工之員工編號、姓名、職稱及薪資。(Employees)

Select EmployeeID, Name, Title, Salary from Employees where Salary > ANY (Select Salary from Employees where Title=’業務’) and Title <> ‘業務’;

8.      查詢所有薪資階高於部門10所有員工之薪資的員工編號、姓名、職稱及薪資。(Employees)

Select EmployeeID, Name, Title, Salary from Employees where DepartmentID<>10 and Salary > All(Select Salary from Employees where DepartmentID=10);

9.      查詢所有員工的任職部門不同於員工編號為1001,1010等人的員工編號、姓名、部門編號及部門名稱(Employees, Departments)

Select E.EmployeeID, E.Name, E.DepartmentID, D.Name from Employees E, Departments D where E.DepartmentID = D.DepartmentID and E.DepartmentID <> All(Select DepartmentID from Employees where EmployeeID in (1001,1010));