| Class Schedule |
Solutions to SQL problems (Lecture 7)
|
1 |
SELECT Age, Salary |
|
FROM Salesperson |
|
|
2 |
SELECT DISTINCT Age, Salary |
|
FROM Salesperson |
|
|
3 |
SELECT Name |
|
FROM Salesperson |
|
|
WHERE Age < 30 |
|
|
4 |
SELECT SalespersonName |
|
FROM Order |
|
|
WHERE CustName = 'Abernathy Construction' |
|
|
5 |
SELECT Name |
|
FROM Salesperson |
|
|
WHERESalary > 49999 AND Salary < 100000 |
|
|
6 |
SELECT Name |
|
FROM Salesperson |
|
|
WHERE Age BETWEEN 49 AND 60 |
|
|
7 |
SELECT Name |
|
FROM Salesperson |
|
|
WHERE Age LIKE ‘5_’ |
|
|
8 |
SELECT Name |
|
FROM Customer |
|
|
WHERE City LIKE ‘%S’ |
|
|
9 |
SELECT SalespersonName, Salary |
|
FROM Order |
|
|
WHERE CustName NOT IN ['Abernathy Construction'] |
|
|
ORDER BY Salary DESC |
|
|
10 |
SELECT COUNT(*) |
|
FROM Order |
|
|
11 |
SELECT COUNT(DISTINCT CustName) |
|
FROM Order |
|
|
12 |
SELECT AVG(Age) |
|
FROM Salesperson |
|
|
13 |
SELECT Name |
|
FROM Salesperson |
|
|
WHERE Age IN (SELECT MAX(Age) FROM Salesperson |
|
|
14 |
SELECT SalespersonName, COUNT(*) |
|
FROM ORDER |
|
|
GROUP BY SalespersonName |
|
|
15 |
SELECT COUNT(*) |
|
FROM Order |
|
|
GROUP BY SalespersonName |
|
|
WHEREAmount > 500 |
|
|
16 |
SELECT Name, Age |
|
FROM Salesperson |
|
|
WHERE Name IN |
|
|
(SELECT SalespersonName |
|
|
FROM Order |
|
|
WHERE CustName = 'Abernathy Construction') |
|
|
ORDER BYAge DESC |
|
|
17 |
SELECT Name, Age |
|
FROM Salesperson, Order |
|
|
WHERE SALESPERSON.Name = ORDER.SalespersonName |
|
|
AND ORDER.CustName = 'Abernathy Construction' |
|
|
ORDER BYAge DESC |
|
|
18 |
SELECT Age |
|
FROM Salesperson |
|
|
WHERE Name IN |
|
|
(SELECT SalespersonName |
|
|
FROM Order |
|
|
WHERE CustName IN |
|
|
(SELECT Name |
|
|
FROM Customer |
|
|
WHERE City = 'Memphis')) |
|
|
19 |
SELECT Age |
|
FROM Salesperson, Order, Customer |
|
|
WHERE SALESPERSON.Name = ORDER.SalespersonName |
|
|
AND ORDER.CustName = CUSTOMER.Name |
|
|
AND City = 'Memphis' |
|
|
20 |
SELECT IndustryType, Age |
|
FROM Salesperson, Customer, Order |
|
|
WHERE SALESPERSON.Name = ORDER.SalespersonName |
|
|
AND ORDER.CustName = CUSTOMER.Name |
|
|
AND City = 'Memphis' |
|
|
21 |
SELECT SALESPERSON.Name, ORDER.CustName |
|
FROM Salesperson LEFT JOIN ORDER |
|
|
ONSALESPERSON.Name = ORDER.SalespersonName |
|
|
We didn't cover q21 in class; so omit |
|
|
22 |
SELECT SalespersonName |
|
FROM Order |
|
|
GROUP BY SalespersonName |
|
|
HAVING COUNT(*) > 1 |
|
|
23 |
SELECT SALESPERSON.Name, Age |
|
FROM Salesperson, Order |
|
|
WHERE SALESPERSON.Name = ORDER.SalespersonName |
|
|
GROUP BY SalespersonName |
|
|
HAVING COUNT(*) > 1 |
|
| Class Schedule |