2020-12-16 09:03:25

create database MusicStore
use MusicStore

--tao bang--
create table Music_Type(
TypeID int identity(1,1) primary key,
Name nvarchar(50) Not null,
Description nvarchar(100)
)

create table Album(
AlbumID varchar(20) primary key,
Title nvarchar(100) Not null,
TypeID int,
Artists nvarchar(100),
Rate int 
)

create table Song(
SongID int identity(1,1) primary key,
AlbumID varchar(20),
SongTitle nvarchar(200) Not null,
Artists nvarchar(50),
Author nvarchar(50),
Hits int
)

alter table Album
add constraint FK_TypeID foreign key (TypeID) references Music_Type(TypeID)

alter table Song
add constraint FK_AlbumID foreign key (AlbumID) references Album(AlbumID)

create clustered index FK_SongTitle on Song(SongTitle)
create nonclustered index FK_Artists on Song(Artists)

--them du lieu--
Insert into Music_Type( Name, Description)
values
('nhac tre', 'soi dong'),
('nhac tru tinh','hay'),
('nhac vang','hay')

select * from Music_Type

Insert into Album( AlbumID,Title,TypeID,Artists,Rate)
values
('1','AAA','1','Duc','2'),
('2','BBB','1','Duca','4'),
('3','CCC','1','Ducb','6'),
('4','DDD','1','Ducc','8')

Insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('1','LLL','NNN','Duc','100'),
('1','HHH','VVV','Duc1','200'),
('1','QQQ','PPP','Duc2','300'),
('1','EEE','TTT','Duc3','400'),
('1','III','NNN','Duc4','500')

select * from Album
where Album.Rate = 5

2020-12-16 09:02:49

reate database MusicStore

use MusicStore

create table MusicType(
    TypeID int Primary key identity(1,1), --Music Type code
	Name nvarchar(50) Not null, --Name of this type
	Description nvarchar(100) --Description about this type
)
go

create table Album(
	AlbumID varchar(20) Primary Key, --Mã của album
	Title nvarchar(100) Not null, --Tiêu đề của album
	TypeID int references MusicType(TypeID),
	Artists nvarchar(100), --Music Artist
	Rate int Default '0' check (Rate <=5) --Evaluation about the album
)
go

create table Song(
 SongID int Primary key identity(1,1),-- ID of song
	AlbumID varchar(20) references Album(AlbumID), --Album contains the song
	SongTitle nvarchar(200) Not null,-- Title of the song
	Artists nvarchar (50),-- Name of the Artist
	Author nvarchar (50), --Name of the Author
	Hits int check (Hits >=0) --Number of times
)
go

create index IX_SongTitle on Song(SongTitle)

create index IX_Artists on Song(Artists)

insert into MusicType(Name,Description)
values
('rapber ','noi nhieu nhanhnhau '),
('nosotop','Nhac paylac nhau '),
('remix','them nhieu bass pass '),
('beatter ','k loi'),
('nhac do','nhac canh mang lang ')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
(1,'Album cua Dong','1','blackpink','5'),
(2,'Album cua jisoo','3','blackpig','5'),
(3,'Album cua rickstar','5','blackpick','2'),
(4,'Album cua Gducky','2','blackpull','5'),
(5,'Album cua HaiPhong','4','blackclover','1')

insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('2','noi ve ty','avcd ','abcc ','2'),
('3','noi ve cong song','avcd ','a6d c','7'),
('5','noi ve dty','ade','a2c','6')

select *from Album where Rate ='5'

select *from Album where Title='Goodbye Swallow'

create view v_Albums
as
select MusicType.Name, Album.AlbumID,Album.Title,Album.Rate
from MusicType,Album
where MusicType.TypeID=Album.TypeID

select * from v_Albums

create view v_TopSongs
as
select Song.SongID,song.SongTitle,Album.Rate
from Song,Album
where song.AlbumID=Album.AlbumID and Song.Hits <= 10

select * from v_TopSongs

create proc sp_SearchByArtist
 @parameter nvarchar (50)
as
 select * from Song where Song.Artists =@parameter

 exec  sp_SearchByArtist avc

create proc sp_ChangeHits
 @parameter int,
 @hit int
as
begin
 update Song set Hits = @hit where SongID=@parameter
end

exec sp_ChangeHits 

2020-12-16 09:01:50

create database called_MusicStore
use called_MusicStore

 create table MusicType(
 TypeID int identity (1,1) primary key,
 Name nvarchar(50) not null,
Description nvarchar(100)
 )
 go

 insert into MusicType(Name,Description)
 values
 ('Vpop','good'),
 ('Kpop','good'),
 ('USpop','good'),
 ('Cpop','good'),
 ('Dpop','good')

 create table Album_1(
 AlbumID nvarchar(20) primary key,
 Title nvarchar (100) not null,
 TypeID int,
 Artists nvarchar(100),
 Rate int
 )
 go 

 insert into Album_1(AlbumID,Title,TypeID,Artists,Rate)
 values
 ('1','Vpop1','1','Nguyen Su','1'),
 ('2','Vpop2','2','Nguyen Su','3'),
 ('3','Vpop3','3','Nguyen Su','5'),
 ('4','Vpop4','4','Nguyen Su','7'),
 ('5','Vpop5','5','Nguyen Su','9')
 
 create table Song_1(
 SongID int identity (1,1) primary key,
 AlbumID varchar(20),
 SongTitle nvarchar(200) not null,
 Artists nvarchar(50),
 Author  nvarchar(50),
 Hits  int 
 )
 go
 insert into Song_1(AlbumID,SongTitle,Artists,Author,Hits)
 values
 ('1','ABC','Nguyen S','XYZ','1000'),
 ('2','BBC','Nguyen A','YYZ','2000'),
 ('3','CBC','Nguyen B','ZYZ','3000'),
 ('4','DBC','Nguyen C','CYZ','4000'),
 ('5','EBC','Nguyen D','QYZ','5000')


 alter table Album_1
 add constraint FK_TypeID  foreign key (TypeID ) references MusicType(TypeID )
 alter table Song_1
 add constraint FK_AlbumID foreign key (AlbumID) references Album_1(AlbumID)

 create clustered index IX_SongTitle on Song_1(SongTitle)
 create nonclustered index IX_Artists on Song_1 (Artists)

 -- insertting data -- 
  select *from MusicType
  select *from Album_1
  select *from Song_1

--- 6 ---
select *from Album_1
where Album_1.Rate = 5

--- 7 ---
select Song_1.SongTitle
from Song_1, MusicType, Album_1
where Album_1.TypeID = MusicType.TypeID and Song_1.AlbumID = Album_1.AlbumID
and Album_1.Title = 'Goodbye Swallow' 

--- 8 ---
create view  v_Albums
as
SELECT Album_1.AlbumID, Album_1.Title, Album_1.Rate, MusicType.Name
FROM Album_1,MusicType, Song_1
WHERE Album_1.TypeID = MusicType.TypeID and Song_1.AlbumID = Album_1.AlbumID
select *from v_Albums

--- 9 ---
create view v_TopSongs
as
select top 10 Song_1.SongTitle, Song_1.Artists, Song_1.Author, Song_1.Hits
FROM Album_1,MusicType, Song_1
WHERE Album_1.TypeID = MusicType.TypeID and Song_1.AlbumID = Album_1.AlbumID
order by Song_1.Hits desc
 select *from v_TopSongs

2020-12-16 09:01:20

create database MusicStore

use MusicStore

create table MusicType(
    TypeID int Primary key identity(1,1), --Music Type code
	Name nvarchar(50) Not null, --Name of this type
	Description nvarchar(100) --Description about this type
)
go

create table Album(
	AlbumID varchar(20) Primary Key, --Mã của album
	Title nvarchar(100) Not null, --Tiêu đề của album
	TypeID int references MusicType(TypeID),
	Artists nvarchar(100), --Music Artist
	Rate int Default '0' check (Rate <=5) --Evaluation about the album
)
go

create table Song(
 SongID int Primary key identity(1,1),-- ID of song
	AlbumID varchar(20) references Album(AlbumID), --Album contains the song
	SongTitle nvarchar(200) Not null,-- Title of the song
	Artists nvarchar (50),-- Name of the Artist
	Author nvarchar (50), --Name of the Author
	Hits int check (Hits >=0) --Number of times
)
go

create index IX_SongTitle on Song(SongTitle)

create index IX_Artists on Song(Artists)

insert into MusicType(Name,Description)
values
('rap','noi nhieu nhanh'),
('nosotop','Nhac paylac'),
('remix','them nhieu bass'),
('beat','k loi'),
('nhac do','nhac canh mang')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
(1,'Album cua Dong','1','blackpink','5'),
(2,'Album cua jisoo','3','blackpig','5'),
(3,'Album cua rickstar','5','blackpick','2'),
(4,'Album cua Gducky','2','blackpull','5'),
(5,'Album cua HaiPhong','4','blackclover','1')

insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('2','noi ve ty','avc','abc','2'),
('3','noi ve cong song','avc','a6c','7'),
('5','noi ve ty','ade','a2c','6')

select *from Album where Rate ='5'

select *from Album where Title='Goodbye Swallow'

create view v_Albums
as
select MusicType.Name, Album.AlbumID,Album.Title,Album.Rate
from MusicType,Album
where MusicType.TypeID=Album.TypeID

select * from v_Albums

create view v_TopSongs
as
select Song.SongID,song.SongTitle,Album.Rate
from Song,Album
where song.AlbumID=Album.AlbumID and Song.Hits <= 10

select * from v_TopSongs

create proc sp_SearchByArtist
 @parameter nvarchar (50)
as
 select * from Song where Song.Artists =@parameter

 exec  sp_SearchByArtist avc

create proc sp_ChangeHits
 @parameter int,
 @hit int
as
begin
 update Song set Hits = @hit where SongID=@parameter
end

exec sp_ChangeHits 1,10

2020-12-16 09:00:42

create database MusicStore

use MusicStore

create table MusicType (
	TypeID int primary key identity(1, 1),
	Name nvarchar(50) not null,
	Description nvarchar(100),
)

create table Album (
	AlbumID	varchar(20) primary key,
	Title nvarchar(100) not null,
	TypeID int foreign key references MusicType(TypeID),
	Artists nvarchar(100),
	Rate int default 0 check (rate <= 5),
)

create table Song (
	SongID int primary key identity (1, 1),
	AlbumID varchar(20) foreign key references Album(AlbumID),
	SongTitle nvarchar (200) not null,
	Artists nvarchar(50),
	Author nvarchar(50),
	Hits int check (Hits >= 0),
)

create index IX_SongTitle on Song(SongTitle)

create index IX_Artists on Song(Artists)

insert into MusicType (Name, Description)
values 
('Jazz', 'Jazz is a music genre that originated in the African-American'),
('Pop', 'Pop is a genre of popular music that originated in its modern form during the mid-1950s'),
('Kpop', 'K-pop is a genre of popular music originating in South Korea.')

select * from MusicType

insert into Album (AlbumID, Title, TypeID, Artists, Rate)
values 
('A111', 'Moana', 1, 'Cara', 3),
('A122', 'Super One', 3, 'SuperM', 5),
('A133', 'dreAMEE', 2, 'AMEE', 5),
('A144', 'Goodbye Swallow', 2, 'Nugu', 4)
select * from Album

insert into Song (AlbumID, SongTitle, Artists, Author, Hits)
values
('A122', 'Infinity', 'Baekhyun', 'SM', 200),
('A133', 'Nang Tho', 'AMEE', 'Hoang Dung', 1000),
('A111', 'How Far Will I Go?', 'Cara', 'Disney', 20000),
('A144', 'No Name', 'Nugu', 'ABC Ent', 1200)

select * from Song

select * from Album where Rate = 5

select SongID, SongTitle, Title from Song, Album where Song.AlbumID = Album.AlbumID and Title = 'Goodbye Swallow'

create view v_Albums
as
select AlbumID, Title, Name, Rate from Album, MusicType where MusicType.TypeID = Album.TypeID

select * from v_Albums

create view v_TopSongs
as
select SongID, SongTitle, AlbumID, Artists, Author, Hits from Song

select top 10 * from v_TopSongs order by Hits desc

create proc sp_SearchByArtists
	@Artists_name nvarchar(100)
as
BEGIN
	select SongID, SongTitle, AlbumID, Artists, Author, Hits from Song where Artists = @Artists_name
	group by SongID, SongTitle, AlbumID, Artists, Author, Hits
END

exec sp_SearchByArtists Cara 

create proc sp_ChangeHits
	@songid int,
	@hits int
as
BEGIN
	select * from Song where SongID = @songid
	group by SongID, SongTitle, AlbumID, Artists, Author, Hits
	update Song set Hits = @hits
END

exec sp_ChangeHits 2, 16777

2020-12-16 09:00:33

create database MusicStore

use MusicStore

create table MusicType(

    TypeID int Primary key identity(1,1), --Music Type code

Name nvarchar(50) Not null, --Name of this type

Description nvarchar(100) --Description about this type

)

go

create table Album(

AlbumID varchar(20) Primary Key, --Mã của album

Title nvarchar(100) Not null, --Tiêu đề của album

TypeID int references MusicType(TypeID),

Artists nvarchar(100), --Music Artist

Rate int Default '0' check (Rate <=5) --Evaluation about the album

)

go

create table Song(

 SongID int Primary key identity(1,1),-- ID of song

AlbumID varchar(20) references Album(AlbumID), --Album contains the song

SongTitle nvarchar(200) Not null,-- Title of the song

Artists nvarchar (50),-- Name of the Artist

Author nvarchar (50), --Name of the Author

Hits int check (Hits >=0) --Number of times

)

go

create index IX_SongTitle on Song(SongTitle)

create index IX_Artists on Song(Artists)

insert into MusicType(Name,Description)

values

('rap','noi nhieu nhanh'),

('nosotop','Nhac paylac'),

('remix','them nhieu bass'),

('beat','k loi'),

('nhac do','nhac canh mang')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)

values

(1,'Album cua Dong','1','blackpink','5'),

(2,'Album cua jisoo','3','blackpig','5'),

(3,'Album cua rickstar','5','blackpick','2'),

(4,'Album cua Gducky','2','blackpull','5'),

(5,'Album cua HaiPhong','4','blackclover','1')

insert into Song(AlbumID,SongTitle,Artists,Author,Hits)

values

('2','noi ve ty','avc','abc','2'),

('3','noi ve cong song','avc','a6c','7'),

('5','noi ve ty','ade','a2c','6')

select *from Album where Rate ='5'

select *from Album where Title='Goodbye Swallow'

create view v_Albums

as

select MusicType.Name, Album.AlbumID,Album.Title,Album.Rate

from MusicType,Album

where MusicType.TypeID=Album.TypeID

select * from v_Albums

create view v_TopSongs

as

select Song.SongID,song.SongTitle,Album.Rate

from Song,Album

where song.AlbumID=Album.AlbumID and Song.Hits <= 10

select * from v_TopSongs

create proc sp_SearchByArtist

 @parameter nvarchar (50)

as

 select * from Song where Song.Artists =@parameter

 exec  sp_SearchByArtist avc

create proc sp_ChangeHits

 @parameter int,

 @hit int

as

begin

 update Song set Hits = @hit where SongID=@parameter

end

exec sp_ChangeHits 1,10

create database MusicStore

use MusicStore

create table MusicType(
    TypeID int Primary key identity(1,1), --Music Type code
	Name nvarchar(50) Not null, --Name of this type
	Description nvarchar(100) --Description about this type
)
go

create table Album(
	AlbumID varchar(20) Primary Key, --Mã của album
	Title nvarchar(100) Not null, --Tiêu đề của album
	TypeID int references MusicType(TypeID),
	Artists nvarchar(100), --Music Artist
	Rate int Default '0' check (Rate <=5) --Evaluation about the album
)
go

create table Song(
 SongID int Primary key identity(1,1),-- ID of song
	AlbumID varchar(20) references Album(AlbumID), --Album contains the song
	SongTitle nvarchar(200) Not null,-- Title of the song
	Artists nvarchar (50),-- Name of the Artist
	Author nvarchar (50), --Name of the Author
	Hits int check (Hits >=0) --Number of times
)
go

create index IX_SongTitle on Song(SongTitle)

create index IX_Artists on Song(Artists)

insert into MusicType(Name,Description)
values
('rap','noi nhieu nhanh'),
('nosotop','Nhac paylac'),
('remix','them nhieu bass'),
('beat','k loi'),
('nhac do','nhac canh mang')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
(1,'Album cua Dong','1','blackpink','5'),
(2,'Album cua jisoo','3','blackpig','5'),
(3,'Album cua rickstar','5','blackpick','2'),
(4,'Album cua Gducky','2','blackpull','5'),
(5,'Album cua HaiPhong','4','blackclover','1')

insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('2','noi ve ty','avc','abc','2'),
('3','noi ve cong song','avc','a6c','7'),
('5','noi ve ty','ade','a2c','6')

select *from Album where Rate ='5'

select *from Album where Title='Goodbye Swallow'

create view v_Albums
as
select MusicType.Name, Album.AlbumID,Album.Title,Album.Rate
from MusicType,Album
where MusicType.TypeID=Album.TypeID

select * from v_Albums

create view v_TopSongs
as
select Song.SongID,song.SongTitle,Album.Rate
from Song,Album
where song.AlbumID=Album.AlbumID and Song.Hits <= 10

select * from v_TopSongs

create proc sp_SearchByArtist
 @parameter nvarchar (50)
as
 select * from Song where Song.Artists =@parameter

 exec  sp_SearchByArtist avc

create proc sp_ChangeHits
 @parameter int,
 @hit int
as
begin
 update Song set Hits = @hit where SongID=@parameter
end

exec sp_ChangeHits 1,10

create database MusicStore

use MusicStore

create table MusicType(
    TypeID int Primary key identity(1,1), --Music Type code
	Name nvarchar(50) Not null, --Name of this type
	Description nvarchar(100) --Description about this type
)
go

create table Album(
	AlbumID varchar(20) Primary Key, --Mã của album
	Title nvarchar(100) Not null, --Tiêu đề của album
	TypeID int references MusicType(TypeID),
	Artists nvarchar(100), --Music Artist
	Rate int Default '0' check (Rate <=5) --Evaluation about the album
)
go

create table Song(
 SongID int Primary key identity(1,1),-- ID of song
	AlbumID varchar(20) references Album(AlbumID), --Album contains the song
	SongTitle nvarchar(200) Not null,-- Title of the song
	Artists nvarchar (50),-- Name of the Artist
	Author nvarchar (50), --Name of the Author
	Hits int check (Hits >=0) --Number of times
)
go

create index IX_SongTitle on Song(SongTitle)

create index IX_Artists on Song(Artists)

insert into MusicType(Name,Description)
values
('rap','noi nhieu nhanh'),
('nosotop','Nhac paylac'),
('remix','them nhieu bass'),
('beat','k loi'),
('nhac do','nhac canh mang')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
(1,'Album cua Dong','1','blackpink','5'),
(2,'Album cua jisoo','3','blackpig','5'),
(3,'Album cua rickstar','5','blackpick','2'),
(4,'Album cua Gducky','2','blackpull','5'),
(5,'Album cua HaiPhong','4','blackclover','1')

insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('2','noi ve ty','avc','abc','2'),
('3','noi ve cong song','avc','a6c','7'),
('5','noi ve ty','ade','a2c','6')

select *from Album where Rate ='5'

select *from Album where Title='Goodbye Swallow'

create view v_Albums
as
select MusicType.Name, Album.AlbumID,Album.Title,Album.Rate
from MusicType,Album
where MusicType.TypeID=Album.TypeID

select * from v_Albums

create view v_TopSongs
as
select Song.SongID,song.SongTitle,Album.Rate
from Song,Album
where song.AlbumID=Album.AlbumID and Song.Hits <= 10

select * from v_TopSongs

create proc sp_SearchByArtist
 @parameter nvarchar (50)
as
 select * from Song where Song.Artists =@parameter

 exec  sp_SearchByArtist avc

create proc sp_ChangeHits
 @parameter int,
 @hit int
as
begin
 update Song set Hits = @hit where SongID=@parameter
end

exec sp_ChangeHits 1,10

2020-12-16 09:00:29

create database MusicStore
use MusicStore

create table MusicType(
TypeID int Primary key identity(1,1),
Name nvarchar(50) Not null,
Description nvarchar(100)
)

create table Album(
AlbumID nvarchar(20) primary key,
Title nvarchar(100) not null,
TypeID int,
Artists nvarchar(100),
Rate int default 0 check (rate<=5),
constraint FK_Album_TypeID foreign key (TypeID) references MusicType(TypeID)
)

create table Song(
SongID int Primary key identity(1,1),
AlbumID nvarchar(20) foreign key references Album(AlbumID),
SongTitle nvarchar(200) not null, 
Artists nvarchar(50),
Author nvarchar(50),
Hits int check (Hits >= 0)
)

select * from  MusicType
select * from  Album
select * from  Song


create nonclustered index IX_SongTitle on Song(SongTitle)
create nonclustered index IX_Artists on Song(Artists)

insert into MusicType(Name,Description)
values
('Pop','Nhe nhang'),
('Rock','Manh me'),
('Acoustic','Vui ve')



insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
('Pop1', N'Pop 1 Nhe nhang','1','Trung Duc',1),
('Rock1', N'Pop 1 Manh me','2','Tran Van Diep',2),
('Acousti1', N'Acousti 1 Vui ve','3','Trung Duc',3)

insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('Pop1', 'Kiss the rain','Artists1','Author1',1),
('Pop1', 'Beautiful','Artists2','Author2',2),
('Rock1', 'Numb','Artists3','Author3',3),
('Rock1', 'Water','Artists42','Author4',4),
('Acousti1', 'Rain on love','Artists5','Author5',5)

--List all the albums rated level 5 (Rate = 5)
select * from Album where Rate = 1

--List all the song titles of the album "Goodbye Swallow"
select Song.SongTitle, Album.Title 
from Song inner join Album on Song.AlbumID = Album.AlbumID
where Album.Title = N'Pop 1 Nhe nhang'

--Create a view named v_Albums with information (albumID, the Title, Name, Rate) taken from Album and MusicType tables
create view v_Albums
as
select Album.AlbumID, Album.Title, MusicType.Name, Album.Rate
from Album inner join MusicType on  Album.TypeID = MusicType.TypeID

select * from v_Albums

--Create a view named v_TopSongs displays information about top 10 songs listened\downloaded
create view v_TopSongs
as
select Song.SongTitle, Song.Hits, Album.Title, MusicType.Name
From Song inner join Album on Song.AlbumID = Album.AlbumID inner join MusicType on  Album.TypeID = MusicType.TypeID
Where Song.Hits <=10

select * from v_TopSongs

--Create a stored procedure named sp_SearchByArtist with input parameter is the nameof the singer and display all the songs that the singer presented.
create Proc sp_SearchByArtist
  @NameArtist nvarchar(100)
as
begin 
	select Song.SongTitle, Song.Artists from Song
	where Song.Artists =  'Artist1'
end

exec sp_SearchByArtist 'Artists1'

--Create a store procedure called sp_ChangeHits with input parameter is the song id and hits (listened\ downloaded). This procedure allows update hits (listened \ download) for asong with the corresponding code

create proc sp_ChangeHits
 @SongID int, @Hits int
 as
begin
		update Song set Song.Hits =  @Hits  where Song.SongID = @SongID
end

exec sp_ChangeHits 1, 5

select * from  MusicType
select * from  Album
select * from  Song

2020-12-16 08:59:40

create database MusicStore
use MusicStore

create table Music_Type (
TypeID int  identity(1,1) Primary key,
Name nvarchar (50) Not null,
Description nvarchar (100)
)

create table Album(
AlbumID varchar (20) Primary Key,
Title nvarchar (100) Not null,
TypeID int,
Artists nvarchar (100),
Rate int
)

create table Song (
SongID int identity(1,1) Primary key,
AlbumID varchar (20) ,
SongTitle nvarchar (200) Not null,
Artists nvarchar (50),
Author nvarchar (50),
Hits int 
)
alter table Album
add constraint FK_TypeID foreign key (TypeID) references  Music_Type(TypeID)

alter table Song
add constraint FK_AlbumID foreign key (AlbumID) references  Album(AlbumID)

create clustered index IX_SongTitle on Song(SongTitle)
create nonclustered index IX_Artists on Song (Artists)

insert into Music_Type (Name,Description)
values
('Nhac Tre ',' 100 bai hat '),
('Nhac cach mang',' 105 Bai hat '),
('Nhac dan ca ',' 200 Bai hat ')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
('1','Nhac tre 1','1','Nghe si abc','3'),
('2','Nhac tre 2','1','Nghe si ggg','1'),
('3','Nhac dan ca 1','3','Nghe si apo','4'),
('4','Nhac dan ca 2','3','Nghe si c','3'),
('5','Nhac cach mang 1','2','Nghe si g','5')

insert into Song (AlbumID,SongTitle,Artists,Author,Hits)
values
('1','bai hat nt 1','Nghe si abc','abc','100'),
('2','bai hat nt 2','Nghe si ggg','jj','400'),
('3','bai hat ndc 1','Nghe si apo','ff','200'),
('4','bai hat ndc 2','Nghe si c','aabc','150'),
('5','bai hat ncm 1','Nghe si g','ajj','600')


select * from Album
where Album.Rate = 5

select Song.SongTitle
from Song,Album,Music_Type
where Album.TypeID = Music_Type.TypeID and Song.AlbumID = Album.AlbumID 
and Album.Title = 'Goodbye Swallow'


alter view v_TopSongs 
as
select top 10 Song.SongTitle,Song.Artists,Song.Author,Song.Hits
from Song,Album,Music_Type
where Album.TypeID = Music_Type.TypeID and Song.AlbumID = Album.AlbumID 
order by Song.Hits desc

select * from v_TopSongs

create view v_Album 
as
select Album.AlbumID,Album.Title,Music_Type.Name,Album.Rate
from Song,Album,Music_Type
where Album.TypeID = Music_Type.TypeID and Song.AlbumID = Album.AlbumID 

select * from v_Album 


create proc sp_SearchByArtist 
@tencasi nvarchar(100)
as
select Song.SongTitle,Song.Artists,Song.Author,Song.Hits
from Song,Album,Music_Type
where Album.TypeID = Music_Type.TypeID and Song.AlbumID = Album.AlbumID 
and Song.Artists = @tencasi

exec sp_SearchByArtist 'Nghe si ggg'
 
create proc sp_ChangeHits
@luot_truy_cap int 
as
select Song.SongTitle,Song.Artists,Song.Author,Song.Hits
from Song,Album,Music_Type
where Album.TypeID = Music_Type.TypeID and Song.AlbumID = Album.AlbumID 
and Song.Hits =  @luot_truy_cap

exec sp_ChangeHits '200'

2020-12-16 08:59:00

create database MusicStore
use MusicStore

create table MusicType (
	TypeID int Primary key identity(1, 1),
	Name nvarchar(50) Not null,
	Description nvarchar(100)
)

create table Album (
	AlbumID varchar(20) Primary Key,
	Title nvarchar(100) not null,
	TypeID int foreign key references MusicType(TypeID),
	Artists nvarchar(100),
	Rate int
)

create table Song (
	SongID int Primary key identity(1, 1),
	AlbumID varchar(20) foreign key references Album(AlbumID),
	SongTitle nvarchar(200) Not null,
	Artists nvarchar(50),
	Author nvarchar(50),
	Hits int
)

create unique index IX_SongTitle on Song(SongTitle)

create unique index IX_Artists on Song(Artists)

insert into MusicType(Name, Description)
values
('Rock', 'Hay'),
('Rap', 'Hay'),
('Chu Tinh', 'Hay')

insert into Album(AlbumID, Title, TypeID, Artists, Rate)
values
('A001', 'Album 1', 1, 'Tuan Hung', 5),
('A002', 'Album 2', 2, 'BinZ', 4),
('A003', 'Goodbye Swallow', 3, 'Dan Truong', 5)

insert into Song(AlbumID, SongTitle, Artists, Author, Hits)
values
('A002', 'Big CityBoy', 'BinZ', 'Author 1', 1000),
('A001', 'Vi Nguoi Khong Xung Dang', 'Tuan Hung', 'Author 2', 500),
('A003', 'Tinh Khuc Vang', 'Dan Truong', 'Author 3', 3000)

select * from MusicType
select * from Album
select * from Song

select Album.AlbumID, Album.Title, Song.SongTitle, Album.Artists, Album.Rate
from Album, Song
where Album.AlbumID=Song.AlbumID and Album.Rate=5

select Song.SongTitle, Album.Title, Album.Artists, Album.Rate, Song.Hits
from Song, Album
where Album.AlbumID=Song.AlbumID and Album.Title='Goodbye Swallow'

create view v_Albums 
as
select Album.AlbumID, Album.Title, MusicType.Name, Album.Rate
from Album, MusicType
where Album.TypeID=MusicType.TypeID

select * from v_Albums

create view v_TopSongs
as
select 


create proc sp_SearchByArtist 
	@Artists nvarchar(50)
as
begin
	select Song.SongTitle, Album.AlbumID, Song.Hits
	from Song, Album
	where Song.AlbumID=Album.AlbumID and Album.Artists=@Artists
end

exec sp_SearchByArtist 'BinZ'

create proc sp_ChangeHits
	@SongId int,
	@Hits int
as
begin
	Select Song.SongID, Album.AlbumID, Album.Rate, Song.SongTitle, Song.Artists, Song.Hits
	from Song, Album
	where Album.AlbumID=Song.AlbumID and Song.SongID=@SongId and Song.Hits=@Hits
end

exec sp_ChangeHits 1, 1000

2020-12-16 08:54:27

create database MusicStore

use MusicStore

create table MusicType (
	TypeID int primary key identity (1,1),
	name nvarchar(50) not null,
	Description nvarchar(100)
)

create table Album (
	AlbumID varchar(20) primary key,
	Title nvarchar(100) not null,
	TypeID int references MusicType(TypeID),
	Artists nvarchar(100),
	rate int 
)

create table Song (
	SongID int primary key identity (1,1),
	AlbumID varchar(20) references Album(AlbumID),
	SongTitle nvarchar(200) not null,
	Artists nvarchar(50),
	Author nvarchar(50),
	Hits int
)

create unique index IX_SongTitle on Song(SongTitle)

create unique index IX_Artists on Song(Artists)

insert into MusicType(name, Description)
values
('POP', 'Hay'),
('Rock', 'Hay'),
('Rap', 'Hay')

insert into Album(AlbumID, Title, TypeID, Artists, rate)
values
('AB01', 'Tran Van Diep', 1, 'Tran Thi Diep', 5),
('AB02', 'Goodbye Swallow', 2, 'Tran Thi A', 3),
('AB03', 'Tran Van B', 3, 'Tran Thi B', 4)

insert into Song(AlbumID, SongTitle, Artists, Author, Hits)
values
('AB01', 'Cho Thi Lai', 'Tran Thi Diep', 'Phan Dong', 1),
('AB02', 'Di Ma', 'Tran Thi A', 'Trung Duc', 2),
('AB03', 'Thay Oi', 'Tran Thi B', 'Phan Dong', 1)

select * from MusicType
select * from Album
select * from Song

select * from Album where rate = 5

select * from Album where Title = 'Goodbye Swallow'

create view v_Albums
as
select Album.AlbumID, Album.Title, MusicType.name, Album.rate
from MusicType, Album
where Album.TypeID = MusicType.TypeID

select * from v_Albums

create proc sp_SearchByArtist
	@Artists nvarchar(50)
as
begin
	select Album.Artists, Album.rate, Song.SongTitle
	from Album, Song 
	where Album.AlbumID = Song.AlbumID and Album.Artists = @Artists
end

exec sp_SearchByArtist 'Tran Thi Diep'

create proc sp_ChangeHits
	
create view v_TopSongs
as
select * from Song where Hits <= 10

select * from v_TopSongs

create proc sp_ChangeHits
	@SongID int, @Hits int
as
begin
	select Song.SongID, Album.Artists, Song.Hits, Song.SongTitle
	from Song, Album
	where Song.AlbumID = Album.AlbumID and Song.SongID = @SongID and Song.Hits = @Hits
end

exec sp_ChangeHits '1', '1'