tugas basis data 3

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 );

Correlated subquery

>>  SELECT f1.firstname, f1.lastname, f1.age  FROM friend f1, friend f2  WHERE f1.state = f2.state  GROUP BY f2.state, f1.firstname, f1.lastname, f1.age   HAVING f1.age = max(f2.age)  ORDER BY firstname, lastname;

>> SELECT f1.firstname, f1.lastname, f1.age FROM friend f1 WHERE age = (  SELECT MAX(f2.age) FROM friend f2  WHERE f1.state = f2.state  )  ORDER BY firstname, lastname;

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.' );

>> INSERT INTO customer (name, city, state, country)  SELECT trim(firstname) || ' ' || lastname, city, state, 'USA' FROM friend;

>> SELECT firstname, lastname, city, state  INTO newfriend FROM friend; SELECT

 >> \d newfriend Table "newfriend"

>> SELECT * FROM newfriend ORDER BY firstname;