Transcript Basic queries Sen Zhang

Basic queries
Sen Zhang
Objectives
• Create search conditions in SQL queries
• Learn how to write SQL queries to retrieve data from a
single database table
• Create SQL queries that perform calculations on
retrieved data
• Use SQL group functions to summarize retrieved data
• Learn how to create SQL queries that join multiple
tables
• Understand how to combine query results using set
operators
2
Creating Search Conditions
in SQL Queries
• An expression that seeks to match specific table
records
• Used in SELECT, UPDATE and DELETE statements
• WHERE fieldname comparison_operator
search_expression
• WHERE S_ID = 1
3
Comparison Operators
4
Defining Search Expressions
• Character strings
– Must be enclosed in single quotes
– Case sensitive
• Dates
– Use to_date function with date string and format model
• Intervals
– Use to_yminterval and to_dsinterval with interval string
format model
5
DML - Queries (the Select statement)
select
attribute list
from
table list
where
condition
group by expression
having
expression
order by expression ;
Select fname, salary from employee where salary > 30000;
 fname, salary(salary>30000( Employee))
6
Select-From-Where Statements
• The principal form of a query is:
SELECT
desired attributes
FROM
one or more tables
WHERE
condition about tuples of the tables
7
Meaning of Single-Relation Query
• Begin with the relation in the FROM clause.
• Apply the selection indicated by the WHERE clause.
• Apply the extended projection indicated by the SELECT clause.
8
Operational Semantics
• To implement this algorithm, think of a tuple variable ranging
over each tuple of the relation mentioned in FROM.
• Check if the “current” tuple satisfies the WHERE clause. It
means whether the “current tuple” makes the where clause as a
whole true or not.
• If true, compute the attributes or expressions of the SELECT
clause using the components of this tuple.
9
SQL> select fname, salary from employee;
FNAME
SALARY
------------ --------Ramirez
43000
Reale
38000
Smith
25000
Warren
25000
Wolons
55000
SQL> select fname, salary from employee where salary>30000;
FNAME
SALARY
------------ --------Ramirez
43000
Reale
38000
Wolons
55000
10
SQL> select fname, salary from employee;
FNAME
SALARY
------------ --------Ramirez
43000
Reale
38000
Smith
25000
Warren
25000
Wolons
55000
Wolons
43534
11
Dual table
• Dual table is a special table
– that contains one column (named dummy) and one row (whose
value is simply X).
• Dual table’s data is never meant to be used directly. Instead, the
DUAL table is provided to support on-the-fly calculations
independent from any tables.
• Example
– Select 18*20 from dual;
12
Showing only unique values
Select distinct salary
From employee
Order by salary ;
Select distinct salary
From employee
Order by salary desc;
13
Order by
• Which columns?
• What sorting direction?
14
Order by
• Order by clause can be used together with select command to
achieve the purpose.
• Sort the return data on attributes of select statements
• Sorting direction could be either descending or Ascending
• Select * from employee order by salary desc;
15
* In SELECT clauses
• When there is one relation in the FROM clause, * in the SELECT
clause stands for “all attributes of this relation.”
SELECT *
FROM employees
WHERE salary > 30000;
16
Changing column orders
SQL> select fname, lname from employee;
FNAME
LNAME
------------ -----------Ramirez
Damian
Reale
Michael
Smith
Jason
Warren
Samantha
Wolons
Aimee
17
SQL> select lname, fname from employee;
LNAME
FNAME
------------ -----------Damian
Ramirez
Michael
Reale
Jason
Smith
Samantha Warren
Aimee
Wolons
18
Renaming Attributes
• If you want the result to have different attribute names, use “AS <new
name>” to rename an attribute.
SELECT fname AS firstname,
salary as
monthlyincome
FROM employee
WHERE salary>3000;
“As” is optional in Oracle SQL
19
concatenate
• select fname || ‘ ‘ || lname as "some body" from employee;
20
Expressions in SELECT Clauses
• Any expression that makes sense can appear as an element of a
SELECT clause.
SELECT fname, lname,
salary * 12 AS Yearincome
FROM employee;
21
Another Example: Constant
Expressions
SELECT fname,
‘Low income’ AS whoneedhelp
FROM employee
WHERE salary < 3000;
22
Complex Conditions in WHERE
Clause
• find the salary
SELECT salary
FROM employee
WHERE fname = ‘Smith’ AND
lname = ‘Jason’;
23
Complex Conditions in WHERE
Clause
• find the salary
SELECT salary
FROM employee
WHERE fname = ‘smith’ AND
lname = ‘jason’;
24
Important Points
• Two single quotes inside a string represent the single-quote
(apostrophe).
• Conditions in the WHERE clause can use AND, OR, NOT, and
parentheses in the usual way boolean conditions are built.
• SQL is case-insensitive from the statement point of view. In
general, upper and lower case characters are the same, however,
it is case-sensitive inside quoted strings, as in any other
language.
25
Use in operator to find whether a tuple is
inside a list of tuples
This statement can find a tuple in a list of tuples
select * from employee where (fname, lname) in ((‘john’, ‘bush’));
The following statement won’t work for = sign works for scalar value
only.
select * from employee where (fname, lname) = (‘john’, ‘bush’);
26
Patterns
•
•
WHERE clauses can have conditions in which a string is compared
with a pattern, to see if it matches.
General form:
– <Attribute> LIKE <pattern>
– or <Attribute> NOT LIKE <pattern>
•
Pattern is a quoted string with
– % = “any string”; multiple character
– _ = “any character.” single character
27
Example
select
*
from
employee
where
fname like '%a%‘
;
28
select
*
from
employee
where
fname like '_a%‘
;
29
Use functions
• select * from employee where upper(lname)='JASON';
30
Creating Complex Search Conditions
• Combines multiple search conditions using the
AND,OR, and NOT logical operators.
• AND – both conditions must be true
• OR – one or both condition must be true
• NOT – opposite of actual value
• Use () to group logical operators
31
Retrieving Data from
a Single Database Table
• SELECT fieldname1, fieldname2, ... FROM
ownername.tablename WHERE search_condition;
• To select:
– All rows omit where clause
– All fields, use *: SELECT * FROM …
– Only unique field values: SELECT DISTINCT fieldname
• Search condition:
–
–
–
–
Use comparison and logical operators
IS NULL/IS NOT NULL to match/exclude NULL values
IN/NOT IN to match set values
LIKE with wildcards % and _ to match character strings
32
Sorting Query Output
• Use ORDER BY sort_key_field(s)
• Default order is ascending, use DESC to sort descending
33
Using Calculations in SQL Queries
• Calculations are performed by DBMS, result only sent to client
• Can use arithmetic operators (+, -, *, /)
• Place calculation in select clause: SELECT price * quantity
FROM …
• Calculations can be performed on NUMBER, DATE and
INTERVAL fields only
• Single-row functions: built in Oracle functions to perform
calculations and manipulate retrieved data values
34
Aggregation functions
group by
having
35
• Aggregate functions (like count or SUM) return the aggregate of
all column values every time they are called, and
36
Oracle8i SQL Group Functions
• Group functions: perform an operation on a group of queried
rows and returns a single result
37
Group By
• Use to group output by the field with duplicate values and apply
group functions to the grouped data
38
Having
• Use to place a search condition on results of group function
calculations
• Like “WHERE” for group functions
• HAVING group_function comparison_operator value
• HAVING SUM(capacity) >= 100
39
GROUP BY...
• The GROUP BY clause can be used in a SELECT statement to
collect data across multiple records and group the results by
one or more columns.
• Without the GROUP BY function it was impossible to find the
sum (or other aggregate data) for each individual group of
column values.
40
Group by
• The syntax for the GROUP BY clause is:
• SELECT column1, column2, ... column_n, aggregate_function
(expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;
• aggregate_function can be a function such as SUM, COUNT,
MIN, or MAX.
41
Example using the SUM function
•
•
As an example, we illustrated how you could use the SUM function to
return the name of the department and the total salary (in the
associated department).
For any column in your SELECT statement that is not encapsulated in
the SUM function or other aggregate function, you must use a GROUP
BY clause. The department field must, therefore, be listed in the
GROUP BY section.
42
Aggregate functions
• Aggregate functions operate against a collection of values, but
return a single value.
• Designed to give you information about subsets of your data,
with groupings defined in any way you please.
43
Five aggregate functions
•
•
AVG(column) Returns the average value of a
Count
– COUNT(column)Returns the number of rows (without a NULL value) of a
column
– COUNT(*)Returns the number of selected rows
– COUNT(DISTINCT column)Returns the number of distinct results
•
•
MAX(column)Returns the highest value of a column
MIN(column)Returns the lowest value of a column
44
GROUP BY...
• It was added to SQL because aggregate functions (like SUM)
return the aggregate of all column values every time they are
called, and without the GROUP BY function it was impossible to
find the sum for each individual group of column values.
45
The syntax for the GROUP BY function
• SELECT column,SUM(column) FROM table GROUP BY column
46
Example
• SELECT Company,SUM(Amount) FROM Sales GROUP BY
Company
47
HAVING...
Example
• HAVING... was added to SQL because the WHERE keyword
could not be used against aggregate functions (like SUM), and
without HAVING... it would be impossible to test for result
conditions.
48
The syntax for the HAVING function is:
SELECT column,SUM(column)
FROM table
GROUP BY column
HAVING SUM(column) condition value
49
SELECT Company,SUM(Amount)
FROM Sales
Where amount>10
GROUP BY Company
HAVING SUM(Amount)>20020
50
SELECT Company,SUM(Amount)
FROM Sales
Where amount>10
GROUP BY Company
HAVING SUM(Amount)>20000
51
SELECT Company,SUM(Amount)
FROM Sales
GROUP BY Company
HAVING SUM(Amount)>20000
What is different between this slide and the previous slide?
52
• For more examples, see script file distributed in our class
53
Formatting Numbers and Dates
• Use to_char function with format models
• TO_CHAR(field_name, 'format_model')
• SELECT inv_id, TO_CHAR(inv_price, '$99,999.99')
FROM inventory WHERE item_id = 1;
54
Format Models
• Used to format data retrieved from database
• Can be used to format a date to display time
or a number to display as a currency
• NUMBER, DATE and INTERVAL data types can be converted
to and from character strings using format models
55
Numerical Format Models
56
Date Format Models
57
Date Format Models
58
Join
59
Why we need to select information from
multiple tables
•
•
At table design stage, the information about an application has been stored
in separate tables, among them, attributes in different tables are linked
together either explicitly by declared as foreign key constraints or implicitly
by other means.
When we ask questions against database, on the other hand, we usually
need information which cannot be supplied from a single table.
60
Example
SQL> select * from student;
SSN
NAME
---------- -------------------1234
daniel
2222
kathy
4543
john
61
SQL> select * from enrollment;
SSN
CLASSNAME
---------- ---------1234
csci100
1234
csci456
2222
csci435
2222
csci400
2222
csci201
62
You need to retrieve
• SSN, student name, enrolled class information in a tabulated form.
SSN
NAME
CLASSNAME
---------- -------------------- ---------1234
daniel
csci100
1234
daniel
csci456
2222
kathy
csci435
2222
kathy
csci400
2222
kathy
csci201
We have to join data from two tables to for the above information.
63
• Joining data together is one of the most significant strengths of a
relational database.
• Joins allow database users to combine data from one table with
data from one or more other tables or views, or synonyms, as
long as they are relations.
64
• Tables are “joined” two at a time making a new relation (a table
generated on the fly) containing all possible combinations of
rows from the original two tables (sometimes called a “cross
join” or “Cartesian product”).
• See sample script
65
• A join condition is usually used to limit the combinations of table
data to just those rows containing columns that match columns
in the other table.
• Most joins are “equi-joins” where the data from a column in one
table exactly matches data in the column of another table.
66
• It is also possible (though usually less efficient) to join using
ranges of values or other comparisons between the tables
involved.
• A table may be “joined” to another table, tables, or even itself,
67
•
•
•
It is important to understand that whenever two or more
tables/views/synonyms (in fact, they are all relations) are listed in a
FROM clause, a join results.
Join conditions serve the purpose of limiting the number of rows
returned by the join.
The absence of a join condition results in all possible combinations of
rows from the involved tables which is usually not useful information.
68
What is an outer join
• An outer join extends the result of a simple join (inner join, equjoin, theta join or natural join).
• An outer join returns all rows that satisfy the join condition and
those rows from one table for which no rows from the other
satisfy the join condition. Such rows are not returned by a
simple join.
69
Outer join in Oracle 8i
• The syntax for performing an outer join in SQL is databasedependent.
• For example, in Oracle 8i, we will place an "(+)" in the WHERE
clause on the other side of the table for which we want to
include all the rows.
70
What is an outer join
• To write a query that performs an outer join of tables A and B
and returns all rows from A, apply the outer join operator (+) to
all columns of B in the join condition.
• For all rows in A that have no matching rows in B, Oracle
returns NULL for any select list expressions containing columns
of B.
71
• See sample scripts distributed in class for various outer joins
72
Joining Multiple Tables
• Join: combine data from multiple database tables using foreign
key references
• SELECT field1, field2, ... FROM table1, table2 WHERE
table1.joinfield = table2.joinfield AND search_condition(s);
• If tables share field names, must prefix field in select with table
name (table1.field1, table2.field1)
• Join condition: part of where clause indicating how tables are
related (table1.foreign_key = table2.primary key)
• Search conditions can be added to join condition using AND
operator
73
Inner Join
• Join two tables based on values in one table being equal
to values in another table
• Also known as equality join, equijoin or natural join
• Returns results only if records exist in both tables
74
Joining Via Linking Table
75
Using a Query Design Diagram
• Helpful for creating complicated queries
• Can use a formula to derive actual query from diagram
76
Outer Join
• Returns all rows in one table and matching rows in joined table
• Inner table: all rows are returned
• Outer table: matching rows are returned
• Outer table marked with a + in join condition
• inner_table.join_field = outer_table.join_field(+)
• Null values are inserted for fields in outer table that are not
found
77
Self-Join
• A query that joins a table to itself
• Used when a table has a foreign key relationship to itself
(usually parent-child relationship)
• Must create a table alias and structure the query as if you are
joining the table to a copy of itself
• FROM table1 alias1, ...
• Use alias, not table name for select and where clauses
78
Self-Join Example
79
Using Set Operators
To Combine Query Results
• Use to select data from multiple tables not connected with
foreign key relationships
80
Set Operators
•
query1 OPERATOR query2; (where operator is UNION, UNION ALL,
INTERSECT, or MINUS)
•
Both queries must have same number of select fields and same data
types in same order
•
UNION suppresses duplicate values
•
UNION ALL includes duplicate values
•
INTERSECT takes only matching fields
•
MINUS takes only fields in query1 not query2
81
SQL NULL (unknown) and threevalued logic
• Null is a special marker used to indicate that a data value is
unknown in the Structured Query Language (SQL). Intuitively,
NULL approximately represents an unknown value.
• Boolean logic is based on two values: true and false
• SQL adopts a super boolean logic, so called three value logic,
which is built upon three values, unknown is added.
• The difference between NULL and unknown is very subtle,
usually treated as the same.
• Unfortunately, if you have deal with NULL in real databases, the
results can be downright frustrating. Some of the SQL rules
surrounding NULL can be surprising or unintuitive.
82
NULL Basics
• An arithmetic operation involving a NULL returns NULL. For
example, NULL minus NULL yields NULL, not zero.
• A boolean comparison between two values involving a NULL
returns neither true nor false, but unknown in SQL's threevalued logic. For example, neither NULL equals NULL nor
NULL not-equals NULL is true.
• An SQL query selects only values whose WHERE expression
evaluates to true, and groups whose HAVING clause evaluates
to true. Otherwise, ( either false or NULL) will won’t select the
tuple.
• The aggregate COUNT(*) counts all NULL and non-NULL
tuples; COUNT(attribute) counts all tuples whose attribute value
is not NULL. Other SQL aggregate functions ignore NULL
values in their computation.
83
Null values
• A null value does not match any criterion, except one that
explicitly checks for a null value.
• IS NULL or IS NOT NULL
Select * from employee
Where dno IS NULL;
Testing whether a value is NULL requires an expression such as IS
NULL or IS NOT NULL.
84
SQL built-in functions
85
SQL built-in functions
• SQL has a lot of built-in functions for counting and
calculations to make your oracle applications easier to use,
more powerful and more efficient.
• Just like C\C++ and Java, which have library functions, so
called build-in functions.
86
User defined function!
• Oracle allow users to design their own functions using
PL/SQL.
• Will be covered later!
87
SQL built-in functions
• Function Syntax
• The syntax for built-in SQL functions could be:
SELECT function(column) FROM table
88
Types of built-in Functions
• Roughly speaking, there are two basic categories of functions in
SQL.
• The basic types of functions are:
– Scalar functions, also called single-row functions, and it usually
works on single attribute value or scalar value.
– Aggregate Functions, also called group functions.
89
Single row (Scalar) functions
• They perform operations that process some specific
attribute of every row in a table.
• Row-by- row control over how the data in your database is
entered, retrieved, or presented.
90
Single row functions categories
•
•
•
•
•
•
System variables
Number
Character (text)
Date
Conversion of different data types.
Miscellaneous
91
System variables
• Maintained by Oracle system to provide with information about
the environment in which the database is running.
– Sysdate
– User
– Userenv
92
System variables Advanced Functions:
•
•
•
•
•
•
•
Case Statement
Decode
Sys_ContextUser
Coalesce
NVL
User
UserEnv
93
Number functions
• Manipulate numeric values, changing them to suit your needs.
– Round (..)
– Trunc(..)
94
More mathemetical functions
•
•
•
•
Abs
Min
Tan
Many others …
95
Text functions
• Also called character functions, manipulating text strings type
data.
–
–
–
–
–
Upper, lower, initcap
Length
Substr
Instr
Ltrim, rtrim
96
Character / String Functions
•
•
•
•
•
•
Pad
Substr
Dump
Translate
Compose
Many others …
97
Data Type Conversion Functions
•
•
•
•
•
•
Cast
To_Char
To_Date
To_Number
To_Timestamp
Many others …
98
Date functions
•
•
•
•
•
•
Rich set functions to make it easier to work with dates.
Sysdate and Trunc
Add_months
Extract(year of ..)
Last_day()
Months_between
99
More Date function
•
•
•
•
•
•
•
•
•
•
Add_Months
New_Time
Sysdate
Trunc
Timestamp
Last_Day
Round
To_Char
Months_Between
More …
100
Other functions
• Decode
101
NVL
• This function performs a simple but useful function: whenever it
is presented with a value that is null, it will return the indicated
value.
•
NVL(input_value, result_if_input_value_is_null)
•
Select product_name, nvl(last_stock_date, ’01-jan-2001’);
102