Structured Query Language

1
Structured Query Language

• Chris Nelson
• CS 157B
• Spring 2008

2
Overview

• Language
• Selections
• Joins
• Insertions
• Updates and Deletes
• Aggregate Functions
• Grouping
• SQL Training Resources

3
SQL Language

• SQL-1 Standardized by ANSI in 1986
• Includes Data Definition Lang. (DDL) statements
  for Schema Modification
• Includes Data Manipulation Lang. (DML) statements
  for Data Manipulation
• SQL-2 Standardized in 1992
• SQL-3 standardization still ongoing

4
Selections

• Selects
• select ltelementgt from lttablegt where ltcriteriagt
• select is Relational algebras projection
  operation.
• where is relational algebras selection
  operation.
• Select does not maintain relational algebras
  uniqueness property. Hence the distinct
  keyword.

5
Selection Criteria

• Numbers/Identities
• Can be indicated with an equal sign
• Strings
• Require single quotes
• Can be wildcarded using
• (multichar)
• _ (singlechar)
• Depending on database implementation, may or may
  not have to worry about case.
• Oracle is case sensitive, SQL Server, Postgres,
  etc. are not

6
Joins

• Inner Join
• Relational Algebras natural join. Joins primary
  keys on foreign keys. Matching elements will form
  a tuple in result list.
• Left/Right Joins
• Allows additional null/non-matching columns to be
  included in result set. Side indicated in join
  will be the mandatory one.
• Outer Join
• Union of left and right joins. Results will
  include inner join matches, as well as right and
  left mismatches.

7
Joins (Continued)

• The previous joins follow the format
• Select ltelemsgt from tableA ltjoinTypegt tableB on
  ltcriteriagtieselect from employee left join
  department on (employee.dept dept.id)
• Also, manual joins can be used
• Select ltelemsgt from tableA, tableB where
  ltcriteriagtieselect from employee, department
  where employee.dept dept.id // note, not
  necessarily same result as above

8
Table Aliasing

• Used in manual joins.
• Useful for joining multiple tables, saving typing
  in where criteria. Follows format
• select lttableAliasgt.property from lttableNamegt
  lttableAliasgt where lttableAliasgt.property foo
• select p.name, r.balanceOwed from person p,
  record r where r.patientId p.id

9
Insertions

• Inserts
• insert into tableName (attributes) values
  (properties)
• Properties must follow same order as attributes
• Can also chain with a select statement to handle
  a sort of copy operationinsert into tableName
  (select)

10
Updates and Deletes

• Updates
• Update lttableNamegt set ltattribgt ltvaluegt where
  ltcriteriagt
• Deletes
• Delete from lttableNamegt where ltcriteriagt
• Leaving out where criteria will delete/Update
  everything in the table

11
Aggregate Functions

• Count returns count of matching tuples
• Average returns average of specified attribue
• Max returns max of specified attribute
• Min returns min of specified attribute
• Sum returns sum of all rows in table attribute
• Follow format
• select fn(ltelemgt) from lttablegt where ltcriteriagt

12
Grouping

• Element grouping
• Group by - groups common elements into one tuple
• having - used to indicate criteria for grouping
• Query Unions
• (select from xyz) union (select from abc)

13
Schema Creation, Deletion

• Primary Keywords
• Createcreate table lttableNamegt
• attribName ltattribTypegtusual attribute
  types include char, varChar(), int.
• Drop
• Cascade delete all connected tables (ie
  matching key pairs)
• Restrict Delete only empty tables
• These have already been covered in-depth in
  previous presentations.

14
Common Errors

• Using double-quotes instead of single for string
  literals
• Forgetting escape characters for words featuring
  apostrophesie oreilly will terminate the
  string after oshould be o\reilly

15
Training

• Slides are nice, but practice is better than
  anything.
• Most databases have sample data sets
  available/installed (ie SQL Servers
  Northwind database. Download herehttp//www.mi
  crosoft.com/downloads/details.aspx?FamilyID066162
  12-0356-46A0-8DA2-EEBC53A68034displaylangen)
• SQL Zoo http//sqlzoo.net/ Has a number of tests
  and tutorials (theyre actually kind of fun)

16
References and Recommended Links

• Principles of Database Systems with Internet and
  Java Applications, by Greg Riccardi
• MySQL Intro http//dev.mysql.com/tech-resources/ar
  ticles/mysql_intro.html
• W3C School SQL intro
• http//www.w3schools.com/sql/sql_intro.asp