Oracle review - summarize the end of last semester of junior year

catalogue

Oracle connection query

Oracle subquery

Using a subquery in a where statement means adding a select statement to the where statement

Using subqueries in the from statement means adding a select statement to the from statement

Using a subquery in a select statement means adding a select statement to the select statement

Usage of union and minus in Oracle

Oracle Rownum usage

Oracle add, modify, and delete

affair

There are some concepts in transactions

Create view

Oracle exercises

Oracle connection query

SQL92 syntax

Join query: it can also be called cross table query. You need to associate multiple tables for query.

1. Display the information of each employee and the name of the Department

Specify connection conditions

select ename, dname from emp, dept where emp.deptno=dept.deptno;
You can also use aliases(But it cannot be increased as keyword)
select ename, dname from emp a, dept b where a.deptno=b.deptno;

The output of the above results is correct because the correct connection is added.

The above query is also called "inner connection", which refers to querying equal data.

2. Obtain the names of employees and their managers

select a.ename, b.ename from emp a, emp b where a.mgr=b.empno;

The above is called "self connection", and only one table is connected. For specific query methods, one table can be regarded as two tables, as shown in the above example: the first table emp a code represents the employee table, and emp b represents the manager table, which is the same as the employee table and the Department table.

sql99 syntax

(internal connection) displays the information of employees whose salary is greater than 2000 and the name of the Department they belong to

use sql99 Syntax:
select ename, dname from emp a join dept b on a.deptno=b.deptno where sal>2000;
or
select ename, dname from emp a inner join dept b on a.deptno=b.deptno where sal >2000;
notes: inner Keywords are generally omitted
 use sql92 Syntax:
select ename, dname from emp a, dept b where a.deptno=b.deptno and sal>2000;

The difference between SQL92 syntax and sql99 syntax: 99 syntax can separate the table connection and query conditions, especially when multiple tables are connected, it will be clearer than SQL92.

(external connection) displays the information of employees whose salary is greater than 2000 and the name of the Department to which they belong. If there are no employees in a department, the Department must also be displayed.

(if there is a master-slave relationship between the two associated tables, then if the master table is on the left of the join, use the left connection; if it is on the right of the join, use the right connection)

Right connection
select a.ename, b.dname from emp a right join dept b on a.deptno=b.deptno;
You can also use left joins
select a.ename, b.dname from dept b left join emp a on a.deptno=b.deptno;

Both the left connection and the right connection are external connections. The left connection is subject to the table on the left. Compared with the table on the right, the equality and inequality between the left connection and the right table will be displayed. The right connection is just the opposite. The outer keyword can also be added to the above left connection and right connection, but this writing method is generally not recommended.

The function that the left connection can complete must be completed by the right connection

Use SQL92 syntax to complete the related queries of left connection and right connection:

Use (+) and place it opposite the main table

3. Get the names of employees and managers. If there is no superior manager, you should also find out:

select e.empno, e.ename, e.mgr, m.ename from emp m , emp e where m.empno(+) = e.mgr ;

Oracle subquery

A subquery is a nested select statement, which can be understood as a table.

Using a subquery in a where statement means adding a select statement to the where statement

1. Query employee information, query who are managers, and display their employee number and employee name

select empno, ename from emp where empno in(select distinct mgr from emp);

Realization idea:

● first find out the number of the manager, and then take out the duplicate records

select distinct mgr from emp where mgr is not null;

● query employees with management number

select empno, ename from emp where empno in(select distinct mgr from emp where mgr is not null);

2. To query who's salary is higher than the average salary of employees, you need to display employee number, employee name and salary realization idea

● get an average salary first

select avg(sal) from emp;

● employees who earn more than the average salary

select empno, ename, sal from emp where sal>(select avg(sal) from emp);

Analysis idea: first, find out the dependent conditions according to the text description and analyze them step by step.

Using subqueries in the from statement means adding a select statement to the from statement

Adding a subquery after from can be directly understood as a table.

1. To query the grade of the average salary of each department, you need to display the department number, average salary and grade number

● get the average salary of the Department first

select deptno, avg(sal) avg_sal from emp group by deptno;

● compare the average salary as a table with the grade table

select a.deptno, a.avg_sal, b.grade 
from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b 
where a.avg_sal between b.losal and b.hisal;

Key point: treat the sub query as a table.

Using a subquery in a select statement means adding a select statement to the select statement

1. Query employee information and department name

select  e.empno, e.ename, e.deptno, (select dname from dept where deptno = e.deptno) as dname from emp e;

Usage of union and minus in Oracle

union can merge sets (add)

select * from emp where job='MANAGER'
union
select * from emp where job='SALESMAN'

Union is equivalent to or or in in some cases, which is equivalent to the following code:

select * from emp where job in('MANAGER','SALESMAN'); Or select * from EMP where job ='manager 'or job ='salesman';

Not all query statements can perform union operations.

select * from emp union select * from dept;

When using union, you must ensure that the result set of the query contains the same number of columns.

select empno, ename from emp
union
select deptno, dname from dept

When using unoin, you must ensure that the fields of query results have the same meaning.

When using unoin, you must ensure that the data types of the fields of the query results are the same.

minus can move out of the set (subtract)

● inquire about department numbers 10 and 20, and remove those with salary greater than 2000 (the first method)

select * from emp where deptno in(10, 20) and sal <=2000;

● inquire about those with department numbers 10 and 20, and remove those with salary greater than 2000 (the second method, use minus)

select * from emp where deptno in(10, 20)
minus
select * from emp where sal>2000

Oracle Rownum usage

Oracle provides rownum, which is an implicit (query result set) field, and rownum is a line number, starting from 1.

rownum implied field

select rownum, a.* from emp a;

Get the first 5 data

select * from emp where rownum <=5;

Obtain all data of Daiwa and Article 5

select * from emp where rownum >5;

oracle does not support the above statements. oracle only supports operations with rownum less than or equal to, and does not support operations with rownum greater than or equal to.

Top 5 earners

select rownum, empno, ename, sal from emp where rownum<=5 order by sal desc;

Because using order by will not change rownum, the value of rownum has been formed when the data is inserted into the table. In the correct way, the sorted data is used as a table, so the rownum of this table is newly formed, so its order can be guaranteed to be correct, as follows:

select empno, ename, sal 
from (select empno, ename, sal from emp order by sal desc)
where rownum <=5

Paging with rownum

Paging is mainly to improve efficiency. Generally, there are many database mechanisms. It mainly locates the start position and end position of records from the database table, such as two pieces of data per page:

Page 1: records 1 ~ 2

Page 2-4

. . . . . . .

Because there are problems with rownum, we need to use three-tier select nesting to complete paging. The purpose of nesting is to convert rownum into our own fields, such as:

select empno, ename, sal
from 
(
 select rownum r, empno, ename, sal 
 from 
  (
   select empno, ename, sal from emp order by sal desc
  )    
  where rownum <=5 
)where r>0

The general paging method can be copied directly when used. Just modify the red font.

select *
from 
(
 select rownum r, t.* 
 from 
  (
    Arbitrary SQL sentence
  )  t  
  where rownum <=Ending line number 
)where r>Starting line number

Oracle add, modify, and delete

insert

Adding, modifying and deleting all belong to DML, and mainly include statements: insert, update and delete.

● Insert syntax format

Insert into Table name(Field,....) values(value,...........)

● omit the insertion of fields

insert into emp values(9999, 'zhangsan', 'MANAGER', NULL, NULL, 200, 100, 10);

This method is not recommended because the insert statement will be affected when the position of the field in the database table changes.

● insertion of specified fields (recommended)

SQL> insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9999, 'zhangsan', 'MANAGER', NULL, NULL, 200, 100, 10);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9999, 'zhangsan', 'MANAGER', NULL, NULL, 200, 100, 10)
*
Error on line 1:
ORA-00001: Violation of unique constraints (SCOTT.PK_EMP)

● the primary key cannot be duplicate

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(8887, 'zhangsan', 'MANAGER', null, sysdate, 200, 100, 10);

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(8888, 'zhangsan', 'MANAGER', null, to_date('2001-01-01', 'yyyy-mm-dd'), 200, 100, 10);

Note sysdate or to_date

● reproduction of table contents

create table emp_bak as select * from emp;

The above statement will automatically create a table and copy all the data to the new table, but you cannot copy the constraints of the table, such as primary key constraints.

● how to put the queried data directly into the existing table and use conditions?

insert into emp_bak select * from emp where job='MANAGER';

update

You can modify data and modify data according to conditions.

● syntax format:

update Table name set Field name 1=Value 1 to be modified, Field name 2=Value 2 to be modified where .......

● salary increase of 10% for employees with job as manager

update emp set sal=sal+sal*0.1 where job='MANAGER';

delete

Data can be deleted or deleted according to conditions.

● syntax format:

Delete from Table name where . . . . .

● delete employees with allowance of 300

delete from emp where comm=300;

● delete employees whose allowance is null

delete from emp where comm is null;

affair

Transactions can guarantee the atomicity of multiple operations, either all successful or all failed. For the database, the transaction ensures that the batch sql is either all successful or all failed. Transactions have four characteristics ACID:

1. Atomicity (multiple operations)

2. Consistency (status of data)

3. Isolation

4. Continuity

There are some concepts in transactions

1. Transaction: a batch of operations (a set of sql)

2. Start transaction

3. Rollback transaction -- Oracle -- rollback

4. Commit transaction -- Oracle -- commit

When a DML statement is executed, a transaction is actually started.

As for the rollback of transactions, it should be noted that only insert, delete and update statements can be rolled back, and the select cannot be rolled back (rolling back the select has no meaning). For create, drop and alter, it cannot be rolled back.

Why use views?, Because the requirements determine that the above statements need to be used in multiple places, if the above code is copied frequently, it will bring cost to maintenance. The view can solve this problem.

Create view

create view v_dept_avg_sal as select a.deptno, a.avg_sal, b.grade from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b where a.avg_sal between b.losal and b.hisal;

There is an error and the permission is insufficient. How to query the permission of a user?

select * from session_privs;

How to switch users?

conn system/bjpowernode

How can scott log in as a dba?

conn scott/tiger as sysdba

How to authorize scott users?

Switch to system user
conn system/bjpowernode
 stay system User is scott Authorization scott Right to create views
grant create view to scott;
Switch to again scott Under user, check whether you have the right to create a view
conn scott/tiger
select * from session_privs;

Scott users already have the right to create views.

How do I use views?

The use of view is consistent with the use of table, but the view cannot be added, deleted or modified, because the view is the result of table. The view is mainly used for the convenience of operation. For the reused result set, it is considered to build the view and create the view, which is generally created for the table that is not frequently modified.

Delete view

drop view V_DEPT_AVG_SAL;

Oracle exercises

1. Name of the person who gets the highest salary in each department

Step 1: get the highest salary in each department

select deptno, max(sal) maxSal from emp group by deptno

Step 2: get results

select ename, sal, e.deptno from emp e join ( select deptno, max(sal) maxSal from emp group bydeptno) t on e.deptno = t.deptno and e.sal = t.maxSal

2. Who is paid above the average salary of the Department

Step 1: get the average salary of each department

select deptno, avg(sal) avgSal from emp group by deptno

Step 2: get results

select ename, sal from emp e join (select deptno, avg(sal) avgSal from emp group by deptno) t on e.deptno = t.deptno and e.sal > t.avgSal

3. Obtain the average salary grade of (all) in the Department as follows:

Step 1: get the salary grade of each employee

select deptno, grade from emp e join salgrade g on e.sal between g.LOSAL and g.HISAL

Step 2: group the results of step 1 with department number to get the results

select deptno, avg(grade) from (select deptno, grade from emp e join salgrade g on e.sal between g.LOSAL and g.HISAL) group by deptno

4. Do not use group function (Max) to get the highest salary (give two solutions)

First (rownum):

● rank employees' salaries in descending order

select * from emp order by sal desc

● get the first data of query results

select sal from ( select * from emp order by sal desc) where rownum = 1

Second (auto correlation)

● use the Emp table as two tables and compare them to obtain values other than the maximum value.

select distinct e.sal from emp e join emp t on e.sal < t.sal

● get the maximum salary

select sal from emp where sal not in (select distinct e.sal from emp e join emp t on e.sal < t.sal)

5. Department number of the Department with the highest average salary (give at least two solutions)

First:

select * from (select deptno from emp group by deptno order by avg(sal) desc) where rownum = 1

Second:

● get the average salary of each department

select deptno, avg(sal) from emp group by deptno

● get the highest data of query results

select max(avgSal) from (select deptno, avg(sal) avgSal from emp group by deptno)

● achieving results

select deptno from (select deptno, avg(sal) avgSal from emp group by deptno) s join (select max (avgSal) maxAvgSal from (select deptno, avg(sal) avgSal from emp group by deptno)) t on s.avgSal = t.maxAvgSal

The third type (aggregate functions can be nested)

select deptno from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno);

6. Department name of the Department with the highest average salary

● refer to the result of the previous question (obtain the department number)

select deptno from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno);

● obtain department name

Select dname from dept where deptno = (select deptno from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno))

7. The Department name of the Department with the lowest level of average salary

Step 1: get the average salary of each department

select deptno, avg(sal) avgSal from emp group by deptno

Step 2: get the average salary grade of each department

select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal

Step 3: get the lowest level

select min(grade) from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal)

Step 4: get the department number

Select deptno from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal) where grade = (select min(grade) from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal))

Step 5: obtain the Department name

Select dname from dept where deptno in (Select deptno from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal) where grade = (select min(grade) from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal)))

8. Get a manager's name that is higher than the maximum salary of an ordinary employee (the employee code does not appear in the mgr field)

Step 1: get the employee numbers of all managers

select distinct mgr from emp where mgr is not null

Step 2: get the maximum salary of ordinary employees

Select max(sal) maxSal from emp where empno not in (select distinct mgr from emp where mgr is not null )

Step 3: get results

select ename , sal from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > (Select max(sal) maxSal from emp where empno not in (select distinct mgr from emp where mgr is not null ))

9. Top five highest paid employees

select *
from 
(
 select rownum r, t.* 
 from 
  (
    Select ename, sal from emp order by sal desc
  )  t  
  where rownum <=5 
)where r>0

10. The sixth to tenth highest paid employees

select *
from 
(
 select rownum r, t.* 
 from 
  (
    Select ename, sal from emp order by sal desc
  )  t  
  where rownum <=10 
)where r>5

11. 5 employees who have obtained the last entry

Select * from ( select ename, hiredate from emp order by hiredate desc ) where rownum <= 5

12. How many employees get each salary grade

Step 1: get the salary grade of each employee

select empno, ename, grade from emp e join salgrade g on e.sal between g.losal and g.hisal

Step 2: group according to the grade, and then obtain the quantity

select grade, count(*) from (select empno, ename, grade from emp e join salgrade g on e.sal bet ween g.losal and g.hisal) group by grade

There are three tables S, C, SC

S(SNO, SNAME) represents (student number, name)

C(CNO, CNAME, CTEACHER) representative (class number, class name, teacher)

SC(SNO, CNO, SCGRADE) representative (student number, class number, grade)

Question:

Question 1: find out the names of all students who have not chosen "dawn" teacher.

Question 2: list the names and average scores of students who failed more than 2 (including 2).

Question 3: names of all students who have studied both course 1 and course 2.

Please write the answer in standard SQL language, and the dialect is OK (please explain what dialect is used).

CREATE TABLE SC
(
  SNO      VARCHAR2(200 BYTE),
  CNO      VARCHAR2(200 BYTE),
  SCGRADE  VARCHAR2(200 BYTE)
);

CREATE TABLE S
(
  SNO    VARCHAR2(200 BYTE),
  SNAME  VARCHAR2(200 BYTE)
);

CREATE TABLE C
(
  CNO       VARCHAR2(200 BYTE),
  CNAME     VARCHAR2(200 BYTE),
  CTEACHER  VARCHAR2(200 BYTE)
);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', 'language', 'Zhang'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', 'Politics', 'king'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', 'English', 'Lee'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', 'mathematics', 'Zhao'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', 'Physics', 'dawn'); 
commit;
 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', 'Student 1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', 'Student 2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', 'Student 3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', 'Student 4'); 
commit;
 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 
commit;

Question 1: find out the names of all students who have not chosen "dawn" teacher.

Step 1: find out the course number taught by Mr. Liming

Select cno from c where CTEACHER =  'dawn'

Step 2: query and select the student number of Mr. Liming's course

select sno from sc where cno in (Select cno from c where CTEACHER =  'dawn')

The third step is to get the result

select sname from s where sno not in ( select sno from sc where cno in (Select cno from c where CTEACHER =  'dawn'))

Question 2: list the names and average scores of students who fail more than 2 (including 2).

Step 1: get the number of students who fail more than 2 (including 2)

select sno from sc where sc.SCGRADE < 60 group by sno having count(*) >= 2

Step 2: get the average score of each student

Select sno, avg(scgrade) avgGrade from sc group by sno

Step 3: get results

Select sname, avgGrade from s join (select sno from sc where sc.SCGRADE < 60 group by sno having count(*) >= 2) n on s.sno = n.sno join (Select sno, avg(scgrade) avgGrade from sc group by sno) g on n.sno = g.sno

Question 3: the names of all students who have studied both course 1 and course 2.

Step 1: query the number of students who have selected courses 1 and 2

Select sno from sc where cno='1' and sno in ( select sno from sc where cno='2' )

Step 2: get results

Select sname from s where sno in (Select sno from sc where cno='1' and sno in ( select sno from sc where cno='2' ))

14. List the names of all employees and direct supervisors

(99 grammar)Select e.ename, nvl(m.ename, 'No superior') as mname from emp e left join emp m on e.mgr = m.empno 
(92 grammar) Select e.ename, nvl(m.ename, 'No superior') as mname from emp e, emp m where e.mgr = m.empno(+)

15. List the number, name and department name of all employees whose employment date is earlier than their direct supervisor

select e.empno, e.ename, d.dname from emp e join emp m on e.mgr = m.empno and e.hiredate < m.hiredate join dept d on e.deptno = d.deptno

16. List Department names and employee information of these departments, as well as those departments without employees

Select d.dname, e.* from emp e right join dept d on e.deptno = d.deptno

17. List all departments with at least one employee

Select dname, count(*) from emp e join dept d on e.deptno = d.deptno group by dname

Select dname, count(e.empno) from emp e right join dept d on e.deptno = d.deptno group by dname having count(e.empno) > 0

18. List all employee information whose salary is more than "SMITH"

select * from emp where sal > (select sal from emp where ename = 'SMITH')

19. List the names of all clerks and their departments, and the number of people in the Department

Step 1: get the information of employees whose jobs are CLERK

Select deptno, ename from emp where job = 'CLERK '

Step 2: get the Department name

select ename ,dname from dept d join (Select deptno, ename from emp where job ='CLERK') t on t.deptno = d.deptno

Step 3: get the number of people in each department

Select dname, count(*) from emp e join dept d on e.deptno = d.deptno group by dname

Step 4: get results

Select ename, d.dname, cc from (select ename ,dname from dept d join (Select deptno, ename from emp where job ='CLERK') t on t.deptno = d.deptno) d join (Select dname, count(*) cc from emp e join dept d on e.deptno = d.deptno group by dname) c on d.dname = c.dname

20. List various jobs with a minimum salary of more than 1500 and the number of all employees engaged in this job

Step 1: get a job with a minimum salary of more than 1500

Select job from emp group by job having min(sal) > 1500

Step 2: obtain the number of employees for each job

Select job ,count(*) from emp group by job

Step 3: get results

Select j.job, cc from (Select job from emp group by job having min(sal) > 1500) j join (Select job ,count(*)  cc from emp group by job) c on j.job = c.job

21. List the names of employees working in the Department "sales" < Sales Department ". It is assumed that they do not know the department number of the sales department

22. List the salary grades of all employees whose salary is higher than the average salary of the company, their departments, superior leaders and employees

The first step is to get the average salary of the company

Select avg(sal) from emp

The second step is to obtain employees whose salary is greater than the average salary

Select * from emp where sal > (Select avg(sal) from emp)

Step 3: associate with department table

Select ename, dname from (Select * from emp where sal > (Select avg(sal) from emp)) t join dept d on t.deptno = d.deptno

Step 4: associate with manager table

Select t.ename, d.dname, m.ename as mname  from (Select * from emp where sal > (Select avg(sal) from emp)) t join dept d on t.deptno = d.deptno left join emp m on t.mgr = m.empno

Step 5: Level Association

Select t.ename full name, d.dname Department name, nvl(m.ename, 'nothing') Superior manager, grade Wage scale  from (Select * from emp where sal > (Select avg(sal) from emp)) t join dept d on t.deptno = d.deptno left join emp m on t.mgr = m.empno join salgrade g on t.sal between g.losal and g.hisal

23. List the names of all employees and departments engaged in the same work as "SCOTT"

Select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno where job = (Select job from emp where ename = 'SCOTT')

24. List the names and salaries of all employees whose salary is equal to the salary of employees in department 30

The first step is to get the salary of all employees in the Department of 30

select sal from emp where deptno = 30

Step 2 get the results

Select ename, sal from emp where sal in (select sal from emp where deptno = 30)

25. List the names and salaries of employees whose salaries are higher than those of all employees working in department 30 Department name

select ename, sal, dname from emp e join dept d on e.deptno = d.deptno where sal >  (select max (sal) maxSal from emp where deptno = 30)

26. List the number of employees working in each department, average salary and average service period

Select d.dname Department name, count(e.empno) Number of employees, round(avg(e.sal), 2) average wages, round(avg(sysdate-hiredate)/365,0) Service period from emp e, dept d where e.deptno = d.deptno group by d.dname

27. List the names, Department names and wages of all employees

Select ename, dname, sal from emp e, dept d where e.deptno = d.deptno

28. List the details and number of people in all departments

Select d.*, (select count(e.empno) from emp e where e.deptno = d.deptno) Number of people from dept d

30. List the minimum salary of the manager of each department

select deptno, min(sal) from (select deptno, sal, empno from emp where empno in (Select distinct mgr from emp where mgr is not null)) group by deptno

32. Find out the superior supervisor of an employee and ask for a salary of more than 3000 among these supervisors

select ename executive director, sal Supervisor salary from emp where empno in (Select distinct mgr from emp where mgr is not null) and sal > 3000

33. Calculate the total salary and department number of department employees with'S' character in the Department name

select deptno from dept where dname like '%S%'
select d.dname, sum(e.sal), count(e.empno) from emp e right join dept d on e.deptno = d.deptno group by d.dname having d.dname like '%S%'

34. Give 10% salary increase to employees who have served for more than 28 years

Update emp set sal = sal*1.1 where (months_between(sysdate, hiredate)/12) > 28

Keywords: Oracle

Added by sn202 on Fri, 25 Feb 2022 03:03:10 +0200

Programming VIP

Oracle review - summarize the end of last semester of junior year

Oracle connection query

Oracle subquery

Using a subquery in a where statement means adding a select statement to the where statement

Using subqueries in the from statement means adding a select statement to the from statement

Using a subquery in a select statement means adding a select statement to the select statement

Usage of union and minus in Oracle

Oracle Rownum usage

Oracle add, modify, and delete

affair

There are some concepts in transactions

Create view

Oracle exercises

Popular Keywords