oracle中unit,Oracle Unit Test1

Problem Set Question 1

You are one database operator for Human Resource Department of XYZ wizard Corperation. You need to generate some reports from data schema objects of hr database account by using SQL Statement. (Table descriptions and data of hr can be found in appendix of our textbook.)

I. you are one rookie SQL programmer who are new for using SQL*PLUS, then you need to familiarize yourself to SQL*Plus and the hr tables you will be working on. Then you do following tasks.

1. You need to know the data schema of major tables(Employees and Departments) (i.e. the columns names and

datatype for each of them), then you using which commands:

_________________________ and __________________________

2. You need to query employees table. In the report concatenate employees id and their full names and name the

column “Employee id and name”, and display the hiredate and salary for each employee.

3. Then you need to display employee unique job codes with no duplication.

4. You need to report three years annual income for each employee and name the column 3 years wage . In the

report, you need include their last name, salary, commission percentage (computation formula for each is “12* salary*commission_pct”).

5. Query employee table to display the employees information(include all column), whose salary are more then

10000 (include 10000)

6. Query employee table to display the employee last name and salary for one employee named Alberto .

7. Query employee table to display employees who working in depatment id 20 , include employee id, Job, and

hiredate, name these column “Emp#”, “Job Code”, “Hire Day”.

8. you need to save the above queries into one files named myfirsttask.sql in the path E:\mysqlcode\ .

II. Your boss request you to generate some more reports on employees with a purpose of retrieving more specific information with every individual. Then you do following tasks;

1. You need to query employees table to display employee s last name, and salary for each individual who earns

salary more than 10000 or less than 5000, then change the condition to match individual with salary more than 5000 and less than 10000. sort the result in descending order by their last name.

2. Query employees table to display employees who do not earn commission. (include employee last name, first

name, commission). Sort the result in descending order for their last name and ascending order for their commission.

3. Query employees table to display employees who are working for departments with department id 10,20,30,90,

and 100. (include employee id, employee last name, first name and their departments id)

4. Query employees table to display employees who working for department 10 and whose telephone number are

started with 650 or 011 .(include employee id, phone number and department id).

5. Query employees table to display all employees who has the characters er in their last name.(include employee

id and last name).

6. produce a query that require user to input last name of employee to query specific employee s information; the sort column should be specified by user as well. (output columns include employee last name, employee_id, and their hiredate).

III. You are asked to do some computation with date (you can use daul table for output if nessassary) , the tasks are as following:

1. Retrieve employee information. Display each employee last name in upper case, display employee hire date in

format like Oct, 31,1981 23:59:59PM (take 1981/10/31 23:59:59 for example) , and display employee how many month they are hired (month count should be integer).

2. Retrieve employees information to display employee whose last name is ended with letters er . (include

employee last name and the length of his/her last name).