CS 386/586 Practice 3, Fall 2011

CS 386/586 practice, Week 3, Fall 2011

Practice using INTERSECT, UNION, with and without the ALL options.

Let’s look at all the skills in the Spy database:

select * from skill

Choose 2 skills:

28 Firearms

48 Pilot

Let’s find out how many people hold each skill

select skill_id, count(*) from skillrel group by skill_id

It’s too hard to find skill 28 and 48. Try this one:

select skill_id, count(*) from skillrel group by skill_id order by skill_id

We see that 42 agents have the skill of Firearms

We see that 43 agents have the skill of Pilot

I wonder if anyone has both skills?

Try this query:

(select agent_id from skillrel where skill_id = 28)

intersect

(select agent_id from skillrel where skill_id = 48)

We see 6 rows:

agent_id

201

316

373

540

1001

Now try this query:

(select agent_id from skillrel where skill_id = 28)

union

(select agent_id from skillrel where skill_id = 48)

We see 79 rows. But if we added 42 and 43, we get 85 rows. We can see that the UNION query is automatically eliminating duplicates.

Try this one:

(select agent_id from skillrel where skill_id = 28)

union all

(select agent_id from skillrel where skill_id = 48)

Now we see 85 rows. This is because duplicates are NOT being eliminated.

Here’s another way to see the set of agents that have a skill of Pilot, a skill of Firearms, or both, without duplicates.

Select distinct * from

((select agent_id from skillrel where skill_id = 28)

union all

(select agent_id from skillrel where skill_id = 48)) as f

Here, we are using a query expression in the from clause. We are using the DISTINCT clause in the SELECT Clause – to eliminate duplicates. We get 79 rows. This is the same answer we get when we take union.

Let’s find the agents_id who have a skill of Firearms but not a skill of Pilot.

(select agent_id from skillrel where skill_id = 28)

except

(select agent_id from skillrel where skill_id = 48)

We see 36 rows – which is 42 (the number of agents with a skill of firearms) – 6 the number of agents in the intersection.

Let’s try it the other way around.

(select agent_id from skillrel where skill_id = 48)

except

(select agent_id from skillrel where skill_id = 28)

We see 37 rows which is 43 (the number of agents with a skill of Pilot) minus 6 (the number of agents in the intersection).

Write a query that finds out whether any sailors reserved a red boat and also (on some other day) reserved a blue boat.

This query will show us the color of the boats in the Reserves table:

select * from reserves natural join boats

This query will show us sailors who reserved a red boat:

select sid from reserves natural join boats where color = 'red'

answer is:

sid

And this query will show us which sailors reserved a blue boat:

select sid from reserves natural join boats where color = ‘blue’

sid

So, let’s take the intersection of those two queries:

select sid from reserves natural join boats where color = 'red'

intersect

select sid from reserves natural join boats where color = 'blue'

and we see:

sid

Suppose we want to see the sailor’s name … for each sailor that reserved a red boat and a blue boat. Then we need to join the result we have so far with the sailors table.

Try this:

(select sid, sname from sailors natural join reserves natural join boats where color = 'red')
intersect
(select sid from reserves natural join boats where color = 'blue')

We should get an error because these two tables are NOT union-compatible. The first one returns 2 attributes while the second one only returns 1 attribute.

Sure enough, we get:

SQL error:

ERROR: each INTERSECT query must have the same number of columns

Let’s fix the query and try it again:

(select sid, sname from sailors natural join reserves natural join boats where color = 'red')
intersect
(select sid from reserves natural join boats where color = 'blue')

And we get:

sid sname

22 Dustin

64 Horatio

Practice using GROUP BY and HAVING.

Let’s find out how many reservations each boat has:

Select bid, count(*) from reserves group by bid

Bid count

102 3

101 2

104 2

103 3

Let’s find out how many reservations each sailor has by changing the grouping attribute to sid

Select bid, count(*) from reserves group by sid

Whoops, we get a syntax error because we are trying to include bid in the query answer but we are grouping on sid. Let’s fix it:

Select sid, count(*) from reserves group by sid

We see:

sid count

31 3

64 2

74 1

22 4

How would you change this query to show just ids for sailors that had 2 or more reservations?

Now, suppose we want to see the sailor’s name as well. Here’s how we can use a subquery in the SELECT clause … provided it returns JUST ONE value for each row in the query answer of the original query.

select r.sid, count(*), (select sname from sailors where r.sid = sid) from reserves r group by sid