STRUCTURED QUERY LANGUAGE SQL

1
STRUCTURED QUERY LANGUAGE (SQL)
SELECT FROM WHERE .
INSERT INTO frank VALUES (ASSO, Associate)

• Prepared By Cheryn Mok

2
What is SQL?

• Originally developed by IBM in 1970s
• It is the standard query language for
  communicating with relational databases
• SQL is desired when communicating between web
  pages and database
• Universal rules established by ANSI (American
  National Standards Institute), accepted by
  database vendors
• Structure and syntax same across most DBMS
  (Oracle, DB2, SQL Server, Access, Sybase)

3
Structured Query Language - Intro

• What does it do?
• Create new records holding data
• Read existing data
• Update existing data
• Delete data
• What does it not do?
• Not a software product but a language
• No front end does not have user forms
• No back end no tools that store data. Not a
  procedural language. It is a set-based language,
  which communicates in statements that define an
  outcome.

4
SQL Standards

• Open standard
• ANSI-SQL / pure SQL
• Vendors differentiate with extra features,
  enhancements
• Oracle PL/SQL
• SQL Server Transact-SQL
• MySQL

5
Microsoft Access

• Serve as both back and and front end
• Separated to
• Application environment (user interface to create
  tables database objects, manage data database
  objects
• Jet database engine (DBMS manages tables and
  processes SQL commands)
• Open Database Connectivity (ODBC)
• Standard communication protocol for the database
  application to interact with different DBMS

6
Open Database Connectivity
Database Application
Access ODBC driver
SQL Server ODBC driver
Oracle ODBC driver
Jet Database Engine
SQL Server DBMS
Oracle DBMS
7
Access Queries

• Query question that can be answered by data
  stored in a database
• Data retrieved from single or multiple tables
• Can include conditions, mathematical functions,
  group retrieved records, and perform group
  functions

• How to create a query
• Click (gt) on the Queries object
• gt New on the toolbar
• gt Design View on the
• New Query window

8
Access Queries

• There are three views on the Query window
• Design view for QBE
• SQL view to enter SQL queries
• Datasheet view to view results

• Query By Example (QBE) is the Access query
  technique that provides pull-down lists for users
  to specify query conditions
• Jet SQL are unique SQL commands used by the Jet
  database engine

9
The Basic SQL Syntax

• The basic SQL expression is in this format
• SELECT column name
• FROM table name(s)
• WHERE conditions
• Users can enter the SQL syntax directly into the
  SQL view. When using QBE, users can see the SQL
  syntax generated clicking on SQL view.

QBE view
SQL view
10
SELECT all columns

• When a user want to retrieve all columns in a
  query, he can use the asterisk () as a wildcard
  character.
• Example
• SELECT FROM Customer

Datasheet view of the results
11
DISTINCT operator

• The DISTINCT operator takes the result of the
  data and drops any duplicate data
• SELECT DISTINCT lastName
• FROM Customer

Without DISTINCT
DISTINCT
12
WHERE and Operators

• When specifying conditions, use the keyword
  WHERE
• WHEREexpressioncomparison operatorexpressio
  n
• Example
• SELECT CustomerNumber,FirstName, LastName
• FROM Customer
• WHERE CreditLimit
• 1500 AND 2000

Text values are case sensitive and must be in
single quotes
13
Comparison Operators
14
Logical Operators
15
Sorting Results

• The keyword ORDER BY organizes the data display
  in an order that is important to the user.
• The user must specify the sort key (column that
  will be used as a basis for ordering data)
• General format for sorting results
• ORDER BY column(s)
• To sort results in ascending order
• ORDER BY column(s) ASC
• To sort results in descending order
• ORDER BY column(s) DESC

16
Sorting Results

• Example
• SELECT FirstName, LastName
• FROM Customer
• ORDER BY LastName

Results with ORDER BY
Access default
17
Performing Mathematical Calculations

• Mathematical operators are used to perform basic
  math calculations
• General Format
• data fieldoperatordata field AS
  alias
• Alias name that serves as the column heading
  when the column is a derived value
• By default, Access assigns a default name, i.e.
  Expr1001
• Aliases must be between 1 to 64 characters long
  and can be numbers, characters and some special
  characters

18
Mathematical Operators

• Example
• SELECT (CreditLimit) (Balance) AS
  AvailableCredit
• FROM . . .

19
Aliases
Alias
Default derived column name
20
Order of Precedence

• SQL evaluates mathematical operators in this
  order of precedence
• Denoting values as positive or negative
• Evaluating multiplication or division operations
• Evaluating addition and subtraction operations
• To change the default order of precedence, the
  user must place an expression within parentheses
  to force it to be evaluated first

21
Data Types

• If your answer displays a wrong data type, you
  can manipulate the data type in a specific way
  using the number function
• Data types
• Character - for data that is not to be used in
  arithmetic expressions
• Numeric - for data that can be used in
  arithmetic expressions
• Time - for data that can be used in time math
• Numeric Data types
• INT, INTEGER
• Only whole numbers
• (no decimals)
• DECIMAL, DEC, NUMERIC
• The range depends on
• scale or precision specified

22
Aggregate Functions

• Aggregate functions work with groups of values
  and reduce them to a single values

23
Aggregate Functions

• Example
• SELECT CreditLimit, COUNT(CustomerNumber)
• FROM Customer
• GROUP BY CreditLimit
• HAVING COUNT(CustomerNumber) gt 1
• When your query involves a group function, and
  there are other columns in your SELECT statement
  that do not require grouping, you must include
  the GROUP BY clause
• HAVING acts like the WHERE keyword, except that
  it works with aggregate functions/ groups

24
Joins (Joining Multiple Tables)

• When data is retrieved from more than one table
  it is known as a JOIN using foreign key
  references
• To qualify the column, the column names that
  exist in more than one table must be preceded by
  the table name.

PK
FK
SELECT SalesRep.lastName, Customer.lastName FROM
SalesRep, Customer
Table name followed by period
25
Joins

• From your WHERE clause, you can specify the table
  and column names on which to join the tables.
  This is know as the join condition
• Join by specifying FK on one side and PK on
  another
• SELECT OrderLine.OrderNumber, Orders.CustomerNumbe
  r, Orders.OrderDate, NumberOrderedQuotedPrice
  
• FROM Orders
• WHERE Orders.OrderNumber OrderLine.OrderNumbe
  r
• ORDER BY OrderLine.OrderNumber DESC

26
Joins

• A Cartesian product occurs if you accidentally
  omit a join condition in a multiple-table query
  every row in one table is joined with every row
  in the other table
• SELECT
• SalesRep.LastName,
• SalesRep.FirstName,
• Customer.LastName
• FROM SalesRep, Customer

27
Comprehensive example

• SELECT customerNumber, Orders.orderNumber,
  orderDate, SUM(numberOrderedquotedPrice) AS
  OrderTotal
• FROM Orders, OrderLine
• WHERE Orders.orderNumber OrderLine.orderNumber
• GROUP BY Orders.ordernumber, customerNumber,
  orderDate
• HAVING SUM(numberOrderedquotedPrice) gt 100
• ORDER BY Orders.orderNumber DESC

28
Aggregate Operators

• UNION combines all the rows that are either in
  the first table, or second table, or both
• INTERSECT combines all the rows that are only in
  both tables
• MINUS/EXCEPT combines a set of rows that are in
  the first table, but are not in the second table

Most DBMS do not support INTERSECT MINUS
Oracle does Access supports UNIONS
29
UNION

• Union requires that the two tables have the same
  structure (union-compatible).
• Union compatible is defined as two relations that
  have the same number of columns and the
  corresponding attributes have the same data type
• General format query 1 UNION query 2
• Example
• SELECT CustomerNumber,lastName
• FROM Customer
• WHERE slsrepNumber 12
• UNION
• SELECT Customer.customerNumber, lastName
• FROM Customer, Orders
• WHERE Customer.CustomerNumber
  Orders.CustomerNumber

30
Insert Records

• When you insert, update and delete data records
  using SQL commands, they are action queries

Action queries change the data that are stored in
the database
31
Insert Records

• General Formats
• INSERT INTO table
• VALUES (column1 value, column2 value
  ...)
• INSERT INTO table(column1, column2, ...)
• VALUES (column1 value, column2 value
  ...)

32
Update Records

• General Formats
• UPDATE table
• SET column1 new data value1, column2
  new data value2, ...
• WHERE search condition

33
Delete Records

• General Formats
• DELETE FROM table
• DELETE FROM table
• WHERE condition

34
Tutorial links

• SQL tutorials
• PL/SQL http//www.hot-oracle.com/showtutorial.htm
  l
• SQL Interpreter and tutorial
• http//www.sqlcourse.com/
• http//sqlcourse2.com/
• Baycon Group SQL tutorial
• http//baycongroup.com/tocsql.htm
• About.com tutorials http//databases.about.com/li
  brary/weekly/aa020401a.htm