2023-07-02 05:37:20

Create database MusicStore
go

Use MusicStore
go

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

Create table Album (
	AlbumID varchar(20) Primary key,
	Title nvarchar(100) not null,
	TypeID int references MusicType(TypeID),
	Artists nvarchar(100),
	Rate int Default 0 
	check(Rate <= 5)
)
go

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 check (Hits >= 0)
)
go

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

insert into MusicType(Name, Description) 
values 
('Indie', 'Chill and Melodious music'), 
('Rock', 'Solid and Tough music'),
('EDM', 'Electronic and Dance music'),
('Jazz', 'Traditional blues and ragtime music'),
('Funk', 'Rhythmic ang danceable music')
go

select * from MusicType
select * from Album
insert into Album (AlbumID, Title, TypeID, Artists, Rate)
values 
('1', 'MomentEP', 1, 'Peggy Gou', 4),
('2', 'Justice', 2, 'Justin Bieber', 2),
('3', 'Motion', 3, 'Calvin Harris', 3),
('4', 'Mommy', 1, 'Duc Anh', 5),
('5', 'Goodbye Swallow', 2, 'Tran Tien', 4)
go

insert into Song (AlbumID, SongTitle, Artists, Author, Hits) 
values 
('1', 'Peaches', 'Justin Bieber', 'Michelle', 10000),
('2', 'Han Pan', 'Peggy Gou', 'Peggy Gou', 434847),
('3', 'Summer', 'Calvin Harris', 'Calvin Harris', 4447),
('4', 'Random', 'Duc Anh', 'Chris', 12),
('5', 'Chim Bo Cau', 'Dong Nhi', 'Tran Tien', 334),
('5', 'Mat Nai', 'O Cao Thang', 'Tran Tien', 12)
go

select * from Album 
where Rate = 5
go

select Song.SongTitle, Album.Title, Album.Artists, Album.Rate
from Album inner join Song
on Album.AlbumID = Song.AlbumID
where Album.Title = 'Goodbye Swallow'
go

create view v_Albums
as 
select Album.AlbumID, Album.Title, MusicType.Name, Album.Rate 
from Album left join MusicType
on Album.TypeID = MusicType.TypeID
go

create view v_TopSongs
as
select top 10 Song.SongID, Song.AlbumID, Song.SongTitle, Song.Artists, Song.Author, Song.Hits N'Number of downloads/listening' from Song 
order by Hits desc
go

select * from v_Albums;
select * from v_TopSongs;
go

create proc sp_SearchByArtists
	@ArtistName nvarchar(50)
as
begin
	select * from Song
	where Artists = @ArtistName
end

insert into Song (AlbumID, SongTitle, Artists, Author, Hits) 
values 
('2', 'Chill Man', 'Peggy Gou', 'Bolabola', 24051)
go

exec sp_SearchByArtists 'Peggy Gou'
go

alter proc sp_ChangeHits 
	@SongID int,
	@Hits int
as
begin 
	update Song set Hits = @Hits 
	where SongID = @SongID
	if @@ROWCOUNT = 0
		begin 
			print N'(CONVERT)Không tìm thấy bảng ghi với SongID = ' + convert(nvarchar(50), @SongID)
			--same with
			print N'(CAST)Không tìm thấy bảng ghi với SongID = ' + cast(@SongID as nvarchar(50))
		end
	else
		begin
			print N'Cập nhật thành công'
		end
end 
go

exec sp_ChangeHits 9, 99999

2021-04-15 05:37:27

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

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

drop table MusicType

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(1200) 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 
('Pop', 'genre of popular music that produces the most hits'),
('EDM', 'EDM is also known as dance music'),
('House', 'genre of electronic music characterized by repetitive beat')


insert into Album(AlbumID, Title, TypeID, Artists, Rate)
values 
('1', 'MomentEP', 1, 'Peggy Gou', 4),
('2', 'Justice', 2, 'Justin Bieber', 2),
('3', 'Motion', 3, 'Calvin Harris', 3),
('4', 'Mommy', 1, 'Duc Anh', 5),
('5', 'Goodbye Swallow', 2, 'Tran Tien', 4)

insert into Song(AlbumID, SongTitle, Artists, Author, Hits)
values 
('1', 'Peaches', 'Justin Bieber', 'Michelle', 10000),
('2', 'Han Pan', 'Peggy Gou', 'Peggy Gou', 434847),
('3', 'Summer', 'Calvin Harris', 'Calvin Harris', 4447),
('4', 'Random', 'Duc Anh', 'Chris', 12),
('5', 'Chim Bo Cau', 'Dong Nhi', 'Tran Tien', 334),
('5', 'Mat Nai', 'O Cao Thang', 'Tran Tien', 12)

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

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


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

drop view v_Albums

select * from v_Albums

create view v_TopSongs 
as 
select top 3 Song.SongTitle, Song.Artists, Song.Hits 'Number of downloads/listening'
from Song 
order by Song.Hits desc; 

select * from v_TopSongs

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

drop proc sp_SearchByArtist

exec sp_SearchByArtist 'Dong Nhi'

2021-04-14 15:54:35

-- tao EXAM--
create database Exam
-- su dung database --
use Exam

--tao bang MusicType--
create table MusicType(
	TypeID int primary key identity(1,1),
	Name nvarchar(50) not Null,
	Description nvarchar(100)
)
-- tao bang Album--
create table Album(
	AlbumID varchar(20) primary key,
	Title nvarchar(100) not null,
	TypeID int,
	constraint fk_Type_ID
	Foreign key (TypeID) references MusicType (TypeID),
	Artists nvarchar(100),
	Rate int default 0,
	check (Rate <=5)
)
-- tao bang Song--
create table Song(
	SongID int primary key identity(1,1),
	AlbumID varchar(20),
	constraint FK_Album_ID
	Foreign key (AlbumID) references Album(AlbumID),
	SongTitle nvarchar(200) not Null,
	Artists nvarchar(50),
	Author nvarchar(50),
	Hits int,
	check ( Hits >= 0)
)

--3.tao Indexer Songtitle-- 
create index IX_SongTitle on Song (SongTitle)
--4.tao indexer Artists--
create index IX_Artists on Song (Artists)
--5. insert 3 item--
insert into MusicType(Name,Description)
values
('Rolling in the Deep','POP'),
('In The End','ROCK'),
('Save Me','R&B')
select * from MusicType
insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
('R001','Rolling in the Deep',1,'ADELE',4),
('R002','In The End',2,'LinKin Park',5),
('R003','Save Me',3,'Future',3)
select * from Album
insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('R001','Rolling in the Deep','ADELE','ADELE',2),
('R002','In The End','LinKin Park','LinKin Park',5),
('R003','Save Me','Future','Future',7)
--6. show rate = 5--
select * from Song
select * from Album 
where Rate = 5
-- 7. show Album bat ky--
select Song.SongTitle,Song.SongID,Song.Artists,Album.Rate
from Song join Album on Song.AlbumID =  Album.AlbumID
	and Album.title = 'Save Me'

select Song.SongTitle,Album.Artists,song.Hits
from Song, Album
where Album.AlbumID = Song.AlbumID and Album.Title = 'Save Me'

--8. tao view v_Album--
create view v_Album
as
select AlbumID, Title,Name,Rate 
from Album join MusicType on Album.TypeID = MusicType.TypeID

select * from v_Album

--9. hien thi top 10 bai hat--
create view v_TopSongs
as
select top(10) * from Song
order by Hits desc

select * from v_TopSongs

--- 10. tao Procedure tim kiem theo Ca si--
create proc sp_SearchByArtist
	@Artists_name nvarchar(100)
as
begin
select * from Song
where Artists = @Artists_name
end

--noi dung can tim de sau sp_SearchByArtist
exec sp_SearchByArtist 'LinKin Park'


---11. tao Procedure tim kiem ID bai hay va HIts de hien thi thong tin bai hat do--
create proc sp_ChangeHits
	@Song_ID int,
	@Hits_ID int
as
begin
select * from Song
where SongID = @Song_ID
update Song set Hits = @Hits_ID
end

exec sp_ChangeHits 2, 12

2021-04-13 14:23:25

--1 create database

create database MusicStore

--2 create table

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 Default '0',

check (Rate<=5)

 )

 create table Song(

SongID int primary key identity(1,1) not null,

AlbumID varchar(20) references Album(AlbumID),

SongTitle nvarchar(200) not null,

Artists nvarchar(50),

Author nvarchar(50),

Hits int check (Hits >= 0)

)

--3 tạo nonclustered index cột songtitile

create nonclustered index IX_SongTitle on Song(SongTitle)

--4 tạo nonclustered index cột srtists

create nonclustered index IX_Artists on Song(Artists)

--5 insert

insert into MusicType(Name, Description)

values

('Pop','pop'),

('Rock','Alternative Rock'),

('Hiphop','Alternative Hiphop')

select * from MusicType

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

values

('Thrillerid', 'Album dinh cao',3,'Michael Jackson','4'),

('21id', 'DEF',1,'Adele','5'),

('thankyou,next_id','Thank You, Next',1,'Ariana Grande','5'),

('TeenageDreamid', 'TeenageDream',1,'Katy Perry','5'),

('/id', '/',2,'Ed Sheeran','3')

select * from Album

drop table Album

drop table MusicType

drop table Song

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

values

('Thrillerid', 'Thriller','Michael Jackson','Michael Jackson',1000000),

('thankyou,next_id', '7 rings','Ariana Grande','Tayla Parx',2000000),

('thankyou,next_id', 'Thank You, Next','Ariana Grande','Tommy Brown',30000000),

('thankyou,next_id', '34,35','Ariana Grande','Peter Lee Johnson',20000000),

('21id', 'Rolling in the Deep','Adele','Paul Epworth',20000000),

('21id', 'Set Fire to the Rain','Adele','Adele Adkins',500000),

('TeenageDreamid', 'Firework','Katy Perry','Jay',1000000),

('TeenageDreamid', 'Teenage Dream','Katy Perry','RM',4000000),

('TeenageDreamid', 'Last Friday Night (T.G.I.F.)','Katy Perry','Ed',600000),

('/id', 'Shape of You','Ed Sheeran','Ed',500000),

('/id', 'Love Song','Ed Sheeran','Ed',1000000)

--6 hiện thi tất cả thong tin các album có rate 5

select * from Album where Rate = 5

--- 7 hiển thị thông tin các bài hát trong album TeenageDream

select Song.AlbumID, Song.SongTitle , Song.Artists , Song.Author ,Album.Rate, Song.Hits

from Song, Album 

where Song.AlbumID=Album.AlbumID and Title = 'TeenageDream'

---8 hiển thị thông tin các album 

create view v_Albums

as

select AlbumID, Title, Name, Rate

from Album, MusicType

where MusicType.TypeID=Album.TypeID

select * from v_Albums

---- 9 hiện thị tất cả thông tin top 10 songs

--- c1

create view v_TopSongs

as

select top(10) SongID, SongTitle, AlbumID, Artists, Author, Hits

from Song

order by Hits desc

select * from v_TopSongs

s

drop view v_TopSongs

-- c2

create view v_TopSongss

as

select top(10) * from Song

order by Hits desc

select * from v_TopSongss

----10 tạo 1 biến lwuu trữu proc cho phép nhập vào tên cảu ca sĩ sau đó hiển thị thông tin các bài hát có nghễ sỹ trùng vs outout nhập

create proc sp_SearchByArtistsss

@Artists_name nvarchar(100)

as

BEGIN

select * from Song

where Artists=@Artists_name

END

--- biến đầu ra ghi luôn sau sp_SearchByArtists

exec sp_SearchByArtistsss Adele 

---11 bạo biến lưu trữ pro cho nhạp vào id bài hát vs hits để hieent hị thông in bài hát 

create proc sp_ChangeHitt

@songid int,

@hits int

as

BEGIN

select * from Song

where SongID=@songid

update Song set Hits = @hits

END

exec sp_ChangeHitt 4, 30000

select* from Song

2021-04-13 14:23:07

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

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

drop table MusicType

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(1200) 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 
('Pop', 'genre of popular music that produces the most hits'),
('EDM', 'EDM is also known as dance music'),
('House', 'genre of electronic music characterized by repetitive beat')


insert into Album(AlbumID, Title, TypeID, Artists, Rate)
values 
('1', 'MomentEP', 1, 'Peggy Gou', 4),
('2', 'Justice', 2, 'Justin Bieber', 2),
('3', 'Motion', 3, 'Calvin Harris', 3),
('4', 'Mommy', 1, 'Duc Anh', 5),
('5', 'Goodbye Swallow', 2, 'Tran Tien', 4)

insert into Song(AlbumID, SongTitle, Artists, Author, Hits)
values 
('1', 'Peaches', 'Justin Bieber', 'Michelle', 10000),
('2', 'Han Pan', 'Peggy Gou', 'Peggy Gou', 434847),
('3', 'Summer', 'Calvin Harris', 'Calvin Harris', 4447),
('4', 'Random', 'Duc Anh', 'Chris', 12),
('5', 'Chim Bo Cau', 'Dong Nhi', 'Tran Tien', 334),
('5', 'Mat Nai', 'O Cao Thang', 'Tran Tien', 12)

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

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


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

drop view v_Albums

select * from v_Albums

create view v_TopSongs 
as 
select top 3 Song.SongTitle, Song.Artists, Song.Hits 'Number of downloads/listening'
from Song 
order by Song.Hits desc; 

select * from v_TopSongs

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

drop proc sp_SearchByArtist

exec sp_SearchByArtist 'Dong Nhi'

2021-04-13 14:18:28

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 default  0 ,
check (Rate<=5)
)
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,
check (Hits >=0)
)
Create nonclustered index IX_SongTitle on Song(SongTitle)
Create nonclustered index IX_Artists on Song(Artists)
insert into MusicType(Name,[Description])
values
('Kpop','E')
('Pop','TRASH'),
('R&B','TRASH')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
('1','Red','1','Col12','3'),
('2','Yellow','2','Eng14','5'),
('3','Blue','3','LisXX','4')
insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('1','A','Col12','Col12','2'),
('2','B','Eng14','Eng14','3'),
('3','C','LisXXX','LisXXX','4')
 Select Album.Rate,Album.AlbumID,Album.Title,Album.TypeID,Album.Artists from Album
 where Album.Rate = 5
 Select Song.SongTitle,Song.AlbumID,Album.Title
 from Song,Album
 Where Album.AlbumID= Song.AlbumID
 and Album.Title = 'Red'
 Create view v_Albums
 as
 select Album.AlbumID,Album.Title,MusicType.Name,Album.Rate
 from Album left join MusicType on MusicType.TypeID = Album.TypeID
 select * from v_Albums
 Create view v_Topsongs
 as 
 select top (10) Song.SongTitle, Album.Title, Song.Hits,Song.AlbumID
 from Song left join Album on Song.AlbumID=Album.AlbumID

 select * from v_Topsongs
 order by Hits desc
 create proc sp_SearchByArtist
 @Singer nvarchar(20)
 as
 begin
 select Song.SongTitle,Album.Title,Album.Artists
 from Song left join Album on Song.AlbumID=Album.AlbumID
 where Album.Artists=@Singer
 end 

 exec sp_SearchByArtist 'Col12'
 create proc sp_ChangeHits 
 @Id int,@Hit int
 as
 begin
 update Song
set Song.Hits=@Hit
where Song.SongID=@Id
 end 
 select * from Song
 exec sp_ChangeHits  1,400

2021-04-13 14:08:34

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 references MusicType(TypeID),
Artists nvarchar(100),
Rate int Default 0 check (Rate <= 5)
)


create table Song (
SongID int Primary key identity (1,1),
AlbumID nvarchar(20) 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 Song(AlbumID,SongTitle,Artists,Author,Hits)
values 
(2,'cá cơm','thanh tùng','sơn tùng',50355),
(1,'max ping','thanh tùng','minh béo',1355),
(1,'fall in love','âu sâm','minh béo',5065)

Insert into MusicType(Name,Description)
values 
('ngáo','chơi cỏ lắm'),
('sến','yêu vào nó thế'),
('lofi','lost monney')

Insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values 
(2,'full 0',1,'âu sâm',5),
(3,'good job',2,'thanh tùng',5),
(1,'Goodbye Swallow',1,'thanh tùng',1)

select * from Album where Album.Rate=5

select Song.SongTitle from Album , Song 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


drop view v_Albums
select * from v_Albums



Create view v_TopSongs
as
select top 10 * from Song
order by (Hits) DESC

select * from v_TopSongs

Create proc sp_SearchByArtist
	@name nvarchar(50)
as
begin

	select * from Song
	where Song.Artists=@name

end

exec sp_SearchByArtist 'thanh tùng'




--cau 11

Create proc sp_ChangeHits
	@Id int,
	@newhit int
as
begin
	 update Song set Hits = @newhit where SongID = @Id
end

exec sp_ChangeHits 1 , 300

2021-04-13 14:00:06

create database db0413_musicStore

use db0413_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
values
	('Classical', 'Lorem ipsum dolor sit amet'),
	('Rock', 'Lorem ipsum dolor sit'),
	('Pop', 'Lorem ipsum')

insert into Album
values
	('C1', 'Lorem 1', 1, 'Artist Lorem C', 5),
	('P1', 'Goodbye Swallow', 3, 'Artist Lorem P', 3),
	('R1', 'Lorem 3', 2, 'Artist Lorem R', 1)

insert into Song
values
	('P1', 'Lorem Pop', 'Artist Lorem P', 'Lorem Aurthor Pop', 10),
	('C1', 'Lorem Classic', 'Artist Lorem C', 'Lorem Aurthor Classic', 25),
	('R1', 'Lorem Rock', 'Artist Lorem R', 'Lorem Aurthor Rock', 9)

select Title, Rate from Album where Rate = 5

select Song.SongTitle, Album.Title 'AlbumName'
from Song left join Album on Song.AlbumID = Album.AlbumID
where Album.Title = 'Goodbye Swallow'

create view v_Album as
	select Album.AlbumID, Album.Title 'the Title', MusicType.Name, Album.Rate
	from Album left join MusicType on Album.TypeID = MusicType.TypeID
select * from v_Album

create view v_TopSongs as
	select top(10) * from Song
	order by Hits desc
select * from v_TopSongs

create proc sp_SearchByArtist
	@Artist nvarchar(50)
as
begin
	select * from Song where Artists = @Artist
end
exec sp_SearchByArtist 'Artist Lorem P'

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

2021-04-13 13:50:38

use VuMinhHieu

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

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

alter table Song
Add constraint chk Check (Hits>=0)

alter table Album
add constraint df
Default 0 for Rate

alter table Album
add constraint ck check(Rate <=5)

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

insert into MusicType(Name,Description)
values
('rap','Hay qa hay'),
('Hai tet','Qua la phe'),
('pretty rap','Cung phe rat phe')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
('Robe 2020','Van Toc Anh Sang',1,'Robber',5),
('QuangTeo','Buong Hang',2,'Ateo',4),
('QuangTeoBay','Quan Tai Hu',3,'MrQteo',5)

insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
values
('Robe 2020','Van Toc Anh Sang','Robber','own',1000),
('Robe 2020','Forrest Gump','Robber','HAT',15000),
('Robe 2020','LUFFY','Robber','CP',12000),
('QuangTeo','Buong Hang','Ateo','Jesus',14000),
('QuangTeoBay','Quan Tai Hu','MrQteo','Jesus',15000)

select Album.AlbumID 'Ma Album', Album.Title 'Ten Album',Album.Rate 'Danh Gia'
from Album
where Album.Rate=5

Select Album.AlbumID 'Ten Album',Song.SongTitle 'Ten Bai Hat', Song.Artists 'Ca Si'
from Album,Song
where Album.AlbumID=Song.AlbumID
and Album.AlbumID='Robe 2020'

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

create view v_TopSongs as
select top 3* from Song
order by Hits desc

create procedure sp_SearchByArtist 
 @input nvarchar(25)
 as
begin
select*
from Song
where Song.Artists = @input
end

drop procedure sp_SearchByArtist


exec sp_SearchByArtist 'Robber'

create procedure sp_ChangeHits
@songid int,
@hits int
as
begin
update Song
set Hits = Hits + @hits
where  @songid =SongID
select*from Song

end

exec sp_ChangeHits 2,-3000

2021-04-05 03:34:47

create database Music
use Music

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,
	Tittle nvarchar(100) not null,
	TypeID int 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) references Album(AlbumID),
	SongTittle nvarchar(200) not null,
	Artists nvarchar(50),
	Author nvarchar(50),
	Hits int,
	check (Hits >=0)
)
--- index
create index IX_SongTitle on Song(SongTittle)
create index IX_Artists on Song(Artists)

insert into MusicType (Name, Description)
values
('Nhac Tre','Toi Ko Phai Nguoi May'),
('Nhac Vang','Toi La Nguoi May'),
('Nhac Rap','Toi La Nguoi')

insert into Album (AlbumID, Tittle, TypeID, Artists, Rate)
values
('F','FFF','3','Ho Quang Hieu','5')
('A','AAA','1','Son Tuong','1'),
('B','BBB','1','Noo','2'),
('C','CCC','2','Binz','3'),
('D','DDD','2','Big Daddy','4'),
('E','EEE','3','Boo','4')


insert into Song (AlbumID, SongTittle, Artists, Author, Hits)
values
('A','A1','ST1','TG1','50'),
('B','B1','Noo1','TG2','60'),
('C','C1','Binz1','TG3','70'),
('D','D1','Big1','TG4','80'),
('E','E1','Boo1','TG5','90')

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

---6. List all the albums rated level 5 (Rate = 5)
select * from Album
where Rate = 5
---7. List all the song titles of the album 
select Song.SongTittle
from Song left join Album on Album.AlbumID = Song.AlbumID

---8. 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.Tittle, MusicType.Name, Album.Rate
	from Album left join MusicType on MusicType.TypeID = Album.TypeID 

select * from v_Albums

---9. Create a view named v_TopSongs displays information about top 10 songs
---tened\downloaded
create view v_TopSongs
as
	select top 10 Song.SongTittle, Album.Tittle, Song.Hits
	from Song left join Album on Album.AlbumID = Song.AlbumID

select * from v_TopSongs

---10. Create a stored procedure named sp_SearchByArtist with input parameter is the name
-- of the singer and display all the songs that the singer presented.
create proc sp_SearchByArtist
	@Noo nvarchar(10)
as
begin
	select Song.SongTittle, Album.Tittle, Album.Artists 
	from Song left join Album on Song.AlbumID = Album.AlbumID
	where Album.Artists = @Noo
end

exec sp_SearchByArtist 'Noo'

-- 11. 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 a
song with the corresponding code.

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

select * from Song

exec sp_ChangeHits 4, 500