请选择 进入手机版 | 继续访问电脑版

SQL Server数据库技术期末大作业

[复制链接]
科达工艺 发表于 2021-1-1 09:59:42 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
题目



  • 设计机票预定信息系统,并完成以下系统功能根本要求: 可以或许实现多种关联查询 航班根本信息的录入:航班的编号、飞机名称、机舱品级等;
  • 机票信息:票价、折扣、当前预售状态及经受业务员等; 客户根本信息:姓名、联系方式、证件及号码、付款情况等;
  • 按照一定条件查询、统计符合条件的航班、机票等;
要求


  • 数据库设计过程中,创建E-R图,然后转换为关系模型,陈诉中要体现出来。
  • 文档内容中包罗数据库的应用配景先容,数据库设计方案,创建、添加、查询、修改等语句以及语句的功能说明。
  • SQL语句要求规范,标点正确,写查询语句(应包含单表查询、连接查询等)、视图、触发器等。
任务


  • 统计航班数量。
  • 查询游客“李慧娟”所有的购票信息,要求输出航班号、出发都会、目标都会、机票代价和起飞时间。
  • 统计各航班的乘客人数和机票销售额,按照乘客人数和机票销售额升序显示。
  • 查询由北京出发的所有航班信息。
  • 查询飞往上海的航班数、最长航线里程数、最短航线里程数、平均航线里程数以及航线总里程数。
  • 统计每一家航空公司的平均航线里程数。
  • 按航空公司显示所属航线的平均里程数大于800公里的分组信息。
  • 按天统计每一家航空公司所属航线的乘客总人数和机票销售总额,按乘客数、机票销售总额升序显示。
  • 查询使用大陆住民身份证购买到北京的机票的乘客。
  • 查询北京发往上海折扣代价最低的航班。
  • 查询每一家航空公司的总里程数。
  • 查询武汉飞往北京的飞机的航班号、机型、里程数、起飞时间,并按照起飞时间排序
  • 查询飞往上海的所有航班信息。
  • 查询代价小于300元的机票。
代码

创建数据库架构

  1. USE [FinalWork]GO/****** Object:  Table [dbo].[Flight]    Script Date: 2021/1/1 0:52:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Flight](        [FlightNumber] [varchar](10) NOT NULL,        [companyID] [nchar](10) NOT NULL,        [FlightName] [varchar](10) NULL,        [fromCity] [nchar](10) NULL,        [toCity] [nchar](10) NULL,        [mileAge] [int] NULL,        [departureTime] [time](7) NULL, CONSTRAINT [PK__Flight__2EAE6F51B04BC0EC] PRIMARY KEY CLUSTERED (        [FlightNumber] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  View [dbo].[FlightFromBeijing]    Script Date: 2021/1/1 0:52:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--3.查询由北京出发的所有航班信息。CREATE VIEW [dbo].[FlightFromBeijing](FlightName,fromCity)ASSELECT FlightName,fromCityFROM flightWHERE fromcity='北京'GO/****** Object:  Table [dbo].[Company]    Script Date: 2021/1/1 0:52:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Company](        [CompanyNumber] [nchar](10) NOT NULL,        [CompanyName] [nchar](10) NULL,        [CompanyAddress] [nchar](50) NULL,        [CompanyHotline] [nchar](10) NULL, CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED (        [CompanyNumber] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  Table [dbo].[Passenger]    Script Date: 2021/1/1 0:52:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Passenger](        [PassengerIdentity] [varchar](50) NOT NULL,        [PName] [varchar](50) NOT NULL,        [gender] [varchar](50) NOT NULL,        [birthday] [varchar](50) NOT NULL,        [PTele] [varchar](50) NOT NULL,        [IdentityStyle] [varchar](50) NOT NULL,        [PaymentState] [varchar](50) NOT NULL,        [PTicketNumber] [varchar](20) NOT NULL, CONSTRAINT [PK_Passenger] PRIMARY KEY CLUSTERED (        [PassengerIdentity] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object:  Table [dbo].[Ticket]    Script Date: 2021/1/1 0:52:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Ticket](        [TicketNumber] [varchar](20) NOT NULL,        [Price] [int] NULL,        [discount] [float] NULL,        [Condition] [varchar](10) NULL,        [worker] [varchar](10) NULL,        [TflightNumber] [varchar](10) NULL, CONSTRAINT [PK_Ticket] PRIMARY KEY CLUSTERED (        [TicketNumber] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[Passenger]  WITH CHECK ADD  CONSTRAINT [FK_Passenger_Ticket] FOREIGN KEY([PTicketNumber])REFERENCES [dbo].[Ticket] ([TicketNumber])GOALTER TABLE [dbo].[Passenger] CHECK CONSTRAINT [FK_Passenger_Ticket]GOALTER TABLE [dbo].[Ticket]  WITH CHECK ADD  CONSTRAINT [FK_Ticket_Flight] FOREIGN KEY([TflightNumber])REFERENCES [dbo].[Flight] ([FlightNumber])GOALTER TABLE [dbo].[Ticket] CHECK CONSTRAINT [FK_Ticket_Flight]GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预售状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Ticket', @level2type=N'COLUMN',@level2name=N'Condition'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'经受业务员' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Ticket', @level2type=N'COLUMN',@level2name=N'worker'GO
复制代码
导入数据

  1. USE [FinalWork]GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'3U8948', N'8         ', N'空客320', N'济南        ', N'昆明        ', 2080, CAST(N'21:30:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'3U8962', N'8         ', N'空客320', N'上海        ', N'成都        ', 2800, CAST(N'11:35:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'CA1947', N'3         ', N'空客340', N'上海        ', N'成都        ', 2800, CAST(N'08:03:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'CZ3117', N'1         ', N'波音738', N'武汉        ', N'北京        ', 1100, CAST(N'08:06:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'CZ3907', N'1         ', N'空客333', N'北京        ', N'上海        ', 1130, CAST(N'18:05:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'CZ6356', N'1         ', N'波音738', N'海口        ', N'郑州        ', 1873, CAST(N'11:51:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'CZ6553', N'1         ', N'空客320', N'长春        ', N'上海        ', 1698, CAST(N'16:30:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'HO1252', N'7         ', N'空客320', N'北京        ', N'上海        ', 1130, CAST(N'06:40:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'HO1284', N'7         ', N'空客320', N'长春        ', N'上海        ', 1698, CAST(N'14:45:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'HU7188', N'4         ', N'波音738', N'武汉        ', N'北京        ', 1100, CAST(N'15:55:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'HU7291', N'4         ', N'波音738', N'海口        ', N'郑州        ', 1873, CAST(N'13:05:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'HU7309', N'4         ', N'波音738', N'海口        ', N'郑州        ', 1873, CAST(N'08:31:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MF8069', N'6         ', N'波音738', N'南宁        ', N'沈阳        ', 2780, CAST(N'08:03:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MF8073', N'6         ', N'波音738', N'厦门        ', N'沈阳        ', 2242, CAST(N'07:41:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MU2451', N'2         ', N'波音738', N'武汉        ', N'北京        ', 1100, CAST(N'09:04:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MU2453', N'2         ', N'波音738', N'武汉        ', N'北京        ', 1100, CAST(N'15:00:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MU2540', N'2         ', N'波音738', N'上海        ', N'成都        ', 2800, CAST(N'19:30:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MU2885', N'2         ', N'空客320', N'南京        ', N'西安        ', 1104, CAST(N'19:50:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MU294', N'2         ', N'空客320', N'上海        ', N'成都        ', 2800, CAST(N'11:15:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MU5102', N'2         ', N'空客333', N'北京        ', N'上海        ', 1130, CAST(N'08:39:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'MU5680', N'2         ', N'空客332', N'长春        ', N'上海        ', 1698, CAST(N'13:50:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'SC1191', N'9         ', N'波音738', N'济南        ', N'昆明        ', 2080, CAST(N'20:00:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'SC4873', N'9         ', N'波音738', N'济南        ', N'昆明        ', 2080, CAST(N'08:45:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'ZH9438', N'5         ', N'空客320', N'海口        ', N'郑州        ', 1873, CAST(N'17:00:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'ZH9516', N'5         ', N'空客320', N'厦门        ', N'沈阳        ', 2242, CAST(N'16:20:00' AS Time))GOINSERT [dbo].[Flight] ([FlightNumber], [companyID], [FlightName], [fromCity], [toCity], [mileAge], [departureTime]) VALUES (N'ZH9602', N'5         ', N'空客320', N'厦门        ', N'沈阳        ', 2242, CAST(N'14:55:00' AS Time))GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'1         ', 3245, 0.4, N'1', N'1', N'3U8948')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'10        ', 312, 0.3, N'0', N'4', N'MF8073')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'11        ', 453, 0.2, N'0', N'4', N'MU2885')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'12        ', 312, 0.6, N'0', N'4', N'MU2885')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'13        ', 546, 0.3, N'0', N'4', N'MU294')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'2         ', 3252, 0.8, N'1', N'1', N'3U8948')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'3         ', 6588, 0.5, N'1', N'1', N'HO1284')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'4         ', 2356, 0.4, N'1', N'1', N'HO1284')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'5         ', 6484, 0.6, N'1', N'3', N'CZ6356')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'6         ', 25476, 0.7, N'1', N'3', N'HU7188')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'7         ', 2466, 0.4, N'0', N'3', N'HU7188')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'8         ', 352, 0.4, N'0', N'3', N'HU7188')GOINSERT [dbo].[Ticket] ([TicketNumber], [Price], [discount], [Condition], [worker], [TflightNumber]) VALUES (N'9         ', 535, 0.2, N'0', N'3', N'MF8073')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'100506198304161675', N'苏地方', N'男', N'1983-04-16', N'13867886598', N'护照', N'1', N'4')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'100712197303186681', N'额外可', N'女', N'1973-03-18', N'15367211029', N'护照', N'1', N'3')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'101009197404104775', N'委任为', N'男', N'1974-04-10', N'13578983521', N'护照', N'1', N'2')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'280103198309125341', N'刘亚蒙', N'男', N'1976-10-20', N'13565888845', N'大陆住民身份证', N'1', N'2 ')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'280103199806195832', N'郝琼琼', N'女', N'1987-03-12', N'15334564321', N'大陆住民身份证', N'1', N'1')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'280104198703125883', N'雷亚波', N'男', N'1988-06-19', N'13686035678', N'大陆住民身份证', N'1', N'2')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'280105197610200914', N'魏国兰', N'女', N'1983-09-12', N'13827653456', N'大陆住民身份证', N'1', N'5')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'640104198703125881', N'他亚波', N'男', N'1988-06-19', N'13686035678', N'港澳通行证', N'1', N'1')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'640105197610200916', N'贴国兰', N'女', N'1983-09-12', N'13827653456', N'港澳通行证', N'1', N'2')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'770103198309125344', N'万亚蒙', N'男', N'1976-10-20', N'13565888845', N'大陆住民身份证', N'1', N'1')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'770103199806195830', N'任琼琼', N'女', N'1987-03-12', N'15334564321', N'港澳通行证', N'1', N'2')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'770106199208113735', N'李慧娟', N'女', N'1992-08-11', N'13967341256', N'大陆住民身份证', N'1', N'6')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'770107198307762086', N'吕兰梦', N'女', N'1983-04-26', N'13878292910', N'大陆住民身份证', N'1', N'1')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'770211197905122417', N'郝嘉志', N'男', N'1979-05-12', N'15945673771', N'大陆住民身份证', N'1', N'2')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'770506198304161678', N'苏彦博', N'男', N'1983-04-16', N'13867886598', N'大陆住民身份证', N'1', N'2')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'770712197303186689', N'严雅可', N'女', N'1973-03-18', N'15367217729', N'大陆住民身份证', N'1', N'5')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'771009197404104770', N'傅明远', N'男', N'1974-04-10', N'13578983521', N'大陆住民身份证', N'1', N'3')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'800211197905122415', N'郝大纲', N'男', N'1979-05-12', N'15945673801', N'护照', N'1', N'5')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'820106199208113738', N'发慧娟', N'女', N'1992-08-11', N'13967341256', N'港澳通行证', N'1', N'3')GOINSERT [dbo].[Passenger] ([PassengerIdentity], [PName], [gender], [birthday], [PTele], [IdentityStyle], [PaymentState], [PTicketNumber]) VALUES (N'820107198308062089', N'才兰梦', N'女', N'1983-04-26', N'13878292910', N'港澳通行证', N'1', N'5')GOINSERT [dbo].[Company] ([CompanyNumber], [CompanyName], [CompanyAddress], [CompanyHotline]) VALUES (N'1         ', N'中国南方航空    ', N'广州市机场路278号95539                                   ', N'95539     ')GOINSERT [dbo].[Company] ([CompanyNumber], [CompanyName], [CompanyAddress], [CompanyHotline]) VALUES (N'2         ', N'中国东方航空    ', N'上海市虹桥路2550号                                       ', N'95530     ')GOINSERT [dbo].[Company] ([CompanyNumber], [CompanyName], [CompanyAddress], [CompanyHotline]) VALUES (N'3         ', N'中国国际航空    ', N'北京市顺义区天柱路30号                                      ', N'95583     ')GOINSERT [dbo].[Company] ([CompanyNumber], [CompanyName], [CompanyAddress], [CompanyHotline]) VALUES (N'4         ', N'海南航空      ', N'海口市国兴大道7号                                         ', N'950718    ')GOINSERT [dbo].[Company] ([CompanyNumber], [CompanyName], [CompanyAddress], [CompanyHotline]) VALUES (N'5         ', N'深圳航空      ', N'深圳宝安国际机场航站四路2033号                                 ', N'95080     ')GOINSERT [dbo].[Company] ([CompanyNumber], [CompanyName], [CompanyAddress], [CompanyHotline]) VALUES (N'6         ', N'厦门航空      ', N'厦门市埭辽路22号                                         ', N'95557     ')GOINSERT [dbo].[Company] ([CompanyNumber], [CompanyName], [CompanyAddress], [CompanyHotline]) VALUES (N'7         ', N'祥瑞航空      ', N'上海市虹翔三路80号                                        ', N'95520     ')GO
复制代码
数据查询语句

[code]--1.查询游客“李慧娟”所有的购票信息,要求输出航班号、出发都会、目标都会、机票代价和起飞时间SELECT Flight.FlightNumber 航班号, tocity 出发都会, fromcity 目标都会, price 代价,  departureTime 起飞时间 FROM flight ,ticket,passengerWHERE Flight.FlightNumber=Ticket.TflightNumber AND Passenger.PTicketNumber=Ticket.TicketNumberAND PName IN(select PName from Passengerwhere PName='李慧娟')--2.统计各航班的乘客人数和机票销售额,按照乘客人数和机票销售额升序显示SELECT Flight.FlightNumber 航班, COUNT(*) 乘客人数, sum(price) 机票销售额 FROM flight ,ticket,passengerWHERE Flight.FlightNumber=Ticket.TflightNumber AND Passenger.PTicketNumber=Ticket.TicketNumberGROUP BY Flight.FlightNumber ORDER BY 乘客人数,机票销售额--3.查询由北京出发的所有航班信息。SELECT * FROM flightWHERE fromcity='北京'--4.查询飞往上海的航班数、最长航线里程数、最短航线里程数、平均航线里程数以及航线总里程数。SELECT COUNT(*) 航班数, mileAge 最长航线里程数, MAX(mileAge) 最短航线里程数, AVG(mileage) 平均航线里程数,SUM(mileage) 航线总里程数FROM flightGROUP BY mileAgeorder by mileAge--WHERE tocity='上海'--5.统计每一家航空公司的平均航线里程数。SELECT companyname 航空公司, AVG(mileage) 平均航线里程数 FROM flight,CompanyWHERE Company.CompanyNumber=Flight.companyIDGROUP BY companynameORDER BY AVG(mileage)--6.按航空公司显示所属航线的平均里程数大于800公里的分组信息。SELECT companyname 航空公司, Flight.FlightNumber 航班, AVG(mileage) 平均里程 FROM flight ,companyWHERE company.companynumber=Flight.CompanyIDGROUP BY companyname,Flight.FlightNumberHAVING AVG(mileage)>800--7.按天统计每一家航空公司所属航线的乘客总人数和机票销售总额,按乘客数、机票销售总额升序显示SELECT companyname 航空公司,Flight.FlightNumber 航线, COUNT(*) 乘客总数, SUM(Price*discount)机票销售总额FROM flight,Passenger,Ticket,CompanyWHERE Flight.FlightNumber=Ticket.TflightNumber AND Ticket.Ticketnumber=Passenger.PTicketNumber AND Company.CompanyNumber=Flight.CompanyID--AND companyname='中国南方航空'GROUP BY companyname,Flight.FlightNumberORDER BY COUNT(*),SUM(price)--8.查询使用大陆住民身份证购买到北京的机票的乘客Select Passenger.*,Ticket.*from Passenger,Ticket,FlightWHERE Passenger.IdentityStyle='大陆住民身份证' and Flight.tocity='北京'--9.查询北京发往上海折扣代价最低的航班Select Flight.*,Ticket.discountfrom Ticket,FlightWHERE 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 ,CompanyWHERE Company.CompanyNumber=Flight.CompanyIDORDER BY 里程数/*11.查询武汉飞往北京的飞机的航班号、机型、里程数、起飞时间,并按照起飞时间排序*/SELECT FlightNumber 航班号, FlightName 机型, fromCity 始发地, tocity 目标地, mileage 里程数,departureTime 起飞时间 FROM FlightWHERE fromcity='武汉' AND tocity='北京'ORDER BY 起飞时间/*12.查询飞往上海的所有航班信息。*/SELECT * FROM flight WHERE tocity='上海'/*13.查询代价小于300元的机票*/SELECT Price 代价,discount 折扣 ,condition 经受状态,worker 受理工作人员FROM TicketWHERE Price
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )