COIS20026 Database Development

1
COIS20026 Database Development Management
Week 5 SQL (Part I) Prepared by Angelika
Schlotzer Updated by Satish Balmuri Updated by
Tony Dobele
2
This week SQL

• Objectives
• explain the role of SQL in relational DBMS
• identify explain the distinction between DDL,
  DML DCL
• use the SQL commands CREATE TABLE, DROP TABLE,
  ALTER TABLE, CREATE INDEX
• be able to use the SQL commands INSERT, UPDATE,
  DELETE

3
Objectives (contd)

• Be able to discuss the role of indexes and use
  the CREATE INDEX/DROP INDEX commands (including
  being able to create a primary key).
• construct correct single table SQL queries with
  the SELECT command using, as appropriate, its
  various clauses options

Note Unless otherwise mentioned all the
references of this lecture material are from the
prescribed course text book or images from
publishers.
4
What is SQL?

• SQL - Structured Query Language
• high level declarative language used for creating
  querying relational databases
• declarative language that focuses on the what
  not the how
• included as a subset in many fourth-generation
  languages

5
What is SQL (contd)

• has basically become the de-facto standard for
  relational database querying
• first ANSI SQL standards published in 1986 and
  updated in 1989, 1992 (SQL-92) and 1999 (SQL-99)

6
RDBMS

• The Relational Database Management System
  implements the relational model
• In a SQL enabled RDBMS, users or applications
  deal with the RDBMS through SQL statements ie
  the SQL acts as an agent between the two
• users can create tables, retrieve data, etc

7
Figure 7-1 A simplified schematic of a typical
SQL environment, as described by the SQL-92
standard
Note that the catalogue is itself stored as a
table
8
SQL Environment

• Catalog
• a set of schemas that constitute the description
  of a database
• Schema
• The structure that contains descriptions of
  objects created by a user (base tables, views,
  constraints)
• Data Definition Language (DDL)
• Commands that define a database, including
  creating, altering, and dropping tables and
  establishing constraints
• Data Manipulation Language (DML)
• Commands that maintain and query a database
• Data Control Language (DCL)
• Commands that control a database, including
  administering privileges and committing data

9
Data Definition Language (DDL)

• The DDL component of SQL allows us to create,
  alter and drop tables and indexes, and implement
  data integrity and domain constraints
• DDL commands currently available (see p 295 of
  the text)
• Create Table Drop Table Alter Table
• Create Index Drop Index Create View
• Drop View Create Schema, Drop Schema

10
Data Manipulation Language (DML)

• The DML component of SQL allows users
  applications to query, update, delete existing
  records in tables and insert new records
• eg Select, Update, Insert, Delete

11
Data Control Language (DCL)

• The DCL component is used to implement controls
  on a database, including administering user
  privileges and ensuring that transactions are
  completed before committing changes to the
  database

12
Creating a Table

• The CREATE TABLE command allows us to create a
  new table in an existing database
• The general format for this command is shown in
  figure 7-5 on page 299 of the text
• You will need to do some preparation before
  creating a table eg identify which columns can
  contain NULL values, etc (see steps in text on pp
  299-301)

13
Table Creation

• Steps in table creation
• Identify data types for attributes
• Identify columns that can and cannot be null
• Identify columns that must be unique (candidate
  keys)
• Identify primary key-foreign key mates
• Determine default values
• Identify constraints on columns (domain
  specifications)
• Create the table and associated indexes

Figure 7-5 General syntax for CREATE TABLE
14
Create Table Command

• Assume that you have the following normalised
  relation for which you wish to create a table -
• EMPLOYEE(EmpID, Name, DateOfBirth, Department)
• We know that EmpID is the primary key
• We will assume the following domains for the
  columns

15
Create Table Command

• EmpID - consists of an alphabetic character
  followed by 3 digits (eg D912)
• Name - consists of a maximum of 40 alphabetic
  characters (eg Alan Jones)
• DateOfBirth - would be a valid date (eg
  27/02/1965)
• Department - consists of maximum of 25 alphabetic
  characters (eg Production, Sales, Advertising)

16
Create Table Command

• We would use the following command to create this
  table -
• CREATE TABLE EMPLOYEE_T
• (EMP_ID VARCHAR(4) NOT NULL,
• NAME VARCHAR(40) NOT NULL,
• DATE_OF_BIRTH DATE,
• DEPARTMENT VARCHAR(25),
• CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMP_ID))
• (figure 7-6 of the text has additional example
  table creation declarations)

17
Create Table Command (again)

• Note how the text (fig 7-6) uses CONSTRAINT and
  REFERENCES to identify foreign keys in a table
• REFERENCES ensures that a value entered for a
  foreign key in one table must exist as a primary
  key value in the referenced table
• does not stop the primary key value from being
  altered
• the ON UPDATE option (p 302) allows us to
  determine what should happen when a primary key
  value (that appears as a foreign key in another
  table) is changed

18
Figure 7-3 Sample Pine Valley Furniture data
customers
orders
order lines
products
19
Figure 7-6 SQL database definition commands for
Pine Valley Furniture
20
Figure 7-6 SQL database definition commands for
Pine Valley Furniture
Defining attributes and their data types
21
Figure 7-6 SQL database definition commands for
Pine Valley Furniture
Non-nullable specifications
Note primary keys should not be null
22
Figure 7-6 SQL database definition commands for
Pine Valley Furniture
Identifying primary keys
This is a composite primary key
23
Figure 7-6 SQL database definition commands for
Pine Valley Furniture
Identifying foreign keys and establishing
relationships
24
Figure 7-6 SQL database definition commands for
Pine Valley Furniture
Default values and domain constraints
25
Figure 7-6 SQL database definition commands for
Pine Valley Furniture
Overall table definitions
26
Microsoft Access Tables

• Creating tables in Microsoft Access is somewhat
  different -
• a table definition window is opened up for you in
  which you can give the same information, but in a
  slightly more user-friendly way
• data integrity controls can be identified when
  the relationships between the tables are
  established

27
Alter Table Command

• Allows us to make changes to an existing table -
• add and drop columns
• change column names, data type, constraint, etc
• eg.
• ALTER TABLE EMPLOYEE_T
• ADD (COMMENCE_DATE DATE)

28
Drop Table Command

• If a table is dropped (deleted) all indexes,
  views, privileges, etc defined for the table will
  also be dropped -
• use carefully as command cannot be undone
• The command to drop the Employee table would be
• DROP TABLE EMPLOYEE_T

29
Create Index Command

• Indexes are created to improve query performance
• For instance, we might create an index for our
  Employee table so that queries on names are
  handled more quickly
• CREATE INDEX NAME_IDX ON EMPLOYEE_T (NAME)
• The command to drop this index would be
• DROP INDEX NAME_IDX

30
Create Index Command

• You should consider carefully before creating
  numerous indexes for your tables -
• each index requires extra storage space
• the applicable index must be updated when data
  values for the indexed columns change
• in the end performance may actually be reduced

31
DML-Inserting Data into a Table

• Data can be inserted interactively or in batch
  mode - we will focus on the interactive mode
• If you will be inserting values for every column
  in the table then an example command for the
  Employee table might be
• INSERT INTO EMPLOYEE_T VALUES
• (D325, Alison Hart, 19/04/2000, Sales)

32
Inserting Data into a Table

• If some attributes (column values) will not be
  inserted, then the following format of the
  command would be used
• INSERT INTO EMPLOYEE_T (EMP_ID, NAME)
• VALUES (F123,Henry Chang)

33
Inserting Data into a Table

• You can also add rows to a table by using a
  subset of another table using both the INSERT and
  SELECT commands eg
• INSERT INTO YEAR2NET_T
• SELECT ID, Name, Class
• FROM ENROLLED
• WHERE YEAR_LEVEL 2

34
Insert Statement More examples

• Inserting into a table
• INSERT INTO CUSTOMER_T VALUES (001,
  CONTEMPORARY Casuals, 1355 S. Himes Blvd.,
  Gainesville, FL, 32601)
• Inserting a record that has some null attributes
  requires identifying the fields that actually get
  data
• INSERT INTO PRODUCT_T (PRODUCT_ID,
  PRODUCT_DESCRIPTION,PRODUCT_FINISH,
  STANDARD_PRICE, PRODUCT_ON_HAND) VALUES (1, End
  Table, Cherry, 175, 8)
• Inserting from another table
• INSERT INTO CA_CUSTOMER_T SELECT FROM
  CUSTOMER_T WHERE STATE CA

35
DML-Deleting Data from a Table

• You can delete all of the rows in a table by
  using the DELETE command without specifying any
  criteria eg
• DELETE FROM EMPLOYEE_T
• When criteria are added only those rows that meet
  these are removed eg
• DELETE FROM EMPLOYEE_T
• WHERE EMP_ID C434

36
DML-Updating Data in a Table

• Existing data can be updated through the UPDATE
  command eg assume that all employees are to
  receive a 5 pay increase
• the command for this might be
• UPDATE PRODUCT_T
• SET UNIT_PRICE 775
• WHERE PRODUCT_ID 7
• OR
• UPDATE SALARY_T
• SET SALARY_AMOUNT
• SALARY_AMOUNT 1.05

37
DML-Select Statement

• The SELECT statement is the one most commonly
  used by users -
• allows us to retrieve information from 1 or more
  tables in the way in which we need to see that
  information
• The 3 most common clauses are
• SELECT - columns, etc to be displayed
• FROM - identifies table(s)/views to use
• WHERE - conditions to apply

38
Select

• This is the most common statement
• The SELECT and FROM statements are always
  required
• WHERE is only needed if conditions are to be
  applied to the result

39
The SELECT Statement

• Used for queries on single or multiple tables
• Clauses of the SELECT statement
• SELECT
• List the columns (and expressions) that should be
  returned from the query
• FROM
• Indicate the table(s) or view(s) from which data
  will be obtained
• WHERE
• Indicate the conditions under which a row will be
  included in the result
• GROUP BY
• Indicate categorization of results
• HAVING
• Indicate the conditions under which a category
  (group) will be included
• ORDER BY
• Sorts the result according to specified criteria

40
SQL statement processing order (adapted from van
der Lans, p.100)
41
Select Examples

• SELECT
• FROM EMPLOYEE_T
• The above will display all the data in the
  employee table -
• column order will be same as for table

42
Select Examples

• SELECT NAME, DEPARTMENT
• FROM EMPLOYEE_T
• Only the name and department values from the
  employee table will be displayed

43
SELECT Example

• Find products with standard price less than 275
• SELECT PRODUCT_NAME, STANDARD_PRICE
• FROM PRODUCT_V
• WHERE STANDARD_PRICE lt 275

Table 7-3 Comparison Operators in SQL
44
Select Examples

• Display those employees who were employed on or
  after 1 January, 1985
• SELECT EMP_ID, NAME, DEPARTMENT
• FROM EMPLOYEE_T
• WHERE COMMENCE_DATE gt 01/01/85

45
Select with Expressions

• Display the number of items on hand the
  selling price of all inventory items (assumes a
  25 mark up for all items)
• SELECT ITEM_NO, DESCRIPTION, ON_HAND, COST
  1.25 AS SELL PRICE
• FROM INVENTORY

46
SELECT Example with ALIAS

• Alias is an alternative column or table name
• SELECT CUST.CUSTOMER AS NAME, CUST.CUSTOMER_ADDRES
  S
• FROM CUSTOMER_V AS CUST
• WHERE NAME Home Furnishings

47
Select with Functions

• How many employees do we currently employ?
• SELECT COUNT ()
• FROM EMPLOYEE_T

48
Select with Functions

• How many different types of inventory items do we
  currently stock?
• SELECT COUNT (ITEM_NO)
• FROM INVENTORY

Note with aggregate functions you cant have
single-valued columns included in the SELECT
clause
49
Wildcards

• Wildcards can be used when an exact match is not
  possible. For example, you may know that a
  persons name begins with C, but cannot
  remember the rest.
• The LIKE qualifier is often used with wildcards
  (except for the asterisk)
• The asterisk () matches up anything

50
Wildcards (contd)

• - used for any number of characters eg LIKE
  C
• _ - underscore takes the place of exactly one
  character eg LIKE SMITH_
• Note MS Access uses the instead of as
  wildcard

51
Boolean Operators

• AND - joins two or more conditions and will only
  return results if all conditions are true
• OR - joins two or more conditions and will return
  results if any of the conditions is true
• NOT - negates any expression
• Precedence NOT, AND, OR

52
Boolean Operator Example

• SELECT ITEM_NO, ON_HAND
• FROM INVENTORY
• WHERE ON_HAND gt 10
• AND DESCRIPTION LIKE bolts
• OR COST lt 1.00

Note with multiple conditions separated by
OR/AND, it is recommended to use braces ()
53
Distinct Qualifier

• Used to eliminate duplication of column values in
  returned results
• Example
• SELECT DISTINCT ITEM_NO
• FROM ITEM_SALES_T
• Note In MS ACCESS Query Designer DISTINCT is
  not available with COUNT, e.g., COUNT (DISTINCT
  Item No) is not available in MS Access query
  designer.

54
IN and NOT IN

• IN and NOT IN are used to match (or negate
  matches) from a list of value
• List of values can be obtained using a SELECT
  statement
• Example
• SELECT S_ID, SUPPLIER_NAME, PHONE
• FROM SUPPLIER_T
• WHERE CITY IN (Sydney, Melb, Canberra)

55
BETWEEN

• SELECT ITEM_NO, ON_HAND
• FROM INVENTORY
• WHERE ITEM_COST
• BETWEEN 10 AND 25

56
Order By

• ORDER BY clause used to sort one or more columns
  values in resultant set into ascending (ASC) or
  descending (DESC) order
• Example
• SELECT FIRST_NAME, LAST_NAME, CUST_ID, ADDRESS
• FROM CUSTOMER_T
• ORDER BY LAST_NAME ASC

57
Group By and Having

• GROUP BY - Groups rows in an intermediate results
  table where the values in those rows are the same
  for one or more columns
• HAVING - can only be used with the GROUP BY
  clause and is used as a secondary WHERE clause to
  specify additional conditions

58
GROUP BY Example

• List states their individual count of
  Suppliers

• SELECT STATE, COUNT (STATE)
• FROM SUPPLIER
• GROUP BY STATE

59
Select with Group By Functions

• Which inventory item currently has the lowest
  quantity in stock?
• SELECT ITEM_NO, MIN(ON_HAND)
• FROM INVENTORY
• GROUP BY ITEM_NO

60
Select with Group By Functions

• Which item has the greatest quantity of stock on
  hand?
• SELECT ITEM_NO, MAX(ON_HAND)
• FROM INVENTORY
• GROUP BY ITEM_NO

61
GROUP BY with HAVING Example

• Identify states that have few than 100 customers
• SELECT STATE, COUNT (STATE)
• FROM CUSTOMER
• GROUP BY STATE
• HAVING COUNT (STATE) lt 100

62
Activity

• Given the following relations
• CUSTOMER(ID, F_NAME, L_NAME, ADDRESS1, CITY,
  STATE, POSTCODE, TELEPHONE)
• ITEM(NO, DESCRIPTION, ON_HAND, COST)
• ORDER(ORDER_ID, DATE, CUST_ID)
• ORDER_ITEM(ORDER_ID, ITEM_NO, QUANTITY)

63
Create Table Activity

• Create tables with appropriate data types and
  constraints for each of the relations shown on
  the previous slide
• consider
• are there any foreign keys in the relations?
• Do we need to consider referential integrity? Why
  or why not?
• How will you ensure data integrity?
• Are any indexes required?

64
Select Activity

• List all of our customers.
• Which items do we currently have in stock?
• List items with more than 15 items in stock.
• List all of the orders for April, 1999.
• How many customers do we have in each state?

65
Select Activity (contd)

• List the cost, item name and item number for all
  items.
• Identify the first name, last name, customer ID
  and telephone number for all customers in New
  South Wales.
• List all of the items that have BOLT as part of
  their description.
• List all of the states that have more than 25
  customers (include customer numbers in the
  output).

66
Select Activity (contd)

• List the order_ID, date and item count of all
  orders that had more than five items.
• The selling price of an item is the item cost
  plus 28. List the item name, item number and
  selling price for all items.

67
Select Activity (contd)

• List the first name, last name and ID of all
  customers who live in either Queensland or
  Victoria.
• List the item number and description of all items
  where the on_hand amount is greater than 5 and
  less than 25.
• List those customers who live in Sydney (New
  South Wales).