ÔN TẬP SQL

BIÊN SOẠN: JAOSU

HTTP://HUEIT.INFO

HTTP://TINHOC360.NET

1. Thao tác dử liệu:

SELECT : Lấy, lọc dử liệu

INSERT: Thêm dử liệu

UPDATE: Sửa dử liệu

DELETE: Xóa dử liệu

2. Lập trình:

DECLARE: Khai báo biến

3. Các kiểu dử liệu:

a. Kiểu sô:

INT: nguyên

FLOAT: thập phân

DECIMAL(m,n)

b. Kiểu chuổi:

char(n)

varchar(n): không lưu trử ký tự có dấu

nvarchar(n): lưu trử ký tự có dấu

text(n):

ntexr(n): có dấu

c. Kiểu logic:

bit: có giá trị là 0 và 1

d. Kiểu ngày tháng:

datetime

4. Các hàm thường dùng:

Getdate(): lấy ngày hiện tại

Year(): Lấy năm

Month(): Lấy tháng

Day(): lấy ngày

Isnull(): kiểm tra giá trị có null (rổng) hay không

Datediff(dd | mm | yy,ngày 1 tháng 1 năm 1,ngày 2 tháng 2 năm 2): Tính xem có bao nhiêu ngày, tháng, năm từ ngày 1 tháng 1 năm 1 đến ngày 2 tháng 2 năm 2

Not in : không nằm trong

Sum(): tính tổng

Count(): đếm

Avg(): tính trung bình

Max(): lấy số lớn nhất

Min(): lấy số bé nhất

(*) Lưu ý:

SQL không phân biệt chử hoa, chử thường

Để hiển thị một chuổi là tiến việt thì ta cần viết chử " N" ở đầu chuổi

%: đại diện một chuổi

Like: tìm kiếm tương tự (vd:where diachi like ' % huế %')

Default: gán giá trị mặc định (vd: gioitinh bit default 1)

[tên bảng].<tên trường> : gọi tên trường trong một bảng

Nếu tên trường là duy nhất thì không cần gọi bảng

5. Tạo CSDL: Create database <tên CSDL>

6. Xóa CSDL: drop database <tên CSDL>

7. Tạo bảng:

Create table <tên bảng>

<tên cột 1> <kiểu dử liệu> [<các ràng buộc>,]

<tên cột 2> <kiểu dử liệu> [<các ràng buộc>,]

.......

<tên cột n> <kiểu dử liệu> [<các ràng buộc>,]

8. Tạo ràng buộc khóa chính:

Constraint <ten rang buoc> primary key (danh sach cac cot)

9. Tạo ràng buột khóa ngoại:

Constraint <ten rang buoc> foreign key (ds cac cot) references <ten bang>(<ten cot>)

10. Ràng buộc điều kiện

Constraint <ten rang buoc> check <cac dieu kien>

11. Sửa bảng:

a. Thêm cột:

Alter table <tên bảng>

Add <định nghĩa cột>

b. Sửa cột:

Alter table <tên bảng>

Alter column <tên cột> <kiểu dử liệu>

c. Xóa cột :

Alter table <tên bảng>

Drop column <tên cột>

d. Xóa bảng:

Alter table <tên bảng>

Drop table <tên bảng>

e. Thêm ràng buộc:

Alter table <tên bảng>

Add constraint <định nghĩa ràng buộc>

f. Xóa ràng buộc:

Alter table <tên bảng>

Drop constraint <tên ràng buộc>

g. Thêm ràng buột mặc định:

Alter table <tên bảng>

Add constraint <tên ràng buộc> default <giá trị mặc định> for <tên côt>

NGÔN NGỬ THAO TÁC DỬ LIỆU

1. Câu lênh SELECT:

Select [all (lấy hết dl)|distinct|top n][ds các cột tham gia truy vấn|* (lấy hết các trường)][into <tên bảng>]

From <ds bảng tham gia truy vấn>

[where <điều kiện lọc dử liệu>]

[group by <ds các cột cần gom nhóm>]

[having <điều kiện lọc dử liệu>]

[order by <các cột cần sắp xếp thứ tự>] [ASC(tăng dần)|DESC(giãm dần)]

(*) Dạng CASE.....WHEN

Case

When <bt điều kiện 1> then <bt1>

When <bt điều kiện 2> then <bt2>

.............

When <bt điều kiện n> then <btn>

Else <bt n+1>

End

Vd:

Select manv,hoten,case when gioitinh=1 then 'nam' when gioitinh=0 then N'nu' end

From nv

BÀI TẬP:

Bài 1:

1. Dùng lệnh create để tạo CSDL có tên quanlyduan và các bảng sau:

PHONG(maphong - pk,tenphong)

DUAN(maduan - pk,tenduan,diadiemda,maphong -fk)

NHANVIEN(manv - pk,hoten,gioitinh,ngaysinh,maphong -fk,luong)

DIADIEMDA(maphong -fk,diadiem)

CHAMCONG(manv -fk,maduan -fk,sogio)

2a. Tạo các khóa chính và khóa ngoại như trên bảng ( trong đó: pk: khóa chính; fk: khóa ngoại)

2b. Bổ sung ràng buộc cho bảng NHANVIEN để đảm bảo rằng 1 nv chỉ có thể làm việc trong Cty khi tuổi từ 18 - 60 tuổi

2c. Bổ sung ràng buộc thiết lập giá trị mặc định là 1 cho cột gioitinh và là 0 cho cột lương nhân viên

Bài giải:

--1a: Tao database co ten quanlyduan

create database quanlyduan

--1b: Tao bang

create table phong

maphong int not null,

tenphong nvarchar(30)

create table duan

maduan int not null,

tenduan nvarchar(30),

diadiemda nvarchar(50),

maphong int

create table diadiemdv

maphong int,

diadiem nvarchar(50)

create table nhanvien

manv int not null,

hoten nvarchar(30),

gioitinh bit,

ngaysinh datetime,

maphong int,

luong int,

create table chamcong

manv int,

maduan int,

sogio int,

--2a1: Tao khoa chinh

alter table phong

add constraint pk_maphong primary key(maphong)

alter table duan

add constraint pk_maduan primary key(maduan)

alter table nhanvien

add constraint pk_manv primary key(manv)

--2a2: Tao khoa ngoai

alter table duan

add constraint fk_maphong foreign key (maphong) references phong(maphong)

alter table nhanvien

add constraint fk_nv_maphong foreign key (maphong) references phong(maphong)

alter table chamcong

add constraint fk_cc_manv foreign key (manv) references nhanvien(manv)

alter table chamcong

add constraint fk_cc_maduan foreign key (maduan) references duan(maduan)

alter table diadiemdv

add constraint fk_dd_maphong foreign key (maphong) references phong(maphong)

--2b:

alter table nhanvien

add constraint chk_ns check ((year(getdate())-year(ngaysinh)>=18) and (year(getdate())-year(ngaysinh)<=60))

--2c:

alter table nhanvien

add constraint default_gioitinh

default 1 for gioitinh

alter table nhanvien

add constraint default_luong default 0 for luong

Bài 2:

1. Tạo CSDL có tên là quanlydiem (bằng lệnh create) gồm các bảng:

MONHOC(mamh - pk,tenmh)

SV(masv - pk,tensv,tuoi)

DIEM(masv - fk,mamh - fk,tensv,ngaythi,diem)

2. Tạo các ràng buộc sau:

a. Tạo các khóa chính

b. Tạo các khóa ngoại

c. Tạo ràng buộc điểm từ 0 - 10; sv có tuổi từ 18 trở lên

3. Thực hiện các truy vấn sau:

a. Lấy ra những sinh viên có họ là Nguyễn

b. Lấy ra các môn học mà có sinh viên thi

c. Lấy ra ds sinh viên và môn học tương ứng mà họ đã thi

d. Lấy ra nhưng sv thi SQL trên 5 điểm

e. Tìm sv có tuổi cao nhất

f. Tính xem mỗi sv đã thi được bao nhiêu ngày rồi

g. Hiển thị ds các sv chưa thi môn nào

h. Lấy ra những sv đạt điểm cao nhất

Bài giải:

-- tao database

create database quanlydiem

-- tao bang

create table monhoc

mamh int not null,

tenmh nvarchar(30)

create table sv

masv int not null,

tensv nvarchar(30),

tuoi int,

create table diem

masv int,

mamh int,

ngaythi datetime,

diem int,

--2a.

alter table monhoc

add constraint pk_monhoc primary key (mamh)

alter table sv

add constraint pk_sv primary key (masv)

--2b.

alter table diem

add constraint fk_diem_mamh foreign key (mamh) references monhoc(mamh)

alter table diem

add constraint fk_diem_masv foreign key (masv) references sv(masv)

--2c.

alter table diem

add constraint chk_diem check (diem>=0 and diem<=10)

alter table sv

add constraint chk_tuoi check (tuoi>=18)

--3a.

select *

from sv

where tensv like N'Nguyễn %'

--3b.

select diem.mamh,tenmh

from monhoc,diem

where monhoc.mamh=diem.mamh

--3c.

select diem.masv,tensv,diem.mamh,tenmh

from monhoc,sv,diem

where monhoc.mamh=diem.mamh and sv.masv=diem.masv

--3d.

select tensv,diem,tenmh

from sv,diem,monhoc

where sv.masv=diem.masv and monhoc.mamh=diem.mamh and diem>5 and tenmh='sql'

--3e.

select masv,tensv,tuoi

from sv

where tuoi=(select MAX(tuoi) from sv)

--3f.

select sv.masv,tensv,tenmh,DATEDIFF(dd,ngaythi,getdate())

from sv,diem,monhoc

where sv.masv=diem.masv and monhoc.mamh=diem.mamh

--3g.

select masv,tensv

from sv

where masv not in (select masv from diem)

--3h.

select tensv,diem

from sv,diem

where sv.masv=diem.masv and diem=(select MAX(diem) from diem)

Bài 3:

1. Dùng lệnh Create tạo CSDL có tên quanlybanhang gồm các bảng

KHACHHANG(makh,tenkh - not null, tuoi)

SANPHAM (masp,tensp,dongia)

HOADON(mahd,makh,ngaylaphd,tongtien)

CHITIETHD(mahd,masp,soluong)

2. Tạo các truy vấn sau

a. Lấy ra những khách hàng trên 50 tuổi

b. Lấy ra những sp được bán trong ngày 10/10/2010

c. Lấy ra những khách hàng đã mua những mặt hàng nào trong ngày 10/10/2010

d. Tính tổng tiền của mỗi hóa đơn

e. Lấy ra những sp chưa bán lần nào

f. Hiển thị ds khách hàng đã mua hàng và ds sp được mua bởi khách hàng đó

g. Hiển thị tên và giá của những sp có giá cao nhất

Bài giải:

--tao database

create database quanlybanhang

--tao bang

create table kh

makh int constraint pk_makh primary key (makh),

tenkh nvarchar(30) not null,

tuoi int,

create table sp

masp int constraint pk_masp primary key (masp),

tensp nvarchar(100),

dongia int,

create table hd

mahd int constraint pk_mahd primary key (mahd),

makh int constraint fk_makh foreign key (makh) references kh(makh),

ngaylaphd datetime,

tongtien int,

create table cthd

mahd int constraint fk_mahd foreign key (mahd) references hd(mahd),

masp int constraint fk_masp foreign key (masp) references sp(masp),

soluong int,

--2a.

select makh,tenkh,tuoi

from kh

where tuoi>50

--2b.

select sp.masp,tensp

from sp,cthd,hd

where ngaylaphd='10/10/2010' and hd.mahd=cthd.mahd and sp.masp=cthd.masp

--2c.

select tenkh,tensp

from kh,hd,sp,cthd

where kh.makh=hd.makh and sp.masp=cthd.masp and hd.mahd=cthd.mahd and ngaylaphd='10/10/2010'

--2d.

select hd.mahd,ngaylaphd,sum(soluong*dongia) as tongtien

from sp,hd,cthd

where sp.masp=cthd.masp and hd.mahd=cthd.mahd

group by hd.mahd,ngaylaphd

--2e.

select masp,tensp

from sp

where masp not in (select masp from cthd)

--2f.

select tenkh,tensp

from kh,sp,hd,cthd

where kh.makh=hd.makh and sp.masp=cthd.masp and hd.mahd=cthd.mahd

--2g.

select tensp,dongia

from sp

where dongia=(select max(dongia) from sp)

Bài 4:

1. Dùng lệnh create để tạo CSDL quanlynhanvien với các bảng sau:

CV(macv - char(5) - pk, tencv - nvarchar(30))

TP(matp - char(5) - pk, tentp - nvarchar(50))

NV(manv - int - pk, tennv - nvarchar(30), macv - char(5) - fk)

PHICT(macv - char(5) - pk, matp - char(5) - pk, phi1ngay - int)

DOTCT(madot - int - pk, matp - char(5) - fk, songay - int, ngaydi - datetime)

THAMGIACT(madot - int - pk, manv - int - pk)

2. Dùng lệnh Alter thực hiện:

2a. Tạo khóa chính

2b. Tạo khóa ngoại

2c. Tạo giá trị mặc định số ngày ở bảng DOTCT = 0

3. Thực hiện các truy vấn sau:

a. Liệt kê các nhân viên có chức vụ là giám đốc

b. Liệt kê chức vụ, thành phố mà có phí công tác trên 50.000

c. Liệt kê danh sách các mã đợt, ngày đi mà đến thành phố có tên là HCM với số ngày đi nhiều hơn 7 ngày

d. Liệt kê danh sách manv, tennv chưa tham gia vào các đợt công tác đi trong tháng 10/2010 đến thành phố có tên Hà Nội

e. Liệt kê danh sách manv, tennv chưa tham gia chuyến công tác nào

f. Liệt kê tổng số đợt đi công tác của mỗi nhân viên

g. Liệt kê danh sách các nhân viên có tổng số ngày đi công tác lớn hơn 100 ngày

Bài giải:

--tao CSDL

create database quanlynhanvien

--tao bang

create table cv

macv char(5) not null,

tencv nvarchar(30),

create table tp

matp char(5) not null,

tentp nvarchar(50),

create table phict

macv char(5) not null,

matp char(5) not null,

phi1ngay int,

create table nv

manv int not null,

tennv nvarchar(30),

macv char(5),

create table dotct

madot int not null,

matp char(5),

songay int,

ngaydi datetime,

create table thamgiact

madot int not null,

manv int not null,

--2. Dung lenh Alter de tao khoa

--a. Tao khoa chinh

alter table cv

add constraint pk_cv_macv primary key (macv)

alter table tp

add constraint pk_tp_matp primary key (matp)

alter table phict

add constraint pk_pct_macv_matp primary key (macv,matp)

alter table nv

add constraint pk_nv_manv primary key (manv)

alter table dotct

add constraint pk_dct_madot primary key (madot)

alter table thamgiact

add constraint pk_tgct_madot_manv primary key (madot,manv)

--b. Tao khoa ngoai

alter table nv

add constraint fk_nv_macv foreign key (macv) references cv(macv)

alter table dotct

add constraint fk_dotct_matp foreign key (matp) references tp(matp)

alter table thamgiact

add constraint fk_tgct_madot foreign key (madot) references dotct(madot)

alter table thamgiact

add constraint fk_tgct_manv foreign key (manv) references nv(manv)

alter table phict

add constraint fk_pct_macv foreign key (macv) references cv(macv)

alter table phict

add constraint fk_pct_matp foreign key (matp) references tp(matp)

--c. Tao gia tri mac dinh so ngay o bang dotct = 0