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

SQL Server电影院数据库管理系统【英文版-源码】

友情连接
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

到此这篇SQL Server电影院数据库管理系统【英文版-源码】--（Movie Theatre Management System Database）的文章就介绍到这了,更多相关内容请继续浏览下面的相关推荐文章，希望大家都能在编程的领域有一番成就！

上一篇：数据库管理系统DBMS的主要功能

下一篇：【MySQL】：探秘主流关系型数据库管理系统及SQL语言

版权声明：
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！

转载请注明出处，原文链接：https://www.xkablog.com/hd-data/4231.html