Limiting Rows Using a Selection retrieve all employees in department 90

Limiting rows using a selection retrieve all

This preview shows page 93 - 98 out of 352 pages.

Limiting Rows Using a Selection “retrieve all employees in department 90” EMPLOYEES Limiting Rows Using a Selection In the example in the slide, assume that you want to display all the employees in department 90. The rows with a value of 90 in the DEPARTMENT_ID column are the only ones that are returned. This method of restriction is the basis of the WHERE clause in SQL.
Image of page 93
Oracle Database 10 g : SQL Fundamentals I 2 - 4 Copyright © 2006, Oracle. All rights reserved. Limiting the Rows That Are Selected Restrict the rows that are returned by using the WHERE clause: The WHERE clause follows the FROM clause. SELECT *|{[DISTINCT] column|expression [ alias ],...} FROM table [WHERE condition(s) ]; Limiting the Rows That Are Selected You can restrict the rows that are returned from the query by using the WHERE clause. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condition is true, the row meeting the condition is returned. In the syntax: WHERE restricts the query to rows that meet a condition condition is composed of column names, expressions, constants, and a comparison operator The WHERE clause can compare values in columns, literal values, arithmetic expressions, or functions. It consists of three elements: Column name Comparison condition Column name, constant, or list of values
Image of page 94
Oracle Database 10 g : SQL Fundamentals I 2 - 5 Copyright © 2006, Oracle. All rights reserved. SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; Using the WHERE Clause Using the WHERE Clause In the example, the SELECT statement retrieves the employee ID, name, job ID, and department number of all employees who are in department 90.
Image of page 95
Oracle Database 10 g : SQL Fundamentals I 2 - 6 Copyright © 2006, Oracle. All rights reserved. SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen' ; Character Strings and Dates Character strings and date values are enclosed in single quotation marks. Character values are case sensitive, and date values are format sensitive. The default date format is DD-MON-RR. Character Strings and Dates Character strings and dates in the WHERE clause must be enclosed in single quotation marks ( '' ). Number constants, however, should not be enclosed in single quotation marks. All character searches are case sensitive. In the following example, no rows are returned because the EMPLOYEES table stores all the last names in mixed case: SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'WHALEN'; Oracle databases store dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default date display is DD-MON-RR. Note: For details about the RR format and about changing the default date format, see the lesson titled “Using Single-Row Functions to Customize Output.”
Image of page 96
Oracle Database 10 g : SQL Fundamentals I 2 - 7 Copyright © 2006, Oracle. All rights reserved.
Image of page 97
Image of page 98

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture