Return how many artists are currently in the band, how many artists have ever been in the band (be careful not to count the same artists multiple times if they've left and rejoined) and the number of albums the band has made. (This is tricky! There are a number of ways you can approach this. Order by band from A-Z. Attached are tables and their values.
Return how many artists are currently in the band, how many artists have ever been in the band (be careful not to count the same artists multiple times if they've left and rejoined) and the number of albums the band has made. (This is tricky! There are a number of ways you can approach this. Order by band from A-Z. Attached are tables and their values.
Chapter5: Working With Excel Tables, Pivottables, And Pivotcharts
Section: Chapter Questions
Problem 10RA
Related questions
Question
Return how many artists are currently in the band, how many artists have ever been in the band (be careful not to count the same artists multiple times if they've left and rejoined) and the number of albums the band has made. (This is tricky! There are a number of ways you can approach this.
Order by band from A-Z.
Attached are tables and their values.
Transcribed Image Text:assignment8.sql x
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
/* Create all tables. Order matters due to foreign keys. */
CREATE TABLE p_artist (
id INT AUTO INCREMENT,
fname VARCHAR (30) NOT NULL,
1name VARCHAR (30),
dob DATE,
hometown VARCHAR (40),
gender CHAR (1),
PRIMARY KEY (id)
L) ENGINE = innodb;
CREATE TABLE p_band (1
id INT AUTO INCREMENT,
title VARCHAR (50) NOT NULL,
year_formed YEAR,
PRIMARY KEY (id)
-) ENGINE = innodb;
CREATE TABLE p member (
artist_id INT NOT NULL,
band_id INT NOT NULL,
pard
joined date DATE,
leave_date DATE,
FOREIGN KEY (artist_id) REFERENCES p_artist (id),
FOREIGN KEY (band_id) REFERENCES p_band (id)
-) ENGINE = innodb;
CREATE TABLE p_album (
id INT not null,
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 p_band (id)
-) ENGINE = innodb;
CREATE TABLE p song (
album_id INT NOT NULL,
name VARCHAR(30) NOT NULL,
length TIME,
FOREIGN KEY (album_id) REFERENCES p_album (id)
-) ENGINE = innodb;
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr