1 List all information about the employees.

       SELECT * FROM EMP_2014170165;

2 List all information about the departments

       SELECT * FROM DEPT_2014170165;

3 List only the following information from the EMP table ( Employee name, employee number, salary, department number)

       SELECT ENAME,EMPNO,SAL,DEPTNO FROM EMP_2014170165;

4 List details of employees in departments 10 and 30.

       SELECT * FROM EMP_2014170165 

       WHERE DEPTNO = 10 OR DEPTNO = 30;

5 List all the jobs in the EMP table eliminating duplicates.

       SELECT DISTINCT JOB FROM EMP_2014170165;

6. What are the names of the employees who earn less than ￡20,000?

       SELECT ENAME FROM EMP_2014170165 WHERE SAL < 20000;

7. What is the name, job title and employee number of the person in department 20 who earns more than ￡25000?

       SELECT ENAME,JOB,EMPNO FROM EMP_2014170165 

       WHERE DEPTNO=20 AND SAL > 25000;

8. Find all employees whose job is either Clerk or Salesman.

       SELECT * FROM EMP_2014170165

       WHERE JOB = ‘CLERK‘ OR JOB = ‘SALESMAN‘

9. Find any Clerk who is not in department 10.

       SELECT * FROM EMP_2014170165

       WHERE DEPTNO=20 AND SAL > 25000;

10. Find everyone whose job is Salesman and all the Analysts in department 20.

       SELECT * FROM EMP_2014170165

       WHERE JOB = ‘SALESMAN‘ OR

       (JOB = ‘ANALYST‘ AND DEPTNO = 20);

11. Find all the employees who earn between ￡15,000 and ￡20,000.

Show the employee name, department and salary.

       SELECT ENAME,DEPTNO,SAL FROM EMP_2014170165 

       WHERE SAL BETWEEN 15000 AND 20000;

12 Find the name of the President.

       SELECT ENAME FROM EMP_2014170165

       WHERE JOB = ‘PRESIDENT‘;

13 Find all the employees whose last names end with S

       SELECT * FROM EMP_2014170165

       WHERE ENAME LIKE ‘%S‘;

14 List the employees whose names have TH or LL in them

       SELECT * FROM EMP_2014170165

       WHERE ENAME LIKE ‘%TH%‘ OR ENAME LIKE ‘%LL%‘;

15 List only those employees who receive commission.

       SELECT * FROM EMP_2014170165 

       WHERE COMM IS NOT NULL;

16 Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.

       SELECT ENAME,JOB,SAL,HIREDATE,DEPTNO 

       FROM EMP_2014170165 

       ORDER BY ENAME;

17. Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.

       SELECT ENAME,JOB,SAL,HIREDATE,DEPTNO 

       FROM EMP_2014170165 

       ORDER BY SAL DESC;

18. List all salesmen in descending order by commission divided by their salary.

       SELECT * FROM EMP_2014170165 

       ORDER BY nvl(COMM, 0)/SAL DESC;

19. Order employees in department 30 who receive commision, in ascending order by commission

       SELECT * FROM EMP_2014170165 

       WHERE DEPTNO=30 AND COMM IS NOT NULL

       ORDER BY COMM DESC;

20 Find the names, jobs, salaries and commissions of all employees who do not have managers.

       SELECT ENAME,JOB,SAL,COMM FROM EMP_2014170165 

       WHERE MGR IS NULL;

21 Find all the salesmen in department 30 who have a salary greater than or equal to ￡18000.

       SELECT * FROM EMP_2014170165

       WHERE DEPTNO=30 AND JOB = ‘SALESMAN‘ AND SAL >= 18000;

22 Find the employees who were hired before 01-Jan-1998 and have salary above 5000 or below 1000.

       SELECT * FROM EMP_2014170165

       WHERE HIREDATE < ‘01-1月-1998‘

       AND (SAL > 5000 OR SAL<1000);

23 What is the command to add primary key constraint to EMPNO

       ALTER TABLE EMP_2014170165 ADD PRIMARY KEY(EMPNO);

24 What is the command to add a new column EMP_family_name to existing EMP table

       ALTER TABLE EMP_2014170165 

       ADD EMP_family_name VARCHAR2(10);

25 How to drop primary key constraint for EMPNO

       ALTER TABLE EMP_2014170165 

       DROP PRIMARY KEY;

26 rename EMP table to EMPLOYEE

       RENAME EMP_2014170165

       TO EMPLOYEE_2014170165;

27 rename EMPLOYEE  back to EMP

       RENAME EMPLOYEE_2014170165

       TO EMP_2014170165;

28 What is the SQL command to remove column EMP_family_name from EMP table

       ALTER TABLE EMP_2014170165

       DROP COLUMN EMP_family_name;

29 What is the SQL command to copy emp table to employee table

       CREATE TABLE EMPLOYEE_2014170165 AS

       SELECT * FROM EMP_2014170165;

30 What is the SQL command to drop employee table

       DROP TABLE EMPLOYEE_2014170165;

31 What is the SQL command to display name’s of employee entered interactively from user

       SELECT * FROM EMP_2014170165

       WHERE ENAME = ‘&ENTER‘;

32 What is the SQL command to find the employee whose commission is NULL

       SELECT * FROM EMP_2014170165 WHERE COMM IS NULL;

数据库练习