ProductsLogo
LogoStudy Documents
LogoAI Grader
LogoAI Answer
LogoAI Code Checker
LogoPlagiarism Checker
LogoAI Paraphraser
LogoAI Quiz
LogoAI Detector
PricingBlogAbout Us
logo

SQL Queries for Movie Database

Verified

Added on  2020/05/08

|15
|4119
|238
AI Summary
This assignment consists of 12 SQL queries designed to manipulate and retrieve data from a movie database. Tasks range from identifying missing genres to updating movie titles and calculating sale prices. The assignment also involves retrieving information about actors, directors, and movies using joins, as well as demonstrating transactional operations.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
Assignment 2
ITECH 1006 – Database Management Systems
Student ID –
Student Name -
Date –

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
Report
Task 1
Relational Schema
1. GENRE
Attribute Datetype Key
FidgenreID INTEGER PRIMARY KEY
FidgenreName TEXT(50)
FidDescription TEXT(250)
2. SUPPLIER
Attribute Datetype Key
FidsupplierID INTEGER PRIMARY KEY
FidsupplierName TEXT(50)
FidContact TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(50)
FidSuburb TEXT(50)
FidPostcode TEXT(5)
FidCountry TEXT(50)
FidComments TEXT(250)
3. ACTOR
Attribute Datetype Key
FidactorID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPlaceOfBirth TEXT(100)
FidDateOfBirth DATE
FidDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY
Fidtitle TEXT(250)
FidunitPrice DECIMAL(5,2)
Fidmarkup DECIMAL(5,2)
Fiddiscount DECIMAL(5,2)
FidqtyInStock INTEGER
FidgenreID INTEGER FOREIGN KEY
FidsupplierID INTEGER FOREIGN KEY
Document Page
5. MANUFACTURER
Attribute Datetype Key
FidmanufacturerID INTEGER PRIMARY KEY
FidmanufacturerName TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(50)
FidSuburb TEXT(50)
FidPostcode TEXT(5)
FidCountry TEXT(50)
6. DIRECTOR
Attribute Datetype Key
FiddirectorID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPlaceOfBirth TEXT(100)
FidDateOfBirth DATE
FidDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FidcollectionID INTEGER PRIMARY KEY
FidcollectionName TEXT(50)
FidDescription TEXT(250)
FiddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FidmadeYear INTEGER
FidmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FidcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
Document Page
10. MOVIE
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FidreleaseYear INTEGER
Fidclassification TEXT(5)
FidstarRating INTEGER
FiddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FidactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FidinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fidrole TEXT(50)
12. CUSTOMER
Attribute Datetype Key
FidcustomerID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(100)
FidPostcode INTEGER
FidloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FidreceiptID INTEGER PRIMARY KEY
FidcustomerID INTEGER FOREIGN KEY
FidreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FidreceiptID INTEGER PRIMARY KEY
FidinventoryID INTEGER FOREIGN KEY
Fidquantity INTEGER
FidamountPaid DECIMAL(5,2)
(Adrienne Watt n.d.)
(teach-ict.com n.d.)

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
Task 2
Create Statements
DROP Database IF EXISTS Startstruck_Entertainment_studentid;
create database Startstruck_Entertainment_studentid;
use Startstruck_Entertainment_studentid;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
FidgenreID int NOT NULL AUTO_INCREMENT,
FidgenreName TEXT(50) NOT NULL,
FidDescription TEXT(250) NULL,
PRIMARY KEY (FidgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FidsupplierID int NOT NULL AUTO_INCREMENT,
FidsupplierName TEXT(50) NOT NULL,
FidContactName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(50) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode TEXT(5) NOT NULL,
FidCountry TEXT(50) NOT NULL,
FidComments TEXT(250) NULL,
PRIMARY KEY (FidsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FidactorID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPlaceOfBirth TEXT(100) NOT NULL,
FidDateOfBirth Date NOT NULL,
FidDateOfDeath Date NULL,
PRIMARY KEY (FidactorID)
);
DROP TABLE IF EXISTS INVENTORY;
Document Page
CREATE TABLE INVENTORY (
FidinventoryID int NOT NULL AUTO_INCREMENT,
Fidtitle TEXT(250) NOT NULL,
FidunitPrice decimal(5,2) NOT NULL,
Fidmarkup decimal(5,2) NOT NULL,
Fiddiscount decimal(5,2) NOT NULL,
FidqtyInStock int NOT NULL,
FidgenreID int NOT NULL,
FidsupplierID int NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidgenreID)
REFERENCES GENRE(FidgenreID),
FOREIGN KEY (FidsupplierID)
REFERENCES SUPPLIER(FidsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FidmanufacturerID int NOT NULL AUTO_INCREMENT,
FidmanufacturerName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(100) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode int NOT NULL,
FidCountry TEXT(50) NULL,
PRIMARY KEY (FidmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FiddirectorID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPlaceOfBirth TEXT(100) NOT NULL,
FidDateOfBirth Date NOT NULL,
FidDateOfDeath Date NULL,
PRIMARY KEY (FiddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FidcollectionID int NOT NULL AUTO_INCREMENT,
FidcollectionName TEXT(50) NOT NULL,
FidDescription TEXT(250) NOT NULL,
FiddateReleased Date NOT NULL,
Document Page
PRIMARY KEY (FidcollectionID)
);
DROP TABLE IF EXISTS COLLECTIBLE;
CREATE TABLE COLLECTIBLE (
FidinventoryID int NOT NULL,
FidmadeYear int NOT NULL,
FidmanufacturerID int NOT NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FidmanufacturerID)
REFERENCES MANUFACTURER(FidmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FidinventoryID int NOT NULL,
FidcollectionID int NOT NULL,
PRIMARY KEY (FidinventoryID, FidcollectionID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FidcollectionID)
REFERENCES COLLECTION(FidcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FidinventoryID int NOT NULL,
FidreleaseYear int NOT NULL,
Fidclassification TEXT(5) NOT NULL,
FidstarRating int NOT NULL,
FiddirectorID int NOT NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FiddirectorID)
REFERENCES DIRECTOR(FiddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FidactorID int NOT NULL,
FidinventID int NOT NULL,
Fidrole TEXT(50) NOT NULL,
PRIMARY KEY (FidactorID,FidinventID),

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
FOREIGN KEY (FidinventID)
REFERENCES MOVIE(FidinventoryID),
FOREIGN KEY (FidactorID)
REFERENCES ACTOR(FidactorID)
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FidcustomerID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(100) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode int NOT NULL,
FidloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FidcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FidreceiptID int NOT NULL AUTO_INCREMENT,
FidcustomerID int NOT NULL,
FidreceiptDate date NOT NULL,
PRIMARY KEY (FidreceiptID),
FOREIGN KEY (FidcustomerID)
REFERENCES CUSTOMER(FidcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FidreceiptID int NOT NULL,
FidinventoryID int NOT NULL,
Fidquantity int NOT NULL,
FidamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FidreceiptID),
FOREIGN KEY (FidreceiptID)
REFERENCES RECEIPT(FidreceiptID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID)
);
Document Page
Task 3
Insert Statements
use Startstruck_Entertainment_studentid;
INSERT INTO GENRE (FidgenreName, FidDescription) 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 (FidsupplierName, FidContactName, FidPhone,
FidEmail, FidAddress, FidSuburb, FidPostcode, FidCountry, FidComments) 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","GlenFid",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor ButterFid","+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 (FidFirstName, FidLastName, FidPlaceOfBirth, FidDateOfBirth,
FidDateOfDeath) 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 (FidFirstName, FidLastName, FidPlaceOfBirth, FidDateOfBirth)
values
Document Page
("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 (FidfirstName, FidlastName, FidPlaceOfBirth,
FidDateOfBirth) 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 (FidfirstName, FidlastName, FidPlaceOfBirth,
FidDateOfBirth, FidDateOfDeath) 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 (FidmanufacturerName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode, FidCountry) 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"),
("Modelworks", "+1456123789", "info@modelworks.com","12 Danshui
Street","Zhongzheng", 1020, "Taiwan"),

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
("Rockstar", "+1451586604", "info@rockstarfigures.com","40 Grand
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (FidfirstName, FidlastName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode) 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 (FidfirstName, FidlastName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode, FidloyaltyDiscount ) 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 (Fidtitle, FidunitPrice, Fidmarkup, Fiddiscount,
FidqtyInStock, FidgenreID, FidsupplierID) 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 (FidinventoryID,FidreleaseYear, Fidclassification, FidstarRating,
FiddirectorID) values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
insert into COLLECTIBLE (FidinventoryID, FidmadeYear, FidmanufacturerID)
values
(1,2016,1),
Document Page
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FidcollectionName, FidDescription, FiddateReleased)
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 (FidactorID, FidinventID, Fidrole) 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_studentid;
/* Query a */
Document Page
select * from Supplier order by FidSuburb;
/* Query b */
Select Fidtitle, FidsupplierName, FidEmail, FidPhone, FidSuburb from Inventory
inner join Movie
on Inventory. FidinventoryID= Movie. FidinventoryID
inner join Supplier on Inventory. FidsupplierID= Supplier. FidsupplierID;
/* Query c */
select CONCAT( FidfirstName," " , FidlastName) as 'ActorName',
YEAR(now()) - YEAR( FidDateOfBirth) - (DATE_FORMAT(now(), '%m%d') <
DATE_FORMAT( FidDateOfBirth, '%m%d')) as ActorAge
from Actor where FidDateOfDeath is null;
/* Query d */
Select Inventory. Fidtitle, FidgenreName from Inventory inner join Genre
on Inventory. FidgenreID= Genre. FidgenreID order by Fidtitle desc;
/* Query e */
select Fidtitle, FidunitPrice, Fidmarkup, FidqtyInStock, FidsupplierName,
FidmanufacturerName
from COLLECTIBLE inner join inventory on COLLECTIBLE. FidinventoryID=
inventory. FidinventoryID
inner join SUPPLIER on SUPPLIER. FidsupplierID= inventory. FidsupplierID
inner join manufacturer on manufacturer. FidmanufacturerID= COLLECTIBLE.
FidmanufacturerID
order by Fidtitle;
/* Query f */
Select FidgenreName from Genre where FidgenreID not in (select FidgenreID
from Inventory);
/* Query g */
SET SQL_SAFE_UPDATES=0;
delete from customer where FidPostcode=3353;
/* Query h */
SET SQL_SAFE_UPDATES=0;
update Inventory set Fidtitle="Wookie Figure – live size" where Fidtitle="Wookie
Figure";
/* Query i */

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
select inventory. Fidtitle, CONCAT( ACTOR. FidfirstName," " , ACTOR.
FidlastName) as 'ActorName', CONCAT( DIRECTOR. FidfirstName," " ,
DIRECTOR. FidlastName) as 'DirectorName'
from MOVIE inner join inventory on MOVIE. FidinventoryID= inventory.
FidinventoryID
inner join DIRECTOR on MOVIE. FiddirectorID= DIRECTOR. FiddirectorID
inner join CAST on MOVIE. FidinventoryID= CAST. FidinventID
inner join ACTOR on CAST. FidactorID= ACTOR. FidactorID;
/* Query j */
SET autocommit=0;
START TRANSACTION;
insert into DIRECTOR ( FiddirectorID, FidfirstName, FidlastName,
FidPlaceOfBirth, FidDateOfBirth) values
(9, "Steven", "Soderberghin", "Atlanta", STR_TO_DATE("01-14-1963","%m-%d-
%Y"));
insert into INVENTORY ( FidinventoryID, Fidtitle, FidunitPrice, Fidmarkup,
Fiddiscount, FidqtyInStock, FidgenreID) values
(10, "Solaris", 18,35,10,27,4);
insert into MOVIE ( FidinventoryID, FidreleaseYear, Fidclassification,
FidstarRating, FiddirectorID) values
(10,2002,'M',4,9);
COMMIT;
/* Query k */
select Fidtitle, FidqtyInStock, ( FidunitPrice + Fidmarkup) as 'SalePrice' from
inventory;
/* Query l */
Select Actor. FidfirstName, Actor. FidlastName, Fidtitle, FidgenreName
from Actor inner join Cast on Actor. FidActorID= Cast. FidActorID
inner join Inventory on Inventory. FidinventoryID= Cast. FidinventID
inner join Genre on Inventory. FidgenreID= Genre. FidgenreID order by Actor.
FidlastName desc;
References
Adrienne Watt (n.d.), Chapter 11 Functional Dependencies, Retrieved from
Document Page
http://opentextbc.ca/dbdesign/chapter/chapter-11-functional-dependencies/
teach-ict.com (n.d.), Third Normal Form, Retrieved from
http://www.teach-ict.com/as_a2_ict_new/ocr/
AS_G061/315_database_concepts/normalisation/miniweb/pg9.htm
1 out of 15
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]