Hello, I am hoping to get help with this question. Can you help me formulate the

Question

Hello,

I am hoping to get help with this question. Can you help me formulate the following select statements in SQL?

Here are my tables:

And here is the project:

}Select one band and artists full name that were in the band during one years time i.e. 2005(you choose the year)
}Return only one band that you choose and has multiple artists for the year selected
}Select album title, band name, and artist(s) full name on that album for one album title that you choose
}Return the one band, album and one or more artist full names
}Select the artists fullname, gender, date of birth(formatted as Mon Day YYYY), and band name where the artist is (female or older than 21) and currently in the band
}Return 2 or more artist with at least one female and one older than 21
?

Hello, I am hoping to get help with this question. Can you help me formulate the following select statements in SQL? Here are my tables: And here is the project: }Select one band and artists full name that were in the band during one years time i.e. 2005(you choose the year) }Return only one band that you choose and has multiple artists for the year selected }Select album title, band name, and artist(s) full name on that album for one album title that you choose }Return the one band, album and one or more artist full names }Select the artists fullname, gender, date of birth(formatted as Mon Day YYYY), and band name where the artist is (female or older than 21) and currently in the band }Return 2 or more artist with at least one female and one older than 21 ?

Explanation / Answer

-- Select one band and artists full name that were in the band during one years time i.e. 2005(you choose the year)
SELECT b.name, ar.first_name, ar.last_name
FROM artist ar, in_band i, band b
WHERE ar.aid = i.aid
AND b.bid = i.bid
AND b.name = 'Beatles'
AND b.year_formed = 1960;

-- Return only one band that you choose and has multiple artists for the year selected
SELECT b.name, ar.first_name, ar.last_name, b.year_formed
FROM artist ar, in_band i, band b
WHERE ar.aid = i.aid
AND b.bid = i.bid
AND b.name = 'Queen'
AND b.year_formed = 1975;
  

-- Select album title, band name, and artist(s) full name on that album for one album title that you choose
SELECT a.title, ar.first_name, ar.last_name
FROM album a, artist ar, in_band i
WHERE a.bid = i.bid
AND ar.aid = i.aid
AND ar.first_name = 'John'
AND ar.last_name = 'Deacon';


-- Return the one band, album and one or more artist full names
SELECT b.name, a.title, ar.first_name, ar.last_name
FROM album a, artist ar, in_band i, band b
WHERE a.bid = i.bid
AND ar.aid = i.aid
AND b.bid = i.bid
AND ar.first_name = 'John'
AND ar.last_name = 'Deacon';

-- Select the artists fullname, gender, date of birth(formatted as Mon Day YYYY), and band name where
-- the artist is (female or older than 21) and currently in the band
SELECT ar.first_name, ar.last_name, ar.gender, DATE_FORMAT(ar.dob,'%b %d %Y'), b.name
FROM artist ar, in_band i, band b
WHERE ar.aid = i.aid
AND b.bid = i.bid
AND ar.gender = 'F'
AND (DATEDIFF(CURDATE(), ar.dob) / 365) > 21;

-- Return 2 or more artist with at least one female and one older than 21
SELECT *
FROM artist
WHERE gender = 'F'
AND (DATEDIFF(CURDATE(), ar.dob) / 365) > 21;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.