Description
Questions #1 to #7 require the SQL select statements, based on table Employees and Departments. (10 points * 10 questions = 100)
- List the employee_id, last name, first name, department ID for those employees that their last name starts with the letters “St”.
- Display the employees’ employee_id, full names (the format will be: last_name, “,” one space, first_name), salary and department ID for those who report to their immediate supervisor with ID as 145.
- Display the employees’ employee_id, full names (the print out format will be: last_name, “,” one space, first_name), salary and department ID for those whose immediate supervisor is John Russell (last name Russell, first name John). Sometimes, we do not know how the database stores the literals (all upper, Initial Capital, or all lower cases). Use the function UPPER, you can make your query case insensitive.
- List the department ID, department name and the number (how many) of employees that work in that department. The output will be sorted in order of number of employees in ascending. Exclude those departments that have no employee yet.
- List the full name (last name, first name), and salary for those employee(s) who is (are) not assigned to a department yet.
- List the employee ID, last name, first name and department ID for those whose last name is not unique among the employees.
- List the employee ID, last name, first name, their department ID, their immediate supervisor’s ID (the manager ID in employees table) and their department head’s ID, (the manager ID in departments table) for those employees who work in department (ID) 60 or department (ID) 70, and their immediate supervisor is not their department head/manager.
Questions 8, 9, and 10 are for PL/SQL coding.
- Write an anonymous PL/SQL block.
In the block, declare a variable called l_name, data type anchored as the same data type of last_name in Employees table; also declare a variable named sal, data type as that of salary in Employees table.
In the executable section, the program will assign the value of l_name as ‘Jackson’, salary as 7777. Then use the DBMS_OUTPUT.PUT_LINE (‘ ‘ ) command to print out the values of that employee’s name and salary. Please use the function TO_CHAR to make the output of salary as popular money format like $7,777.00.
- Each of the following two PL/SQL blocks declares a Boolean variable,
in the 9 (a), there is no error, neither output, please correct it to printout the statement;
When running the 9(b), you will get error, please correct it.
Set serveroutput on
REM (a)
DECLARE
v1 boolean := NULL;
Begin
IF v1 = Null THEN
DBMS_OUTPUT.PUT_LINE (‘Value of v1 is NULL.’);
END IF;
END;
REM 9(b)
DECLARE
v2 boolean := FALSE;
Begin
IF v2 = False THEN
DBMS_OUTPUT.PUT_LINE (‘Value of v2 is ‘ || v2 || ‘.’ ‘);
END IF;
END;
- Write an anonymous PL/SQL block with a nested (inner) block.
In the out block, declare a variable called counter using integer as its data type with initial value of 101, also declare a variable called v1 using varchar2 (30) as its data type, assigning ‘Adams’ as its initial value.
In the inner block, declare a variable with the same name as “counter” with data type of integer, and assigning the initial value as 55.
In the executable section of inner block, print out the values of the variable counter declared in the outer block and inner block, also print out the value of variable v1 declared in the outer block.
Some hints.
Q1. Use like clause for Pattern match, such as below
Where last_name like ‘St%’ ; — this is case sensitive, or
Where UPPER (last_name) like ‘ST%’ ;
Q2. Use concatenation operation to combine the last name and first name:
last_name || ‘, ‘ || first_name [AS] fullname, — [ ] is optional
SELECT … , TO_CHAR (salary, ‘$999,999’) salary, …
— use TO_CHAR function to display currency
Q3. A subquery may make it easier in logic, such as
WHERE manager_id =
( select employee_id from employees where …
Use where upper (last_name) = ‘RUSSELL’ …
that can make your where condition case insensitive.
Of course natural join will work too if you prefer: make join the table employees with itself,
such as
FROM employees e, employees m
WHERE m.employee_id = e.manager_id AND …
Q4. Using “Group by” and function COUNT.
Information about which employee works for which department are described in the employee table; department name is listed only in departments table.
Notice that the select list must match the group by column_list except the aggregation functions.
That means, if you have
“SELECT d.Department_ID, department_name, count (*)… ”
as select list, then you will copy the whole select list (excluding the aggregation function)
“ Group by d.DEPARTMENT_ID, department_name”
Not include “ count(*)”, as the system knows there is one value returned for each group.
The compiler does not know (or does not want to find out) that each department_id has a unique department_name. Group by all the columns in select list (except the aggregation function) makes sure there is only one row returned by each group.
Q5. The department_id is not assigned, that can be translated into SQL language as
WHERE department_ID IS null; — “ = null ” is wrong in where condition.
Q6. A subquery may help:
where last_name in
(select last_name from employees
group by last_name
having count(*) > 1)
This kind subquery is useful to check the duplication (of values) in a table.
Q7. This question reminds you that the two columns with same name of “manager_id” in
employees and departments tables do not have the same domain.
You need to join the two tables employees and departments;
FROM employees e, departments d
WHERE e.department_ID = d.department_ID and
e.department_id in (60, 70) and
e.manager_ID != d.manager_id
Q8. PL/SQL. Simple questions, remember to run command
SET serveroutput on
You only need to run this command once for one session (or say for one log-in).
Q9. Please refer to the sample on page 3, note2_PLSQL_Fundamentals.docx.
Q10. Please refer to the Example 1 on page 6 in Note2_PLSQL_Fundamentals.docx.
