Bài tập sql server cơ bản phần 2

Tiếp tục là một số bài tập SQL cơ bản và nâng cao, theo mình thì nó cũng đã gọi gọn tất cả kiến thức xử lý với phần mềm quản trị cơ sở dữ liệu SQL Server rồi. Nguồn bài tập này mình lấy từ tiến sĩ Phan Anh Phong giảng viên trường đại học Vinh.

Bài tập sql server cơ bản phần 2

Đề bài:

Mô hình diagram

Vì hình hơi khó nhìn nên mình cài đặt luôn cơ sở dữ liệu ở dưới, các bạn có thể coppy trực tiếp vào sql server.

Phần 1: Cài đặt cơ sở dữ liệu

create database baitap;
use baitap;

create table department(dname char(15), dnumber int primary key,
mgrssn char(15), mgrstartssn char(15));

create table deft_locations(dnumber int foreign key references department,
dlocation char(20), primary key(dnumber,dlocation));

create table project(pname char(15),pnumber int primary key,
plocation char(15),dnum int foreign key references department);

create table employe(fname char(15),minit char(5),lname char(15),ssn char(15) primary key,
bdate char(12),addres char(50),sex char(5),salary int, supperssn char(20),
dno int foreign key references department(dnumber));

create table work_on(essn char(15) foreign key references employe(ssn),
pno int foreign key references project(pnumber), hours float(1),primary key(essn,pno));

create table dependen(essn char(15) foreign key references employe(ssn),dependen_name char(10),
sex char(5), bdate char(15),relationship char(20), primary key(essn,dependen_name));

insert into department values
('reserch',5,'333445555','1988-05-22'),
('administration',4,'987654321','1995-01-01'),
('headquarters',1,'888665555','1981-06-19');

insert into deft_locations values
(1,'houston'),
(4,'stafford'),
(5,'bellaire'),
(5,'sugarland'),
(5,'houston');

insert into project values
('productx',1,'bellaire',5),
('producty',2,'sugarland',5),
('productz',3,'houston',5),
('computerization',10,'stafford',4),
('reorganizaton',20,'houston',1),
('newbenefits',30,'stafford',4);

insert into employe values
('john','b','smith','123456789','1965-01-09','731 fondren, houston,tx','m',30000,'333445555',5),
('franklin','t','wong','333445555','1955-12-08','638 voss,houston,tx','m',40000,'888665555',5),
('alicia','j','zelaya','999887777','1968-07-19','3321 castle,spring,tx','f',25000,'987654321',4),
('jennifer','s','wallace','987654321','1941-06-20','291 berry,bellaire,tx','f',43000,'888665555',4),
('ramesh','k','narayan','666884444','1962-09-15','975 fire oak,humble,tx','m',38000,'453453453',5),
('joyce','a','english','453453453','1972-07-31','5631 rice,houston,tx','f',25000,'333445555',5),
('ahmad','v','jabbar','987987987','1969-03-29','980 dallas,houston,tx','m',25000,'987654321',4),
('james','e','borg','888665555','1937-11-10','450 stone, houston,tx','m',55000,'null',1);

insert into work_on values
('123456789',1,32.5),
('123456789',2,7.5),
('666884444',3,40.0),
('453453453',1,20.0),
('453453453',2,20.0),
('333445555',2,10.0),
('333445555',3,10.0),
('333445555',10,10.0),
('333445555',20,10.0),
('999887777',30,30.0),
('999887777',10,10.0),
('987987987',10,35.0),
('987987987',30,5.0),
('987654321',30,20.0),
('987654321',20,15.0),
('888665555',20,null);

select *from work_on;

insert into dependen values
('333445555','alice','f','1986-04-05','daughter'),
('333445555','theodore','m','1983-10-25','son'),
('333445555','joy','f','1958-05-03','spouse'),
('987654321','abner','m','1942-02-28','spouse'),
('123456789','michael','m','1988-01-04','son'),
('123456789','alice','f','1988-12-30','daughter'),
('123456789','elizbeth','f','1967-05-05','spouse');

Phần 2: Câu hỏi truy vấn

2.Cho biết tên các nhân viên trong các phòng ban 4 & 5 làm việc cho dự án ProductX nhiều hơn 10 giờ 1 tuần

3. Liệt kê tất cả nhân viên có người thân có first name trùng với first name của họ.

4.Tìm tên của tất cả nhân viên bị giám sát trực tiếp bởi “Joyce A. English”.

5.Liệt kê thông tin tất cả dự án và tổng số giờ một tuần mà tất cả nhân viên phải làm cho dự án đó.

6. Liệt kê tên dự án và tên của tất cả các nhân viên làm việc cho dự án đó.

7. Liệt kê tên của tất cả nhân viên không tham gia bất cứ dự án nào.

8. Với mỗi phòng ban, hãy liệt kê tên và lương trung bình của tất cả nhân viên làm việc cho phòng ban đó.

9. Cho biết lương trung bình của tất cả nhân viên nữ.

10. Cho biết tên và địa chỉ của tất cả nhân viên làm ít nhất một dự án tại Houston nhưng phong ban của nhân viên

11. Tiệt kê last name của tất cả các người quản lý của các phòng ban nhưng không có thành phần phụ thuộc.

Phần 3: Câu hỏi cập nhật

12. Thay đổi địa chỉ của nhân viên có mã là ‘123456789’ thành ’92 leduan,vinh’.

13. Thay đổi mối quan hệ của nhân viên ‘franklin’ với người phụ thuộc ‘joy’ thành ‘friend’.

14. Tăng lương cho các nhân viên của phòng ban ‘headquarters’ lên 2 lần.

15. Giảm 5% lương cho các nhân viên có tổng số giờ làm việc <40 giờ 1 tuần.

16. Loại dự án có tên ‘product z’ ra khỏi csdl.

17. Xóa tất cả nhân viên có mã giám sát là 999887777.

ĐÁP ÁN

--2-- Cho biết tên các nhân viên trong các phòng ban 4 & 5 làm việc cho dự án ProductX nhiều hơn 10 giờ 1 tuần 
select fname, minit, lname from employe as a
join department as b on(a.dno=b.dnumber and (b.dnumber=4 or b.dnumber=5))
join project as c on (b.dnumber=c.dnum and c.pname='productx')
join work_on as d on (c.pnumber=d.pno and a.ssn=d.essn)
group by fname,minit,lname , d.hours having d.hours>10.0;

--3-- Liệt kê tất cả nhân viên có người thân có first name trùng với first name của họ. 
select distinct * from employe as a join dependen as b
on a.ssn=b.essn and a.fname=b.dependen_name;

--4-- Tìm tên của tất cả nhân viên bị giám sát trực tiếp bởi “Joyce A. English”. 
select a.fname, a.minit, a.lname, a.ssn from employe as a
join employe as b on a.supperssn=b.ssn and(b.fname='joyce' and b.minit='a' and b.lname='english');

--5-- Liệt kê thông tin tất cả dự án và tổng số giờ một tuần mà tất cả nhân viên phải làm cho dự án đó. 
select a.*,SUM(b.hours) as 'work/week'
from project as a join work_on as b
on a.pnumber=b.pno
group by a.pname,a.pnumber,a.plocation,a.dnum;

--6-- Liệt kê tên dự án và tên của tất cả các nhân viên làm việc cho dự án đó.
select distinct c.pname , a.fname,a.minit,a.lname
from employe as a join work_on as b 
on a.ssn=b.essn join project as c
on b.pno=c.pnumber;

--7-- Liệt kê tên của tất cả nhân viên không tham gia bất cứ dự án nào. 
select a.fname, a.minit, a.lname
from employe as a
where a.ssn not in( select essn from work_on);

--8-- Với mỗi phòng ban, hãy liệt kê tên và lương trung bình của tất cả nhân viên làm việc cho phòng ban đó.
select b.dname,AVG(a.salary) as'luongtb'
from department as b join employe as a
on a.dno=b.dnumber
group by b.dname;

--9-- Cho biết lương trung bình của tất cả nhân viên nữ. 
select AVG(salary) as ' luongtb'
from employe
where sex='f';

--10-- Cho biết tên và địa chỉ của tất cả nhân viên làm ít nhất một dự án tại Houston nhưng phong ban của nhân viên đó không đặt tại Houston. 
select distinct fname, minit, lname, addres
from employe as a join department as b 
on a.dno=b.dnumber and a.ssn in(select essn from work_on where pno in (select pnumber from project where plocation='houston'))
join deft_locations as c on b.dnumber=c.dnumber and c.dlocation not in('houston');

--11-- liệt kê last name của tất cả các người quản lý của các phòng ban nhưng không có thành phần phụ thuộc.
select a.lname as 'last name'
from employe as a join department as b
on a.ssn=b.mgrssn
where a.ssn not in(select essn from dependen);

--12-- thay đổi địa chỉ của nhân viên có mã là '123456789' thành '92 leduan,vinh'.
update employe
set addres='92 leduan,vinh'
where ssn='123456789';
select * from employe;

--13-- thay đổi mối quan hệ của nhân viên 'franklin' với người phụ thuộc 'joy' thành 'friend'.
update dependen
set relationship='friend'
where (essn=(select ssn from employe where fname='franklin')) and (dependen_name='joy');
select * from dependen;

--14-- tăng lương cho các nhân viên của phòng ban 'headquarters' lên 2 lần.
update employe
set salary=(select salary from employe as a join department as b on a.dno=b.dnumber where dname='headquarters')*2
where dno=(select dnumber from department where dname='headquarters');

--15-- giảm 5% lương cho các nhân viên có tổng số giờ làm việc <40 giờ 1 tuần.
update employe
set salary=(select salary from employe as a join work_on as b on a.ssn=b.essn group by salary having sum(hours)<40.0)/100*95
where ssn=(select essn from work_on group by essn having SUM(hours)<40.0);

--16-- loại dự án có tên 'product z' ra khỏi csdl.
delete from project where pname='productx';
--không xóa được vì ảnh hướng đến khóa ngoài.

--17-- xóa tất cả nhân viên có mã giám sát là 999887777.
delete from employe where supperssn='999887777';
--không xóa được vì ảnh hướng đến khóa ngoài.

Vậy là mình đã xử lý xong phần bài tập sql server cơ bản này, cơ sở dữ liệu thật sự là một môn học rất quan trọng. Nó liên quan đến gần như tất cả chuyên ngành của chúng ta sau này. Mình chúc các bạn học tập thật tốt, mình xin được kết thúc bài viết tại đây.

Xem những bài tập khác  tại đây