2.Joins and subqueries

[mind1949]

Intro

This category deals primarily with a foundational concept in relational database systems: joining. Joining allows you to combine related information from multiple tables to answer a question. This isn't just beneficial for ease of querying: a lack of join capability encourages denormalisation of data, which increases the complexity of keeping your data internally consistent.
This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject.

Q1: retrive start times of members bookings

How can you produce a list of the start times for bookings by members named 'David Farrell'?

select b.starttime
	from cd.bookings as b 
		inner join cd.members as m
			on b.memid = m.memid 
				and m.firstname = 'David'
				and m.surname = 'Farrell';

/*or*/
select bks.starttime 
	from 
		cd.bookings bks
		inner join cd.members mems
			on mems.memid = bks.memid
	where 
		mems.firstname='David' 
		and mems.surname='Farrell'; 

Q2: Work out the start times of bookings for tennis courts

How can you produce a list of the start times for bookings for tennis courts, for the date '2012-09-21'? Return a list of start time and facility name pairings, ordered by the time.

select b.starttime as start, f.name
	from
		cd.facilities as f
		inner join cd.bookings as b
			on b.facid = f.facid
	where f.name like 'Tennis Court _' and
		b.starttime between '2012-09-21' and '2012-09-22'
order by b.starttime;

Q3: Produce a list of all members who have recommended another member

How can you output a list of all members who have recommended another member? Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname).

select distinct m1.firstname, m1.surname -- distinct 表示行不同,而不是列不同
	from cd.members as m1
		inner join cd.members as m2
			on m2.recommendedby = m1.memid
	where not m2.recommendedby is null
order by surname, firstname;

Q4: Produce a list of all members, along with their recommender

How can you output a list of all members, including the individual who recommended them (if any)? Ensure that results are ordered by (surname, firstname).

select m1.firstname as memfname,
		m1.surname as memsname,
		m2.firstname as recfname,
		m2.surname as recsname
	from cd.members as m1
		left outer join cd.members as m2
			on m2.memid = m1.recommendedby
order by m1.surname, m1.firstname;

Q5: produce a list of members who have used tennis course

How can you produce a list of all members who have used a tennis court? Include in your output the name of the court, and the name of the member formatted as a single column. Ensure no duplicate data, and order by the member name.

select distinct (m.firstname || ' ' || m.surname) as member,
		f.name as facility
	from cd.members as m
		inner join cd.bookings as b
			on m.memid = b.memid
		inner join cd.facilities as f
			on b.facid = f.facid
	where f.name like 'Tennis%'
order by member -- 别名可以在任何地方使用, 排序也不例外

Q6: Produce a list of costly bookings

How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. Include in your output the name of the facility, the name of the member formatted as a single column, and the cost. Order by descending cost, and do not use any subqueries.

select m.firstname || ' ' || m.surname as member,
		f.name as facility,
		case
			when m.memid = 0 then
				b.slots*f.guestcost
			else
				b.slots*f.membercost
		end as cost
	from
		cd.members as m
		inner join cd.bookings as b
			on m.memid = b.memid
		inner join cd.facilities as f
			on b.facid = f.facid
	where 
		b.starttime between '2012-09-14' and '2012-09-15'
		and (
			m.memid = 0 and b.slots*f.guestcost > 30
		  	or
		  	m.memid != 0 and b.slots*f.membercost > 30
		)
order by cost desc;

Q7: produce a list of all members, along with their recommender(if any), using no join

How can you output a list of all members, including the individual who recommended them (if any), without using any joins? Ensure that there are no duplicates in the list, and that each firstname + surname pairing is formatted as a column and ordered.

-- 使用子查询可以进行where过滤,也可以填充计算字段
-- 这道题是填充计算字段
select distinct m1.firstname || ' ' || m1.surname as member,
	(select m2.firstname || ' ' || m2.surname 
	  	from cd.members as m2
	  	where m2.memid = m1.recommendedby
	) as recommender
	from 
		cd.members as m1
order by member;

/*or*/
select distinct mems.firstname || ' ' ||  mems.surname as member,
	(select recs.firstname || ' ' || recs.surname as recommender 
		from cd.members recs 
		where recs.memid = mems.recommendedby
	)
	from 
		cd.members mems
order by member;

Q8: Produce a list of costly bookings, using a subquery

The Produce a list of costly bookings exercise contained some messy logic: we had to calculate the booking cost in both the WHERE clause and the CASE statement. Try to simplify this calculation using subqueries. For reference, the question was:

How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. Include in your output the name of the facility, the name of the member formatted as a single column, and the cost. Order by descending cost.

select member, facility, cost
	from (
	  select m.firstname || ' ' || m.surname as member,
	  		f.name as facility,
	  		case
	  			when f.facid = 0 then
	  				b.slots*f.guestcost
	  			else
	  				b.slots*f.membercost
	  		end as cost
	 		from
	  			cd.members as m
	  			inner join cd.bookings as b
	  				on m.memid = b.memid
	  			inner join cd.facilities as f
	  				on b.facid = f.facid
	  		where
	  			b.starttime between '2012-09-14' and '2012-09-15' -- 这里不能有分号
	 ) as bookings -- 这里必须起一个别名
	where cost > 30 -- 使用内部的查询简化了外部查询的where子句
order by cost desc;

[mind1949]

第二章完成!(^o^)/