Scribd
Upload
4 views4 pages

Flight Booking and Airport Data Analysis

The document contains SQL commands for managing a flight booking database, including altering tables, creating views, and executing various queries to retrieve flight and booking information. Key operations include adding unique constraints, counting flights from airports, and retrieving passenger booking details. The document also includes logic for determining available seats and identifying frequent passengers based on booking history.

Uploaded by

hoangducanh.1865.3
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views4 pages

Flight Booking and Airport Data Analysis

The document contains SQL commands for managing a flight booking database, including altering tables, creating views, and executing various queries to retrieve flight and booking information. Key operations include adding unique constraints, counting flights from airports, and retrieving passenger booking details. The document also includes logic for determining available seats and identifying frequent passengers based on booking history.

Uploaded by

hoangducanh.1865.3
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

USE QLChuyenBay_20230015;

-- 1
ALTER TABLE DATVE
ADD CONSTRAINT UNIQUE_DATVE UNIQUE (MACB, MAGHE);

-- 2
SELECT [Link] AS MASBDI, [Link] AS [Tên sân bay đi], COUNT([Link]) AS
[Số chuyến bay]
FROM SANBAY SB
LEFT JOIN CHUYENBAY CB ON [Link] = [Link]
GROUP BY [Link], [Link]

SELECT [Link] AS MASBDI, [Link] AS [Tên sân bay đi], COUNT([Link]) AS


[Số chuyến bay]
FROM SANBAY SB
LEFT JOIN CHUYENBAY CB ON [Link] = [Link]
GROUP BY [Link], [Link]

-- 3
SELECT [Link], [Link] AS [Tên sân bay đi], [Link] [Tên sân bay đến]
FROM SANBAY SBDI
JOIN CHUYENBAY CB ON [Link] = [Link]
JOIN SANBAY SBDEN ON [Link] = [Link]
WHERE [Link] = N'Nội Bài'
AND CAST([Link] AS DATE) = CAST(GETDATE() AS DATE);

SELECT [Link], [Link] AS [Tên sân bay đi], [Link] AS [Tên sân bay
đến], [Link]
FROM CHUYENBAY CB
JOIN SANBAY SBDI ON [Link] = [Link]
JOIN SANBAY SBDEN ON [Link] = [Link]
WHERE [Link] = N'Nội Bài'
AND CAST(GETDATE() AS DATE) = CAST([Link] AS DATE)

-- 4
SELECT [Link], [Link], [Link], [Link] [Số ghế],
[Link]
FROM CHUYENBAY CB
JOIN MAYBAY MB ON [Link] = [Link]
WHERE [Link] = (
SELECT COUNT([Link])
FROM DATVE DV
WHERE [Link] = [Link]
GROUP BY [Link] -- KHAC MA CB VAN CO THE CUNG MA MB
)

SELECT [Link], [Link], [Link], BANGTAM.


[Số ghế], [Link]
FROM (
SELECT [Link], [Link], [Link], COUNT([Link]) AS [Số
ghế], [Link], [Link]
FROM CHUYENBAY CB
JOIN DATVE DV ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link],
[Link]
) AS BANGTAM
JOIN MAYBAY MB ON [Link] = [Link]
WHERE BANGTAM.[Số ghế] = [Link]
-- 5
SELECT TOP 3 [Link], [Link], COUNT([Link]) AS [Số vé]
FROM DATVE DV
JOIN HANHKHACH HK ON [Link] = [Link]
JOIN CHUYENBAY CB ON [Link] = [Link]
WHERE YEAR([Link]) = 2022
GROUP BY [Link], [Link]
ORDER BY [Số vé] DESC

SELECT TOP 3 [Link], [Link], COUNT([Link]) AS [Số vé]


FROM HANHKHACH HK
JOIN DATVE DV ON [Link] = [Link]
JOIN CHUYENBAY CB ON [Link] = [Link]
WHERE YEAR([Link]) = 2022
GROUP BY [Link], [Link]
ORDER BY [Số vé] DESC

-- 6
SELECT TOP 1 [Link], ([Link] - [Link]) AS [Số ghế trống],
[Link]
FROM CHUYENBAY CB
JOIN MAYBAY MB ON [Link] = [Link]
JOIN (
SELECT [Link], COUNT([Link]) AS SOHK
FROM CHUYENBAY CB
JOIN DATVE DV ON [Link] = [Link]
GROUP BY [Link]
) AS BANGTAM ON [Link] = [Link]
WHERE CAST([Link] AS DATE) = CAST(GETDATE() AS DATE)
ORDER BY [Số ghế trống] DESC

-- 7
SELECT [Link]
FROM DATVE DV
JOIN HANHKHACH HK ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT YEAR([Link])) = 1;

-- 8
CREATE VIEW ThongTinDatVe AS
SELECT [Link], [Link], [Link], [Link], [Link]
FROM HANHKHACH h
JOIN DATVE d ON [Link] = [Link]
JOIN CHUYENBAY c ON [Link] = [Link]
WHERE [Link] > GETDATE();

CREATE VIEW ThongTinDatVe AS


SELECT [Link], [Link], [Link], [Link], [Link]
FROM HANHKHACH HK
JOIN DATVE DV ON [Link] = [Link]
JOIN CHUYENBAY CB ON [Link] = [Link]
WHERE [Link] > GETDATE();

You might also like