Key of Part 1

1. c
2. a,b,c
3. b
4. a
5. a
6. a,d
7. c,d
8. a,b
9. a,d
10. c
11. d
12. b
13. c
14. a
15. d
16. d
17. c

Select into

1. Tạo bảng titles_1 có cùng cấu trúc và cùng dữ liệu với bảng titles.
2. Tạo bảng titles_2 có cùng cấu trúc và chỉ chứa những quyển sách thuộc loại trad_cook trong bảng titles.
3. Tạo bảng titles_3 có cùng cấu trúc với bảng titles (không chứa dữ liệu).

Compute & Compute by

1. Northwind: Tìm tổng số lượng tất cả các sản phẩm của từng hóa đơn (Order Details)_ Compute by.
2. Hiển thị tổng số lượng sản phẩm đã bán_ Compute.
3. Hiển thị số lượng hóa đơn của từng nhà vận chuyển đã vận chuyển (ShipperID=ShipVia)

Subqueries

In:
1. Tìm những tên của những nxb (pub_name) nào cung cấp những quyển sách có giá là $79.96.
2. Northwind: Tìm tên những sản phẩm (productName) của nhà cung cấp (Suppliers) đến từ quốc gia (country) Germany.
3. Northwind: Tìm các hóa đơn của khách hàng đến từ quốc gia (country) Germany.
4. Tìm tên những nhà sách (stor_name) nào bán những quyển sách có từ Computer hoặc Cooking trong tên quyển sách (title)
5. Tìm tên đầy đủ của những tác giả (au_lname+au_fname) của những quyển sách có ngày xuất bản là 6/12/1991.

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

BT về nhà

--Bai tap ve nha
create view RevOrderDetails as
select o.OrderID,CustomerID,ProductID,Quantity
from [Order Details] od, Orders o
where od.OrderID=o.OrderID

select * from RevOrderDetails

alter trigger DeleteOrder
on RevOrderDetails instead of Delete
as
begin
delete from [Order Details] where OrderID in(select OrderID from deleted)
delete from Orders where OrderID in (select OrderID from deleted)
end

delete from RevOrderDetails where OrderID=10248

Phần II_p228_Q3 và Q4

--3.
sp_helptext InsertSales

--4.
alter trigger UpdateTitleAdvance
on titles with encryption
for update
as
if (select advance from inserted)>15000
begin
print 'Gia tri cua cot advance phai nho hon 15000'
rollback transaction
end

sp_helptext UpdateTitleAdvance

Phần II_p228_Q2

create trigger UpdateTitleAdvance
on titles
for update
as
if (select advance from inserted)>15000
begin
print 'Gia tri cua cot advance phai nho hon 15000'
rollback transaction
end

update titles set advance=16000 where title_id='BU1032'

Phần II_p228

create trigger InsertSales
on Sales
for Insert
as
if((select ord_date from inserted) > getdate())OR((select qty from inserted) < 10)
begin
print 'Not Insert'
rollback transaction
end


insert into sales values ('6380','6870','10/26/2006',110,'ON invoice','BU1032')

S15_1

CSDL Northwind
Tao procedure hiển thị tất cả nhưng hóa đơn và khoảng thời gian từ ngày đặt hàng đến ngày giao hàng của khách hàng: có người đại diện là Yang Wang.

Giai BT

--BT1 p204
--Xoa procedure neu no co ton tai truoc
use pubs
go
drop Get_Sales_Avg
go
--Cach 1
create procedure Get_Sales_Avg @a_id id
as
select avg(ytd_sales) as 'Gia tri TB' from titles
where title_id in (select title_id from titleauthor where au_id=@a_id)
go
--Cach 2
create procedure Get_Sales_Avg_1 @a_id varchar (20)
as
select avg(ytd_sales) as 'Gia tri TB' from titles t inner join titleauthor ta
on t.title_id=ta.title_id where au_id=@a_id
go
----------
create procedure Get_Sales_Avg_1 @a_id varchar (20)
as
select avg(ytd_sales) as 'Gia tri TB' from titles t,titleauthor ta
where t.title_id=ta.title_id and au_id=@a_id
go
exec Get_Sales_Avg_1 '267-41-2394'
--BT2: p204
exec Get_Sales_Avg '267-41-2394' with recompile

--BT3: p204
sp_stored_procedures
--BT4: p204
sp_helptext Get_Sales_Avg

--Bai tap ve nha
--Bai 1
create procedure Get_sales_Avg @a_id varchar(11) as
if (select count(*) from authors where au_id=@a_id)=0
begin
raiserror('AuthorID khong nhin thay',1,2)
end
else
select avg(ytd_sales) as 'Gia tri TB' from titles t,titleauthor ta
where t.title_id=ta.title_id and au_id=@a_id

exec Get_sales_Avg '213-46-8916'
--Cau 2:

alter procedure Get_sales_Avg @a_id varchar(11) as
if (select count(*) from authors where au_id=@a_id)!=0
begin
declare @x int
select @x=avg(ytd_sales) from titles t,titleauthor ta
where t.title_id=ta.title_id and au_id=@a_id
if @x > 5000
print 'High year-to-date sales'
else
print 'Low year-to-date sales'
end

exec Get_sales_Avg '267-41-2394'

Question for User & Security

1. Tạo ra một người dùng win_1, sao đó cấp quyền login cho người dùng này vào SQL Server bằng câu lệnh.
2. Cấp quyền cho người dùng sql_1 có quyền db_owner trên database pubs

Question for Index

CSDL Pubs

1. Tạo ra một index nonclustered duy nhất trên bảng EmployeeTerritories tại 2 cột EmployeeID và TerritoryID có phần trăm khoảng trống ở nút giữa và nút lá là 50%.
   
   
2. Tạo 1 clustered index trên bảng Region tại cột RegionID.
   
   
3. Tạo một chỉ mục clustered có giá trị duy nhất trên bảng Territories trên các cột (TerritoryID, RegionID), đảm bảo giá trị điền đầy ở mức lá là để trống 20%

Question for View & Cursor

CSDL Pubs

1. Tạo một view có tên là Store_Discount từ bảng Discount và Stores. View này bao gồm các trường storeID, storeName và discounttype (kiểu giảm giá đang được đưa ra)
   
   
2. Tạo view dùng để hiển thị tất cả các quyển sách trong bảng titles cùng với một cột gọi là Total, cột này liệt kê giá (price) nhân với số tiền bán được year-to-date.
   
   
3. Tạo view dùng để hiển thị tất cả các tác giả có họ (last name) bắt đầu bằng ký tự M.

Question for Trigger

CSDL Pubs

1. Tạo một trigger in ra một câu thông báo lỗi sau:
   Raiserror (“%d rows have been modified”,0,1,@@rowcount)
   Cho các thao tác thêm, sửa, xóa trên bảng authors
   
   
2. Tạo 1 trigger cập nhật giá của các quyển sách với điều kiện là quyển sách của tác giả Anne thì cho cập nhật, ngược lại thì không cho cập nhật.
   
   
   
3. Tạo một bảng mới có cùng cấu trúc và cùng dữ liệu với bảng titles. Tạo một trigger để xóa toàn bộ bảng nếu số hàng trong bảng vừa tạo là lớn hơn 20 ngược lại thì in ra câu thông báo …
   
   
4. Viết một trigger khi thêm quyển sách có mã là BU0032 và mã tác giả là 172-32-1981 vào bảng titleauthor (lưu ý là phải thêm vào các bảng còn lại)
   

CSDL Northwind:

1. Nhập vào một nhà vận chuyển. Nếu nhà vận chuyển có chứa giá trị Rose thì không cho nhập. In ra thông báo lỗi.
2. Viết một câu lệnh tạo trigger nếu xóa mã sp là 13 và mã sản phẩm là 10248 là không chấm nhận.
3. Viết 1 câu lệnh trigger trên bảng Employee sao cho nếu xóa 1 mẫu tin nào đó trên bảng Employee nó sẽ tự động đưa vào bảng RetiresEmployee.

Question for Store Procedure

CSDL Northwind

1. Tạo một procedure hiển thị các nhà cung cấp của sản phẩm thuộc loại 1.

2. Tạo một procedure hiển thị những nhân viên có ngày bắt đầu vào làm nằm trong khoảng thời gian được nhập vào. Viết câu lệnh thực thi procedure trên nhập vào từ ngày 16/12/1991 đến ngày 17/10/1992

Answer_Question_SQL_P2

1. N-M

2. Dạng chuẩn 2 (Second Normal Form)

3. SQL server sẽ trả về lỗi

4. Nested subqueries
Correlated subqueries

5. True

6. Chuyển thành kiểu dữ liệu bit, các giá trị khác 0 sẽ chuyển thành 1
Kiểu dữ liệu Char hoặc Varchar có thể chuyển thành kiểu Int chỉ khi nó chứa ký số

7. varbinary

8. True

9. False

10. 1024

11. With schemabinding

12. User-defined stored procedures
System stored procedures

13. 2100

14. EXEC procedure_name
EXECUTE procedure_name

15. Một trigger chỉ áp dụng cho một bảng
Một trigger có thể kết hợp cả 3 thao tác trên bảng: : INSERT, UPDATE, and DELETE

16. Update table

17. CREATE TRIGGER ins_trigg ON Passenger
FOR INSERT
AS
if (select pnr_no from inserted)> 500
begin
print 'PNR_no do not exeed 500'
rollback tran
end

18. Autocommit mode

19. Isolation

20. True

Question 20

20. Con trỏ đã đóng (close) nó vẫn còn tồn tại trong bộ nhớ
a. True
b. False

Question 19

19. Một giao dịch chỉnh sửa chỉ được truy cập dữ liệu trước hoặc sau khi một giao dịch khác hoàn thành. Thuộc tính nào sau đây của giao dịch định nghĩa cho trường hợp này
a. Atomicity
b. Consistency
c. Isolation
d. Durability

Question 18

18. Chế độ giao dịch mặc định của SQL Server là?
a. Explicit mode
b. Implicit mode
c. Autocommit mode