二：限制和数据排序-GangLin

附件：二：限制和数据排序.pdf

学习环境配置：http://blog.chinaunix.net/uid-26230811-id-3330279.html

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table

[WHERE condition(s)];

SQL> SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;

SQL> SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen';

比较条件

SQL> SELECT last_name, salary FROM employees WHERE salary <= 3000;

Using the BETWEEN Condition

SQL> SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;

Using the IN Condition

SQL> SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);

SQL> SELECT last_name, job_id

2 FROM employees

3 WHERE job_id

4 NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

SQL> SELECT last_name, job_id, salary

2 FROM employees

3 WHERE job_id = 'SA_REP'

4 OR job_id = 'AD_PRES'

5 AND salary > 15000;

SQL> SELECT last_name, job_id, salary

2 FROM employees

3 WHERE (job_id = 'SA_REP'

4 OR job_id = 'AD_PRES')

5 AND salary > 15000;

SQL> SELECT last_name, job_id, department_id, hire_date

2 FROM employees

3 ORDER BY hire_date ;

SQL> SELECT last_name, job_id, department_id, hire_date
2 FROM employees
3 ORDER BY hire_date DESC ;

列别名排序

SQL> SELECT employee_id, last_name, salary*12 annsal
2 FROM employees
3 ORDER BY annsal;

多个列的排序

根据ORDER BY列表的顺序进行排序

SQL> SELECT last_name, department_id, salary
2 FROM employees
3 ORDER BY department_id, salary DESC;

练习

1. Create a query to display the last name and salary of employees earning more than $12,000.

Place your SQL statement in a text file named lab2_1.sql. Run your query.

SQL> select last_name,salary from employees where salary>12000;

2. Create a query to display the employee last name and department number for employee number

176.

SQL> select LAST_NAME,DEPARTMENT_ID from employees where EMPLOYEE_ID=176;

3. Modify lab2_1.sql to display the last name and salary for all employees whose salary is not in the range of $5,000 and $12,000. Place your SQL statement in a text file named lab2_3.sql.

SQL> select LAST_NAME,SALARY from employees where SALARY <5000 or SALARY>12000;

4.Display the employee last name, job ID, and start date of employees hired between February 20, 1998, and May 1, 1998. Order the query in ascending order by start date.

SQL> select LAST_NAME,JOB_ID,HIRE_DATE from employees where HIRE_DATE between '20-FEB-98' and '01-MAY-98';

5. Display the last name and department number of all employees in departments 20 and 50 in alphabetical order by name.

SQL> select LAST_NAME,DEPARTMENT_ID from employees where DEPARTMENT_ID between 20 and 50;

6. Modify lab2_3.sql to list the last name and salary of employees who earn between $5,000 and $12,000, and are in department 20 or 50. Label the columns Employee and Monthly Salary, respectively. Resave lab2_3.sql as lab2_6.sql. Run the statement in lab2_6.sql.

SQL> select LAST_NAME as "Employee",SALARY as "Molthly Salary" from employees where (SALARY between 5000 and 12000)and(DEPARTMENT_ID between 20 and 50);

7. Display the last name and hire date of every employee who was hired in 1994.

SQL> select LAST_NAME,HIRE_DATE from employees where HIRE_DATE like '%94';

8. Display the last name and job title of all employees who do not have a manager.

SQL> SELECT LAST_NAME,JOB_ID FROM employees WHERE MANAGER_ID IS NULL;

9. Display the last name, salary, and commission for all employees who earn commissions. Sort

data in descending order of salary and commissions.

SQL> SELECT LAST_NAME,SALARY,COMMISSION_PCT FROM employees WHERE COMMISSION_PCT IS NOT NULL ORDER BY SALARY DESC,COMMISSION_PCT DESC;

10. Display the last names of all employees where the third letter of the name is an a.

SQL> SELECT LAST_NAME FROM employees WHERE LAST_NAME LIKE '__a%';

11. Display the last name of all employees who have an a and an e in their last name.

SQL> SELECT LAST_NAME FROM employees WHERE LAST_NAME like '%a%' and LAST_NAME like '%e%';

12. Display the last name, job, and salary for all employees whose job is sales representative or stock clerk and whose salary is not equal to $2,500, $3,500, or $7,000.

SQL> SELECT LAST_NAME,JOB_ID,SALARY FROM employees WHERE (JOB_ID LIKE 'ST_CLERK%' OR JOB_ID LIKE 'SA_REP%')AND SALARY NOT IN (2500,3500,7000) ;

13. Modify lab2_6.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. Resave lab2_6.sql as lab2_13.sql. Rerun the statement in lab2_13.sql.

SQL> select LAST_NAME as "Employee",SALARY as "Molthly Salary",COMMISSION_PCT from employees WHERE COMMISSION_PCT=0.2;