SQL The Structured Query Language

1
SQL The Structured Query Language
2
What is SQL?

• A language for expressing queries to a relational
  database. That is, it is a mechanism for posing
  questions to the database
• Examples
• Which students have a GPA greater than 7.0?
• Which employees have an annual salary of 40,000?
• What are the top 5 rentals for the week from Nov
  10th to 17th?

3
What Does a SQL Query Look Like?

• Basic form
• SELECT DISTINCT target-list
• FROM relation-list
• WHERE condition

4
SQL Query Form

• target-list is a list of fields from the
  relations in the relation-list
• relation-list is a list of the relations/tables
  from which we wish to extract data
• condition specifies criteria which data must meet
  in order to be captured by the query
• Can use , gt, lt, and combine with AND, OR, or NOT
• DISTINCT is optional, removes duplicate entries
  (default is do not remove duplicates)

5
Simple Example

• SELECT sname FROM sailors
• Returns/Matches

6
How does an SQL query work?

• Conceptually
• compute the cross product of all tables in the
  relation-list
• remove all records which do not meet the criteria
  outlined in condition
• remove all fields which are not specified in the
  target-list
• if DISTINCT specified, remove all duplicate rows
• In reality, a RDBMS will do this differently for
  efficiency concerns.

7
Example

• SELECT Sailors.sname
• FROM Sailors, Reserves
• WHERE Sailors.sid Reserves.sid AND Reserves.bid
  103

8
Aliases

• In the last example we had to specify the table
  for each field
• Cumbersome
• Can abbreviate with aliases
• SELECT S.sname
• FROM Sailors S, Reserves R
• WHERE S.sid R.sid AND R.bid 103

9
More Complex SQL

• Can use expressions
• SELECT sname, age 2 FROM sailors
• Would match

10
Even More SQL

• Sorting output
• SELECT bname, Model
• FROM Boats
• ORDER BY bname
• Sorts the output alphabetically by boat name

11
SQL Does More

• SQL is not just for retrieving data
• Can be used for most RDBMS operations
• creating/removing data
• Updating values
• For more info, see CSC 370