Query Assignment There are three queries below. Please write them up in proper S
ID: 3887216 • Letter: Q
Question
Query Assignment
There are three queries below. Please write them up in proper SLQ format.
Consider the following three tables, primary and foreign keys.
Table Name SalesPeople
Attribute Name Type Key Type
EmployeeNumber Number Primary Key
Name Character
JobTitle Character
Address Character
PhoneNumber Character
YearsInPosition Number
Table Name ProductDescription
Attribute Name Type Key Type
ProductNumber Number Primary Key
ProductName Character
ProductPrice Number
Table Name SalesOrder
Attribute Name Type Key Type
SalesOrderNumber Number Primary Key
ProductNumber Number Foreign Key
EmployeeNumber Number Foreign Key
SalesOrderDate Date
Assume that you draw up a new sales order for each product sold.
Develop the following queries:
a. All the Sales People with more than five years in position. (3 pts)
b. All the Products that cost more than $50. (3 pts)
c. All the Sales Orders sold by Sales People less than 3 years in the position. (3 pts)
Explanation / Answer
a)All the Sales People with more than five years in position.
select EmployeeNumber,Name,YearsInPosition from SalesPeople where YearsInPosition>5 ORDER BY YearsInPosition DESC;
b)All the Products that cost more than $50.
select * from ProductDescription where ProductPrice>$50;
c)All the Sales Orders sold by Sales People less than 3 years in the position.
select SO.SalesOrderNumber from SalesOrder SO, SalesPeople SP where SO.EmployeeNumber=SP.EmployeeNumber and YearsInPosition<3;