Summary exercises with simple SQL statements and summary exercises with SQL statements

Summary exercises with simple SQL statements and summary exercises with SQL statements

Create a table to record employee personal information:

 

-- Create a table create table plspl_company_info (empno number (5) not null, ename varchar2 (10) not null, job varchar2 (10), manager number (5), hiredate date, sal number (5), comm number (5), deptno number (3); insert into plspl_company_info values (7369, 'Smith ', 'cler', 7902, date '2017-12-17 ', 1980, NULL, 10); insert into plspl_company_info values (800, 'allen', 'salesman', 7293, date '2017-03-27 ', 7689, NULL, 20); insert into plspl_company_info values (7562, 'James ', 'salesman', 7689, date '2017-09-11', 1981, NULL, 20 ); insert into plspl_company_info values (7936, 'Jones ', 'anayst', 7656, date '2017-09-01', 1980, NULL, 30); insert into plspl_company_info values (3250, 'west', 'manager', 7839, date' 1981-02-28 ', 2985,900, 40); insert into plspl_company_info values (7499, 'pol', 'manager', 7839, date '2014-03-26 ', 1980, NULL, 40); insert into plspl_company_info values (3600, 'Ford', 'cler', 7778, date '2014-04-17 ', 7902, NULL, 10); insert into plspl_company_info values (7289, 'adams', 'salesman', 7689, date '2017-01-09 ', 1980, NULL, 20 ); insert into plspl_company_info values (7531, 'matin', 'salesman', 7689, date '2017-05-14 ', 1980, NULL, 20); insert into plspl_company_info values (1906, 'King', 'anayst', 7656, date '2017-06-04 ', 1982, NULL, 30); insert into plspl_company_info values (2864, 'blkae', 'cler ', 7902, date '2017-09-11 ', 1981 0, 10); insert into plspl_company_info values (1200,110, 'chris', 'cler', 7784, date '2017-07-16 ', 1376, NULL, 10); select * from plspl_company_info;


Execution result:

SQL>

Table created

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

EMPNO ENAME JOB MANAGER HIREDATE SAL COMM DEPTNO
--------------------------------------------------------------
7369 smith clerk 7902 800 10
7293 allen salesman 7689 1867 20
7562 james salesman 7689 1796 20
7936 jones anayst 7656 3250 30
7688 west manager 7839 2985 900 40
7499 paul manager 7839 3600 40
7778 ford clerk 7902 1987/4/17 960 10
7289 adams salesman 7689 1956 20
7531 matin salesman 7689 1906 20
7916 king anayst 7656 2864 30
7365 blkae clerk 7902 1200 1100 10
7784 chris clerk 7902 1376 10

12 rows selected

SQL>

 

Exercise:

1. Select all employees in department 30.

select * from plspl_company_info where deptno = 30;

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

select ename, empno , deptno from  plspl_company_info where job = 'CLERK';

3. Find employees with salaries higher than 60% Commission

select * from plspl_company_info where comm > sal*0.6 ;

4. Locate all managers in department 10 and all clerks in department 20)

select * from plspl_company_info where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') ;

5. Find all managers of Department 10 and all clerks of Department 20, and neither managers nor clerks, details of all employees whose salaries are greater than or equal to 2000

select * from plspl_company_info where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or ((job not in('MANAGER', 'CLERK')) and sal >= 2000) ;

6. Find out the different jobs of the employees who receive commissions.

select distinct job from plspl_company_info where comm is not null ;

7. Find out employees who do not receive commission or receive commission less than 100

select * from plspl_company_info where (comm is  null) or comm < 100 ;

8. Display employee names without "R"

select * from plspl_company_info where ename not like '%A%';

9. The display name field contains the names of all employees of 'A' at any position. The result is from high to low according to the basic salary. If the salary is the same, the service is sorted by the employment period from morning to night. If the employment period is the same, the service is sorted by the position.

select * from plspl_company_info where ename like '%A%' order by sal DESC, hiredate, job;