Database Fundamentals Lab 1 – SQL I Simple Queries
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Database Fundamentals (31271)
Lab 1 – SQL I
Simple Queries
Introduction
The following SQL exercises are based on Pine Valley Furniture Company (PVFC) case study as described in the reference book. You can see the initial ERD in Figure 2-22. Based on the business processes at PVFC, the following entity types have been identified:
• The company sells a number of different furniture products. These products are grouped into several product lines. The identifier for product is Product ID, whereas the identifier for product line is Product Line ID. We identify the following additional attributes for product: Product Description, Product Finish, and Product Standard Price. Another attribute for product line is the Product Line Name. A product line may group any number of products but must group at least one product. Each product must belong to exactly one product line.
• Customers submit orders for products. The identifier for each order is Order ID, and another attribute is Order Date. A customer may submit any number of orders. Each order is submitted by exactly one costumer. The identifier for a costumer is Customer ID. Other attributes include Customer Name, Customer Address, and Customer Postal Code.
• A given customer order must request at least one product and only one product per order line item. Any product sold by PVF may not appear on any order line item or may appear on one or more order line items. An attribute associated with each order line item is Ordered Quantity.
• PVF has established sales territories for its customers. Each customer may do business in any number of these sales territories or may not do business in any territory. A sales territory has one to many customers. The identifier for a sales territory is Territory ID and an attribute is Territory Name.
• PVF has several salespersons. The identifier for salespersons is Salesperson ID. Other attribute include Salesperson Name, Salesperson Telephone, and Salesperson Fax. A sales person serves exactly one sales territory. Each territory serves by one or more salespersons.
• Each product assembled from specified quantity of one or more raw materials. The identifier for the raw material entity is Material ID. Other attributes include Unite Of Measure, Material Name, Material Standard Cost. Each material assembled into one or more products, using a specified quantity of the raw material for each product.
• Raw materials are supplied by vendors. The identifier for a vendor is Vendor ID. Other attributes include Vendor Name and Vendor Address. Each raw material can be supplied by one or more vendors. A vendor may supply any number of raw materials or may not supply any raw materials to PVF. Supply Unit Price is the unit price at which a particular vendor supplies a particular raw material.
• PVF has established a number of work centres. The identifier for a work center is Work Center ID. Another attribute is Work Center Location. Each product is produced in one or more work centers. A work center may be used to produce any number of products or may not be used to produce any products.
• The company has more than 100 employees. The identifier for employee is Employee ID. Other attributes include Employee Name, Employee Address, and Skill. An employee may have more than one skill. Each employee may work in one or more work centers. A work center must have at least one employee woking in that center but may have any number of employee. A skill may be possessed by more than one employee or possibly no employees.
• Each employee has exactly one supervisor; however, a manager has no supervisor. An employee who is a supervisor may supervise any number of employees, but not all employees are supervisors.
ERD
Database
PVFC Database Tables are as follows:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Customer_T
(CustomerID NUMERIC(4) NOT NULL,
CustomerName VARCHAR(25) ,
CustomerAddress VARCHAR(30) ,
CustomerCity VARCHAR(20) ,
CustomerState CHAR(2) ,
CustomerPostalCode VARCHAR(10) ,
CONSTRAINT Customer_PK PRIMARY KEY (CustomerID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Territory_T
(TerritoryID NUMERIC(4) NOT NULL,
TerritoryName VARCHAR(50) ,
CONSTRAINT Territory_PK PRIMARY KEY (TerritoryID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE DoesBusinessIn_T
(CustomerID NUMERIC(4) NOT NULL,
TerritoryID NUMERIC(4) NOT NULL,
CONSTRAINT DoesBusinessIn_PK PRIMARY KEY (CustomerID, TerritoryID),
CONSTRAINT DoesBusinessIn_FK1 FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID),
CONSTRAINT DoesBusinessIn_FK2 FOREIGN KEY (TerritoryID) REFERENCES Territory_T(TerritoryID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Salesperson_T
(SalespersonID NUMERIC(4) NOT NULL,
SalespersonName VARCHAR(25) ,
SalespersonTelephone VARCHAR(50) ,
SalespersonFax VARCHAR(50) ,
SalespersonAddress VARCHAR(30) ,
SalespersonCity VARCHAR(20) ,
SalespersonState CHAR(2) ,
SalespersonZip VARCHAR(20) ,
SalesTerritoryID NUMERIC(4) ,
CONSTRAINT Salesperson_PK PRIMARY KEY (SalespersonID),
CONSTRAINT Salesperson_FK1 FOREIGN KEY (SalesTerritoryID) REFERENCES Territory_T(TerritoryID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Skill_T
(SkillID VARCHAR(12) NOT NULL,
SkillDescription VARCHAR(30) ,
CONSTRAINT Skill_PK PRIMARY KEY (SkillID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Employee_T
(EmployeeID VARCHAR(10) NOT NULL,
EmployeeName VARCHAR(25) ,
EmployeeAddress VARCHAR(30) ,
EmployeeCity VARCHAR(20) ,
EmployeeState CHAR(2) ,
EmployeeZip VARCHAR(10) ,
EmployeeBirthDate DATE ,
EmployeeDateHired DATE ,
EmployeeSupervisor VARCHAR(10) ,
CONSTRAINT Employee_PK PRIMARY KEY (EmployeeID),
CONSTRAINT Employeer_FK FOREIGN KEY (EmployeeSupervisor) REFERENCES Employee_T(EmployeeID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE EmployeeSkills_TCONSTRAINT EmployeeSkills_PK PRIMARY KEY (EmployeeID, SkillID),(EmployeeID VARCHAR(10) NOT NULL,
SkillID VARCHAR(12) NOT NULL,
QualifyDate DATE ,
CONSTRAINT EmployeeSkills_FK1 FOREIGN KEY (EmployeeID) REFERENCES Employee_T(EmployeeID),
CONSTRAINT EmployeeSkills_FK2 FOREIGN KEY (SkillID) REFERENCES Skill_T(SkillID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE WorkCenter_T
(WorkCenterID VARCHAR(12) NOT NULL,
WorkCenterLocation VARCHAR(30) ,
CONSTRAINT WorkCenter_PK PRIMARY KEY (WorkCenterID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE WorksIn_T
(EmployeeID VARCHAR(10) NOT NULL,
WorkCenterID VARCHAR(12) NOT NULL,
CONSTRAINT WorksIn_PK PRIMARY KEY (EmployeeID, WorkCenterID),
CONSTRAINT WorksIn_FK1 FOREIGN KEY (EmployeeID) REFERENCES Employee_T(EmployeeID),
CONSTRAINT WorksIn_FK2 FOREIGN KEY (WorkCenterID) REFERENCES WorkCenter_T(WorkCenterID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE ProductLine_T
(ProductLineID NUMERIC(4) NOT NULL,
ProductLineName VARCHAR(50) ,
CONSTRAINT ProductLine_PK PRIMARY KEY (ProductLineID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Product_T
(ProductID NUMERIC(4) NOT NULL,
ProductLineID NUMERIC(4) ,
ProductDescription VARCHAR(50) ,
ProductFinish VARCHAR(20) ,
ProductStandardPrice NUMERIC(6,2) ,
ProductOnHand NUMERIC(6) ,
CONSTRAINT Product_PK PRIMARY KEY (ProductID),
CONSTRAINT Product_FK1 FOREIGN KEY (ProductLineID) REFERENCES ProductLine_T(ProductLineID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE ProducedIn_T
(ProductID NUMERIC(4) NOT NULL,
WorkCenterID VARCHAR(12) NOT NULL,
CONSTRAINT ProducedInPK PRIMARY KEY (ProductID, WorkCenterID),
CONSTRAINT ProducedInFK1 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID),
CONSTRAINT ProducedInFK2 FOREIGN KEY (WorkCenterID) REFERENCES WorkCenter_T(WorkCenterID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE CustomerShipAddress_T
(ShipAddressID NUMERIC(4) NOT NULL,
CustomerID NUMERIC(4) NOT NULL,
TerritoryID NUMERIC(4) NOT NULL,
ShipAddress VARCHAR(30) ,
ShipCity VARCHAR(20) ,
ShipState CHAR(2) ,
ShipZip VARCHAR(10) ,
ShipDirections VARCHAR(100) ,
CONSTRAINT CSA_PK PRIMARY KEY (ShipAddressID),
CONSTRAINT CSA_FK1 FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID),
CONSTRAINT CSA_FK2 FOREIGN KEY (TerritoryID) REFERENCES Territory_T(TerritoryID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Order_T
(OrderID NUMERIC(5) NOT NULL,
CustomerID NUMERIC(4) ,
OrderDate DATE ,
FulfillmentDate DATE ,
SalespersonID NUMERIC(4) ,
ShipAdrsID NUMERIC(4) ,
CONSTRAINT Order_PK PRIMARY KEY (OrderID),
CONSTRAINT Order_FK1 FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID),
CONSTRAINT Order_FK2 FOREIGN KEY (SalespersonID) REFERENCES Salesperson_T(SalespersonID),
CONSTRAINT Order_FK3 FOREIGN KEY (ShipAdrsID) REFERENCES CustomerShipAddress_T(ShipAddressID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE OrderLine_T
(OrderLineID NUMERIC(4) NOT NULL,
OrderID NUMERIC(5) NOT NULL,
ProductID NUMERIC(4) NOT NULL,
OrderedQuantity NUMERIC(10) ,
CONSTRAINT OrderLine_PK PRIMARY KEY (OrderLineID),
CONSTRAINT OrderLine_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID),
CONSTRAINT OrderLine_FK2 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE PaymentType_T
(PaymentTypeID VARCHAR(50) NOT NULL,
TypeDescription VARCHAR(50) NOT NULL,
CONSTRAINT PaymentType_PK PRIMARY KEY (PaymentTypeID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Payment_T
(PaymentID NUMERIC(5) NOT NULL,
OrderID NUMERIC(5) NOT NULL,
PaymentTypeID VARCHAR(50) NOT NULL,
PaymentDate DATE ,
PaymentAmount NUMERIC(6,2) ,
PaymentComment VARCHAR(255) ,
CONSTRAINT Payment_PK PRIMARY KEY (PaymentID),
CONSTRAINT Payment_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID),
CONSTRAINT Payment_FK2 FOREIGN KEY (PaymentTypeID) REFERENCES PaymentType_T(PaymentTypeID));
CREATE TABLE Shipped_T
(OrderLineId NUMERIC(4) NOT NULL,
ShippedQuantity NUMERIC(10) NOT NULL,
ShippedDate DATE,
CONSTRAINT Shipped_PK PRIMARY KEY (OrderLineId),
CONSTRAINT Shipped_FK1 FOREIGN KEY (OrderLineId) REFERENCES OrderLine_T(OrderLineID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Vendor_T
(VendorID NUMERIC(4) NOT NULL,
VendorName VARCHAR(25) ,
VendorAddress VARCHAR(30) ,
VendorCity VARCHAR(20) ,
VendorState CHAR(2) ,
VendorZipcode VARCHAR(50) ,
VendorPhone VARCHAR(12) ,
VendorFax VARCHAR(12) ,
VendorContact VARCHAR(50) ,
VendorTaxIDNumber VARCHAR(50) ,
CONSTRAINT Vendor_PK PRIMARY KEY (VendorID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE RawMaterial_T
(MaterialID VARCHAR(12) NOT NULL,
MaterialName VARCHAR(30) ,
Thickness VARCHAR(50) ,
Width VARCHAR(50) ,
MatSize VARCHAR(50) ,
Material VARCHAR(15) ,
MaterialStandardPrice NUMERIC(6,2) ,
UnitOfMeasure VARCHAR(15) ,
MaterialType VARCHAR(50),
CONSTRAINT RawMaterial_PK PRIMARY KEY (MaterialID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Uses_T
(MaterialID VARCHAR(12) NOT NULL,
ProductID NUMERIC(4) NOT NULL,
QuantityRequired NUMERIC(5) ,
CONSTRAINT Uses_PK PRIMARY KEY (ProductID, MaterialID),
CONSTRAINT Uses_FK1 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID),
CONSTRAINT Uses_FK2 FOREIGN KEY (MaterialID) REFERENCES RawMaterial_T(MaterialID));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Supplies_T
(VendorID NUMERIC(4) NOT NULL,
MaterialID VARCHAR(12) NOT NULL,
SupplyUnitPrice NUMERIC(6,2) ,
CONSTRAINT Supplies_PK PRIMARY KEY (VendorID, MaterialID),
CONSTRAINT Supplies_FK1 FOREIGN KEY (MaterialID) REFERENCES RawMaterial_T(MaterialID),
CONSTRAINT Supplies_FK2 FOREIGN KEY (VendorID) REFERENCES Vendor_T(VendorID));;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Exercises
Load your database with the Pine Valley Furniture Company data and run the following queries:
1- List all products in alphabetic order.
2- List all products ordered by PRICE in descending order, and products in ascending order.
3- How many work centers does Pine Valley have?
4- Where are the work centers located?
5- List the employees whose names begin with an R.
6- Which employees were hired during 1999.
7- List the customers who live in California or Washington. Order them by zip code, from high to low.
8- Display the product line ID and the average standard price for all products in each product line.
9- For each customer, list the CustomerID and total number of orders.
10- For each customer, list the CustomerID and total number of orders placed in March 2010.
11- List territories and the number of their customers?
12- For each salespersonid shows the number of orders.
2023-06-21