Tugas Basis Data III

Praktikum 6

Membuat Table array_test : 

>>  CREATE TABLE array_test (col1 INTEGER[5], col2 INTEGER[][],col3 INTEGER[2][2][]);

Memasukkan data ke table array_test :

>> INSERT INTO array_test VALUES ('{1,2,3,4,5}','{{1,2},{3,4}}','{{{1,2},{3,4}},{{5,6},{7,8}}}');

Melihat data dari table array_test :

>> SELECT * FROM array_test;

Melihat data col1 dari table array_test :

>> SELECT col1[4] FROM array_test;

Melihat data col2 dari table array_test :

>> SELECT col2[2][1] FROM array_test;

Melihat data col3 dari table array_test :

>> SELECT col2[1][2][2] FROM array_test;

Melihat Nama customer dengan menggunakan berbagai cara :

>> SELECT name FROM customer, salesorder WHERE customer.customer_id = salesorder.customer_id AND salesorder.order_id = 14673;

>> SELECT name FROM customer WHERE customer.customer_id = ( SELECT salesorder.customer_id FROM salesorder WHERE order_id = 14673 );

Melihat Nama employee dengan berbagai cara :

>> SELECT DISTINCT employee.name FROM employee, salesorder WHERE employee.employee_id = salesorder.employee_id AND salesorder.order_date = '19/7/1994';

>> SELECT name FROM employee WHERE employee_id IN (SELECT employee_id FROM salesorder WHERE order_date = '19/7/1994' );

Melihat daftar customer yang tidak memiliki order (tidak mempunyai pesanan) :

>> SELECT name FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM salesorder );

>> SELECT name FROM employee WHERE employee_id IN ( SELECT employee_id FROM salesorder WHERE order_date = '19/7/1994' );

>> SELECT name FROM employee WHERE employee_id = ANY ( SELECT employee_id FROM salesorder WHERE order_date = '19/7/1994' );

>> SELECT name FROM employee WHERE EXISTS ( SELECT employee_id FROM salesorder WHERE salesorder.employee_id = employee.employee_id AND order_date = '19/7/1994' );

Melihat daftar customer yang tidak memiliki order (tidak memesanan) :

>>  SELECT name FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM salesorder );

>> SELECT name FROM customer WHERE customer_id <> ALL ( SELECT customer_id FROM salesorder );

>> SELECT name FROM customer WHERE NOT EXISTS ( SELECT customer_id FROM salesorder WHERE salesorder.customer_id = customer.customer_id );

Melihat nama customer beserta order(pesanannya) :

>> SELECT name, order_id FROM customer, salesorder WHERE customer.customer_id = salesorder.customer_id UNION ALL SELECT name, NULL FROM customer WHERE customer.customer_id NOT IN (SELECT customer_id FROM salesorder) ORDER BY name;

Menghapus customer_id yang tidak memiliki pesanan :

>> DELETE FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM salesorder );

Merubah tanggal salesorder dengan menggunakan customer_id sebagai patokannya :

>> UPDATE salesorder SET ship_date = '16/11/96' WHERE customer_id = ( SELECT customer_id FROM customer WHERE name = 'Fleer Gearworks, Inc.' );

Tugas Basis Data II

Praktikum 3 .

"Joining Table"

Pertama buatlah beberapa table seperti gambar dibawah ini :

Setelah membuat beberapa table diatas, lalu masukkan data ke dalama masing-masing table tersebut seperti gambar dibawah ini :

Cara memanggil customer_id pada tabel salesorder dgn menggunakan order_id "14673".

>> SELECT customer_id FROM salesorder WHERE order_id = 14673;

Cara memanggil name pada table customer menggunakan customer_id "648".

>> SELECT name FROM customer WHERE customer_id = 648;

Cara memanggil name pada table customer dan salesorder dengan menggunakan customer_id dan order_id "14673" pd table salesorder.

>> SELECT customer.name FROM customer, salesorder WHERE customer.customer_id = salesorder.customer_id AND  salesorder.order_id = 14673;

Cara memanggil order_id untuk name pada table customer.

>> SELECT salesorder.order_id FROM salesorder, customer WHERE customer.name = 'Fleer Gearworks, Inc.' AND salesorder.customer_id = customer.customer_id;