数据库上机试题答案

Database system theory course: experiment

Textbook: the first course in database systems DBMS:Sqlserver sample database: northwind Majors: MIS08 of KMUST office:

Instructor:chenyu Email:cykust@yahoo.cn

Introduces:

CustomersPKCustomerCustomerDemoPK,FK2PK,FK1CustomerIDCustomerTypeIDI1I4I3I2CustomerIDCompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFaxEmployeesOrdersPKFK1,I2,I1FK2,I3,I4I5OrderIDCustomerIDEmployeeIDOrderDateRequiredDateShippedDateShipViaFreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountryPKI1EmployeeIDLastNameFirstNameTitleTitleOfCourtesyBirthDateHireDateAddressCityRegionPostalCodeCountryHomePhoneExtensionPhotoNotesReportsToPhotoPathCustomerDemographicsPKCustomerTypeIDCustomerDescPKShippersShipperIDCompanyNamePhoneEmployeeTerritoriesPK,FK1PK,FK2EmployeeIDTerritoryIDI6FK3,I7I2I8FK1TerritoriesPKTerritoryIDTerritoryDescriptionRegionIDOrder DetailsPK,FK1,I2,I1PK,FK2,I4,I3OrderIDProductIDUnitPriceQuantityDiscountFK1RegionPKRegionIDRegionDescriptionCategoriesPKI1CategoryIDCategoryNameDescriptionPictureSuppliersPKI1SupplierIDPKCompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFaxHomePageI3FK2,I5,I4FK1,I1,I2ProductsProductIDProductNameSupplierIDCategoryIDQuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinuedI2

DBMS: TABLES: VIEWS:

TOTAL COLUMNS: TOTAL INDEXS:

Microsoft SQL Server 13 0 88 26

1

TOTAL FOREIGN KEY: 13

TABLE COLUMN INDEX FOREIGN KEY Territories 3 0 1 Suppliers 12 2 0 Shippers 3 0 0 Region 2 0 0 Products 10 5 2 Orders 14 8 3 Order Details

5 4 2 EmployeeTerritories 2 0 2 Employees 18 2 1 Customers

11 4 0 CustomerDemographics 2 0 0 CustomerCustomerDemo 2 0 2 Categories

4

1

0

TABLE 列 记录 FK 说明 Territories 3 53 1 销售区域（区域码、描述、所属地区码） Suppliers 12 29 0 供货商（供货商码、名称、联系人、职位、地址、城市、地区、邮编、国家、电话、传真、公司主页） Shippers 3 3 0 货运公司（货运公司代码、名称、电话） Region 2 4 0 地区（地区码、名称） Products 10 77 2 产品（产品码、名称、供货商码、产品分类码、单位数量、产品单价、库存数量 、订货数量、最低库存量、是否停售） Orders 14 830 3 订单（订单号、顾客码、雇员码、订购日期、需要日期、送货日期、货运单位码、货运价、接受者、送货地址、送货城市、送货地区、邮编、国家） Order Details 5 2155 2 订单细项（订单号、产品码、产品单价、订购数量、折扣） EmployeeTerritories 2 49 2 雇员-地区（雇员码、雇员所负责区域码） Employees 18 9 1 雇员（雇员码、姓、名、职位、尊称、生日、雇佣日期、住址、城市、地区、邮编、国家、电话、分机号码、照片、描述、上级编码、照片存储路径） Customers 11 91 0 顾客（顾客码、名称、联系人、职位、地址、城市、地区、邮编、国家、电话、传真） CustomerDemographics 2 0 0 顾客类别（顾客码、顾客类别描述） CustomerCustomerDemo 2 0 2 顾客-类别（顾客码、顾客类别码） Categories 4 8 0 产品类别表（类别号、名称、描述、图片） 2

Contents: Part A SQL

1 DATA DEFINE /*define the data structure of student system,including create table clause,drop table clause,and alter table clause*/ 2 DATA MANIPUNATE

/*Find all of suppliers */ SELECT *

FROM suppliers;

/*Find all of products*/ SELECT *

FROM products;

/*Find all of customers*/ SELECT *

FROM customers;

/*Find all of employees*/ SELECT *

FROM employees;

/*Find all of orders*/ SELECT * FROM orders;

/*Find all of order details*/ SELECT *

FROM order details

/*Find all of shippers*/ SELECT *

FROM shippers;

/*Find all of Categories*/ SELECT Categories.* FROM Categories;

/*Find the all of citys where supplier come from */ SELECT city FROM suppliers;

/*Find the catefories of products*/ SELECT categoryID FROM products;

/*Find lastname,firstname,birthday and age of every employees*/ SELECT lastname,firstname, year(Date())-year(BirthDate) AS age FROM employees;

/*spool the special format ,such as, NOW INTRODUCE company name + firstname+lastname +title TO

3

ATTEND THE KUNMING IMPORT OR EXPORT COMMODITY EXPO IN JUN,2006. AA Trade Corporation.*/

/*******like / not like*/

/*Find the supplier who live in London*/ SELECT *

FROM suppliers

WHERE city='London';

/*Find the supplier whose postalcode begin with number 7*/ SELECT *

FROM Suppliers

WHERE Postalcode like'7*';

/*Find the supplier whose postalcode include number 1 */ SELECT *

FROM suppliers

WHERE postalcode like '*1*';

/*Find the supplier whose postalcode is number 1 in 3 position from the left */ SELECT *

FROM suppliers

WHERE postalcode like '??1*';

/*Find the supplier whose postalcode isnot number 1 in 3 position from the left*/ SELECT *

FROM suppliers

WHERE postalcode not like '??1*';

/*Find the supplier whose address include the character ‘_’*/ SELECT *

FROM suppliers

WHERE address like '*-*';

/*Find the empolyee whose first name is only 4 character*/ SELECT *

FROM employees

WHERE firstname like '????'; /*in /not in */

/*Find the supplier whose address locate in London or Paris*/ SELECT *

FROM suppliers

WHERE city in (‘London’,’Paris’);

/*Find the supplier whose region is NSW or MA*/ SELECT *

FROM suppliers

WHERE region in ('NSW','MA');

/*Find the product which categoryid are 1 or 2 or 3 */ SELECT *

FROM products

WHERE categoryid in (1,2,3);

4

/*Find the product which categoryid arenot 1 or 2 or 3 */ SELECT *

FROM products

WHERE categoryid in (1,2,3);

/*between and /not between and */

/*Find the product which unitsinstock is between 10 and 20*/ SELECT *

FROM products

WHERE unitsinstock between 10 and 20;

/*Find the supplier which supplierid is between 5 and 9*/ SELECT *

FROM suppliers

WHERE supplierid between 5 and 9;

/*Find the supplier which supplierid isnot between 5 and 9*/ SELECT *

FROM suppliers

WHERE supplierid not between 5 and 9;

/*>, <, <>, >=, =<*/

/*Find the product which unitsinstock is more than 10*/ SELECT *

FROM products

WHERE unitsinstock > 10;

/*Find the empolyee who age is less than 30*/ SELECT*

FROM employees

WHERE year(Date())-year(BirthDate) < 30;

/*Find the empolyee who age isnot less than 30*/ SELECT*

FROM employees

WHERE year(Date())-year(BirthDate) >=30;

/*is null /is not null*/

/*Find the supplier whose homepage is null*/ SELECT*

FROM suppliers

WHERE homepage is null;

/*Find the supplier whose homepage isnot null*/ SELECT*

FROM suppliers

WHERE homepage is not null;

5

本文来源：https://www.bwwdw.com/article/3g82.html