Transcript No Slide Title
COIS20026 Database Development & Management
Week 5 – SQL (Part – I) Prepared by: Angelika Schlotzer Updated by: Satish Balmuri Updated by: Tony Dobele
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
Objectives (cont’d)
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.
3
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
4
What is SQL (cont’d)
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)
5
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
6
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
7
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 8
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
9
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
10
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
11
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)
12
Table Creation
Figure 7-5: General syntax for CREATE TABLE
Steps in table creation:
1.
Identify data types for attributes 2.
3.
Identify columns that can and cannot be null Identify columns that must be unique (candidate keys) 4.
5.
6.
7.
Identify primary key foreign key mates Determine default values Identify constraints on columns (domain specifications) Create the table and associated indexes 13
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
14
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)
15
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)
16
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
17
Figure 7-3: Sample Pine Valley Furniture data
customers orders order lines products
18
Figure 7-6: SQL database definition commands for Pine Valley Furniture 19
Figure 7-6: SQL database definition commands for Pine Valley Furniture Defining attributes and their data types 20
Figure 7-6: SQL database definition commands for Pine Valley Furniture Non-nullable specifications Note: primary keys should not be null 21
Figure 7-6: SQL database definition commands for Pine Valley Furniture Identifying primary keys This is a composite primary key 22
Figure 7-6: SQL database definition commands for Pine Valley Furniture Identifying foreign keys and establishing relationships 23
Figure 7-6: SQL database definition commands for Pine Valley Furniture Default values and domain constraints 24
Figure 7-6: SQL database definition commands for Pine Valley Furniture Overall table definitions 25
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
26
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);
27
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 ;
28
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 ;
29
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
30
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’) ;
31
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’);
32
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;
33
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’; 34
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’;
35
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;
36
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
37
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
38
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 39
SQL statement processing order (adapted from van der Lans, p.100) 40
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
41
Select Examples
SELECT NAME, DEPARTMENT FROM EMPLOYEE_T; Only the name and department values from the employee table will be displayed
42
SELECT Example
Find products with standard price less than $275 SELECT FROM PRODUCT_NAME, STANDARD_PRICE PRODUCT_V WHERE STANDARD_PRICE < 275 Table 7-3: Comparison Operators in SQL 43
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 >= #01/01/85#
44
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
45
SELECT Example with ALIAS
Alias is an alternative column or table name SELECT CUST .CUSTOMER AS NAME , CUST.CUSTOMER_ADDRESS FROM CUSTOMER_V AS CUST WHERE NAME = ‘Home Furnishings’; 46
Select with Functions
How many employees do we currently employ?
SELECT COUNT (*) FROM EMPLOYEE_T;
47
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 can’t have single-valued columns included in the SELECT clause
48
Wildcards
Wildcards can be used when an exact match is not possible. For example, you may know that a person’s 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
49
Wildcards (cont’d)
% - 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
50
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
51
Boolean Operator Example
SELECT ITEM_NO, ON_HAND FROM INVENTORY WHERE ON_HAND > 10 AND DESCRIPTION LIKE “%bolts” OR COST < 1.00; Note: with multiple conditions separated by OR/AND, it is recommended to use braces ()
52
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.
53
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”);
54
BETWEEN
SELECT ITEM_NO, ON_HAND FROM INVENTORY WHERE ITEM_COST BETWEEN 10 AND 25;
55
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;
56
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
57
GROUP BY Example
List states & their individual count of Suppliers SELECT STATE, COUNT (STATE) FROM SUPPLIER GROUP BY STATE;
58
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;
59
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;
60
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) < 100;
61
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)
62
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?
63
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?
64
Select Activity (cont’d)
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).
65
Select Activity (cont’d)
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.
66
Select Activity (cont’d)
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).
67