Transcript Structured Query Language

Structured Query Language
• used for defining and manipulating data in
Relational DBs
• aimed at:
– reducing training costs
– increasing productivity
– improve application portability
– increase application longevity
– reduce dependency on single vendors
– enable cross systems communication
• In practice, SQLs can be a bit different
Querying RDBs with SQL
• use a form of pseudo english to retrieve data in a
view (which looks like a table)
• syntax is based on a number of “clauses”
• Select: specifies what data elements will be
included in the view
• From: lists the tables involved
• Where: specifies conditions to filter the data
– specific values sought
– links between tables
Example with one table
• find the name and address of
customer number 12
Example with a range
• find the movies that are classified as
adventure
Example with two tables
• List all movies with their associated
type
Example with two tables
• same for Lucas movies only
Use of a Search Condition nested queries
• find the name and address of the
customer(s) who has rented star wars
Additional syntax
• Add computation in the “select” statement:
– select SUM(price)
– select AVG(price), MAX, MIN, COUNT
• Simplify comparisons with a BETWEEN clause and
LIKE clause (with *, ?)
• Add sorting instruction after the where clause
– ORDER BY name (alphabetical)
– ORDER BY price (ascending)
• Provide aggregate information by grouping data:
– GROUP BY customer
• Compute fines for a rental
• find the average duration of the
movies for rent
• find the average duration of all
completed rentals
• find how much cash customer “Fred” has
generated in total