RDBMS – Bài thực hành 02

Bài 1: Cài đặt SQL Server 2005
Đọc file: Huong dan cai đat SQL Server 2005.doc
Bài 2: Tạo và xóa database từ giao diện đồ họa
Bài 3: Xem thông tin về file *.mdf và *.ldf
Bài 4: Backup và Restore 1 database
Bài 5: Attach và detach 1 database
Bài 6: Tạo user và login

Câu lệnh: CREATE, INSERT, UPDATE,DELETE
Bài 1:
Tạo CSDL quản lý điểm học viên. CSDL có tên là DIEM và gồm 3 bảng
Bảng Student:
Student(RN,Name,Age,Gender)
Bảng Subject(môn học):
Subject(sID, sName)
Bảng StudentSubject
StudentSubject(RN,SID,Mark,Date).
1. Tạo các bảng trên
a. Đặt khóa chính cho các bảng:
Student

khóa là RN //Roll Number: mã sinh viên

Subject

khóa là sID //Subject Identify

StudentSubject khóa là (RN,sID)
b. Đặt ràng buộc để trường Mark chỉ nhận các giá trị trong khoảng [0->10]


LỜI GIẢI
create database DIEM
use DIEM
--CAU 1
create table Student(rn int,name varchar(25),age int,gender bit)
create table Subject(sID int,sName varchar(10))
create table StudentSubject(rn int,sID int,Mark int,Date datetime)
go
--CAU 1a
alter table Student
alter column rn int not null
go
alter table Student
add constraint PK1 primary key(rn)
go
-alter table Subject
alter column sID int not null
go
alter table Subject
add constraint PK2 primary key(sID)
go
-alter table StudentSubject

3


RDBMS – Bài thực hành 02
alter column sID int not null
go

Insert subject values(3,'HTML')
go

4


RDBMS – Bài thực hành 02
Insert StudentSubject values(3,3,9,'7/31/2005')
-- CAU 2d
Insert student (rn,name) values(4,'Tai Linh')
go
Insert StudentSubject values(4,1,5,'7/30/2005')
-- CAU 2e
Insert student (rn,name) values(5,'My Le')
Insert subject values(4,'CF')
go
Insert StudentSubject values(5,4,10,'7/19/2005')
-- CAU 2f
Insert student (rn,name) values(6,'Ngoc Oanh')
go
Insert StudentSubject values(6,1,9,'7/25/2005')
go
-- CAU 3
-- CAU 3a
update student
set gender=0
where name in ('My Linh','Tai Linh','My Le')
go
-- CAU 3b
update student

select sName,count(*) from subject s left join studentsubject ss on
s.sID=ss.sID
group by sName
having count(*)>1
--select sName from subject
--where sID in (select sID from studentsubject ss where subject.sID=ss.sID
group by sID having count(*)>1)

Câu lệnh: SELECT
Phần I: QUẢN LÝ SINH VIÊN MƯỢN SÁCH
Đề bài
Tạo cơ sở dữ liệu quản lý sách (QuanLySach) với 2 bảng dữ liệu được cho như sau:
Student

6


RDBMS – Bài thực hành 02

RN

Name

Age

1

Pham Ngoc Hung

28


Và bảng Book chứa thông tin mượn sách của sinh viên:
Book
RN

BookTitle

1

SQL

2

EPC

1

HTML

1

Java

2

DW

3

Javascript

create database QuanLySach;
use QuanLySach;
go;
-- Tao bang Student
create table Student
(
RN int identity(1,1) PRIMARY KEY,
[name] nvarchar(32)
)
-- Them mot cot vao bang
8


RDBMS – Bài thực hành 02

alter table Student
add age int NOT NULL DEFAULT (18)
go
-- HIen thi de xem da tao xong chua
select * from Student;
-- Tao bang Book
create table Book
(
BookId int identity(100,2) PRIMARY KEY,
BookTitle nvarchar(256) NOT NULL,
RN int -- foreign key references Student(RN)
)
-- them rang buoc vao bang da co san
alter table Book
add constraint fk foreign key (RN) references

'Javascript');
-- hien thi du lieu vua nhap vao bang Book
select * from Book;
-- cau 1.1
---- Cach 1
select * from Student
select DISTINCT RN from Book;
select RN, [Name], Age from Student where RN in (select
DISTINCT RN from Book);
---- Cach 2
------ Noi 2 bang ma khong co dieu kien gi ca!!!
select Student.RN, Student.[Name], Student.Age, Book.RN,
Book.BookTitle
from Student, Book

10


RDBMS – Bài thực hành 02

------ Noi 2 bang, nhuwng chi chon Student.ID trung voi
Book.ID (chi chon
------ nhung sinh vien muon sach)
select Student.RN, Student.[Name], Student.Age, Book.RN,
Book.BookTitle
from Student, Book where Student.RN = Book.RN;
------ Loai bo truong Book.RN vi thua
select Student.RN, Student.[Name], Student.Age,
Book.BookTitle
from Student, Book where Student.RN = Book.RN;

from Student, Book
where (Student.RN = Book.RN) and (Student.[Name] =
'Tran Tuan Duong');
---- Loai bo cac truong khong can thiet
select Student.[Name], Book.BookTitle
from Student, Book
where (Student.RN = Book.RN) and (Student.[Name] =
'Tran Tuan Duong');
---- Cau hoi: Tran Tuan Duong muon bao nhieu quyen sach?
select COUNT(*) AS [So quyen sach muon]
from Student, Book
where (Student.RN = Book.RN) and (Student.[Name] =
'Tran Tuan Duong');
-- Cau 1.4
---- Cach 1
Select * from Student s1, Student s2;
Select s1.RN, s1.[Name], s1.Age from Student s1, Student
s2
where (s1.[RN] != s2.[RN]) and (s1.Age = s2.Age)
---- Cach 2
------ Dau tien la cho ra nhung tuoi co nguoi trung nhau
Select Age from Student Group by Age Having Count(RN) >=
2;

12


RDBMS – Bài thực hành 02

------ Loc ra ten nhung nguoi co tuoi nam trong nhom nay



RDBMS – Bài thực hành 02

-- Cau 2.2
Select [Name] from Student
where Age = (Select MAX(Age) FROM Student);
Select [Name] from Student
where Age = (Select MIN(Age) FROM Student);
-- cau 2.4
select Age, count(RN) from student Group By Age;

-- cau 2.5 ???
select Age, count(RN) from student Group By Age;
-- Cau 2.6 Hoc vien nao muon nhieu sach nhat???
DECLARE @TEMP
Select MAX([SoLuong]) From (Select COUNT(*) As [SoLuong]
From Book Group By RN);
Phần II: QUẢN LÝ BÁN HÀNG
1. Sử dụng các câu lệnh T-SQL
Tạo CSDL để quản lý sản phẩm đã bán của một cửa hàng, cơ sở dữ liệu có tên là
QLHANG, tạo 3 bảng để lưu trữ các thông tin sau:
Bảng Customers: lưu trữ thông tin về khách hàng: customerid, customername,tei.
Bảng Items: lưu trữ thông tin về hàng hóa: itemid, itemname, quantity, prìce
Bảng CustomerItem: lưu trữ thông tin về những sản phẩm đã dược bán mua:
customerid,itemid,quantity.
2. Nhập các thông tin sau vào CSDL(Sử dụng câu lệnh T-SQL)
a. Nhập dữ liệu bảng Customers như sau

14

customerid int not null,
customername varchar(50),
tel varchar(10))
go
--tao bang items
create table items(
itemid int not null,
itemname varchar(50),
quantity int,
price float)
go
--tao bang customeritem
create table customeritem(
customerid int,
itemid int,
quantity int)
go
--CAU 2a
insert customers values(1,'dinh truong son','1234567')
insert customers values(2,'mai thanh minh','1357777')
insert customers values(3,'nguyen hong ha','2468888')
go
--CAU 2b
insert items values(1,'tu lanh',5,3500)
insert items values(2,'tivi',2,3000)
insert items values(3,'dieu hoa',1,8000)

16




17

ci on


RDBMS – Bài thực hành 02

i.itemid =ci.itemid
group by customername
order by sum(ci.quantity*price) desc
go
--CAU 6
if exists(select itemname from items inner join
customeritem on items.itemid = customeritem.itemid
inner join customers on customers.customerid =
customeritem.customerid
where itemname = 'tu lanh' and tel = '2468888')
select 'co mua'
else
select 'khong mua'
go
--CAU 7
select sum(items.quantity-customeritem.quantity) as
conlai,
sum(customeritem.quantity*price) as tienlai
from items inner join customeritem on
items.itemid = customeritem.itemid
go
--CAU 8

having sum(quantity) > 1)
go

Phần III: Tự làm
Câu 1
Xây dựng cơ sở dữ liệu có tên là LopHoc, có một bảng dữ liệu tên là DSSV (danh sách sinh
viên), trong bảng này có các trường dữ liệu như sau:
Tên dữ liệu

Tên trường

Loại dữ liệu

Mã sinh viên

masv

Chuỗi ký tự có đúng 6 ký tự.

Tên sinh viên

tensv

Chuỗi ký tự không vượt quá 32 ký tự.

Tuổi

tuoi

Số nguyên



Xóa Bảng DSSV khỏi cơ sở dữ liệu LopHoc.



Xóa Cơ sở dữ liệu LopHoc.

Hướng dẫn
* Để tạo mới cơ sở dữ liệu, ta nhập lệnh sau vào cửa sổ Query:
CREATE DATABASE LopHoc;
Tô đậm lệnh trên và bấn F5 để chạy.
* Để tạo một bảng mới, ta chuyển vào làm việc trong cơ sở dữ liệu này (bằng lệnh USE), sau
đó tạo bảng bằng các lệnh sau:
USE LopHoc;
GO;
CREATE TABLE DSSV (
masv nvarchar(6) PRIMARY KEY,
tensv nvarchar(32),
tuoi int,
sodt nvarchar(15),
diachi nvarchar(256)
)
Nhập các lệnh trên vào cửa sổ Query, tô đậm vùng lệnh và bấm F5 để chạy.
* Để nhập dữ liệu cho bảng, ta sử dụng câu lệnh INSERT như sau:
INSERT INTO DSSV (masv, tensv, tuoi, sodt, diachi)
VALUES ('A012345', 'Pham Ngoc Hung', 32, '0982872507', 'Hoang Quoc Viet, Cau Giay,
Ha Noi');

20

bảng có tên là Sach, trong đó có các trường dữ liệu sau:
Tên dữ liệu Tên trường

Dạng dữ liệu

21

Kiểu dữ liệu

Ràng buộc


RDBMS – Bài thực hành 02

masach (trường khóa) Chuỗi ký tự không nvarchar(20)

Mã sách

Trường khóa

vượt quá 20 ký tự
Tiêu đề sách tieude

Chuỗi ký tự không nvarchar(100) Không được bỏ
vượt quá 100 ký tự

trống (NOT NULL)

Chuỗi ký tự không nvarchar(32)



trống

tháng (DateTime)

Chuỗi ký tự không nvarchar(256) Được phép bỏ trống

nhaxuatban

bản

vượt quá 256 ký tự.

Giá bán (ghi dongia

Số thực

float

Không được phép
bỏ trống

trên bìa)
Số lượng

Không được bỏ

Số nguyên

soluong

học kỹ thuật

Lục
B00002 Giải tích hàm Phạm Kỳ

12/12/2005 NXB Đại học 75000 900
quốc gia Hà

Anh

22


RDBMS – Bài thực hành 02
Nội
B00101 Đất rừng

Đoàn Giỏi

10/03/2003 NXB văn học 20000 2000

Chu Lai

13/11/2005 NXB văn học 54000 1500

Phạm Ngọc

12/12/2001 NXB văn học 12000 4000

phương nam

BANDOC
STT

MaBD char(2)

Ten varchar(35)

Tuoi(int)

1
2

---

---

---

SACH
STT MaSach char(4)
1

--

Tensach
(varchar)
--

Soluong
(number)


--

--

Ngaymuon
(Date)
--

NgayTra
(Date)
--

Soluong
(number)
--

Tạo lập các ràng buộc
1. Tạo ràng buộc khoá chính trên các bảng BANDOC, SACH(MaBD, Masach)
2. Tạo ràng buộc khoá ngoại trên bảng MUONTRA
3. Tạo ràng buộc indentity(1,1) trên trường STT của các bảng
4. Cập nhật lại số lượng đầu sách trong bảng SACH theo điều kiện dưới đây:
Nếu mã loại (MaLoai=’TH’) thì số lượng sách = 500

24