Soal UAS Sistem Basis Data

Soal UAS Sistem Basis Data

Download database di akhir postingan ini :

Kerjakansoal-soal query berikut, tuliskan query mysql jawabannya, jumlah record hasil query, dansertakan 1 (satu) contohscreenshootouputnya.

1. Berapa total pembayaran (amount) yang dilakukan customer MARY SMITH mulaitanggal 1 januari 2005 sampai 1 juli 2005

2. Tampilkanjumlahpendapatan yang diterimatokopadasetiapKategori Film selamabulanAgustustahun 2005

3. Tampilkan 3 buah film di setiapkategorinya yang paling larisdipinjamoleh customer selamatahun 2005

.

JAWABAN

1.Soal:

Berapa total pembayaran (amount) yang dilakukan customer MARY SMITH mulaitanggal 1 januari 2005 sampai 1 juli 2005 

Query:

SELECT CONCAT_WS(' ',customer.`first_name`,customer.`last_name`) AS `NamaLengkap`,SUM(payment.amount) AS `TotalBayar`
FROM customer,payment
WHERE customer.customer_id=payment.`customer_id`
AND CONCAT_WS(' ',customer.`first_name`,customer.`last_name`)='MARY SMITH'
AND payment.`payment_date` BETWEEN '2005-01-01' AND '2005-07-01';

Jumlahbaris: 1

Screenshot:

2. Soal:

Tampilkanjumlahpendapatan yang diterimatokopadasetiapKategori Film selamabulanAgustustahun 2005


Query:

SELECT category.`name`,SUM(payment.`amount`) AS `JumlahPendapatan`
FROM category,film_category,payment,rental,inventory
WHERE category.`category_id`=film_category.`category_id`
AND film_category.`film_id`=inventory.`film_id`
AND inventory.`inventory_id`=rental.`inventory_id`
AND rental.`rental_id`=payment.`rental_id`
AND payment.`payment_date` BETWEEN ’2005-08-01′ AND ’2005-08-31′
GROUP BY (category.`name`);

Jumlahbaris: 16
Screenshot:

Query Alternatif lain :

SELECT category.`name`,SUM(payment.`amount`) AS `JumlahPendapatan`
FROM category,film_category,payment,rental,inventory
WHERE category.`category_id`=film_category.`category_id`
AND film_category.`film_id`=inventory.`film_id`
AND inventory.`inventory_id`=rental.`inventory_id`
AND rental.`rental_id`=payment.`rental_id`
AND MONTH(payment.`payment_date`)=’08′
GROUP BY (category.`name`);

Jumlahbaris: 16
Screenshot:

3.   Soal:

Tampilkan 3 buah film di setiapkategorinya yang paling larisdipinjamoleh customer selamatahun 2005

Query :

SELECT category.`name`,film.`title`,COUNT(inventory.`film_id`) AS `JumlahPinjam`
FROM category,film_category,film,inventory,rental
WHERE category.`category_id`=film_category.`category_id`
AND film_category.`film_id`=film.`film_id`
AND film.`film_id` = inventory.`film_id`
AND inventory.`inventory_id` = rental.`inventory_id`
AND YEAR(rental.`rental_date`) = ’2005′
GROUP BY category.`name`,film.`title`
ORDER BY category.`name`,`JumlahPinjam` DESC;

Jumlahbaris:958
Screenshot:

Download Database

Download it – Database Soal