Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Based on the following tables given write an SQL query to retrieve the track number and composition title of all tracks on the cd titled

Based on the following tables given write an SQL query to retrieve the track number and composition title of all tracks on the cd titled "Big One". **do not encode in your query any information about which cd that is(make the query find it) image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
CREATE TABLE label / lbltitle VARCHAR (40), lblstreet VARCHAR (50), lblcity VARCHAR(30), lblstate VARCHAR(15), lblpostcode VARCHAR (12), lblnation VARCHAR(30), PRIMARY KEY (lbltitle)); CREATE TABLE cd ( cdid INTEGER, cdlblid VARCHAR(30), cdtitle VARCHAR(30), cdyear INTEGER, lbltitle VARCHAR (40), PRIMARY KEY (cdid), FOREIGN KEY (lbltitle) REFERENCES label (lbltitle)); CREATE TABLE composition ( compid INTEGER, comptitle VARCHAR(40), combyear INTEGER, PRIMARY KEY (compid)); CREATE TABLE recording rcdid INTEGER, rcdlength INTEGER, rcddate DATE, compid INTEGER, PRIMARY KEY (rcdid, compid), FOREIGN KEY (compid) REFERENCES composition (compid)); CREATE TABLE track cdid INTEGER, trknum. INTEGER, rcdid INTER CREATE TABLE recording rcdid INTEGER, rcdlength INTEGER, rcddate DATE, compid INTEGER, PRIMARY KEY (rcdid, compid), FOREIGN KEY (compid) REFERENCES composition (compid)); CREATE TABLE track ( cdid INTEGER, trknum INTEGER, rcdid INTEGER, compid INTEGER, PRIMARY KEY (trknum, cdid), FOREIGN KEY (cdid) REFERENCES cd (cdid), FOREIGN KEY (rcdid, compid) REFERENCES recording (rcdid, compid)); CREATE TABLE person panid INTEGER, panfname VARCHAR(40), psnlname VARCHAR(40), PRIMARY KEY (psnid)); CREATE TABLE person_recording ( ponrcdrole VARCHAR (25), psnid INTEGER, compid INTEGER rcdid INTEGER, PRIMARY KEY (psnredrole, panid, compid, rcdid), FOREIGN KEY (psnid) REFERENCES person (psnid) FOREIGN KEY (rcdid, compid) REFERENCES recording (rcdid, compid)): CREATE TABLE CREATE TABLE recording rcdid INTEGER, rcdlength INTEGER, rcddate DATE, compid INTEGER, PRIMARY KEY (rcdid, compid), FOREIGN KEY (compid) REFERENCES composition (compid)) CREATE TABLE track ( cdid INTEGER, trknum. INTEGER, rcdid INTEGER, compid INTEGER, PRIMARY KEY (trknum, cdid), EOREIGN KEY (cdid) REFERENCES cd (cdid), FOREIGN KEY (rcdid, compid) REFERENCES recording (rcdid, compid)); CREATE TABLE person panid INTEGER, psnfname VARCHAR(40), psnlname VARCHAR (40). PRIMARY KEY (psnid)); CREATE TABLE person_recording psnrcdrole VARCHAR (25), panid INTEGER, compid INTEGER rcdid INTEGER, PRIMARY KEY (psnredrole, panid, compid, rcdid), FOREIGN KEY (psnid) REFERENCES person (panid). FOREIGN KEY (rcdid, compid) REFERENCES recording (rcdid, compid)): CREATE TABLE penfname VARCHAR (40), psnlname VARCHAR(40), PRIMARY KEY (psnid)); CREATE TABLE person_recording psnrcdrole VARCHAR (25), psnid INTEGER, compid INTEGER, rcdid INTEGER, PRIMARY KEY (psnrcdrole, panid, compid, rcdid), FOREIGN KEY (psnid) REFERENCES person (psnid), FOREIGN KEY (rcdid, compid) REFERENCES recording (rcdid, compid)); CREATE TABLE person composition pancomprole VARCHAR(40), psncomporder INTEGER, psnid INTEGER, compid INTEGER, PRIMARY KEY (psncomprole, psnid, compid), FOREIGN KEY (psnid) REFERENCES person (psnid), FOREIGN KEY (compid) REFERENCES composition (compid)); CREATE TABLE person_cd psncdorder INTEGER, psnid INTEGER, cdid INTEGER, PRIMARY KEY (psnid, cdid), FOREIGN KEY (psnid) REFERENCES person (panid), FOREIGN KEY (coid) REFERENCES cd (cdid))

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

SQL Antipatterns Avoiding The Pitfalls Of Database Programming

Authors: Bill Karwin

1st Edition

1680508989, 978-1680508987

More Books

Students also viewed these Databases questions