[ File # csp10909445, License # 3089529 ]
Licensed through http://www.canstockphoto.com in accordance with the End User License Agreement (http://www.canstockphoto.com/legal.php)
(c) Can Stock Photo Inc. / z_amir

Database Praktikum pertemuan 7


Sintaks (Syntax)

Alias

Untuk  field name

            SELECT “field_name”

            AS “field_alias”

            FROM “table_name”

    Untuk table name

            SELECT “field_name”

            FROM “table_name”

            AS ”table_alias”

In

            SELECT “field_name”

            FROM “table_name”

            WHERE “field_name” IN(value1, value2, ..)

 

Exists

            SELECT “field_name”

            FROM “table_name”

            WHERE EXISTS (value1, value2, ..) 

 

Select into

            SELECT “field_name”

            INTO “new_table [IN externaldatabase]”

            FROM source 

 

Tabel relasional

Tabel Relasional
Tabel Relasional

 


Soal

/1. Tampilkan TreatmentId dan TreatmentName dimana TreatmentId adalah ‘TM001’ atau ‘TM002’.
(in)/–in mirip or 

select TreatmentId, TreatmentName

from MsTreatment

where

where TreatmentId in(‘TM001′,’TM002’)

 

/2.Tampilkan TreatmentName dan Price dimana TreatmentTypeName bukanlah ‘Hair Treatment’ maupun ‘Message / Spa’. (in, not in)/

select TreatmentName, Price

from MsTreatment

where TreamentTypeId in(

select TreatmentTypeId

from MsTreatmentType

where TreatmentTypeName not in( ‘Hair Treatment’,’Message / Spa’ )

)

–Cara ke-2 : 

select TreatmentName,Price
from MsTreatment a join MsTreatmentType b on a.TreatmentTypeId = b.TreatmentTypeId
where TreatmentTypeName not in (‘Hair Treatment’,’Message / Spa’)

–Hasilnya sama dengan cara ke-1 , tetapi kalau dalam soal, gunakan keyword yang diminta 

 

/3.Tampilkan CustomerName, CustomerPhone dan CustomerAddress dimana
Customer tersebut pernah melakukan transaksi pada hari jumat dan panjang karakter
CustomerName lebih dari 8 digit. (in, datename, weekday,len)/

–Urutan letak keyword sesuai di soal 

select CustomerName,CustomerPhone,CustomerAddress

from MsCustomer

where CustomerId in(

select CustomerId

from HeaderSalonServices

where DATENAME(weekday,TransactionDate)=’friday’

and LEN(CustomerName)>8

)

–Cara ke-2 : 

select CustomerName,CustomerPhone,CustomerAddress

from MsCustomer

where LEN(CustomerName)>8 AND  CustomerId in(

select CustomerId

from HeaderSalonServices

where DATENAME(weekday,TransactionDate)=’Friday’

)

 

/4. Tampilkan TreatmentTypeName, TreatmentName dan Price dimana CustomerName mengandung kata ‘Putra’ dan transaksi terjadi pada tanggal 22. (in, like, day)/

select TreatmentTypeName,TreatmentName,Price

from MsTreatmentType a, MsTreatment b

where a.TreatmentTypeId = b.TreatmentTypeId

and TreatmentId in(

select TreatmentId

from MsCustomer a, HeaderSalonServices b, DetailSalonServices c

where a.CustomerId = b.CustomerId and b.TransactionId = c.TransactionId

and CustomerName like ‘%Putra%’ and DAY(TransactionDate)= 22

)

 

/5.Tampilkan StaffName, CustomerName dan TransactionDate ( didapat dari TransactionDate dengan format ‘Mon dd, yyyy’) dimana digit terakhir TreatmentID merupakan genap. (convert, exists, right)/ 

select StaffName, CustomerName, TransactionDate = convert(varchar(100),TransactionDate,107)

from MsCustomer a join HeaderSalonServices b on a.CustomerId = b.CustomerId  join MsStaff c on c.StaffId = b.StaffId

where exists(

select * from DetailSalonServices d

where RIGHT(TreatmentId,1) % 2 =0  AND  b.TransactionId = d.TransactionId

)

 

/6. Tampilkan CustomerName, CustomerPhone dan CustomerAddress dimana Customer tersebut pernah dilayani oleh Staff yang panjang karakter nama-nya merupakan bilangan ganjil. (exists, len)/

select CustomerName, CustomerPhone  CustomerAddress 

from MsCustomer a

where exists (

select *

from HeadersalonServices b, MsStaff c

where a.CustomerId = b.CustomerId and b.StaffId = c.StaffId

and len(StaffName) % 2 = 1

)

 

/*7. Tampilkan ID (didapat dari tiga digit terakhir StaffId) dan Name (didapat dari nama tengah StaffName) dimana Staff tersebut tidak pernah melayani Customer berjenis kelamin ‘Male’ dan StaffName terdiri dari tiga kata atau lebih.

(right, substring, charindex, len, exists, in, not like, like)*/ 

select Id = RIGHT(StaffId,3), Name = SUBSTRING(

SUBSTRING(StaffName,CHARINDEX(‘ ‘,StaffName)+1,LEN(StaffName)),

1,

CHARINDEX(‘ ‘,StaffName)+1

)

from MsStaff a

where exists(

select * from HeaderSalonServices b

where a.StaffId =b.StaffId

and CustomerId in(

select CustomerId from MsCustomer where CustomerGender not like ‘Male’

and StaffName like ‘% % %’

)

)

 

/8.Tampilkan TreatmentTypeName, TreatmentName dan Price dimana
Price lebih besar dari rata-rata seluruh Price.
(alias subquery, avg)/

select TreatmentTypeName,TreatmentName,Price

from MsTreatmentType a join MsTreatment b on a.TreatmentTypeId = b.TreatmentTypeId ,

–alias subquery :

(

select avg(Price) as rata

from MsTreatment

)as c

where Price > c.rata

 

/9. Tampilkan StaffName, StaffPosition dan StaffSalary dimana StaffSalary merupakan salary tertinggi atau terendah. (alias subquery, max, min)/

select StaffName,StaffPosition,StaffSalary

from MsStaff a ,

(

select MAX(StaffSalary) as tertinggi , MIN(STaffSalary) as terendah

from MsStaff

)as b

where StaffSalary in(b.tertinggi,b.terendah)

 

/10. Tampilkan CustomerName, CustomerPhone, CustomerAddress dan
Count Treatment (didapat dari banyaknya Treatment yang di lakukan tiap transaksi)
dimana Count Treatment merupakan Count Treatment terbesar.
(count, alias subquery, max)/

select CustomerName, CustomerPhone, CustomerAddress,

[Count Treatment]=c.banyak

from MsCustomer a join HeaderSalonServices b  on a.CustomerId = b.CustomerId ,

(

select COUNT(TreatmentId) as banyak, TransactionId

from DetailSalonServices

group by TransactionId

) c,

(

select MAX( e.banyak as [max] )

from (

select COUNT(TreatmentId) as banyak, TransactionId

from DetailSalonServices

group by TransactionId

)e

)d

where b.TransactionId = c.TransactionId  AND c.banyak = d.max

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s