Unisa Study Notes



INF3707 May/June 2014Section A1.D2.D3.E4.D5.B6.C7.B (This is incorrectly marked as A in the textbook or it is a trick question)8.B9.A10.DSection BQuestion 2SELECTLAST_NAME,JOB_ID,HIRE_DATEFROMEMPLOYEESWHERELAST_NAME IN ('Matos','Taylor')ORDER BY HIRE_DATE;Question 3SELECTLAST_NAME,ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) MOTNTHS_WORKEDFROMEMPLOYEESORDER BY MONTHS_BETWEEN(SYSDATE,HIRE_DATE);Question 4SELECTLAST_NAME,NVL(TO_CHAR(COMMISSION_PCT),'No Commission') COMMFROMEMPLOYEES;Question 5SELECTMAX(SALARY) - MIN(SALARY) DIFFERENCEFROMEMPLOYEES;Question 6SELECTE.LAST_NAME,E.JOB_ID,E.DEPARTMENT_ID,D.DEPARTMENT_NAMEFROMEMPLOYEES E JOIN DEPARTMENTS DON D.DEPARTMENT_ID = E.DEPARTMENT_IDJOIN LOCATIONS L ON L.LOCATION_ID = D.LOCATION_IDWHERE L.City = 'Toronto';Question 7-- USING A SET OPERATORSELECT DISTINCTE.LAST_NAME,D.DEPARTMENT_ID,D.DEPARTMENT_NAMEFROMEMPLOYEES ELEFT JOIN DEPARTMENTS DON D.DEPARTMENT_ID = E.DEPARTMENT_IDUNIONSELECTE.LAST_NAME,D.DEPARTMENT_ID,D.DEPARTMENT_NAMEFROMDEPARTMENTS DLEFT JOIN EMPLOYEES EON D.DEPARTMENT_ID = E.DEPARTMENT_ID;-- USING AN OUTER JOINSELECT DISTINCTE.LAST_NAME,D.DEPARTMENT_ID,D.DEPARTMENT_NAMEFROMEMPLOYEES EFULL OUTER JOIN DEPARTMENTS DON D.DEPARTMENT_ID = E.DEPARTMENT_ID;Question 8CREATE VIEW employees_vuASSELECT DISTINCTEMPLOYEE_ID,LAST_NAME employee,DEPARTMENT_IDFROMEMPLOYEES;SELECT * FROM employees_vu;Question 9SELECTcolumn_Name "Name",nullable "NULL?",data_type "Data Type",data_length "Data Length",data_precision "Data Precision"FROM user_tab_columns UTCWHERE table_name = UPPER('&mytable')Question 10CREATE SEQUENCE DEPT_ID_SEQSTART WITH 200INCREMENT BY 10MAXVALUE 1000NOCACHENOCYCLE;CREATE TABLE DEPT(DEPT_ID NUMBER PRIMARY KEY NOT NULL,DEPT_NAME VARCHAR2(100));INSERT INTO DEPTVALUES(DEPT_ID_SEQ.NEXTVAL,'Administration');Question 11.1SELECT*FROMEMPLOYEESWHERE MANAGER_ID IN(SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = &&EMPLOYEE_ID)ORDEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = &&EMPLOYEE_ID);Question 11.2WITH SUMMARYAS(SELECT JOB_ID FROM EMPLOYEES EGROUP BY JOB_IDHAVING COUNT(EMPLOYEE_ID) > 5)SELECTJ.*FROMJOBS JJOIN SUMMARY S ON S.JOB_ID = J.JOB_ID;Question 12.iCREATE TABLE EMPLOYEES2 ASSELECTEMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_IDFROMEMPLOYEES;Question 12.iiALTER TABLE EMPLOYEES2RENAME COLUMN EMPLOYEE_ID TO IDALTER TABLE EMPLOYEES2RENAME COLUMN DEPARTMENT_ID TO DEPT_IDQuestion 12.iiiALTER TABLE EMPLOYEES2DROP COLUMN FIRST_NAME;DESC EMPLOYEES2;Question 12.ivALTER TABLE EMPLOYEES2SET UNUSED DEPT_ID;DESCRIBE EMPLOYEES2;Question 12.vALTER TABLE EMPLOYEES2DROP UNUSED COLUMNS;DESCRIBE EMPLOYEES2;Question 12.vDROP TABLE EMPLOYEES2;SELECT * FROM RECYCLEBINWHEREORIGINAL_NAME = 'EMPLOYEES2'AND TYPE = 'TABLE';Question 13i The REGEX_REPLACE regular expression will replace a regular expression pattern of characters with another string of characters wherever it occurs in a string.ii. The REGEX_INSTR function allows you to search a string for a regular expression pattern and return the position of the pattern.iii. REGEXP_SUBSTR function allows you to search a string for a regular expression pattern and return the pattern. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download