|
|
| |
|
|
Practice SQL questions
|
|
This question was asked in an interview with companies called Zantaz and Autonomy.
There's no better way to hone your sql skills than to practice. The following questions are meant to help you do that.
It's recommended that you try finding the answers on your own before reading the given answer.
They are based on the tables presented below.
|
|
Salesperson
|
ID
|
Name
|
Age
|
Salary
|
|
1
|
Abe
|
61
|
140000
|
|
2
|
Bob
|
34
|
44000
|
|
5
|
Chris
|
34
|
40000
|
|
7
|
Dan
|
41
|
52000
|
|
8
|
Ken
|
57
|
115000
|
|
11
|
Joe
|
38
|
38000
|
Customer
|
ID
|
Name
|
City
|
Industry Type
|
|
4
|
Samsonic
|
pleasant
|
J
|
|
6
|
Panasung
|
oaktown
|
J
|
|
7
|
Samony
|
jackson
|
B
|
|
9
|
Orange
|
Jackson
|
B
|
|
Orders
|
Number
|
order_date
|
cust_id
|
salesperson_id
|
Amount
|
|
10
|
8/2/96
|
4
|
2
|
540
|
|
20
|
1/30/99
|
4
|
8
|
1800
|
|
30
|
7/14/95
|
9
|
1
|
460
|
|
40
|
1/29/98
|
7
|
2
|
2400
|
|
50
|
2/3/98
|
6
|
7
|
600
|
|
60
|
3/2/98
|
6
|
7
|
720
|
|
70
|
5/6/98
|
9
|
7
|
150
|
|
Given the tables above, find the following:
a. The names of all salespeople that have an order with Samsonic.
b. The names of all salespeople that do not have any order with Samsonic.
c. The names of salespeople that have 2 or more orders.
d. Write a SQL statement to insert rows into a table called highAchiever(Name, Age), where
a salesperson must have a salary of 100,000 or greater to be included in the table.
|
|
Let's start by answering part a. It's obvious that we would need to do a join,
because the data in one table will not suffice to answer this question.
Now, what tables should we use for the join? We know that the customer ID of Samsonic is 4, so
we can use that information and do a simple join with the salesperson and customer tables. The SQL
would look like this:
select salesperson.name from salesperson, orders where salesperson.id = orders.salesperson_id and cust_id = '4';
We can also use subqueries (a query within a query) to come up with another possible answer.
Here is an alternative, but less efficient, solution using a subquery:
select salesperson.name from salesperson where salesperson.id = '{select orders.salesperson_id from orders, customer where orders.cust_id = customer.id and customer.name = 'Samsonic'}';
Click next to check out the answer to parts B and C.
|
|
|
