SQL Server – Đáp án bài tập CSDL – https://final-blade.com

SELECTCONCAT(FirstName,’ ‘,LastName)ASFullName,Title

FROMEmployee;

SELECTCONCAT(FirstName,’ ‘,LastName)ASFullName,Title

FROMEmployee

WHEREFirstNameLIKE’ % a % ‘;

SELECT*

FROMGenre

ORDERBYName;

SELECTCOUNT(GenreId)ASNoG

FROMGenre;

— Album id = 1

SELECT*

FROMTrack

WHEREAlbumId=1;

— Thời lượng lớn hơn 250.000 milliseconds

SELECT*

FROMTrack

WHEREMilliseconds>250000;

— Được sáng tác bởi tác giả có tên Smith

SELECT*

FROMTrack

WHEREComposerLIKE’ % Smith % ‘;

— Có định dạng ( Media Type ) là ‘ MPEG audio file ‘

SELECT*

FROMTrackINNERJOINMediaType

ONTrack.MediaTypeId=MediaType.MediaTypeId

WHEREMediaType.Name=’ MPEG audio file ‘;

SELECTDISTINCTCity

FROMCustomer

ORDERBYCity;

SELECTTOP(10)*

FROMInvoice

ORDERBYTotalDESC;

SELECT*

FROMInvoice

ORDERBYInvoiceDate

OFFSET99ROWS

FETCHFIRST1ROWONLY;

SELECT*

FROMTrackINNERJOINAlbum

ONTrack.AlbumId=Album.AlbumId

WHEREAlbum.Title=’ Supernatural ‘

ORAlbum.Title=’ Use Your Illusion I ‘;

SELECT*

FROMInvoice

WHEREInvoiceDateBETWEEN’ 2011 – 02-01 ‘AND’ 2011 – 02-28 ‘;

SELECT*

FROMInvoiceLine

WHEREInvoiceId=47;

SELECT*

FROMTrackINNERJOINGenre

ONTrack.GenreId=Genre.GenreId

WHEREGenre.NameIN(‘ Jazz ‘,’ Blues ‘,’ Pops ‘);

SELECT*

FROMTrack

WHEREComposerISNULL;

SELECTAlbumId,COUNT(TrackId)ASTrack_no

FROMTrack

GROUPBYAlbumId

HAVINGAlbumId=10;

SELECTAlbumId,COUNT(TrackId)

FROMTrack

GROUPBYAlbumId

HAVINGCOUNT(TrackId)BETWEEN18AND20

ORDERBYAlbumId;

SELECTCustomerId,CONCAT(FirstName,’ ‘,LastName)ASCustomer_Name

FROMCustomer

WHERESupportRepIdIN(

SELECTEmployeeId

FROMEmployee

WHEREPostalCodeLIKE’ T2P % ‘

);

SELECTAVG(MyAlbum.Size)

FROM(

SELECTSUM(CAST(BytesASBIGINT))ASSize

FROMTrack

GROUPBYAlbumId

)ASMyAlbum;

SELECTAlbumId,Title,

(

SELECTCOUNT(TrackId)

FROMTrack

WHERETrack.AlbumId=Album.AlbumId

)ASTrack_Count

FROMAlbum

ORDERBYTrack_CountDESC;

–KháchhàngkhôngởUSA

SELECTCustomerId,

CONCAT(FirstName,’ ‘,LastName)AS’ FullName ‘,

Country

FROMCustomer

WHERECountry< >‘ USA ‘

–KháchhàngởBrazil

SELECTCustomerId,

CONCAT(FirstName,’ ‘,LastName)AS’ FullName ‘,

Country

FROMCustomer

WHERECountry=’ Brazil ‘

SELECT

I.InvoiceId,

CONCAT(C.FirstName,’ ‘,C.LastName)AS’ FullName ‘,

I.InvoiceDate,

I.Total

FROMInvoiceASIINNERJOINCustomerASC

ONI.CustomerId=C.CustomerId

WHEREC.Country=’ Brazil ‘

ORDERBYI.TotalDESC

SELECTCONCAT(FirstName,’ ‘,LastName)AS’ FullName ‘

FROMEmployee

WHERETitleLIKE’ % Sale % ‘

SELECT

YEAR(InvoiceDate)ASInvoiceYear,

SUM(Total)ASTotal

FROMInvoice

WHEREYEAR(InvoiceDate)IN(2009,2010,2011)

GROUPBYYEAR(InvoiceDate)

SELECTCOUNT(*)

FROMInvoiceLine

WHEREInvoiceId=37;

SELECTInvoiceId,COUNT(*)ASItems

FROMInvoiceLine

GROUPBYInvoiceId

ORDERBYCOUNT(*)DESC

SELECTBillingCountry,COUNT(*)ASInvoice_No

FROMInvoice

GROUPBYBillingCountry

HAVINGCOUNT(*)>20

ORDERBYCOUNT(*)

SELECTPlaylist.Name,COUNT(PlaylistTrack.TrackId)ASTrack_No

FROMPlaylistINNERJOINPlaylistTrack

ONPlaylist.PlaylistId=PlaylistTrack.PlaylistId

GROUPBYPlaylist.Name

SELECTTOP(1)E.EmployeeId,SUM(I.Total)ASSales

FROMEmployeeASE

INNERJOINCustomerASCONE.EmployeeId=C.SupportRepId

INNERJOINInvoiceASIONC.CustomerId=I.CustomerId

WHEREYEAR(I.InvoiceDate)

=2010

GROUPBYE.EmployeeId

SELECT

EmployeeId,

CONCAT(FirstName,’ ‘,LastName)ASFullname,

Support.Clients

FROMEmployeeINNERJOIN(

SELECTSupportRepId,COUNT(*)ASClients

FROMCustomer

GROUPBYSupportRepId

)ASSupport

ONEmployee.EmployeeId=Support.SupportRepId

SELECT*

FROMCustomer

WHERECustomerIdIN(

SELECTTOP(3)CustomerId

FROMInvoice

WHEREYEAR(InvoiceDate)=2012

GROUPBYCustomerId

ORDERBYSUM(Total)DESC

)

SELECTTOP(3)Genre.Name,COUNT(*)ASSales

FROMInvoiceLine

INNERJOINTrackONInvoiceLine.TrackId=Track.TrackId

INNERJOINGenreONGenre.GenreId=Track.GenreId

GROUPBYGenre.Name

ORDERBYSalesDESC

SELECT*

FROMPlaylist

SELECTDISTINCTCountry

FROMCustomer

SELECT*

FROMTrack

WHEREComposerLIKE’ % smith % ‘ANDGenreId=3

SELECTTOP(5)*

FROMInvoice

WHEREYEAR(InvoiceDate)=2012

ORDERBYTotalDESC

SELECTSUM(total)AS’ Total Income 2012 ‘

FROMInvoice

WHEREYEAR(InvoiceDate)=2012

SELECTGenre.NameASGenre,COUNT(*)ASTrack_No

FROMTrackINNERJOINGenre

ONTrack.GenreId=Genre.GenreId

GROUPBYGenre.Name

SELECTTrack.*

FROMTrackINNERJOINGenre

ONTrack.GenreId=Genre.GenreId

WHEREGenre.Name=’ Rock ‘

SELECTAlbum.TitleASAlbum,Artist.NameASArtist

FROMAlbumINNERJOINArtist

ONAlbum.ArtistId=Artist.ArtistId

WHEREArtist.Name=’ AC / DC ‘

SELECT*

FROMGenre

SELECT*

FROMPlaylist

SELECT*

FROMArtist

SELECTDISTINCTBillingCity

FROMInvoice

SELECTDISTINCTBillingCountry

FROMInvoice

SELECTDISTINCTCountry

FROMCustomer

SELECT*

FROMCustomer

WHERECountry=’ USA ‘ORCountry=’ Brazil ‘

SELECT*

FROMCustomer

WHERECity=’ Toronto ‘ANDCountry=’ Canada ‘

SELECT*

FROMCustomer

WHEREFirstNameLIKE’ h % ‘ORFirstNameLIKE’ k % ‘

SELECT*

FROMInvoice

WHEREInvoiceDateBETWEEN’ 2012 – 05-01 ‘AND’ 2012 – 05-31 ‘

SELECT*

FROMInvoice

WHERETotalBETWEEN5AND10

SELECT*

FROMInvoice

WHEREBillingCityIN(‘ New York ‘,’ Chicago ‘,’ Toronto ‘)

SELECTTOP(5)*

FROMInvoice

ORDERBYTotalDESC

SELECTTOP(5)*

FROMTrack

ORDERBYUnitPriceDESC

SELECTTOP(5)*

FROMTrack

ORDERBYMillisecondsDESC

SELECTAVG(Total)

FROMinvoice

SELECTAVG(UnitPrice)

FROMTrack

SELECTCOUNT(*)

FROMCustomer

SELECTAlbumId,COUNT(*)

FROMTrack

GROUPBYAlbumId

SELECTGenreId,COUNT(*)

FROMTrack

GROUPBYGenreId

SELECTMediaTypeId,COUNT(*)

FROMTrack

GROUPBYMediaTypeId

SELECT*

FROMTrackINNERJOINGenre

ONTrack.GenreId=Genre.GenreId

WHEREGenre.Name=’ Pop ‘

SELECT*

FROMTrackINNERJOINAlbum

ONTrack.AlbumId=Album.AlbumId

WHEREAlbum.Title=’ Big Ones ‘

SELECT*

FROMTrackINNERJOINMediaType

ONTrack.MediaTypeId=MediaType.MediaTypeId

WHEREMediaType.Name=’ MPEG audio file ‘

SELECT*

FROMTrackINNERJOINAlbum

ONTrack.AlbumId=Album.AlbumId

SELECT*

FROMAlbumINNERJOINArtist

ONAlbum.ArtistId=Album.AlbumId

SELECT*

FROMInvoiceINNERJOINCustomer

ONInvoice.CustomerId=Customer.CustomerId

SELECTGenre.GenreId,COUNT(*)

FROMTrackINNERJOINGenre

ONTrack.GenreId=Genre.GenreId

GROUPBYGenre.GenreId

HAVINGCOUNT(*)>100

SELECTAlbum.AlbumId,COUNT(*)

FROMTrackINNERJOINAlbum

ONTrack.AlbumId=Album.AlbumId

GROUPBYAlbum.AlbumId

HAVINGCOUNT(*)>15

SELECTMediaType.MediaTypeId,COUNT(*)

FROMTrackINNERJOINMediaType

ONTrack.MediaTypeId=MediaType.MediaTypeId

GROUPBYMediaType.MediaTypeId

HAVINGCOUNT(*)

>

200