Tutorial 4 – REPORT CMT221 DATABASE DATABASE ORGANIZA ORGANIZ ATION AND DESIGN KHAIRUL AZIM BIN MAHAMAD
121!!
1. The management management would would like like to have have a list list of books books located located at both both locations without the duplicates. Attributes which have to be shown in the list are ISBN, title, categor and cost. !ow man records will be in the list" There are 11 records in the list. list.
#. The management management would would like like to know know the total number number of books located located at both locations $duplicates are allowed%. &our S'( statement must produce a displa as follows) SELECT COUNT(ISBN) AS TOTAL FROM ( SELECT ISBN, TITLE, CATEGORY, COST FROM BOOKS_IN_KL UNION SELECT ISBN, TITLE, CATEGORY, COST FROM BOOKS_IN_PEN ) 3. The
management management would like like to know the statistical statistical information information of cost cost price of all the distinct books located at both locations. l ocations. These include ma*imum, minimum, average, standard deviation and variance of the cost price. &our S'( statement must produce a displa as follows. +lease take note that the values for average, standard deviation and variance must be rounded to decimal places.
SELECT MAX(COST) AS MAX_COST, MIN(COST) MIN(COST) AS MIN_COST, MIN_COST, ROUND(AVG(COST),4) AS AVD_COST, ROUND(VARIANCE(COST),4) AS VARIANCE_COST, ROUND(STDDEV(COST),4) AS STDDEC_COST FROM ( SELECT ISBN, COST FROM BOOKS_IN_KL UNION SELECT ISBN, COST FROM BOOKS_IN_PEN )
. The management management would would like like to get get a list which shows shows the common common books books in both locations. All columns must be included in the displa list. SELECT * FROM BOOKS_IN_KL INTERSECT SELECT * FROM BOOKS_IN_PEN
-. The management management would would like like to get get a list which shows shows the books books at uala uala (umpur branch but not available at the +enang branch. All columns must be included in the displa list. SELECT * FROM BOOKS_IN_KL MINUS SELECT * FROM BOOKS_IN_PEN
/. The management management would would like like to get get a list which shows shows the books books at +enang +enang branch but not available at the uala (umpur branch. All columns must be included in the displa list.
SELECT * FROM BOOKS_IN_PEN MINUS SELECT * FROM BOOKS_IN_KL
0. reate a trigger which does the following) whenever a record in the BOOKS_IN_KL table is updated or a new record is inserted, the 23T4+2I3 will be auto computed as -5 more than the cost price. 6or instance, a book with cost price of 718.88 is inserted9 the 23T4+2I3 of that book would be 718.-8. CREATE OR REPLACE TRIGGER TRG_AUTO_COMPUTE_RET_PRICE AFTER INSERT OR UPDATE OF COST ON BOOKS_IN_KL BEGIN UPDATE BOOK_IN_KL SET RET_PRICE = 1.! * COST" END"
:. !ow man rows will be created when the following S'( command is e*ecuted" SELECT * FROM JOB CROSS JOIN EMPLOYEE; 198 records will be in the list.
;. 1;: records will be in the list.$true% 10.
+roduce the output based on the following S'( command.
SELECT JOB_CODE, JOB_DESCRIPTION, EMP_LNAME FROM JOB NATURAL JOIN EMPLOYEE ORDER BY EMP_LNAME;
11.+roduce the output based on the following S'( command. SELECT JOB_CODE, JOB_DESCRIPTION, EMP_LNAME FROM JOB JOIN EMPLOYEE USING(JOB_CODE) ORDER BY EMP_LNAME;
1#.+roduce the output based on the following S'( command. SELECT JOB_CODE, EMP_LNAME FROM JOB LEFT JOIN EMPLOYEE USING(JOB_CODE) ORDER BY EMP_LNAME;
1<.+roduce the output based on the following S'( command. SELECT JOB.JOB_CODE, EMP_LNAME FROM JOB LEFT JOIN EMPLOYEE ON JOB.JOB_CODE EMPLOYEE.JOB_CODE ORDER BY EMP_LNAME;
1.+roduce a list of =obs from the JOB table where the >?B4!@4!?2 is lesser than the overall average of the >?B4!@4!?2. The anticipated output is shown as follows SELECT #OB_CODE, #OB_DESCRIPTION, #OB_C$G_$OUR FROM #OB %$ERE #OB_C$G_$OUR & (SELECT AVG(#OB_C$G_$OUR) FROM #OB)
1-.+roduce a list of emploees from the EMPLOYEE table with the following displa) The rst column displas the last name in all capital letters. The third column displas the rst name in all lowercase letters. The second and the fourth columns displa the siCe of the last name and the rst name respectivel. The fth column displas the rst three characters of the rst name. SELECT UPPER (EMP_LNAME) AS L_NAME , LENGT$ (EMP_LNAME) AS LNAME_SI'E , LO%ER (EMP_FNAME) AS F_NAME , LENGT$ (EMP_FNAME) AS FNAME_SI'E , SUBSTR(EMP_FNAME, 1, ) AS FNAME_PREFIX FROM EMPLOYEE"
1/.reate a stored procedure which does the following) increase the >?B4!@4!?2 in the JOB table to 185 of the original value for +rogrammer, Sstems Analst and Database Designer. ode) CREATE OR REPLACE PROCEDURE INCREASE_#OB_C$G_$OUR AS BEGIN UPDATE #OB SET #OB_C$G_$OUR = 1.1 #OB_C$G_$OUR %$ERE #OB_CODE = ! OR #OB_CODE = !1 OR #OB_CODE = !+" END"
BEGIN INCREASE_#OB_C$G_$OUR" END"
