Transcript Introduction to Java - Tonga Institute of Higher Education

SQL Review
Tonga Institute of Higher
Education
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.
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



SELECT Statement Basics

How do I get data from a database? Use SELECT!
SELECT <Column Names> FROM <Table Name>
WHERE <Criteria>

Returns data from the columns of the rows that match
the criteria.
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
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’
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%’
Any characters
can be before Maria

Any characters
can be after Maria
Returns 2 rows both include
Maria in the ContactName
column.
Often % is used for wildcards
Sometimes * is used for wildcards
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%’
Comprehension
Check
SELECT Statement
INSERT Statement Basics

How do I add new data to a database? Use
INSERT!

INSERT INTO <Table Name> (<Column
Names>) VALUES (<Data>)

Inserts a new row with data in the columns
specified.
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.
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’)
Comprehension
Check
INSERT Statement
UPDATE Statement Basics

How do I change data in a database? Use
UPDATE!

UPDATE <Table Name> SET <Column Name>
= ‘<New Value>’ WHERE <Criteria>

Updates rows that match the criteria by
changing the data in the columns specified to
the new value.
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.
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’
Comprehension
Check
UPDATE Statement
DELETE Statement Basics

How do I delete data from a database? Use
DELETE!
DELETE FROM <Table Name> WHERE <Criteria>

Deletes rows that match the criteria.
DELETE FROM Customers WHERE
ContactName like ‘%Maria%’
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.
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’
Comprehension
Check
DELETE Statement