SQL Server电影院数据库管理系统【英文版-源码】--（Movie Theatre Management System Database）_若如初见kk_

网络投稿 02-07 3273

SQL Server电影院数据库管理系统【英文版-源码】友情连接1、医疗信息管理系统数据库--MySQL2、邮件管理数据库设计--MySQL3、学生成绩管理系统数据库设计--MySQL4、点餐系统数据库设计--SQL Server5、商品管理系统数据库设计--SQL Server6、SQL Server医疗信息管理系统数据库【英文版-源码】--（Medical Management System Database） 1. 创建数据库及数据表、插入数据2. 查询数据3. 创建视图

友情连接 1、医疗信息管理系统数据库–MySQL

医疗信息管理系统数据库–MySQL

2、邮件管理数据库设计–MySQL

邮件管理数据库设计–MySQL

3、学生成绩管理系统数据库设计–MySQL

学生成绩管理系统数据库设计–MySQL

4、点餐系统数据库设计–SQL Server

点餐系统数据库设计–SQL Server

5、商品管理系统数据库设计–SQL Server

商品管理系统数据库设计–SQL Server

6、SQL Server医疗信息管理系统数据库【英文版-源码】–（Medical Management System Database）

SQL Server医疗信息管理系统数据库【英文版-源码】–（Medical Management System Database）

1. 创建数据库及数据表、插入数据 --******Create/Drop Databse****** -- if MovieTheatre exists, kill current connections to Database -- make single user IF DB_ID('MovieTheatre') IS NOT NULL BEGIN USE [MASTER]; ALTER DATABASE [MovieTheatre] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE MovieTheatre; END GO -- create new database called MovieTheatre CREATE DATABASE MovieTheatre; GO USE MovieTheatre; GO --******Create Tables******* --Classifications DROP TABLE IF EXISTS Classifications GO CREATE TABLE Classifications ( classification CHAR(2) NOT NULL PRIMARY KEY, classificationName VARCHAR(150) NOT NULL, classificationMinimumAge CHAR(14) NOT NULL, ); GO --Movies Tbl DROP TABLE IF EXISTS Movies GO CREATE TABLE Movies ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO -- Genres DROP TABLE IF EXISTS Genres GO CREATE TABLE Genres ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO -- MoviesGenres DROP TABLE IF EXISTS MoviesGenres GO CREATE TABLE MoviesGenres ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO -- CinemasTypes DROP TABLE IF EXISTS CinemasTypes GO CREATE TABLE CinemasTypes ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO -- Cinemas DROP TABLE IF EXISTS Cinemas GO CREATE TABLE Cinemas ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO -- Sessions DROP TABLE IF EXISTS MovieSessions GO CREATE TABLE MovieSessions ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO -- Customers DROP TABLE IF EXISTS Customers GO CREATE TABLE Customers ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO -- TicketPurchases DROP TABLE IF EXISTS TicketPurchases GO CREATE TABLE TicketPurchases ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO -- CustomersReviews DROP TABLE IF EXISTS CustomersReviews GO CREATE TABLE CustomersReviews -- customer Can only review a movie ONCE ( -- 需要完整代码请添加文章底部微信，付费咨询 ); GO --******Database Population******* INSERT INTO Classifications VALUES ('G', 'General', 'Not Applicable'), -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO Genres (genresType) VALUES -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO Movies VALUES -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO MoviesGenres VALUES -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO CinemasTypes VALUES -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO Cinemas VALUES -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO Customers VALUES -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO CustomersReviews (customersReviewText, customersReviewRating, customerEmail, movieId) VALUES -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO MovieSessions VALUES -- 需要完整代码请添加文章底部微信，付费咨询 INSERT INTO TicketPurchases VALUES -- 需要完整代码请添加文章底部微信，付费咨询 2. 查询数据 USE MovieTheatre; -- Query 1 � Child Friendly Movies -- SELECT movie name, duration and classification of all movies -- WHERE: -- duration of less than 100 minutes -- a classification of �G� or �PG�. -- Order the results by duration. SELECT movieName, movieDuration, classification FROM dbo.Movies WHERE (movieDuration < 100) AND (classification IN ('G', 'PG')) ORDER BY movieDuration; -- Query 2 � Movie Search -- SELECT the movie name, session date/time, cinema type name and cost of all upcoming sessions (i.e. session date/time is later than the current date/time) -- WHERE: -- �star wars� anywhere in the movie name -- Order the results by session date/time SELECT dbo.Movies.movieName, dbo.vSession.sessionDateTime, dbo.vSession.sessionTicketCost, dbo.vSession.cinemaTypeName -- 需要完整代码请添加文章底部微信，付费咨询 -- Query 3 � Review Details -- SELECT text of the reviewdetails,date/time the review was posted, the rating given, the first name, age (calculated from the date of birth) -- WHERE: -- movie ID number of 5 -- Order the results by the review date, in descending order SELECT dbo.CustomersReviews.customersReviewText, dbo.CustomersReviews.customersReviewDateTime, dbo.CustomersReviews.customersReviewRating, dbo.Customers.customerFirstName, FLOOR(DATEDIFF(DAY, dbo.Customers.customerDOB, GETDATE()) / 365.25) AS Age -- 需要完整代码请添加文章底部微信，付费咨询 -- Query 4 � Genre Count -- SELECT the name of all genres in the genre table, and the number of movies of each genre -- show all genres, even if there are no movies of that genre in the database SELECT dbo.Genres.genresType AS Genres, COUNT(dbo.MoviesGenres.movieId) AS MovieCount -- 需要完整代码请添加文章底部微信，付费咨询 -- Query 5 � Movie Review Stats -- SELECT names of all movies in the movie table, how many reviews have been posted per movie, and the average star rating of the reviews per movie -- include all movies, even if they have not been reviewed -- Round the average rating to one decimal place -- order the results by the average rating, in descending order SELECT dbo.Movies.movieName, COUNT(dbo.CustomersReviews.movieId) AS MovieReviewCount, AVG(CAST(dbo.CustomersReviews.customersReviewRating AS FLOAT(1))) AS AverageStarRating -- 需要完整代码请添加文章底部微信，付费咨询 -- Query 6 � Top Selling Movies -- SELECT name and total number of tickets sold of the THREE most popular movies (determined by total ticket sales) SELECT TOP (3) dbo.vSession.movieName, SUM(dbo.TicketPurchases.ticketPurchaseNumberPurchased) AS TotalTicketsSold -- 需要完整代码请添加文章底部微信，付费咨询 -- Query 7 � Customer Ticket Stats -- SELECT full names (by concatenating their first name and last name) of all customers in the customer table, how many tickets they have each purchased, and the total cost of these tickets -- include all customers, even if they have never purchased a ticket -- Order the results by total ticket cost, in descending order SELECT dbo.Customers.customerFirstName + ' ' + dbo.Customers.customerLastName AS CustomerName, ISNULL(SUM(dbo.TicketPurchases.ticketPurchaseNumberPurchased),0) AS TicketsPurchased, ISNULL(SUM(dbo.TicketPurchases.ticketPurchaseNumberPurchased * dbo.MovieSessions.sessionTicketCost),0) AS TotalSpent -- 需要完整代码请添加文章底部微信，付费咨询 -- Query 8 � Age Appropriate Movies -- SELECT movie name, duration and description of all movies that a certain customer (chosen by you) can legally watch, -- WHERE: -- based on the customer�s date of birth and the minimum age required by the movie�s classification -- SUB QUERY: -- SELECT a customer -- WHERE: -- whose date of birth makes them 15-17 years old -- so that the results include all movies except those classified �R� --***** Please note: I have limited the result to a single customer as the sub query requirements stated: SELECT a customer --***** if we wanted to return all customers that suit between the age limit, I would remove the customer email where clause in the sub query SELECT dbo.Movies.movieName, dbo.Movies.movieDuration, dbo.Movies.movieDescription -- 需要完整代码请添加文章底部微信，付费咨询 -- Query 9 � Session Revenue -- SELECT session ID, session date/time, movie name, cinema name, tickets sold and total revenue of all sessions that occurred in the past -- Total revenue is the session cost multiplied by the number of tickets sold -- Ensure that sessions that had no tickets sold appear in the results (with 0 tickets sold and 0 revenue) -- Order the results by total revenue, in descending order SELECT dbo.vSession.sessionId, dbo.vSession.sessionDateTime, dbo.vSession.movieName, dbo.vSession.cinemaName, SUM(ISNULL(dbo.TicketPurchases.ticketPurchaseNumberPurchased, 0)) AS TotalTicketsSold, ISNULL(dbo.TicketPurchases.ticketPurchaseNumberPurchased * dbo.vSession.sessionTicketCost, 0) AS TotalTicketRevenue -- 需要完整代码请添加文章底部微信，付费咨询 3. 创建视图 USE MovieTheatre; GO -- Cinema View -- Create a view that selects the cinema ID number, cinema name, seating capacity and the name of the cinema type for all cinemas DROP VIEW IF EXISTS vCinema GO CREATE VIEW vCinema AS SELECT dbo.Cinemas.cinemaId, dbo.Cinemas.cinemaName, dbo.Cinemas.cinemaSeatingCapacity, dbo.CinemasTypes.cinemaTypeName -- 需要完整代码请添加文章底部微信，付费咨询 GO -- Session View -- Create a view that selects the following details of all rows in the “session” table: -- The session ID number, session date/time and cost of the session. -- The movie ID number, movie name and classification of the movie (e.g. “PG”) being shown. -- The cinema ID number, cinema name, seating capacity and cinema type name of the cinema that the session is in. DROP VIEW IF EXISTS vSession GO CREATE VIEW vSession AS SELECT dbo.MovieSessions.sessionId, dbo.MovieSessions.sessionDateTime, dbo.MovieSessions.sessionTicketCost, dbo.MovieSessions.movieId, dbo.Movies.movieName, dbo.Movies.classification, dbo.vCinema.cinemaId, dbo.vCinema.cinemaName, dbo.vCinema.cinemaSeatingCapacity, dbo.vCinema.cinemaTypeName -- 需要完整代码请添加文章底部微信，付费咨询 GO