Bài giảng trong sách

--bai 1
select title_id,null from titles
union
select title_id,au_id from titleauthor
union
select null,au_id from titleauthor

--bai 2
select title,type from titles where type='business'

--bai 3
select DISTINCT type from titles

select type from titles

--bai 4
SELECT au_fname+' '+au_lname AS 'Full name', [Sum of RoyalTyper]
FROM Authors a
INNER JOIN (
select au_id, sum(royaltyper) 'Sum of RoyalTyper'
from titleauthor
GROUP BY au_id
) b
ON a.au_id = b.au_id


SELECT au_fname+' '+au_lname AS 'Full name', [Sum of RoyalTyper]
FROM Authors a,( select au_id, sum(royaltyper) 'Sum of RoyalTyper'
from titleauthor
GROUP BY au_id
) b
where a.au_id = b.au_id


SELECT au_fname+' '+au_lname AS 'Full name', [Sum of RoyalTyper]
FROM Authors a
INNER JOIN (
select au_id, sum(royaltyper) 'Sum of RoyalTyper'
from titleauthor
GROUP BY au_id
) b
ON a.au_id = b.au_id



--bai 5
select * from authors

select au_id, au_fname + ' ' + au_lname AS 'Full name',Phone, Address, city
from authors

--bai 6
SELECT distinct a.au_id, au_fname + ' ' + au_lname AS 'Full name',Phone, Address, city
FROM authors a
INNER JOIN TitleAuthor b
ON a.au_id = b.au_id AND
EXISTS (
SELECT title_id
FROM titles c
WHERE b.title_id=c.title_id AND type='business'
)

SELECT a.au_id, au_fname + ' ' + au_lname AS 'Full name',Phone, Address, city
FROM authors a
INNER JOIN TitleAuthor b
ON a.au_id = b.au_id
INNER JOIN titles c
ON b.title_id=c.title_id AND type='business'
--bai 7
SELECT au_fname + ' ' + au_lname AS 'Full name', d.tong
FROM authors a
INNER JOIN (
select b.au_id, count(b.au_id) Tong FROM TitleAuthor b
INNER JOIN Titles c
ON b.title_id = c.title_id
GROUP BY b.au_id
) d
ON a.au_id = d.au_id


SELECT DISTINCT au_fname+' '+au_lname AS 'Full Name',
Count(*) AS Qty
FROM Authors a
INNER JOIN TitleAuthor b
ON a.au_id = b.au_id
INNER JOIN Titles c
ON b.title_id = c.title_id
GROUP BY au_fname, au_lname

--Bai tap ve nha
--bai 1
select title_id, count(title_id) as 'So luong sach', sum(qty) as 'Tong so luong'
from sales
group by title_id
--bai 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)
--bai 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)

--BAI TAP THEM
--1. Lay ra gia tri cua quyen sach nao duoc viet boi tac gia Dean
select title from titles where title_id in(
select title_id from titleauthor where au_id in
(select au_id from authors where au_fname like '%Dean%'))

--Ket noi titleauthor voi bang authors
select authors.au_id,phone,title_id
from authors,titleauthor
where authors.au_id= titleauthor.au_id

select a.au_id,phone,title_id
from authors a,titleauthor ta
where a.au_id= ta.au_id

select authors.au_id,phone,title_id
from authors inner join titleauthor
on authors.au_id= titleauthor.au_id


select authors.au_id,phone,titles.title_id,type
from authors,titleauthor,titles
where authors.au_id= titleauthor.au_id and titleauthor.title_id=titles.title_id