SQL

1
SQL

• Structured Query Language
• Pronounced S-Q-L or Sequel

2
Introduction

• SQL is an industry standard language used for
  querying relational database management systems
  (RDBMS)
• Developers of RDBMSs include their own
  implementation of SQL
• But these are all very much alikeit is a
  standardized language
• If you learn SQL for one RDBMS, it is easy to use
  SQL for another RDBMS
• This is convenient for employees in organizations
  that use several different RDBMSs

3
Underneath

• Much of what Access does behind the scenes is
  done with SQL
• For example, queries created with the design view
  GUI have an underlying text-based SQL
  implementation
• In query design view, you can see the SQL
  equivalent statement by using the SQL View
  button, or by selecting SQL View from the View
  menu
• For basic selections and simple databases,
  Accesss GUI approach (menus and dialog boxes)
  suffice
• For more advanced queries or more complicated
  databases, SQL is necessary

4
SQL Versions

• Originally developed by IBM in late 1970s
• Endorsed by the American National Standards
  Institute (ANSI) in 1992
• This standard is SQL-92
• It is the current standard and is used by MS
  Access
• SQL3 is a later version
• Incorporates some object-oriented concepts
• This version has received little attention from
  commercial DBMS vendors

5
SQL Components

• DDL Data definition language
• Definition/creation of database components, such
  as tables
• DML Data manipulation language
• Allows manipulation of database components
• DCL Data control language
• Provides internal security for a database

6
Basic Syntax

• The language is text based
• SQL uses the SELECT statement to define what data
  to retrieve and how to present that data
• Basic format
• SELECT _____
• FROM _____
• WHERE _____
• ORDER BY _____

7
Basic Syntax

• After SELECT, list the fields to display
• After FROM, list the tables used in the query
• After WHERE, list the selection criteria
• After ORDER BY, list the sort keys
• Enclose field names which have embedded spaces in
  brackets
• Precede a field name with the name of its table
  and connect the table name to the field name with
  a period .
• Separate field names and table names by commas ,
• End a statement with a semicolon

8
Other Commands

• Clause often used in SELECT statements
• GROUP BY _____
• After GROUP BY, list of grouping options
• Used for displaying sums
• SQL contains statements besides the select
  statement
• For example, CREATE TABLE, DECLARE, INSERT INTO,
  OPEN, CLOSE, IF, COMMIT,

9
Select Example

• SELECT Ord Id, P Id, Date, Qty, Price
• FROM Orders
• What does this statement do?

10
What Does it Do?

• SELECT Ord Id, P Id, Date, Qty, Price
• FROM Orders
• Displays the fields Ord Id, P Id, Date, Qty, and
  Price from the Orders table

11
Another Example

• SELECT Ord Id, P Id, Date, Qty, Price
• FROM Orders
• WHERE Qty5
• ORDER BY Date
• What does this statement do?

12
What Does it Do?

• SELECT Ord Id, P Id, Date, Qty, Price
• FROM Orders
• WHERE Qty5
• ORDER BY Date
• Displays the fields Ord Id, P Id, Date, Qty, and
  Price from the Orders table where Qty 5, sorts
  the display by Date

13
Joins

• Recall that Queries may be used to join tables
  together
• This allows fields from various tables to be
  displayed or printed together

14
Types of Joins

• Inner Join is the default and most common
• Records are selected when they have the same
  value in the common field that links the tables
• Left Outer Join
• All records are selected from the first table and
  only those from the second table that have a
  matching value in the common field
• Right Outer Join
• All records are selected from the second table
  and only those from the first table that have a
  matching value in the common field
• Self Join table joined with itself
• Can be either inner or outer join
• Used when there is a field that may contain
  values in another field

15
Joins

• From the query design screen, one may specify the
  type of join by double-clicking the linking line
  to display the join properties
• The default join type is inner

16
Join Definition Using GUI
Inner Join
Outer Join Left Join
Outer Join Right Join
17
Inner Join SQL Example

• SELECT S Id, P Name, S Qty, P Price
• FROM Products INNER JOIN Sales ON Products.P Id
  Sales.P Id

18
Outer Join SQL Example

• SELECT S Id, P Name, S Qty, P Price
• FROM Products LEFT JOIN Sales ON Products.P Id
  Sales.P Id

19
End