An Introduction to SQL

1
An Introduction to SQL

• Kirk AnneComputing Information Technology
• SUNY Geneseo
• kma_at_geneseo.edu

2
What is a database?
3
Parts of a database

• Attributes (fields)
• An attribute or field is a component of a record
  that describes something about an item.
• Records
• A record is the representation of an individual
  item.
• Table
• A collection of records
• Database
• A collection of tables and rules for accessing
  the tables

4
What is a relational database?

• Originally developed by E.F. Codd in 1970
• Organizes data into tables where each item is a
  row and the attributes of the item are in
  columns.
• Different from flat file databases because you
  can define relationships between items in
  different tables.

5
Parts of a database
Record
Tables
Attribute/Field

• Records become rows
• Attributes/fields become columns
• Rules determine the relationship between the
  tables and tie the data together to form a
  database

6
I need a new database!

• Many people ask for new databases when in fact
  they only need a new table within an existing
  database.
• The data within the tables should be all related
  somehow.
• By owner
• By project

7
Creating a database

• What information are we trying to store?
• How do we describe the information?
• Phone Book/Contact entries
• Name
• Address
• Company
• Phone Number
• URL/Web Page
• Age
• Height (in meters)
• Birthday
• When we added the entry

8
Data Types

• Binary
• Database specific binary objects
• Pictures, digital signatures, etc.
• Boolean
• True/False values
• Character
• Fixed width or variable size
• Numeric
• Integer, Real (floating decimal point), Money
• Temporal
• Time, Date, Timestamp

9
Phone Book/Contact Record

• Name Character
• Address Character
• Company Character
• Phone Number Character
• URL/Web Page Character
• Age Integer
• Height Real (float)
• Birthday Date
• When we added the entry Timestamp

10
Normal Forms

• Summarized from Barry Wises article on Database
  Normalization
• http//www.phpbuilder.com/columns/barry20000731.ph
  p3?page1

11
What are the normal forms?

• E. F. Codd in 1972 wrote a paper on Further
  Normalization of the Data Base Relational Model
• Normal forms reduce the amount of redundancy and
  inconsistent dependency within databases.
• Codd proposed three normal forms and through the
  years two more have been added.

12
The Zero Form

• No rules have been applied
• Where most people start (and stop)
• No room for growth
• Usually wastes space

13
First Normal Form

• Eliminate repeating columns in each table
• Create a separate table for each set of related
  data
• Identify each set of related data with a primary
  key

Benefits Now we can have infinite phone numbers
or company addresses for each contact. Drawback
Now we have to type in everything over and over
again. This leads to inconsistency, redundancy
and wasting space. Thus, the second normal form
14
Second Normal Form

• Create separate tables for sets of values that
  apply to multiple records
• Relate these tables with a foreign key.

15
Third Normal Form

• Eliminate fields that do not depend on the
  primary key.

Is this enough? Codd thought so What about
many to many?
16
Kinds of Relationships

• One to One
• One row of a table matches exactly to another
• One person, one id number, one address
• One to Many
• One row of a table matches many of another
• One person, many phone numbers
• Many to Many
• One row may match many of another or many rows
  match one row of another

17
Fourth Normal Form

• In a many to many relationship, independent
  entities cannot be stored in the same table.

18
Fifth Normal Form

• The very esoteric one that is probably not
  required to get the most out of your database.
• The original table must be reconstructed from
  the tables into which it has been broken down.
• The rule ensures that you have not created any
  extraneous columns and all the tables are only as
  large as they need to be.

19
Why normalize?

• Increases the integrity of the data
• Reduces redundancy
• Improves efficiency
• Although normalization can be hard, it is worth
  it in the long run.

20
What do I need to remember?

• Keep normalization in mind.
• Dont replicate data in a table.
• If you break the rules, know why you are breaking
  the rules and do it for a good reason.

21
All you need to know about SQL in 30 minutes (or
less)
22
Basic SQL Commands

• Creating tables with CREATE
• Adding data with INSERT
• Viewing data with SELECT
• Removing data with DELETE
• Modifying data with UPDATE
• Destroying tables with DROP

23
Creating tables with CREATE

• Generic form
• CREATE TABLE tablename (
• column_name data_type attributes,
• column_name data_type attributes,
• )
• Table and column names cant have spaces or be
  reserved words like TABLE, CREATE, etc.

24
Phone Book/Contact Record

• Name Character
• Address Character
• Company Character
• Phone Number Character
• URL/Web Page Character
• Age Integer
• Height Real (float)
• Birthday Date
• When we added the entry Timestamp

25
Phone Book/Contact Table

• CREATE TABLE contacts (
• Name VARCHAR(40),
• Address VARCHAR(60),
• Company VARCHAR(60),
• Phone VARCHAR(11),
• URL VARCHAR(80),
• Age INT,
• Height FLOAT,
• Birthday DATE,
• WhenEntered TIMESTAMP
• )
• Plan your tables very carefully!
• Once created, they are difficult to change!

26
Phone Book/Contact Table

• CREATE TABLE contacts (
• ContactID INT PRIMARY KEY,
• Name VARCHAR(40),
• Address VARCHAR(60),
• Company VARCHAR(60),
• Phone VARCHAR(11),
• URL VARCHAR(80),
• Age INT,
• Height FLOAT,
• Birthday DATE,
• WhenEntered TIMESTAMP
• )
• If you are going to use the relational nature of
  a database,dont forget you need to have a
  unique way to access records!
• There is a way to make the key automatically
  increment,so you dont have to worry about which
  one is next.

27
Data Types

• Binary
• Database specific binary objects (BLOB)
• Boolean
• True/False values (BOOLEAN)
• Character
• Fixed width (CHAR) or variable size (VARCHAR)
• Numeric
• Integer (INT), Real (FLOAT), Money (MONEY)
• Temporal
• Time (TIME), Date (DATE), Timestamp (TIMESTAMP)

28
Adding data with INSERT

• Generic Form
• INSERT INTO tablename (column_name,)
• VALUES (value,)

29
Inserting a record into contacts

• INSERT INTO contacts (contactid,name,address,compa
  ny,phone,url,age,height,birthday,whenentered)
• VALUES
• (1,Joe,123 Any St.,ABC,
• 800-555-1212,http//abc.com,30,1.9,6/14/197
  2,now())

30
Inserting a partial record

• INSERT INTO contacts (contactid,name,phone)
• VALUES (2,Jane,212-555-1212)

31
Automatic key generation

• CREATE SEQUENCE contactidseq
• Change the ContactID line in the CREATE TABLE
  to
• ContactID INT DEFAULT nextval(contactidseq)
  PRIMARY KEY
• Or when inserting into a table
• INSERT contacts (contactid,name,phone)
• VALUES (nextval(contactidseq),Jack, 716-5
  55-1212)

32
Viewing data with SELECT

• Generic Form
• SELECT column, FROM table, WHERE condition
  GROUP BY group_by_expression HAVING condition
  ORDER BY order_expression
• The most used command
• Probably the most complicated also
• If used improperly, can cause very long waits
  because complex computations

33
A few simple SELECTs

• SELECT FROM contacts
• Display all records in the contacts table
• SELECT contactid,name FROM contacts
• Display only the record number and names
• SELECT DISTINCT url FROM contacts
• Display only one entry for every value of URL.

34
Refining selections with WHERE

• The WHERE subclause allows you to select
  records based on a condition.
• SELECT FROM contacts WHERE age
• Display records from contacts where age
• SELECT FROM contacts WHERE age BETWEEN 18 AND
  35
• Display records where age is 18-35

35
Additional selections

• The LIKE condition
• Allows you to look at strings that are alike
• SELECT FROM contacts WHERE name LIKE J
• Display records where the name starts with J
• SELECT FROM contacts WHERE url LIKE .com
• Display records where url ends in .com

36
Removing data with DELETE

• Generic Form
• DELETE FROM table WHERE condition
• DELETE FROM contacts WHERE age

37
Modifying data with UPDATE

• Generic Form
• UPDATE table SET columnexpression
• WHERE condition
• UPDATE contacts SET companyAOL
• WHERE companyTime Warner

38
Destroying tables with DROP

• Generic Form
• DROP TABLE tablename
• DROP TABLE contacts

39
More about SELECT
40
Normal Forms and SELECT

• Good database design using the normal forms
  requires data to be separated into different
  tables
• SELECT allows us to join the data back together
• We can use views to create virtual tables

41
The Normal Forms

• First Form
• Eliminate replicated data in tables
• Create separate tables for each set of related
  data
• Identify each set of related data with a primary
  key
• Second Form
• Create separate tables for sets of values that
  apply to multiple records
• Relate the tables with a foreign key
• Third Form
• Eliminate fields that do not depend on the
  primary key
• Fourth Form
• In many-to-many relationships, independent
  entities cannot be stored in the same table

42
Joining together tables

• SELECT name,phone,zip FROM people, phonenumbers,
  address WHERE people.addressidaddress.addressid
  AND people.idphonenumbers.id

43
Different types of JOINs

• Inner Join
• Unmatched rows in either table arent printed
• Left Outer Join
• All records from the left side are printed
• Right Outer Join
• All records from the right side are printed
• Full Outer Join
• All records are printed
• Multiple Table Join
• Join records from multiple tables

44
General form of SELECT/JOIN

• SELECT columns,
• FROM left_table
• join_type JOIN right_table ON condition
• SELECT name,phone FROM people
• JOIN phonenumbers ON people.idphonenumbers.id

45
Other versions

• SELECT name,phone FROM people
• LEFT JOIN phonenumbers ON people.idphonenumbers
  .id
• SELECT name,phone FROM people
• RIGHT JOIN phonenumbers ON people.idphonenumber
  s.id
• SELECT name,phone FROM people
• FULL JOIN phonenumbers ON people.idphonenumbers
  .id

46
Theta style vs. ANSI

• Theta Style (used in most SQL books)
• SELECT name,phone,zip FROM people, phonenumbers,
  address
• WHERE people.addressidaddress.addressid AND
  people.idphonenumbers.id
• ANSI Style uses JOIN
• SELECT name,phone,zip FROM people
• JOIN phonenumbers ON people.idphonenumbers.id
• JOIN address ON people.addressidaddress.addressi
  d

47
Other SELECT examples

• SELECT FROM contacts WHERE name is NULL
• SELECT FROM contacts WHERE zip IN
  (14454,12345)
• SELECT FROM contacts WHERE zip IN ( SELECT
  zip FROM address WHERE stateNY )

48
GROUP BY/HAVING

• The GROUP BY clause allows you to group results
  together with aggregate functions
• AVG(), COUNT(), MAX(), MIN(), SUM()
• COUNT DISTINCT
• HAVING allows you to search the GROUP BY results

49
GROUP BY Examples

• SELECT company,count(company)FROM contactsGROUP
  BY company
• SELECT company,count(company)FROM contactsGROUP
  BY companyHAVING count(company) 5

50
ORDER BY

• The ORDER BY clause allows you to sort the
  results returned by SELECT.
• SELECT FROM contacts
• ORDER BY company
• SELECT FROM contacts
• ORDER BY company, name

51
Views

• You can use CREATE VIEW to create a virtual
  table from a SELECT statement.
• CREATE VIEW contactview AS
• (SELECT name,phone,zip FROM
• people,phonenumbers,address
• WHERE people.idphonenumbers.id AND
  people.addressidaddress.addressid)

52
Now Lets do it on the web

• PHP style

53
Basic PHP/SQL interaction

• Open a connection to the database
• If ok, generate SQL command
• Execute SQL command
• Handle responses from the server
• If not done, go back to step 2
• If done, close connection to database

54
Creating a table with PHP

• // create a connection with the database
• connectionpg_Connect("hostoracle.geneseo.edu
  port5432
• dbnamemgmt357 usermgmt357 passwordecommerce")
  
• // if there is no connection, generate an error
  and get out
• if (!connection)
• print pg_ErrorMessage() exit(-1)
• // Create the table with the pg_exec command
• resultpg_exec(connection,"create table
  customer (
• id int8,
• name varchar(50),
• address varchar(50),
• email varchar(64)
• )")
• if (result)
• print "The customer table has been created."
• else
• print pg_ErrorMessage()

55
Entering data with PHP

• if (submit)
• // create a connection with the database
• connectionpg_Connect("hostoracle.geneseo.edu
  port5432 dbnamemgmt357 usermgmt357
• passwordecommerce")
• if (!connection)
• print pg_ErrorMessage()
• exit(-1)
• idtime() // set a unique id number to each
  record
• resultpg_exec(connection, "insert into
  customer VALUES (id,'name','address','email')
  ")
• if (result)
• print "The customer data has been inserted."
• else

• pg_close(connection)
• else
• // display form
• ?
• PHP_SELF?"
• Name
• Address
• Email
• information
• ?

56
Updating data with PHP

• if (submit)
• // create a connection with the database
• connectionpg_Connect("hostoracle.geneseo.edu
  port5432 dbnamemgmt357 usermgmt357
• passwordecommerce")
• if (!connection)
• print pg_ErrorMessage()
• exit(-1)
• resultpg_exec(connection, "update customer
• set name'name',
• set address'address',
• set email'email' where id'id'")
• if (result)
• print "The customer data has been inserted."

• pg_close(connection)
• else
• // display form
• ?
• PHP_SELF?"
• Id
• Name
• Address
• Email
• information
• ?

57
Displaying data with PHP

• resultpg_exec(connection,"select from
  customer")
• if (result)
• // get the number of rows and store it in r
• r pg_numrows(result)
• print "The customer table has r row(s) of
  data."
• // set the row counter to the beginning 0
• row0
• print "\n"
• // while there are records to deal with...
• while (datapg_fetch_object(result,row))
• print ""
• print "data-name"
• print "data-address"
• print "data-email"
• print "\n"
• row // increment the counter
• print "\n"

58
Updating data with PHP part 2

• if (submit)
• // create a connection with the database
• connectionpg_Connect("hostoracle.geneseo.edu
  port5432 dbnamemgmt357 usermgmt357
• passwordecommerce")
• if (!connection)
• print pg_ErrorMessage()
• exit(-1)
• resultpg_exec(connection, "update customer
• set name'name',
• set address'address',
• set email'email' where id'id'")
• if (result)
• print "The customer data has been inserted."

• pg_close(connection)
• else
• // display form
• ?
• PHP_SELF?"
• connectionpg_Connect("hostoracle.geneseo.edu
  port5432 dbnamemgmt357 usermgmt357
  passwordecommerce")
• resultpg_exec(connection,"select id from
  customer limit 30")
• row0
• print "\n"
• while (datapg_fetch_object(result,row))
• print "data-id\n"
• row
• print "\n"
• ?
• Name
• Address
• Email

59
Getting even easier PEAR

• Abstracting the database interface
• include('dbinfo.php')
• require_once( 'DB.php' )
• db  DBconnect( "mysql//userpass_at_host/dbn
  ame" )
• // no need to select DB
• sql  'SELECT  FROM demo'
• demoResult  db-query(sql)
• while (demoRow  demoResult-fetchRow()) 
•     echo demoRow2 . '
  '
• db-disconnect()
• ?
• http//www.phpbuilder.com/columns/allan20010115.ph
  p3?page1

60
Resources

• PHP Sites
• http//www.php.net
• http//www.phpbuilder.com
• http//www.devshed.com
• http//www.geneseo.edu/kma/PHP_Intro
• Books
• SQL in a Nutshell, Kevin Kline, OReilly
• PostgreSQL Introduction and Concepts, Bruce
  Momjian, Addision Wesley
• Introduction to Database Systems, C.J. Date