1)

 CREATE DATABASE QLDonvi

ON PRIMARY ( NAME = QLDonvi_Data,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\QLDonvi_Data.MDF',

SIZE = 50MB,

MAXSIZE = 200MB,

FILEGROWTH = 10%)

LOG ON ( NAME = QLDonvi_Log,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\QLDonvi_Log.LDF',

SIZE = 10MB,

FILEGROWTH = 5%)

GO

CREATE TABLE PHAN_XUONG

(

TENPX NVARCHAR(30) PRIMARY KEY,

SOTS INT,

DIEMTB DECIMAL

)

CREATE TABLE DANH_SACH

(

SOBD NCHAR(5) PRIMARY KEY ,

TENNV NVARCHAR(25),

NGAYSINH DATETIME,

GIOITINH NVARCHAR(4),

BAC_THO INT,

TENPX NVARCHAR(30)

FOREIGN KEY (TENPX)

REFERENCES PHAN_XUONG(TENPX),

DIEMLT DECIMAL,

DIEMTH DECIMAL,

TONGDIEM DECIMAL,

KETQUA NVARCHAR(5)

)

 2)

INSERT INTO PHAN_XUONG VALUES('PX1',30,'7.8')

INSERT INTO PHAN_XUONG VALUES('PX2',20,'5.1')

INSERT INTO PHAN_XUONG VALUES('PX3',40,'8.9')

INSERT INTO DANH_SACH VALUES('BD1','PHAM MINH LONG','2/3/1990','NAM',2,'PX2',5.6,6.7,31.3,'DAT')

INSERT INTO DANH_SACH VALUES('BD2','PHAN HUNG','2/7/1990','NAM','4','PX2','5.8','3.7',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD3','LE LINH','6/5/1990','NU','6','PX2','8.6','9.7',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD4','PHAN ANH','2/7/1990','NAM','5','PX2','5.1','6.0',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD5','PHAM PHONG','9/3/1992','NAM','3','PX3','7.6','9',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD6','CHI MAI','8/4/1997','NU','6','PX3','5','6.9',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD7','HOANG ANH','2/9/1991','NAM','2','PX2','7.0','6.7',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD8','PHAM LONG','8/6/1989','NAM','2','PX1','9','6.7',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD9','NGUYEN PHAN','8/3/1970','NAM','5','PX1','9','8',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD10','CHI VAN CANH','2/8/1989','NAM','6','PX2','3','6.7',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD11','PHAM LAM ANH','7/3/1979','NU','2','PX3','8','8',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD12','HUY HAU','2/5/1991','NAM','8','PX1','5.9','6.4',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD13','MAI LAN','2/7/1998','NU','6','PX2','7','2',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD14','LINH CHI','2/3/1989','NU','3','PX2','6','6.7',NULL,NULL)

INSERT INTO DANH_SACH VALUES('BD15','PHAM HOANG LONG','2/3/1990','NAM','2','PX1','6.9','6',NULL,NULL)

 3)

UPDATE DANH_SACH SET TONGDIEM =DIEMLT*2+DIEMTH*3 FROM DANH_SACH

UPDATE DANH_SACH SET KETQUA='DAT' FROM DANH_SACH WHERE DIEMLT>5 AND DIEMTH>7

UPDATE DANH_SACH SET BAC_THO=BAC_THO+1 FROM DANH_SACH WHERE DIEMLT>5 AND DIEMTH>7

 4)

SELECT DISTINCT *

FROM DANH_SACH

WHERE (GIOITINH='NAM' AND (2011-YEAR(NGAYSINH))<52)

OR (GIOITINH='NU' AND (2011-YEAR(NGAYSINH))<49)

DELETE FROM DANH_SACH

WHERE (GIOITINH='NAM' AND (2011-YEAR(NGAYSINH))>52)

OR (GIOITINH='NU' AND (2011-YEAR(NGAYSINH))>49)

SELECT *FROM DANH_SACH

 5)

SELECT SOBD,TENNV,NGAYSINH,GIOITINH,TENPX,DIEMLT,DIEMTH,T ONGDIEM,KETQUA

FROM DANH_SACH

GROUP BY TENPX,SOBD,TENNV,NGAYSINH,GIOITINH,DIEMLT,DIEMTH,T ONGDIEM,KETQUA

ORDER BY TENPX,TONGDIEM DESC

6)

SELECT *

FROM DANH_SACH

WHERE TONGDIEM IN( SELECT MAX(TONGDIEM) FROM DANH_SACH)

7)

SELECT COUNT(SOBD) AS TONGNU ,AVG(DIEMLT) AS TBLT ,AVG(DIEMTH) AS TBTH

FROM DANH_SACH

WHERE GIOITINH='NU'

 8) 

SELECT TENPX,COUNT(SOBD) AS SOTHISINH , AVG(TONGDIEM) AS DIEMTB

FROM DANH_SACH

GROUP BY TENPX

9)

CREATE PROC InTT

@PX NVARCHAR(30)

AS

(SELECT *

FROM Danh_Sach

WHERE TenPX = @PX

AND TongDiem IN

(SELECT MAX(TongDiem)

FROM Danh_Sach

WHERE TenPX = @PX))

EXEC InTT 'PX2'

 10)

CREATE PROC BACTHO

@B INT

AS

SELECT *

FROM DANH_SACH

WHERE BAC_THO=@B

EXEC BACTHO 2