assgn3_query - FROM CURRENT_BACKORDER, MANAGER WHERE...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
-- Course: SE6M03 -- Student: Yixian Luo -- Student No: 0901958 - CONNECT TO SE4M03; C -- QUERY 1 - SELECT WORKER.EMPLOYEENO FROM WORKER, HASNAME WHERE WORKER.MANAGERNO = HASNAME.EMPLOYEENO AND FIRST ='TONY7' AND LAST ='TONA7' AND MIDDLE IS NULL ; -- QUERY 2 - SELECT WORKER.EMPLOYEENO, LAST, FIRST, MIDDLE FROM WORKER, HASNAME WHERE WORKER.EMPLOYEENO = HASNAME.EMPLOYEENO ORDER BY LAST, FIRST, MIDDLE ; -- QUERY 3 - SELECT MANAGER.EMPLOYEENO, AREA_CODE, NUMBER FROM MANAGER, HASPHONE WHERE MANAGER.EMPLOYEENO = HASPHONE.EMPLOYEENO ; -- QUERY 4 - SELECT DISTINCT ASSEMBLYNO FROM SUBPART ORDER BY ASSEMBLYNO ; -- QUERY 5 - SELECT MANAGER.EMPLOYEENO, PARTNO, ORIG_QUANTITY, REMAINING_QUANTITY, BO_DATE, BACKORDERED_BY FROM MANAGER, CURRENT_BACKORDER WHERE MANAGER.EMPLOYEENO = CURRENT_BACKORDER.BACKORDERED_BY ; -- QUERY 6 - SELECT EMPLOYEENO, PARTNO, BO_DATE, FD FROM (SELECT MANAGER.EMPLOYEENO, PARTNO, BO_DATE, (CAST ('01/01/2000' AS DATE)) AS FD
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: FROM CURRENT_BACKORDER, MANAGER WHERE MANAGER.EMPLOYEENO = CURRENT_BACKORDER.BACKORDERED_BY) UNION (SELECT MANAGER.EMPLOYEENO, PARTNO, BO_DATE, DATE(FULFILLED) AS FD FROM OLD_BACKORDER, MANAGER WHERE MANAGER.EMPLOYEENO = OLD_BACKORDER.BACKORDERED_BY) ORDER BY EMPLOYEENO ; ;-- QUERY 7-WITH BIN_STORED(WAREHOUSEID, BINNO, VALUE) AS (SELECT WAREHOUSEID, BINNO, SUM(SIZE) FROM BATCH GROUP BY WAREHOUSEID, BINNO) SELECT BIN.WAREHOUSEID, BIN.BINNO, CAPACITY-VALUE AS REMAINING_CAPACITY FROM BIN_STORED, BIN WHERE BIN.WAREHOUSEID = BIN_STORED.WAREHOUSEID AND BIN.BINNO = BIN_STORED.BINNO ORDER BY BIN.WAREHOUSEID, BIN.BINNO ;-- QUERY 8-WITH WORK_FOR(MANAGERNO, NUMBER_MANAGED) AS (SELECT MANAGERNO, COUNT(EMPLOYEENO) FROM WORKER GROUP BY MANAGERNO) SELECT MANAGERNO, NUMBER_MANAGED FROM WORK_FOR WHERE NUMBER_MANAGED IN ( SELECT MIN(NUMBER_MANAGED) FROM WORK_FOR) ; TERMINATE;...
View Full Document

Page1 / 2

assgn3_query - FROM CURRENT_BACKORDER, MANAGER WHERE...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online