SALESPERSON (Name, Age, Salary) ORDER (Number, Customer Name, Salesperson Name, Amount) CUSTOMER (Name, City, Industry Type)
The questions in this group refer to the following three relations:
SALESPERSON (Name, Age, Salary)
ORDER (Number, Customer Name, Salesperson Name, Amount)
CUSTOMER (Name, City, Industry Type)
SALESPERSON
Name |
Age |
Salary |
Abel |
63 |
120,000 |
Baker |
38 |
42,000 |
Jones |
26 |
36,000 |
Murphy |
42 |
50,000 |
Zenith |
59 |
118,000 |
Kobad |
27 |
34,000 |
ORDER
Number |
CustName |
SalespersonName |
Amount |
100 |
Abernathy Construction |
Zenith |
560 |
200 |
Abernathy Construction |
Jones |
1800 |
300 |
Manchester Lumber |
Abel |
480 |
400 |
Amalgamated Housing |
Abel |
2500 |
500 |
Abernathy Construction |
Murphy |
6000 |
600 |
Tri-City Builders |
Abel |
700 |
700 |
Manchester Lumber |
Jones |
150 |
CUSTOMER
Name |
City |
Industry Type |
Abernathy Construction |
Willow |
B |
Manchester Lumber |
Manchester |
F |
Tri-City Builders |
Memphis |
B |
Amalgamated Housing |
Memphis |
B |
Use the data in those tables and show the SQL statements to display or modify data as indicated in the following questions:
- Show the ages and salaries of all salespeople.
2.Show the ages and salaries of all salespeople but omit duplicates.
3.Show the names of all salespeople under 30 years old.
4.Show the names of all salespeople who can earn more than $49,999 and less than $100,000.
5.Show the names and salary of all salespeople who do not have an order with Abernathy Construction, in ascending order of salary.
6.Compute the number of orders.
7.Compute the number of orders for each salesperson, considering only orders for an amount exceeding 500.
8.Show the industry type and ages of the salespeople of all orders for companies in Memphis.
9.Show the names of salespeople who have two or more orders.
10.Show an SQL statement to delete customer ABERNATHY CONSTRUCTION.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps