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

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s