[Top] 20 typical Java interview questions [Season 9 _ Chang rupeng]

 

The following questions will be used:4Tables. For details about the fields in these four tables, refer to the following content:

Employees (employee_id number (6) Not null, first_name varchar2 (20), last_name varchar2 (25) not null, email varchar2 (25) not null, phone_number varchar2 (20 ), hire_date date not null, job_id varchar2 (10) Not null, salary number (8, 2), commission_pct number (2, 2), manager_id number (6), department_id number (4 ))

 

Dept (department_id number (4) not null, department_name varchar2 (30) not null, manager_id number (6), location_id number (4 ))

 

Locations (location_id number (4) not null, city varchar2 (20 ))

 

Job_grades (grade_level varchar2 (3), lowest_sal number, highest_sal number)

1.Write a query to display the current date, and the column label is displayedDate.

 Select sysdate "date" from dual;

2.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). For each employeeEmployee number,Last_name,SalaryAndSalaryAdd15%And expressed as an integer. The column label is displayedNew salary

Select employee_id, last_name, salary, round (salary * 1.15, 0) "new salary" from employees;

3.Add a column on the basis of Question 2, which is the new salaryNew salaryIn the column (new salary refers to the salary after the increase of 15%) minus the old salary, the column label isIncrease.

Select employee_id, last_name, salary, round (salary * 1.15, 0) "new salary", round (salary * 1.15, 0)-salary "increase" from employees;

4.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Write a query in upper case, and other letters in lower case show the employee'sLast namesDisplay the name length. The start letter for all names isJ,AOrMTo give each column an appropriate label. Employee'sLast namesSorting result.

Select initcap (last_name) "name", length (last_name) "length" from employees where last_name like 'J % 'or last_name like'm %' or last_name like 'a % 'order by last_name;

5.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). For each employeeLast nameAnd calculate the number of months from the employee's employment date to today, column labelMonths_worked. The result is sorted by the number of months of employment, rounded to the nearest integer month.

Select last_name, round (months_between (sysdate, hire_date) months_worked from employees order by months_between (sysdate, hire_date );

6.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Write a query to calculate each employee:<Employee'sLast Name> earns <salary> monthly but wants <3TimesSalary>. Column labelDream salaries.

Select last_name | 'earns' | to_char (salary, 'fm $99,999.00 ') | 'monthly but wants' | to_char (salary * 3, 'fm $99,999.00 ') | '. '"Dream salaries" from employees;

7.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Create a query to displayLast nameAndSalary. Format15Length,$Left fill, column labelSalary.

Select last_name, lpad (salary, 15, '$') salary from employees;

8.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). DisplayLast name,Hire dateAndSalaryCheck date, which is the first Monday after six months of service, column labelReview. Formatted date display looks like"Monday, the thirty-first of July, 2000".

Select last_name, hire_date, to_char (next_day (add_months (hire_date, 6), 'monday'), 'fmday, "the" ddspth "of" month, yyyy') review from employees;

9.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). DisplayLast name,Hire dateAnd employees start working on Sunday, column labelsDay, And Monday is used as the start day of the week.

Select last_name, hire_date, to_char (hire_date, 'day') Day

 From employees order by to_char (hire_date-1, 'D ');

10.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Create a query to displayLast namesAndCommission (Commission)Ratio. If the employee has no commission"No Commission", Column labelComm.

Select last_name, nvl (to_char (commission_pct), 'no Commission') Comm from employees;

11.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Create a query to displayLast namesAnd displays their annual salary with an asterisk. Each asterisk represents1000Meiyuan. Sort data by salary in descending order. Column label isEmployees_and_their_salaries.

 Select rpad (last_name, 8) | ''| rpad ('', (salary * 12)/1000 + 1, '*') employees_and_their_salaries from employees order by salary DESC;

12.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). UseDecodeFunction, write a query, according to the following data show all employees based onJob_idColumn value level.

WorkLevel

Ad_presA

St_manB

It_progC

Sa_repD

St_clerkE

Not above0

Select job_id, decode (job_id, 'st _ cler', 'E', 'sa _ rep ', 'D', 'it _ prog', 'C ', 'st _ man ',' B ', 'ad _ pres', 'A', '0 ')Grade from employees;

13.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Dept (department_id, department_name, manager_id, location_id ). Write a query to display the last name, Department number, and department name of all employees.

Select E. last_name, E. department_id, D. department_name from employees e, departments d Where E. department_id = D. department_id;

14.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Dept (department_id, department_name, manager_id, location_id ). Create a department80The unique list of all jobs in, including the department location in the output.

 Select distinct job_id, location_id from employees, parameters where employees. department_id = parameters. department_idAnd employees. department_id = 80;

15.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Dept (department_id, department_name, manager_id, location_id ). Locations (location_id, city ). Write a query to show all employees with commissionsLast name,Department name,Location IDAnd city.

Select E. last_name, D. department_name, D. location_id, l. c ityFrom employees e, departments D, locations L where E. department_id = D. department_id and D. location_id = L. location_id and E. commission_pct is not null;

16.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Dept (department_id, department_name, manager_id, location_id ). Show allLast namesThere is a lower caseAEmployee'sLast nameAndDepartment name.

Select last_name, department_name from employees, parameters where employees. department_id = parameters. department_id and last_name like '% A % ';

17. employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Dept (department_id, department_name, manager_id, location_id ). Locations (location_id, city ). Write a query to show the Toronto last name , job , Department number and Department name .

select e. last_name, E. job_id, E. department_id, D. department_name from employees e join orders ments D on (E. department_id = D. department_id) join locations L on (D. location_id = L. location_id) where lower (L. city) = 'toronto ';

18.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Display employee'sLast nameAndEmployee numberTogether with their manager'sLast nameAndManager number. Column labels areEmployee,EMP #,ManagerAndMgr #

Select W. last_name "employee", W. employee_id "EMP #", M. last_name "manager", M. employee_id "Mgr #" from employees w join employees m on (W. manager_id = m. employee_id );

19.Show all employees includingKingHe has no manager. Sort results by employee ID

Select W. last_name "employee", W. employee_id "EMP #", M. last_name "manager", M. employee_id "Mgr #" from employees w left Outer Join employees m on (W. manager_id = m. employee_id );

20.Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ). Create a query to display all employees in the same department as the specified employee(Colleagues)OfLast names,Department numbers. Give each column an appropriate label.

Select E. department_id department, E. last_name employee, C. last_name colleague from employees e join employees C on (E. department_id = C. department_id) Where E. employee_id <> C. employee_id order by E. department_id, E. last_name, C. last_name;