--CREATE DATABASE FOR CONGTYVANCHUYENCREATE DATABASE CTYVANCHUYEN ON PRIMARY(NAME ='CTYVANCHUYEN',FILENAME='C:\DATATHUCHANH\CTYVANCHUYEN.MDF',SIZE = 3072KB ,MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON ( NAME = 'CTYVANCHUYEN_DBMS_log', FILENAME = 'C:\DATATHUCHANH\CTYVANCHUYEN_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048KB , FILEGROWTH = 10%)GOUSE [CTYVANCHUYEN]GOCREATE TABLE [dbo].[TINHTHANH]([MATT] [varchar](3) NOT NULL,[TENTT] [varchar](50) NOT NULL,PRIMARY KEY(MATT))GOCREATE TABLE [dbo].[NHACUNGCAP]([MANCC] [varchar](15) NOT NULL,[TENNCC] [nvarchar](50) NOT NULL,[MATINH] [varchar](3) NULL,PRIMARY KEY(MANCC))GOCREATE TABLE [dbo].[DANHMUCHANGHOA]([MAHANG] [varchar](15) NOT NULL,[TENHANG][nvarchar](50) NOT NULL,[TENTT] [nvarchar](50) NULL,[TRONGLUONG][float] NULLPRIMARY KEY(MAHANG))GOCREATE TABLE [dbo].[CUAHANG]([MACH] [varchar](15) NOT NULL,[TENCUAHANG] [nvarchar](50) NOT NULL,[MATINH] [varchar](3) NULL,PRIMARY KEY(MACH))GOCREATE TABLE [dbo].[CUNGUNG]([MAHANG] [varchar](15) NULL,[MANCC] [varchar](15) NULL,[MACH] [varchar](15) NULL,[SOLUONG][float] NULL)GOALTER TABLE [CUNGUNG] WITH CHECK ADD FOREIGN KEY([MAHANG])REFERENCES [DANHMUCHANGHOA] ([MAHANG])ALTER TABLE [CUNGUNG] WITH CHECK ADD FOREIGN KEY([MANCC]) REFERENCES [NHACUNGCAP] ([MANCC])ALTER TABLE [CUNGUNG] WITH CHECK ADD FOREIGN KEY([MACH])REFERENCES [CUAHANG] ([MACH])select A.TENTT from dbo.TINHTHANH A LEFT join dbo.NHACUNGCAP B on A.MATT=B.MATINHselect * from dbo.TINHTHANH A RIGHT join dbo.NHACUNGCAP B on A.MATT=B.MATINHselect * from dbo.TINHTHANH A FULL join dbo.NHACUNGCAP B on A.MATT=B.MATINHselect MATINH, MANCC,TENNCC from dbo.NHACUNGCAP ORDER BY MANCC DESCSELECT * FROM dbo.TINHTHANHupdate dbo.NHACUNGCAPset MATINH=nullwhere MATINH='HAN'--CAU 1--DROP DATABASE [CompanyDBMS]CREATE DATABASE Dethi ON PRIMARY ( NAME = 'Dethi', FILENAME = 'C:\DATA\Dethi.mdf' , SIZE = 3072KB ,MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = 'Dethi_log', FILENAME = 'C:\DATA\Dethi_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048KB , FILEGROWTH = 10%)GOUSE dethiCREATE table TINHTHANH(MATT VARCHAR (3) NOT NULL,TENTT NVARCHAR (50) NULL,PRIMARY KEY (MATT))CREATE TABLE NHACUNGCAP(MANCC VARCHAR (15) NOT NULL,TENNCC NVARCHAR (50) NULL,MATINH VARCHAR (3) NULL,PRIMARY KEY (MANCC),FOREIGN KEY (MATINH) REFERENCES TINHTHANH(MATT))CREATE TABLE DANHMUCHANGHOA(MAHANG VARCHAR (15) NOT NULL,TENHANG NVARCHAR (50) NOT NULL,QUYCACH NVARCHAR (10) NOT NULL,TRONGLUONG FLOAT NOT NULL,PRIMARY KEY (MAHANG))CREATE TABLE CUAHANG(MACH VARCHAR (15) NOT NULL,TENCUAHANG NVARCHAR (50) NOT NULL,MATINH VARCHAR (3) NOT NULL,PRIMARY KEY (MACH))CREATE TABLE CUNGUNG(MAHANG VARCHAR (15) NOT NULL,MANCC VARCHAR (15) NOT NULL,MACH VARCHAR (15) NOT NULL,SOLUONG FLOAT NOT NULL,PRIMARY KEY (MANCC,MAHANG,MACH),FOREIGN KEY (MAHANG) REFERENCES DANHMUCHANGHOA(MAHANG),FOREIGN KEY (MANCC) REFERENCES NHACUNGCAP (MANCC),FOREIGN KEY (MACH) REFERENCES CUAHANG (MACH))--CAU 2: INSERT into TINHTHANH(MATT,TENTT) VALUES ('HAN','Ha Noi') INSERT into TINHTHANH(MATT,TENTT) VALUES ('HCM','Ho Chi Minh') insert into NHACUNGCAP (MANCC, TENNCC, MATINH) VALUES ('SV001','Cty TNHH Thang Loi', 'HAN') insert into NHACUNGCAP (MANCC, TENNCC, MATINH) VALUES ('SV002','Cty TNHH Thanh Cong', 'HCM') insert into NHACUNGCAP (MANCC, TENNCC, MATINH) VALUES ('SV003','Cty TNHH Truong Son', 'HCM') insert into DANHMUCHANGHOA (MAHANG, TENHANG, QUYCACH, TRONGLUONG) VALUES ('PTD001','PS2','Hop','120')insert into DANHMUCHANGHOA (MAHANG, TENHANG, QUYCACH, TRONGLUONG) VALUES ('PTD002','Dacco','Hop','130')insert into DANHMUCHANGHOA (MAHANG, TENHANG, QUYCACH, TRONGLUONG) VALUES ('PTD003','Close up','Hop','140')insert into DANHMUCHANGHOA (MAHANG, TENHANG, QUYCACH, TRONGLUONG) VALUES ('PTD004','Colgate','Hop','160')insert into CUAHANG(MACH, TENCUAHANG ,MATINH) VALUES ('CH001','FIDIMART', 'HCM')insert into CUAHANG(MACH, TENCUAHANG ,MATINH) VALUES ('CH002','COOPMART', 'HAN')insert into CUAHANG(MACH, TENCUAHANG ,MATINH) VALUES ('CH003','MINIMART', 'HAN')INSERT INTO CUNGUNG (MANCC, MAHANG, MACH, SOLUONG) VALUES ('SV001','PTD001','CH002','100')INSERT INTO CUNGUNG (MANCC, MAHANG, MACH, SOLUONG) VALUES ('SV002','PTD002','CH001','200')INSERT INTO CUNGUNG (MANCC, MAHANG, MACH, SOLUONG) VALUES ('SV003','PTD003','CH001','150')--CAU 3SELECT TENNCC FROM NHACUNGCAP WHERE MANCC IN (SELECT MANCC FROM CUAHANG D INNER JOIN CUNGUNG E ON D.MACH = E.MACHWHERE TENCUAHANG = 'FIDIMART')--CAU 4SELECT TENHANG FROM DANHMUCHANGHOA WHERE MAHANG IN(SELECT MAHANG FROM CUAHANG D INNER JOIN CUNGUNG E ON D.MACH = E.MACHWHERE TENCUAHANG = 'FIDIMART')--CAU 5SELECT MAX(TRONGLUONG) AS 'Trong luong lon nhat'from DANHMUCHANGHOASELECT MIN(trongluong) as 'trong luong nho nhat'from DANHMUCHANGHOASELECT AVG(trongluong) as 'Trong luong trung binh'from DANHMUCHANGHOA--CAU 6CREATE view danhsachas(SELECT TENCUAHANG FROM CUAHANG WHERE MACH NOT IN(select MACH from cungungwhere MANCC = 'SV001'))select * from danhsach--CAU 7CREATE PROC CAU7AS (SELECT TENNCC, SUM (SOLUONG) AS TONGLUONGHANG FROM NHACUNGCAP b INNER JOIN CUNGUNG e ON B.MANCC=E.MANCCGROUP BY TENNCC)EXEC CAU7