Genre Distribution of Songs

The assignment content consists of six tables: 'Country', 'Genre', 'Publisher', 'Song_at_youtube', 'songs', and 'song_sales'. The content also includes several SQL queries designed to extract specific data from the tables. Some of the queries are used to identify top-rated songs, low-rated songs, and genre distribution. Another query is used to find artists who were born after 1980.

Data Model
Create Statements
Table 1:
create table Publisher
(
Publisher_IDint(3),
Publisher_NameVarchar(20),
Location Varchar(20),
Primary Key (Publisher_ID)
);

Table 2:
create table Genre
(
Genre_IDint(2),
Genre_NameVarchar(14),
Primary Key (Genre_ID)
);
Table 3:
create table composer
(
Composer_IDint(8),
C_FnameVarchar(15),
C_LnameVarchar(15),
Primary Key (Composer_ID)
);
Table 4:
create table Artist
(
Artist_IDint(8),
A_FnameVarchar(15),
A_LnameVarchar(15),
Artist_DOB Date,
Primary Key (Artist_ID)
);

Table 5:
create table songs
(
song_IDint(5),
Song_Titlevarchar(20),
Date_of_release Date,
Genre_IDint(3),
Publisher_IDint(3),
Primary Key (song_ID),
FOREIGN KEY (Genre_ID) REFERENCES Genre(Genre_ID),
FOREIGN KEY (Publisher_ID) REFERENCES Publsiher(Publisher_ID)
);
Table 6:
create table Song_sales
(
Song_IDint(5),
yearrint (4),
record_soldint(10),
FOREIGN KEY (Song_ID) REFERENCES songs(Song_ID)
);
Table 7:
create table Song_at_youtube
(
Song_IDint(5),
Artist_IDint(3),
FOREIGN KEY (Song_ID) REFERENCES songs(Song_ID),
FOREIGN KEY (Artist_ID) REFERENCES artist(Artist_ID)
);

Insert Into Statements
Table 1:
insert into artist values
(100,"Adam","Levine", '1982-09-01');
insert into artist values
(101,"Taylor","Swift", '1989-11-16');
insert into artist values
(102,"Dan","Reynolds", '1984-04-19');
insert into artist values
(103,"Chester","Bennington", '1983-06-05');
insert into artist values
(104,"Frank","Sinatra", '1964-05-22');
insert into artist values
(105,"Paul","Sinatra", '1936-07-07');
insert into artist values
(106,"Ellie","Goulding", '1987-03-08');
insert into artist values
(107,"Sara","Tedder", '1984-02-21');
insert into artist values
(108,"Ryan","Baerily", '1982-10-16');

Table 2:
insert into composer values
(100000,"Adam", "Levine");
insert into composer values
(100001,"Taylor","Swift");
insert into composer values
(100002,"Dan", "Reynolds");
insert into composer values
(100003,"Chester", "Bennington");
insert into composer values
(100004,"Frank", "Sinatra");
insert into composer values
(100005,"Paul", "McCartney");
insert into composer values
(100006,"Ellie", "Reynolds");
insert into composer values
(100007,"Sara", "bariely");
insert into composer values
(100008,"Ingrid", "Michelson");

Table 3:
insert into Genre values
(10,"Alternative");
insert into Genre values
(11,"Country");
insert into Genre values
(12,"Pop");
insert into Genre values
(13,"Rock");
insert into Genre values
(14,"Blues");
insert into Genre values
(15,"HipHop");
insert into Genre values
(16,"Electronic");
insert into Genre values
(17,"R&B");
insert into Genre values
(18,"Soft_Rock");
Table 4:
insert into Publisher
(200,"Universal Records","San Diego");
insert into Publisher
(201,"B3 Music","California");

insert into Publisher
(202,"ABC Publishing","New York");
insert into Publisher
(203,"Alpha Major","California");
insert into Publisher
(204,"Katchy Sounds","Boston");
insert into Publisher
(205,"Manhattan Beats","Manhattan");
insert into Publisher
(206,"H Music","Houston");
insert into Publisher
(207,"Gill Tunes","Arizona");
insert into Publisher
(208,"Quark Inc.","New York");
Table 5:
insert into Song_at_youtube
(500,100);
insert into Song_at_youtube
(501,101);
insert into Song_at_youtube

(502,102);
insert into Song_at_youtube
(503,103);
insert into Song_at_youtube
(504,104);
insert into Song_at_youtube
(505,105);
insert into Song_at_youtube
(506,106);
insert into Song_at_youtube
(507,107);
insert into Song_at_youtube
(508,108);
Table 6:
insert into songs values
(1000,"Counting Stars",'2013-01-03',31,205,108);
insert into songs values
(1001,"Blank Space",'2014-10-06',36,202,101);
insert into songs values
(1002,"Style",'2015-02-15',31,205,108);
insert into songs values
(1003,"She Will be Love",'2007-06-09',31,206,100);
insert into songs values

(1004,"Demomns",'2013-01-05',30,207,102);
insert into songs values
(1005,"Radioactive",'2012-05-18',30,207,102);
insert into songs values
(1006,"In the End",'2002-09-26',33,209,103);
insert into songs values
(1007,"Numb",'2002-04-27',33,209,103);
insert into songs values
(1008,"Brave",'2007-03-27',36,209,103);
Table 7:
insert into Song_sales values
(1000,2012,200550);
insert into Song_sales values
(1003,2013,125000);
insert into Song_sales values
(1005,2013,500000);
insert into Song_sales values
(1006,2013,110000);
insert into Song_sales values
(1007,2014,45000);

SQL QUERIES
1. Select * from Artist a, Songs s
Where a.Artist_ID=s.Artist_ID
Group by s.Artist_ID having count(*) >1;
2. Select s.Song_ID, s.Song_title from songs s
having count(s.Artist_ID)>2;
NO OUTPUT
3. Select p.Publisher_ID,p.Publisher_Name, count(s.Publisher_ID) as No_of_songs from publisher
p, songs s
where p.Publisher_ID=s.Publisher_ID
group by s.Publisher_ID;
4. For Top Rated
Select s.Song_title as Top_rated_Songs from Songs s, song_sales a
Where a.Song_ID=s.Song_ID
and a.record_sold>200000;

For Low Rated
Select s.Song_title as Low_rated_Songs from Songs s, song_sales a
Where a.Song_ID=s.Song_ID
and a.record_sold<200000;
5. Select s.Song_title, s.Song_ID from pusongs.Songs s, pusongs.song_sales a
Where a.Song_ID=s.Song_ID
having count(a.record_sold/0.25);
6. Select g.Genre_Name from genre g, Songs s
Where g.Genre_ID=s.Genre_ID
group by g.Genre_ID;
NO OUTPUT
7. Select Artist_ID from Artist
where Artist_DOB like '%-05-%';
8. Select a.A_Fname, a.A_Lname,a.Artist_DOB, s.Song_title, p.Publisher_Name, g.Genre_Name
from Artist a, songs s, publisher p, genre g
where Artist_DOB > '1980-%-%'
and a.Artist_ID=s.Artist_ID
group by a.Artist_DOB;