WRITE OUT THE SQL COMMANDS NEEDED TO LIST OR RETRIEVE THE BELOW INFORMATION (use
ID: 3879786 • Letter: W
Question
WRITE OUT THE SQL COMMANDS NEEDED TO LIST OR RETRIEVE THE BELOW INFORMATION (use SELECT, FROM, WHERE, JOIN, ON, etc) - write the full SQL commands
7. Consider the General Hardware Corp. relational database. Using the informal relational command language described in this lesson, write commands to:
a. List the product name and unit price of all of the products.
b. List the employee names and titles of all the employees of customer 2198.
c. Retrieve the record for office number 1284.
d. Retrieve the records for customers headquartered in Los Angeles.
e. Find the size of office number 1209.
f. Find the name of the salesperson assigned to office number 1209.
g. List the product name and quantity sold of each product sold by salesperson 361.
WRITE OUT THE SQL COMMANDS NEEDED TO LIST OR RETRIEVE THE BELOW INFORMATION (use SELECT, FROM, WHERE, JOIN, ON, etc) - write the full SQL commands
8. Consider the General Hardware Corp. relational database and the data stored in it, as shown in Figure 6.17. Find the answer using SQL commands to each of the following queries:
a. Select rows from the CUSTOMER EMPLOYEE relation in which Customer Number = 2198.
b. Select rows from the CUSTOMER EMPLOYEE relation in which Customer Number = 2198. Project Employee Number and Employee Name over that result.
c. Select rows from the PRODUCT relation in which Product Number = 21765.
d. Select rows from the PRODUCT relation in which Product Number = 21765. Project Unit Price over that result.
e. Join the SALESPERSON and CUSTOMER relations using the Salesperson Number attribute of each as the join fields. Select rows from that result in which Salesperson Name = Baker. Project Customer Name over that result.
f. Join the PRODUCT relation and the SALES relation using the Product Number attribute of each as the join fields. Select rows in which Product Name = Pliers. Project Salesperson Number and Quantity over that result.
Explanation / Answer
Answers:
7)
a) select ProductName,UnitPrice from PRODUCT;
b)select EmployeeName,Title from CUSTOMEREMPLOYEE where CustomerNumber=2198;
c)select *from office where OfficeNumber=1284;
d)select *from CUSTOMER where HQCITY='Los Angeles'
e)select size from office where OfficeNumber=1209;
f)select SalesPersonName from SALESPERSON where OfficeNumber=1209;
g)SELECT PRODUCT.ProductName, SALES.Quantity
FROM PRODUCT
INNER JOIN SALES ON PRODUCT.ProductNumber= SALES.ProductNumber and SALES.SalesPersonNumber=137;
8)
a) select *from CUSTOMEREMPLOYEE where CustomerNumber=2198;
b) select EmployeeNumber,EmployeeName from CUSTOMEREMPLOYEE where CustomerNumber=2198;
c) select *from PRODUCT where ProductNumber=21765;
d) select UnitPrice from PRODUCT where ProductNumber=21765;
e) select CustomerName from CUSTOMER INNER JOIN SALESPERSON ON SALESPERSON.SalesPersonNumber=CUSTOMER.SalesPersonNumber and SALESPERSON.SalePersonName='Baker';
f)Select SalesPersonNumber and Quantity from SALESPERSON INNER JOIN PRODUCT ON PRODUCT.ProductNumber=SALES.ProductNumber and Product.ProductName='pilers';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.