SQL Server資料庫技術期末大作業

1. 題目：設計機票預定資訊系統，并完成以下系統功能基本要求：
   能夠實作多種關聯查詢
   航班基本資訊的錄入：航班的編號、飛機名稱、機艙等級等；
   機票資訊：票價、折扣、當前預售狀態及經受業務員等；
   客戶基本資訊：姓名、聯系方式、證件及號碼、付款情況等；
   按照一定條件查詢、統計符合條件的航班、機票等；
   要求：
   （1）資料庫設計程序中，建立E-R圖，然后轉換為關系模型，報告中要體現出來，
   （2）檔案內容中包括資料庫的應用背景介紹，資料庫設計方案，創建、添加、查詢、修改等陳述句以及陳述句的功能說明，
   （3）SQL陳述句要求規范，標點正確，寫查詢陳述句（應包含單表查詢、連接查詢等）、視圖、觸發器等，
   系統完成任務：

1. 統計航班數量，
2. 查詢旅客“李慧娟”所有的購票資訊，要求輸出航班號、出發城市、目的城市、機票價格和起飛時間，
3. 統計各航班的乘客人數和機票銷售額，按照乘客人數和機票銷售額升序顯示，
4. 查詢由北京出發的所有航班資訊，
5. 查詢飛往上海的航班數、最長航線里程數、最短航線里程數、平均航線里程數以及航線總里程數，
6. 統計每一家航空公司的平均航線里程數，
7. 按航空公司顯示所屬航線的平均里程數大于800公里的分組資訊，
8. 按天統計每一家航空公司所屬航線的乘客總人數和機票銷售總額，按乘客數、機票銷售總額升序顯示，
9. 查詢使用大陸居民身份證購買到北京的機票的乘客，
10. 查詢北京發往上海折扣價格最低的航班，
11. 查詢每一家航空公司的總里程數，
12. 查詢武漢飛往北京的飛機的航班號、機型、里程數、起飛時間，并按照起飛時間排序
13. 查詢飛往上海的所有航班資訊，
14. 查詢價格小于300元的機票，

--1.查詢旅客“李慧娟”所有的購票資訊，要求輸出航班號、出發城市、目的城市、機票價格和起飛時間
SELECT Flight.FlightNumber 航班號, tocity 出發城市, fromcity 目的城市, price 價格,  departureTime 起飛時間 
FROM flight ,ticket,passenger
WHERE Flight.FlightNumber=Ticket.TflightNumber AND Passenger.PTicketNumber=Ticket.TicketNumber
AND PName IN
(select PName from Passenger
where PName='李慧娟')
--2.統計各航班的乘客人數和機票銷售額，按照乘客人數和機票銷售額升序顯示
SELECT Flight.FlightNumber 航班, COUNT(*) 乘客人數, sum(price) 機票銷售額 
FROM flight ,ticket,passenger
WHERE Flight.FlightNumber=Ticket.TflightNumber AND Passenger.PTicketNumber=Ticket.TicketNumber
GROUP BY Flight.FlightNumber 
ORDER BY 乘客人數,機票銷售額

--3.查詢由北京出發的所有航班資訊，
SELECT * FROM flight
WHERE fromcity='北京'
--4.查詢飛往上海的航班數、最長航線里程數、最短航線里程數、平均航線里程數以及航線總里程數，
SELECT COUNT(*) 航班數, mileAge 最長航線里程數, MAX(mileAge) 最短航線里程數, AVG(mileage) 平均航線里程數,SUM(mileage) 航線總里程數
FROM flight
GROUP BY mileAge
order by mileAge
--WHERE tocity='上海'

--5.統計每一家航空公司的平均航線里程數，
SELECT companyname 航空公司, AVG(mileage) 平均航線里程數 FROM flight,Company
WHERE Company.CompanyNumber=Flight.companyID
GROUP BY companyname
ORDER BY AVG(mileage)
--6.按航空公司顯示所屬航線的平均里程數大于800公里的分組資訊，
SELECT companyname 航空公司, Flight.FlightNumber 航班, AVG(mileage) 平均里程 
FROM flight ,company
WHERE company.companynumber=Flight.CompanyID
GROUP BY companyname,Flight.FlightNumber
HAVING AVG(mileage)>800
--7.按天統計每一家航空公司所屬航線的乘客總人數和機票銷售總額，按乘客數、機票銷售總額升序顯示
SELECT companyname 航空公司,Flight.FlightNumber 航線, COUNT(*) 乘客總數, SUM(Price*discount)機票銷售總額
FROM flight,Passenger,Ticket,Company
WHERE Flight.FlightNumber=Ticket.TflightNumber AND Ticket.Ticketnumber=Passenger.PTicketNumber AND Company.CompanyNumber=Flight.CompanyID
--AND companyname='中國南方航空'
GROUP BY companyname,Flight.FlightNumber
ORDER BY COUNT(*),SUM(price)

--8.查詢使用大陸居民身份證購買到北京的機票的乘客
Select Passenger.*,Ticket.*
from Passenger,Ticket,Flight
WHERE Passenger.IdentityStyle='大陸居民身份證' and Flight.tocity='北京'

--9.查詢北京發往上海折扣價格最低的航班
Select Flight.*,Ticket.discount
from Ticket,Flight
WHERE Flight.tocity='上海' and Flight.Fromcity='北京'
order by Ticket.discount*Price

--10.查詢每一家航空公司的總里程數
SELECT  COUNT(*) 乘客總數, SUM(price) 機票銷售總額
FROM flight,passenger,Ticket,Company;
SELECT companyname 航空公司名稱,mileAge 里程數 FROM flight ,Company
WHERE Company.CompanyNumber=Flight.CompanyID
ORDER BY 里程數

/*11.查詢武漢飛往北京的飛機的航班號、機型、里程數、起飛時間，并按照起飛時間排序*/
SELECT FlightNumber 航班號, FlightName 機型, fromCity 始發地, tocity 目的地, mileage 里程數,departureTime 起飛時間 
FROM Flight
WHERE fromcity='武漢' AND tocity='北京'
ORDER BY 起飛時間
/*12.查詢飛往上海的所有航班資訊，*/
SELECT * FROM flight 
WHERE tocity='上海'
/*13.查詢價格小于300元的機票*/
SELECT Price 價格,discount 折扣 ,condition 經受狀態,worker 受理作業人員
FROM Ticket
WHERE Price<3000