[ 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 2


Sintaks (Syntax) 

Select 

SELECT “field_name” FROM  table_name

Distinct

SELECT DISTINCT “field_name”

FROM  table_name

Where

SELECT “field_name”

FROM  table_name 

WHERE {condition}

Between

SELECT “field_name”

FROM  table_name 

WHERE “field_name” BETWEEN ‘value1’ AND ‘value2’

Like

SELECT “field_name”

FROM  table_name 

WHERE “field_name” LIKE {PATTERN}


 

2014-04-06_115225
Tabel Relasional

 Isi file Create + insert.sql

— Create MsCustomer
CREATE TABLE MsCustomer(
CustomerId CHAR(5) PRIMARY KEY,
CustomerName VARCHAR(50),
CustomerGender VARCHAR(10),
CustomerPhone VARCHAR(13),
CustomerAddress VARCHAR(100),
CONSTRAINT cekIDCust CHECK(CustomerId LIKE ‘CU[0-9][0-9][0-9]’)
)

— Create MsStaff
CREATE TABLE MsStaff(
StaffId CHAR(5) PRIMARY KEY,
StaffName VARCHAR(50),
StaffGender VARCHAR(10),
StaffPhone VARCHAR(13),
StaffAddress VARCHAR(100),
StaffSalary NUMERIC(11,2),
StaffPosition VARCHAR(20),
CONSTRAINT cekIDStaff CHECK(StaffId LIKE ‘SF[0-9][0-9][0-9]’)
)

— Create MsTreatmentType
CREATE TABLE MsTreatmentType(
TreatmentTypeId CHAR(5) PRIMARY KEY,
TreatmentTypeName VARCHAR(50),
CONSTRAINT cekIDTType CHECK(TreatmentTypeId LIKE ‘TT[0-9][0-9][0-9]’)
)

— Create MsTreatment
CREATE TABLE MsTreatment(
TreatmentId CHAR(5) PRIMARY KEY,
TreatmentTypeId CHAR(5) REFERENCES MsTreatmentType ON UPDATE CASCADE ON DELETE CASCADE,
TreatmentName VARCHAR(50),
Price NUMERIC(11,2),
CONSTRAINT cekIDTreat CHECK(TreatmentId LIKE ‘TM[0-9][0-9][0-9]’)
)

— Create HeaderSalonServices
CREATE TABLE HeaderSalonServices(
TransactionId CHAR(5) PRIMARY KEY,
CustomerId CHAR(5) REFERENCES MsCustomer ON UPDATE CASCADE ON DELETE CASCADE,
StaffId CHAR(5) REFERENCES MsStaff ON UPDATE CASCADE ON DELETE CASCADE,
TransactionDate DATE,
PaymentType VARCHAR(20),
CONSTRAINT cekIDTrans CHECK(TransactionId LIKE ‘TR[0-9][0-9][0-9]’)
)

— Create DetailSalonServices
CREATE TABLE DetailSalonServices(
TransactionId CHAR(5) REFERENCES HeaderSalonServices ON UPDATE CASCADE ON DELETE CASCADE,
TreatmentId CHAR(5) REFERENCES MsTreatment ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY(TransactionId, TreatmentId)
)

— Insert Data
INSERT INTO MsCustomer VALUES
(‘CU001’, ‘Franky’, ‘Male’, ‘08566543338’, ‘Daan mogot baru Street no 6’),
(‘CU002’, ‘Ernalia Dewi’, ‘Female’, ‘085264782135’, ‘Tanjung Duren Street no 185’),
(‘CU003’, ‘Elysia Chen’, ‘Female’, ‘085754206611’, ‘Kebon Jeruk Street no 120’),
(‘CU004’, ‘Brando Kartawijaya’, ‘Male’, ‘081170225561’, ‘Greenvil Street no 88’),
(‘CU005’, ‘Andy Putra’, ‘Male’, ‘087751321421’, ‘Sunter Street no 42’)

INSERT INTO MsStaff VALUES
(‘SF001’, ‘Dian Felita Tanoto’, ‘Female’, ‘085265442222’, ‘Palmerah Street no 56’, 15000000, ‘Top Stylist’),
(‘SF002’, ‘Mellisa Pratiwi’, ‘Female’, ‘085755552011’, ‘Kebon Jeruk Street no 151’, 10000000, ‘Top Stylist’),
(‘SF003’, ‘Livia Ashianti’, ‘Female’, ‘085218542222’, ‘Kebon Jeruk Street no 19’, 7000000, ‘Stylist’),
(‘SF004’, ‘Indra Saswita’, ‘Male’, ‘085564223311’, ‘Sunter Street no 91’, 7000000, ‘Stylist’),
(‘SF005’, ‘Ryan Nixon Salim’, ‘Male’, ‘085710255522’, ‘Kebon Jeruk Street no 123’, 3000000, ‘Stylist’)

INSERT INTO MsTreatmentType VALUES
(‘TT001’, ‘Hair Treatment’),
(‘TT002’, ‘Message / Spa’),
(‘TT003’, ‘Beauty Care’),
(‘TT004’, ‘Nail Treatment’),
(‘TT005’, ‘Body Treatment’)

INSERT INTO MsTreatment VALUES
(‘TM001’, ‘TT001′,’Cutting by Stylist’, 150000),
(‘TM002’, ‘TT001′,’Cutting by Top Stylist’, 450000),
(‘TM003’, ‘TT001′,’Cutting Pony’, 50000),
(‘TM004’, ‘TT001′,’Blow’, 90000),
(‘TM005’, ‘TT001′,’Coloring’, 480000),
(‘TM006’, ‘TT001′,’Highlight’, 320000),
(‘TM007’, ‘TT001′,’Japanese Perm’, 700000),
(‘TM008’, ‘TT001′,’Digital Perm’, 1100000),
(‘TM009’, ‘TT001′,’Special Perm’, 1100000),
(‘TM010’, ‘TT001′,’Rebonding Treatment’, 1100000),
(‘TM011’, ‘TT002′,’Creambath’, 150000),
(‘TM012’, ‘TT002′,’Hair Spa’, 250000),
(‘TM013’, ‘TT002′,’Hair Mask’, 250000),
(‘TM014’, ‘TT002′,’Hand Spa Reflexy’, 200000),
(‘TM015’, ‘TT002′,’Reflexy’, 250000),
(‘TM016’, ‘TT002′,’Back Theraphy Massage’, 300000),
(‘TM017’, ‘TT003′,’Make Up’, 500000),
(‘TM018’, ‘TT003′,’Make Up Wedding’, 5000000),
(‘TM019’, ‘TT003′,’Facial’, 300000),
(‘TM020’, ‘TT004′,’Manicure’, 80000),
(‘TM021’, ‘TT004′,’Pedicure’, 100000),
(‘TM022’, ‘TT004′,’Nail Extension’, 250000),
(‘TM023’, ‘TT004′,’Nail Acrylic Infill’, 340000),
(‘TM024’, ‘TT005′,’Japanese Treatment’, 350000),
(‘TM025’, ‘TT005′,’Scalp Treatment’, 250000),
(‘TM026’, ‘TT005′,’Crystal Treatment’, 400000)

INSERT INTO HeaderSalonServices VALUES
(‘TR001’, ‘CU001’, ‘SF004’, ‘2012/12/20’, ‘Credit’),
(‘TR002’, ‘CU002’, ‘SF005’, ‘2012/12/20’, ‘Credit’),
(‘TR003’, ‘CU003’, ‘SF003’, ‘2012/12/20’, ‘Cash’),
(‘TR004’, ‘CU004’, ‘SF005’, ‘2012/12/20’, ‘Debit’),
(‘TR005’, ‘CU005’, ‘SF003’, ‘2012/12/21’, ‘Debit’),
(‘TR006’, ‘CU001’, ‘SF005’, ‘2012/12/21’, ‘Credit’),
(‘TR007’, ‘CU002’, ‘SF001’, ‘2012/12/22’, ‘Cash’),
(‘TR008’, ‘CU003’, ‘SF002’, ‘2012/12/22’, ‘Credit’),
(‘TR009’, ‘CU005’, ‘SF004’, ‘2012/12/22’, ‘Debit’)

INSERT INTO DetailSalonServices VALUES
(‘TR001’, ‘TM001’),
(‘TR001’, ‘TM005’),
(‘TR002’, ‘TM010’),
(‘TR002’, ‘TM015’),
(‘TR003’, ‘TM025’),
(‘TR003’, ‘TM009’),
(‘TR004’, ‘TM001’),
(‘TR004’, ‘TM006’),
(‘TR004’, ‘TM015’),
(‘TR004’, ‘TM016’),
(‘TR005’, ‘TM016’),
(‘TR006’, ‘TM006’),
(‘TR006’, ‘TM015’),
(‘TR007’, ‘TM002’),
(‘TR007’, ‘TM005’),
(‘TR008’, ‘TM002’),
(‘TR008’, ‘TM006’),
(‘TR009’, ‘TM005’),
(‘TR009’, ‘TM006’)

— Display Data
SELECT * FROM MsCustomer
SELECT * FROM MsStaff
SELECT * FROM MsTreatment
SELECT * FROM MsTreatmentType
SELECT * FROM HeaderSalonServices
SELECT * FROM DetailSalonServices

/*
sp_msforeachtable’DROP TABLE ?’
sp_msforeachtable’SELECT * FROM ?’

DROP TABLE DetailSalonServices
DROP TABLE HeaderSalonServices
DROP TABLE MsTreatment
DROP TABLE MsTreatmentType
DROP TABLE MsCustomer
DROP TABLE MsStaff

DELETE FROM DetailSalonServices
DELETE FROM HeaderSalonServices
DELETE FROM MsTreatment
DELETE FROM MsTreatmentType
DELETE FROM MsCustomer
DELETE FROM MsStaff
*/

 


 

Cara melakukan create database :

create database PLT

Note :

Cast fungsinya mengubah tipe data

Where : Syarat (bisa disertai AND dan OR)

Like : Mencari kriteria karakter tertentu

[] : range; format : ‘[x-y]z%’

_a : Underscore; Imbuhan 1 digit di depan a, bisa lebih dari 1 underscore.

%m% : Berapapun karakter di depan sampai ketemu ‘m’ dan berapapun karakter di belakang (Intinya mencari m).

Perbedaan char dan varchar :

char(5)

varchar(5)

char : AB = A B_ _ _

(Muncul tiga digit underscore)

varchar : AB = AB

(Tiga digit sisanya kosong / tidak muncul)

Kalau bingung tekan F1 (Langsung ke Help), klik Index di kiri bawah kemudian bisa mencari keyword yang diinginkan

Format alias dapat dipakai dengan menggunakan format : Nama panjang, Inisial alias

Contoh : MsCustomer, a

Selanjutnya untuk memanggil MsCustomer dapat mengetik ‘a’ saja

CONVERT : (Tipe data, nama table, kode(didapat dari F1 Help Index bagian CONVERT))

Contoh : CONVERT(varchar(100), transactionDate, 107) >> mon dd yyyy

Fungsi ‘in’ : Untuk syarat yang lebih dari 1

SUBSTRING : >> Memotong text dari tempat tertentu >> 3 parameter wajib

(Object (Kata), mulai dari berapa, panjang berapa (harus diisi)) -> return varchar

CHARINDEX : >> Dimulai dari karakter mana >> 2 parameter wajib, max 3

(Apa yang dicari, object, dimulai posisi berapa(tidak harus diisi, defaultnya 1)) -> return int

LEFT(Nama table, berapa banyak huruf yang mau diambil dari kiri)

RIGHT (Nama table, berapa banyak huruf yang mau diambil dari kanan)

DATEDIFF(DAY/MONTH/YEAR, Nama Table, ‘Format tanggal (ikutin datanya)’) < x (Terjadi selisih x hari sebelum tanggal tsb)

UPPER (Kalimat yang mau dikapitalin)


 

Soal (Case) 

–1.Tampilkan semua kolom dari MsStaff dimana StaffGender adalah ‘Female’.

select *

from MsStaff

where StaffGender = ‘female’

/*2.Tampilkan StaffName dan StaffSalary (didapat dari StaffSalary dengan menambahkan kata ‘Rp. ’ diawalnya) 

dimana StaffSalary lebih besar atau sama dengan 10.000.000 dan StaffName mengandung huruf ‘m’.(cast, like)*/

select StaffName, ‘Rp. ‘ + CAST(StaffSalary as varchar(100))

from MsStaff

where StaffSalary >= 10000000 and StaffName like ‘%m%’

/*3.Tampilkan TreatmentName dan Price dimana TreatmentTypeName

adalah ‘message / spa’ atau ‘beauty care’.(in)*/

select TreatmentName, Price

from MsTreatment a, MsTreatmentType b  //a dan b -> alias

where a.TreatmentTypeId = b.TreatmentTypeId

and TreatmentTypeName in(‘message / spa’,’beauty care’)

/4.Tampilkan StaffName, StaffPosition dan
TransactionDate (dengan format ‘Mon dd,yyyy’)
dimana StaffSalary diantara 7000000 dan 10000000.(convert, between)/

select StaffName, StaffPosition,

TransactionDate = CONVERT(varchar(100), TransactionDate,107)

from MsStaff a, HeaderSalonServices b

Where a.StaffId = b.StaffId

AND StaffSalary between 7000000 and 10000000

/5. Tampilkan Name (didapat dari nama pertama CustomerName),
Gender (didapat dari huruf pertama CustomerGender) dan PaymentType dimana PaymentType
adalah ‘Debit’.
(substring, charindex, left)/

–SUBSTRING(apa yg dipotong, mulai dr berapa, panjang berapa)->return varchar

–CHARINDEX(apa yang dicari,object,dimulai posisi berapa)->return int

 

SELECT SUBSTRING(customername,1,CHARINDEX(‘ ‘,customername,1)) as Name,

LEFT(customerGender,1) as Gender, paymentType

from MsCustomer a, HeaderSalonServices b

WHERE a.CustomerId = b.CustomerID

AND PaymentType = ‘Debit’

/6.Tampilkan Initial (didapat dari huruf pertama CustomerName dan
huruf pertama dari kata kedua CustomerName dengan format huruf kapital) dan
Day (didapat dari hari terjadinya transaksi) dimana selisih tanggal terjadinya transaksi
dengan tanggal 24 Desember 2012 kurang dari tiga hari.
(upper, left, substring, charindex, datename, weekday, datediff, day)/

select Upper(LEFT(CustomerName,1)+

SUBSTRING(CustomerName,CHARINDEX(‘ ‘,CustomerName)+1,1)) as Initial,

DATENAME (weekday,TransactionDate) as [Day]

from MsCustomer a, HeaderSalonServices b

where a.CustomerId = b.CustomerId

AND DATEDIFF(day,TransactionDate,’2012-12-24’)< 3

 

/*7. Tampilkan TransactionDate dan CustomerName (didapat dari nama terakhir CustomerName) dimana CustomerName terdiri dari dua kata atau lebih dan customer tersebut pernah melakukan transaksi pada hari sabtu.

(right, charindex, reverse, like, datename, weekday)*/ 

select TransactionDate, RIGHT(CustomerName,CHARINDEX(‘ ‘,reverse(CustomerName),1))

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

where CustomerName like ‘% %’ and

DATENAME(weekday,TransactionDate) =’saturday’

 

/*8. Tampilkan StaffName, CustomerName, CustomerPhone (didapat dari CustomerPhone dengan mengganti bilangan ‘0’ dengan ‘+62’) dan CustomerAddress dimana CustomerName diawali huruf vokal (a,i,u,e,o) dan StaffName terdiri dari tiga kata atau lebih. (replace, like) */  

select StaffName, CustomerName,

replace(CustomerPhone,’0′,’+62′) as CustomerPhone,  CustomerAddress

from MsStaff a join HeaderSalonServices b on a.StaffId = b.StaffId

join MsCustomer c on b.CustomerId = c.CustomerId

where CustomerName like'[a,i,u,e,o]%’

and StaffName like ‘% % %’

 

/9. Tampilkan StaffName,TreatmentName dan Term of Transaction (didapat dari selisih tanggal transaksi dengan 24 Desember 2012) dimana panjang karakter dari TreatmentName lebih besar dari 20 atau TreatmentName terdiri dari dua kata atau lebih. (datediff, day, len, charindex)

select StaffName, TreatmentName,

Datediff(day,TransactionDate,’2012-12-24′) as [Term of Transaction]

from MsStaff a, MsTreatment d, HeaderSalonServices b , DetailSalonServices c

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

and  c.TreatmentId = d.TreatmentId

and LEN(TreatmentName) > 20 or CHARINDEX(‘ ‘,TreatmentName,1)>= 2

 

/10. Tampilkan TransactionDate, CustomerName, TreatmentName, Discount (didapat dari 20% Price dengan format bilangan bulat) dan PaymentType dimana transaksi terjadi pada tanggal 22. (cast, day)

select TransactionDate, CustomerName,TreatmentName,

cast(0.2*Price as numeric(11,2)) as Discount, PaymentType

from MsCustomer a, HeaderSalonServices b, DetailSalonServices c, MsTreatment d

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

and c.TreatmentId = d.TreatmentId and DAY(TransactionDate) = 22

 

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