ER圖+SQL語(yǔ)句練習(xí)題new

1、以下圖是企業(yè)訂單管理系統(tǒng)的E-R圖，提供對(duì)職工信息、客戶信息、供給商信息、產(chǎn)品信息、訂單信息、訂單明細(xì)進(jìn)行管理。pk描述為主鍵，fk描述為外鍵，外鍵約束通過(guò)圖下箭頭線進(jìn)行標(biāo)注。其中職員信息表中的sex取值為m或f,表示為男和女。訂單明細(xì)表OrderDetails供給商編號(hào) SupplierIDchar(5)供給商名稱 SupplierName varchar(50) 聯(lián)系 Phonevarchar(20)通信地址 Addressvarchar(50)郵政編碼 PostalCodevarchar(15)所在城市 Cityvarchar(20)供給商信息表Supplier<pk>訂單號(hào)

2、OrderlD 產(chǎn)品編號(hào)ProductID 單價(jià) UnitPrice 數(shù)量 Quantitychar(5)char(5) numeric(12,2) smallint<pk,fk1><pk,fk2>FK_PRODUCT.SUPPLIER產(chǎn)品信息表Products產(chǎn)品編號(hào)ProductIDchar(5)<pk>產(chǎn)品名稱ProductNamevarchar(50)供給商編號(hào)SupplierIDchar(5)<fk>FK ORDERDERODUCTSFK ORDERDET ORDERS訂單表Orders職員信息表Employees職工編號(hào)Employe

3、eIDchar(5)<pk>職工姓名 EmployeeName varchar(30)性別sexchar(1)出生日期BirthDatesmalldatetime入職時(shí)間HireDatesmalldatetime部門編號(hào)DepartmentIDchar(5)<fk>職稱Titlevarchar(20)通信地址Addressvarchar(50)聯(lián)系 LinkPhonevarchar(20)薪水SalarysmallmoneyFK_EMPLOYEEDEPTMENT部門信息表DepartmentFK ORDERS EMPLOYEE訂單號(hào)OrderlD 客戶編號(hào)Custome

4、rID 銷售人員編號(hào)EmployeeID 訂單日期 OrderDate 交付日期RequiredDate 訂單總金額Amountchar(5)char(5)char(5) smalldatetime smalldatetime numeric(12,2)<pk><fk1><fk2>FK ORDERSCUSTOMER部門編號(hào)DepartmentlD_char(5)_ <pk>部門名稱 DepartmentName char(50)客戶編號(hào) CustomerIDchar(5)客戶名稱CustomerNamevarchar(50)聯(lián)系 Phonevar

5、char(20)通信地址Addressvarchar(50)郵政編碼PostalCodevarchar(15)所在城市Cityvarchar(20)客戶信息表Customers<pk>創(chuàng)立腳本如下:/*=*/* Table:供給商信息表 Supplier*/*=*/create table Supplier (SupplierIDchar(5)not null,SupplierNamevarchar(50)not null,Phonevarchar(20)not null,Addressvarchar(50)null,PostalCodevarchar(15)null,City v

6、archar(20) null, constraint PK_Supplier primary key(SupplierID)go/*=*/* Table: 產(chǎn)品信息表 Products */ /*=*/ create table Products (ProductID char(5) not null,ProductName varchar(50) not null,SupplierID char(5)not null,constraint PK_Products primary key(ProductID),constraint FK_PRODUCTS_SUPPLIER foreign k

7、ey (SupplierID)references Supplier (SupplierID)go /*=*/* Table: 客戶信息表 Customers */ /*=*/ create table Customers (CustomerIDchar(5)not null,CustomerName varchar(50) not null,Phonevarchar(20)not null,Addressvarchar(50)null,PostalCodevarchar(15)null,Cityvarchar(20)null,(CustomerID)constraint PK_Custome

8、rs primary key go/*=*/* Table: 部門信息表 Department */*=*/ create table Department (not null, not null, (DepartmentID)DepartmentID char(5)DepartmentName char(50)constraint pk_Department primary key)go/*=*/* Table: 職員信息表 Employees */ /*=*/ create table Employees (EmployeeIDchar(5)not null,EmployeeNamevar

9、char(30)not null,sexchar(1)not null,BirthDatesmalldatetimenull,HireDatesmalldatetimenull,DepartmentIDchar(5)not null,Titlevarchar(20)null,Addressvarchar(50)null,LinkPhonevarchar(20)null,Salarysmallmoneynot null,constraint PK_Employees primary key(EmployeeID),constraint FK_EMPLOYEE_DEPTMENT foreign k

10、ey (DepartmentID) references Department (DepartmentID),constraint CK_Sex check (sex in ('M','F')go/*=*/ /* Table: 訂單表 Orders */ /*=*/ create table Orders (OrderIDchar(5)not null,CustomerIDchar(5)not null,EmployeeID char(5) not null,OrderDatesmalldatetimenot null,RequiredDatesmalldate

11、timenull,Amountnumeric(12,2)not null,constraint PK_Sales primary key(OrderID),constraint FK_ORDERS_CUSTOMER foreign key (CustomerID) references Customers (CustomerID),constraint FK_ORDERS_EMPLOYEE foreign key (EmployeeID) references Employees (EmployeeID)go/*=*/* Table: 訂單明細(xì)表 OrderDetails */ /*=*/ c

12、reate table OrderDetails (OrderIDchar(5)not null,ProductIDchar(5)not null,UnitPricenumeric(12,2)not null,Quantitysmallintnot null,constraint pk_saledetails primary key(OrderID, ProductID),constraint FK_ORDERDETAILS_ORDERS foreign key (OrderID) references Orders (OrderID),constraint FK_ORDERDETAILS_P

13、RODUCTS foreign key (ProductID) references Products (ProductID)Go根據(jù)上圖完成如下操作：-1 、查找職工的編號(hào)、部門和出生日期，如果出生日期為空值，顯示日期不詳, 并按部門排序輸出 , 日期格式為 yyyy-mm-dd 。/ 判斷空值和數(shù)據(jù)類型轉(zhuǎn)換函數(shù)-2 、查找與喻自強(qiáng)在同一個(gè)單位的職工 ID 、職工、性別、部門和職稱-3 、按部門進(jìn)行匯總，輸出部門名稱和統(tǒng)計(jì)部門總工資。-4 、查找商品名稱為 14 寸顯示器商品的銷售情況，顯示該商品的編號(hào)、銷售數(shù)量、單價(jià) 和金額-5 、在銷售明細(xì)表中按產(chǎn)品編號(hào)進(jìn)行匯總，統(tǒng)計(jì)每種產(chǎn)品的銷售數(shù)量和

14、金額-6 、按客戶編號(hào)統(tǒng)計(jì)每個(gè)客戶 1996 年的訂單總金額超過(guò) 50 萬(wàn)的客戶編號(hào)和訂單總金額- 如何插入日期？ 還可 year(orderdate)=1996!- - 7 、查找有銷售記錄的客戶編號(hào)、名稱和訂單總額-8 、查找在 1997 年中有銷售記錄的客戶編號(hào)、名稱和訂單總額-9 、查找一次銷售最大的銷售記錄-10 、查找至少有 3 次銷售的業(yè)務(wù)員名單和銷售日期-11 、用存在量詞 Exists 查找沒(méi)有訂貨記錄的客戶名稱-12 、使用左外連接查找每個(gè)客戶的客戶編號(hào)、名稱、訂貨日期、訂單金額、訂貨日期， 日期格式為 yyyy-mm-dd ，按客戶編號(hào)排序，同一客戶再按訂單降序排序輸出-

15、13 、查找產(chǎn)品“ 16M DRAM 的銷售情況，要求顯示相應(yīng)的銷售員的、性別，銷售日 期、銷售數(shù)量和金額，其中性別用男、女表示)-14 、查找每個(gè)人的銷售記錄，要求顯示銷售員的編號(hào)、性別、- 產(chǎn)品名稱、數(shù)量、單價(jià)、金額和銷售日期-15 、查找銷售金額最大的客戶名稱和總貨款-16 、查找銷售總額少于 1000 元的銷售員編號(hào)、和銷售額-17 、查找至少銷售了 3 種商品的客戶編號(hào)、客戶名稱、商品編號(hào)、商品名稱、數(shù)量和金 額-18 、查找至少與客戶“世界技術(shù)開(kāi)發(fā)公司銷售相同的客戶編號(hào)、名稱和商品編號(hào)、商 品名稱、數(shù)量和金額-19 、查找表中所有姓劉的職工的工號(hào)，部門，薪水-20、查找所有定單金額

16、高于 20000 的所有信息包括客戶編號(hào)和名稱-21、統(tǒng)計(jì)表中職工的薪水在 40000-60000 之間的人數(shù)-22、查詢表中的每個(gè)部門的職工的平均工資，但只查詢住址是上海市的職工-23、將表中住址為 " 上海市 " 的職工住址改為 " 北京市 "-24、查找業(yè)務(wù)部或會(huì)計(jì)部的女職工的根本信息。-25、顯示每種產(chǎn)品的銷售金額總和，并依銷售金額由大到小輸出。-26、選取編號(hào)界于 C0001 '和 C0004 '的客戶編號(hào)、客戶名稱、客戶地址。-27、計(jì)算出一共銷售了幾種產(chǎn)品。-28、將業(yè)務(wù)部職工的薪水上調(diào) 3%。-29、由 employee

17、表中查找出薪水最低的職工信息。-30II IIJ、使用join查詢客戶為客戶丙所購(gòu)貨物的客戶名稱","定單金額","定貨日期 號(hào)碼"-31 、由Orders表中查找出訂單金額大于“ E0013業(yè)務(wù)員在1996/10/15這天所接每一張訂單的金額的所有訂單。-32、計(jì)算 'P0001' 產(chǎn)品的平均銷售單價(jià)-33、找出公司女職工所接的定單-34、找出同一天進(jìn)入公司效勞的職工-35、找出目前業(yè)績(jī)超過(guò) 232000 元的職工編號(hào)和。-36 、查詢出 employee 表中所有女職工的平均工資和住址在上海市的所有女職工的 平均工資-37、

18、在 employee 表中查詢薪水超過(guò)職工平均薪水的職工信息。-38排序。、找出目前銷售業(yè)績(jī)超過(guò) 40000 元的業(yè)務(wù)員編號(hào)及銷售業(yè)績(jī)， 并按銷售業(yè)績(jī)從大到小-39、找出公司男業(yè)務(wù)員所接且訂單金額超過(guò)2000 元的訂單號(hào)及訂單金額。-40 、查詢 Orders 表中訂單金額最高的訂單號(hào)及訂單金額。-41 、查詢?cè)诿繌堄唵沃杏嗁?gòu)金額超過(guò) 24000 元的客戶名及其地址。-42 、求出每位客戶的總訂購(gòu)金額， 顯示出客戶號(hào)及總訂購(gòu)金額， 并按總訂購(gòu)金額降序排 列。-43 、求每位客戶訂購(gòu)的每種產(chǎn)品的總數(shù)量及平均單價(jià)， 并按客戶號(hào)， 產(chǎn)品號(hào)從小到大排 列。-44 、查詢訂購(gòu)了三種以上產(chǎn)品的訂單號(hào)。-45 、查詢訂購(gòu)的產(chǎn)品至少包含了訂單 10003 中所訂購(gòu)產(chǎn)品的訂單。-46 、在 Orders 表中查找出訂單金額大于“ E0013 業(yè)務(wù)員在 1996/11/10 這天所接每一 張訂單的金額的所有訂單，并顯示承接這些訂單的業(yè)務(wù)員和該訂單的金額。-47 、查詢末承接業(yè)務(wù)的職工的信息。-48 、查詢來(lái)自上海市的客戶的，