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

友情连接

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