Your task is to create a single .sql script that contains the SQL queries that produce the output for the following 5 questions.
- Download this script: Assignment8.sql download.
- After reading the queries that you need to write, you may need to edit the INSERT statements as necessary to ensure you have adequate data.
- Add the SELECT queries for this assignment to the script.
- Run your copy of the script in yourindividualMySQL database. Do not use your team database where you already have different tables with the same names!
Do not hard code! In other words, do not use specific IDs in your WHERE instead of writing a query that works for any data added that might be added to the tables. Don't just look at the data, figure out the answer and then print that specific name or ID
Whenever you're asked for an artist's full name, combine the parts of their name into one value - i.e. if fname = "Jane" and lname = "Doe", return "Jane Doe".
Question 1
For each artist who is currently in a band and either is under 30 or was born after 2000:
Return the artist’s full name, gender, date of birth (formatted like "06/12/2019"), and band name.
Order the results by band name
Question 2
For all artists who are currently in a band:
Return the artist’s full name (as a single value), the band name, and how long in years the artist was in the band. The artist should only be shown once for each band.
(Hint: Remember that the data might include an artist who left a band and then rejoined. That is the challenge here!)
Question 3
For each band that does not currently have a female member:
Return the name of the band and each artist's full name (as a single value).
Order by band name and artist name.
(Hint: use a subquery.)
Question 4
Find the album title, band name, format, and total length of all albums.
Order from longest to shortest.
Try to return the format as the word "single" or "double" rather than just the store value "s" or "d".
Question 5
For all non-male artists who are currently in more than one band:
Return the artist's full name (as a single value), the names of the bands that they are in, and all albums made by that band along with the year made.
Order by band from A-Z and within each band order the albums by the year they were made.
sql code
CREATE TABLE artist (
id INT NOT NULL,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30),
dob DATE,
hometown VARCHAR(40),
gender CHAR(1),
PRIMARY KEY (id)
) ENGINE = innodb;
CREATE TABLE band (
id INT NOT NULL,
title VARCHAR(50) NOT NULL,
year_formed YEAR,
PRIMARY KEY (id)
) ENGINE = innodb;
CREATE TABLE member (
artist_id INT NOT NULL,
band_id INT NOT NULL,
joined_date DATE,
leave_date DATE,
FOREIGN KEY (artist_id) REFERENCES artist(id),
FOREIGN KEY (band_id) REFERENCES band(id)
) ENGINE = innodb;
CREATE TABLE album (
id INT auto_increment,
pub_year YEAR,
title VARCHAR(50),
publisher VARCHAR(50),
format CHAR(1),
band_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (band_id) REFERENCES band(id)
) ENGINE = innodb;
CREATE TABLE song (
album_id INT NOT NULL,
name VARCHAR(30) NOT NULL,
length TIME,
FOREIGN KEY (album_id) REFERENCES album(id)
) ENGINE = innodb;