SQL Structured Query Language

1
SQL (Structured Query Language)

• Retrieving Data Using SQL

2
SQL

• SQL Structured Query Language
• The standard language for relational databases
• Non-Procedural Language
• Tables are manipulated implicitly by specifying
  conditions that need to be satisfied by the data

3
SQL Command Categories

• DML Data Manipulation Language
• DDL Data Definition Language
• DCL Data Control Language

4
Sample Database for SQL Lectures

• ACCOUNT (AcctNo, AcctName, Phone, Balance)
• PRODUCT (ProdNo, Descrip, Price, QtyHand,
  VendCode)
• SALESREP (RepId, Lname, Fname, Rep_Phone,
  CommRate)
• ORDER (OrderNo, OrderDate, OrdRep, AcctNo,
  OrdAmt, DelvDate, PaidFlg)
• ORDERLINE (OrderNo, ProdNo, OrderQty )

5
Sample Database for SQL Lectures

• ORDER (OrderNo, OrderDate, OrdRep, AcctNo,
  OrdAmt, DelvDate, PaidFlg)
• FK OrdRep references SALESREP
• FK AcctNo references ACCOUNT
• Note OrdRep may be NULL

6
Sample Database for SQL Lectures

• ORDERLINE (OrderNo, ProdNo, OrderQty)
• FK OrderNo references ORDER
• FK ProdNo references PRODUCT

7
Relational Model Diagram
ACCOUNT
SALESREP
ORDER
PRODUCT
ORDERLINE
8
Sample Data
9
Retrieval in a Relational Database

• Query is non-procedural
• Specifies WHAT we want (properties of answer,
  i.e. student with GPA gt 4.0)
• But not HOW to obtain it
• Result of a query looks like a table
• Referred to as a Virtual Table
• Can be multiple rows, multiple columns
• Can be single row and/or single column

10
Retrieval in a Relational Database

• Result based on data values that occur in rows
• Not on physical placement of data within rows
• Not on physical placement or ordering of rows
• Selection Criteria can be based on ANY column
• Not just special key fields

11
Outline of a Simple SELECT Statement

• SELECT column-list
• FROM table-list
• WHERE criterion
• ORDER BY column-list ASC/DESC
• The column-list included in the SELECT clause
  must include column names in a comma-delimited
  structure.

12
Outline of a Simple SELECT Statement (cont.)

• Every table which is the originating source of
  the columns listed in the SELECT clause must
  appear in the FROM clauses listing of tables.
• Every table which is the originating source of
  the columns listed in the WHERE clause must
  appear in the FROM clauses listing of tables.

13
Retrieval in SQL

• Listing all of the ORDER tables data
• SELECT FROM ORDER
• Output from SELECT stmt

14
Retrieval in SQL

• Show a few columns from the ORDER table
• SELECT ORDERNO, ACCTNO, ORDAMT
• FROM ORDER
• Output from statement

15
Retrieval in SQL

• Can show columns in different left-to-right
  order
• SELECT ACCTNO, ORDREP, ORDERNO FROM ORDER
• Output from statement

16
WHERE clause Numeric tests

• We can choose selected rows to be displayed,
  using a criterion based on the value of one of
  the data columns in the table
• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE gt 1200
• Output from statement

17
WHERE clause Numeric tests

• .. WHERE PRICE lt 500.00
• Output from execution of SELECT statement

18
WHERE clause Numeric tests

• .. WHERE PRICE lt 500.00
• Output from execution of SELECT statement

19
Ranges using BETWEEN

• Values in a range are more easily, and more
  efficiently done using BETWEEN
• Select ProdNo, Descrip, Price
• From PRODUCT
• Where Price BETWEEN 400 and 1000

20
Ranges using BETWEEN

• The BETWEEN clause is inclusive of the end
  values, 400 and 1000,
• Therefore they are included in the output listing
  on the previous slide

21
Ranges using BETWEEN

• Now, compare that result with the following
  statement and its result
• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICEgt 400 AND PRICE lt 1000
• What is the difference in the 2 results?

22
WHERE clause Character tests

• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE VENDCODE C2

23
WHERE clause Character tests

• SELECT LNAME, REP_PHONE
• FROM SALESREP
• WHERE LNAME gt RAMAKRISHMAN

24
WHERE clause Character tests

• SELECT LNAME, REP_PHONE
• FROM SALESREP
• WHERE LNAME BETWEEN R AND W

25
Compound Conditions AND, OR

• We can combine multiple conditions
• SELECT DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE gt 500
• AND VENDCODE A3
• NOTE the test column VENDCODE need not be in
  the SELECT clause

26
Compound Conditions AND, OR

• Multiple conditions can involve different
  columns, as in the previous example, or even the
  same column, as in this example.
• SELECT DESCRIP, PRICE, VENDCODE
• FROM PRODUCT
• WHERE VENDCODE A3
• OR VENDCODE C2
• NOTE Use parenthesis () when mixing AND / OR
  conditions

27
Negative Comparisons

• SELECT DESCRIP, PRICE, VENDCODE
• FROM PRODUCT
• WHERE VENDCODE A3

28
Negative Comparisons

• Valid alternatives for
• WHERE VENDCODE ltgt A3
• WHERE NOT VENDCODE A3
• INVALID forms or alternatives
• WHERE VENDCODE NOT A3
• WHERE VENDCODE A3

29
The IN Comparison

• The IN comparison helps when we are seeking any
  one of several values
• Find products from either vendor A3 or C2
• SELECT DESCRIP, PRICE, VENDCODE
• FROM PRODUCT
• WHERE VENDCODE IN (A3, C2)

30
The IN Comparison

• Output from the previous SELECT stmt

31
The IN Comparison

• Find products from any vendor other than A3 and
  C2
• SELECT DESCRIP, PRICE, VENDCODE
• FROM PRODUCT
• WHERE VENDCODE NOT IN (A3, C2)

32
The LIKE Comparison

• Find sales reps whose last name start with an F
• SELECT LNAME
• FROM SALESREP
• WHERE LNAME LIKE F

33
The LIKE Comparison

• Find products whose description contains the word
  LASER
• SELECT PRODNO, DESCRIP
• FROM PRODUCT
• WHERE DESCRIP LIKE LASER

34
The LIKE Comparison

• represents any character string
• _ (underscore) represents a single character
• LIKE _A would search for descriptions that
  have an A as
• the 2nd letter

35
ORDER BY

• The ORDER BY clause allows us to control the
  sequencing of the rows in the resulting display
  based on the value of one of the data columns
• SELECT ORDERNO, ORDERAMT, ACCTNO
• FROM ORDER
• ORDER BY ORDERAMT

36
ORDER BY

• We can also specify the sequencing column by its
  relative position within the SELECT clause
• ORDER BY 2
• We can cause the rows to be sequenced from
  highest value to lowest (default is ASCENDING)
• ORDER BY 2 DESC

37
ORDER BY

• The ORDER BY clause comes last, after all WHERE
  clauses and any other clauses.
• SELECT ORDERNO, ORDAMT, ACCTNO
• FROM ORDER
• WHERE ORDAMT gt 2400
• ORDER BY ORDAMT DESC

38
Ordering on Multiple Columns

• We can specify a second ordering field, to be
  applied to ties on the first ordering field
• SELECT ORDERNO, ORDAMT, ACCTNO
• FROM ORDER
• ORDER BY ACCTNO, ORDAMT DESC

39
Ordering on Multiple Columns

• DESC only applies to the immediately preceding
  column.
• Multiple order by fields can also be specified
  using column numbers
• ORDER BY 3, 2 DESC
• The field numbers are the ordinal position of the
  fields in the SELECT statement.

40
DISTINCT

• If we select all sales rep IDs from the ORDER
  table, we get repetitions
• SELECT ORDREP
• FROM ORDER

41
DISTINCT

• If we use DISTINCT, result rows that are exact
  matches are not repeated
• SELECT DISTINCT ORDREP
• FROM ORDER

42
DISTINCT

• With more than 1 column selected, DISTINCT
  suppresses only exact duplications of the entire
  row
• DISTINCT applies to entire rows, not single
  columns
• SELECT DISTINCT ORDREP, ACCTNO
• FROM ORDER

43
Arithmetic Expressions

• Show the result of calculations
• SELECT PRODNO, PRICE 1.05
• FROM PRODUCT

44
Arithmetic Expressions

• SELECT PRODNO, PRICE 1.04 price1,
• PRICE 1.06 PRICE2, PRICE 1.08 PRICE3
• FROM PRODUCT

45
Arithmetic Expressions

• To give a heading label to the column resulting
  from a calculation, leave a blank after the
  expression and give the column label, followed by
  the comma delimiting the items
• e.g. PRICE 1.04 PRICE1,

46
Arithmetic Expressions

• These column labels can be used in ORDER BY
  clauses, but not in WHERE clauses
• Must use the expression in a WHERE clause
• For readability, you may also use the word as
  just before the label.
• SELECT PRODNO, PRICE 1.04 AS PRICE1,
• PRICE 1.06 AS PRICE2,
• PRICE 1.08 AS PRICE3
• FROM PRODUCT

47
Other Content for SELECT

• Can include a constant (either character or
  numeric) in the list of items selected
• For example, can introduce a column with a fixed
  character string in each result row as a label
  for a calculated result
• SELECT PRODNO, INVENTORY VALUE IS , PRICE
  QTYHAND
• FROM PRODUCT

48
Concatenation

• SALESREP
• We can concatenate character strings into a
  single result column
• SELECT LNAME , FNAME as SALESREP_NAME
• FROM SALESREP

49
Concatenation

• If the first field is variable length, there are
  no extra trailing blanks
• SELECT FNAME LNAME as SALESREP_NAME
• FROM SALESREP
• NOTE the concatenation symbol is 2 characters,
  each a single vertical line

50
Scalar Functions

• SUBSTR is a scalar function that can be used in
  queries
• SELECT PRODUCT, SUBSTR(DESCRIP, 1, 8) SHORTNAME
• FROM PRODUCT
• Syntax SUBSTR (column, start position, length)

51
Working with Dates

• Date constants need to be expressed with a
  4-digit year.
• You can use either a hyphen or a / to separate
  the parts
• With the hyphen the format is
• yyyy-mm-dd
• With the back slash, / the format is
• mm/dd/yyyy

52
Working with Dates

• SELECT ORDERNO, ORDERDATE
• FROM ORDER
• WHERE ORDERDATE gt 1993-12-25

53
Working with Dates

• SELECT ORDERNO, ORDERDATE
• FROM ORDER
• WHERE ORDERDATE lt 12/15/1993

54
Date Functions

• MONTH, DAY and YEAR functions can be used to
  extract the subparts of a date field
• SELECT ORDERNO,
• MONTH(ORDERDATE) as MONTH, DAY(ORDERDATE) as
  DAY, YEAR(ORDERDATE) as YEAR
• FROM ORDER

55
Date Functions

• Output from the code on the previous slide

56
Date Formats

• We can convert a date into several character
  formats.
• Such a result can then be treated as a character
  field for further manipulations.
• SELECT ORDERNO, DELVDATE, CHAR(DELVDATE, ISO)
  DASHED,
• CHAR(DELVDATE, USA) SLASHED
• FROM ORDER

57
Date Formats

• Output from previous coded statement

58
Date Arithmetic

• Subtracting two date fields directly yields a
  date duration value which has the format
  yyyymmdd
• where
• yyyy is the year
• mm is the month
• dd is the day of the month
• 216 means 2 months and 16 days
• not 216 days

59
Date Arithmetic

• Suppose we wanted to know how long each order
  took to deliver from the date it was ordered?
• The following query (on the next slide) might
  give misleading answers, if any were over a month

60
Date Arithmetic

• SELECT ORDERNO, ORDERDATE, DELVDATE, DELVDATE
  ORDERDATE
• FROM ORDER
• WHERE DELVDATE IS NOT NULL

61
Finding Elapsed Days

• The DAYS function converts dates into a number
  representing the number of days since a fixed
  base date.
• Subtracting 2 such conversions yields the actual
  difference in number of days between two dates
• Code on next slide.

62
Finding Elapsed Days

• SELECT ORDERNO, ORDERDATE, DELVDATE,
  DAYS(DELVDATE) DAYS(ORDERDATE)
• FROM ORDER
• WHERE DELVDATE IS NOT NULL

63
Finding Elapsed Days

• For all orders which have not been delivered,
  how many days has it been since they were
  ordered?
• CURRENT DATE is a system variable that has
  todays date and can be used in queries.
• SELECT ORDERNO, ORDERDATE,
• DAYS (CURRENT DATE) DAYS(ORDERDATE)
• FROM ORDER

64
Labeled Durations

• We can perform adjustment calculations with
  dates, using the DAY, MONTH and YEAR labeled
  durations.
• SELECT ORDERNO, ORDERDATE, ORDERDATE 15 DAYS,
• ORDERDATE 2 MONTHS 10 DAYS.
• ORDERDATE 1 YEAR
• FROM ORDER

65
Database Terminology

• Relational database
• Structures data in tables
• columns, attributes
• rows, tuples
• Keys
• primary keys
• composite key
• surrogate key
• foreign keys
• candidate keys

66
More Database Terminology

• Relationships
• Null
• Data types
• Text
• Memo
• Number
• Currency
• Date/Time
• AutoNumber
• Domain values

67
Database Design Guidelines

• Group related fields together in a single table
• i.e. fields about a student in a student table
• Avoid duplication of data
• i.e. if a student has taken several courses,
  combining student name and phone number with each
  course taken would duplicate that students name
  and phone number. To avoid the duplication
  create two tables one for the student name and
  phone number and one for the course taken
  information.

68
Database Design Guidelines

• Avoid tables that will have many null values
• i.e. a table that would include military service
  information with the student name and phone
  number. Normally better to put the students
  military service information in a separate table
  since most students would not have any military
  service information