Basis Data

Tugas 2

1. Tampilkan rincian lengkap semua hotel

SELECT
PropertyForRent.propertyNo AS 'Property No.'
,CONCAT(PropertyForRent.street, ' ', PropertyForRent.city, ' ', PropertyForRent.Postcode) AS 'Property Address'
,PropertyForRent."type" AS 'Property Type'
,PropertyForRent.rent AS 'Rent Rate'
,BRANCH.branchNo AS 'Registered Branch No'
,CONCAT(PrivateOwner.fname, ' ', PrivateOwner.lname) AS 'Owner Name'
,PrivateOwner.telNo AS 'Owner Tel Number'
,Viewing.comments AS 'Customer Review'
FROM
PropertyForRent
JOIN PrivateOwner
ON PropertyForRent.ownerNo=PrivateOwner.ownerNo
JOIN Viewing
ON PropertyForRent.propertyNo=Viewing.propertyNo
JOIN BRANCH
ON PropertyForRent.branchNo=BRANCH.branchNo

1. Tampilkan rincian lengkap semua hotel

2. Tampilkan rincian lengkap dari semua hotel di London.

SELECT 
PropertyForRent.propertyNo AS 'Property No.'
,CONCAT(PropertyForRent.street, ' ', PropertyForRent.city, ' ', PropertyForRent.Postcode) AS 'Property. Address'
,PropertyForRent."type" AS 'Property Type'
,PropertyForRent.rent AS 'Rent Rate'
,CONCAT(STAFF.fName, ' ',STAFF.lName) AS 'Staff In Charge'
,BRANCH.branchNo AS 'Registered Branch No'
,CONCAT(PrivateOwner.fname, ' ', PrivateOwner.lname) AS 'Owner Name'
,PrivateOwner.telNo AS 'Owner Tel Number'
,Viewing.comments AS 'Customer Review'
FROM
PropertyForRent
JOIN PrivateOwner
ON PropertyForRent.ownerNo=PrivateOwner.ownerNo
JOIN Viewing
ON PropertyForRent.propertyNo=Viewing.propertyNo
JOIN BRANCH
ON PropertyForRent.branchNo=BRANCH.branchNo
JOIN STAFF
ON PropertyForRent.staffNo=STAFF.staffNo
WHERE PropertyForRent.city='London'

2. Tampilkan rincian lengkap dari semua hotel di London.

3. Tampilkan nama dan alamat dari semua tamu yang tinggal di London, nama diurutkan berdasarkan abjad.

SELECT 
CONCAT(Client.fName,' ',Client.lName) AS 'Client Name'
,PropertyForRent.street AS 'Rent Property Street'
,PropertyForRent.city AS 'Rent Property City'
,PropertyForRent.Postcode AS 'Rent Property Postcode'
FROM Client
JOIN Registration
ON Client.clientNo=Registration.clientNo
JOIN PropertyForRent
ON Registration.staffNo=PropertyForRent.staffNo
WHERE PropertyForRent.city='London'
ORDER BY Client.fName ASC

4. Tampilkan semua ruang ganda atau keluarga dengan harga di bawah £ 40,00 per malam, dalam urutan harga.

5. Berapa banyak hotel yang ada?

SELECT COUNT(*) FROM PropertyForRent 

6. Berapa harga rata-rata kamar?

SELECT AVG(PropertyForRent.rent) AS 'Average Room Rent Price'
FROM PropertyForRent 

6. Berapa harga rata-rata kamar?

7. Berapa total pendapatan per malam dari semua kamar ganda?

8. Berapa banyak tamu yang berbeda telah membuat pemesanan untuk Agustus?

9. Menyisipkan baris ke masing-masing tabel tersebut.

10. Update harga semua kamar sebesar 5%.