Oracle foundation – simple query exercise (1)

Time:2020-5-22

Practice based on EMP table under Scott user in Oracle Database

  1. Query all tables under Scott user
select * from tabs;
  1. Query all information in employee table
select * from emp;
  1. Query employee number, name, job, salary.
select empno,ename,job,sal from emp;
  1. Query employee number, name, job, salary, list title in Chinese.
Select empno "employee number", ename "employee name", job "job", Sal "salary" from EMP;
  1. Inquire about the type of employee’s work.
select distinct job from emp;
  1. Query all employee numbers, names, jobs. Display in the following format: Number: 7369 – Name: Smith – job: Clerk (return a column)
Select 'No.:' | empno | '- Name:' | ename | '- job:' | job as details from EMP;
  1. Query employee number, name, job, annual salary
select empno,ename,job,(sal + nvl(comm,0))*12 YearlySalary from emp;
  1. Query all information of employees with salary greater than 1500
select * from emp where sal >1500;
  1. Query the name and bonus of employees who can get bonus
select ename,comm from emp where NVL(comm,0)>0;

select ename,comm from emp where DECODE(comm,null,0,comm) > 0;
  1. Query employees whose salary is more than 1500 or who can get bonus
select ename from emp where sal > 1500 or nvl(comm,0) > 0;

select ename from emp where sal > 1500 or DECODE(comm,null,0,comm) > 0;
  1. Query employees whose salary is more than 1500 and who can receive bonus
select ename from emp where sal > 1500 and nvl(comm,0) > 0;

select ename from emp where sal > 1500 and DECODE(comm,null,0,comm) > 0;
  1. Query employees whose salary is no more than 1500 or who are not allowed to receive bonus
select ename from emp where sal <= 1500 or nvl(comm,0) = 0;
  1. Query all employees with salary between 1500 and 3000
select * from emp where sal >=1500 and sal <= 3000;
  1. Query the current time of the system
select sysdate from dual;
  1. Search for employee information hired in 1981
select * from emp where hiredate like '%81%';

select * from emp where to_char(hiredate,'YYYY') = '1981';

select * from emp where hiredate <= to_date('1981-12-31','YYYY-mm-dd') and hiredate >= to_date('1981-01-01','YYYY-mm-dd');

select * from emp where hiredate between to_date('1981-01-01','YYYY-mm-dd') and to_date('1981-12-31','YYYY-mm-dd');
  1. Query employee information with the third letter “a” in the employee’s name
select * from emp where ename like '__A%';
  1. Query employee information with employee number 7369
select  * from emp where empno = '7369';
  1. Query employee information with employee number not 7369
select  * from emp where empno != '7369';

select  * from emp where empno <> '7369';
  1. Query employee information with number 73697900
select * from emp where empno in (7369,7900);
  1. Employee information with query number not 73697900
select * from emp where empno not in (7369,7900);
  1. Query employee information, sorted by salary from low to high
select * from emp order by sal;

select * from emp order by sal asc;
  1. Query employee information, sorted by salary from high to low
select * from emp order by sal desc;
  1. Please check the name and position of the employee without leader
select ename,job from emp where mgr is null;
  1. Query the name and position of employees with leaders
select ename,job from emp where mgr is not null;
  1. Query the names of all employees, positions and leaders
select e1.ename,e1.job,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+);
  1. Query all employee information in department 30
select * from emp where deptno = 30;
  1. List the names, numbers and department numbers of all clerks (clerk) (in ascending order of department numbers)
select ename,empno,deptno from emp where job = 'CLERK' order by deptno;
  1. Identify employees with higher commission than salary
select * from emp where nvl(comm,0) > sal;
  1. Find out about employees whose Commission is less than 40% of salary
select * from emp where nvl(comm,0) < sal*0.4;

Recommended Today

ibserver.exe What is the process? What is the role of the ibserver process

Process file: ibserver or ibserver.exeProcess name: InterBase server moduleProcess category: process with security riskEnglish Description: ibserver.exe is a process associated with the InterBase Server from Borland (Inprise). InterBase is a cross-platform embedded database. Chinese reference:Sorry, there is no Chinese reference for the time being!Produced by: BorlandOf: InterBase serverSystem process: noBackground program: noNetwork related: noCommon error: […]