Listing table rows
The SELECT command is used to list the contents
of a table. The syntax is:
Introduction to SELECT columnlist FROM tablename
Structured Query SELECT * FROM PRODUCTS
SELECT ProductID
Language (SQL) ,ProductName
,UnitPrice
FROM Products
Mr. Arnel Albis
Lecturer
Database Systems: 9th Ed, Coronel et al. 2
Updating table rows Deleting table rows
Use the UPDATE command to modify data in a Use the DELETE command to erase data in a
table. The syntax is: table. The syntax is:
UPDATE tablename DELETE FROM tablename
SET columnname = expression [, columnname = [WHERE conditionlist];
expression
[WHERE conditionlist]; DELETE FROM Employees
WHERE EmployeeID = 10
UPDATE Products
SET ProductName = 'Pork Adobo'
WHERE UnitsInStock >= 120
Database Systems: 9th Ed, Coronel et al. 3 Database Systems: 9th Ed, Coronel et al. 4
Selecting rows with conditional
Using computed columns and column alias
restrictions
SELECT columnlist SELECT ProductID
FROM tablelist ,ProductName
[WHERE conditionlist]; ,UnitPrice * UnitsInStock
FROM Products
SELECT ProductID
,ProductName
,UnitPrice
FROM Products
WHERE UnitPrice = 22
Database Systems: 9th Ed, Coronel et al. 5
Using computed columns and column alias Logical Operators: Using the OR operator
continued
SELECT ProductID SELECT ProductID
,ProductName ,ProductName
,UnitPrice * UnitsInStock AS TotalValue ,UnitPrice
FROM Products FROM Products
WHERE UnitPrice = 22
OR UnitPrice = 12
Database Systems: 9th Ed, Coronel et al. 8
Logical Operators: Using the AND operator Logical Operators: Using the NOT operator
SELECT ProductID SELECT ProductID
,ProductName ,ProductName
,UnitPrice ,UnitPrice
,UnitsOnOrder FROM Products
FROM Products WHERE NOT UnitPrice < 60
WHERE UnitPrice >= 10 AND UnitsOnOrder >= 50
Database Systems: 9th Ed, Coronel et al. 9 Database Systems: 9th Ed, Coronel et al. 10
Special Operators The BETWEEN special operator
BETWEEN used to check whether an attribute SELECT ProductID
value is within a range ,ProductName
IS NULL used to check whether an attribute ,UnitPrice
value is null FROM Products
LIKE used to check whether an attribute value WHERE UnitPrice BETWEEN 40 AND 60
matches a given string pattern
IN used to check whether an attribute value
matches any value within a value list
Database Systems: 9th Ed, Coronel et al. 11 Database Systems: 9th Ed, Coronel et al. 12
The IS NULL special operator The LIKE special operators
SELECT EmployeeID It is used in conjunction with wildcards to find
,LastName patterns within string attributes.
,FirstName
Wildcard Description
,Region
FROM Employees _ Any single character
WHERE Region IS NULL % Any series of zero or more characters
[a-f] Any single character in the range a-f
[^a-f] Any single character NOT in the range
a-f
[abc] Any single character contained in the
list (a, b, or c)
[^abc] Any single character NOT contained in
Database Systems: 9th Ed, Coronel et al. 13 the list (a, b, or c)
The LIKE special operator The LIKE special operator
Using the _ wildcard Using the % wildcard
SELECT LastName, FirstName, Title, City SELECT LastName, FirstName, Title, City
FROM Employees FROM Employees
WHERE FirstName LIKE '__bert' WHERE Title LIKE 'Sales%'
Database Systems: 9th Ed, Coronel et al. 15 Database Systems: 9th Ed, Coronel et al. 16
The LIKE special operator The LIKE special operator
Using the [a-f] wildcard Using the [^a-f] wildcard
SELECT CustomerID SELECT CustomerID
,CompanyName ,CompanyName
,ContactName ,ContactName
,ContactTitle ,ContactTitle
,City ,City
FROM Customers FROM Customers
WHERE CompanyName LIKE '[c-k]%' WHERE CompanyName LIKE '[^c-k]%'
Database Systems: 9th Ed, Coronel et al. 17 Database Systems: 9th Ed, Coronel et al. 18
The LIKE special operator The LIKE special operator
Using the [abc] wildcard Using the [^abc] wildcard
SELECT CustomerID SELECT CustomerID
,CompanyName ,CompanyName
,ContactName ,ContactName
,ContactTitle ,ContactTitle
,City ,City
FROM Customers FROM Customers
WHERE CompanyName LIKE '[cflp]%' WHERE CompanyName LIKE '[^cflp]%'
Database Systems: 9th Ed, Coronel et al. 19 Database Systems: 9th Ed, Coronel et al. 20
The IN special operator Ordering a listing
SELECT CustomerID, CompanyName SELECT columnlist
,ContactName, ContactTitle, City FROM tablelist
FROM Customers [WHERE conditionlist]
WHERE City = 'London
[ORDER BY columnlist [ASC | DESC] ];
OR City = 'Sao Paulo'
SELECT *
is the same as FROM Products
ORDER BY ProductName
SELECT CustomerID, CompanyName
,ContactName, ContactTitle, City SELECT *
FROM Customers FROM Products
WHERE City IN ('London','Sao Paulo') ORDER BY ProductName DESC
Database Systems: 9th Ed, Coronel et al. 21 Database Systems: 9th Ed, Coronel et al. 22
Listing unique values Aggregate Functions
Produces a list of only those values that are FUNCTION OUTPUT
different from one another. COUNT The number of rows
containing non-null values
SELECT DISTINCT City MIN The minimum attribute value
FROM Customers encountered in a given
column
ORDER BY City
MAX The maximum attribute value
encountered in a given
column
SUM The sum of all values for a
given column
AVG The arithmetic mean
(average) for a specified
column
Database Systems: 9th Ed, Coronel et al. 23
The COUNT function The MAX and MIN function
Used to tally the number of non-null values of an Use to get the Highest (Maximum) and Lowest
attribute. (Minimum) value respectively.
SELECT COUNT(*) SELECT MAX(UnitsOnOrder)
FROM Customers FROM Products
SELECT MIN(UnitPrice)
FROM Products
Database Systems: 9th Ed, Coronel et al. 25 Database Systems: 9th Ed, Coronel et al. 26
The SUM function The AVG function
It computes the total sum for any specified It computes for the average of any specified
attribute, using whatever conditions imposed attribute
SELECT SUM(UnitsOnOrder) SELECT AVG(UnitsOnOrder)
FROM Products FROM Products
Database Systems: 9th Ed, Coronel et al. 27 Database Systems: 9th Ed, Coronel et al. 28
Grouping Data Grouping Data
It is generally used when attribute columns are
SELECT OrderDate
combined with aggregate functions in the SELECT
,AVG(Freight) AS AverageFreight
statement.
FROM Orders
GROUP BY OrderDate
SELECT columnlist ORDER BY OrderDate
FROM tablelist
[WHERE conditionlist ]
[GROUP BY columnlist ]
[HAVING conditionlist ]
[ORDER BY columnlist [ASC | DESC] ];
Database Systems: 9th Ed, Coronel et al. 29 Database Systems: 9th Ed, Coronel et al. 30
The GROUP BYs HAVING clause
It operates very much like the WHERE clause in
the SELECT statement. However, the WHERE
clause applies to columns and expressions for
individual rows, while the HAVING clause is
applied to the output of a GROUP BY operation.
SELECT OrderDate
,AVG(Freight) AS AverageFreight
FROM Orders
GROUP BY OrderDate
,ShipRegion
HAVING ShipRegion IS NULL
ORDER BY OrderDate
Database Systems: 9th Ed, Coronel et al. 31
