Lab 05 Topic: Intermediate SQL Title: Joy of Joining Marks: Total 100. Each question carries 10 marks. Consider the following relations. Table1: Bands # ID, name, formedin, country '1', 'Pink Floyd',...

1 answer below »
There are 10 questions, they're all MySQL subqueries, please use Left Join/Right Join for each question.


Lab 05 Topic: Intermediate SQL Title: Joy of Joining Marks: Total 100. Each question carries 10 marks. Consider the following relations. Table1: Bands # ID, name, formedin, country '1', 'Pink Floyd', '1965', 'UK' '2', 'king Crimson', '1969', 'UK' '3', 'Can', '1968', 'Germany' '4', 'Doors', '1967', 'USA' '5', 'Velvet Underground', '1967', 'USA' '6', 'Miles', '1980', 'BD' '7', 'LRB', '1990', 'BD' Table 2: Albums # aID, title, bandId, year '1', 'Meddle', '1', '1970' '2', 'Animala', '1', '1977' '3', 'Red', '2', '1974' '4', 'Landed', '3', '1975' '5', 'Soundtracks', '3', '1970' '6', 'Beat', '2', '1982' '7', 'The Doors', '4', '1967' '8', 'Strange Days', '4', '1967' '9', 'Loaded', '5', '1970' Table3: Musicians # mId, name, bandId, startYear, endYear '1', 'Roger Waters', '1', '1966', '1983' '2', 'Syd Barrett', '1', '1966', '1968' '3', 'Robert Fripp', '2', '1969', '2002' '4', 'Adrian Belew', '2', '1981', '2002' '5', 'Irmin Schmidt', '3', '1968', '1978' '6', 'Michael Karoli', '3', '1968', '1978' '7', 'Jim Morrison', '4', '1967', '1971' '8', 'Lou Reed', '5', '1967', '1973' Notes: · bandID is a foreign key on bands in both relations where this attribute appears. · Consider all attributes indicating years to be integers with appropriate comparison operators applicable. · In what follows we assume that if a musician is listed as being in the band during a year in which an album was recorded, the musician played on it. · First attribute of each relation is its primary key. Write SQL queries to get the following results. 1. Find out the bands that didn’t publish any album (you must use left join). 2. Find the names of the Albums that weren’t published by the band “Pink Floyd” (you must use right join). 3. Find all musicians who played for ‘‘Can’’. Output the names of the musicians and the years they played in the band. 4. Find all musicians who played in the band ‘‘Pink Floyd’’ in the year 1971. Output the names of the musicians. 5. Find all musicians who played in their band in the band’s year of inception. Output the names of the musicians and the names of the bands. 6. Find all musicians who participated in recording of the album ‘‘Meddle’’. Output the names of the musicians. 7. Find all bands in which ‘‘Irmin Schmidt’’ DID NOT play. Output the names of the bands. (you must use join). 8. Find all albums recorded by the bands who had at least one musician leave before 1972. Output the name of the album and the year of release. 9. Find all albums recorded by ‘‘King Crimson’’ before ‘‘Adrian Belew’’ joined the band. Report the album titles and years. 10. Find all albums on which ‘‘Lou Reed’’ played. Report the title of the album, the name of the band that recorded it and the year of the album’s release. 1 1
Answered Same DayOct 21, 2021

Answer To: Lab 05 Topic: Intermediate SQL Title: Joy of Joining Marks: Total 100. Each question carries 10...

Ali Asgar answered on Oct 22 2021
125 Votes
/************************
/*QUERY 1
************************/
SELECT B.name
FROM bands B LEFT JO
IN albums A
    ON B.ID = A.bandId
WHERE A.aID IS NULL;
/************************
/*QUERY 2
************************/
SELECT A.title
FROM (    SELECT ID FROM bands WHERE name = 'Pink Floyd' ) B RIGHT JOIN albums A ON B.ID = A.bandId
WHERE B.ID IS NULL;
/************************
/*QUERY 3
************************/
SELECT M.name, M.startYear, M.endYear
FROM musician M JOIN bands B
    ON M.bandId = B.ID
WHERE B.name = 'Can';
/************************
/*QUERY...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here