CREATE TABLE courses 
	(course TEXT PRIMARY KEY,
	 title TEXT,
	 credits, NUMERIC,
	 si CHAR(1));
	 
CREATE TABLE sections 
	(crn NUMERIC PRIMARY KEY,
	 course TEXT REFERENCES courses (course),
	 section NUMERIC,
	 days TEXT);

SELECT courses.course, courses.title FROM courses 
JOIN sections ON courses.course = sections.course 
WHERE sections.days = 'TTh';

CREATE DATABASE bands;
\c bands;

CREATE TABLE band
(
  band_id integer PRIMARY KEY,
  name text,
  genre text,
  formed integer
);

INSERT INTO band (band_id, name, genre, formed) VALUES (1, 'Baby Metal', 'heavy metal', 2010), 
                                         (2, 'Imagine Dragons', 'alternative rock', 2008), 
                                         (3, 'Sakura Gakuin', 'J-pop', 2010), 
                                         (4, 'Egyptian', 'alternative rock', 2010),
                                         (5, 'Earth Wind & Fire', 'funk', 1969);

CREATE TABLE musician
(
  musician_id integer PRIMARY KEY,
  stagename text,
  realname text,
  origin text,
  birthdate DATE
);

INSERT INTO musician (musician_id, stagename, realname, origin, birthdate) VALUES 
    (1, 'Su-Metal', 'Suzuka Nakamoto', 'Hiroshima Prefecture, Japan', '1997-12-20'), 
    (2, 'MoaMetal', 'Moa Kikuchi', '  Kanagawa Prefecture, Japan', '1999-7-4'),
    (3, 'YuiMetal', 'Yui Mizuno', 'Aichi Prefecture, Japan', '1999-06-20'), 
    (4, 'Aiko Yamaide', 'Aiko Yamaide', 'Kagoshima Prefecture, Japan', '2002-12-01'),
    (5, 'Mariri Sugimoto', 'Mariri Sugimoto', 'Hiroshima Prefecture, Japan', '2000-08-04'), 
    (6, 'Hana Taguchi', 'Hana Taguchi', 'Nagano Prefecture, Japan', '1999-7-4'),
    (7, 'Dan Reynolds', 'Daniel Reynolds', 'Las Vegas, Nevada, United States', '1987-07-14'), 
    (8, 'Wing', 'Daniel Wayne Sermon', '  American Fork, Utah, United States', NULL),
    (9, 'Aja Volkman-Reynolds', 'Aja Volkman-Reynolds', 'Eugene, OR', '1987-01-01'),
    (10, 'Daniel Platzman', 'Daniel Platzman', '  Atlanta, Georgia, United States', '1986-09-28'),
    (11, 'Ben McKee', 'Benjamin Arthur McKee', 'Forestville, California, United States', '1985-04-07');

create table bandmember (
  band_id integer REFERENCES band(band_id),
  member_id integer REFERENCES musician(musician_id),
  PRIMARY KEY (band_id, member_id));
 
INSERT INTO bandmember VALUES (1, 1), (1, 2), (1, 3), 
     (2, 7), (2, 8), (2, 10), (2, 11),
     (3, 1), (3, 2), (3, 3), (3, 4), (3, 5), (3, 6),
     (4, 7), (4, 9);

create table instrument (
  id integer PRIMARY KEY,
  name text,
  family text
  
  );

insert into instrument (id, name, family) VALUES (1, 'vocals', 'voice'), 
   (2, 'screams', 'voice'), (3, 'dance', 'dance'),
   (4, 'koto', 'string'), (5, 'electric guitar', 'string'), (6, 'drums', 'percussion'), (7, 'bass', 'string'), (8, 'keyboards', 'keyboards');

create table plays (
  member_id integer REFERENCES musician(musician_id),
  instrument_id integer REFERENCES instrument(id),
  PRIMARY KEY (member_id, instrument_id));
 
insert into plays VALUES (1, 1), (1, 3), (2, 2), (2, 3), (3, 2), (3,3), 
   (7, 1), (7, 8), (8, 5), (9, 1), (9,8), (10, 6), (10,1), (11, 7); 

SELECT musician.stagename FROM musician 
JOIN bandmember ON 
musician.musician_id = bandmember.member_id 
JOIN band ON 
bandmember.band_id = band.band_id 
WHERE band.name = 'Imagine Dragons';

stagename    
-----------------
 	Dan Reynolds
 	Wing
 	Daniel Platzman
 	Ben McKee
(4 rows)

SELECT instrument.name FROM instrument 
JOIN plays ON 
instrument.id = plays.instrument_id 
JOIN musician  ON 
musician.musician_id = plays.member_id 
WHERE musician.stagename = 'Ben McKee';

CREATE TABLE friends (
person INTEGER REFERENCES musician(musician_id),
friends_with INTEGER REFERENCES musician(musician_id));

 INSERT INTO friends (person, friends_with) VALUES
  (7, 8),
  (7, 9),
  (9, 11);   

SELECT musician.stagename FROM musician 
JOIN friends ON musician.musician_id = friends.person 
WHERE musician.stagename = 'Dan Reynolds';

SELECT m1.stagename FROM musician as m1 
JOIN friends ON m1.musician_id = friends.friends_with 
JOIN musician as m2 ON friends.person = m2.musician_id 
WHERE m2.stagename = 'Dan Reynolds';

       stagename       
 ----------------------
  Wing
  Aja Volkman-Reynolds
 (2 rows)

SELECT m1.stagename FROM musician as m1 
  JOIN bandmember AS b1 ON m1.musician_id = b1.member_id 
  JOIN bandmember as b2 ON b1.band_id = b2.band_id 
  JOIN musician AS m2 ON m2.musician_id = b2.member_id 
  WHERE m1.stagename <> 'Dan Reynolds' 
  AND m2.stagename = 'Dan Reynolds';