Giải bài tập CSDL Bài toán xe khách (Đề CH DHQGHCM)

Giải bài toán CSDL Quản lý xe khách

Bài giải
USE MASTER
GO
DROP DATABASE HT_LAI_XE
GO
CREATE DATABASE HT_LAI_XE
GO
USE HT_LAI_XE
GO
CREATE TABLE LAIXE
(
    MALX NVARCHAR(10) PRIMARY KEY,
    HOTENLX NVARCHAR(500),
    SODIENTHOAILX NVARCHAR(50),
    MATP NVARCHAR(50)
)
GO
CREATE TABLE XE
(
    MAXE NVARCHAR(10) PRIMARY KEY,
    NHANHIEUXE NVARCHAR(500),
    SOCHONGOI INT
)
GO
CREATE TABLE CHUYENDI
(
    MACHUYEN NVARCHAR(10) PRIMARY KEY,
    MATP_XUATPHAT NVARCHAR(50),
    MATP_DEN NVARCHAR(50),
    NGAYDI DATETIME,
    NGAYVE DATETIME
)
GO
CREATE TABLE PHANCONG
(
    MACHUYEN NVARCHAR(10),
    MAXE NVARCHAR(10),
    MALX NVARCHAR(10),
     CONSTRAINT  FP_PHANCONG PRIMARY KEY (MACHUYEN,MAXE,MALX)
)
GO

ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_CHUYENDI FOREIGN KEY (MACHUYEN) REFERENCES CHUYENDI(MACHUYEN)
GO
ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_XE FOREIGN KEY (MAXE) REFERENCES XE(MAXE)
GO
ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_LAIXE FOREIGN KEY (MALX) REFERENCES LAIXE(MALX)
GO
INSERT INTO LAIXE VALUES('LX01',N'Nguyễn Văn A','0907190279','TG')
GO
INSERT INTO LAIXE VALUES('LX02',N'Nguyễn Văn B','0907190179','HCM')
GO
INSERT INTO LAIXE VALUES('LX03',N'Nguyễn Văn C','0907190379','LA')
GO
INSERT INTO LAIXE VALUES('LX04',N'Nguyễn Văn D','0907190479','BT')
GO
INSERT INTO LAIXE VALUES('LX05',N'Nguyễn Văn E','0907190579','ST')
GO
-----
INSERT INTO XE VALUES('XE01',N'Toyota','15')
GO
INSERT INTO XE VALUES('XE02',N'Merce','45')
GO
INSERT INTO XE VALUES('XE03',N'Ford','30')
GO
INSERT INTO XE VALUES('XE04',N'Hyundai','50')
GO
INSERT INTO XE VALUES('XE05',N'New Morning','4')
GO
-----
INSERT INTO CHUYENDI VALUES('CH01','HN','HCM','6/6/2014','6/7/2014')
GO
INSERT INTO CHUYENDI VALUES('CH02','LA','BL','6/7/2014','6/8/2014')
GO
INSERT INTO CHUYENDI VALUES('CH03','HCM','HN','6/6/2014','6/8/2014')
GO
INSERT INTO CHUYENDI VALUES('CH04','HCM','KG','6/7/2014','6/10/2014')
GO
INSERT INTO CHUYENDI VALUES('CH05','ST','AG','6/8/2014','6/15/2014')
GO
-----
INSERT INTO PHANCONG VALUES('CH05','XE01','LX01')
GO
INSERT INTO PHANCONG VALUES('CH01','XE02','LX02')
GO
INSERT INTO PHANCONG VALUES('CH02','XE03','LX03')
GO
INSERT INTO PHANCONG VALUES('CH02','XE04','LX04')
GO
INSERT INTO PHANCONG VALUES('CH04','XE05','LX05')
GO
INSERT INTO PHANCONG VALUES('CH05','XE05','LX05')
GO
INSERT INTO PHANCONG VALUES('CH04','XE04','LX04')
GO
INSERT INTO PHANCONG VALUES('CH05','XE03','LX03')
GO
INSERT INTO PHANCONG VALUES('CH02','XE02','LX02')
GO
INSERT INTO PHANCONG VALUES('CH01','XE01','LX01')
GO
--
INSERT INTO PHANCONG VALUES('CH02','XE01','LX01')
GO
INSERT INTO PHANCONG VALUES('CH04','XE02','LX01')
GO
INSERT INTO PHANCONG VALUES('CH03','XE03','LX01')
GO
INSERT INTO PHANCONG VALUES('CH02','XE04','LX01')
GO
INSERT INTO PHANCONG VALUES('CH01','XE05','LX01')
GO
--1/ Liệt kê  MAXE,NHANHIEUXE, HOTENLX tham gia chuyến từ HCM đến HN?
------------------------------------------------------------------------
SELECT A.MAXE,NHANHIEUXE,HOTENLX FROM PHANCONG A,CHUYENDI B,LAIXE C,XE D
WHERE A.MACHUYEN=B.MACHUYEN
AND A.MALX=C.MALX
AND A.MAXE=D.MAXE
AND MATP_XUATPHAT='HCM' AND MATP_DEN='HN'
---cách 2
SELECT XE.MAXE,XE.NHANHIEUXE, LX.HOTENLX FROM
PHANCONG PC INNER JOIN XE   ON XE.MAXE=PC.MAXE
INNER JOIN LAIXE LX  ON LX.MALX=PC.MALX
INNER JOIN CHUYENDI CD ON PC.MACHUYEN=CD.MACHUYEN
WHERE MATP_XUATPHAT=N'HCM' AND MATP_DEN=N'HN'

--2/ Liệt kê MALX,HOTENLX đã lái tất cả xe của CTY
------------------------------------------------------------------------

SELECT MALX FROM LAIXE E
WHERE NOT EXISTS
(
    SELECT *
    FROM PHANCONG A, XE B
    WHERE A.MAXE=B.MAXE
    AND B.MAXE NOT in
    (
        SELECT maxe
        FROM PHANCONG K
        WHERE K.MALX=E.MALX
    )
)


------------------------------------------------------------------------
--3/ Liệt kê MACHUYEN,MATP_XUATPHAT,MATP_DEN mà có ít nhất 2 xe khác nhau đi cùng chuyến đi đó?
------------------------------------------------------------------------
SELECT A.MACHUYEN FROM CHUYENDI A, PHANCONG B
WHERE A.MACHUYEN= B.MACHUYEN
GROUP BY A.MACHUYEN
HAVING COUNT(MAXE)>=ALL(SELECT 2)