-- 3A -- SQL/86

Use the tables in your schema. Write a query that will list all of the employees (last names), whose last name starts with 'G', and the departments (give the name of the department) to which they belong. Include all employees who have not yet been assigned to any department. If an employee does not belong to a department, use the string "unassigned" for the department name. Sort the results by last name in ascending sequence. Use the terms "Last Name" and "Department" for the column headings. Write the query in SQL/86 and SQL/92. Note: do not use IS NULL or IS NOT NULL in your query.

-- 3B -- SQL/92

ICS 120X01_W21.JOBS P. JOB_ID VARCHAR2 (10 BYTE) JOB_TITLE VARCHAR2 (35 BYTE) MIN_SALARY NUMBER (O) MAX_SALARY NUMBER (6) JOB_ID_PK (JOB_ID) JOB_ID_PK (JOB_ID) ICS120 XD1 W21. EMPLOYEES P EMPLOYEE_ID NUMBER (0) FIRST NAME VARCHAR2 (20 BYTE) LAST NAME VARCHAR2 (25 BYTE) U EMAIL VARCHAR2 (25 BYTE) PHONE_NUMBER VARCHAR2 (20 BYTE) HIRE_DATE DATE FJOB_ID VARCHAR2(10 BYTE) SALARY NUMBER (8.2) COMMISSION_PCT NUMBER (2.2) MANAGER ID NUMBER (0) DEPARTMENT_ID NUMBER (4) EMP_EMAIL_UK (EMAIL) EMP_EMP_ID_PK (EMPLOYEE_ID) EMP_DEPT_FK (DEPARTMENT_ID) EMP_JOB_FK (JOB_ID) ) EMP_MANAGER FK (MANAGER_ID) EMP DEPARTMENT_IX (DEPARTMENT ID) EMP_EMAIL_UK (EMAIL) EMP_EMP_ID_PK (EMPLOYEE_ID) EMP_JOB_IX (JOB_ID) EMP MANADER_IX (MANAGER_ID) EMP_NAME_IX (LAST_NAME, FIRST_NAME) Column Names ICS120X01_W21. JOB HISTORY KPF EMPLOYEE_ID NUMBER (0) PSTART_DATE DATE END_DATE DATE FJOB_ID VARCHAR2(10 BYTE) F DEPARTMENT_ID NUMBER (4) JHIST_EMP_ID_ST_DATE_PK (EMPLOYEE_ID. START_DATE) JHIST_DEPT_FK (DEPARTMENT_ID) JHIST_EMP_FK (EMPLOYEE_ID) JHIST_JOB_FK (JOB_ID) JHIST_DEPARTMENT_IX (DEPARTMENT_ID) JHIST_EMPLOYEE_IX (EMPLOYEE_ID) JHIST_EMP_ID_ST_DATE_PK (EMPLOYEE_ID, START_DATE) JHIST JOB_IX (JOB_ID) ICS120X01_W21 DEPARTMENTS P. DEPARTMENT_ID NUMBER (4) DEPARTMENT_NAME VARCHAR2 (30 BYTE) F MANAGER_ID NUMBER (0) F LOCATION_ID NUMBER (4 DEPT_ID_PK (DEPARTMENT_ID) DEPT_LOC_FK (LOCATION_ID) DEPT_MGR_FK (MANAGER_ID) DEPT_ID_PK (DEPARTMENT_ID) DEPT_LOCATION_IX (LOCATION_ID) V Primary Key Constraint Name Foreign Key Constraint Names Index Names ICS120X01_W21.LOCATIONS P. LOCATION_ID NUMBER (4) STREET_ADDRESS VARCHAR2(40 BYTE) POSTAL_CODE VARCHAR2(12 BYTE) CITY VARCHAR2 (30 BYTE) STATE_PROVINCE VARCHAR2(28 BYTE) F COUNTRY_ID CHAR 2 BYTE) LOC_ID_PK (LOCATION_ID) ) LOC_C_ID_FK (COUNTRY_ID) LOC_CITY_IX (CITY) LOC_COUNTRY_IX (COUNTRY_ID) LOC_ID_PK (LOCATION_ID) LOC_STATE_PROVINCE_IX (STATE_PROVINCE) ICS 120X01_W21.COUNTRIES P. COUNTRY_ID CHAR 2 BYTE) COUNTRY_NAME VARCHAR2(40 BYTE) REGION_ID NUMBER COUNTRY_C_ID_PK (COUNTRY_ID) 3 COUNTR_REG_FK (REGION_ID) ICS120X01_W21 REGIONS REGION_ID NUMBER REGION NAME VARCHAR2 (26 BYTE) REG_ID_PK (REGION_ID) REG_ID_PK (REGION_ID)