SQL Select Statement

About This Presentation

Title:

SQL Select Statement

Description:

... wish to display all the attributes and all the rows in the ... SELECT attributes. FROM tableName. WHERE attribute=condition; Selection using a SELECT statement ... – PowerPoint PPT presentation

Number of Views:31

Avg rating:3.0/5.0

Slides: 27

Provided by: hopeliv

Category:

more less

Transcript and Presenter's Notes

Title: SQL Select Statement

1
SQL Select Statement

Database Technology

2
Aims

To introduce
SELECT statement
FROM clause
WHERE clause
AND and OR conditional statements
Relational Operators

3
Aims

To Demonstrate
Retrieving attributes from more than one table
Hiding duplicate values in the result of a query.

4
SQL SELECT Statement

SQL SELECT statement allows to ask questions of
our stored data.
SELECT from
Tables in our database
Within the tables
Particular records (rows)
Particular attributes (columns)

5
Tables in our database
Imagine we wish to display all the attributes and
all the rows in the supplier table
6
Simplest SELECT statement

SELECT allFields
FROM singleTable
SELECT
FROM tblSupplier

7
Simplest SELECT statement result
All attributes and all records in the supplier
table
8
Projection using a SELECT statement

Projection is where we select only particular
attributes (columns) in our table.
SELECT attribute1, attribute2
FROM tableName

9
Result of Projection
SELECT supplierID, suppName FROM tblSupplier
10
Selection using a SELECT statement

To select only particular rows we must add an
additional clause to our select statement.
The WHERE clause.
contains a condition
rows are checked against the condition
only rows which match the condition will be
returned in the result.

11
Selection using a SELECT statement

SELECT attributes
FROM tableName
WHERE attributecondition

12
Selection using a SELECT statement

SELECT supplierID, suppName, suppAdd1, suppAdd2,
suppPostCode
FROM tblSupplier
WHERE suppName"Mayumi's"

13
Combination using Selection and Projection

SELECT supplierID, suppName
FROM tblSupplier
WHERE suppName"Mayumi's"

14
SQL Relational Operators

SQL uses the following simple relational
operators.
equals
lt is less than
gt is greater than
lt is less than or equal
gt is greater than or equal
ltgt is not equal

15
Records in the products table
16
Selection using a relational operator

SELECT prodID, prodDesc, cost
FROM tblProduct
WHERE cost gt 20

17
AND and OR

Combining conditions in the WHERE clause.

Both conditions must be true for a record to be
returned.
Either condition may be true for a record to be
returned

18
AND

SELECT prodID, prodDesc, cost, category
FROM tblProduct
WHERE costgt30
AND category"Confections"

19
OR

SELECT prodID, prodDesc, cost, category
FROM tblProduct
WHERE costgt30
OR category"Confections"

20
Selecting data from more than one table (JOINING
TABLES)

SELECT TableName1.ColumnName1, TableName1.ColumnNa
me2, TableName2.ColumnName3
FROM TableName1, TableName2
WHERE TableName1. ColumnName1
TableName2.ColumnName3

21
Selecting data from more than one table (JOINING
TABLES)

SELECT tblSupplier.supplierID, suppName, orderID,
orderDate
FROM tblSupplier, tblOrder
WHERE tblSupplier.supplierID tblOrder.supplierID

22
Result of Query
Only suppliers with orders are shown. Any
supplier without at least one related order is
not returned in the result.
23
Hiding duplicates

We can therefore choose to see only the name of
any supplier who has associated orders.

SELECT suppName FROM tblSupplier, tblOrder WHERE
tblSupplier.supplierID tblOrder.supplierID
24
Query result
Notice that this query however produces duplicate
names, we can remove these duplicates by refining
the query.
25
Hiding Duplicate Rows