⭐ EXPERIMENT – 4
(SQL Table Creation, Insertions & Basic SQL Queries)
4.1 CREATE TABLES
Musicians
CREATE TABLE Musicians (
musicianID INT PRIMARY KEY,
name VARCHAR(50)
);
Instruments
CREATE TABLE Instruments (
instrumentID INT PRIMARY KEY,
name VARCHAR(50),
musicalKey VARCHAR(10)
);
Albums
CREATE TABLE Albums (
albumID INT PRIMARY KEY,
title VARCHAR(50),
copyrightDate DATE,
format VARCHAR(20)
);
Songs
CREATE TABLE Songs (
songID INT PRIMARY KEY,
title VARCHAR(50),
author VARCHAR(50),
albumID INT,
FOREIGN KEY (albumID) REFERENCES Albums(albumID)
);
Musician_Instruments
CREATE TABLE Musician_Instruments (
musicianID INT,
instrumentID INT,
PRIMARY KEY(musicianID, instrumentID),
FOREIGN KEY(musicianID) REFERENCES Musicians(musicianID),
FOREIGN KEY(instrumentID) REFERENCES Instruments(instrumentID)
);
Musician_Songs
CREATE TABLE Musician_Songs (
musicianID INT,
songID INT,
PRIMARY KEY(musicianID, songID),
FOREIGN KEY(musicianID) REFERENCES Musicians(musicianID),
FOREIGN KEY(songID) REFERENCES Songs(songID)
);
⭐ 4.2 INSERT 5 RECORDS INTO EACH TABLE
Musicians
INSERT INTO Musicians VALUES
(101,'John Smith'),
(102,'Alice Johnson'),
(103,'Michael Brown'),
(104,'Sarah Davis'),
(105,'David Wilson');
Instruments
INSERT INTO Instruments VALUES
(201,'Guitar','E'),
(202,'Piano','C'),
(203,'Drums','N/A'),
(204,'Violin','G'),
(205,'Bass','E');
Albums
INSERT INTO Albums VALUES
(301,'Summer Vibes','2021-06-01','CD'),
(302,'Night Groove','2022-03-15','Digital'),
(303,'Acoustic Dreams','2020-11-20','Vinyl');
Songs
INSERT INTO Songs VALUES
(401,'Sunny Days','John Smith',301),
(402,'Moonlight Dance','Alice Johnson',302),
(403,'Morning Breeze','Michael Brown',301),
(404,'Evening Stars','Sarah Davis',303),
(405,'Jazz Nights','David Wilson',302);
Musician_Instruments
INSERT INTO Musician_Instruments VALUES
(101,201),(101,205),
(102,202),(103,203),
(104,204),(105,201),(105,203);
Musician_Songs
INSERT INTO Musician_Songs VALUES
(101,401),(102,402),(103,403),
(104,404),(105,405),(101,403),(105,402);
⭐ 4.3 SQL QUERIES
1. Distinct musician names and instruments
SELECT DISTINCT [Link], [Link] AS instrument
FROM Musicians m
JOIN Musician_Instruments mi ON [Link] = [Link]
JOIN Instruments i ON [Link] = [Link];
2. Song names with album titles
SELECT [Link] AS song, [Link] AS album
FROM Songs s
JOIN Albums a ON [Link] = [Link];
3. Musicians who performed in “Summer Vibes”
SELECT DISTINCT [Link]
FROM Musicians m
JOIN Musician_Songs ms ON [Link] = [Link]
JOIN Songs s ON [Link] = [Link]
JOIN Albums a ON [Link] = [Link]
WHERE [Link] = 'Summer Vibes'
ORDER BY [Link];
4. IDs of musicians who play Guitar or Piano
SELECT DISTINCT musicianID
FROM Musician_Instruments
WHERE instrumentID IN (
SELECT instrumentID FROM Instruments
WHERE name IN ('Guitar','Piano')
);
⭐ EXPERIMENT – 5
(Nested Queries & Aggregate Functions)
Given musicians table with age.
1. Musicians who performed on song 403
SELECT name
FROM Musicians
WHERE musicianID IN (
SELECT musicianID FROM Musician_Songs WHERE songID = 403
);
2. Youngest musician (using MIN)
SELECT name, age
FROM Musicians
WHERE age = (SELECT MIN(age) FROM Musicians);
3. Musicians whose age > any musician named “Alice Johnson”
SELECT name, age
FROM Musicians
WHERE age > (
SELECT age FROM Musicians WHERE name='Alice Johnson'
);
4. Youngest musician (NO MIN, nested query)
SELECT name, age
FROM Musicians m
WHERE NOT EXISTS (
SELECT 1 FROM Musicians m2
WHERE [Link] < [Link]
);
5. Average age per instrument
SELECT [Link] AS instrument, AVG([Link]) AS avgAge
FROM Instruments i
JOIN Musician_Instruments mi ON [Link] = [Link]
JOIN Musicians m ON [Link] = [Link]
GROUP BY [Link], [Link];
6. Average age for instruments played by ≥ 2 musicians
SELECT [Link], AVG([Link])
FROM Instruments i
JOIN Musician_Instruments mi ON [Link] = [Link]
JOIN Musicians m ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) >= 2;
7. Musician with highest rating (no MAX)
SELECT name, rating
FROM Musicians m
WHERE NOT EXISTS (
SELECT 1 FROM Musicians m2 WHERE [Link] > [Link]
);
8. Musician with second highest rating
SELECT name, rating
FROM Musicians
WHERE rating = (
SELECT MAX(rating)
FROM Musicians
WHERE rating < (SELECT MAX(rating) FROM Musicians)
);
9. musicianID, name & song title (JOIN)
SELECT [Link], [Link], [Link]
FROM Musicians m
JOIN Musician_Songs ms ON [Link] = [Link]
JOIN Songs s ON [Link] = [Link];
10. Instruments where AVG age ≥ 30
SELECT [Link], AVG([Link]) AS avgAge
FROM Instruments i
JOIN Musician_Instruments mi ON [Link] = [Link]
JOIN Musicians m ON [Link] = [Link]
GROUP BY [Link]
HAVING AVG([Link]) >= 30;
⭐ EXPERIMENT – 6
(SQL JOIN QUERIES)
1. musician names, song titles, album titles
SELECT [Link], [Link], [Link] AS album
FROM Musicians m
JOIN Musician_Songs ms ON [Link] = [Link]
JOIN Songs s ON [Link] = [Link]
JOIN Albums a ON [Link] = [Link];
a) Musicians who performed songs from albums released after 2015
SELECT DISTINCT [Link]
FROM Musicians m
JOIN Musician_Songs ms ON [Link] = [Link]
JOIN Songs s ON [Link] = [Link]
JOIN Albums a ON [Link] = [Link]
WHERE YEAR([Link]) > 2015;
b) Album title & number of unique musicians
SELECT [Link], COUNT(DISTINCT [Link]) AS musicianCount
FROM Albums a
JOIN Songs s ON [Link] = [Link]
JOIN Musician_Songs ms ON [Link] = [Link]
GROUP BY [Link];
c) Musicians who performed more than 3 songs
SELECT [Link], COUNT([Link]) AS totalSongs
FROM Musicians m
JOIN Musician_Songs ms ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 3;
d) List all songs with musicians (LEFT JOIN)
SELECT [Link] AS song, [Link] AS musician
FROM Songs s
LEFT JOIN Musician_Songs ms ON [Link] = [Link]
LEFT JOIN Musicians m ON [Link] = [Link];
e) Albums with songs written by more than one author
SELECT [Link]
FROM Albums a
JOIN Songs s ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT(DISTINCT [Link]) > 1;
⭐ EXPERIMENT – 7
(Relational Algebra — but FIRST SQL as you requested)
⭐ 7.1 SQL QUERIES (Your Request)
1. Names of musicians who play the instrument “Guitar”
SELECT [Link]
FROM Musicians m
JOIN Musician_Instruments mi ON [Link] = [Link]
JOIN Instruments i ON [Link] = [Link]
WHERE [Link] = 'Guitar';
2. Musicians who play more than one instrument
SELECT [Link]
FROM Musicians m
JOIN Musician_Instruments mi ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 1;
3. Instrument names played by more than 5 musicians
SELECT [Link]
FROM Instruments i
JOIN Musician_Instruments mi ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 5;
⭐ 7.2 RELATIONAL ALGEBRA (Official Required Answers)
1. Names of musicians who play “Guitar”
π name (
Musicians ⨝ [Link] = Musician_Instruments.musicianID
⨝ Musician_Instruments.instrumentID = [Link]
⨝ σ name='Guitar' (Instruments)
)
2. Musicians who play more than one instrument
π name (
γ musicianID, name; COUNT(instrumentID)→instCount
(Musicians ⨝ Musician_Instruments)
σ instCount > 1
)
3. Instruments played by more than 5 musicians
π name (
γ instrumentID, COUNT(musicianID)→musCount (Musician_Instruments)
σ musCount > 5
⨝ Instruments
)