Lecture6:Data Manipulation in SQL , Simple SQL queries

1
Lecture6Data Manipulation in SQL , Simple SQL
queries
Ref. Chapter5

• Prepared by L. Nouf Almujally

2
The Process of Database Design
3
Tables in the Examples

• Customer(custNo, custName, custSt, custCity, age)
• Product(prodNo, prodName, prodDes, price)
• Orders(ordNo, ordDate, custNo, prodNo, quantity)
• Where
• custName, custSt, custCity, prodName, prodDes are
  strings
• ordDate is date
• Others are numbers

4
Sample Data in Customer Table

custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
5
Sample Data in Product Table

prodNo prodName prodDes price
100 P0 Food 100
101 P1 healthy Food 100
102 P2 200
103 P3 self_raising flour,80wheat 300
104 P4 network 80x 300
6
Sample Data in Orders Table

ordNo ordDate custNo prodNo quantity
1 01-jan-2003 1 100 2
2 02-jan-2003 1 101 1
3 01-jan-2003 2 102 1
4 01-jan-2003 3 100 2
5 03-jan-2003 1 101 1
6 06-mar-2003 2 100 10
7
Data Manipulation (DML)

• DML is used to retrieve and modify data in the
  tables
• Four basic statements
• Insert Into
• Select
• Update
• delete From

8
Insert Statement

• The INSERT statement adds one or more new rows of
  data to a database table.
• Syntax
• Note
• value list must correspond to column list
• If column list is omitted, then a value for every
  attribute is required
• The data types must be correct

INSERT INTO table_name VALUES (value1,value2,valu
e3,...)
INSERT INTO table_name (column1,column2,column3,..
.)VALUES (value1,value2,value3,...)
9
Insert Statement Example

• Example for table Customer,
• Insert into Customer(custNo, custName) values
  ('6', 'John')
• Output 1 row inserted
• Insert into Customer values ('7', 'David ',
  'St1','City1', 20)
• Output 1 row inserted

10
Simple SELECT Queries

• The SELECT command is used for submitting queries
  to the DBMS.
• Syntax

SELECT expression_list FROM table_list
WHERE condition ORDER
BY expression_list
11
Simple SELECT Queries

• Expression in SELECT statement
• Condition in WHERE statement
• an expression that can be evaluated to TRUE or
  FALSE.
• Only rows satisfying the condition will be
  chosen.
• Condition can be simple comparison or compound
  expression

Expression Example
column names SELECT prodNo
arithmetic operators for numbers , -, , / SELECT Price10
Constant SELECT 'The first name is', fname FROM customer
12
Conditions in the WHERE Clause

• WHERE clause consists of five basic search
  conditions
• Comparison Compare the value of one expression
  to the value of another expression ( , lt, gt, lt,
  gt, ltgt).
• Range Test whether the value of an expression
  falls within a specified range of values
  (BETWEEN/ NOT BETWEEN).
• Set membership Test whether the value of an
  expression equals one of a set of values (IN/ NOT
  IN).
• Pattern match Test whether a string matches a
  specified pattern (LIKE/ NOT LIKE).
• NULL Test whether a column has null value (IS
  NULL/ IS NOT NULL).
• Note Basic comparisons can be compounded by AND,
  OR, NOT
• Eg, prodNo100 and ordDate'01-jan-2003'

13
Simple Queries Comparison search condition

• Comparison operators , lt , gt , lt , gt , ltgt
• Example 1 List all products (by prodNo and
  price) which are priced more than 100.
• Select prodNo, price
• From Product
• Where price gt100
• Example 2 What is the name of the customer whose
  custNo is 1?
• Select custName
• From customer
• Where custNo1

prodNo price
102 200
103 300
104 300
custName
C1
14
Listing All Data in a Table

• If WHERE clause is omitted, all rows will be
  listed.
• Example List all data in the customer table
• SELECT custNo, custName, custSt, custCity
• FROM customer
• OR (use for all columns)
• SELECT FROM CUSTOMER

custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
15
Simple Queries Compound comparison search
condition

• Compound comparison operators AND , OR , NOT ,
  ( )
• Order of evaluation
• Expression is evaluated left to right
• Between brackets
• NOT
• AND
• OR

16
Examples

• Example Find all orders of product 100 before
  02/01/03.
• SELECT
• FROM orders
• WHERE prodNo 100 AND ordDate
  lt'02-jan-2003'
• Example Find all products priced less than 200
  or greater than 300
• SELECT
• FROM product
• WHERE price lt 200 OR price gt300

ordNo ordDate custNo prodNo quantity
1 01-jan-2003 1 100 2
4 01-jan-2003 3 100 2
prodNo prodName prodDes price
100 P0 Food 100
101 P1 healthy food 100
17
More Examples

• Example Find the customer with name C1 and live
  in Riyadh or Jeddah
• SELECT
• FROM customers
• WHERE custName 'C1 AND
  (custCity'Jeddah' OR custCity'Riyadh')

custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
18
Simple Queries BETWEEN / NOT BETWEEN

• The BETWEEN operator is used to select values
  within a range.
• The NOT BETWEEN checks if a value is outside a
  range.
• Syntax

SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN NOT BETWEEN value1
AND value2
19
BETWEEN Example

• Example List products priced between 200 and
  300.
• SELECT
• FROM product
• WHERE price gt200 and price lt300
• or equivalently
• SELECT
• FROM product
• WHERE price between 200 and 300

prodNo prodName prodDes price
102 P2 200
103 P3 self_raising flour,80wheat 300
104 P4 network 80x 300
20
Simple Queries IN / NOT IN

• IN tests whether a data value matches one of a
  list values.
• NOT IN checks for data values that do not lie in
  a specific list of values
• Syntax

SELECT column_name(s) FROM table_name
WHERE column_name IN NOT IN (value1,value2,...)
21
IN Example

• Example List all customers living in Riyadh, or
  Dammam, or Jeddah.
• SELECT
• FROM Customer
• WHERE custCity 'Jeddah' OR custCity
  'Riyadh' OR
• custCity 'Dammam'
• or equivalently
• SELECT
• FROM Customer
• WHERE custCity IN (Jeddah', Riyadh',
  Dammam')

custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
22
Simple Queries LIKE / NOT LOKE

• LIKE is used to search for a specified pattern in
  a column.
• NOT LIKE allows you to select records that does
  NOT match the pattern.
• Syntax
• SQL has special pattern matching symbol
• represents any sequence of zero or more
  characters
• _ represents any single character

SELECT column_name(s) FROM table_name
WHERE column_name LIKE NOT LIKE pattern
23
LIKE Example

• Example List all products whose description
  contain the string 'Food'.
• SELECT
• FROM product
• WHERE prodDes LIKE 'Food'

prodNo prodName prodDes price
100 P0 Food 100
101 P1 healthy food 100
24
More Examples of LIKE NOT LIKE

• LIKE 'H_' any string beginning with H and
  exactly 2 characters long.
• NOT LIKE 'H' any string not beginning with H
• LIKE 'y' any string ending with 'y'

25
Simple Queries IS NULL and IS NOT NULL

• It is not possible to test for NULL values with
  comparison operators, such as , lt, or ltgt.
• To test for null values in a query, use IS NULL
  or IS NOT NULL in the WHERE clause.
• Comparisons between a NULL and any other value,
  return unknown and the result will not be
  included in the final results
• Syntax

SELECT column_name(s) FROM table_name
WHERE column_name IS NULL
26
IS NULL and IS NOT NULL Examples

• Example List all products with a product
  description.
• SELECT
• FROM product
• WHERE prodDes IS NOT NULL
• Similarly, to list products without description,
  use
• SELECT
• FROM product
• WHERE prodDes IS NULL

prodNo prodName prodDes price
100 P0 Food 100
101 P1 healthy Food 100
103 P3 self_raising flour,80wheat 300
104 P4 network 80x 300
prodNo prodName prodDes price
102 P2 200
27
(No Transcript)
28
Simple Queries Use of DISTINCT

• Use Distinct in the select statement To remove
  duplicate values
• Syntax

SELECT DISTINCT column_name,column_name
FROM table_name
29
Use of DISTINCT Example

• Example List all customer cities.
• SELECT custCity FROM customer
• A city will be repeated if there are more than
  one customer in that city. To eliminate the
  duplicates, use
• SELECT DISTINCT custCity FROM customer

custCity
Jeddah
Riyadh
Riyadh
Dammam
Riyadh
custCity
Jeddah
Riyadh
Dammam
30
Simple Queries Ordering of Rows

• Rows can be put in ascending or descending order
  of some columns. To do this, use ORDER BY
• Syntax
• Default order (ie, if desc is not used) is
  ascending

SELECT column_name,column_name FROM
table_name WHERE Condition
ORDER BY column_name,column_name ASCDESC
31
Ordering of Rows Example

• Example list all products in descending order
  of price
• SELECT
• FROM product
• ORDER BY price desc
• Can also order by several attributes, eg.
• ORDER BY price desc, prodName

prodNo prodName prodDes price
103 P3 self_raising flour,80wheat 300
104 P4 network 80x 300
102 P2 200
100 P0 Food 100
101 P1 healthy Food 100
32
Ordering of Rows Example

• SELECT FROM Individual
• ORDER BY FirstName, LastName

33
Operators summary
Operator Function
equal to
lt less than
gt greater than
lt less than equal to
gt greater than equal to
ltgt not equal to
LIKE used as wildcard. eg. LIKE PRE
IN test for in an enumerated list.
BETWEEN used to select values within a range
34
(No Transcript)
35
Update Statement

• The UPDATE statement is used to update records in
  a table
• Syntax
• Notice the WHERE clause in the SQL UPDATE
  statement!The WHERE clause specifies which
  record or records that should be updated. If you
  omit the WHERE clause, all records will be
  updated!

UPDATE table_name SET column1value1,
column2value2,... WHERE
some_columnsome_value
36
Update Statement Example

• Example Customer C1 has changed his city to
  Riyadh.
• UPDATE Customer SET custCityRiyadh ,
  custSt'12 Mains Rd' WHERE CustNameC1'
• Output 1 row updated
• Select From Customer

custNo custName custSt custCity age
1 C1 12 Main Rd Riyadh 20
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
37
Update Statement Example

• Be careful when updating records. If we had
  omitted the WHERE clause, in the example before,
  like this
• UPDATE Customer SET custCityRiyadh ,
  custSt'12 Mains Rd'
• Output 5 rows updated
• Select From Customer

custNo custName custSt custCity age
1 C1 12 Main Rd Riyadh 20
2 C2 12 Main Rd Riyadh 30
3 C3 12 Main Rd Riyadh 25
4 C4 12 Main Rd Riyadh
5 C5 12 Main Rd Riyadh
38
Delete Statement

• The DELETE statement is used to delete records in
  a table.
• Syntax
• Notice the WHERE clause in the SQL DELETE
  statement!The WHERE clause specifies which
  record or records that should be deleted. If you
  omit the WHERE clause, all records will be
  deleted!
• DELETE does not delete the table itself, only
  rows in the table.

DELETE FROM table_name WHERE
some_columnsome_value
39
Delete Statement Example

• Example Delete Customer C1
• DELETE FROM Customer WHERE CustNameC1'
• Output 1 row deleted
• Select From Customer

custNo custName custSt custCity age
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
40
Delete Statement Example

• Example Delete all Customers
• DELETE FROM Customer
• Output 5 row deleted
• Select From Customer

custNo custName custSt custCity age




41
Truncate Statement

• TRUNCATE deletes all data in a table and frees
  storage space for the table rows ( deletes data
  faster but you cannot rollback)
• Syntax
• TRUNCATE get rid of the data but not the table
  itself (DROP)

Trancate table_name
42
Truncate Example

• Example Delete all Products
• TRUNCATE TABLE product

prodNo prodName prodDes price
43
Extra Example
EMPLOYEE
DEPARTMENT
44
Basic SQL SELECT Queries
SELECT firstName, lastName FROM Employee
WHERE employeeNo E1
45
Compound Comparison
SELECT deptNumber FROM EMPLOYEE WHERE
lastName Smith OR lastName Hodges
Duplicate Removal
SELECT DISTINCT deptNumber FROM EMPLOYEE
46
Set Membership Search ( IN)
SELECT deptNumber, mailNumber FROM
DEPARTMENT WHERE deptName IN ( Computer
Science, Physics)
Pattern Match Search ( LIKE)
SELECT employeeNo, deptNumber FROM
EMPLOYEE WHERE firstName LIKE an
47
Sorting Output from Queries
SELECT employeeNo , lastName FROM
EMPLOYEE ORDER BY lastName

48
(No Transcript)
49
References

• Database Systems A Practical Approach to
  Design, Implementation and Management. Thomas
  Connolly, Carolyn Begg. 5th Edition,
  Addison-Wesley, 2009.