Assignment Week 2 SQL

[UsepSumaryana]

Task Week#2 - SQL

1. Melakukan Transaction SQL : https://github.com/UsepSumaryana/training-sql/issues/1#issuecomment-1544464714
2. Membuat table struktur dari form yang disediakan : https://github.com/UsepSumaryana/training-sql/issues/1#issuecomment-1544558878
3. Eksekusi script sql subquery pada modul training: https://github.com/UsepSumaryana/training-sql/issues/1#issuecomment-1544635365

[UsepSumaryana]

Melakukan Transaction SQL

SET autocommit=0;

a. Insert 5 record & commited.

START TRANSACTION;

INSERT INTO offices (officeCode, city, phone, addressLine1, addressLine2, state, country, postalCode, territory)
  VALUES (8, 'Burnham', '707-862-0868', '36557 Union Crossing', 'Room 1928', NULL, 'New Zealand', '7600', 'NZ'),
  (9, 'Tianya', '508-742-4665', '05 Graedel Terrace', 'Apt 174', NULL, 'China', '2341', 'CN'),
  (10, 'Dalan', '790-328-7138', '3032 Spenser Point', 'Apt 869', NULL, 'China', '1124', 'CN'),
  (11, 'Malonty', '233-233-1130', '19206 Banding Terrace', '12th Floor', NULL, 'Czech Republic', '3821', 'CZ'),
  (12, 'Ezeiza', '238-733-7589', '1 Green Trail', 'PO Box 47673', NULL, 'Argentina', '1804', 'AR');

COMMIT;

b. Insert/update/delete di 1 sesi dan read table di sesi yg berbeda.

START TRANSACTION;

UPDATE offices SET city = 'JAKARTA';

SESI 1 :

SESI 2 :

ROLLBACK;

c. Rollback ke savepoint.

START TRANSACTION;

DELETE FROM Offices WHERE officeCode = 12;

SAVEPOINT save_point_1;

DELETE FROM Offices WHERE officeCode = 11;

ROLLBACK TO SAVEPOINT save_point_1;

DELETE FROM Offices WHERE officeCode = 10;

COMMIT;

[UsepSumaryana]

Membuat table struktur dari form yang disediakan

[UsepSumaryana]

Eksekusi script sql subquery pada modul training

a. SELECT statement

SELECT
  *
FROM customers c
WHERE customerNumber IN (SELECT
  customerNumber
FROM payments
WHERE amount > 100000);

b. INSERT statement

INSERT INTO customers_backup SELECT * FROM customers WHERE customerNumber BETWEEN 100 AND 120;

c. UPDATE statement

UPDATE orders
SET STATUS = 'On Hold'
WHERE customerNumber IN (SELECT
  customerNumber
FROM customers
WHERE country = 'USA');

d. DELETE statement

DELETE FROM payments WHERE customerNumber IN (SELECT customerNumber FROM customers WHERE creditLimit > 200000);

e. Filter dari column dengan agregasi nilai paling besar

SELECT * FROM customers c WHERE creditLimit = (SELECT MAX(creditLimit) FROM customers c2);

f. Query dari Subquery sebagai source data

SELECT
  sales.firstName,
  sales.lastName,
  office.city
FROM (SELECT
  firstName,
  lastName,
  officeCode
FROM employees e
WHERE jobTitle = 'VP Sales') AS sales
JOIN offices office
  ON sales.officeCode = office.officeCode

g. Combine query UNION

SELECT
  e.firstName
FROM employees e
UNION
SELECT
  c.contactFirstName
FROM customers c;

h. Combine query INTERSECT

Karena di mysql tidak ada intersect di ubah menggunakan distinct dan join

SELECT DISTINCT 
   customerNumber 
FROM orders o 
   INNER JOIN customers c USING(customerNumber);