Structured Query Language

1
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

2
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

3
Example with one table

• find the name and address of customer number 12

4
Example with a range

• find the movies that are classified as adventure

5
Example with two tables

• List all movies with their associated type

6
Example with two tables

• same for Lucas movies only

7
Use of a Search Condition - nested queries

• find the name and address of the customer(s) who
  has rented star wars

8
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

9

• Compute fines for a rental

10

• find the average duration of the movies for rent
• find the average duration of all completed
  rentals

11

• find how much cash customer Fred has generated
  in total