SQL Structured Query Language - PowerPoint PPT Presentation

1 / 68
About This Presentation
Title:

SQL Structured Query Language

Description:

Sample Database for SQL Lectures ... the 2nd letter. ORDER BY ... Can include a constant (either character or numeric) in the list of items selected ... – PowerPoint PPT presentation

Number of Views:43
Avg rating:3.0/5.0
Slides: 69
Provided by: barbara204
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com