[Examination] Bài Thi Thực Hành SQL Server
Tags:
Phản hồi từ học viên
5
(Dựa trên đánh giá ngày hôm nay)
![Hoàng Thái Sơn [C2010L]](https://www.gravatar.com/avatar/f7030644ed6e4a7a1fd3fd27abf6ff02.jpg?s=80&d=mm&r=g)
Hoàng Thái Sơn
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
![Cao Tuấn Minh [community,C2010L]](https://www.gravatar.com/avatar/cd09dd9df2e38aac8a3f6f573ad1ba5e.jpg?s=80&d=mm&r=g)
Cao Tuấn Minh
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'
![Võ Như Việt [C2010L]](https://www.gravatar.com/avatar/fb93c99beb23339eb21f4d4ffe8981af.jpg?s=80&d=mm&r=g)
Võ Như Việt
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
![Đinh Hữu Nghĩa [community,C2010L]](https://www.gravatar.com/avatar/5cfd1df08b11f8b8a34b0528bce5cf11.jpg?s=80&d=mm&r=g)
Đinh Hữu Nghĩa
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
![Lã Đức Anh [community,C2010L]](https://www.gravatar.com/avatar/6ba958fe697787cee13bc898c4c1f7cf.jpg?s=80&d=mm&r=g)
Lã Đức Anh
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'
![Nguyễn Đông Dương [community,C2010L]](https://www.gravatar.com/avatar/6816a1f830b9f239bf733c9e324cc79a.jpg?s=80&d=mm&r=g)
Nguyễn Đông Dương
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
![Đào Mạnh Dũng [C2010L]](https://www.gravatar.com/avatar/6a111fa53fd75dc87034660a8857df16.jpg?s=80&d=mm&r=g)
Đào Mạnh Dũng
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
![Vũ Ngọc Văn [community,C2010L]](https://www.gravatar.com/avatar/3bec4690245af20ea34f68305e3e24b1.jpg?s=80&d=mm&r=g)
Vũ Ngọc Văn
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
![hieuvm0512 [community,C2010L]](https://www.gravatar.com/avatar/0cacbf21fed14b987a433597d2edc14f.jpg?s=80&d=mm&r=g)
hieuvm0512
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
![Phạm Ngọc Đại [community,C2010G]](https://www.gravatar.com/avatar/a2d762c32047edba0ef5bd2049993b20.jpg?s=80&d=mm&r=g)
Phạm Ngọc Đại
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