Practice ORACLE database questions

ORACLE database exercises

Use the emp table under the scott/tiger user to complete the following exercises. The structure of the table is explained as follows

emp employee table field content is as follows:

empno employee number

ename Employee name

job work

mgr Superior number

hiredate Date hired

sal salary

comm Commission

deptno department number

1.Select all employees in department 30.

2. List the names, numbers and department numbers of all clerks (CLERK).

3. Find employees whose commissions are higher than their salaries.

4. Find employees whose commission is higher than 60% of their salary.

5. Find the detailed information of all managers (MANAGER) in department 10 and all clerks (CLERK) in department 20.

6. Find the detailed information of all managers (MANAGER) in department 10, all clerks (CLERK) in department 20, and all employees who are neither managers nor clerks but whose salary is greater than or equal to 2000.

7. Find out the different jobs of employees who work on commission.

8. Find employees who charge no commission or charge less than 100 commission.

9. Find all employees employed on the third to last day of each month.

10. Find employees who were hired more than 12 years ago.

11. Display the names of all employees with initial capital letters.

12. Display the name of the employee with exactly 5 characters.

13.Display the names of employees without "R".

14.Display the first three characters of all employees’ names.

15. Display the names of all employees and replace all "A" with a

16. Display the names and dates of employment of employees with more than 10 years of service.

17. Display employee details, sorted by name.

18. Display the names and dates of employment of employees, ranking the oldest employees first based on their years of service.

19. Display the names, jobs and salaries of all employees, sorted by job in descending order, or by salary if the jobs are the same.

20. Display the names of all employees, the year and month they joined the company, sorted by the month of employment date, if the months are the same, the employees with the earliest year will be sorted first.

21. Display the daily salary of all employees when a month is 30 days, ignoring the remainder.

22. Find all employees hired in February (of any year).

23.For each employee, display the number of days since he joined the company.

24. Display the names of all employees whose name field contains "A" anywhere.

25. Display the service years of all employees in the form of year, month and day. (Approximately)

oracle question

(1) select deptno from dept where depptno in (select deptno from em);

(2)select empno,ename,sal from em where sal>(select sal from em where ename = 'SMITH');

(3)SELECT e.ename,d.dname from em e,dept d where e.deptno=d.deptno and e.job = 'CLERK';

(4)select empno,ename from em where job in (select job from em where ename = 'SCOTT') ;

(5)select job,min(sal) from em group by job;

(6)select d.dname ,min(e.sal) from dept d,em e

where d.deptno=e.deptno and e.job ='MANAGER' group by d.dname;

Oracle 11G SQL practice homework questions

7.select employee name, salary from table where salary 8.select employee name, salary from table where salary>avg (salary) order by position;

9. Example: The number of column n in t1 is [2,3]; the number of column n in t2 is [1,2,3,4]

select * from t2 where n >any(select n from t1); Result: 3,4.

Note: Query which of the numbers in column n in table t2 is the largest number [including max] than the number in column n in table t1.

select * from t2 where n = any(select n from t1); Result: 2,3.

select * from t2 where n 10.select * from t2 where n >all(select n from t1); Result: 4.

select * from t2 where n 12.SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2; merge table_name1 and table_name2 tables without duplicate columns.

SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2; Connect all the data in the two tables table_name1 and table_name2, there are duplicates.

An oracle stored procedure problem solution

CREATE TABLE SC(

SNO INT,

CNO varchar(10),

GRADE INT

);

INSERT INTO SC VALUES(95001, 'Math', 75);

INSERT INTO SC VALUES(95001, '中文', 92);

INSERT INTO SC VALUES(95002, 'English', 64);

INSERT INTO SC VALUES(95002, 'English', 77);

INSERT INTO SC VALUES(95003, 'Math', 85);

CREATE TABLE SC_TOTAL(

SNO INT PRIMARY KEY,

"Mathematics" number(5,2),

"Chinese" number(5,2),

"English" number(5,2),

"Average score" number(5,2)

);

DECLARE

v_row_count INT;

BEGIN

FOR sc_rec IN (SELECT * FROM SC)

LOOP

-- Determine whether the data has

SELECT COUNT(*) INTO v_row_count

FROM SC_TOTAL

WHERE SNO = sc_rec.SNO;

IF v_row_count = 0 THEN

-- The data does not exist. Insert first.

INSERT INTO SC_TOTAL(SNO) VALUES (sc_rec.SNO);

END IF;

-- Based on the course, update specific columns.

IF sc_rec.CNO = 'Mathematics' THEN

UPDATE SC_TOTAL SET "MATH" = sc_rec.GRADE WHERE SNO = sc_rec.SNO;

ELSIF sc_rec.CNO = 'Chinese' THEN

UPDATE SC_TOTAL SET "中文" = sc_rec.GRADE WHERE SNO = sc_rec.SNO;

ELSIF sc_rec.CNO = 'English' THEN

UPDATE SC_TOTAL SET "English" = sc_rec.GRADE WHERE SNO = sc_rec.SNO;

END IF;

-- Calculate the average score.

UPDATE SC_TOTAL

SET "Average score" = (NVL ("Mathematics", 0) NVL ("Chinese", 0) NVL ("English", 0))

/ (NVL2 ("Mathematics", 1,0) NVL2 ("Chinese", 1, 0) NVL2 ("English", 1, 0))

WHERE SNO = sc_rec.SNO;

END LOOP;

END;

/

-- Data verification.

SQL>SELECT * FROM SC_TOTAL;

SNO Mathematics Chinese English Average Score

---------- ---------- ---------- ---------- ------- ---

95001 75 92 83.5

95002 77 77

95003 85 85

The above is the detailed content of Practice ORACLE database questions. For more information, please follow other related articles on the PHP Chinese website!