Bài tập truy vấn cơ sở dữ liệu SQL

Nắm rõ sơ đồ thực thể liên kết và cấu trúc các bảng phục vụ cho phần truy vấn sau.

1. Cú pháp truy vấn dữ liệu tổng quát

SELECT FROM WHERE GROUP BY HAVING ORDER BY

2. Dạng truy vấn đơn giản

SELECT FROM

=> lấy ra một số cột trong một bảng nào đó.

Ví dụ: lấy ra mã sinh viên, họ đệm và tên của các sinh viên trong bảng SINHVIEN

SELECT MaSV, Hodem, Ten FROM SINHVIEN;

3. Ký tự đại diện *

Ký tự * đại diện cho tất cả các cột

SELECT * FROM

Ví dụ: Lấy ra danh sách các khoa

SELECT * FROM KHOA;

4. Mệnh đề WHERE

• Dùng để thiết lập điều kiện lấy dữ liệu

SELECT FROM WHERE

Ví dụ: lấy ra mã sinh viên, họ đệm và tên của các sinh viên nữ

SELECT MaSV, HoDem, Ten FROM SINHVIEN WHERE GioiTinh = "Nữ";

• Các phép toán trong mệnh đề WHERE:
  • Toán tử so sánh: >, =, 2 từ bảng SINHVIEN SELECT SINHVIEN.MaLop, LOP.TenLop, COUNT[SINHVIEN.MaSV] AS 'Số sinh viên' FROM SINHVIEN, LOP WHERE SINHVIEN.MaLop = LOP.MaLop GROUP BY SINHVIEN.MaLop, LOP.TenLop HAVING COUNT[SINHVIEN.MaSV]>=2

    9. Phát biểu Select với AS

    • Đặt lại tên cột khi hiển thị kết quả

    Ví dụ: Hiển thị thông tin về các sinh viên với các kết quả học tập của họ. Khi hiển thị cột KetQua đổi tên thành cột DiemTB

    SELECT SINHVIEN.MaSV, HoDem, Ten, NgaySinh, GioiTinh, TenMH, KetQua AS DiemTB FROM SINHVIEN, KETQUA, MONHOC WHERE SINHVIEN.MaSV = KETQUA.MaSV AND KETQUA.MaMH = MONHOC.MaMH;

    10. Phát biểu Select với DISTINCT

    • Nếu kết quả truy vấn có nhiều bản ghi trùng nhau, để chỉ các bản tin duy nhất [không trùng nhau] ta dùng từ khoá DISTINCT

    Ví dụ: In ra danh sách các lớp trong bảng SINHVIEN

    SELECT DISTINCT MaLop FROM SINHVIEN;

    11. Truy vấn con

    • Đôi khi ta cần sử dụng kết quả của 1 câu truy vấn để làm điều kiện cho 1 câu truy vấn khác, khi đó ta gọi là truy vấn con.
    • Khi thực hiện, truy vấn con sẽ được thực hiện trước, rồi lấy kết quả để thực hiện truy vấn chính.
    • Dạng tổng quát:
    SELECT FROM WHERE … IN [ NOT IN, =, , …] [SELECT FROM WHERE ]

    Ví dụ: Hiển thị thông tin về những sinh viên đã đăng kí học và không phải thi lại môn học nào

    SELECT SINHVIEN.MaSV, HoDem, Ten, NgaySinh, MaMH, KetQua FROM SINHVIEN, KETQUA WHERE [SINHVIEN.MaSV=KETQUA.MaSV] AND SINHVIEN.MaSV IN [SELECT MaSV FROM KETQUA WHERE KetQua>=5];

    Chúc các bạn thành công!

Các thẻ: Cơ sở dữ liệu sinh viênSelectSQLTruy vấn cơ sở dữ liệu

Tổng hợp các bài tập tạo cơ sở dữ liệu và truy vấn các câu lệnh trong SQL server có lời giải chi tiết rất hay

Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây [269.69 KB, 52 trang ]

Tạo database1và viết câu truy vấn Sql Server

Tổng hợp các bài tập tạo cơ sở dữ liệu và truy vấn các câu
lệnh trong SQL Server Quản lý sinh viên, quản lý bán hàng,
quản lý điểm sinh viên có lời giải chi tiết Cách tạo cơ sở dữ
liệu và viết câu truy vấn SQL Server

I.QUẢN LÝ ĐIỂM SINH VIÊN - TẠO
DATABASE VÀ VIẾT CÂU TRUY VẤN
/*=====================Create DataBase======================*/
use master
go
if exists[select name from sysdatabases where
name='QuanLyDiemSV']
drop Database QuanLyDiemSV
go
Create Database QuanLyDiemSV
go
use QuanLyDiemSV
go
/*=============DANH MUC KHOA==============*/
Create table DMKhoa
[
MaKhoa char[2] primary key,
TenKhoa nvarchar[30]not null,
]
/*==============DANH MUC SINH VIEN============*/
Create table DMSV
[
MaSV char[3] not null primary key,
HoSV nvarchar[15] not null,

TenSV nvarchar[7]not null,
Phai nchar[7],
NgaySinh datetime not null,
NoiSinh nvarchar [20],
MaKhoa char[2],
HocBong float,
Xem thêm tài liệu: //bit.ly/tailieuztechbook

1


Tạo database2và viết câu truy vấn Sql Server

]

/*===================MON HOC========================*/
create table DMMH
[
MaMH char [2] not null,
TenMH nvarchar [25]not null,
SoTiet tinyint
Constraint DMMH_MaMH_pk primary key[MaMH]
]

/*=====================KET QUA===================*/
Create table KetQua
[
MaSV char[3] not null,
MaMH char [2]not null ,
LanThi tinyint,

Diem decimal[4,2],
Constraint KetQua_MaSV_MaMH_LanThi_pk primary key
[MaSV,MaMH,LanThi]
]
/*==========================TAO KHOA
NGOAI==============================*/
Alter table dmsv
add Constraint DMKhoa_MaKhoa_fk foreign key [MaKhoa]
References DMKhoa [MaKhoa]
Alter table KetQua
add constraint KetQua_MaSV_fk foreign key [MaSV] references
DMSV [MaSV],
constraint DMMH_MaMH_fk foreign key [MaMH] references DMMH
[MaMH]
/*==================NHAP DU LIEU====================*/
/*==============NHAP DU LIEU DMMH=============*/
Insert into DMMH[MaMH,TenMH,SoTiet]
values['01',N'Cơ Sở Dữ Liệu',45]
Xem thêm tài liệu: //bit.ly/tailieuztechbook

2


Tạo database3và viết câu truy vấn Sql Server

Insert into DMMH[MaMH,TenMH,SoTiet]
values['02',N'Trí Tuệ Nhân Tạo',45]
Insert into DMMH[MaMH,TenMH,SoTiet]
values['03',N'Truyền Tin',45]
Insert into DMMH[MaMH,TenMH,SoTiet]

values['04',N'Đồ Họa',60]
Insert into DMMH[MaMH,TenMH,SoTiet]
values['05',N'Văn Phạm',60]
/*==============NHAP DU LIEU DMKHOA=============*/
Insert into DMKhoa[MaKhoa,TenKhoa]
values['AV',N'Anh Văn']
Insert into DMKhoa[MaKhoa,TenKhoa]
values['TH',N'Tin Học']
Insert into DMKhoa[MaKhoa,TenKhoa]
values['TR',N'Triết']
Insert into DMKhoa[MaKhoa,TenKhoa]
values['VL',N'Vật Lý']
/*==============NHAP DU LIEU DMSV=============*/
SET DATEFORMAT DMY
GO
Insert into DMSV
values['A01',N'Nguyễn Thị',N'Hải',N'Nữ','23/02/1990',N'Hà
Nội','TH',130000]
Insert into
DMSV[MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong]
values['A02',N'Trần Văn',N'Chính',N'Nam','24/12/1992',N'Bình
Định','VL',150000]
Insert into
DMSV[MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong]
values['A03',N'Lê Thu Bạch',N'Yến',N'Nữ','21/02/1990',N'TP
Hồ Chí Minh','TH',170000]
Insert into
DMSV[MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong]
values['A04',N'Trần Anh',N'Tuấn',N'Nam','20/12/1990',N'Hà
Nội','AV',80000]

Insert into
DMSV[MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong]
values['B01',N'Trần Thanh',N'Mai',N'Nữ','12/08/1991',N'Hải
Phòng','TR',0]
Insert into
DMSV[MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong]
Xem thêm tài liệu: //bit.ly/tailieuztechbook

3


Tạo database4và viết câu truy vấn Sql Server

values['B02',N'Trần Thị Thu',N'Thủy',N'Nữ','02/01/1991',N'TP
Hồ Chí Minh','AV',0]
/*==============NHAP DU LIEU BANG KET QUA=============*/
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A01','01',1,3]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A01','01',2,6]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A01','02',2,6]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A01','03',1,5]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A02','01',1,4.5]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A02','01',2,7]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A02','03',1,10]

Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A02','05',1,9]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A03','01',1,2]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A03','01',2,5]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A03','03',1,2.5]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A03','03',2,4]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['A04','05',2,10]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['B01','01',1,7]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['B01','03',1,2.5]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['B01','03',2,5]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['B02','02',1,6]
Insert into KetQua[MaSV,MaMH,LanThi,Diem]
values['B02','04',1,10]
/*===============CAP NHAT THONG TIN=================*/
--câu 2-update dmmh
Xem thêm tài liệu: //bit.ly/tailieuztechbook

4


Tạo database5và viết câu truy vấn Sql Server

set sotiet=45
where mamh='05'
--câu 3,4--update dmsv
set tensv=N'Kỳ',phai ='Nam'
where masv='b01'
-------câu 5----update dmsv
set ngaysinh='05/07/1990'
where masv='b02'
----------câu 6---------update dmsv
set hocbong=hocbong+100000
where makhoa='Av'
-------câu 7---------delete from ketqua
where lanthi=2 and diem100000
order by makhoa desc
--19. Liệt kê các sinh viên có học bổng từ 150,000 trở lên và
sinh ở Hà Nội, gồm các thông
--tin: Họ tên sinh viên, Mã khoa, Nơi sinh, Học bổng.
Select HoSV+' '+TenSV As N'Họ Tên Sinh Viên',MaKhoa As N'Mã
Khoa',NoiSinh As N'Nơi Sinh',HocBong As N'Học Bổng'
From DMSV
Where HocBong>=150000 and NoiSinh=N'Hà Nội'
--20. Danh sách các sinh viên của khoa Anh văn và khoa Vật lý,
gồm các thông tin: Mã sinh
--viên, Mã khoa, Phái.
Select MaSV As N'Mã Sinh Viên',MaKhoa As N'Mã Khoa',Phai As
N'Phái'
From DMSV

Where MaKhoa='AV' or MaKhoa='VL'
--21. Cho biết những sinh viên có ngày sinh từ ngày 01/01/1991
đến ngày 05/06/1992 gồm
--các thông tin: Mã sinh viên, Ngày sinh, Nơi sinh, Học bổng.
Select MaSV As N'Mã Sinh Viên' ,NgaySinh As N'Ngày Sinh',NoiSinh
As N'Nơi Sinh',HocBong As N'Học Bổng'
From DMSV
Where NgaySinh>='01/01/1991' and NgaySinh=80000 and HocBong30 and SoTiet20
--28. Danh sách những sinh viên có tuổi từ 20 đến 25, thông tin
gồm: Họ tên sinh viên, Tuổi,
--Tên khoa.
select hosv+' '+tensv 'Tên sinh viên','Tuổi'=year[getdate[]]year[ngaysinh],tenkhoa 'Tên khoa'
from dmsv sv,dmkhoa khoa
where sv.makhoa=khoa.makhoa and [year[getdate[]]year[ngaysinh]between 20 and 25]
--29. Danh sách sinh viên sinh vào mùa xuân năm 1990, gồm các

thông tin: Họ tên sinh viên,
--Phái, Ngày sinh.
select hosv+' '+tensv 'Tên sinh viên',phai 'Phái',ngaysinh 'Ngày
sinh'
from dmsv
where year[ngaysinh]=1990 and [month [ngaysinh]in[1,2,3]]
--between 1 and 3]
Xem thêm tài liệu: //bit.ly/tailieuztechbook

8


Tạo database9và viết câu truy vấn Sql Server

--30. Cho biết thông tin về mức học bổng của các sinh viên, gồm:
Mã sinh viên, Phái, Mã
--khoa, Mức học bổng. Trong đó, mức học bổng sẽ hiển thị là “Học
bổng cao” nếu giá trị
--của field học bổng lớn hơn 500,000 và ngược lại hiển thị là
“Mức trung bình”
select masv'Mã sinh viên',phai'Phái',makhoa 'Mã khoa','Mức trung
bình'=case when hocbong>500000 then N'Học bổng cao' else N' Mức
trung bình' end
from dmsv
--D. Truy vấn sử dụng hàm kết hợp: max, min, count, sum, avg và
gom
--nhóm
--32. Cho biết tổng số sinh viên của toàn trường
select 'Tổng sinh viên toàn trường'=count[masv]
from dmsv

--33. Cho biết tổng sinh viên và tổng sinh viên nữ.
--đây là cách viết gộp trong bảng
select 'Tổng sinh viên'=count[masv],'Tổng sinh viên nữ'=sum[case
phai when N'nữ'then 1 else 0 end]
from dmsv
-----------select 'Tổng sinh viên'=count[masv],t.nu 'Tổng sinh viên nữ'
from dmsv,
[
select count[masv] as 'nu'
from dmsv
where phai=N'Nữ'
] as t
group by t.nu
--34. Cho biết tổng số sinh viên của từng khoa.
select makhoa 'Mã khoa','Mã sinh viên'=count[masv]
from dmsv
group by makhoa
--35. Cho biết số lượng sinh viên học từng môn.
select tenmh'Tên môn học',count[distinct masv]'Mã sinh viên'
from ketqua kq,dmmh mh
where kq.mamh=mh.mamh
group by tenmh
--36. Cho biết số lượng môn học mà sinh viên đã học[tức tổng số
môn học có torng bảng kq]
select count[distinct mamh]'Tổng số môn học'
from ketqua
Xem thêm tài liệu: //bit.ly/tailieuztechbook

9

Tạo database
10và viết câu truy vấn Sql Server

--37. Cho biết tổng số học bổng của mỗi khoa.
select makhoa 'Mã khoa',sum[hocbong]'Tổng học bổng'
from dmsv
group by makhoa
--38. Cho biết học bổng cao nhất của mỗi khoa.
select makhoa 'Mã khoa',max[hocbong]'Học bổng cao nhất'
from dmsv
group by makhoa
--39. Cho biết tổng số sinh viên nam và tổng số sinh viên nữ của
mỗi khoa.
select makhoa,'Tổng sinh viên nam'=sum[case phai when N'nam'then
1 else 0 end],'Tổng sinh viên nữ'=sum[case phai when N'nữ'then 1
else 0 end]
from dmsv
group by makhoa
--40. Cho biết số lượng sinh viên theo từng độ tuổi.
select year[getdate[]]-year[ngaysinh] 'Tuổi',count[masv] 'Số sinh
viên'
from dmsv
group by year[getdate[]]-year[ngaysinh]
--41. Cho biết những năm sinh nào có 2 sinh viên đang theo học
tại trường.
select year[ngaysinh]'Năm',count[Masv]'Số sinh viên'
from dmsv
group by year[ngaysinh]
having count[Masv]=2

--42. Cho biết những nơi nào có hơn 2 sinh viên đang theo học tại
trường.
select NoiSinh, count[Masv]'Số sinh viên'
from dmsv
group by NoiSinh
having count[Masv]>=2
--43. Cho biết những môn nào có trên 3 sinh viên dự thi.
select mamh 'Mã môn học',count[masv]'Số Sinh viên'
from ketqua
group by mamh
having count[masv]>3
--44. Cho biết những sinh viên thi lại trên 2 lần.
select masv,mamh,count[lanthi]'so lan thi lai'
from ketqua
group by masv,mamh
having count[lanthi]>2
Xem thêm tài liệu: //bit.ly/tailieuztechbook

10


Tạo database
11và viết câu truy vấn Sql Server

--45. Cho biết những sinh viên nam có điểm trung bình lần 1 trên
7.0
select Hosv+' '+tensv 'Họ tên sinh
viên',phai,lanthi,avg[Diem]'diem trung binh'
from ketqua kq,dmsv sv
where kq.masv=sv.masv and lanthi=1 and phai=N'nam'

group by lanthi,phai, Hosv+' '+tensv
having avg[Diem]>7.0
--46. Cho biết danh sách các sinh viên rớt trên 2 môn ở lần thi
1.
select masv 'Mã sinh viên',count[mamh]'Số môn rớt'
from ketqua
where lanthi=1 and diem=2
--47. Cho biết danh sách những khoa có nhiều hơn 2 sinh viên nam
select makhoa 'Mã khoa','Số sinh viên nam'=count[masv]
from dmsv
where phai=N'Nam'
group by makhoa
having count[masv]>=2
--48. Cho biết những khoa có 2 sinh đạt học bổng từ 200.000 đến
300.000.
select makhoa 'Mã khoa','Số sinh viên'=count[masv]
from dmsv
where hocbong between 200000 and 300000
group by makhoa
having count[masv]>2
--49. Cho biết số lượng sinh viên đậu và số lượng sinh viên rớt
của từng môn trong lần thi 1.
--làm từng bảng
select tenmh,'Số sinh viên Đậu'=count[masv]
from ketqua kq,dmmh mh
where kq.mamh=mh.mamh and lanthi=1 and diem>=5
group by tenmh
select tenmh,'Số sinh viên Rớt'=count[masv]

from ketqua kq,dmmh mh
where kq.mamh=mh.mamh and lanthi=1 and diem=5 then 1 else 0 end ],'Số sinh viên Rớt'=sum[case when
diem=all[select count[masv]
from dmsv
group by makhoa]
--cach 2:
select tenkhoa
from dmsv sv,dmkhoa kh
where sv.makhoa=kh.makhoa
group by tenkhoa
having count[tenkhoa]= [select max[t.tong]
from
[
select count[masv]

from dmsv
group by makhoa
] as t
]

as tong

--55. Cho biết khoa nào có đông nữ nhất.
select tenkhoa 'Tên khoa'
from dmsv sv,dmkhoa kh
where sv.makhoa=kh.makhoa and phai=N'nữ'
group by tenkhoa
having count[tenkhoa]>=all[select count[masv]
from dmsv
where phai=N'nữ'
group by makhoa]
--56. Cho biết môn nào có nhiều sinh viên rớt lần 1 nhiều nhất.
select mamh
from ketqua
where lanthi=1 and diem=all [select count[diem]
Xem thêm tài liệu: //bit.ly/tailieuztechbook

13


Tạo database
14và viết câu truy vấn Sql Server

from ketqua
where lanthi=1 and diem[
select diem
from ketqua kq, dmsv sv
where sv.masv=kq.masv and mamh='05' and
makhoa='av'
]
--G. Truy vấn con trả về nhiều giá trị, sử dụng lượng từ all,
any, union, top.
--58. Cho biết sinh viên có nơi sinh cùng với Hải.
select masv,hosv+' '+tensv
from dmsv
where noisinh=[ select noisinh
from dmsv
where tensv=N'hải']
--59. Cho biết những sinh viên nào có học bổng lớn hơn tất cả học
bổng của sinh viên thuộc khoa anh văn
select masv
from dmsv
where hocbong>=all [select hocbong from dmsv where makhoa='av']
--60. Cho biết những sinh viên có học bổng lớn hơn bất kỳ học
bổng của sinh viên học khóa anh văn

select masv,hocbong
from dmsv
where hocbong>=any [select hocbong from dmsv where makhoa='av']
--61. Cho biết sinh viên nào có điểm thi môn cơ sở dữ liệu lần 2
lớn hơn tất cả điểm thi lần 1
--môn cơ sở dữ liệu của những sinh viên khác.
select masv
from ketqua
where mamh='01' and lanthi=2 and diem>=all[select diem from
ketqua where mamh='01' and lanthi=1]
--62. Cho biết những sinh viên đạt điểm cao nhất trong từng môn.
select masv,ketqua.mamh,diem
from ketqua, [select mamh, max[diem] as maxdiem
Xem thêm tài liệu: //bit.ly/tailieuztechbook

14


Tạo database
15và viết câu truy vấn Sql Server

from ketqua
group by mamh]a
where ketqua.mamh=a.mamh and diem=a.maxdiem
--63. Cho biết những khoa không có sinh viên học.
select *
from dmkhoa
where not exists [select distinct makhoa
from ketqua,dmsv where
ketqua.masv=dmsv.masv and makhoa=dmkhoa.makhoa]

--64. Cho biết sinh viên chưa thi môn cơ sở dữ liệu.
select *
from dmsv
where not exists
[select distinct*
from ketqua
where mamh = '01' and masv=dmsv.masv]
--65. Cho biết sinh viên nào không thi lần 1 mà có dự thi lần 2.
select masv
from ketqua kq
where lanthi=2 and not exists
[select *
from ketqua
where lanthi=1 and masv=kq.masv]
--66. Cho biết môn nào không có sinh viên khoa anh văn học.
select tenmh
from dmmh
where
not exists
[select mamh
from ketqua kq,dmsv sv
where sv.masv=kq.masv and sv.makhoa='av' and dmmh.mamh=mamh]
--67. Cho biết những sinh viên khoa anh văn chưa học môn văn
phạm.
Select MaSV
From DMSv dmsv
Where
MaKhoa='AV' And Not Exists [Select *
From
KetQua

Where
MaMH='05' And MaSV=dmsv.MaSV
]
--68. Cho biết những sinh viên không rớt môn nào.
Select MaSV
From DMSV dmsv
Xem thêm tài liệu: //bit.ly/tailieuztechbook

15


Tạo database
16và viết câu truy vấn Sql Server

Where Not Exists [Select *
From KetQua
Where Diem0 And Not Exists [Select *
From KetQua
Where Diem0
Group By MaKhoa
Having count[MaSV]>=All [Select count[MaSV]
From DMSv
where hocbong>0
Group By MaKhoa
]
UNION
Select MaKhoa,count[MaSV]'So Luong SV'
From DMSV
Where HocBong>0
Group By MaKhoa
Having count[MaSV]0
Group By MaKhoa
]

Xem thêm tài liệu: //bit.ly/tailieuztechbook

16


Tạo database
17và viết câu truy vấn Sql Server

--71. Cho biết 3 sinh viên có học nhiều môn nhất.
Select top 3 MaSV,Count[Distinct MaMH]'Số môn học'
From KetQua
Group By MaSV
Having Count[Distinct MaMH]>=All[Select count[ distinct
MaMH]
From KetQua
Group By MaSV
]
/*==========================H. Truy vấn dùng phép chia
=========================*/
--72. Cho biết những môn được tất cả các sinh viên theo học.
Select MaMH
From KetQua
Group By MaMH
Having count[distinct MaSV]=[Select count[MaSV]
From DMSv
]
--73. Cho biết những sinh viên học những môn giống sinh viên có
mã số A02 học.
Select distinct MaSV
From KetQua kq
Where Exists[Select distinct MaMH
From KetQua
Where MaSV='A02' and MaMH=kq.MaMH
]
--74.Cho biết những sinh viên học những môn bằng đúng những môn
mà sinh viên A02 học.
Select TenSV
From KetQua kq,DMSv dmsv,[Select MaSV,MaMH,count[distinct

MaMH]SoMon
From KetQua
Where MaSV='A02'
Group By MaSV,MaMH]a
Where kq.MaSV=dmsv.MaSV and kq.MaMH=a.MaMH and kq.MaSV a
.MaSV
Group By TenSV
Having count[distinct kq.MaMH]=[Select count[distinct MaMH]
Xem thêm tài liệu: //bit.ly/tailieuztechbook

17


Tạo database
18và viết câu truy vấn Sql Server

From KetQua
Where
MaSV='A02']

Select dmsv.MaSV
From KetQua kq, DMSv dmsv
Where kq.MaSV=dmsv.MaSV and MaMH=[Select distinct MaMH
From KetQua
Where MaSV='A02' and MaMH=kq.MaMH] and
dmsv.MaSV Not Like 'A02'
Group By dmsv.MaSV
Having count[distinct MaMH]=[Select count[distinct MaMH]
From KetQua
Where MaSV='A02']

--75. Tạo một bảng mới tên sinhvien-ketqua: gồm: MASV, HoSV,
TenSV, SoMonHoc. Sau
--đó Thêm dữ liệu vào bảng này dựa vào dữ liệu đã có.
Create Table SinhVien_KetQua
[
MaSV char[3] not null,
HoSV nvarchar[15] not null,
TenSV nvarchar[7]not null,
SoMonHoc tinyint
]
Insert Into SinhVien_KetQua
Select dmsv.MaSV,HoSV,TenSV,count[distinct MaMH]
From DMSV dmsv,KetQua kq
Where dmsv.MaSV=kq.MaSV
Group By dmsv.MaSV,HoSV,TenSV
--76. Thêm vào bảng khoa cột Siso, cập nhật sỉ số vào khoa từ dữ
liệu sinh viên.
go
alter table dmkhoa
add siso tinyint
Xem thêm tài liệu: //bit.ly/tailieuztechbook

18


Tạo database
19và viết câu truy vấn Sql Server

go

update dmkhoa
set siso=[select count[masv]
from dmsv
where makhoa='av'
group by[makhoa]]
where makhoa='av'
update dmkhoa
set siso=[select count[masv]
from dmsv
where makhoa='TH'
group by[makhoa]]
where makhoa='Th'
update dmkhoa
set siso=[select count[masv]
from dmsv
where makhoa='Tr'
group by[makhoa]]
where makhoa='Tr'
update dmkhoa
set siso=[select count[masv]
from dmsv
where makhoa='vl'
group by[makhoa]]
where makhoa='vl'
--77. Tăng thêm 1 điểm cho các sinh viên vớt lần 2. Nhưng chỉ
tăng tối đa là 5 điểm
update ketqua
set diem=diem+1
where lanthi=2 and diem+1=6.5]
--79. Thiết lập học bổng bằng 0 cho những sinh viên thi hai môn
rốt ở lần 1
Xem thêm tài liệu: //bit.ly/tailieuztechbook

19


Tạo database
20và viết câu truy vấn Sql Server

update dmsv
set hocbong=0
where masv in [select masv
from ketqua
where lanthi=1 and diem=5
--83. Danh sách sinh viên học môn văn phạm và môn cơ sở dữ liệu
create view cau83
as
select *
from dmsv
where masv in
[select distinct ketqua.masv
from ketqua,dmsv
where dmsv.masv=ketqua.masv and [mamh='01' or mamh='05']
]
drop view cau83
--84. Trong mỗi sinh viên cho biết môn có điểm
Thông tin gồm: mã sinh viên,

--tên sinh viên, tên môn, điểm.
Xem thêm tài liệu: //bit.ly/tailieuztechbook

thi

lớn nhất.

20


Tạo database
21và viết câu truy vấn Sql Server

create view cau84
as
select distinct dmsv.masv,tensv,tenmh,max[diem]diem
from dmsv,ketqua,dmmh
where dmsv.masv=ketqua.masv and dmmh.mamh=ketqua.mamh
group by dmsv.masv,tensv,tenmh
select * from cau84
--85. Danh sách sinh viên: Không rớt lần 1 hoặc ,Không học môn
văn phạm
create view cau85
as
select *
from dmsv
where masv in
[select masv
from ketqua
where [lanthi=1 and diem =all[select count[masv]
from dmsv
where phai=N'nữ'
group by makhoa]]

/*===============HẾT================*/

Xem thêm tài liệu: //bit.ly/tailieuztechbook

21


Tạo database
22và viết câu truy vấn Sql Server

II. QUẢN LÝ BÁN HÀNG - TẠO DATABASE VÀ
VIẾT CÂU TRUY VẤN
/* Tạo Cơ Sở Dữ Liệu */
CREATE DATABASE QuanLyBanHang
USE QuanLyBanHang
/* Tạo Bảng Hàng */
CREATE TABLE HANG
[
MaH Char[10] PRIMARY KEY,
TenH Varchar[100] NOT NULL,
DVT Char[10] NOT NULL,
NoiSX Varchar[20],
SoLuong Int
]
/* Tạo Bảng Khách */
CREATE TABLE KHACH
[
Mak Char[10] PRIMARY KEY,
TenK Varchar[50] NOT NULL,
DiaChi Varchar[20],
SoDT Char[15]
]
/* Tạo Bảng Hóa Ðơn */
CREATE TABLE HOADON
[
SoHD Char[10] PRIMARY kEY,
Ngay DateTime NOT NULL,
MaK Char[10],
CONSTRAINT MaK_FK FOREIGN KEY [MaK] REFERENCES KHACH [MaK]
]

/* Tạo Bảng Chi Tiết Hóa Đơn */
CREATE TABLE CHITIETHOADON
[
SoHD Char[10],
MaH Char[10],
SoLuong int NOT NULL,
DonGia Money NOT NULL,
CONSTRAINT MaH_SoHD_PK PRIMARY KEY [MaH, SoHD],
Xem thêm tài liệu: //bit.ly/tailieuztechbook

22


Tạo database
23và viết câu truy vấn Sql Server

CONSTRAINT MaH_FK FOREIGN KEY [MaH] REFERENCES HANG [MaH],
CONSTRAINT SoHD_FK FOREIGN KEY [SoHD] REFERENCES HOADON [SoHD]
]
/* Nhập Dữ Liệu Cho Các Bảng */
INSERT INTO HANG
VALUES
['MaH01','But Bi','Cai','Ha Noi','5000'],
['MaH02','But chi','Cai','Thai Nguyen','5000'],
['MaH03','Thuoc Ke','Cai','Hoa Binh','6000'],
['MaH04','Com Pa','Cai','Hai Phong','5500'],
['MaH05','Vo Viet','Quyen','Cao Bang','7000']
INSERT INTO KHACH
VALUES
['MaK01','Nguyen Bao An','Ha Noi','0989009876'],

['MaK02','Hoang Thu Thuy','Ha Tinh','0978409876'],
['MaK03','Tran Thi Trang','Nghe An','0967679854'],
['MaK04','Vu Hong Quan','Thai Nguyen','0989076454'],
['MaK05','Ngo Van Tung','Hai Phong','0989346578']
INSERT INTO HOADON
VALUES
['HD01','09/23/2014','MaK01'],
['HD02','01/19/2014','MaK01'],
['HD03','05/01/2014','MaK03'],
['HD04','04/01/2014','MaK04'],
['HD05','04/27/2014','MaK04']
INSERT INTO CHITIETHOADON
VALUES
['HD01','MaH01','1000','3000'],
['HD02','MaH02','2000','2500'],
['HD03','MaH02','1500','5000'],
['HD04','MaH05','1000','4000'],
['HD05','MaH03','2000','6000']
/* Tạo View Tổng Hợp Thông Tin Về Các Khách Hàng Đã Mua Hàng Tại Cửa
Hàng */
CREATE VIEW VWKACHMUAHANG
AS
SELECT *
FROM KHACH
WHERE MaK IN [ SELECT MaK
FROM HOADON
Xem thêm tài liệu: //bit.ly/tailieuztechbook

23

Tạo database
24và viết câu truy vấn Sql Server

]
/* Tạo View Tổng Hợp Thông Tin Về Khách Hàng Có Địa Chỉ ở Thái Nguyên
Và Từng Mua Hàng Tại Cửa Hàng */
CREATE VIEW VWDIACHIKHACH
AS
SELECT *
FROM KHACH
WHERE MaK IN [ SELECT MaK
FROM HOADON
]
AND DiaChi='Thai Nguyen'
/* Tạo View Tổng Hợp Thông Tin Về Các Mặt Hàng Được Sản Xuất Tại Thái
Nguyên Hoặc Cao Bằng */
CREATE VIEW VWNOISX
AS
SELECT *
FROM HANG
WHERE NoiSX='Thai Nguyen' OR NoiSX='Cao Bang'
/* Tổng Hợp Thông Tin Về Các Khách Hàng Đã Từng Mua Các Mặt Hàng Được
Sản Xuất Tại Thái Nguyên */
CREATE VIEW VWKHACHMUAHANG
AS
SELECT *
FROM KHACH
WHERE MaK IN [ SELECT MaK
FROM HANG, HOADON, CHITIETHOADON

WHERE HOADON.SoHD=CHITIETHOADON.SoHD
AND HANG.MaH=CHITIETHOADON.MaH
AND NoiSX='Thai Nguyen'
]
/* Tạo View Tổng Hợp Thông Tin Về Các Mặt Hàng Đã Được Bán Trong Ngày
Mùng 01/04/2014 */
CREATE VIEW VWHANGDUOCBAN
AS
SELECT *
FROM HANG
WHERE MaH IN [ SELECT MaH
FROM CHITIETHOADON
WHERE SoHD IN [ SELECT SoHD
FROM HOADON
WHERE DAY[Ngay]='01'
Xem thêm tài liệu: //bit.ly/tailieuztechbook

24


Tạo database
25và viết câu truy vấn Sql Server

AND MONTH[Ngay]='04'
AND YEAR[Ngay]='2014'
]

]

/* Tạo View Tổng Hợp Thông Tin Về Các Khách Hàng Đã Mua Hàng Trong

Ngày 01/05/2014 */
CREATE VIEW VWTTKHACHHANG
AS
SELECT *
FROM KHACH
WHERE MaK IN [ SELECT MaK
FROM HOADON
WHERE DAY[Ngay]='01'
AND MONTH[Ngay]='05'
AND YEAR[Ngay]='2014'
]
/* Tạo View Tổng Hợp Thông Tin Về Các Mặt Hàng Không Bán Được Trong
Tháng 2 Năm 2014 */
CREATE VIEW VWHANGCHUABANDUOC
AS
SELECT *
FROM HANG
WHERE MaH NOT IN [ SELECT MaH
FROM CHITIETHOADON
WHERE SoHD IN [SELECT SoHD
FROM HOADON
WHERE MONTH[NGAY]='02'
AND YEAR[NGAY]='2014'
]
]

Xem thêm tài liệu: //bit.ly/tailieuztechbook

25