161 lines
7.5 KiB
PL/PgSQL
Executable File
161 lines
7.5 KiB
PL/PgSQL
Executable File
-- SQL DML: SCHEMA C##ART
|
|
SET search_path TO art;
|
|
|
|
BEGIN;
|
|
-- 20 Queries
|
|
-- Q1: Select all columns and all rows from one table
|
|
SELECT * FROM USERROLE;
|
|
|
|
-- Q2: Select five columns and all rows from one table
|
|
SELECT CONTACT_ID, FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL FROM CONTACT;
|
|
|
|
-- Q3: Select all columns from all rows from one view
|
|
SELECT * FROM VIEW_AVAILABLE_INVENTORY;
|
|
|
|
-- Q4: Using a join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product
|
|
SELECT * FROM CONTACT ct
|
|
JOIN ADDRESS ad ON ad.FK_CONTACT_ID=ct.CONTACT_ID;
|
|
|
|
-- Q5: 5: Select and order data retrieved from one table
|
|
SELECT * FROM ZIP ORDER BY 1;
|
|
|
|
-- Q6: Using a join on 3 tables, select 5 columns from the 3 tables. Use syntax that would limit the output to 10 rows
|
|
SELECT iv.SKU, iv.PRODUCT_NAME,iv.BASE_PRICE,iv.LEAD_TIME_DAYS, iv.QUANTITY_AVAILABLE,
|
|
au.AUTHORIZATION_STATUS, au.EXPIRATION_DATE, au.AUTHORIZATION_DATE, c.*, au.FK_DOCUMENT_ID
|
|
FROM INVENTORY iv
|
|
JOIN authorize au ON au.AUTHORIZATION_ID = iv.FK_AUTHORIZATION_ID
|
|
JOIN CONTACT c ON c.CONTACT_ID= iv.FK_CONTACT_ID
|
|
FETCH FIRST 10 ROWS ONLY;
|
|
|
|
--Q7: Select distinct rows using joins on 3 tables
|
|
SELECT DISTINCT iv.SKU, iv.PRODUCT_NAME,iv.BASE_PRICE,iv.LEAD_TIME_DAYS, iv.QUANTITY_AVAILABLE,
|
|
au.AUTHORIZATION_STATUS, au.EXPIRATION_DATE, au.AUTHORIZATION_DATE, au.FK_CONTACT_ID, au.FK_DOCUMENT_ID,
|
|
oi.ORDER_ITEM_ID, oi.QUANTITY, oi.UNIT_PRICE, oi.TRANSACTION_ID, oi.LINE_TOTAL
|
|
FROM INVENTORY iv
|
|
JOIN AUTHORIZE au ON au.AUTHORIZATION_ID = iv.FK_AUTHORIZATION_ID
|
|
JOIN ORDER_ITEMS oi ON oi.FK_INVENTORY_ID = iv.INVENTORY_ID;
|
|
|
|
-- Q8: Use GROUP BY and HAVING in a select statement using one or more tables
|
|
SELECT iv.SKU
|
|
FROM INVENTORY iv
|
|
JOIN AUTHORIZE au ON au.AUTHORIZATION_ID = iv.FK_AUTHORIZATION_ID
|
|
JOIN ORDER_ITEMS oi ON oi.FK_INVENTORY_ID = iv.INVENTORY_ID
|
|
GROUP BY iv.SKU, iv.QUANTITY_AVAILABLE HAVING iv.QUANTITY_AVAILABLE > 0;
|
|
|
|
-- Q9: Use IN clause to select data from one or more tables
|
|
SELECT * FROM PROFILE pf JOIN USERROLE ur ON pf.PROFILE_ID = ur.FK_PROFILE_ID;
|
|
|
|
-- Q10: Select length of one column from one table (use LENGTH function)
|
|
SELECT LENGTH(ROLENAME) FROM USERROLE;
|
|
|
|
-- Q11: Delete one record from one table. ROLLBACK afterwards so that the data will not be physically removed.
|
|
SAVEPOINT sp1;
|
|
|
|
SELECT * FROM PROFILE pf JOIN USERROLE ur ON pf.PROFILE_ID = ur.FK_PROFILE_ID;
|
|
DELETE FROM USERROLE WHERE FK_PROFILE_ID=3;
|
|
SELECT * FROM PROFILE pf JOIN USERROLE ur ON pf.PROFILE_ID = ur.FK_PROFILE_ID;
|
|
|
|
ROLLBACK TO SAVEPOINT sp1;
|
|
|
|
-- Q12: Update one record from one table. demonstrate table contents before and after the UPDATE.
|
|
SELECT * FROM PROFILE WHERE PROFILE_ID=3;
|
|
UPDATE PROFILE SET ACTIVE = FALSE WHERE PROFILE_ID=3;
|
|
SELECT * FROM PROFILE WHERE PROFILE_ID=3;
|
|
|
|
ROLLBACK TO SAVEPOINT sp1;
|
|
COMMIT;
|
|
|
|
-- Q13: Plans show
|
|
SELECT * FROM PLAN pl
|
|
LEFT OUTER JOIN DOCUMENT doc ON pl.FK_DOCUMENT_ID = doc.DOCUMENT_ID;
|
|
|
|
-- Q14: show authorized suppliers whose expiration date is greater than 30 days from now.
|
|
SELECT * FROM VIEW_SUPPLIER vsu
|
|
LEFT OUTER JOIN AUTHORIZE aut ON aut.FK_CONTACT_ID = vsu.FK_CONTACT_ID
|
|
WHERE aut.AUTHORIZATION_STATUS='approved' AND aut.EXPIRATION_DATE > CURRENT_TIMESTAMP + INTERVAL '30 days';
|
|
|
|
-- Q15:
|
|
SELECT ur.ROLENAME, ct.FIRST_NAME, ct.LAST_NAME, pl.DESCRIPTION FROM SUBSCRIPTION srp
|
|
LEFT OUTER JOIN CONTACT ct ON srp.FK_CONTACT_ID = ct.CONTACT_ID
|
|
LEFT OUTER JOIN PROFILE pf ON pf.FK_CONTACT_ID = ct.CONTACT_ID
|
|
LEFT OUTER JOIN USERROLE ur ON ur.FK_PROFILE_ID = pf.PROFILE_ID
|
|
LEFT OUTER JOIN PLAN pl ON srp.FK_PLAN_ID = pl.PLAN_ID;
|
|
|
|
-- Q16:
|
|
SELECT ct.FIRST_NAME, ct.LAST_NAME, adr.STREET, z.CITY, z.STATE, z.ZIP
|
|
FROM ADDRESS adr
|
|
LEFT OUTER JOIN CONTACT ct ON adr.FK_CONTACT_ID = ct.CONTACT_ID
|
|
LEFT OUTER JOIN ZIP z ON adr.ZIP = z.ZIP;
|
|
|
|
-- Q17:
|
|
SELECT COALESCE(vws.ROLENAME, vwsp.ROLENAME, vwc.ROLENAME, vwsys.ROLENAME) AS ROLENAME,
|
|
COALESCE(vws.ACTIVE, vwsp.ACTIVE, vwc.ACTIVE, vwsys.ACTIVE) AS ACTIVE,
|
|
ct.FIRST_NAME, ct.LAST_NAME, adr.STREET, z.CITY, z.STATE, z.ZIP
|
|
FROM ADDRESS adr
|
|
LEFT OUTER JOIN CONTACT ct ON adr.FK_CONTACT_ID = ct.CONTACT_ID
|
|
LEFT OUTER JOIN ZIP z ON adr.ZIP = z.ZIP
|
|
LEFT OUTER JOIN VIEW_STAFF vws ON vws.FK_CONTACT_ID = ct.CONTACT_ID
|
|
LEFT OUTER JOIN VIEW_SUPPLIER vwsp ON vwsp.FK_CONTACT_ID = ct.CONTACT_ID
|
|
LEFT OUTER JOIN VIEW_CUSTOMER vwc ON vwc.FK_CONTACT_ID = ct.CONTACT_ID
|
|
LEFT OUTER JOIN VIEW_SYSTEMROLE vwsys ON vwsys.FK_CONTACT_ID = ct.CONTACT_ID;
|
|
|
|
-- Q18: Verify rows in tables
|
|
SELECT 'CONTACT' AS TABLE_NAME, COUNT(*) AS ROW_COUNT FROM CONTACT UNION ALL
|
|
SELECT 'ZIP', COUNT(*) FROM ZIP UNION ALL
|
|
SELECT 'ADDRESS', COUNT(*) FROM ADDRESS UNION ALL
|
|
SELECT 'PROFILE', COUNT(*) FROM PROFILE UNION ALL
|
|
SELECT 'USERROLE', COUNT(*) FROM USERROLE UNION ALL
|
|
SELECT 'STAFF', COUNT(*) FROM STAFF UNION ALL
|
|
SELECT 'DOCUMENT', COUNT(*) FROM DOCUMENT UNION ALL
|
|
SELECT 'AUTHORIZE', COUNT(*) FROM AUTHORIZE UNION ALL
|
|
SELECT 'PLAN', COUNT(*) FROM PLAN UNION ALL
|
|
SELECT 'INVENTORY', COUNT(*) FROM INVENTORY UNION ALL
|
|
SELECT 'EXHIBIT', COUNT(*) FROM EXHIBIT UNION ALL
|
|
SELECT 'ORDER_ITEMS', COUNT(*) FROM ORDER_ITEMS UNION ALL
|
|
SELECT 'INVOICE', COUNT(*) FROM INVOICE UNION ALL
|
|
SELECT 'SUBSCRIPTION', COUNT(*) FROM SUBSCRIPTION UNION ALL
|
|
SELECT 'TASK_HISTORY', COUNT(*) FROM TASK_HISTORY
|
|
ORDER BY TABLE_NAME;
|
|
|
|
-- Q19: rough check uniqueness: across tables columns
|
|
-- Q19: Unique index column counts per table
|
|
-- Oracle: user_indexes + user_ind_columns
|
|
-- PG equivalent: pg_indexes (basic) or pg_index + pg_class + pg_attribute
|
|
SELECT
|
|
t.relname AS table_name,
|
|
COUNT(DISTINCT i.indexrelid) AS unique_index_count,
|
|
COUNT(a.attname) AS unique_column_count
|
|
FROM pg_index i
|
|
JOIN pg_class t ON t.oid = i.indrelid
|
|
JOIN pg_class ix ON ix.oid = i.indexrelid
|
|
JOIN pg_attribute a ON a.attrelid = t.oid
|
|
AND a.attnum = ANY(i.indkey)
|
|
WHERE i.indisunique = TRUE
|
|
AND t.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = current_schema())
|
|
GROUP BY t.relname
|
|
ORDER BY t.relname;
|
|
|
|
-- Q20: Likely weak entity tables — tables where a PK column is also an FK column
|
|
-- Oracle: all_cons_columns + all_constraints filtered to USER
|
|
-- PG equivalent: information_schema tables
|
|
SELECT tc.table_name
|
|
FROM information_schema.table_constraints tc
|
|
JOIN information_schema.key_column_usage kcu_pk
|
|
ON kcu_pk.constraint_name = tc.constraint_name
|
|
AND kcu_pk.table_schema = tc.table_schema
|
|
JOIN information_schema.key_column_usage kcu_fk
|
|
ON kcu_fk.table_name = tc.table_name
|
|
AND kcu_fk.column_name = kcu_pk.column_name
|
|
AND kcu_fk.table_schema= tc.table_schema
|
|
JOIN information_schema.table_constraints fkc
|
|
ON fkc.constraint_name = kcu_fk.constraint_name
|
|
AND fkc.table_schema = kcu_fk.table_schema
|
|
WHERE tc.constraint_type = 'PRIMARY KEY'
|
|
AND fkc.constraint_type = 'FOREIGN KEY'
|
|
AND tc.table_schema = current_schema()
|
|
GROUP BY tc.table_name
|
|
ORDER BY tc.table_name;
|
|
|
|
COMMIT;
|
|
|