mySQL

---NEED HELP WITH PROBLEM #5 AND #6 I provided everything you need for the database I am just not reciving the correct output. comments are shown by the symbol "//" what i have so far for #5 and #6 is shown all the way below---

TASK:

5.

Return the artists full name and age and the band name of all female

artists who are in a band that has more than one album. Order by band

name and artist name. Return two or more female artists. (Hint: use a

subquery.)

6.

Return the artists full name (as a single value), the band name, and how

long in years the artist was in the band for all artists who are or ever

have been in bands. Include artists currently in bands and those who are

no longer in the band. The artist should only be shown once for each

band. (Hint: the artist that left and rejoined is the challenge.)

LAB 5

//CREATING TABLES

CREATE TABLE artist

(

aid int auto_increment,

fname varchar(30) not null,

lname varchar(30) not null,

dob date not null,

gender char(1),

primary key(aid)

)

engine = innodb;

CREATE TABLE band

(

bid int auto_increment,

name varchar(30) not null,

year_formed date not null,

primary key(bid)

)

engine = innodb;

CREATE TABLE in_band

(

bid int,

aid int,

date_in date not null,

date_out date not null,

FOREIGN KEY(bid) REFERENCES band(bid),

FOREIGN KEY(aid) REFERENCES artist(aid)

)

engine = innodb;

CREATE TABLE album

(

albumid int auto_increment,

bid int,

published_year date not null,

title varchar(30) not null,

price decimal,

publisher varchar(30) not null,

format varchar(30),

FOREIGN KEY(bid) REFERENCES band(bid),

primary key(albumid)

)

engine = innodb;

// INSERTING DATA

Insert into artist(fname,lname,dob, gender)

Values('Tommy', 'DeNardo', '1995-10-25','M'),

('Luis','Lopez','1995-07-13','M'),

('Mary','Jane','1969-06-09','F'),

('Suzy','Smith','1921-05-15','F'),

('Jane','Doe','1945-12-25','F')

(Tanner','Smith','1991-07-03','M')

;

Insert into band(name,year_formed)

Values

('Grungefest', '1995-10-25'),

('Apple Cider', '1999-11-12'),

('Team Cinco', '2018-05-29')

;

//GRUNGEFEST MEMBERS

//TOMMY JOINS AND LEAVES

insert into in_band(aid, bid, date_in, date_out)

values

(1,1,'1995-10-25','1995-10-26');

//TOMMY RETURNS

insert into in_band(aid, bid, date_in)

values

(1,1,'2001-10-26');

//TANNER JOINS

insert into in_band(aid, bid, date_in)

values

(7,1,'1995-10-25');

//LUIS JOINS

insert into in_band(aid, bid, date_in)

values

(2,1,'1995-10-25');

//LUIS JOINS another band

insert into in_band(aid, bid, date_in)

values

(2,3,'2018-05-30');

//APPLE CIDER

insert into in_band(aid, bid, date_in)

values

(3,2,'1999-11-12'), (4,2,'1999-11-12'), (5,2,'1999-11-12');

//ALBUMS

How to change data type: Alter table album modify published_year int(4);

//GrungeFest

Insert into album(bid, published_year, title, publisher, format, price)

Values

(1, '1996', 'Grunge Up the Beat', 'Secretly','Vinyl','299.95'),

(1, '2000', 'Grunge Juice', 'Secretly','Vinyl','99.95'),

(1, '2008', 'Grunge Up, Get Down', 'Secretly','Vinyl','29.95'),

(1, '2018', 'Momma Stole My Grunge', 'Secretly','Digital','9.95');

//AppleCider

Insert into album(bid, published_year, title, publisher, format, price)

Values

(2, '1999', 'How Ya Like Them Apples?', 'Def Jam', 'Cassette','79.95'),

(2, '2001', 'Not Far From The Tree', 'Def Jam', 'Vinyl', '39.95'),

(2, '2013', 'Keeping The Doctor Away', 'Def Jam', 'USB Drive', '29.95'),

(2, '2017', 'Rotten', 'Def Jam','CD', '19.95');

//Team Cinco

Insert into album(bid, published_year, title, publisher, format, price)

Values

(3, '2000', 'Cuatro de Mayo Ms Uno', 'Death Row', 'CD','505.05');

(changing/updating title of album in the database)

Update album set title='WHATEVER' where bid=3;

HOMEWORK

Tommy: 1-2

Tanner: 3-4

Luis: 5-6

//PROBLE

Return the artists full name, gender, date of birth (formatted as Mon Day YYYY), and band name where the artist is either female or is older than 21; the artist(s) listed must currently be in the band. You should return at least two artists, including at least one female and one over 21 non-female member. Make sure your data proves your OR works!

convert(varchar(10), cast(ts as date), 101) from

//PROBLEM 3

SELECT name, gender, dob, convert(date, FROM WHERE

4.Return the names of all bands that do not currently have a female member. Return at least one band. (Hint: use a subquery.)

//PROBLEM 4 --- need help

SELECT gender, name,

FROM artist AS a, band AS b

WHERE b.name NOT IN (

SELECT a.gender,

FROM artist AS a,

WHERE a.id = b.id AND a.gender = 'F'

);

//ERROR IN THIS ONE #5

SELECT DISTINCT gender , fname, lname, name DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)),'%y')+0 AS age

FROM artist AS a, band AS b, album AS al

WHERE (gender = 'F' AND a.aid = b.bid AND al.albumid > 2 )

GROUP BY fname;

//POSSIBLY CORRECT #5

SELECT DISTINCT gender , fname, lname, name,

DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)),'%y')+0 AS age

FROM artist AS a, band AS b, in_band as ib

WHERE (gender = 'F' AND b.bid = ib.bid)

GROUP BY gender, fname, lname;

DATE_FORMAT((CURDATE() - dob), '%y')+0 AS age

//Problem #6

SELECT DISTINCT name, CONCAT(fname, ' ', lname) As FullName,date_in, date_out,

YEAR(date_out) - YEAR(date_in) - (DATE_FORMAT(date_out, '%m%d') < DATE_FORMAT(date_in, '%m%d')) as diff_years

FROM artist AS a, in_band AS i, band AS b

WHERE b.bid = a.aid

GROUP BY name, FullName, diff_years;