sKDKixZR

1/*
2***************************************
3Video Store SQL Code
4IT436-1902A
5
6James Strickland, Pace Student
7Barry University
8
9DATE: 6/8/19
10
11***************************************
12*/
13
14/* Create Table Stucture */
15
16/*  Create Accounts Table 
17    I thew the drop table in there because I had
18    already created the tables before I re-wrote this script */
19
20DROP TABLE IF EXISTS Accounts; 
21
22CREATE TABLE Accounts (
23	account_id int IDENTITY NOT NULL,
24	customer_id int NOT NULL,
25	account_name varchar(150) NOT NULL,
26	account_description varchar(150) NULL,
27	CONSTRAINT PK_Accounts PRIMARY KEY (account_id ASC)
28);
29
30/* Add data to the accounts table */
31
32
33/* Create Customers Table */
34
35DROP TABLE IF EXISTS Customers;
36
37CREATE TABLE Customers(
38       customer_id int IDENTITY NOT NULL,
39       customer_firstname varchar(150) NOT NULL,
40       customer_lastname varchar(150) NULL,
41       customer_address varchar(250) NULL,
42       customer_phone varchar(50) NOT NULL,
43       customer_email varchar(150) NULL,
44CONSTRAINT PK_customers PRIMARY KEY (customer_id ASC)
45);
46
47/* Insert customers data into the table */
48
49insert  into Customers([customer_id],[customer_firstname],[customer_lastname],[customer_address],[customer_phone],[customer_email]) values 
50	(101, 'John', 'Doe', '123 Any St., Anytown, FL 32333','212-555-1212','doej@email.com'),
51	(102, 'Jane','Doe', '123 Any St., Anytown, FL 32333', '212-555-1313','doej2@email.com'),
52	(103, 'Frank', 'Sellars', '144 Franklin Ave, Miami, FL 32399', '212-555-5511', 'sellarsf@email.com'),
53	(104, 'Sue', 'Sellars', '144 Frankin Ave, Miami, FL 32399', '212-555-4123', 'sellarss@email.com');
54
55
56/* Create Movies Table */
57
58DROP TABLE IF EXISTS Movies;
59
60CREATE TABLE Movies(
61       movie_id int IDENTITY NOT NULL,
62       release_year int NULL,
63       movie_title varchar(150) NULL,
64       movie_description varchar(150) NULL,
65       number_in_stock int NULL,
66       rental_or_sale_or_both char(1) NULL,
67       rental_daily_rate decimal(18, 2) NULL,
68CONSTRAINT PK_Movies PRIMARY KEY (movie_id ASC)
69);
70
71/* Insert movies data into Movies table */
72
73insert  into Movies([movie_id],[release_year],[movie_title],[movie_description],[number_in_stock],[rental_or_sale_or_both],[rental_daily_rate]) values 
74	(9999, 1995, 'The Rooster', 'This is a movie desription',10, 'b', 10);
75
76/* Rentals Table */
77
78DROP TABLE IF EXISTS Rentals;
79
80CREATE TABLE Rentals(
81       rental_id int IDENTITY NOT NULL,
82       customer_id int NOT NULL,
83       movie_id int NOT NULL,
84       rental_date_out datetime2(3) NOT NULL,
85       rental_date_return datetime2(3) NULL,
86       rental_amount_due decimal(18, 2) NOT NULL,
87       rental_status_id int NOT NULL,
88CONSTRAINT PK_Rentals PRIMARY KEY (rental_id ASC)
89);
90
91/* Rental Status Table */
92
93DROP TABLE IF EXISTS RentalStatus;
94
95CREATE TABLE RentalStatus(
96       rental_status_id int IDENTITY NOT NULL,
97       rental_status varchar(50) NOT NULL,
98       rental_status_description varchar(150) NULL,
99CONSTRAINT PK_RentalStatus PRIMARY KEY (rental_status_id ASC)
100);
101
102/* Transactions Table */
103
104DROP TABLE IF EXISTS Transactions;
105
106CREATE TABLE Transactions(
107       transaction_id int NOT NULL,
108       account_id int NOT NULL,
109       item_rental_id int NOT NULL,
110       transaction_date datetime2(3) NOT NULL,
111       transaction_amount decimal(18, 2) NOT NULL,
112       transaction_comment varchar(150) NULL,
113CONSTRAINT PK_Transactions PRIMARY KEY (transaction_id ASC)
114);
115
116/* ************************************************************ */