Structured Query Language

1
Structured Query Language

• Overview
• Creating Tables
• Inserting, Modifying, and Deleting Data
• select Statement
• select Statement Clauses
• Statistical Functions
• Complex Queries
• Joins
• Nested Queries (Subselects)

2
Overview

• The Structured Query Language (SQL) arose out of
  an experimental project at IBM to develop a
  relational DBMS in the late 1970s
• Occurred in an environment in which there was a
  lot of excitement about the relational data model
• Other developers of RDBMSs adopted SQL as the
  database language for
• creating relations
• inserting data
• modifying data
• retrieving data

3
Importance of SQL

• Portable across
• hardware platforms
• operating systems
• vendors
• Relatively simple command set
• Data Definition CREATE, DROP, ALTER
• Data Manipulation SELECT, UPDATE, INSERT,
• DELETE
• Data Security GRANT, REVOKE

4
Some Mechanics

• Oracle is running in this Lab on machines 02,
  03, 05, 06, 07, 08, 10, 11
• Locate the SQLPLus icon and copy to your
  desktop (C\ORANT\BIN\)
• You have each been given a user account
• User name initiallastname
• Password initiallastname
• Host string robinnew
• You will be taken to the SQLgt prompt, at which
  point you may enter SQL commands such as the
  examples which follow

5
More Mechanics

• In what follows
• reserved words in SQL are printed in courier
• user-specific input is printed in italics
• optional parts of commands are in brackets
• all statements must end with a semicolon
• the terms
• relation and table,
• tuple and row, and
• attribute and column
• are interchangeable

6
Creating Tables

• General Structure
• create table table-name ( attribute-name
  datatype not null default def-value )
• where
• table-name is the name of a relation
• denotes a list of one or more items
• attribute-name is the name of an attribute
• datatype is a recognized SQL data type
• def-value is an optional default value for tuples
• Every table must contain at least one attribute,
  and a may contain a maximum of 255 attributes

7
Creating Tables(contd)

• Four basic data types
• number(precision, scale)
• e.g., quantity number(6,2)
• char(length)
• e.g., cname char(25)
• long
• date
• Numerous derived types
• e.g., int, double precision, float

8
Creating Tables(contd)

• Examples
• create table customers (cid char(4) not null,
  cname varchar(13), city varchar(20), discnt
  number(4,2) default 0.0)
• create table agents (aid char(3) not null, aname
  varchar(13), city varchar(20), percent float(4,2)
  default 5.00)

9
Creating Tables(contd)

• Dropping Tables
• General Structure drop table table-name
• Example drop table customers
• Altering Table Structure
• General Structure alter table table-name add
  (att-name datatype) modify att-name
  (datatype not null)

10
Inserting Data

• General Structure
• insert into table-name (att-name)
• values (expression) ... another method
• Example
• insert into customers (cid, cname, city, discnt)
  values (c001, Tip Top, Duluth, 7.00)

11
Modifying Data

• General Structure
• update table-name set att-name expression
  where search-condition
• Replaces the values of specified attributes with
  values of specified expression for all tuples
  satisfying the search condition
• Examples
• update customers set discnt 10.00 where
  cidc001
• update customers set discnt discnt0.5

12
Deleting Data

• General form
• delete from table-name where condition
• Examples
• delete from customers where city Duluth
• removes tuples c001 and c004
• delete from customers
• removes all tuples from customer
• (but does not drop the table from the database)

13
select Statement

• Provides the query capability in SQL
• Based on the relational algebra
• Three basic components of a statement
• select ...
• from ...
• where ...
• We will begin with several simple examples, then
  consider the clauses of the statement in more
  detail
• Warning select statements can become very
  complex

14
select Statement(contd)

• General structure
• select attribute
• from table
• where (condition)
• In this structure
• attribute is a list of one or more attributes
• table is a list of one or more tables
• condition is a simple or complex condition that
  determines which tuples will be included in the
  result

15
select Statement(contd)

• Simple examples
• select aid, aname
• from agents
• select aname, city
• from agents
• where city New York
• select cname
• from customers
• where (discnt gt 8 or city Dallas)

16
select Statement Clausesselect and from

• Simplest form Lists all attributes and all
  tuples
• select from customers
• Displays the contents of the specified table
• Simple form with projection
• select cname, discnt from customers
• Projects the specified attributes
• Simple form with projection and no duplicates
• select distinct cid, aid, pid from orders
• Eliminates duplicates in the projection

17
select Statement Clauseswhere

• Simplest form
• select from products
• where price 1.00
• Selects tuples satisfying a simple relational
  condition
• , lt, gt, lt, gt, (!, ltgt, or )
• Compound condition
• select from customers
• where city Dallas or city Duluth
• select cid from customers
• where cname ACME and discnt gt 0

18
select Statement Clauseswhere - contd

• Condition with ranges not between
• select cname, city, discnt from customers
• where discnt between 5 and 10
• select pid, pname, quantity from products
• where price not between 0.75 and 1.50
• Conditions with values from a known set not in
• select ordno, qty from orders
• where qty in (500, 1000, 1500)
• Note other equivalent ways to express these
  queries

19
select Statement Clauseswhere - contd

• Conditions involving string patterns not like
• select pname from products
• where pname like p
• p is called a pattern or mask
• _ may be used instead of
• either may appear before or after a partial
  string of characters
• Conditions involving null values is not null
• select from customers
• where discnt is not null

20
select Statement Clausesgroup by

• Previous form does not allow the grouping to be
  restricted by tuples satisfying some condition
• simple form
• select pid from orders
• group by pid
• Produces same result as select distinct ...
• select cid, count(dollars), sum(dollars) from
  orders
• group by cid
• Uses some statistical functions which provide
  much more useful results

21
select Statement Clausesgroup by ... having

• Previous form does not allow restriction of
  result to groupings satisfying certain conditions
• having form
• select cid, sum(dollars)
• from orders
• where month mar or month feb
• group by cid
• having sum(dollars) gt 1000
• Illustrates degree to which queries can become
  complex

22
select Statement Clausesorder by

• Results of a query are often more useful if
  ordered in a particular way
• E.g., Orders listed from largest to smallest
  dollar amount
• Achieved using the order by clause
• select ordno, pid, dollars from orders order by
  dollars desc
• select ordno, pid from orders
• order by dollars desc, pid
• Ordering can also be done by an expression

23
Statistical Functions

• SQL supports the following functions for
  summarizing data
• count counts the of distinct values of
  attribute among tuples in a table (or total
  number of tuples)
• min, max smallest and largest values of an
  attribute
• sum sums values of an attribute
• avg finds mean of values of an attribute
• stddev computes standard deviation of values
• variance computes variance of values

24
Statistical FunctionsExamples

• select count() from orders
• select count (distinct city)
• from customers
• select min(dollars), max(dollars)
• from orders
• select sum(qty) from orders
• where (pid in
• (select pid from orders
• where cid c001)
• and cid c001)
• select avg(dollars) from orders

25
Complex QueriesUnion

• union operator implements the union operation of
  relational algebra
• Recall union compatibility requirement
• select from customers union
• select from inactive_customers
• select city from customers union
• select city from agents

26
Complex QueriesIntersect

• intersect operator implements the intersect
  operation of relational algebra
• Recall compatibility requirement
• select from customers intersect
• select from inactive_customers
• select city from customers intersect select city
  from agents

27
Complex QueriesMinus

• minus operator implements the difference
  operation of relational algebra
• Recall compatibility requirement
• select from customers minus
• select from inactive_customers
• select distinct pid from products minus select
  distinct pid from orders

28
Cartesian Product and Join

• Join operation provides the power to answer
  queries involving multiple tables
• Many commercial versions of SQL do not have an
  explicit join command
• Various versions of the join are subsets of the
  Cartesian product of two relations
• General Form Theta join
• select attribute-list
• from table-1, table-2
• where table-1.attribute theta
• table-2.attribute

29
Cartesian Product and JoinExample

• select p.quantity, o.qty
• from orders o, products p
• where p.pid o.pid
• When more than one table is involved in a query,
  table-name.attribute-name resolves ambiguity in
  the case of common attribute names across tables
• p and o are aliases which ease the composition
  and clarity of queries

30
Cartesian Product and JoinExamples

• Retrieve the names of customers and agents
  involved in all orders
• select distinct customers.cname, agents.aname
• from customers, orders, agents
• where customers.cid orders.cid and
• orders.aid agents.aid

31
Nested Queries (Subselects)

• Complex queries can be generally broken down into
  a series of simpler queries
• Tables can be created as intermediate results
  of such queries
• Can be cumbersome and inefficient
• Queries involving several distinct steps can be
  handled by embedding subqueries in the where
  clause
• in and exists operators play a useful role

32
Nested Queries (Subselects)Example

• select distinct orders.cid from orders
• where orders.aid in
• (select aid from agents
• where city Duluth
• or city Dallas)
• Conceptually, the inner select return a set of
  values (aids) to the outer select
• lhs in rhs evaluates to true (i.e., tuple is
  included) when the value of the expression on lhs
  is contained in the set of values constituting
  the rhs

33
Nested Queries (Subselects)Example

• select cid from orders x
• where pid p01 and exists
• (select from orders
• where cid x.cid and pid p07)
• exists evaluates to true when the subquery is not
  empty
• Above example can be expressed without a subquery

34
Nested Queries (Subselects)Example

• select c.cname from customers c
• where not exists (select from orders x
• where c.cid x.cid and x.aid a05)
• Clearly, SQL queries can become quite complex and
  unintuitive
• Dont worry if you dont fully understand all
  queries at this point
• Best way to learn is to make up queries and
  practice formulating them