ER图+SQL语句练习题new

1下图是企业订单管理系统的E-R图，提供对职工信息、客户信息、供应商信息、产品信息、订单信息、订单明细进行管理。pk描述为主键，fk描述为外键，外键约束通过图下箭头线进行标注。其中职员信息表中的sex取值为m或f，表示为男和女。FK_ORDERDET_ORDERSFK_ORDERDET_PRODUCTSFK_ORDERS_CUSTOMERFK_ORDERS_EMPLOYEEFK_EMPLOYEE_DEPTMENTFK_PRODUCT_SUPPLIER客户信息表Customers客户编号CustomerID客户名称CustomerName联系电话Phone通信地址Address邮政编码PostalCode所在城市Citychar(5)varchar(50)varchar(20)varchar(50)varchar(15)varchar(20)pk职员信息表Employees职工编号EmployeeID职工姓名EmployeeName性别sex出生日期BirthDate入职时间HireDate部门编号DepartmentID职称Title通信地址Address联系电话LinkPhone薪水Salarychar(5)varchar(30)char(1)smalldatetimesmalldatetimechar(5)varchar(20)varchar(50)varchar(20)smallmoneypkfk订单明细表OrderDetails订单号OrderID产品编号ProductID单价UnitPrice数量Quantitychar(5)char(5)numeric(12,2)smallintpk,fk1pk,fk2订单表Orders订单号OrderID客户编号CustomerID销售人员编号EmployeeID订单日期OrderDate交付日期RequiredDate订单总金额Amountchar(5)char(5)char(5)smalldatetimesmalldatetimenumeric(12,2)pkfk1fk2产品信息表Products产品编号ProductID产品名称ProductName供应商编号SupplierIDchar(5)varchar(50)char(5)pkfk供应商信息表Supplier供应商编号SupplierID供应商名称SupplierName联系电话Phone通信地址Address邮政编码PostalCode所在城市Citychar(5)varchar(50)varchar(20)varchar(50)varchar(15)varchar(20)pk部门信息表Department部门编号DepartmentID部门名称DepartmentNamechar(5)char(50)pk创建脚本如下：/*==============================================================*//*Table:供应商信息表Supplier*//*==============================================================*/createtableSupplier(SupplierIDchar(5)notnull,SupplierNamevarchar(50)notnull,2Phonevarchar(20)notnull,Addressvarchar(50)null,PostalCodevarchar(15)null,Cityvarchar(20)null,constraintPK__Supplierprimarykey(SupplierID))go/*==============================================================*//*Table:产品信息表Products*//*==============================================================*/createtableProducts(ProductIDchar(5)notnull,ProductNamevarchar(50)notnull,SupplierIDchar(5)notnull,constraintPK__Productsprimarykey(ProductID),constraintFK_PRODUCTS_SUPPLIERforeignkey(SupplierID)referencesSupplier(SupplierID))go/*==============================================================*//*Table:客户信息表Customers*//*==============================================================*/createtableCustomers(CustomerIDchar(5)notnull,CustomerNamevarchar(50)notnull,Phonevarchar(20)notnull,3Addressvarchar(50)null,PostalCodevarchar(15)null,Cityvarchar(20)null,constraintPK__Customersprimarykey(CustomerID))go/*==============================================================*//*Table:部门信息表Department*//*==============================================================*/createtableDepartment(DepartmentIDchar(5)notnull,DepartmentNamechar(50)notnull,constraintpk_Departmentprimarykey(DepartmentID))go/*==============================================================*//*Table:职员信息表Employees*//*==============================================================*/createtableEmployees(EmployeeIDchar(5)notnull,EmployeeNamevarchar(30)notnull,sexchar(1)notnull,BirthDatesmalldatetimenull,HireDatesmalldatetimenull,DepartmentIDchar(5)notnull,Titlevarchar(20)null,Addressvarchar(50)null,LinkPhonevarchar(20)null,4Salarysmallmoneynotnull,constraintPK__Employeesprimarykey(EmployeeID),constraintFK_EMPLOYEE_DEPTMENTforeignkey(DepartmentID)referencesDepartment(DepartmentID),constraintCK_Sexcheck(sexin('M','F')))go/*==============================================================*//*Table:订单表Orders*//*==============================================================*/createtableOrders(OrderIDchar(5)notnull,CustomerIDchar(5)notnull,EmployeeIDchar(5)notnull,OrderDatesmalldatetimenotnull,RequiredDatesmalldatetimenull,Amountnumeric(12,2)notnull,constraintPK__Salesprimarykey(OrderID),constraintFK_ORDERS_CUSTOMERforeignkey(CustomerID)referencesCustomers(CustomerID),constraintFK_ORDERS_EMPLOYEEforeignkey(EmployeeID)referencesEmployees(EmployeeID))go/*==============================================================*//*Table:订单明细表OrderDetails*//*==============================================================*/createtableOrderDetails(5OrderIDchar(5)notnull,ProductIDchar(5)notnull,UnitPricenumeric(12,2)notnull,Quantitysmallintnotnull,constraintpk_saledetailsprimarykey(OrderID,ProductID),constraintFK_ORDERDETAILS_ORDERSforeignkey(OrderID)referencesOrders(OrderID),constraintFK_ORDERDETAILS_PRODUCTSforeignkey(ProductID)referencesProducts(ProductID))Go根据上图完成如下操作：6--1、查找员工的编号、姓名、部门和出生日期，如果出生日期为空值，显示日期不详,并按部门排序输出,日期格式为yyyy-mm-dd。//判断空值和数据类型转换函数--2、查找与喻自强在同一个单位的员工ID、员工姓名、性别、部门和职称--3、按部门进行汇总，输出部门名称和统计部门总工资。--4、查找商品名称为14寸显示器商品的销售情况，显示该商品的编号、销售数量、单价和金额--5、在销售明细表中按产品编号进行汇总，统计每种产品的销售数量和金额--6、按客户编号统计每个客户1996年的订单总金额超过50万的客户编号和订单总金额--如何插入日期？？还可year(orderdate)=1996!!--7、查找有销售记录的客户编号、名称和订单总额--8、查找在1997年中有销售记录的客户编号、名称和订单总额--9、查找一次销售最大的销售记录--10、查找至少有3次销售的业务员名单和销售日期--11、用存在量词Exists查找没有订货记录的客户名称--12、使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额、订货日期，日期格式为yyyy-mm-dd，按客户编号排序，同一客户再按订单降序排序输出--13、查找产品“16MDRAM”的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用男、女表示)--14、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、--产品名称、数量、单价、金额和销售日期--15、查找销售金额最大的客户名称和总货款--16、查找销售总额少于1000元的销售员编号、姓名和销售额--17、查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额--18、查找