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


Sintaks (Syntax)

Insert Into
INSERT INTO  table_name ( field1_name ,  field2_name , …)
VALUES ( value1 ,  value2 , …)

Update
UPDATE  table_name
SET  field1_name  = [new value]
WHERE {condition}

Delete From
DELETE FROM  table_name
WHERE {condition}

 


 

Tabel Relasional
Tabel Relasional

Materi :

 

select * from MsCustomer

insert into MsCustomer
values(‘CU006′,’diaz’,’0123456789′,’terserah’)

insert into MsCustomer (CustomerId, CustomerName)
values(‘CU007′,’diaz’)

insert into MsCustomer (CustomerName,CustomerId)
values(‘diaz’,’CU008′)

–cara memindahkan data dr tabel MsCustomer ke customer :
create table customer(
CustomerId char(10) primary key,
CustomerName varchar(50)
)

insert into customer
select CustomerId, CustomerName
from MsCustomer

select * from customer

–insert 2data secara sekaligus
insert into MsCustomer (CustomerId, CustomerName)
values(‘CU009′,’diaz’),(‘CU010′,’diaz’)

drop table customer –hapus tabel customer

update MsCustomer set CustomerName =’marshal’,
CustomerGender=’Male’
where CustomerId = ‘CU006’

/1. Ubahlah CustomerName menjadi nama pertama dari CustomerName pada table MsCustomer dimana Customer tersebut pernah melakukan transaksi pada tanggal 21. Kemudian tampilkan seluruh data dari MsCustomer.(update, left, charindex, join, datepart, day)/

begin tran –singkatan begin transaction
update MsCustomer set CustomerName = LEFT(CustomerName,CHARINDEX(‘ ‘,CustomerName))
from MsCustomer a
join HeaderSalonServices b on a.CustomerId = b.CustomerId
where DAY(TransactionDate) = 21

commit
rollback –mengembalikan / kebalikan dr begin tran

update MsCustomer set CustomerName = ‘Andi’
where CustomerId=’CU001′

select * from MsCustomer

–begin tran mirip atm

–delete data di 1 tabel
begin tran
delete from MsCustomer
where CustomerId = ‘CU009’

–cara 1 join
delete MsCustomer
from MsCustomer a
join HeaderSalonServices b on a.CustomerId = b.CustomerId
where day(TransactionDate) = 21

–cara 2 join
select *
from MsCustomer a, HeaderSalonServices b
where a.CustomerId = b.CustomerId

–beda dalam hal proses ttg join, secara proses lebih cepat cara 1
–PK,FK,not null, constraint jgn lupa di create table

alter table MsCustomer
add TinggiBadan int –add[nama kolom][tipe_data]

alter table MsCustomer
drop column TinggiBadan

alter table MsCustomer
add constraint c1 check(CustomerGender in(‘Male’,’Female’))

alter table MsCustomer
drop c1

create table mahasiswa(
nim char(10)
)

alter table mahasiswa
alter column nim char(10) not null

–alter add primary key
alter table mahasiswa
add primary key(nim)

–alter drop primary key
alter table mahasiswa
drop PK__mahasisw__DF97D0EA7132C993

insert into mahasiswa values(‘1234567890’) –bisa dijalanin berkali2 karena PK sudah di drop

select * from sys.objects


Soal( Case)

 

  1. Insertlah data berikut!

(insert)

 

Table : MsStaff

StaffID StaffName StaffGender StaffPhone StaffAddress StaffSalary StaffPosition
SF006 Jeklin Harefa Female 085265433322 Kebon Jeruk Street no 140 3000000 Stylist
SF007 Lavinia Female 085755500011 Kebon Jeruk Street no 153 3000000 Stylist
SF008 Stephen Adrianto Male 085564223311 Mandala Street no 14 3000000 Stylist
SF009 Rico Wijaya Male 085710252525 Keluarga Street no 78 3000000 Stylist

 

Insert into MsStaff values

(‘SF006’,’Jeklin Harefa’,’Female’,’085265433322’,’Kebon Jeruk Street no 140’,’3000000’,’Stylist’),

(‘SF007’,’Lavinia’,’Female’,’ 085755500011’,’Kebon Jeruk Street no 153’,’3000000’,’Stylist’),

(‘SF008’,’Stephen Adrianto’,’Male’,’ 085564223311’,’Mandala Street no 14’,’3000000’,’Stylist’),

(‘SF009’,’Rico Wijaya’,’Male’,’ 085710252525’,’Keluarga Street no 78’,’3000000’,’Stylist’)

 

 

Table : HeaderSalonServices

TransactionID CustomerID StaffID TransactionDate PaymentType
TR010 CU001 SF004 2012/12/23 Credit
TR011 CU002 SF006 2012/12/24 Credit
TR012 CU003 SF007 2012/12/24 Cash
TR013 CU004 SF005 2012/12/25 Debit
TR014 CU005 SF007 2012/12/25 Debit
TR015 CU005 SF005 2012/12/26 Credit
TR016 CU002 SF001 2012/12/26 Cash
TR017 CU003 SF002 2012/12/26 Credit
TR018 CU005 SF001 2012/12/27 Debit

 

Insert into HeaderSalonServices values

(‘TR010’,’CU001’,’ SF001’,’2012/12/23’,’Credit’),

(‘TR011’,’ CU002’,’ SF006’,’ 2012/12/24’,’Credit’),

(‘TR012’,’ CU003’,’ SF007’,’ 2012/12/24’,’Cash’),

(‘TR013’,’ CU004’,’ SF005’,’ 2012/12/25’,’Debit’),

(‘TR014’,’ CU005’,’ SF007’,’ 2012/12/25’,’Debit’),

(‘TR015’,’ CU005’,’ SF005’,’ 2012/12/26’,’Credit’),

(‘TR016’,’ CU002’,’ SF001’,’ 2012/12/26’,’Cash’),

(‘TR017’,’ CU003’,’ SF002’,’ 2012/12/26’,’Credit’),

(‘TR018’,’ CU005’,’SF001’,’ 2012/12/27’,’Debit’)

 

Table : DetailSalonServices

TransactionID TreatmentID
TR010 TM003
TR010 TM005
TR010 TM010
TR011 TM015
TR011 TM025
TR012 TM009
TR013 TM003
TR013 TM006
TR013 TM015
TR014 TM016
TR015 TM016
TR015 TM006
TR016 TM015
TR016 TM003
TR016 TM005
TR017 TM003
TR018 TM006
TR018 TM005
TR018 TM007

 

            INSERT INTO DetailSalonServices VALUES

(‘TR010’, ‘TM003’),

(‘TR010’, ‘TM005’),

(‘TR010’, ‘TM010’),

(‘TR011’, ‘TM015’),

(‘TR011’, ‘TM025’),

(‘TR012’, ‘TM009’),

(‘TR013’, ‘TM003’),

(‘TR013’, ‘TM006’),

(‘TR013’, ‘TM015’),

(‘TR014’, ‘TM016’),

(‘TR015’, ‘TM016’),

(‘TR015’, ‘TM006’),

(‘TR016’, ‘TM015’),

(‘TR016’, ‘TM003’),

(‘TR016’, ‘TM005’),

(‘TR017’, ‘TM003’),

(‘TR018’, ‘TM006’),

(‘TR018’, ‘TM005’),

(‘TR018’, ‘TM007’)

 

  1. Insertlah data berikut!

(insert, dateadd, day, getdate)

Insert into HeaderSalonServices values

(‘TR019’,’CU005’,’SF004’,DATEADD(day,3,Getdate()),’Credit’)

 

Table : HeaderSalonServices

TransactionID CustomerID StaffID TransactionDate PaymentType
TR019 CU005 SF004 Didapat dari tanggal hari ini ditambah 3 hari. Credit

 

 

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