[Pemrograman Basis Data] Pembahasan Quiz Kajian 2 : Fungsi Agregasi, Join, View dan Set Operator

Gambar Schema HR dan OE

Berdasarkan Schema OE pada gambar diatas, kerjakan soal-soal di bawah ini!

NOMOR GANJIL :

1. Tampilkan rata-rata jumlah transaksinya untuk setiap nama customer!

            SELECT AVG(orders.order_total),
                   customers.cust_first_name
            FROM orders JOIN customers
            USING (customer_id)
            GROUP BY customers.cust_first_name;

2. Buatlah view untuk menampilkan nama Gudang, quantity on hand dan nama produknya untuk harga produk antara 1000 sampai 2500 diurutkan berdasarkan nama Gudang!

            CREATE VIEW warehouse_name as 
            SELECT warehouse_name, quantity_on_hand, product_name 
            FROM warehouse JOIN inventories 
            USING (warehouse_id) 
            JOIN product_information 
            USING(product_id) 
            WHERE list_price between 1000 and 2500 
            ORDER BY warehouse_name;

3. Tampilkan id produk, harga unit dari tabel order items dan id produk, list price dari tabel product information menggunakan perintah UNION/UNION ALL walaupun ada duplikasi!

            SELECT product_id 'id produk', unit_price 'harga unit' 
            FROM order_items 
            UNION ALL
            SELECT product_id, list_price
            FROM product_information;

 

NOMOR GENAP :

1. Tampilkan jumlah barang terjual untuk setiap nama produk!

         SELECT SUM(quantity),product_name 
         FROM order_items JOIN PRODUCT INFORMATION 
         USING(PRODUCT_ID) 
         GROUP BY (PRODUCT_NAME);

2. Buatlah view untuk menampilkan nama customer, tanggal order, nama produk, jumlah order per produk untuk order pada bulan Mei sampai Juni!

         CREATE VIEW laporan AS 
         SELECT CONCAT(cust_first_name," ",cust_last_name) AS 'Nama Customer', 
                order_date AS 'Tanggal Order',product_name AS 'Nama Produk', 
                quantity AS 'Jumlah order per produk' 
         FROM CUSTOMERS JOIN ORDER 
         USING(customer_id) JOIN ORDER_ITEM 
         USING(order_id) JOIN PRODUCT_INFORMATION 
         USING(product_id) 
         WHERE month(order_date)>='05' AND month(order_date)<='06';

3. Tampilkan id customer dari tabel orders dan tabel customers menggunakan perintah UNION/UNION ALL dengan menghilangkan duplikasi!

          SELECT customer_id from orders
          UNION
          SELECT customer_id from customers;