2020-12-16 08:53:31

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 08:52:43

create database imusic
use imusic
create table musictype(
	id int primary key identity(1,1),
	name nvarchar(50) not null,
	description_music nvarchar(100) 
)
create table album(
	albumid varchar(20) primary key,
	title nvarchar(100) not null,
	id_musictype int references musictype(id),
	artists nvarchar(100),
	rate int default 0,
	check (rate < 6)
)
select * from album

create table song(
	id int primary key identity(1,1),
	id_album varchar(20) references album(albumid),
	songtitle nvarchar(200) not null,
	artists nvarchar(50),
	author nvarchar(50),
	hits int,
	check (hits >= 0)
)

CREATE INDEX index_songtitle ON song(songtitle)
CREATE INDEX index_artists ON song(artists)

--insert
insert into musictype(name,description_music)
values
('Pop','Nhac nhe'),
('Rock','Nhac manh me'),
('Ballad','Nhac du duong')

select * from musictype

insert into album(albumid,title,id_musictype,artists,rate)
values 
('A1','My tam vol 1',1,'My Tam',3),
('A2','My tam vol 2',2,'My Tam',4),
('A3','Cam Ly vol 1',3,'Cam Ly',4),
('A4','Buc Tuong',2,'Team Buc Tuong',4)

insert into song (id_album,songtitle,artists,author,hits)
values
('A1','Hoa my toc nau','My Tam','Hoang Ha', 7),
('A1','Bien nho','My Tam','Hoang Anh', 10),
('A2','Bong hong thuy tinh','Buc Tuong','Tran Lap', 12),
('A3','ABC','Cam Ly','Nguyen A', 8)
--List album co rate = 5
select * from album where album.rate = 5
--List song title  of album'My tam vol 1'
select song.songtitle, album.title
from song, album
where song.id_album = album.albumid and album.title = 'My tam vol 1'

--view_v_album
create view v_album as
select album.albumid, album.title, musictype.name, album.rate
from album, musictype
where album.id_musictype = musictype.id

select * from v_album

--view_v_topsong

create view v_topsong1 as
select song.songtitle, song.artists, song.author, album.rate, musictype.name 
from song, album, musictype 
where song.hits < 11 and song.id_album = album.albumid and album.id_musictype = musictype.id

select * from v_topsong1

--proc sp_searchByArtist1
create proc sp_searchByArtist1
	@songartists nvarchar(50)
as
begin	
	select song.artists, song.songtitle, album.title 'Album', album.rate, song.hits
	from song, album
	where song.id_album = album.albumid and song.artists = @songartists
end

exec sp_searchByArtist1 'My Tam'

--proc change hit
create proc sp_ChangeHit
	@songid int,
	@hit int
as
begin
	update song
	set song.hits = @hit
	where song.id = @songid
end

exec sp_ChangeHit 2, 11

2020-12-16 08:50:34

create database MusicStore
use MusicStore

create table MusicType(
	TypeID int primary key identity(1,1) not null,
	Name nvarchar(50),
	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)
)

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

insert into MusicType(Name, Description)
values
('Pop','Kpop'),
('Rock','Alternative Rock'),
('Hiphop','Alternative Hiphop')

select * from MusicType

insert into Album
values
('123', 'ABC',2,'Day6','4'),
('456', 'DEF',3,'JayZ','4'),
('789', 'GHI',1,'BTS','5'),
('246', 'Goodbye Swallow',2,'Queen','5'),
('357', 'MNO',1,'NCT','3')

select * from Album

insert into Song(AlbumID, SongTitle , Artists , Author , Hits)
values
('123', 'Shoot me','Day6','Young K',1000),
('456', 'NY SOM','JayZ','Jay',2000),
('789', 'ON','BTS','RM',3000),
('246', 'BR','Queen','Ed',2000),
('357', 'Kick it','NCT','SM',2000),
('123', 'My Day','Day6','Young K',500),
('456', 'ABC1234','JayZ','Jay',1000),
('789', 'Fire','BTS','RM',4000),
('246', 'sdfg123','Queen','Ed',6000),
('357', 'Regular','NCT','SM',5000),
('357', 'Love Song','NCT','SM',1000)

select * from Song

select * from Album where Rate = 5

select 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 * from Song
	where Artists=@Artists_name
	group by SongID, SongTitle, AlbumID, Artists,Author, Hits
END

exec sp_SearchByArtists NCT 

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

exec sp_ChangeHits 5, 2500