Pengantar Sistem Basis Data (PSBD) LAB PERT 7 -10

Ini kunci jawaban PSBD lab waktu gw semester pendek tahun 2011 pert 7 - 10,di compile sendiri ya :p

--pert 7
1.    Tampilkan BreadName dan Price dari Bread.
select breadname,price from bread


2.    Tampilkan semua data dari Bread dimana Price lebih besar dari 40000.
 select * from bread where price >40000


3.    Tampilkan Name, Address, PhoneNumber, dan Email dari Customer dimana PhoneNumber berakhiran ‘1’.
(like)
 select Name, address, PhoneNumber, Email from customer where phonenumber like '%1'

4.    Tampilkan TypeName dari BreadType dimana TypeName mengandung huruf ‘o’ dan ‘a’.
(like)
 select typename from breadtype where typename like '%o%' and typename like '%a%'

5.    Tampilkan 5 huruf pertama dari Name dari tabel Employee, [HandPhone Number] (didapat dari PhoneNumber dengan format digit pertama PhoneNumber diganti dengan ’62-’) dari Employee
(left, substring)
 select left(name,5) as name, '+62-'+substring(PhoneNumber,2,100) as phonenumber from employee

6.    Tampilkan Name dan Addr (didapat dari kata pertama Address dari Customer) dimana Email dari Customer mengandung kata ‘yahoo’.
(like, substring, charindex)
 select name, substring(address,1,charindex(' ',address)) as addr from customer where email like '%yahoo%'

7.    Tampilkan BreadName dari Bread dengan format huruf capital dan Price in thousands (didapat dari Price dibagi 1000) dimana Price yang ditampilkan adalah Price yang memiliki kelipatan dari 5000.
(upper)
 select upper(breadname) as breadname, price/1000 as [price in thousand] from bread where price %5000=0 

8.    Tampilkan Name dengan format terbalik, PhoneNumber, Position dari Employee dimana digit terakhir PhoneNumber adalah 4. Urutkan data berdasarkan Position secara ascending.
(reverse, right, order by)
 select reverse(Name), phonenumber,position from employee where right(PhoneNumber,1)=4 order by position asc
 


9.    Tampilkan semua data dari Bread dimana jumlah huruf BreadName lebih kecil dari 15 dan BreadName terdiri dari 2 kata. Urutkan berdasarkan Price secara descending.
(len, like, order by)
 select * from bread where len(breadname)<15 and breadname like '% %' and breadname not like '% % %'order by price desc

10.    Tampilkan No (didapat dari digit terakhir dari CustomerCode), PhoneNumber, Name, Email, dan Address dari Customer dimana Name mengandung huruf ‘v’ atau ‘e’ dan digit ke-3 PhoneNumber adalah bilangan ganjil. Urutkan data berdasarkan Email secara descending.
(right, like, substring, order by)
select RIGHT(customercode,1) as No, phonenumber,name ,email ,address from Customer where Name like '%v%' and Name like '%e%' and SUBSTRING(PhoneNumber,3,1)%2=1 order by Email desc

--pert 8
1.    Tampilkan BreadName, dan Qty (didapat dari jumlah roti yg sudah terjual).
(count, group by)
select BreadName, count(price) as Qty from detailtransaction dt, bread b
where dt.breadcode = b.breadcode
group by breadName


2.    Tampilkan TotalQuantity didapat dari jumlah Quantity dari DetailTransaction dimana jenis PaymentType-nya adalah ‘Cash’.
(sum)
 select sum(quantity) as [Total Quantity] from detailtransaction td, headerTransaction th
where td.transactioncode = th.transactioncode
and paymentType = 'cash'

3.    Tampilkan Month(didapat dari bulan TransactionDate), dan Day (didapat dari tanggal transaksi terakhir dari setiap bulan) dimana Day lebih besar dari 20.
(datename, max, day, group by, having)
 select datename(month, transactiondate) as [Month], max(day(transactiondate)) as [Day] from headertransaction
group by datename(month, transactiondate)
having max(day(transactiondate)) > 20


4.    Tampilkan BreadCode, dan Average (didapat dari rata-rata Quantity transaksi penjualan) dimana digit terakhir BreadCode bernilai genap.
(avg, right, group by)
 select b.BreadCode, avg(quantity) as Average from bread b, detailtransaction td
where b.breadcode = td.breadcode
and right(b.breadcode, 1) % 2 = 0
group by b.breadcode


5.    Tampilkan Number of Transaction (didapat dari jumlah transaksi) dimana selisih bulan TransactionDate dengan  ‘1 April 2009’  lebih dari sama dengan  0.
(count, datediff, month)
 select count(td.transactioncode) [Number of Transaction] from headertransaction th, detailtransaction td
where th.transactioncode = td.transactioncode
and  datediff(month, transactiondate, '04/01/2009') >= 0


6.    Tampilkan Average Day (didapat dari rata-rata tanggal TransactionDate), CustomerCode, dan EmployeeCode dimana digit terakhir CustomerCode ditambah dengan digit terakhir EmployeeCode lebih dari 6 dan tanggal dari TransactionDate lebih dari 7.
(avg, cast, right, day, group by)
 select avg(day(transactiondate)) as [Average Day], CustomerCode, EmployeeCode from headertransaction
where cast(right(customercode, 1) as int) + cast(right(employeecode, 1) as int) > 6
and day(transactiondate) > 7
group by CustomerCode, EmployeeCode

7.    Tampilkan MinimumPrice (didapat dari nilai paling kecil dari Price), Name dari Employee, dan BreadName dimana digit pertama dari Name Employee sama dengan digit kedua BreadName.
(min, left, substring, group by)
 select min(price) as [MinimumPrice], e.[Name], BreadName from customer c, Employee e, headertransaction th, detailtransaction td, bread b
where e.employeecode = th.employeecode
and c.customercode = th.customercode
and th.transactioncode = td.transactioncode
and td.breadcode = b.breadcode
and left(e.[name], 1) = substring(b.breadname, 2, 1)
group by e.[Name], BreadName

8.    Tampilkan Date(didapat dari TransactionDate dengan format dd mon yy), Quantity dimana BreadName  mengandung huruf ‘a’ kemudian tampilkan total dari quantity.
(convert, like, sum, compute)
 select convert(varchar(100) , transactiondate, 106)as Date, Quantity from bread b, detailtransaction td, headertransaction th
where b.breadcode = td.breadcode
and th.transactioncode = td.transactioncode
and breadName like '%a%'
compute sum(quantity)


9.    Tampilkan TransactionCode, BreadCode, PaymentType, Quantity dimana Quantity lebih dari 5 kemudian tampilkan banyaknya transaksi.
(like, compute, count)
 select td.TransactionCode, BreadCode, PaymentType, Quantity from detailtransaction td, headertransaction th
where td.transactioncode = th.transactioncode
and quantity > 5
compute count(td.transactioncode)

10.    Tampilkan Name dari Customer, Email, Quantity dimana Email mengandung ‘yahoo’ dan Name mengandung huruf ‘n’. Kemudian urutkan berdasarkan Name secara ascending. Lalu Tampilkan jumlah quantity berdasarkan Name.
(sum, order by, compute by, count, like)
select c.[Name], Email, Quantity from headertransaction th, detailtransaction td, customer c
where td.transactioncode = th.transactioncode
and th.customercode = c.customercode
and email like '%yahoo%'
and c.[Name] like '%n%'
order by c.[Name]
compute sum(quantity) by c.[name]

--pert 9
1.    Tampilkan Name, PhoneNumber dari Customer yang pernah melakukan transaksi.
(in)
select [Name], PhoneNumber from customer c
where c.customercode in
(
    select customercode from headertransaction
)

2.    Tampilkan EmployeeCode, Name, PhoneNumber dimana bulan dari TransactionDate adalah bulan ke-3.
(in, month)
select EmployeeCode, [Name], PhoneNumber from employee e
where e.employeecode in
(
    select employeecode from headertransaction
    where month(transactiondate) = 3
)


3.    Tampilkan semua data dari Bread dimana Position adalah ‘Cashier’.
(in, like)
select * from bread b
where b.breadcode in
(
    select breadcode from detailtransaction td
    where td.transactioncode in
    (
        select transactioncode from headertransaction th
        where th.employeecode in
        (
            select employeecode from employee
            where position like 'Cashier'
        )
    )
)

4.    Tampilkan Name, Email dari Customer dimana Email mengandung karakter ‘-‘ dan huruf pertama dari Name pada Employee adalah ‘t’
(in, charindex, like)
select [Name], Email from customer c
where c.customercode in
(
    select customercode from headertransaction th
    where th.employeeCode in
    (
        select employeecode from employee
        where charindex('t', [name]) = 1
    )
)
and email like '%-%'

5.    Tampilkan Date (didapat dari TransactionDate dengan format mm/dd/yyyy) dan PaymentType dimana Quantity lebih dari 3.
(exists, convert)
select convert(varchar, transactiondate, 101) as Date, PaymentType from headertransaction th
where exists
(
    select * from detailtransaction td
    where th.transactioncode = td.transactioncode
    and quantity > 3
)

6.    Tampilkan semua data dari Bread dimana TypeName mengandung kata ‘cake’ dan Quantity lebih besar dari 6.
(exists, in, like)
select * from bread b
where exists
(
    select * from breadtype t
    where  b.typeCode = t.typeCode
    and TypeName like '%cake%'
)
and breadcode in
(
    select breadcode from detailtransaction td
    where quantity > 6
)

7.    Tampilkan CustomerCode, Date (didapat dari TransactionDate dengan format mm-dd-yyyy), dan EmployeeCode dimana Name dari Customer diawali karakter ‘c’ atau ‘y’ dan digit terakhir EmployeeCode habis dibagi 2 atau 5.
(exists, in, like, cast, right)
select CustomerCode, convert(varchar, transactiondate, 110) as Date, EmployeeCode  from headerTransaction th
where exists
(
    select * from customer c
    where th.customercode = c.customercode
    and [Name] like '[cy]%'
)
and employeecode in
(
    select employeecode from employee
    where cast(right(employeecode, 1) as int) % 2 = 0
    or cast(right(employeecode, 1) as int) % 5 = 0
)

8.    Tampilkan TypeName, Total (didapat dari jumlah Quantity) dimana BreadName terdiri dari 2 kata dan digit ke-6 PhoneNumber dari Customer adalah 9.
 (sum, not, alias subquery, substring, like)
select TypeName, Total from breadtype bt,
(select sum(quantity) as total from detailtransaction) tbl
where exists
(
    select * from bread b
    where b.typecode = bt.typecode
    and breadname like '% %'
    and not breadname like '% % %'
    and breadcode in
    (
        select breadcode from detailtransaction
        where transactioncode in
        (
            select transactioncode from headertransaction
            where customercode in
            (
                select customercode from customer
                where substring(phonenumber, 6, 1) = 9
            )
        )
    )
)

9.    Tampilkan BreadCode, BreadName, dan Price dimana Price lebih kecil dari rata-rata Price dan karakter pertama dari kata ke-2 BreadName adalah karakter ‘t’.
(alias subquery, avg, not, like)
select BreadCode, BreadName, Price from bread b,
(select avg(price) as av from bread b) tbl1
where price < av
and breadName like '% t%'
and not breadName like '% % t%'

10.    Tampilkan Address (didapat dari Address dengan format terbalik) dan Email dari Customer dimana bulan dari TransactionDate lebih besar dari Quantity yang terbesar.
(reverse, in, month, max, subquery)
select reverse(address) as Address, Email from customer
where customercode in
(
    select customercode from headertransaction th,
    (select max(quantity) as maks from detailtransaction) tbl1
    where month(transactiondate) > maks
)


--pert 10
1.    Tampilkan TypeCode, BreadName, dan Price (dengan format ‘Rp.’ digabung dengan Price dan diakhiri ‘ .-‘) dimana TypeName adalah ‘Cookies’.
(cast, join)
select b.typecode,b.breadname,'RP.'+cast(price as varchar)+' .-' as price from BreadType bt join bread b
on bt.typecode = b.TypeCode where TypeName = 'cookies'

2.    Tampilkan Name dan PhoneNumber dari Employee kemudian digabung dengan Name dan PhoneNumber dari Customer.
(union all)
select E.name, e.phonenumber from Employee e
union all
select c.name, c.phonenumber from Customer c

3.    Tampilkan TransactionCode, BreadName, Price, dan Quantity dimana digit pertama Price dikali dengan 8 sama dengan digit terakhir BreadCode.
(join, left, right)
select dt.transactioncode,b.breadname, b.price, dt.quantity from bread b join DetailTransaction dt
on dt.BreadCode = b.BreadCode
where LEFT(price,1)*8 = RIGHT(b.BreadCode,1)

4.    Tampilkan Employee Name (didapat dari Name dari tabel Employee), Customer Name(didapat dari Name dari tabel Customer), PaymentType dimana Position dari Employee diakhiri huruf ‘r’ dan tanggal dari TransactionDate lebih dari tanggal 10.
(join, like, day)
select E.name as [Employee Name], c.name As [Customer Name], ht.paymenttype from Employee e join HeaderTransaction ht on
e.EmployeeCode = ht.EmployeeCode
join Customer C on
C.CustomerCode = ht.CustomerCode where
Position like '%r' and DAY(TransactionDate) >10

5.    Tampilkan UnionBreadName dimana BreadName hanya terdiri dari 1 kata kemudian digabung dengan TypeName dimana TypeName terdiri dari 2 kata atau lebih. Lalu urutkan data secara descending.
(union, not, like, order by)
select breadname as UnionBreadName from bread
where BreadName not like '% %'
union
select typename from BreadType
where TypeName like '% %'
order by unionbreadname desc

6.    Tampilkan Name (didapat dari BreadName dengan format huruf besar), Total (didapat dari hasil perkalian Price dengan Quantity), TransactionDate dimana Price habis dibagi Quantity dikali 3.
(join, upper)
select UPPER(b.BreadName) as [Name], quantity * price as [Total], transactiondate from detailtransaction dt
join headertransaction ht
on dt.transactioncode = ht.transactioncode
join bread b on b.breadcode = dt.breadcode

7.    Tampilkan Name, Email Name (didapat dari Email tanpa nama domain), Quantity (didapat dari Quantity digabung ‘ pcs’) dimana Email dari Customer mengandung ‘yahoo’.
(substring, charindex, cast, like, join)
select name, SUBSTRING(email,0, CHARINDEX('@',email))as [email name],
CAST(quantity as varchar )+'pcs' as quantity from Customer c
join HeaderTransaction ht on
C.CustomerCode = ht.CustomerCode
join DetailTransaction dt on
ht.TransactionCode = dt.TransactionCode
where c.Email like '%yahoo%'

8.    Tampilkan BreadCode, TransactionCode, Date (didapat dari TransactionDate dengan format dd/mm/yy), PaymentType dimana bulan dikali tanggal dari TransactionDate lebih dari 50 dan digit terakhir dari EmployeeCode sama dengan digit terakhir dari CustomerCode.
(distinct, convert, join, month, day, right)

select distinct b.BreadCode, th.TransactionCode, convert(varchar, transactiondate, 103) as Date, PaymentType from bread b
join detailtransaction td
on td.breadcode = b.breadcode
join headerTransaction th
on th.transactioncode = td.transactioncode
where month(transactiondate) * day(transactiondate) > 50
and right(employeecode, 1) = right(customercode, 1)

9.    Tampilkan Name dari Employee dan Total (didapat dari jumlah Quantity) dimana Position dari Employee mengandung huruf ‘e’.
(join, like, group by)

select c.[Name], sum(quantity) as Total from employee c
join headertransaction th
on c.employeecode = th.employeecode
join detailtransaction td
on td.transactioncode = th.transactioncode
where position like '%e%'
group by c.[Name]

10.    Tampilkan TypeCode, TypeName, TotalPrice (didapat dari jumlah Price), dan MaximumQty (didapat dari nilai maksimum Quantity) dimana Quantity lebih besar dari 5 serta tampilkan jumlah dari TotalPrice dan jumlah dari MaximumQty.
(join,sum,max,group by,compute)

select bt.TypeCode, TypeName, sum(price) as TotalPrice, max(Quantity) as MaximumQty from breadType bt
join bread b
on b.typeCode = bt.typeCode
join detailTransaction td
on td.breadcode = b.breadcode
where Quantity > 5
group by typeName, bt.typeCode
compute sum(sum(price)), sum(max(Quantity))

Pengantar Sistem Basis Data (PSBD)

Database adalah kumpulan data yang disimpan di dalam komputer secara sistematik sehingga dapat diperiksa menggunakan suatu program komputer untuk memperoleh informasi dari basis data tersebut

view adalah tabel yang dibangun dari satu atau beberapa tabel yang sudah ada. Secara fisik, VIEW tidak membuat penyimpanan data seperti tabel biasa, melainkan hanya menyimpan referensi/pointer ke record pada tabel-tabel yang berkaitan

Komponen database: Hardware, Software, Data, Prosedur, Orang

Keuntungan DBMS: konsistensi data, Meningkatkan integritas data, Meningkatkan keamanan

Kelemahan DBMS:  Kompleksitas, biaya DBMS, Biaya konversi, Dampak kegagalan tinggi

ANTI SPARC three level:

External level: user melihat databse, menjelaskan bagian database

Conceptual level: Kumpulan view database, menjelaskan data yg didatabase dan relasinya

Internal level: perwakilan fisik database di computer, menjelaskan bagaimana data di database

Superkey:  adalah sebuah atau sekumpulan atribut yang secara unik mengidentifikasi sebuah tupel dalam tabel relasi.

Candidate Key: adalah super key yang himpunan bagian yang sebenarnya tidak ada yang menjadi super key juga.

Primary Key: adalah candidate key yang dipilih sebagai pengidentifikasi unik untuk sebuah tabel relasi

Alternate Key: adalah candidate key yang tidak dipilih sebagai primary key.

DATA MODEL: Object-based model, Record based data model, physical data model

Multi user DBMS: Teleprocessing, File server, Client server

Relational Integrity Rules

NULL:  adalah nilai sebuah atribut yang tidak diketahui atau tidak ada pada sebuah tupel dalam tabel relasi.

Entity Integrity: adalah sebuah peraturan integritas yang menyatakan bahwa setiap tabel relasi harus mempunyai sebuah primary key, dan atribut/sekumpulan atribut yang dipilih sebagai primary key harus mempunyai nilai dan nilai tersebut harus unik dan tidak NULL.

Referential Integrity: adalah sebuah peraturan integritas yang menyatakan bahwa setiap atribut sebuah tabel relasi yang menunjuk ke tabel relasi lainnya harus merupakan hubungan yang valid