Praktikum 6

1. Buat table array_test dengan mengetikan syntax CREATE TABLE array_test (col1 INTEGER[5], col2 INTEGER[][],col3 INTEGER[2][2][]);

2. Masukan data ke table array_test dengan mengetikan syntax INSERT INTO array_test VALUES ('{1,2,3,4,5}','{{1,2},{3,4}}','{{{1,2},{3,4}},{{5,6},{7,8}}}');

3. Lihat data dari tabel array_test dengan mengetikan syntax SELECT * FROM array_test;

4. Lihat data col1 dari tabel array_test dengan mengetikan syntax SELECT col1[4] FROM array_test;

 

5. Lihat data col1 dari tabel array_test dengan mengetikan syntax SELECT col2[2][1] FROM array_test;

6. Lihat data col1 dari tabel array_test dengan mengetikan syntax SELECT col3[1][2][2] FROM array_test;

7. Lihat data nama customer dengan menggunakan berbagai macam syntax

- SELECT name FROM customer2, salesorder WHERE customer2.customer_id = salesorder.customer_id AND salesorder.order_id = 14673;

- SELECT name FROM customer2 WHERE customer2.customer_id = ( SELECT salesorder.customer_id FROM salesorder WHERE order_id = 14673 );


8. Lihat data nama employee dengan menggunakan berbagai macam syntax

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

 

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

9. Lihat data daftar customer yang tidak memiliki order dengan menggunakan berbagai macam syntax

- SELECT name FROM customer2 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 = '7/19/1994' );

 




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

 

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


 10. Lihat data daftar customer yang tidak memesan dengan berbagai macam syntax

- SELECT name FROM customer2 WHERE customer_id NOT IN ( SELECT customer_id FROM salesorder );

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

- SELECT name FROM customer2 WHERE NOT EXISTS ( SELECT customer_id FROM salesorder WHERE salesorder.customer_id = customer2.customer_id );

11.Lihat nama customer beserta order (pesanan) dengan memasukan syntax SELECT name, order_id FROM customer2, salesorder WHERE customer2.customer_id = salesorder.customer_id UNION ALL SELECT name, NULL FROM customer2 WHERE customer2.customer_id NOT IN (SELECT customer_id FROM salesorder) ORDER BY name;

12. Hapus customer_id yang tidak memiliki order dengan cara memasukan syntax DELETE FROM customer2 WHERE customer_id NOT IN ( SELECT customer_id FROM salesorder );

13. Ubah tanggal salesorder (customer_id sebagai patokan) dengan memasukan syntax UPDATE salesorder SET ship_date = '16/11/96' WHERE customer_id = ( SELECT customer_id FROM customer WHERE name = 'Fleer Gearworks, Inc.' );