Oracle Les02_限制和排序数据.pptx

限制和排序数据;Objectives;课程章节;使用选择功能来限制行;限制选定的行;SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;;SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen ;;比较条件;SELECT last_name, salary FROM employees WHERE salary = 3000 ;;SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ;;SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201) ;;SELECT first_name FROM employees WHERE first_name LIKE S% ;;使用 LIKE 条件;SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;;逻辑条件;SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary = 10000 AND job_id LIKE %MAN% ;;SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary = 10000 OR job_id LIKE %MAN% ;;SELECT last_name, job_id FROM employees WHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP) ;;课程章节;优先顺序规则;SELECT last_name, job_id, salary FROM employees WHERE job_id = SA_REP OR job_id = AD_PRES AND salary 15000;;课程章节;ORDER BY 子句;排序;Sorting;课程章节;替代变量;Substitution Variables;SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = employee_num ;;使用替换变量;SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = job_title ;;指定列名、表达式和文本;SELECT employee_id, last_name, job_id, column_name FROM employees ORDER BY column_name ;;课程章节;使用DEFINE命令;SET VERIFY ON SELECT employee_id, last_name, salary FROM employees WHERE employee_id = employee_num;;Quiz;In this lesson, you should have learned how to: Use the WHERE clause to restrict rows of output: Use the comparison conditions Use the BETWEEN, IN, LIKE, and NULL operators Apply the logical AND, OR, and NOT operators Use the ORDER BY clause to sort rows of output: Use ampersand substitution to restrict and sort output at run time;Practice 2: Overview;1.创建一个查询，显示工资超过12000的员工的姓氏和工资，将你的SQL语句保存到lab2_1.sql文件中，并使用该脚本进行