Database Praktikum pertemuan 4


Sintaks (Syntax)

Create view

Create view view_name
[ ( column , …..) ]
As subquery
[ with check option]

Drop View

Drop view [view_name]


DDL (Data Definition Language) => Create , Drop, Alter
DML (Data Manipulation Language) => Insert, Select, Delete , Update
DCL (Data Control Language) => Grant, Revoke

Tabel Relasional
Tabel Relasional

–view -> virtual tabel yang telah dibuat dari query sebelumnya dan hanya bisa dilihat saja
–view berupa Data proteksi

–create view [nama view] as [query]
create view allCustomer
as
select * from MsCustomer
select * from allCustomer

/ 1. Buatlah sebuah view dengan nama ViewBonus untuk menampilkan BonusId (didapat dari CustomerId yang 2 huruf pertamanya diganti dengan kata ‘BN’) dan CustomerName dimana panjang kata CustomerName lebih besar dari 10. (create view, stuff, len)/
–STUFF(nama variabel, mulainya dr mana,panjangnya berapa,diganti dengan kata apa)

create view ViewBonus
as
select BonusId = STUFF(CustomerID,1,2,’BN’), CustomerName
from MsCustomer
where LEN(CustomerName)>10

select BonusID, CustomerName  from viewBonus

/2. Buatlah sebuah view dengan nama ViewCustomerData untuk menampilkan Name (didapat dari kata pertama CustomerName), Address (didapat dari CustomerAddress) dan Phone (didapat dari CustomerPhone) dimana CustomerName terdiri dari dua kata atau lebih. (create view, substring, charindex) /

CREATE VIEW viewCustomerData
AS
SELECT Name = SUBSTRING(CustomerName, 1, CHARINDEX(‘ ‘, CustomerName, 1)),
Address = CustomerAddress, Phone = CustomerPhone
FROM MsCustomer
WHERE CHARINDEX(‘ ‘, CustomerName, 1)>0
–CustomerName like ‘% %’

select * from viewCustomerData

/ 3. Buatlah sebuah view dengan nama ViewTreatment untuk menampilkan TreatmentName, TreatmentTypeName, Price (didapat dari Price ditambahkan ‘Rp. ’ di awal) dimana TreatmentTypeName adalah ‘Hair Treatment’ dan Price di antara 450000 dan 800000. (create view, cast, between) /
CREATE VIEW ViewTreatment
as
select TreatmentName, TreatmentTypeName, Price = ‘Rp.’ + cast(Price as varchar(100))
from MsTreatment a, MsTreatmentType b
where a.TreatmentTypeId = b.TreatmentTypeId
and TreatmentTypeName = ‘Hair Treatment’
and Price between 450000 and 800000

select * from viewTreatment

/4. Buatlah sebuah view dengan nama ViewTransaction untuk menampilkan StaffName, CustomerName, TransactionDate (didapat dari TransactionDate dengan format ‘dd Mon yyyy’) dan PaymentType dimana tanggal terjadinya transaksi diantara 21 dan 25 dan PaymentType merupakan ‘Credit’.
(create view, convert, day, between)/
CREATE VIEW ViewTransaction
AS
Select StaffName, CustomerName,
TransactionDate = CONVERT (varchar(100),TransactionDate,106),PaymentType
From MsCustomer a,HeaderSalonServices b,MsStaff c
Where a.CustomerId = b.CustomerId
and b.StaffId = c.StaffId
and DAY(TransactionDate) between 21 and 25
and PaymentType = ‘Credit’

Select * from ViewTransaction

/5. Buatlah sebuah view dengan nama ViewBonusCustomer untuk menampilkan BonusId (didapat dari CustomerId dengan menggantikan kata ‘CU’ dengan kata ‘BN’), Name (didapat dari nama terakhir Customer dengan format huruf kecil), Day (didapat dari nama hari terjadinya transaksi) dan TransactionDate (didapat dari TransactionDate dengan format ‘mm/dd/yyyy’) dimana CustomerName terdiri dari 2 kata atau lebih dan Name mengandung huruf ‘a’.
(create view, replace, lower, substring, charindex, len, datename, convert, like)/

create view ViewBonusCustomer
as
select REPLACE(a.CustomerId,’CU’,’BN’) as BonusId ,
lower(substring(CustomerName,CHARINDEX(‘ ‘,CustomerName,2),Len(CustomerName)))  as Name,
Datename(weekday,TransactionDate) as [Day],
CONVERT(varchar(100),TransactionDate,101) as TransactionDate
from MsCustomer a join HeaderSalonServices b on a.CustomerId = b.CustomerId
where CustomerName like ‘% % %’ and CustomerName like ‘[a]%’

select * from ViewBonusCustomer

/6. Buatlah sebuah view dengan nama ViewTransactionByLivia yang menampilkan TransactionId, Date (didapat dari TransactionDate dengan format ‘Mon dd, yyyy’), dan TreatmentName dimana Staff yang menangani transaksi bernama ‘Livia Ashianti’ dan transaksi terjadi pada tanggal 21.(create view, convert, like, day)/ 

create view ViewTransactionByLivia
as
select b.TransactionId, CONVERT(varchar(100),TransactionDate,107) as [Date],
TreatmentName
from MsStaff a, HeaderSalonServices b, DetailSalonServices c, MsTreatment d
where a.StaffId = b.StaffId and b.TransactionId= c.TransactionId and c.TreatmentId = d.TreatmentId
and StaffName like ‘%Livia Ashianti%’ and day(TransactionDate) = 21

select * from ViewTransactionByLivia

/* 7. Ubahlah view ViewCustomerData menjadi menampilkan ID (didapat dari 3 digit terakhir CustomerId), Name (didapat dari kata CustomerName), Address (didapat dari CustomerAddress) dan Phone (didapat dari CustomerPhone) dimana CustomerName terdiri dari dua kata atau lebih. (alter, right, charindex)*/

alter view viewCustomerData
as
select ID = RIGHT(customerID,3), Name = customerName, [Address] = CustomerAddress,
Phone = customerPhone
from MsCustomer
where CHARINDEX(‘ ‘,CustomerName) >= 2
–and customerName like ‘% % %’ -> bisa seperti ini juga
select * from viewCustomerData

/8. Buatlah sebuah view dengan nama ViewCustomer untuk menampilkan CustomerId, CustomerName dan CustomerGender. Kemudian tambahkan data ke ViewCustomer dengan spesifikasi tabel di bawah ini:

2014-04-06_182117
//Kemudian tampilkan seluruh data dari MsCustomer.

create view ViewCustomer
as
select CustomerId, CustomerName, CustomerGender
from MsCustomer

select * from viewCustomer

insert into viewCustomer values(‘CU006′,’Benny’,’Male’)

select * from MsCustomer

/*9. Hapuslah data yang berada di dalam view ViewCustomerData yang memiliki ID ‘005’. Kemudian tampilkan seluruh data pada MsCustomer. */ 

2014-04-06_182310
select * from MsCustomer
select * from viewCustomerData
delete from viewCustomerData where ID = ‘005’

/10. Hapuslah view ViewCustomerData. /
drop view viewCustomerData

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.