Transcript sql and query and cross and database

QBE
• A query is a question represented in a way
that the database management system can
recognize and process.
• Query-by-Example offers a very visual way
to construct queries.
Simple and Compound Criteria
• Criteria are restrictions that the records to
be retrieved must satisfy.
• Compound criteria combines multiple
criteria by using the commonly used
operators AND and OR.
QBE
•
•
•
•
•
•
•
Simple retrieval
AND / OR conditions
Two conditions in a single field
Computed fields
Calculating statistics e.g count, average
Grouping
Joins with or without restrictions
Advanced QBE
•
•
•
•
•
•
Pattern match - LIKE
List of Values Match – IN
Non-Matching Value- NOT IN
Parameter query
Crosstabs query
Action queries (Update, Insert, & Delete)
Assignment 3
• MS Access 2000
• Page AC 3.38
• #1-12
The Relational Algebra
• Relational algebra is a theoretical way of
manipulating a relational database.
• Retrieving data from a relational database
involves issuing relational algebra
commands to obtain results.
Relational Operators
•
•
•
•
•
•
•
•
Projection
Selection (restriction)
Union
Difference
Product (Cartesian)
Join
Intersection
Division (hard to do in SQL)
Normal Set Operations
• The union of two tables is a table containing
all rows that are either the first table, the
second table, or both.
• The intersection of two tables is a table
containing all rows that are common to
both.
• The difference of tables A & B is the set of
all rows that are in A, but not in B.
Project
Restrict
Union
Intersection
Difference
Cartesian Product
• The PRODUCT of two tables is a table
obtained by concatenating every row in the
first with every row in the second
Product of Two Tables
JOINS
• Cross join
– Cartesian Product
• Simple or natural join or inner join
– No dangling tuples
• Outer join (full, left or right)
– Includes dangling tuples by padding out with
NULLs
Codd’s Relational System
• Users perceive database as collection of
tables only
• The RESTRICT, PROJECT and JOIN
relations are supported
Classification Scheme
• Tabular structure
– does not support RESTRICT, PROJECT & JOIN
• Minimally relational
– does not support all relational algebra operations
• Relationally complete
– supports a full implementation of SQL
• Fully relational
– supports integrity rules as well
SQL
• Creates the components of a database
• Manipulates components into various
views
– DDL (to define and create database
components)
– DML (to manipulate database components)
– DCL (to provide internal security for the
database)
Why use SQL?
• More powerful than QBE (performs unions
and sub-queries)
• Cannot use the DDL component of SQL in
QBE
• Can be used from within other applications
(Excel, Word, Visual Basic)
• Industry standard language (useful outside
Access)
SQL
• DDL
– CREATE
– ALTER
– DROP
• DML
–
–
–
–
–
Required keywords
SELECT, FROM
Optional clauses
WHERE, ORDER BY
GROUP BY, HAVING
General form of SELECT
•
•
•
•
•
SELECT [DISTINCT] field(s)
FROM table(s)
[WHERE condition]
[GROUP BY field(s) [HAVING condition]]
[ORDER BY field(s)];
Searching with WHERE
• Range search
– BETWEEN / NOT BETWEEN
• Set membership search
– IN / NOT IN
• Pattern search
– LIKE / NOT LIKE
• Null search
– IS NULL / IS NOT NULL
Aggregate Functions
•
•
•
•
•
COUNT
SUM
AVG
MAX
MIN
SubQueries / Nested Queries
• A subselect statement is used following a
relational operator in the WHERE or
HAVING clauses of the outer SELECT
statement
Form/Report Controls
• Bound
– data source is a field in the underlying table
• Unbound
– to display titles, labels and graphics
• Calculated
– data source is an expression, created from one
or more fields
Custom Forms/Reports
•
•
•
•
Use design view
Add header/footer (for form)
Sort/Group Data in a Report
Add Report header/footer (for group totals)
Toolbox
•
•
•
•
List boxes
Combo boxes
Check boxes
Command buttons
Assignment 4
• MS Access 2000
• Page AC 5.55-5.56
• #1-12 (Use SQL for 3-6)