初级认证培训——SQL基本开发.pptVIP

The MINUS Operator Use the MINUS operator to return rows returned by the first query that are not present in the second query (the first SELECT statement MINUS the second SELECT statement). Guidelines The number of columns and the datatypes of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical. All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator to work. Instructor Note To illustrate the MINUS operator, run the script demo\15_minus.sql. Using the CASE Expression In the preceding SQL statement, the value of JOB_ID is decoded. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary. The same statement can be written with the DECODE function. Using the DECODE Function In the preceding SQL statement, the value of JOB_ID is tested. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary. The same statement can be expressed in pseudocode as an IF-THEN-ELSE statement: IF job_id = IT_PROG THEN salary = salary*1.10 IF job_id = ST_CLERK THEN salary = salary*1.15 IF job_id = SA_REP THEN salary = salary*1.20 ELSE salary = salary Subqueries You can build powerful statements out of simple ones by using subqueries. Subqueries can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself or some other table. Subqueries are very useful for writing SQL statements that need values based on one or more unknown conditional values. In the syntax: operator includes a comparison operator such as , =, or IN Note: Comparison oper