bài tập cá nhân hệ quản trị cơ sở dữ liệu

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 (1.43 MB, 20 trang )

<span class="text_page_counter">Trang 1</span><div class="page_container" data-page="1">

<b>ĐẠI HỌC DUY TÂN</b>

</div><span class="text_page_counter">Trang 2</span><div class="page_container" data-page="2">

use QLNVien;

-- Bang phòng ban

Create Table Phong (

MaPhong char(10) not null, TenPhong nvarchar(50) not null, DiaDiem nvarchar(100) not null, soNV int,

constraint pk_MaPhong primary key (MaPhong), constraint ch_SoNV check (SoNV>= )0

-- Bang nhân viên

Create Table NhanVien MaNV char (10)not null, Holot nvarchar(30)not null, TenNV nvarchar(10)not null, SDT varchar (20),

MaPhong char(10) not null,

constraint pk_MaNV primary key (MaNV),

constraint fk_MaPhong foreign key (MaPhong) REFERENCES Phong MaPhong) );

-- Bang dự án

Create Table Duan( MaDA char (10)not null, TenDA nvarchar(100) not null,

Kinhphi float,constraint pk_MaDA primary key (MaDA), constraint ch_kinhphi check (kinhphi>= )0

-- Bang nhân viên dự án

Create Table NVDA( MaNV char (10)not null, MaDA char (10)not null, sogio int,

constraint pk_a primary key (MaNV MaDA, ),

constraint fk_MaNV foreign key (MaNV ) REFERENCES Nhanvien MaNV), constraint fk_MaDA foreign key (MaDA ) REFERENCES DuAn MaDA), constraint ch_sogio check (sogio>= )0

-- Thêm trường cho bang phòng

alter table Phong ADD Diachi nvarchar (100); Select From Phong

-- Thêm dữ liệu cho bang Phòng

insert into Phong Select From Phong

-- Thêm dữ liệu cho bang Nhân Viên

Select from NhanVien

</div><span class="text_page_counter">Trang 3</span><div class="page_container" data-page="3">

-- Bang phòng ban

Create Table Phong (

MaPhong char(10) not null, TenPhong nvarchar(50) not null, DiaDiem nvarchar(100) not null, soNV int,

constraint pk_MaPhong primary key (MaPhong), constraint ch_SoNV check (SoNV>= )0

-- Bang nhân viên

Create Table NhanVien MaNV char (10)not null, Holot nvarchar(30)not null, TenNV nvarchar(10)not null, SDT varchar (20),

MaPhong char(10) not null,

constraint pk_MaNV primary key (MaNV),

constraint fk_MaPhong foreign key (MaPhong) REFERENCES Phong MaPhong) );

-- Bang dự án

Create Table Duan( MaDA char (10)not null, TenDA nvarchar(100) not null,

Kinhphi float,constraint pk_MaDA primary key (MaDA), constraint ch_kinhphi check (kinhphi>= )0

-- Bang nhân viên dự án

Create Table NVDA( MaNV char (10)not null, MaDA char (10)not null, sogio int,

constraint pk_a primary key (MaNV MaDA, ),

constraint fk_MaNV foreign key (MaNV ) REFERENCES Nhanvien MaNV), constraint fk_MaDA foreign key (MaDA ) REFERENCES DuAn MaDA), constraint ch_sogio check (sogio>= )0

);

</div><span class="text_page_counter">Trang 4</span><div class="page_container" data-page="4">

alter table Phong ADD Diachi nvarchar (100); Select From Phong

-- Thêm dữ liệu cho bang Nhân Viên

insert into NhanVien

--Thêm dữ liệu cho bang Dự Án

select from Duan insert into Duan

values (N'DA01' N'Xây nhà 1 TâG, ng',N'3000000'); insert into Duan

values (N'DA02' N'Xây nhà TâG, 2 ng',N'3000000'); insert into Duan

values (N'DA03' N'Xây nhà TâG, 3 ng',N'3000000'); insert into Duan

values (N'DA04' N'Xây nhà TâG, 4 ng',N'3000000'); insert into Duan

values (N'DA05' N'Xây nhà TâG, 5 ng',N'3000000');

</div><span class="text_page_counter">Trang 5</span><div class="page_container" data-page="5">

select from NVDA insert into NVDA

-- tẠO BANG có câSu trúc như sau LUUNV1 (MANV, TENNV,MaPhong)

drop table LuuNVPhong1 create table LuuNVPhong1 (

MaNV Char(10)not null, TenNV nvarchar (10) not null, MaPhong char(10) not null,

constraint pk_MaNV1 primary key (MaNV) );

-- MuổSn xóa ban ... sư dụng câu lệnh ở dưới

drop table Phong drop table Duan drop table Nhanvien drop table NVDA

--- Xóa dữ liệu cua bang

delete from NhanVien

---select from LuuNVPhong1 insert into LuuNVPhong1

Select MaNV,TenNV MaPhong , From NhanVien Where MaPhong = 'Phòng 1';

---select from LuuNVPhong1 insert into LuuNVPhong1

Select MaNV,TenNV MaPhong , From NhanVien

</div><span class="text_page_counter">Trang 6</span><div class="page_container" data-page="6">

Update diadiem = N'CâGn Thở'

set KinhPhi = 1.1 KinhPhi* ;

-- Tẵng thêm 10% nưã cho dự án 01

Update Duan

set KinhPhi = 1.1 KinhPhi* Where MaDA = 'DA01'

Select from Duan

/*Cập nhật dữ liệu Update nhatkybanhang

set thanhtien = Soluong * dongia from mathang

where nhatkybanhang.mahang = mahang.mahang */ /*-- Xóa dữ liệu cua bang

delete from NhanVien [from danh sách bang] [where điêGu kiện]

--- Xóa những dự án có sổS giờ nho hởn 5

delete from NVDA where Sogio <'46';

-- Xóa nhunbwgx dự án éo có kinh phí

delete from Duan where KinhPhi = '0'

--Xóa những nhân viên phịng tổ chức sự kiện

delete from NhanVien

</div><span class="text_page_counter">Trang 7</span><div class="page_container" data-page="7">

SELECT MaNV,TenNV FROM NhanVien;

-- Hiên thị thổng tin MaNV , HoLot , TenNV đổi tên trường thành tiêSng việt

Select MaNV ASN'Mã Nhân Viên', HoLot ASN'Họ Lót Cua Nhân Viên', TenNV AS

N'Tên Cua Nhân Viên' from NhanVien;

-- Có 3 cách đê đổi tên trường

tiêu_đêG_cột tên_trường= tên_trường tiêu_đêG_cột tên_trường AS tiêu_đêG_cột

-- Hiên THị thổng tin Họ và Tên Nhân Viên

Select [HoLot] +' '+[TenNV]+' ' 'Bê Đê'+ AS N'Họ Và Tên Nhân Viên'from NhanVien;

-- Tính tổng sổS nhân viên

Selectcount(*) asN'Tổng SổS Nhân Viên'from NhanVien;

--Hiên thị 2 nhân viên đâGu tiên

Select Top 2 *from NhanVien;

--Hiên thị 2 nhân viên đâGu tiên và lưu bang có tên Nhân Viên Top 2

Select Top 2 *into NhanVienTop2 from NhanVien; select from NhanVienTop2;

-- Hiên thị 20% nhân viên

select top 30 percent from NhanVien;

-- Hiên thị nhân viên đã nhận dự án --SELECT * FROM NhanVien

Select DISTINCT MaNV from NVDA;

--Hiên thị những nhân viên họ NguyêFn

select from NhanVien where HoLot LIKEN'Lê Vẵn';

--select * from NhanVien where HoLot NOT LIKE N'Lê Vẵn'; ( NêSu như phu định) -- Các điêGu kiện lọc >,<,=

-- And , or ,like , not like, in , not in , between , not between... -- Hiên thị những nhân viên NV01 , NV07 , NV 04

select from NhanVien where MaNV IN 'NV01' 'NV07' 'NV04', , );

-- HOẶC where MaNV = 'NV01' OR MaNV = 'NV07' OR MaNV = 'NV 04'; -- Hiên thị những nhân viên bẵSt đâGu từ chữ L

select from NhanVien where TenNV Like 'L%');

select from NhanVien where TenNV Not Like 'L%'); -- Hiện những tên ko có

</div><span class="text_page_counter">Trang 8</span><div class="page_container" data-page="8">

--- Checkbox là dúng OR , còn Options And -- Hiên thị thổng tin MaNV, TenNV , TenPhong, MaPhong

select MaNV , TenNV A MaPhong TenPhong , . , from NhanVien A Phong B, where A.MaPhong B MaPhong = . and TenNV Not Like 'L%');

-- Hiên thị thổng tin MaNV , TenNV , MaDA, TenDA

select A.MaNV TenNV B MaDA TenDA , , . , from NhanVien A Duan B NVDA C, , Where ( A MaNV C MaNV . = . ) And ( .B MaDA C= .MaDA);

CUSTUMER_ID Char(10) not null,

CUSTUMER_NAME nvarchar(100)not null, -- tên KHÁCH HÀNG

CUSTUMER_ADDRESS nvarchar(100)not null,-- địa chỉ

CITY nvarchar(50)not null,

STATE nvarchar (50 ) not null,-- Tình trạng

POSTAL_CODE nvarchar(50) not null,--Mã bưu điện

constraint pk_CUSTMER_ID primary key (CUSTUMER_ID) );

create table T_ORDER (

ORDER_ID CHAR(10)NOT NULL, ORDER_DATE DATE NOT NULL, CUSTUMER_ID CHAR(10) NOT NULL,

constraint pk_ORDER_ID primary key (ORDER_ID),

constraint fk_CUSTUMER_ID foreign key (CUSTUMER_ID) REFERENCES T_CUSTOMER (CUSTUMER_ID)

create table T_ORDER_LINE (

ORDER_ID CHAR(10)NOT NULL, PRODUCT_ID CHAR(20) NOT NULL, ORDERED_QUANTRI INT,

constraint pk_a primary key (ORDER_ID PRODUCT_ID, ),

constraint fk_ORDER_ID foreign key (ORDER_ID)REFERENCES T_ORDER ORDER_ID), constraint fk_PRODUCT_ID foreign key (PRODUCT_ID)REFERENCES T_PRODUCT (PRODUCT_ID),

constraint ch_ORDERED_QUANTRI check (ORDERED_QUANTRI 0> ) );

</div><span class="text_page_counter">Trang 9</span><div class="page_container" data-page="9">

PRODUCT_ID CHAR(20) NOT NULL,

PRODUCT_DESCRIPTION NVARCHAR(100) NOT NULL,-- MÔ TA SAN PHẨM

PRODUCT_FINISH NVARCHAR(50)NOT NULL, STANDARD_PRICE int,-- giá chuân

PRODUCT_LINE_ID NVARCHAR(50), -- DÒNG SAN PHẨM

constraint pk_PRODUCT_ID primary key (PRODUCT_ID), constraint ch_STANDARD_PRICE check(STANDARD_PRICE > )0 );

drop table T_CUSTOMER drop table T_ORDER_LINE drop table T_ORDER drop table T_PRODUCT

--- BẵSt đâGu nhập dữ liệu

--- Nhập dữ liệu cho bang T_CUSTOMER

select from T_CUSTOMER insert into T_CUSTOMER

values (N'KH01' N'Lê Nhật Đẵng', ,N'5 Ngổ QuyêGn' N'Đà ,

NẵFng' N'Kho, e' N'MBD001001', ); insert into T_CUSTOMER

values (N'KH02' N'Lê Nhật Phong' N'5 Ngổ QuyêG, , n' N'Đà ,

NẵFng' N'Kho, e' N'MBD001002', ); insert into T_CUSTOMER

values (N'KH03' N'Lê Nhật Ngọc' N'5 Ngổ QuyêG, , n' N'Đà ,

NẵFng' N'Kho, e' N'MBD001003', ); insert into T_CUSTOMER

values (N'KH04' N'Lê Nhật HiêS, u' N'5 Ngổ QuyêG, n' N'Đà ,

NẵFng' N', KHỏe' N'MBD001004', ); insert into T_CUSTOMER

values (N'KH05' N'Lê Nhật Khiêm' N'5 Ngổ QuyêG, , n' N'Đà ,

select from T_ORDER_LINE INsert into T_ORDER_LINE

values (N'ORDER1' N'SA, N PHẨM 1',N'1'); INsert into T_ORDER_LINE

values (N'ORDER2' N'SA, N PHẨM 2' N' 2', ); INsert into T_ORDER_LINE

values (N'ORDER3' N'SA, N PHẨM 3',N'3'); INsert into T_ORDER_LINE

N PHẨM 4' N' 4'

</div><span class="text_page_counter">Trang 10</span><div class="page_container" data-page="10">

values (N'ORDER5' N'SA, N PHẨM 5',N'5');

SELECT FROM T_PRODUCT INSERT INTO T_PRODUCT

VALUES (N'SAN PHẨM 1' N'DÙNG ĐỂ VỢT MFI' N'ĐÃ HỒN , ,

THIỆN' N'300000' N'ID001', , ); INSERT INTO T_PRODUCT

VALUES (N'SAN PHẨM 2' N'DÙNG ĐỂ CHIỂSU SÁNG ĐƯỜNG ĐỂM' N'ĐÃ HOÀN , ,

THIỆN' N'400000' N'ID002', , ); INSERT INTO T_PRODUCT

VALUES (N'SAN PHẨM 3' N'DÙNG ĐỂ BẨFY CHUỘT' N'ĐÃ HOÀN , ,

THIỆN' N'500000' N'ID003', , ); INSERT INTO T_PRODUCT

VALUES (N'SAN PHẨM 4' N'DÙNG ĐỂ BẮST CÁ' N'ĐÃ HOÀN THIỆN', , ,N'600000' N'ID004', ); INSERT INTO T_PRODUCT

VALUES (N'SAN PHẨM 5' N'DÙNG ĐỂ CHỤP A, NHI',N'ĐÃ HOÀN

Set PRODUCT_DESCRIPTION = N'AAA'

WHERE PRODUCT_ID ='SAN PHẨM 2';

-- ViêSt 5 câu lệnh xóa dữ liệu

select from T_ORDER delete from T_ORDER where ORDER_ID = N'ORDER1'; delete from T_ORDER where ORDER_ID = N'ORDER'; delete from T_ORDER where ORDER_ID = N'ORDER3';

</div><span class="text_page_counter">Trang 11</span><div class="page_container" data-page="11">

where ORDER_ID = N'ORDER4'; delete from T_ORDER where ORDER_ID = N'ORDER5';

-- Truy xuâSt dữ liệu với câu lệnh SELECT /* Cus pháp chung cua câu lệnh SELECT có dạng

2. cHO BIETES c_id c_name C_ADDRES cu, , a những khách hàng ở thành phổS "Đà nẮFNG"

CUSTUMER_ID CUSTUMER_NAME CUSTUMER_ADDRESS T_CUSTOMER

CITY =(N'ĐÀ NẮFNG');

3. Hiên thị thổng tin khách hàng đổi tên trường Mã só Họ Tên Khách Địa : , , Chỉ

CUSTUMER_ID

select AS N'Mã SổS Nhân Viên' CUSTUMER_NAME , as N'Họ Tên Khách', CUSTUMER_ADDRESS N'Địa Chỉ'FROM T_CUSTOMER;

--4 Hiên thị C_ID cua những khách hàng đã tham gia mua hàng

select count(distinct (CUSTUMER_ID)) ASN'Các Khách Hàng Đã Mua Hàng'from T_ORDER;

--5 Hiên Thị thổng tin 5 khách hàng đàu tiên T_cus_tops

5 T_CUSTOMER

--Hiên thị thổng tin C_ID, C_NAME, P_ID,P_NAME, ORDER_Q,S_PRICE_TOTAL

CUSTUMER_ID CUSTUMER_NAME PRODUCT_ID PRODUCT_FINISH

ORDER_QUANTRI, STANDARD_PRICE TOTAL,

T_CUSTOMER A T_ORDER B T_ORDER_LINE C T_PRODUCT D

</div><span class="text_page_counter">Trang 12</span><div class="page_container" data-page="12">

MAHP nvarchar (10)not null, TENHP nvarchar (50) not null,

MASV nvarchar (10)not null, MAHP nvarchar (10)not null,

constraint pk_MASV primary key (MASV MAHP),,

constraint fk_MAHP foreign key (MAHP ) references HOCPHAN MAHP) );

</div><span class="text_page_counter">Trang 13</span><div class="page_container" data-page="13">

insert into HOCPHAN

values ('IS401', 'HQTCSDL','5'); insert into HOCPHAN

values ('IS301', 'CSDL', '3'); insert into HOCPHAN

values ('IS201', 'TINUD', '10'); select from HOCPHAN

--IF ELSE

--ViêSt lệnh thêm 1 đẵng ký cho SV có mã S1 vào học phâGn 'IS401', nêSu ss đk > ss

--lớp Is401 thì đki thành cổng,ngược lại đki thâSt bại

DECLARE @siso int DECLARE @sisodk int

select @siso = SISO from HOCPHAN where MAHP = 'IS401'

select @sisodk = count(*)from DANGKI where MAHP = 'IS401';

PRINT N'ĐẮNG KÝ KHÔNG THÀNH CÔNG';

--Tạo bẵng sinh viên

CREATE table SINHVIEN (

MASV intnot null,

HOTEN nvarchar (40) not null

constraint pk_MASV1 primary key (MASV) );

--chèn dữ liệu cho sinh viên

select from SINHVIEN insert into SINHVIEN

--ViêSt 1 đoạn vẵn xác định 1 mã SV mới theo qui định như sau:

--Mã SV tẵng dâGn, nêSu cịn chổF trổSng thì chèn vào(vd: 1,2,5,7 thì sv mới sẽ là select from SINHVIEN

--Tạo bang nhân viên

CREATE table NHANVIEN (

</div><span class="text_page_counter">Trang 14</span><div class="page_container" data-page="14">

NGAYSINH datenot null, GIOITINH nvarchar (10)not null, constraint pk_MANV primary key (MANV) );

--select from NHANVIEN insert into NHANVIEN

values('M01' N'NguyêF, n Vẵn A',N'01/12/1972' N'Nam', ); insert into NHANVIEN

values('M02' N'NguyêF, n Thị C',N'04/12/1954' N'Nữ', ); insert into NHANVIEN

values('M03' N'NguyêF, n Thị B',N'11/23/1960' N'Nữ', ); insert into NHANVIEN

values('M04' N'TrâG, n Vẵn D',N'07/12/1974' N'Nam', ); insert into NHANVIEN

values('M05' N'Võ Vẵn A' N'01/12/1940' N'Nam', , , );

--select From NHANVIEN

where datediff(yy NGAYSINH , ,GETDATE())>=

MaPhong char(10) not null, TenPhong nvarchar(50) not null, DiaDiem nvarchar(100) not null, soNV int,

constraint pk_MaPhong primary key (MaPhong), constraint ch_SoNV check (SoNV>= )0

-- Bang nhân viên

Create Table NhanVien MaNV char (10)not null, Holot nvarchar(30)not null, TenNV nvarchar(10)not null, SDT varchar (20),

MaPhong char(10) not null,

constraint pk_MaNV primary key (MaNV),

constraint fk_MaPhong foreign key (MaPhong) REFERENCES Phong MaPhong) );

-- Bang dự án

Create Table Duan( MaDA char (10)not null, TenDA nvarchar(100) not null,

</div><span class="text_page_counter">Trang 15</span><div class="page_container" data-page="15">

constraint ch_kinhphi check (kinhphi>= )0 );

-- Bang nhân viên dự án

Create Table NVDA( MaNV char (10)not null, MaDA char (10)not null, sogio int,

constraint pk_a primary key (MaNV MaDA, ),

constraint fk_MaNV foreign key (MaNV ) REFERENCES Nhanvien MaNV), constraint fk_MaDA foreign key (MaDA ) REFERENCES DuAn MaDA), constraint ch_sogio check (sogio>= )0

-- Thêm trường cho bang phòng

alter table Phong ADD Diachi nvarchar (100); Select From Phong

-- Thêm dữ liệu cho bang Phòng

insert into Phong Select From Phong

-- Thêm dữ liệu cho bang Nhân Viên

Select from NhanVien create database QLNVien use QLNVien;

-- Bang phòng ban

Create Table Phong (

MaPhong char(10) not null, TenPhong nvarchar(50) not null, DiaDiem nvarchar(100) not null, soNV int,

constraint pk_MaPhong primary key (MaPhong), constraint ch_SoNV check (SoNV>= )0

-- Bang nhân viên

Create Table NhanVien MaNV char (10)not null, Holot nvarchar(30)not null, TenNV nvarchar(10)not null, SDT varchar (20),

MaPhong char(10) not null,

constraint pk_MaNV primary key (MaNV),

constraint fk_MaPhong foreign key (MaPhong) REFERENCES Phong MaPhong) );

-- Bang dự án

Create Table Duan( MaDA char (10)not null,

</div><span class="text_page_counter">Trang 16</span><div class="page_container" data-page="16">

constraint ch_kinhphi check (kinhphi>= )0 );

-- Bang nhân viên dự án

Create Table NVDA( MaNV char (10)not null, MaDA char (10)not null, sogio int,

constraint pk_a primary key (MaNV MaDA, ),

constraint fk_MaNV foreign key (MaNV ) REFERENCES Nhanvien MaNV), constraint fk_MaDA foreign key (MaDA ) REFERENCES DuAn MaDA), constraint ch_sogio check (sogio>= )0

-- Thêm trường cho bang phòng

alter table Phong ADD Diachi nvarchar (100); Select From Phong

-- Thêm dữ liệu cho bang Nhân Viên

insert into NhanVien select from NhanVien;

--Thêm dữ liệu cho bang Dự Án

select from Duan insert into Duan

values (N'DA01' N'Xây nhà 1 TâG, ng',N'3000000'); insert into Duan

values (N'DA02' N'Xây nhà TâG, 2 ng',N'3000000'); insert into Duan

values (N'DA03' N'Xây nhà TâG, 3 ng',N'3000000'); insert into Duan

values (N'DA04' N'Xây nhà TâG, 4 ng',N'3000000'); insert into Duan

values (N'DA05' N'Xây nhà TâG, 5 ng',N'3000000');

--Thêm dữ liệu cho bang Nhân Viên Dự Án