SQL Review

1
SQL Review

• Tonga Institute of Higher Education

2
SQL Introduction

• SQL (Structured Query Language) a language that
  allows a developer to work with data in a
  database.
• This presentation serves to provide a basic
  review of SQL statements. Advanced concepts are
  covered in IT244 Database Management Systems
• All examples will use the Northwind Access
  database. This database is available from
  www.microsoft.com.

3
Working with Data in a Database

• SQL answers 4 common questions
• How do I get data from a database? SELECT
• How do I add new data to a database? INSERT
• How do I change data in a database? UPDATE
• How do I delete data from a database? DELETE

4
SELECT Statement Basics

• How do I get data from a database? Use SELECT!
• SELECT ltColumn Namesgt FROM ltTable Namegt WHERE
  ltCriteriagt
• Returns data from the columns of the rows that
  match the criteria.

5
SELECT Column Names

• Specify the column names that you wish to receive
  data from.
• Multiple column names should be separated by
  commas.
• SELECT CompanyName, ContactName, Phone FROM
  Customers
• An asterick () can be used to get data from all
  the columns.
• SELECT From Customers

6
SELECT Criteria

• Criteria can be added to get specific rows of
  data.
• For example, this code returns rows that include
  CompanyNames from Customers that have a contact
  name Maria.
• SELECT CompanyName FROM Customers WHERE
  ContactName Maria
• In this case, nothing is returned because there
  is no customer with the Contact Name of exactly
  Maria

7
SELECT Criteria Wildcards

• Criteria wildcards can be used to find rows that
  contains certain pieces of data.
• For example, this code returns all CompanyNames
  from Customers that INCLUDE a contact name Maria.
• SELECT CompanyName FROM Customers WHERE
  ContactName like Maria
• Returns 2 rows both include Maria in the
  ContactName column.

Often is used for wildcards Sometimes is used
for wildcards
Any characters can be after Maria
Any characters can be before Maria
8
SELECT Summary

• Using the SELECT statement, we can query our
  database.
• For example, to get all the column data from all
  rows with a contact name that includes Maria, use
  this code
• SELECT FROM Customers WHERE ContactName like
  Maria

9
Comprehension Check

• SELECT Statement

10
INSERT Statement Basics

• How do I add new data to a database? Use INSERT!
• INSERT INTO ltTable Namegt (ltColumn Namesgt) VALUES
  (ltDatagt)
• Inserts a new row with data in the columns
  specified.

11
INSERT Data

• INSERT INTO Customers (CustomerID, CompanyName)
  VALUES (TICO, Ti Company and Associates)
• Each column name to the left of VALUES matches a
  piece of data to the right.
• Use commas to separate each column name and piece
  of data.
• String require single quotes. Sometimes, double
  quotes may be used.
• Make sure you insert data for required columns.

12
INSERT Summary

• Using the INSERT statement, we can insert new
  rows into our database.
• For example, to add a new customer with the ID
  TICO and name of Ti Company and Associates, use
  this code
• INSERT INTO Customers (CustomerID, CompanyName)
  VALUES (TICO, Ti Company and Associates)

13
Comprehension Check

• INSERT Statement

14
UPDATE Statement Basics

• How do I change data in a database? Use UPDATE!
• UPDATE ltTable Namegt SET ltColumn Namegt ltNew
  Valuegt WHERE ltCriteriagt
• Updates rows that match the criteria by changing
  the data in the columns specified to the new
  value.

15
UPDATE Details

• UPDATE Customers SET ContactName Sione
  Tukuia, Phone 13533 WHERE CustomerID
  ANATR
• Use commas to separate each data update.
• String require single quotes. Sometimes, double
  quotes may be used.
• Make sure the criteria is correct. If no
  criteria is included, then all data will be
  updated.

16
UPDATE Summary

• Using the UPDATE statement, we can update data in
  our database.
• For example, to change the contact name and phone
  number of the customer with ID ANATR, use the
  following code
• UPDATE Customers SET ContactName Sione
  Tukuia, Phone 13533 WHERE CustomerID
  ANATR

17
Comprehension Check

• UPDATE Statement

18
DELETE Statement Basics

• How do I delete data from a database? Use
  DELETE!
• DELETE FROM ltTable Namegt WHERE ltCriteriagt
• Deletes rows that match the criteria.
• DELETE FROM Customers WHERE ContactName like
  Maria

19
DELETE Details

• DELETE FROM Customers WHERE CustomerID ANATR
• Make sure the criteria is correct. If no
  criteria is included, then all data will be
  deleted.

20
DELETE Summary

• Using the DELETE statement, we can delete data in
  our database.
• For example, to delete the customer with ID
  ANATR, use the following code
• DELETE FROM Customers WHERE CustomerID ANATR

21
Comprehension Check

• DELETE Statement