1. Create a query to display all the data from the table, departments. Separate each column output by a comma. Name the column title THE OUTPUT.
2. Display all employee last names in which the third letter of the name is a
3. Display the last name, job and salary for all employees whose job is sales
representative or stock clerk and whose salary is not equal to 2500, 3500 or
7000
4. Use the concat fuction to join the last_name, space and first_name columns of
the employees table
5. Display all the last_name which doesn't contain the letter a
6. Run the following to create a table and insert four rows:
create table emp (name varchar2(5),gender varchar2(1) );
insert into emp values('Kwasi','F');
insert into emp values('Yaw','F');
insert into emp values('Ama','M');
insert into emp values('Akos','M');
commit;
select * from emp;
NAME GENDER
----- ----------
Kwasi F
Yaw F
Ama M
Akos M
You will realise that the males and the females have been given wrong gender, write a report to display the correct gender for each person ie
NAME GENDER
----- ----------
Kwasi M
Yaw M
Ama F
Akos F
7. Write a query that displays the last name and salary of employees who earn more than an amount that the
user specifies after a prompt.
8. Create a query that prompts the user for a manager ID and generates the employee ID, last name, salary
and department name for that manager's employees. The query should have the ability to sort the report on a selected column.
9. Write a query that displays the last name, hire date and day of the week (eg Monday) on which the employee started, giving appropriate labels.
10.Write a query that displays the employees' last names and commission amounts. If an employee does not earn commission, show "No Commission". Label the column COMM.
11.Create a query that displays the first eight characters of the employees' last names and indicates the amounts of the salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES.Eg, elvis* * ie elvis earns 2000
12. Create a report to display the manager number and the salary of the lowest-paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is 6000 or less. Sort the output in descending order of salary.
13.Create a query to display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997 and 1998. Create the appropriate column headings.
ie TOTAL 1995 1996 1997 1998
20 1 2 2 3
14. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50, 80 and 90.
15.Write a query to display the last name,job,department name for all employees who work in Toronto.
16.Create a query that displays employee last name, department numbers, and all the employees who work in the same department as a given employee.Eg:
DEPARTMENT EMPLOYEE COLLEAGUE
20 Fay Hartstein
20 Hartstein Fay
17. Create a query to find the names and hire dates for all employees who were hired before their managers, along with their managers' names and hire dates.
18.Create a report that displays the department number, last name and job ID for every employee in the Executive department.
19. Write a query to display the employee number, last name and salary of all employees who earn more than the average salary and who work in a department with any employee whose last name contains a u.
