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: 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.
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:
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 with 4 images
