Bands and albums

You have to crate a database which can record information about bands and their albums. Design tables and their relationship!

Bands

Field	Type	Constraints	Description
band_id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier for the band
name	VARCHAR(100)	NOT NULL, UNIQUE	Name of the band
genre	VARCHAR(50)	NULL allowed	Musical genre
formed_year	YEAR	NULL allowed	The year the band was formed
country	VARCHAR(50)	NULL allowed	Country of origin

CREATE TABLE bands (
  band_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  genre VARCHAR(50),
  formed_year YEAR,
  country VARCHAR(50)
);

Albums

Field	Type	Constraints	Description
album_id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier for the album
title	VARCHAR(100)	NOT NULL	Title of the album
release_year	YEAR	NULL allowed	The year the album was released
band_id	INT	NOT NULL, FOREIGN KEY → bands(band_id) ON DELETE CASCADE ON UPDATE CASCADE	The band that released the album

CREATE TABLE albums (
  album_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  release_year YEAR,
  band_id INT NOT NULL,
  FOREIGN KEY (band_id) REFERENCES bands(band_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

Field	Type	Constraints	Description
song_id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier for the song
title	VARCHAR(100)	NOT NULL	Title of the song
duration	TIME	NOT NULL	Duration of the song
album_id	INT	NOT NULL, FOREIGN KEY → albums(album_id) ON DELETE CASCADE ON UPDATE CASCADE	The album the song belongs to

CREATE TABLE songs (
  song_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  duration TIME NOT NULL,
  album_id INT NOT NULL,
  FOREIGN KEY (album_id) REFERENCES albums(album_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

Bands

band_id	name	genre	formed_year	country
1	Radiohead	Alternative Rock	1985	United Kingdom
2	Metallica	Heavy Metal	1981	United States
3	Sigur Rós	Post-Rock	1994	Iceland

INSERT INTO bands (name, genre, formed_year, country)
VALUES
  ('Radiohead', 'Alternative Rock', 1985, 'United Kingdom'),
  ('Metallica', 'Heavy Metal', 1981, 'United States'),
  ('Sigur Rós', 'Post-Rock', 1994, 'Iceland');

Albums

album_id	title	release_year	band_id
1	OK Computer	1997	1
2	Master of Puppets	1986	2
3	Takk...	2005	3

INSERT INTO albums (title, release_year, band_id)
VALUES
  ('OK Computer', 1997, 1),
  ('Master of Puppets', 1986, 2),
  ('Takk...', 2005, 3);

Songs

song_id	title	duration	album_id
1	Paranoid Android	00:06:23	1
2	Battery	00:05:12	2
3	Glósóli	00:06:15	3

INSERT INTO songs (title, duration, album_id)
VALUES
  ('Paranoid Android', '00:06:23', 1),
  ('Battery', '00:05:12', 2),
  ('Glósóli', '00:06:15', 3);

Question 1: Which bands are in the database?

SELECT name FROM bands;

Question 2: Which albums were released after 2000?

SELECT title, release_year 
FROM albums 
WHERE release_year > 2000;

Question 3: Which songs are longer than 6 minutes?

SELECT title, duration FROM songs WHERE duration > '00:06:00';

Question 4: Update the release year of the “Master of Puppets” album to 1985.

UPDATE albums
SET release_year = 1985
WHERE title = 'Master of Puppets';

Question 5: Which bands released which albums?

SELECT 
    b.name AS band_name, 
    a.title AS album_title
FROM bands b
JOIN albums a ON b.band_id = a.band_id;

Question 6: Which albums contain which songs?

SELECT 
  a.title AS album_title, 
  s.title AS song_title
FROM albums a
JOIN songs s ON a.album_id = s.album_id;

Question 7: How many albums has each band released?

SELECT 
  b.name AS band_name, 
  COUNT(a.album_id) AS album_count
FROM bands b
LEFT JOIN albums a ON b.band_id = a.band_id
GROUP BY b.name;

Question 8: Delete the song titled “Battery” from the database.

DELETE FROM songs
WHERE title = 'Battery';