2021-02-18 10:13:11

create database iMusicManagement

use iMusicManagement

create table MusicType(
	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 foreign key references MusicType(TypeID) ,
	Artists nvarchar(100),
	Rate int default 0 check (Rate <= 5)
)

create table Song(
	SongID int identity(1, 1) primary key,
	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
('Hip Hop', 'street style'),
('Rock', 'crazy style'),
('Jazz','normal style' )

insert into Album(AlbumID, Title, TypeID, Artists, Rate)
values
(1122, 'The Eminem Show', 1, 'Eminem', 4),
(2233, 'Abbey Road', 2, 'The Beatles', 5),
(3344, 'Sunlight', 3, 'Herbie Hancock', 3)

insert into Song(AlbumID, SongTitle, Artists, Author, Hits)
values
(1122, 'Superman', 'Eminem', 'Eminem', 50),
(2233, 'Something', 'The Beatles', 'George Harrison', 40),
(1122, 'Without Me', 'Eminem','Eminem', 50)


select Album.Title 
from Album
where Album.Rate >= 5

select Song.SongTitle, Album.Title
from Song, Album
where Song.AlbumID = Album.AlbumID and Album.Title='The Eminem Show'

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

create view v_TopSongs as
select top 10 Song.SongTitle, Song.Hits
from Song
order by Song.Hits desc

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


declare @Artists nvarchar(50)
select	@Artists = 'Eminem'
exec sp_SearchByArtist @Artists


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



select * from v_TopSongs

2021-02-18 09:43:52

#2137.sql

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

create table Album (
	AlbumID varchar(20) Not null,
	Title nvarchar(100) Not null ,
	TypeID int,
	Artists nvarchar(100),
	Rate int default 0 check(Rate between 0 and 5)
)

create table Song (
	SongID int identity,
	AlbumID varchar(20),
	SongTitle nvarchar(200) Not null,
	Artists nvarchar(50),
	Author nvarchar(50),
	Hits int check(Hits >= 0)
)

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

alter table Album
add primary key (AlbumID)

alter table Song 
add primary key (SongID)

alter table Album
add constraint fk_TypeID foreign key (TypeID) references MusicType(TypeID)

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

insert into MusicType(Name, Description)
values
	('Country', 'is a genre of popular music that originated with blues'),
	('Blues', 'is a music genre and musical form which was originated in the Deep South of the United States'),
	('Avant-garde', 'Avant-garde music is music that is considered to be at the forefront of innovation in its field')

insert into Album(AlbumID, Title, TypeID, Artists, Rate)
values
	('132', 'qwertyuiop', 1, 'qaz', 5),
	('465', 'asdfghjkl', 2, 'wsx', 4),
	('798', 'zxcvbnm', 3, 'edc', 2)

insert into Song(AlbumID, SongTitle, Artists, Author, Hits)
values
	('132', 'plm','qaz', 'vbn', 146),
	('798', 'ijn','edc', 'yrt', 6562),
	('465', 'okm','wsx', 'hgf', 215)

select * from Album
	where Album.Rate = 5

select Song.SongTitle from Song, Album
	where Album.Title = 'asdfghjkl'
		and Album.AlbumID = Song.AlbumID

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

create view [v_TopSongs]
as
select top(10) Song.SongTitle, Song.Artists, Song.Author, Song.Hits
	from Song
		group by Song.SongTitle, Song.Artists, Song.Author, Song.Hits
		order by Song.Hits desc

create proc [sp_SearchByArtist]
	@Singer nvarchar(50),
	@Song nvarchar(200) output
as 
begin 
	select Song.SongTitle, Song.Artists
		from Song
			where Song.Artists = @Singer
end

declare @Song nvarchar(200)
exec sp_SearchByArtist 'qaz', @Song = @Song output
print @Song

select * from [v_TopSongs]
select * from [v_Albums]
select * from Album
select * from MusicType
select * from Song

2020-12-16 09:09:44

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_TopSongs1
as
select Song.SongID,song.SongTitle,Album.Rate
from Song,Album
where song.AlbumID=Album.AlbumID 

select top 10* from v_TopSongs1

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:07:23

create database assingment_sql
use assingment_sql

create table musictype(
typeid int identity (1,1) primary key,
name nvarchar (50) not null,
descripttion 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',)

create table song (
songid int identity(1,1) primary key,
albumid varchar (20) foreign key references album(albumid),
songtitle nvarchar (200) not null,
artists nvarchar (50),
author nvarchar (50),
hits int,)

create clustered index ix_songtitle on song(songtitle)
create nonclustered index ix_artists on song(artists)

insert into musictype
values
('VPOP','bai nhac rat la hay'),
('KPOP','bai nhac rat la hay'),
('CPOP','bai nhac rat la hay')

select * from musictype

insert into album
values
('R01','big city boi','1','binz','1'),
('R02','lac troi','2','son tung','1'),
('R03','gat di nuoc mat','3','noo','1')

select * from album

update  album
set rate = 5 
where albumid = 'R02';

update  album
set rate = 5 
where albumid = 'R03';

update album
set title = 'Goodbye Swallow'
where rate = '1'

update album
set title = 'Goodbye Swallow'
where rate = '5'

insert into song
values 
('R01','nhac hay thi gio tay','son tung mtp','son tung','50'),
('R02','tuong lai nho nha vo','noo','bao anh','100'),
('R03','sac duc phu doi trai','binz','snoop dog','70')

select * from song

update song
set songtitle = 'Goodbye Swallow'
where hits = '50';

update song
set songtitle = 'Goodbye Swallow'
where hits = '70';

select album.albumid , album.title , album.typeid , album.artists 
from album
where rate = '5';

select album.albumid,album.typeid,album.artists,album.rate
from album
where title = 'Goodbye Swallow'

select * from musictype
select * from album

create view v_albums
as
select album.albumid , album.title , musictype.name , album.rate
from album , musictype
where musictype.typeid = album.typeid 

select * from v_albums

create view v_topsong
as
select top 10 song.songtitle,song.artists,song.author,song.hits
from song,album,musictype
where album.typeid = musictype.typeid and song.albumid=album.albumid
order by song.hits desc

select * from v_topsong

create proc sp_searbyartists 
@art nvarchar (100)
as
select song.songtitle,song.artists,song.author,song.hits
from song,album,musictype
where album.typeid=musictype.typeid and song.albumid=album.albumid
and song.artists = @art

exec sp_searbyartists 'binz'

create proc sp_changehits
@luot int
as
select song.songtitle,song.artists,song.author,song.hits
from song,album,musictype
where album.typeid=musictype.typeid and song.albumid = album.albumid
and song.hits = @luot

exec sp_changehits '70'

2020-12-16 09:05:11

create database ThucHanh
use ThucHanh

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,
constraint FK_TypeID foreign key(TypeID) references MusicType(TypeID),
Artists nvarchar(100),
Rate int
)

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

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

insert into MusicType(Name,Description)
values
('Bolero','Nhạc Vàng'),
('RAP','Hiphop'),
('Vpop','Nhạc Việt')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
('R001','Người Đi Ngoài Phố','Bolero','Phương Anh','1'),
('R001','Người Đi Ngoài Phố','Bolero','Phương Anh','3'),
('R001','Người Đi Ngoài Phố','Bolero','Phương Anh','6')

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

2020-12-16 09:05:10

create database MusicStore
use MusicStore

create table MusicType(
TypeID int primary key identity, ---Music Type code
Name nvarchar(50) NOT NULL, ---Name of this type
Description nvarchar(100), ---Description about this type
)

insert into MusicType( Name, Description)
values
('Thanh Xuan','Namdepsnol'),
('Nang','nlklkanknsksn'),
('Mua','abdkjabkdjajks')

select * from MusicType

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) , ---Identify type of music
Artists nvarchar(100), ---Music Artist
Rate int Default '0' check (Rate <= 5),---Evaluation about the album
)

create table Album(AlbumID, Title, TypeID, Artists, Rate)
values
 (1,'bsjhbhs','2','Son Tung','5'),
 (2,'asxbjs','6','Binz','4'),
 (3,'sjasb','1','SoBin','1')
 select * from Album
 select * from Album where Rate ='5'
 select *from Album where Title='Goodbye Swallow'
create table Song(
SongID int primary key identity(1,1),---ID of song
AlbumID nvarchar(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
Hit int check (Hit  >= 0),---Number of times listened/downloaded
)
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 Song
create index IX_SongTitle on Song(SongTitle)
create index IX_Artists on Song(Artists)
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:04:13

create database MusicStore
use MusicStore

create table MusicType(
	TypeID int Primary key identity(1,1),
	Name nvarchar(50),
	Description nvarchar(100)
)
create table Album(
	AlbumID varchar(20) primary key,
	Title nvarchar(100) not null,
	TypeID int constraint fk_TypeID foreign key references MusicType(TypeID),
	Artists nvarchar(100),
	Rate int default 0
)
create table Song(
	SongID int primary key identity(1,1),
	AlbumID varchar(20) constraint fk_AlbumID foreign key references Album(AlbumID),
	SongTitle nvarchar(200) not null,
	Artists nvarchar(50),
	Author nvarchar(50),
	Hits int
)
create clustered index IX_SongTile on Song(SongTitle)
create clustered index IX_Artists on Song(Artists)
insert into MusicType(Name, Description)
values('co_dien', 'nhe_nhang'),
('pop', 'soi_dong'),
('vang', 'du_duong')
select*from MusicType

insert into Album(AlbumID, Title, TypeID, Artists, Rate)
values(23,'abc',2,'tran van a',4),
(34,'dcf',1,'tran van b',3),
(27,'edr',2,'tran van c',2)

insert into Song(AlbumID, SongTitle, Artists, Author, Hits)
values(23, 'a', 'nguyen van a', 'van a',20),
(27, 'a', 'nguyen van b', 'van b',35),
(34, 'a', 'nguyen van c', 'van c',28)

select Rate
from Album
where Rate = 5;

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

create view v_Albums as
select Song.AlbumID, Album.Title, MusicType.Name,Album.Rate
from Song, Album, MussicType

2020-12-16 09:04:11

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,
	constraint FK_TypeID foreign key(TypeID) references MusicType(TypeID),
	Artists nvarchar(100),
	Rate int 
)
create table Song(
	SongID int primary key identity(1,1),
	AlbumID varchar(20)
	constraint FK_AlbumID foreign key (AlbumID) references Album(AlbumID),
	SongTitle nvarchar(200) not null,
	Artists nvarchar(50),
  	Author nvarchar(50),
	Hits int
)
create index IX_SongTitle on Song(SongTitle)
create index IX_Artists on Song(Artists)
 
 select * from MusicType
 insert into MusicType(Name,Description)
 values
 ('Pop','youthful'),
 ('Ballab','Incense'),
 ('Rocks','exciting')
 select * from Album
 insert into Album(AlbumID,Title,TypeID,Artists,Rate)
 values
 ('T01','Goodbye Swallow',1,'Son Tung MTP',5),
 ('T02','J97 Music',2,'Jack',5),
 ('T03','Gang Gang',3,'Noo Phuoc Thinh',4)
 select * from Song
 insert into Song(AlbumID,SongTitle,Artists,Author,Hits)
 values
 ('T01','Em Cua Ngay Hom Qua','Goodbye Swallow','Son Tung MTP',30000),
 ('T02','Hong Nhan','J97 Music','Jack',40000),
 ('T03','Xin Loi Em','Gang Gang','Binz',20000)
 
 select * from Album
 where Rate = 5

 Select Album.Title,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
select * from v_Albums

create view named v_TopSongs
as
select Song.AlbumID,Song.Artists,Song.SongTitle,Song.Hits,Album.Artists,Album.Rate
from Song,Album
where Album.AlbumID = Song.AlbumID
select * from v_TopSongs

2020-12-16 09:04:02

create database Management_Music
use Management_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,
	   Title nvarchar(100) Not null,
	   TypeID int,
	   Constraint Fk_TypeID foreign key(TypeID) references MusicType(TypeID),
	   Artists nvarchar(100),
	   Rate int
)

create table Song (
       SongId int primary key identity(1,1),
	   AlbumID varchar(20),
	   Constraint Fk_AlbumID Foreign key(AlbumID) references Album(AlbumID),
	   SongTitle nvarchar(200) Not null,
	   Artists nvarchar(50),
	   Author nvarchar(50),
	   Hits int
)

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

insert into MusicType(Name,Description)
values
('Rap', 'strong'),
('Bolero', 'lightly'),
('Pop Balap', 'chill')

insert into Album(AlbumID,Title,TypeID,Artists,Rate)
values
('R001', 'Goi Do',2, 'Dan Truong', 13123),
('R002', 'Sau Tim Thieu Hong', 1, 'Phi Nhung', 21414),
('R003', 'Thuyen Hoa', 3, 'Ho Quang Hieu', 3155)

insert into Song(SongId,AlbumID,SongTitle,Artists,Autho,Hits)
values

2020-12-16 09:03:54

use music

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

create table Album (
	AlbumID varchar(20) primary key,
	Title nvarchar(100) not null,
	TypeID int foreign key references Music_Type,
	Artists nvarchar(100),
	Rate int default'0'
)

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

create index IX_Song_Title
on Song (Song_Title)

create index IX_Atists
on Song (Atists)

insert into Music_Type (TypeID, Name, Descripition)
values
(1, 'Nhạc Trẻ', 'sôi động'),
(2, 'Tình cảm', 'cảm động'),
(3, 'nhạc nước ngoài', 'kém ngoại ngữ nghe không hiểu')

insert into Album(AlbumID, Title, TypeID, Artists, Rate)
values
(1, 'Buông đôi tay nhau ra', 01, 'Sơn Tùng MTP', 5),
(2, 'Bước qua đời nhau', 02, 'Lê Bảo Bình', 5),
(3, 'lover', 03, 'Taylor Swift', 5)

insert into Song(SongID, AlbumID, Song_Title, Atists, Author, Hits)
values
(1, 3, 'lover', 'Taylor Swift', 'Taylor Swift', 142885161),
(2, 1, 'Buông đôi tay nhau ra', 'Sơn Tùng MTP', 'Sơn Tùng MTP', 105795610),
(3, 2, 'Bước qua đời nhau', 'Lê Bảo Bình', 'Khắc Việt', 104375508)

select max(Rate) as Title
from Album

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