SQL I

1
SQL - I

• Create Tables
• Basic Select
• Select with Join
• Other Select Options

2
What is SQL?

• Structured Query Language
• Language for database definition, manipulation,
  and control
• International standard
• Standalone and embedded usage
• Intergalactic database speak

3
CREATE Syntax

• CREATE TABLE table_name (
• Col_name data-type ltoption_argsgt,
• Repeat above for each column
• ltoptional table constraintsgt
• )
• See Figure, page 60 of text.

4
MySQL Create Table Example

• CREATE TABLE Student (
• StuID Char(9) NOT NULL,
• PawPrint VarChar(20) NOT NULL,
• LName VarChar(20),
• FName VarChar(20),
• Email VarChar(20) NOT NULL
• Constraint PK_Student
• Primary Key (StuID) )

5
Converting ERD to Tables

• Entity Type Rule Each entity type (except
  subtypes) becomes a table and inherits PK and
  attributes from entity
• 1-M Relationship Rule Each 1-M relationship
  becomes a foreign key in the table near the
  Crows Foot symbol (child entity type).
• M-N Relationship Rule Each M-N relationship
  becomes a separate table. The PK is the combined
  key of the entities participating in the M-N
  relationship
• Identification Dependency Rule Each identifying
  relationship adds a component to the PK of the
  weak entity.

6
Converting ERD to Tables

• Optional 1-M Relationship Rule Each 1-M
  relationship with min cardinality of 0 on the
  parent side becomes a new table. The table has
  FKs from participating entities with the child
  entity PK becoming the PK.
• Generalization Hierarchy Rule Each entity in
  hierarchy becomes a table. Subtypes inherit PK
  from parent. Use CASCADE option for deletions of
  referenced rows.
• 1-1 Relationship Rule Each 1-1 relationship is
  converted into two foreign keys. If the
  relationship is optional with respect to one
  side, that FK may be dropped.

7
From ERD to SQL

• Create Table Pet (
• PID Number NOT NULL Auto_Increment,
• Name VarChar(50) NOT NULL,
• Breed VarChar(50),
• Gender ENUM (M,F),
• Temper ENUM
• (Aggressive,Timid,Semi-Aggressive),
• OID Number,
• Constraint PK_Pet Primary Key (PID),
• Constraint FK_PetToOwner Foreign Key (OID)
  References Owner(OID) )

8
SQL Statements
9
SELECT Statement Overview

• SELECT ltlist of column expressionsgt
• FROM ltlist of tables and join operationsgt
• WHERE ltlist of logical expressions for rowsgt
• GROUP BY ltlist of grouping columnsgt
• HAVING ltlist of logical expressions for groupsgt
• ORDER BY ltlist of sorting specificationsgt
• Expression combination of columns, constants,
  operators, and functions

10
Complex Select Example from Pet Table

• Select P.Breed, Count(P.PID)
• From Pet P
• Where Gender M AND TemperTimid
• Group By P.Breed
• Having Count(P.PID) gt 5
• Order By P.Breed DESC

11
University Database
12
First SELECT Examples

• SELECT
• FROM Faculty
• SELECT
• FROM Faculty
• WHERE FacSSN '543210987'
• SELECT FacFirstName, FacLastName, FacSalary
• FROM Faculty
• SELECT FacFirstName, FacLastName, FacSalary
• FROM Faculty
• WHERE FacSalary gt 65000
• AND FacRank 'PROF'

13
Using Expressions

• SELECT FacFirstName, FacLastName, FacCity,
• FacSalary1.1 AS IncreasedSalary, FacHireDate
• FROM Faculty
• WHERE year(FacHireDate) gt 1991
• SELECT FacFirstName, FacLastName, FacCity,
• FacSalary1.1 AS IncreasedSalary, FacHireDate
• FROM Faculty
• WHERE
• to_number(to_char(FacHireDate, 'YYYY')) gt 1991

14
Inexact Matching

• Match against a pattern
• LIKE operator
• Use meta characters to specify patterns
• Wildcard ( or )
• Any single character (? or _)
• Varies by database

• SELECT
• FROM Offering
• WHERE
• CourseNo LIKE 'IS'
• SELECT
• FROM Offering
• WHERE
• CourseNo LIKE 'IS'

15
Using Dates

• Dates are numbers
• Date constants and functions are not standard
• Example 7 (Access)
• SELECT FacFirstName, FacLastName, FacHireDate
• FROM Faculty
• WHERE FacHireDate BETWEEN 1/1/1994
• AND 12/31/1995
• Example 7 (Oracle)
• SELECT FacFirstName, FacLastName, FacHireDate
• FROM Faculty
• WHERE FacHireDate BETWEEN '1-Jan-1994'
• AND '31-Dec-1995'

16
Other Single Table Examples
Example 8 Testing for null values SELECT
OfferNo, CourseNo FROM Offering WHERE
FacSSN IS NULL AND OffTerm 'SUMMER' AND
OffYear 2003 Example 9 Mixing AND and OR
SELECT OfferNo, CourseNo, FacSSN FROM Offering
WHERE (OffTerm 'FALL' AND OffYear 2002)
OR (OffTerm 'WINTER' AND OffYear 2003)
17
Join Operator

• Most databases have many tables
• Combine tables using the join operator
• Specify matching condition
• Can be any comparison but usually
• PK FK most common join condition
• Relationship diagram useful when combining tables

18
Join Example
19
Cross Product Style

• List tables in the FROM clause
• List join conditions in the WHERE clause
• SELECT OfferNo, CourseNo, FacFirstName,
• FacLastName
• FROM Offering, Faculty
• WHERE OffTerm 'FALL' AND OffYear 2002
• AND FacRank 'ASST' AND CourseNo LIKE 'IS'
• AND Faculty.FacSSN Offering.FacSSN

20
Join Operator Style

• Use INNER JOIN and ON keywords
• FROM clause contains join operations
• SELECT OfferNo, CourseNo, FacFirstName,
• FacLastName
• FROM Offering INNER JOIN Faculty
• ON Faculty.FacSSN Offering.FacSSN
• WHERE OffTerm 'FALL' AND OffYear 2002
• AND FacRank 'ASST' AND CourseNo LIKE 'IS'

21
Name Qualification

• Ambiguous column reference
• More than one table in the query contains a
  column referenced in the query
• Ambiguity determined by the query not the
  database
• Use column name alone if query is not ambiguous
• Qualify with table name if query is ambiguous

22
Summarizing Tables

• Row summaries important for decision-making tasks
• Row summary
• Result contains statistical (aggregate) functions
• Conditions involve statistical functions
• SQL keywords
• Aggregate functions in the output list
• GROUP BY summary columns
• HAVING summary conditions

23
GROUP BY Examples

• SELECT FacRank, AVG(FacSalary) AS AvgSalary
• FROM Faculty
• GROUP BY FacRank
• SELECT StdMajor, AVG(StdGPA) AS AvgGpa
• FROM Student
• WHERE StdClass IN ('JR', 'SR')
• GROUP BY StdMajor
• HAVING AVG(StdGPA) gt 3.1

24
SQL Summarization Rules

• Columns in SELECT and GROUP BY
• SELECT non aggregate and aggregate columns
• GROUP BY list all non aggregate columns
• WHERE versus HAVING
• Row conditions in WHERE
• Group conditions in HAVING

25
Summarization and Joins

• Powerful combination
• List join conditions in the WHERE clause
• List the number of students enrolled in each fall
  2003
• offering.
• SELECT Offering.OfferNo,
• COUNT() AS NumStudents
• FROM Enrollment, Offering
• WHERE Offering.OfferNo Enrollment.OfferNo
• AND OffYear 2003
• GROUP BY Offering.OfferNo