[go: up one dir, main page]
Scribd
Upload
72 views14 pages

Database Programming With SQL: 2-2 Limit Rows Selected

Uploaded by

Abhay Kumar Sharma BOODHOO
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
72 views14 pages

Database Programming With SQL: 2-2 Limit Rows Selected

Uploaded by

Abhay Kumar Sharma BOODHOO
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 14

Database Programming with SQL

2-2
Limit Rows Selected

Copyright © 2017, Oracle and/or its affiliates. All rights reserved.


Objectives

This lesson covers the following objectives:


• Apply SQL syntax to restrict the rows returned from a query
• Demonstrate application of the WHERE clause syntax
• Explain why it is important, from a business perspective, to be
able to easily limit data retrieved from a table
• Construct and produce output using a SQL query containing
character strings and date values

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 2
Limit Rows Selected
SELECT Statement

• You use SELECT to retrieve information from the database.


• A SELECT statement must include at a minimum a SELECT
clause and a FROM clause.
• The WHERE clause is optional.
SELECT*|{[DISTINCT] column | expression alias]..}
FROM table
[WHERE condition(s)];

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 3
Limit Rows Selected
WHERE Clause

• When retrieving data from the database, you may need to


limit the rows of data that are displayed.
• You can accomplish this using the WHERE clause.
• A WHERE clause contains a condition that must be met, and it
directly follows the FROM clause in a SQL statement.
• The syntax for the WHERE clause is:
WHERE column_name comparison_condition comparison_value

• Note: An alias cannot be used in the WHERE clause!

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 4
Limit Rows Selected
WHERE Clause

• Examine the following SQL statement from the


Employees database:
SELECT employee_id, first_name, last_name EMPLOYEE_ID FIRST_NAME LAST_NAME
FROM employees;
100 Steven King
101 Neena Kochhar
102 Lex De Haan

• By adding a WHERE clause, the rows are limited to those rows


where the value of employee_id is 101.
SELECT employee_id, first_name, last_name
FROM employees EMPLOYEE_ID FIRST_NAME LAST_NAME
WHERE employee_id = 101; 101 Neena Kochhar

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 5
Limit Rows Selected
Comparison Operators in the WHERE
Clause
• As you saw on the previous slide, the = sign can be used in
the WHERE clause.
• In addition to the "equal to" operator (=), other comparison
operators can be used to compare one expression to another:
– = equal to
– > greater than
– >= greater than or equal to
– < less than
– <= less than or equal to
– <> not equal to (or != or ^=)

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 6
Limit Rows Selected
Comparison Operators in the WHERE
Clause
• In the example below, the department_id column is used in
the WHERE clause, with the comparison operator = .
• All employees with a department_id of 90 are returned.
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 90;

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID


100 King 90
101 Kochhar 90
102 De Haan 90

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 7
Limit Rows Selected
Character and Date Strings in the WHERE
Clause
• Character strings and dates in the WHERE clause must be
enclosed in single quotation marks ' '.
• Numbers, however, should not be enclosed in single
quotation marks.

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 10
Limit Rows Selected
Character and Date Strings in the WHERE
Clause
• Look at the following example from the Employees database.
• The WHERE clause contains a string and is enclosed in single
quotation marks.
SELECT first_name, last_name
FROM employees
WHERE last_name = 'Taylor';

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 9
Limit Rows Selected
Character and Date Strings in the WHERE
Clause
• What do you think will happen if the WHERE clause is written
as:
WHERE last_name = 'jones';

• All character searches are case-sensitive.


• Because the employees table stores all the last names in the
proper case, no rows are returned in this example.

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 10
Limit Rows Selected
Character and Date Strings in the WHERE
Clause
• This is an important point to remember.
• In another lesson, you will learn to use other SQL keywords
UPPER, LOWER, and INITCAP that will make it easier to avoid
a case-sensitive mistake.

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 11
Limit Rows Selected
Comparison Operators in the WHERE
Clause
• Comparison operators can be used in all of the following ways
in the WHERE clause:
WHERE hire_date < '01-Jan-2000'

WHERE salary >= 6000

WHERE job_id = 'IT_PROG'

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 12
Limit Rows Selected
Comparison Operators in the WHERE
Clause
• In the following example from the Employees database,
which rows will be selected?
• Will salaries of 3000 be included in the result set?
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 13
Limit Rows Selected
Summary

In this lesson, you should have learned how to:


• Apply SQL syntax to restrict the rows returned from a query
• Demonstrate application of the WHERE clause syntax
• Explain why it is important, from a business perspective, to be
able to easily limit data retrieved from a table
• Construct and produce output using a SQL query containing
character strings and date values

DPS2L2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 14
Limit Rows Selected

You might also like