Database Management Systems Assignment
VerifiedAdded on 2020/03/23
|15
|4148
|242
AI Summary
The assignment focuses on database management systems, requiring students to solve a series of SQL queries related to various aspects like inventory management, actor information, movie details, and director attributes. It involves manipulating data from multiple tables using JOIN operations and understanding concepts like WHERE clauses, CONCAT functions, and date formatting.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
Assignment 2
ITECH 1006 – Database Management Systems
Student ID – 30329735
Student Name -
Date –
ITECH 1006 – Database Management Systems
Student ID – 30329735
Student Name -
Date –
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Report
Task 1
Relational Schema
1. GENRE
Attribute Datetype Key
FiielddgenreID INTEGER PRIMARY KEY
FiielddgenreName CHAR(50)
FiielddDescription CHAR(250)
2. SUPPLIER
Attribute Datetype Key
FiielddsupplierID INTEGER PRIMARY KEY
FiielddsupplierName CHAR(50)
FiielddContact CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(50)
FiielddSuburb CHAR(50)
FiielddPostcode CHAR(5)
FiielddCountry CHAR(50)
FiielddComments CHAR(250)
3. ACTOR
Attribute Datetype Key
FiielddactorID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPlaceOfBirth CHAR(100)
FiielddDateOfBirth DATE
FiielddDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY
Fiielddtitle CHAR(250)
FiielddunitPrice DECIMAL(5,2)
Fiielddmarkup DECIMAL(5,2)
Fiieldddiscount DECIMAL(5,2)
FiielddqtyInStock INTEGER
FiielddgenreID INTEGER FOREIGN KEY
FiielddsupplierID INTEGER FOREIGN KEY
Task 1
Relational Schema
1. GENRE
Attribute Datetype Key
FiielddgenreID INTEGER PRIMARY KEY
FiielddgenreName CHAR(50)
FiielddDescription CHAR(250)
2. SUPPLIER
Attribute Datetype Key
FiielddsupplierID INTEGER PRIMARY KEY
FiielddsupplierName CHAR(50)
FiielddContact CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(50)
FiielddSuburb CHAR(50)
FiielddPostcode CHAR(5)
FiielddCountry CHAR(50)
FiielddComments CHAR(250)
3. ACTOR
Attribute Datetype Key
FiielddactorID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPlaceOfBirth CHAR(100)
FiielddDateOfBirth DATE
FiielddDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY
Fiielddtitle CHAR(250)
FiielddunitPrice DECIMAL(5,2)
Fiielddmarkup DECIMAL(5,2)
Fiieldddiscount DECIMAL(5,2)
FiielddqtyInStock INTEGER
FiielddgenreID INTEGER FOREIGN KEY
FiielddsupplierID INTEGER FOREIGN KEY
5. MANUFACTURER
Attribute Datetype Key
FiielddmanufacturerID INTEGER PRIMARY KEY
FiielddmanufacturerName CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(50)
FiielddSuburb CHAR(50)
FiielddPostcode CHAR(5)
FiielddCountry CHAR(50)
6. DIRECTOR
Attribute Datetype Key
FiieldddirectorID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPlaceOfBirth CHAR(100)
FiielddDateOfBirth DATE
FiielddDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FiielddcollectionID INTEGER PRIMARY KEY
FiielddcollectionName CHAR(50)
FiielddDescription CHAR(250)
FiieldddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddmadeYear INTEGER
FiielddmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FiielddcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
10. MOVIE
Attribute Datetype Key
Attribute Datetype Key
FiielddmanufacturerID INTEGER PRIMARY KEY
FiielddmanufacturerName CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(50)
FiielddSuburb CHAR(50)
FiielddPostcode CHAR(5)
FiielddCountry CHAR(50)
6. DIRECTOR
Attribute Datetype Key
FiieldddirectorID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPlaceOfBirth CHAR(100)
FiielddDateOfBirth DATE
FiielddDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FiielddcollectionID INTEGER PRIMARY KEY
FiielddcollectionName CHAR(50)
FiielddDescription CHAR(250)
FiieldddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddmadeYear INTEGER
FiielddmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FiielddcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
10. MOVIE
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddreleaseYear INTEGER
Fiielddclassification CHAR(5)
FiielddstarRating INTEGER
FiieldddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FiielddactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fiielddrole CHAR(50)
12. CUSTOMER
Attribute Datetype Key
FiielddcustomerID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(100)
FiielddPostcode INTEGER
FiielddloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FiielddreceiptID INTEGER PRIMARY KEY
FiielddcustomerID INTEGER FOREIGN KEY
FiielddreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FiielddreceiptID INTEGER PRIMARY KEY
FiielddinventoryID INTEGER FOREIGN KEY
Fiielddquantity INTEGER
FiielddamountPaid DECIMAL(5,2)
(Lucid Software Inc. 2015)
Task 2
KEY
FiielddreleaseYear INTEGER
Fiielddclassification CHAR(5)
FiielddstarRating INTEGER
FiieldddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FiielddactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fiielddrole CHAR(50)
12. CUSTOMER
Attribute Datetype Key
FiielddcustomerID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(100)
FiielddPostcode INTEGER
FiielddloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FiielddreceiptID INTEGER PRIMARY KEY
FiielddcustomerID INTEGER FOREIGN KEY
FiielddreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FiielddreceiptID INTEGER PRIMARY KEY
FiielddinventoryID INTEGER FOREIGN KEY
Fiielddquantity INTEGER
FiielddamountPaid DECIMAL(5,2)
(Lucid Software Inc. 2015)
Task 2
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Create Statements
DROP Database IF EXISTS Startstruck_Entertainment_30329735;
create database Startstruck_Entertainment_30329735;
use Startstruck_Entertainment_30329735;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
FiielddgenreID int NOT NULL AUTO_INCREMENT,
FiielddgenreName CHAR(50) NOT NULL,
FiielddDescription CHAR(250) NULL,
PRIMARY KEY (FiielddgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FiielddsupplierID int NOT NULL AUTO_INCREMENT,
FiielddsupplierName CHAR(50) NOT NULL,
FiielddContactName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(50) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode CHAR(5) NOT NULL,
FiielddCountry CHAR(50) NOT NULL,
FiielddComments CHAR(250) NULL,
PRIMARY KEY (FiielddsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FiielddactorID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPlaceOfBirth CHAR(100) NOT NULL,
FiielddDateOfBirth Date NOT NULL,
FiielddDateOfDeath Date NULL,
PRIMARY KEY (FiielddactorID)
);
DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
FiielddinventoryID int NOT NULL AUTO_INCREMENT,
DROP Database IF EXISTS Startstruck_Entertainment_30329735;
create database Startstruck_Entertainment_30329735;
use Startstruck_Entertainment_30329735;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
FiielddgenreID int NOT NULL AUTO_INCREMENT,
FiielddgenreName CHAR(50) NOT NULL,
FiielddDescription CHAR(250) NULL,
PRIMARY KEY (FiielddgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FiielddsupplierID int NOT NULL AUTO_INCREMENT,
FiielddsupplierName CHAR(50) NOT NULL,
FiielddContactName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(50) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode CHAR(5) NOT NULL,
FiielddCountry CHAR(50) NOT NULL,
FiielddComments CHAR(250) NULL,
PRIMARY KEY (FiielddsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FiielddactorID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPlaceOfBirth CHAR(100) NOT NULL,
FiielddDateOfBirth Date NOT NULL,
FiielddDateOfDeath Date NULL,
PRIMARY KEY (FiielddactorID)
);
DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
FiielddinventoryID int NOT NULL AUTO_INCREMENT,
Fiielddtitle CHAR(250) NOT NULL,
FiielddunitPrice decimal(5,2) NOT NULL,
Fiielddmarkup decimal(5,2) NOT NULL,
Fiieldddiscount decimal(5,2) NOT NULL,
FiielddqtyInStock int NOT NULL,
FiielddgenreID int NOT NULL,
FiielddsupplierID int NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddgenreID)
REFERENCES GENRE(FiielddgenreID),
FOREIGN KEY (FiielddsupplierID)
REFERENCES SUPPLIER(FiielddsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FiielddmanufacturerID int NOT NULL AUTO_INCREMENT,
FiielddmanufacturerName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(100) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode int NOT NULL,
FiielddCountry CHAR(50) NULL,
PRIMARY KEY (FiielddmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FiieldddirectorID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPlaceOfBirth CHAR(100) NOT NULL,
FiielddDateOfBirth Date NOT NULL,
FiielddDateOfDeath Date NULL,
PRIMARY KEY (FiieldddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FiielddcollectionID int NOT NULL AUTO_INCREMENT,
FiielddcollectionName CHAR(50) NOT NULL,
FiielddDescription CHAR(250) NOT NULL,
FiieldddateReleased Date NOT NULL,
PRIMARY KEY (FiielddcollectionID)
);
FiielddunitPrice decimal(5,2) NOT NULL,
Fiielddmarkup decimal(5,2) NOT NULL,
Fiieldddiscount decimal(5,2) NOT NULL,
FiielddqtyInStock int NOT NULL,
FiielddgenreID int NOT NULL,
FiielddsupplierID int NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddgenreID)
REFERENCES GENRE(FiielddgenreID),
FOREIGN KEY (FiielddsupplierID)
REFERENCES SUPPLIER(FiielddsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FiielddmanufacturerID int NOT NULL AUTO_INCREMENT,
FiielddmanufacturerName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(100) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode int NOT NULL,
FiielddCountry CHAR(50) NULL,
PRIMARY KEY (FiielddmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FiieldddirectorID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPlaceOfBirth CHAR(100) NOT NULL,
FiielddDateOfBirth Date NOT NULL,
FiielddDateOfDeath Date NULL,
PRIMARY KEY (FiieldddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FiielddcollectionID int NOT NULL AUTO_INCREMENT,
FiielddcollectionName CHAR(50) NOT NULL,
FiielddDescription CHAR(250) NOT NULL,
FiieldddateReleased Date NOT NULL,
PRIMARY KEY (FiielddcollectionID)
);
DROP TABLE IF EXISTS COLLECTIBLE;
CREATE TABLE COLLECTIBLE (
FiielddinventoryID int NOT NULL,
FiielddmadeYear int NOT NULL,
FiielddmanufacturerID int NOT NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiielddmanufacturerID)
REFERENCES MANUFACTURER(FiielddmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FiielddinventoryID int NOT NULL,
FiielddcollectionID int NOT NULL,
PRIMARY KEY (FiielddinventoryID, FiielddcollectionID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiielddcollectionID)
REFERENCES COLLECTION(FiielddcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FiielddinventoryID int NOT NULL,
FiielddreleaseYear int NOT NULL,
Fiielddclassification CHAR(5) NOT NULL,
FiielddstarRating int NOT NULL,
FiieldddirectorID int NOT NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiieldddirectorID)
REFERENCES DIRECTOR(FiieldddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FiielddactorID int NOT NULL,
FiielddinventID int NOT NULL,
Fiielddrole CHAR(50) NOT NULL,
PRIMARY KEY (FiielddactorID,FiielddinventID),
FOREIGN KEY (FiielddinventID)
REFERENCES MOVIE(FiielddinventoryID),
FOREIGN KEY (FiielddactorID)
CREATE TABLE COLLECTIBLE (
FiielddinventoryID int NOT NULL,
FiielddmadeYear int NOT NULL,
FiielddmanufacturerID int NOT NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiielddmanufacturerID)
REFERENCES MANUFACTURER(FiielddmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FiielddinventoryID int NOT NULL,
FiielddcollectionID int NOT NULL,
PRIMARY KEY (FiielddinventoryID, FiielddcollectionID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiielddcollectionID)
REFERENCES COLLECTION(FiielddcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FiielddinventoryID int NOT NULL,
FiielddreleaseYear int NOT NULL,
Fiielddclassification CHAR(5) NOT NULL,
FiielddstarRating int NOT NULL,
FiieldddirectorID int NOT NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiieldddirectorID)
REFERENCES DIRECTOR(FiieldddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FiielddactorID int NOT NULL,
FiielddinventID int NOT NULL,
Fiielddrole CHAR(50) NOT NULL,
PRIMARY KEY (FiielddactorID,FiielddinventID),
FOREIGN KEY (FiielddinventID)
REFERENCES MOVIE(FiielddinventoryID),
FOREIGN KEY (FiielddactorID)
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
REFERENCES ACTOR(FiielddactorID)
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FiielddcustomerID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(100) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode int NOT NULL,
FiielddloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FiielddcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FiielddreceiptID int NOT NULL AUTO_INCREMENT,
FiielddcustomerID int NOT NULL,
FiielddreceiptDate date NOT NULL,
PRIMARY KEY (FiielddreceiptID),
FOREIGN KEY (FiielddcustomerID)
REFERENCES CUSTOMER(FiielddcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FiielddreceiptID int NOT NULL,
FiielddinventoryID int NOT NULL,
Fiielddquantity int NOT NULL,
FiielddamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FiielddreceiptID),
FOREIGN KEY (FiielddreceiptID)
REFERENCES RECEIPT(FiielddreceiptID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID)
);
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FiielddcustomerID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(100) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode int NOT NULL,
FiielddloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FiielddcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FiielddreceiptID int NOT NULL AUTO_INCREMENT,
FiielddcustomerID int NOT NULL,
FiielddreceiptDate date NOT NULL,
PRIMARY KEY (FiielddreceiptID),
FOREIGN KEY (FiielddcustomerID)
REFERENCES CUSTOMER(FiielddcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FiielddreceiptID int NOT NULL,
FiielddinventoryID int NOT NULL,
Fiielddquantity int NOT NULL,
FiielddamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FiielddreceiptID),
FOREIGN KEY (FiielddreceiptID)
REFERENCES RECEIPT(FiielddreceiptID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID)
);
Task 3
Insert Statements
use Startstruck_Entertainment_30329735;
INSERT INTO GENRE (FiielddgenreName, FiielddDescription) VALUES
("Comedy",""),
("Sports", "Fictional and sports documentaries"),
("Children","All age groups to 18"),
("SciFi",""),
("Crime",""),
("Documentary","Factual accounts including science and history"),
("Fiction","Any other fiction"),
("Fantasy",""),
("Adventure",""),
("Action","");
INSERT INTO SUPPLIER (FiielddsupplierName, FiielddContactName,
FiielddPhone, FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode,
FiielddCountry, FiielddComments) values
("Figures’n’Toys","Anita Schmidt","0456789163","sales@fnt.com.au","94 Lancaster
St","Collingwood",3066,"Australia",""),
("Mega Entertainment","James
McGrath","+61355551549","sales@megatainment.com","32 Easy
St","Perth",6140,"Australia",""),
("Crazy Collectibles","Ernie
Miller","0448789987","sales@crazycollectibles.com.au","3 North
Road","Auckland",1111,"New Zealand",""),
("Rare Films","Samantha Stevens","0751672924","rarefilms.com.au","9 Newcastle
Ave","Cabramatta",2166,"Australia",""),
("Premiere Entertainment","Winston
Jones","0657168342","premiereentertainment.com","1548 Hume
Hwy","GlenFiieldd",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor ButterFiieldd","+4107529817","sales@movie-
magic.co.uk","88 Bond Parade","London",9911,"UK","Recalculate VAT"),
("VIC Entertainment","Rupert
Ballinger","0498528417","vicentertainment.gov.au","62
BluebirdCrt","Collingwood",3066,"Australia",""),
("Samson Films","Delilah Jones","+6174019841","samsonfilms.com.au","27
Fortitude Dr","Brisbane",4001,"Australia","Minimum order $100");
insert into ACTOR (FiielddFirstName, FiielddLastName, FiielddPlaceOfBirth,
FiielddDateOfBirth, FiielddDateOfDeath) values
("Christopher","Lee","Belgravia London (UK)",STR_TO_DATE("05-27-
1922","%m-%d-%Y"),STR_TO_DATE("06-07-2015","%m-%d-%Y")),
("Marilyn","Monroe","Los Angeles California (USA)",STR_TO_DATE("06-01-
1926","%m-%d-%Y"),STR_TO_DATE("08-05-1962","%m-%d-%Y"));
Insert Statements
use Startstruck_Entertainment_30329735;
INSERT INTO GENRE (FiielddgenreName, FiielddDescription) VALUES
("Comedy",""),
("Sports", "Fictional and sports documentaries"),
("Children","All age groups to 18"),
("SciFi",""),
("Crime",""),
("Documentary","Factual accounts including science and history"),
("Fiction","Any other fiction"),
("Fantasy",""),
("Adventure",""),
("Action","");
INSERT INTO SUPPLIER (FiielddsupplierName, FiielddContactName,
FiielddPhone, FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode,
FiielddCountry, FiielddComments) values
("Figures’n’Toys","Anita Schmidt","0456789163","sales@fnt.com.au","94 Lancaster
St","Collingwood",3066,"Australia",""),
("Mega Entertainment","James
McGrath","+61355551549","sales@megatainment.com","32 Easy
St","Perth",6140,"Australia",""),
("Crazy Collectibles","Ernie
Miller","0448789987","sales@crazycollectibles.com.au","3 North
Road","Auckland",1111,"New Zealand",""),
("Rare Films","Samantha Stevens","0751672924","rarefilms.com.au","9 Newcastle
Ave","Cabramatta",2166,"Australia",""),
("Premiere Entertainment","Winston
Jones","0657168342","premiereentertainment.com","1548 Hume
Hwy","GlenFiieldd",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor ButterFiieldd","+4107529817","sales@movie-
magic.co.uk","88 Bond Parade","London",9911,"UK","Recalculate VAT"),
("VIC Entertainment","Rupert
Ballinger","0498528417","vicentertainment.gov.au","62
BluebirdCrt","Collingwood",3066,"Australia",""),
("Samson Films","Delilah Jones","+6174019841","samsonfilms.com.au","27
Fortitude Dr","Brisbane",4001,"Australia","Minimum order $100");
insert into ACTOR (FiielddFirstName, FiielddLastName, FiielddPlaceOfBirth,
FiielddDateOfBirth, FiielddDateOfDeath) values
("Christopher","Lee","Belgravia London (UK)",STR_TO_DATE("05-27-
1922","%m-%d-%Y"),STR_TO_DATE("06-07-2015","%m-%d-%Y")),
("Marilyn","Monroe","Los Angeles California (USA)",STR_TO_DATE("06-01-
1926","%m-%d-%Y"),STR_TO_DATE("08-05-1962","%m-%d-%Y"));
insert into ACTOR (FiielddFirstName, FiielddLastName, FiielddPlaceOfBirth,
FiielddDateOfBirth) values
("Chris","Hemsworth","Melbourne Vic (Aus)",STR_TO_DATE("08-11-1983","%m-
%d-%Y")),
("Angelina","Jolie","Los Angeles, Californaia (USA)",STR_TO_DATE("06-04-
1975","%m-%d-%Y")),
("Meryl","Streep","Summit New Jersey (USA)",STR_TO_DATE("06-22-
1949","%m-%d-%Y")),
("Charlize","Theron","Benoni Gauteng (Sth Africa)",STR_TO_DATE("08-07-
1975","%m-%d-%Y")),
("Drew","Barrymore","Culver City California (USA)",STR_TO_DATE("02-22-
1975","%m-%d-%Y")),
("Lucy","Liu","Jackson Heights New York (USA)",STR_TO_DATE("12-02-
1968","%m-%d-%Y")),
("David","Tennant","Bathgate (UK)",STR_TO_DATE("04-18-1971","%m-%d-
%Y")),
("Dwayne (The Rock)","Johnson","Hayward California
(USA)",STR_TO_DATE("05-02-1972","%m-%d-%Y")),
("Robert (Jnr)","Downey","Manhattan, New York (USA)",STR_TO_DATE("04-04-
1966","%m-%d-%Y"));
insert into DIRECTOR (FiielddfirstName, FiielddlastName, FiielddPlaceOfBirth,
FiielddDateOfBirth) values
("Angelina","Jolie","Los Angeles California (USA)",STR_TO_DATE("06-04-
1975","%m-%d-%Y")),
("Drew","Barrymore","Culver City, California (USA)",STR_TO_DATE("02-22-
1975","%m-%d-%Y")),
("Steven","Spielberg","Cincinnati, Ohio (USA)",STR_TO_DATE("12-18-
1946","%m-%d-%Y")),
("David","Heyman","London (UK)",STR_TO_DATE("07-26-1961","%m-%d-
%Y")),
("Quinton","Tarantino","Knoxville Tennessee (USA)",STR_TO_DATE("03-27-
1963","%m-%d-%Y")),
("Kenneth","Branagh","Belfast (UK)",STR_TO_DATE("12-10-1960","%m-%d-
%Y"));
insert into DIRECTOR (FiielddfirstName, FiielddlastName, FiielddPlaceOfBirth,
FiielddDateOfBirth, FiielddDateOfDeath) values
("Alfred","Hitchcock","London (UK)",STR_TO_DATE("08-13-1899","%m-%d-
%Y"), STR_TO_DATE("04-29-1980","%m-%d-%Y")),
("Terence","Fisher","Maida Vale, London (UK)",STR_TO_DATE("02-23-
1904","%m-%d-%Y"), STR_TO_DATE("06-18-1980","%m-%d-%Y"));
insert into MANUFACTURER (FiielddmanufacturerName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode, FiielddCountry)
values
("Focus Home Interactive", "+48299836600", "sales@fhi.com","9 Rue de Chat
Noir","Paris", 7519,"France"),
("Movie Figure Smithy", "+69922124569", "sales@thesmithy.com","4 Beach Road
East","Wan Chai", 7654, "Hong Kong"),
FiielddDateOfBirth) values
("Chris","Hemsworth","Melbourne Vic (Aus)",STR_TO_DATE("08-11-1983","%m-
%d-%Y")),
("Angelina","Jolie","Los Angeles, Californaia (USA)",STR_TO_DATE("06-04-
1975","%m-%d-%Y")),
("Meryl","Streep","Summit New Jersey (USA)",STR_TO_DATE("06-22-
1949","%m-%d-%Y")),
("Charlize","Theron","Benoni Gauteng (Sth Africa)",STR_TO_DATE("08-07-
1975","%m-%d-%Y")),
("Drew","Barrymore","Culver City California (USA)",STR_TO_DATE("02-22-
1975","%m-%d-%Y")),
("Lucy","Liu","Jackson Heights New York (USA)",STR_TO_DATE("12-02-
1968","%m-%d-%Y")),
("David","Tennant","Bathgate (UK)",STR_TO_DATE("04-18-1971","%m-%d-
%Y")),
("Dwayne (The Rock)","Johnson","Hayward California
(USA)",STR_TO_DATE("05-02-1972","%m-%d-%Y")),
("Robert (Jnr)","Downey","Manhattan, New York (USA)",STR_TO_DATE("04-04-
1966","%m-%d-%Y"));
insert into DIRECTOR (FiielddfirstName, FiielddlastName, FiielddPlaceOfBirth,
FiielddDateOfBirth) values
("Angelina","Jolie","Los Angeles California (USA)",STR_TO_DATE("06-04-
1975","%m-%d-%Y")),
("Drew","Barrymore","Culver City, California (USA)",STR_TO_DATE("02-22-
1975","%m-%d-%Y")),
("Steven","Spielberg","Cincinnati, Ohio (USA)",STR_TO_DATE("12-18-
1946","%m-%d-%Y")),
("David","Heyman","London (UK)",STR_TO_DATE("07-26-1961","%m-%d-
%Y")),
("Quinton","Tarantino","Knoxville Tennessee (USA)",STR_TO_DATE("03-27-
1963","%m-%d-%Y")),
("Kenneth","Branagh","Belfast (UK)",STR_TO_DATE("12-10-1960","%m-%d-
%Y"));
insert into DIRECTOR (FiielddfirstName, FiielddlastName, FiielddPlaceOfBirth,
FiielddDateOfBirth, FiielddDateOfDeath) values
("Alfred","Hitchcock","London (UK)",STR_TO_DATE("08-13-1899","%m-%d-
%Y"), STR_TO_DATE("04-29-1980","%m-%d-%Y")),
("Terence","Fisher","Maida Vale, London (UK)",STR_TO_DATE("02-23-
1904","%m-%d-%Y"), STR_TO_DATE("06-18-1980","%m-%d-%Y"));
insert into MANUFACTURER (FiielddmanufacturerName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode, FiielddCountry)
values
("Focus Home Interactive", "+48299836600", "sales@fhi.com","9 Rue de Chat
Noir","Paris", 7519,"France"),
("Movie Figure Smithy", "+69922124569", "sales@thesmithy.com","4 Beach Road
East","Wan Chai", 7654, "Hong Kong"),
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
("Modelworks", "+1456123789", "info@modelworks.com","12 Danshui
Street","Zhongzheng", 1020, "Taiwan"),
("Rockstar", "+1451586604", "info@rockstarfigures.com","40 Grand
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (FiielddfirstName, FiielddlastName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode) values
("Brigg","John","53159510","JohnnoBrigg@gmail.com","1 Bells
Road","Napoleons","3352"),
("Summers","Joe","45613208","jsummers@yahoo.com","52 Main
St","Maryborough","3465"),
("Winter","Sam","12356713","winter@xyz.net.au","2156 Skipton
Rd","Sebastopol","3356"),
("Fall","Sally","15626543","autumn@gmail.com","65 Commercial
St","Clunes","3370"),
("Spencer","Troy","040853185","tspencer88@gmail.com","65Crest
Ave","Ballarat","3353"),
("Jones","Tommy","51657896","whereistom@gone4good.com","1254 Sturt
St","Ballarat","3353");
insert into CUSTOMER (FiielddfirstName, FiielddlastName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode,
FiielddloyaltyDiscount ) values
("Spring","Mary","13265413","flowers@iprimus.com","98 Neil
St","Beaufort","3373",0.12),
("Jones","Pat","049513572","patsyJ@anet.com.au","46 Wattle
Ave","Wendouree","3355",0.1),
("Matthews","Doreen","21356467","d.matthews@mynet.com","7 Hepburn Springs
Blvd","Daylesford","3460",0.15),
("McKenzie","Barry","32165489","bazza@adventure.com","47 Gillies
St","Ballarat","3350",0.19);
insert into INVENTORY (Fiielddtitle, FiielddunitPrice, Fiielddmarkup,
Fiieldddiscount, FiielddqtyInStock, FiielddgenreID, FiielddsupplierID) values
("R2D2", 27.00,160,0,2,4,3),
("Han Solo Figure", 10.00,170,0,12,4,3),
("Millenium Falcon Model", 128.00,140,0,7,4,2),
("Syberia", 10.00,110,5,2,4,4),
("Devil Wears Prada", 10.00,130,5,14,1,8),
("Great Expectations", 10.00,180,5,6,7,2),
("Wookie Figure", 418.00,130,0,1,4,2),
("Dekker Figure", 22.00,210,0,6,4,1),
("USS Enterprise Model", 110.00,120,10,2,4,4);
insert into MOVIE (FiielddinventoryID,FiielddreleaseYear, Fiielddclassification,
FiielddstarRating, FiieldddirectorID) values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
Street","Zhongzheng", 1020, "Taiwan"),
("Rockstar", "+1451586604", "info@rockstarfigures.com","40 Grand
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (FiielddfirstName, FiielddlastName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode) values
("Brigg","John","53159510","JohnnoBrigg@gmail.com","1 Bells
Road","Napoleons","3352"),
("Summers","Joe","45613208","jsummers@yahoo.com","52 Main
St","Maryborough","3465"),
("Winter","Sam","12356713","winter@xyz.net.au","2156 Skipton
Rd","Sebastopol","3356"),
("Fall","Sally","15626543","autumn@gmail.com","65 Commercial
St","Clunes","3370"),
("Spencer","Troy","040853185","tspencer88@gmail.com","65Crest
Ave","Ballarat","3353"),
("Jones","Tommy","51657896","whereistom@gone4good.com","1254 Sturt
St","Ballarat","3353");
insert into CUSTOMER (FiielddfirstName, FiielddlastName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode,
FiielddloyaltyDiscount ) values
("Spring","Mary","13265413","flowers@iprimus.com","98 Neil
St","Beaufort","3373",0.12),
("Jones","Pat","049513572","patsyJ@anet.com.au","46 Wattle
Ave","Wendouree","3355",0.1),
("Matthews","Doreen","21356467","d.matthews@mynet.com","7 Hepburn Springs
Blvd","Daylesford","3460",0.15),
("McKenzie","Barry","32165489","bazza@adventure.com","47 Gillies
St","Ballarat","3350",0.19);
insert into INVENTORY (Fiielddtitle, FiielddunitPrice, Fiielddmarkup,
Fiieldddiscount, FiielddqtyInStock, FiielddgenreID, FiielddsupplierID) values
("R2D2", 27.00,160,0,2,4,3),
("Han Solo Figure", 10.00,170,0,12,4,3),
("Millenium Falcon Model", 128.00,140,0,7,4,2),
("Syberia", 10.00,110,5,2,4,4),
("Devil Wears Prada", 10.00,130,5,14,1,8),
("Great Expectations", 10.00,180,5,6,7,2),
("Wookie Figure", 418.00,130,0,1,4,2),
("Dekker Figure", 22.00,210,0,6,4,1),
("USS Enterprise Model", 110.00,120,10,2,4,4);
insert into MOVIE (FiielddinventoryID,FiielddreleaseYear, Fiielddclassification,
FiielddstarRating, FiieldddirectorID) values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
insert into COLLECTIBLE (FiielddinventoryID, FiielddmadeYear,
FiielddmanufacturerID) values
(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FiielddcollectionName, FiielddDescription,
FiieldddateReleased) values
("Star Wars Droids", "6 piece Star Wars droids collection",STR_TO_DATE("07-01-
2015","%m-%d-%Y")),
("Star WarsMega Collection", "8 piece Star Wars collection of characters from death
star",STR_TO_DATE("06-15-2008","%m-%d-%Y")),
("Alien", "3 piece Alien collection",STR_TO_DATE("09-30-1999","%m-%d-%Y")),
("Startrek", "10 part super collection",STR_TO_DATE("12-24-2001","%m-%d-
%Y")),
("Spaceships collection", "15 part spaceship collection from a variety of
movies",STR_TO_DATE("12-24-2001","%m-%d-%Y")),
("Bladerunner", "5 character original Bladerunner collection",STR_TO_DATE("12-
15-1990","%m-%d-%Y"));
insert into CAST (FiielddactorID, FiielddinventID, Fiielddrole) values
(1,4, "Hero"),
(2,4, "Heroine"),
(3,4, "Comedian"),
(4,5,"Comedian"),
(1,5,"Hero"),
(5,5,"Heroine"),
(6,5,"Villain"),
(7,6, "Hero"),
(8,6, "Heroine"),
(9,6, "Comedian");
FiielddmanufacturerID) values
(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FiielddcollectionName, FiielddDescription,
FiieldddateReleased) values
("Star Wars Droids", "6 piece Star Wars droids collection",STR_TO_DATE("07-01-
2015","%m-%d-%Y")),
("Star WarsMega Collection", "8 piece Star Wars collection of characters from death
star",STR_TO_DATE("06-15-2008","%m-%d-%Y")),
("Alien", "3 piece Alien collection",STR_TO_DATE("09-30-1999","%m-%d-%Y")),
("Startrek", "10 part super collection",STR_TO_DATE("12-24-2001","%m-%d-
%Y")),
("Spaceships collection", "15 part spaceship collection from a variety of
movies",STR_TO_DATE("12-24-2001","%m-%d-%Y")),
("Bladerunner", "5 character original Bladerunner collection",STR_TO_DATE("12-
15-1990","%m-%d-%Y"));
insert into CAST (FiielddactorID, FiielddinventID, Fiielddrole) values
(1,4, "Hero"),
(2,4, "Heroine"),
(3,4, "Comedian"),
(4,5,"Comedian"),
(1,5,"Hero"),
(5,5,"Heroine"),
(6,5,"Villain"),
(7,6, "Hero"),
(8,6, "Heroine"),
(9,6, "Comedian");
Task 4
SQL Queries
use Startstruck_Entertainment_30329735;
/* Query a */
select * from Supplier order by FiielddSuburb;
/* Query b */
Select Fiielddtitle, FiielddsupplierName, FiielddEmail, FiielddPhone,
FiielddSuburb from Inventory inner join Movie
on Inventory. FiielddinventoryID= Movie. FiielddinventoryID
inner join Supplier on Inventory. FiielddsupplierID= Supplier. FiielddsupplierID;
/* Query c */
select CONCAT( FiielddfirstName," " , FiielddlastName) as 'ActorName',
YEAR(now()) - YEAR( FiielddDateOfBirth) - (DATE_FORMAT(now(), '%m%d') <
DATE_FORMAT( FiielddDateOfBirth, '%m%d')) as ActorAge
from Actor where FiielddDateOfDeath is null;
/* Query d */
Select Inventory. Fiielddtitle, FiielddgenreName from Inventory inner join Genre
on Inventory. FiielddgenreID= Genre. FiielddgenreID order by Fiielddtitle desc;
/* Query e */
select Fiielddtitle, FiielddunitPrice, Fiielddmarkup, FiielddqtyInStock,
FiielddsupplierName, FiielddmanufacturerName
from COLLECTIBLE inner join inventory on COLLECTIBLE.
FiielddinventoryID= inventory. FiielddinventoryID
inner join SUPPLIER on SUPPLIER. FiielddsupplierID= inventory.
FiielddsupplierID
inner join manufacturer on manufacturer. FiielddmanufacturerID= COLLECTIBLE.
FiielddmanufacturerID
order by Fiielddtitle;
/* Query f */
Select FiielddgenreName from Genre where FiielddgenreID not in (select
FiielddgenreID from Inventory);
/* Query g */
SET SQL_SAFE_UPDATES=0;
delete from customer where FiielddPostcode=3353;
SQL Queries
use Startstruck_Entertainment_30329735;
/* Query a */
select * from Supplier order by FiielddSuburb;
/* Query b */
Select Fiielddtitle, FiielddsupplierName, FiielddEmail, FiielddPhone,
FiielddSuburb from Inventory inner join Movie
on Inventory. FiielddinventoryID= Movie. FiielddinventoryID
inner join Supplier on Inventory. FiielddsupplierID= Supplier. FiielddsupplierID;
/* Query c */
select CONCAT( FiielddfirstName," " , FiielddlastName) as 'ActorName',
YEAR(now()) - YEAR( FiielddDateOfBirth) - (DATE_FORMAT(now(), '%m%d') <
DATE_FORMAT( FiielddDateOfBirth, '%m%d')) as ActorAge
from Actor where FiielddDateOfDeath is null;
/* Query d */
Select Inventory. Fiielddtitle, FiielddgenreName from Inventory inner join Genre
on Inventory. FiielddgenreID= Genre. FiielddgenreID order by Fiielddtitle desc;
/* Query e */
select Fiielddtitle, FiielddunitPrice, Fiielddmarkup, FiielddqtyInStock,
FiielddsupplierName, FiielddmanufacturerName
from COLLECTIBLE inner join inventory on COLLECTIBLE.
FiielddinventoryID= inventory. FiielddinventoryID
inner join SUPPLIER on SUPPLIER. FiielddsupplierID= inventory.
FiielddsupplierID
inner join manufacturer on manufacturer. FiielddmanufacturerID= COLLECTIBLE.
FiielddmanufacturerID
order by Fiielddtitle;
/* Query f */
Select FiielddgenreName from Genre where FiielddgenreID not in (select
FiielddgenreID from Inventory);
/* Query g */
SET SQL_SAFE_UPDATES=0;
delete from customer where FiielddPostcode=3353;
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
/* Query h */
SET SQL_SAFE_UPDATES=0;
update Inventory set Fiielddtitle="Wookie Figure – live size" where
Fiielddtitle="Wookie Figure";
/* Query i */
select inventory. Fiielddtitle, CONCAT( ACTOR. FiielddfirstName," " , ACTOR.
FiielddlastName) as 'ActorName', CONCAT( DIRECTOR. FiielddfirstName," " ,
DIRECTOR. FiielddlastName) as 'DirectorName'
from MOVIE inner join inventory on MOVIE. FiielddinventoryID= inventory.
FiielddinventoryID
inner join DIRECTOR on MOVIE. FiieldddirectorID= DIRECTOR.
FiieldddirectorID
inner join CAST on MOVIE. FiielddinventoryID= CAST. FiielddinventID
inner join ACTOR on CAST. FiielddactorID= ACTOR. FiielddactorID;
/* Query j */
SET autocommit=0;
START TRANSACTION;
insert into DIRECTOR ( FiieldddirectorID, FiielddfirstName, FiielddlastName,
FiielddPlaceOfBirth, FiielddDateOfBirth) values
(9, "Steven", "Soderberghin", "Atlanta", STR_TO_DATE("01-14-1963","%m-%d-
%Y"));
insert into INVENTORY ( FiielddinventoryID, Fiielddtitle, FiielddunitPrice,
Fiielddmarkup, Fiieldddiscount, FiielddqtyInStock, FiielddgenreID) values
(10, "Solaris", 18,35,10,27,4);
insert into MOVIE ( FiielddinventoryID, FiielddreleaseYear, Fiielddclassification,
FiielddstarRating, FiieldddirectorID) values
(10,2002,'M',4,9);
COMMIT;
/* Query k */
select Fiielddtitle, FiielddqtyInStock, ( FiielddunitPrice + Fiielddmarkup) as
'SalePrice' from inventory;
/* Query l */
Select Actor. FiielddfirstName, Actor. FiielddlastName, Fiielddtitle,
FiielddgenreName
from Actor inner join Cast on Actor. FiielddActorID= Cast. FiielddActorID
inner join Inventory on Inventory. FiielddinventoryID= Cast. FiielddinventID
SET SQL_SAFE_UPDATES=0;
update Inventory set Fiielddtitle="Wookie Figure – live size" where
Fiielddtitle="Wookie Figure";
/* Query i */
select inventory. Fiielddtitle, CONCAT( ACTOR. FiielddfirstName," " , ACTOR.
FiielddlastName) as 'ActorName', CONCAT( DIRECTOR. FiielddfirstName," " ,
DIRECTOR. FiielddlastName) as 'DirectorName'
from MOVIE inner join inventory on MOVIE. FiielddinventoryID= inventory.
FiielddinventoryID
inner join DIRECTOR on MOVIE. FiieldddirectorID= DIRECTOR.
FiieldddirectorID
inner join CAST on MOVIE. FiielddinventoryID= CAST. FiielddinventID
inner join ACTOR on CAST. FiielddactorID= ACTOR. FiielddactorID;
/* Query j */
SET autocommit=0;
START TRANSACTION;
insert into DIRECTOR ( FiieldddirectorID, FiielddfirstName, FiielddlastName,
FiielddPlaceOfBirth, FiielddDateOfBirth) values
(9, "Steven", "Soderberghin", "Atlanta", STR_TO_DATE("01-14-1963","%m-%d-
%Y"));
insert into INVENTORY ( FiielddinventoryID, Fiielddtitle, FiielddunitPrice,
Fiielddmarkup, Fiieldddiscount, FiielddqtyInStock, FiielddgenreID) values
(10, "Solaris", 18,35,10,27,4);
insert into MOVIE ( FiielddinventoryID, FiielddreleaseYear, Fiielddclassification,
FiielddstarRating, FiieldddirectorID) values
(10,2002,'M',4,9);
COMMIT;
/* Query k */
select Fiielddtitle, FiielddqtyInStock, ( FiielddunitPrice + Fiielddmarkup) as
'SalePrice' from inventory;
/* Query l */
Select Actor. FiielddfirstName, Actor. FiielddlastName, Fiielddtitle,
FiielddgenreName
from Actor inner join Cast on Actor. FiielddActorID= Cast. FiielddActorID
inner join Inventory on Inventory. FiielddinventoryID= Cast. FiielddinventID
inner join Genre on Inventory. FiielddgenreID= Genre. FiielddgenreID order by
Actor. FiielddlastName desc;
(Raghu Ramakrishnan, Johannes Gehrke, Jeff Derstadt, Scott Selicoff, Lin Zhu n.d.)
References
Raghu Ramakrishnan, Johannes Gehrke, Jeff Derstadt, Scott Selicoff, Lin Zhu,
Database Management Systems Solutions Manual IIIrd Edition
Lucid Software Inc. (2015), ER Diagram Symbols and Meaning, Retrieved from
http://www.sparxsystems.com/enterprise_architect_user_guide/modeling_languages/
data_flow_diagrams.html.
Actor. FiielddlastName desc;
(Raghu Ramakrishnan, Johannes Gehrke, Jeff Derstadt, Scott Selicoff, Lin Zhu n.d.)
References
Raghu Ramakrishnan, Johannes Gehrke, Jeff Derstadt, Scott Selicoff, Lin Zhu,
Database Management Systems Solutions Manual IIIrd Edition
Lucid Software Inc. (2015), ER Diagram Symbols and Meaning, Retrieved from
http://www.sparxsystems.com/enterprise_architect_user_guide/modeling_languages/
data_flow_diagrams.html.
1 out of 15
Related Documents
![[object Object]](/_next/image/?url=%2F_next%2Fstatic%2Fmedia%2Flogo.6d15ce61.png&w=640&q=75){kind=small}
Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
![[object Object]](/_next/static/media/star-bottom.7253800d.svg)
Unlock your academic potential
© 2024 | Zucol Services PVT LTD | All rights reserved.