Structured Query Language

1
Structured Query Language

• and its components

2
SQL

• SQL stands for Structured Query Language.
• There is a standard SQL called the American
  National Standards Institutes 2003 Standard SQL
  (ANSI2003 SQL)
• Most database vendors cover much of the standard,
  but do not adhere to it completely.
• Note, when you start using Oracle SQL, you will
  see that many of the functions are different.

3
Database Vendors

• While there is a standard, there are many
  providers of database systems.
• Some of the providers you may find
• Oracle
• Microsoft SQL Server
• Microsoft Access (very limited)
• MySQL (open source popular for projects)
• PostGres (open source)

4
Some definitions

• Before explaining SQL, lets look at some
  definitions
• Metadata This is data about data, i.e. table
  definitions, column definitions, etc.
• Data This is the value that is held in the
  database, that must follow the rules of the
  metadata.
• Transaction A transaction is a unit of work,
  passed to the database for processing.
• Session A process that connects to the database,
  relating an individual user to a specific
  database (or schema) allowing the user to
  interact with the database, ending when the user
  disconnects from the database.

5
SQL Whats in it?

• SQL is made up of different categories of
  commands
• Data Definition Language
• Data Manipulation Language
• Transaction control statements
• Session or data control statements

6
Data Definition Language (DDL)

• This consists of commands that enable the
  database administrator, in association with the
  application developer, to manipulate the
  infrastructure of the database.
• This infrastructure is known as the conceptual
  schema. It enables definition of the metadata.
• The commands used to do this are
• CREATE
• DROP
• ALTER
• TRUNCATE

7
Data Manipulation Language (DML)

• This consists of commands that enable the
  application developer to manipulate the data in
  the tables.
• The commands used are
• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE

8
Transaction Control Statements

• These statements allow the application developer
  to group DML statements, in order to conduct a
  transaction.
• A transaction is a unit of work, passed to the
  database for processing.
• A transaction will often require
• Selection from one or more tables or views
• Insertion to one or more tables or views
• Update to one or more tables or views
• Deletion from one or more tables.
• Examples
• Place an order for several items
• Register as a student
• Pay a phone bill.

9
Transaction Control Statements

• Most of the statements used during a transaction
  are DML statements (some DDL statements may be
  used).
• There are also transaction safeguard statements.
  These are
• Commit
• Rollback

10
Data Control Language (DCL)

• These consist of statements that allow the
  database (schema) owner to control either his /
  her own sessions, or sessions of other users
  trying to access his / her data.
• The statements are
• GRANT
• REVOKE
• And various SET commands (Session control)

11
So far we have met

• SELECT
• INSERT
• CREATE
• DROP
• Lets look back at them.

12
SELECT

• This statement is very versatile and is the
  single retrieval mechanism.
• Its basic components are
• SELECT
• Field-list
• FROM
• Table-list
• We have tried these in the labs, and will
  continue to do so.

13
SELECT field-list

• The field-list in a SELECT statement can be
• A wildcard character to denote all eligible
  fields.
• A column name that is unique to one of the tables
  in the table list.
• A table-name.column-name to specify a column from
  a specific table from the table list.
• A database.table-name.column-name to specify a
  column in a specific table in a specific database
• Please note Oracle calls the domain in which the
  tables reside a schema. SQL Server calls this
  domain a database.
• A derived field

14
Derived fields in the SELECT field-list

• Fields can be derived by
• Performing calculations on column-fields from the
  table-list.
• E.g. unitprice quantity as linecost
• Using functions on the column-fields from the
  table list.
• E.g. day(orderdate)
• Using database provided functions.
• E.g. SQL Server getdate() gets the current date
  from the system, not from any table.
• Oracle Select sysdate from dual (Dual is a
  working storage area for use in sessions
  connected to the database).

15
Table-list

• Initially, the queries we do will be on single
  tables, but as we get more fluent with SQL, we
  will start to do multi-table selects.
• The tables must belong to the same database /
  schema.
• Sometimes the database/schema name is required to
  qualify the table name.
• See later.

16
Additions to Select

• If you think of a table (e.g. Dog) as a 2D grid,
  the column-list manipulates the columns

DogId Name Weight Age Diet Exercise Breed_Id
1 Goldie 40 3 Standard Standard Glab
2 Mutt 46 2 Standard Standard Glab
3 Spot 44 4 Standard Standard Pood
4 Sooty 55 5 Standard Standard Blab
5 Beauty 50 3 Specialised Specialised Grtv
6 Jack 67 4 Specialised Specialised GDan
7 Pal 55 5 Standard Standard Blab
Ive shortened the column names here to fit it in
the slide.
17
Select name, age from dog

• This query picks out specific columns from the
  table.

DogId Name Weight Age Diet Exercise Breed_Id
1 Goldie 40 3 Standard Standard Glab
2 Mutt 46 2 Standard Standard Glab
3 Spot 44 4 Standard Standard Pood
4 Sooty 55 5 Standard Standard Blab
5 Beauty 50 3 Specialised Specialised Grtv
6 Jack 67 4 Specialised Specialised GDan
7 Pal 55 5 Standard Standard Blab
18
To pick out rows

• This is called projection and is done using the
  WHERE clause.
• This clause goes after the basic select
• SELECT column-list FROM table-list WHERE
  condition
• The condition usually relates to a value in one
  or more of the columns from the column list.

19
Conditions

• The conditions can include
• gt,lt,ltgt,lt,gt, !, NOT, between, IS NULL, IS LIKE.
• The IS NULL returns a true if the value in the
  column is null, and a false otherwise.
• You will NEVER get anything if you use the
  condition WHERE column NULL
• Null means undefined. You cannot equate to
  undefined!

20
LIKE

• LIKE allows us to match patterns in strings.
• Wildcard characters can be used to represent
• A character from a string _
• A variable length substring from a string .

21
Manipulating ROWS

• The WHERE clause allows to choose from specific
  ROWS in our query
• SELECT dogname, dogage FROM dog WHERE dogage
  BETWEEN 2 and 4
• Look back at the exercises we did last week.

22
Formatting output

• We looked at date formatting last week in the
  lab. What other formatting may we want?
• String formatting
• Concatenation
• SQL Server
• Oracle
• Renaming
• AS clause SELECT corder.unitprice as Price or
• SELECT corder.unitprice as Unit Price
• Numeric formatting (use cast, convert (SQL
  Server) and to_char(Oracle).

23
Ordering your data
Dogname DogAge
Mutt 2
Goldie 3
Beauty 3
Jack 4
Spot 4
Sooty 5
Pal 5

• To sort the output by a particular column, add
  the suffix ORDER BY column-name
• E.g. SELECT dogname, dogage FROM dog ORDER BY
  dogage

24
Ordering your data

• You can reverse the order
• SELECT dogname, dogage FROM dog ORDER BY dogage
  DESC

Dogname DogAge
Sooty 5
Pal 5
Jack 4
Spot 4
Goldie 3
Beauty 3
Mutt 2
25
Ordering your data

• You can also order it by two columns
• SELECT dogname, dogage FROM dog ORDER BY dogage,
  dogname DESC

Dogname DogAge
Mutt 2
Goldie 3
Beauty 3
Spot 4
Jack 4
Sooty 5
Pal 5