Session 4

Chương 4

Bài tập : Cơ sở dữ liệu Pubs

1. SELECT title_id FROM titles
UNION
SELECT title_id FROM titleauthor
UNION all
SELECT au_id FROM authors
2. SELECT * FROM Titles WHERE type='business'
3. SELECT DISTINCT type FROM Titles
4. SELECT au_id, SUM(royaltyper) as ‘Tong nhuan but’ FROM titleauthor GROUP BY au_id
5. SELECT * FROM authors
6. SELECT * FROM authors WHERE au_id IN
(SELECT au_id FROM titleauthor WHERE title_id IN
(SELECT title_id FROM Titles WHERE type='business'))

Hoặc
SELECT * FROM authors WHERE EXISTS
(SELECT au_id FROM titleauthor
WHERE authors.au_id=titleauthor.au_id AND title_id IN
(SELECT title_id FROM Titles WHERE type='business'))
Hoặc
SELECT * FROM authors WHERE EXISTS
(SELECT au_id FROM titleauthor
WHERE authors.au_id=titleauthor.au_id AND EXISTS
(SELECT title_id FROM Titles WHERE type='business'
AND Titles.title_id=titleauthor.title_id))
7. select au_id, count(*) as ‘So luong sach’ from titleauthor group by au_id
8. select title_id, avg(qty) as ‘So luong trung binh’ from sales group by title_id
Bài tập về nhà

1. select title_id, count(*) as 'total number of orders', sum(qty) as 'total quantity'
from sales group by title_id
2. select distinct au_id from dbo.titleauthor
where exists (select count(*) from dbo.sales
where sales.title_id=titleauthor.title_id
having count(*)>=1)
3. select distinct au_id from dbo.titleauthor
where not exists (select count(*) from dbo.sales
where sales.title_id=titleauthor.title_id
having count(*)>=1)