TOPIC IS Design Methodology/ DATABASE systems Homework 5 – DDL Using the descrip
ID: 3802376 • Letter: T
Question
TOPIC IS Design Methodology/ DATABASE systems
Homework 5 – DDL
Using the description below write the DDL to create the definitions necessary.
Modify the above DDL to be able to show all of the albums that have a specific song on it.
ALBUMS
Cat#
Title
Artist #
Music Category
Record Label
EK-38308
Greatest Hits
25
Rock
Full Moon
LPM-2256
GI Blues
10
Rock
Epic
LSP-246
Blue Hawaii
10
Rock
Epic
M16014
Surfin Safari
20
Rock
RCA
7499-2256
Between the Buttons
15
Rock
Capitol
945024-2
Unplugged
30
Rock
Reprise
ARTISTS
Artist#
Artist Name
Yr. Born
Yr. Died
10
Elvis Presley
1935
1977
15
Rolling Stones
1943
20
Beach Boys
1942
25
Dan Fogelberg
1951
30
Eric Clapton
1945
TRACKS
Cat#
Track#
TrackTitle
TrackLength
TrackSample
EK-38308
1
Part of the Plan
3:30
partplan.mp3
EK-38308
2
Heart Hotel
6:00
hearthotel.mp3
EK-38308
3
Hard to Say
5:33
hardtosay.mp3
LPM-2256
1
Blue Suede Shoes
1:04
bluesuede.mp3
LPM-2256
2
Franfort Special
4:33
franfspec.mp3
LPM-2256
3
Wooden Heart
3:45
woodheart.mp3
7499-2256
1
RubyTuesday
4:33
rubytues.mp3
7499-2256
2
Power of Gold
3:44
powergold.mp3
945024-2
1
hey hey
5:33
heyhey.mp3
945024-2
2
layla
6:30
layla.mp3
945024-2
5
alberta
3:33
alberta.mp3
Cat#
Title
Artist #
Music Category
Record Label
EK-38308
Greatest Hits
25
Rock
Full Moon
LPM-2256
GI Blues
10
Rock
Epic
LSP-246
Blue Hawaii
10
Rock
Epic
M16014
Surfin Safari
20
Rock
RCA
7499-2256
Between the Buttons
15
Rock
Capitol
945024-2
Unplugged
30
Rock
Reprise
Explanation / Answer
For retrieving all the albums which have a particular track in them, i am assuming below:
1. Table names are ALBUMS and TRACKS
2. Tracks table Cat# is related with Albums table through column Cat# in Albums table.
To achieve the above, we will first try to find all the categories which contains this track.
Query for that would be: select Cat from TRACKS where TrackTitle like '%<Give track title>%'
Now based on these Categories, we will search on Albums table, and whoever album has the same category, that will be the part of the result.
Hence overall Query:
select * from ALBUMS where Cat in (
select Cat from TRACKS where TrackTitle like '%<Give track title>%'
)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.