Oracle interview questions

 

Oracle interview questions

1. Simple SQL query:

1): count the number of employees in each department

Select Dept, count (*) from employee group by dept;

2): count the number of employees in each Department greater than one record

Select Dept, count (*) from employee group by dept having count (*)> 1;

3): the name of the department where the employee whose salary exceeds 1200

Select E. first_name, salary, D. Name

From s_emp E, s_dept d

Where E. dept_id = D. id

And salary> 1200;

 

Ii. How to delete duplicate records in SQL: (rowid (Oracle pseudo column) is used ))

1) create a temporary table.

SQL> Create Table temp_emp as (select distinct * from employee)

SQL> truncate table employee; (clear the data in the employee table)

SQL> rename temp_emp to employee; (rename the table)

2) It also uses rowid, but it is more efficient.

SQL> Delete from employee where rowid not in (

Select max (t1.rowid) from employee T1 group

T1.emp _ id, t1.emp _ name, t1.salary); -- min (rowid) can be used here.

 

Iii. Top N problem: (using rownum (Oracle pseudo column ))

-- Rownum can only use the <= or <relational comparison operator

Select * From s_emp where rownum <= 2;

-- Query the three persons with the highest salary in the company

/* Select * from EMP

Where rownum <= 3

Order by Sal DESC; */incorrect

Select * from (select * from EMP order by Sal DESC)

Where rownum <= 3;

 

4. Paging query:

-- Query records 1-5

Select * from (select rownum num, s_emp. * From s_emp)

Where num> = 1 and num <= 5;

-- Sort by salary, five pages per page, search for the second page

Select salary, first_name

From (

Select S. *, rownum RM

From (select *

From s_emp

Order by salary d

) S

)

Where RM between 6 and 10

2. Three tables (15 minutes): (SQL)

Student table (student ID, name, gender, age, organization and Department)

Course Curriculum (No., course name)

SC Course Selection table (student ID, course number, score)

The table structure is as follows:

1) write an SQL statement to query the student ID and name (3 minutes) that have selected the 'computer methods)

A: The SQL statement is as follows:

Select Stu. Sno, Stu. sname from student Stu

Where (select count (*) from SC where SnO = Stu. SnO and CNO =

(Select CNO from course where cname = 'computer methods '))! = 0;

2) write an SQL statement to query the name of the course selected by 'Stephen Chow '(3 minutes)

A: The SQL statement is as follows:

Select cname from course where CNO in (select CNO from SC where SnO = (select SnO from student where sname = 'Stephen Chow '));

3) write an SQL statement to query the student ID and name (9 minutes) of the five courses selected)

A: The SQL statement is as follows:

Select Stu. Sno, Stu. sname from student Stu

Where (select count (*) from SC where SnO = Stu. SnO) = 5;