Transcript Document

CIS 375—Web App Dev II
SQL
Introduction





SQL (Structured _______ Language) is an ANSI
standard language for accessing databases.
SQL can execute queries, retrieve data, insert new
records, delete records, and update records in a
database.
SQL works with database programs like MS Access,
DB2, Informix, MS SQL Server, Oracle, Sybase, etc.
A database contains one or more ______. Each table
is identified by a name and contains records (rows)
and fields (columns).
Besides W3Schools, you can also try
2
http://www.sqlcourse.com/ (optional).
SQL Queries, DML, DDL


Suppose a table named Persons has fields SSN,
LastName, FirstName, Age, Address, City and has
ten records.
An SQL query:





SELECT LastName FROM Persons
Provides a list of all ten last names in the table
SQL also has a DML (Data ___________ Language)
to update, insert, and delete records.
SQL also has a DDL (Data __________ Language) to
do things like create and delete tables, and to create
table indexes (________ keys).
For example, SSN would be an index in Persons.
3
SQL: The SELECT Statement


The SELECT statement specifies ________ of data
to be returned from a table.
Examples of SQL SELECT statements for the table
Persons.




SELECT LastName, FirstName FROM Persons (use a “,” to
separate column names)
SELECT * FROM Persons (* means “all”)
Executing an SQL statement produces a ________
set of data.
Some database programs require a “;” after each
SQL statement, but many don’t.
4
SQL: The WHERE Clause


The WHERE clause specifies _____ of data to be
returned from a table.
Example of SQL WHERE clause.



SELECT * FROM Persons WHERE City=‘Springfield’
(returns all fields for records where City is Springfield)
Use quotes around text, but not around numbers.
Other operators include <>, >, <, >=, <=,
__________, and LIKE.


SELECT LastName, FirstName FROM Persons WHERE City
BETWEEN ‘A’ AND ‘M’
SELECT * FROM Persons WHERE LastName LIKE ‘A%’
(returns all last names beginning with A)
5
SQL: AND, OR, NOT


AND and OR are used to join conditions in a
_______ clause.
Examples:



SELECT * FROM Persons WHERE LastName = ‘Johnson’
AND FirstName = ‘Richard’
SELECT * FROM Persons WHERE (FirstName='Tove' OR
FirstName='Stephen') AND LastName='Svendson'
You can also use the keyword _____ with many
different kinds of SQL statements:


SELECT * FROM Persons WHERE NOT (City=‘Berlin' OR
City=‘London')
SELECT LastName, FirstName FROM Persons WHERE City
6
NOT BETWEEN ‘A’ AND ‘M’
SQL: SELECT DISTINCT



A SELECT statement could possibly result in multiple
values.
Use DISTINCT to avoid this possibility.
Example:



Suppose you want a list of all distinct cities in the Persons
table.
SELECT DISTINCT City FROM Persons
Without DISTINCT, the city of Springfield, for example,
might appear several times in the list (for each person
who lives in Springfield).
7
SQL: ORDER BY


ORDER BY is used for _________ results of a query.
Example:


Sort by multiple columns:


SELECT * FROM Persons ORDER BY LastName, FirstName
Sort in descending or ascending order:


SELECT LastName, FirstName FROM Persons ORDER BY
LastName
SELECT * FROM Persons ORDER BY City DESC, LastName
ASC
You can test your SQL skills with a real database at
http://www.w3schools.com/sql/sql_tryit.asp.
8
SQL: INSERT INTO Statement

To insert a new row into a table:


INSERT INTO Persons VALUES (‘Bush', ‘George W.', 56,
‘1600 Pennsylvania Ave.', ‘Washington, D.C.')
To insert only certain values in certain columns of a
new row:

INSERT INTO Persons (LastName, Address) VALUES
(‘Hood', ‘Sherwood Forest')
9
SQL: UPDATE Statement

To modify data in an existing row:


UPDATE Persons SET FirstName = ‘Rick' WHERE
LastName = ‘Johnson‘
Update several columns in a row:

UPDATE Persons SET Address = 'Stien 12', City =
'Stavanger' WHERE LastName = 'Rasmussen'
10
SQL: DELETE Statement

To delete one or more rows from a table:


DELETE FROM Persons WHERE LastName = 'Rasmussen'
To delete all rows:

DELETE * FROM Persons
11
SQL: COUNT Statement

To count all rows in a table (‘*’ means no ________
specified):


Using COUNT with a WHERE clause:


SELECT COUNT(*) FROM Persons WHERE Age>20
When a column is specified, the null entries are not
counted:


SELECT COUNT(*) FROM Persons
SELECT COUNT(Age) FROM Persons
To count distinct entries in a particular column:

SELECT COUNT(DISTINCT City) FROM Persons
12