Lecture7:Data Manipulation in SQL Advanced Queries

1
Lecture7Data Manipulation in SQLAdvanced
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
Aggregate Functions

• COUNT - returns the number of selected values
• SUM - returns the sum of selected (numeric)
  values
• AVG - returns the average of selected (numeric)
  values
• MIN - returns the minimum of selected values
• MAX - returns the maximum of selected values

8
Use of COUNT(column_name)

• The COUNT(column_name) function returns the
  number of values (NULL values will not be
  counted) of the specified column
• Syntax

SELECT COUNT(column_name) FROM table_name
9
Use of COUNT(column_name)

• Example 1 List the number of products in the
  product table
• SELECT count(prodNo) FROM product
• Example 2 List the number of product
  descriptions in the product table
• SELECT count(prodDes) FROM product
• Note count(prodDes) does not count rows that
  have NULL value for prodDes.

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
5
4
10
Use of COUNT()

• The COUNT() function returns the number of
  records in a table (NULL values will be counted)
• Syntax

SELECT COUNT() FROM table_name
11
Use of COUNT ()

• Example 1 How many products are there in the
  product table?
• SELECT count() FROM product
• Example 2 How many products are priced at 300?
• SELECT count()
• FROM product
• WHERE price 300
• Note count() also count rows that have NULL
  values

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
5
2
12
Use of COUNT(DISTINCT column_name)

• The COUNT(DISTINCT column_name) function returns
  the number of distinct values of the specified
  column
• Syntax

SELECT COUNT(DISTINCT column_name) FROM
table_name
13
Use of COUNT(DISTINCT column_name)

• Example1 How many cities are the customers
  located in ?
• SELECT count(distinct custCity) from customer
• Example 2 How many customers ordered products
  since 01/01/2003?
• SELECT count(distinct custNo)
• FROM orders
• WHERE ordDate gt '01-jan-2003'

3
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
3
14
Use of SUM

• The SUM() function returns the total sum of a
  numeric column.
• Syntax

SELECT SUM(column_name) FROM table_name
15
Use of SUM Example

• Example 1 How many products were ordered by
  customer 1?
• SELECT SUM(quantity)
• FROM orders
• WHERE custNo 1
• Example 2 How many orders were made by customer
  1 and how many products did he order?
• SELECT count(ordNo), SUM(quantity)
• FROM orders
• WHERE custNo 1

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
4
3
4
16
Use of Avg

• The AVG() function returns the average value of a
  numeric column.
• Syntax

SELECT AVG(column_name) FROM table_name
17
Use of Min, Max

• The MIN() function returns the smallest value of
  the selected column.
• The MAX() function returns the largest value of
  the selected column.
• Syntax

SELECT MIN(column_name), MAX (column_name)
FROM table_name
18
Example Use of AVG, MIN and MAX

• Example list the minimum, maximum and average
  price of all products.
• SELECT MIN(price), MAX(price), AVG(price)
• FROM product
• Note if some product's price are NULLs, then
  SUM and AVG do not take those products into
  consideration.

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
100
300
200
19
(No Transcript)
20
Advanced queries (GROUP BY)

• General Syntax of SELECT command
• SELECT DISTINCT ALL
• columnExpression AS newName ,...
• FROM TableName alias , ...
• WHERE condition
• GROUP BY columnList
• HAVING condition
• ORDER BY columnList
• Order of the clauses cannot be changed.
• Only SELECT and FROM are mandatory

21
The GROUP BY Statement

• The GROUP BY statement is used in conjunction
  with the aggregate functions to group the
  result-set by one or more columns.
• Syntax

SELECT column_name, aggregate_function(column_name
) FROM table_name WHERE condition
GROUP BY column_name
22
Use of GROUP BY

• Use GROUP BY clause to get sub-totals.
• SELECT and GROUP BY closely integrated each item
  in SELECT list must be single-valued per group,
  and SELECT clause may only contain
• Column names in the group by clause
• Aggregate functions
• Constants
• Expression involving combinations of the above
• If WHERE is used with GROUP BY, WHERE is applied
  first, then groups are formed from rows
  satisfying condition.

23
Example 1 ( use of group by )
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Nora
2 2008/10/23 1600 Sara
3 2008/09/02 700 Nora
4 2008/09/03 300 Nora
5 2008/08/30 2000 Yara
6 2008/10/04 100 Sara

• Orders
•  find the total (total order) of each customer.
• use the GROUP BY statement to group the
  customers.
• SELECT Customer, SUM(OrderPrice)
• FROM Orders GROUP BY Customer

24
Example 1

• The result ( output )
• what happens if we omit the GROUP BY statement
• SELECT Customer,SUM(OrderPrice) FROM
  Orders
• The result

Customer SUM(OrderPrice)
Nora 2000
Sara 1700
Yara 2000
Customer SUM(OrderPrice)
Nora 5700
Sara 5700
Nora 5700
Nora 5700
Yara 5700
Sara 5700
25
Example 2

• List the quantity of each product ordered during
  Jan 2003.
• SELECT prodNo, sum(quantity)
• FROM orders
• WHERE ordDategt'01-jan-2003'
• AND ordDatelt'01-Feb-2003'
• GROUP BY prodNo

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
prodNo Sum ( quantity )
100 4
101 2
102 1
26
Example 3

•  return the minimum and maximum salaries for each
  department in the employees table
• SELECT deptNumber, MIN(salary), MAX (salary)
• FROM employees
• GROUP BY deptNumber
• ORDER BY deptNumber

department_id MIN(salary), MAX (salary)
D1 44000 60000
D2 45000 58000
27
Grouping Output from Queries
Example 4
Example 1 no grouping
SELECT count() FROM EMPLOYEE
COUNT() 5
Without group by COUNT() returns the number of
tuples in the table
28
Grouping Output from Queries
Example 2 group by SELECT deptNumber,
count() FROM EMPLOYEE GROUP
BY deptNumber ORDER BY
deptNumber
29
Use of HAVING

• HAVING clause is designed for use with GROUP BY
  to restrict groups that appear in final result
  table.
• Similar to WHERE, but WHERE filters individual
  rows whereas HAVING filters groups.
• Column names in HAVING clause must also appear in
  the GROUP BY list or be contained within an
  aggregate function.
• SYNTAX

SELECT column_name, aggregate_function(column_name
) FROM table_name WHERE column_name
operator value GROUP BY column_name
HAVING aggregate_function(column_nam
e) operator value
30
EXAMPLE 1
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Nora
2 2008/10/23 1600 Sara
3 2008/09/02 700 Nora
4 2008/09/03 300 Nora
5 2008/08/30 2000 Yara
6 2008/10/04 100 Sara

• find if any of the customers have a total order
  of less than 2000
• SELECT Customer,SUM(OrderPrice)
• FROM OrdersGROUP BY Customer HAVING
  SUM(OrderPrice)lt2000
• - Without Having

Customer SUM(OrderPrice)
Sara 1700
Customer SUM(OrderPrice)
Nora 2000
Sara 1700
Yara 2000
31
Example 2
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Nora
2 2008/10/23 1600 Sara
3 2008/09/02 700 Nora
4 2008/09/03 300 Nora
5 2008/08/30 2000 Yara
6 2008/10/04 100 Sara

• find if the customers Nora" or Yara" have a
  total order of more than 1500
• SELECT Customer,SUM(OrderPrice)
• FROM Orders WHERE CustomerNora' OR
  CustomerYara' GROUP BY Customer
  HAVING SUM(OrderPrice)gt1500

Customer SUM(OrderPrice)
Nora 2000
Yara 2000
32
Example 3
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

• List the product number and the quantity ordered
  for each product which has a total order of more
  than 2 in Jan 2003.
• SELECT prodNo, sum(quantity)
• FROM orders
• WHERE ordDategt'01-jan-2003' AND
  ordDatelt'01-Feb-2003'
• GROUP BY prodNo
• HAVING sum(quantity)gt2

prodNo sum(quantity)
100 4
33
Example 4

• SELECT deptNumber, count()
• FROM EMPLOYEE
• GROUP BY deptNumber
• HAVING count()gt2
• ORDER BY deptNumber

deptNumber count()
D1 3
34
(No Transcript)
35
Transaction Control

• There are following commands used to control
  transactions
• COMMIT to save the changes.
• Syntax
• ROLLBACK to rollback the changes.
• Syntax
• SAVEPOINT creates points within groups of
  transactions in which to ROLLBACK
• Syntax

COMMIT
ROLLBACK
SAVEPOINT SAVEPOINT_NAME
36
Savepoint

• The SAVEPOINT statement names and marks the
  current point in the processing of a transaction
• Example
• SQLgt INSERT INTO AUTHOR VALUES ('A11l', 'john',
  'garmany', '123-345-4567', '1234 here st',
  'denver', 'CO','90204', '9999')1 row created.
• SQLgt savepoint in_authorSavepoint created.
• SQLgt INSERT INTO BOOK_AUTHOR VALUES ('A111',
  'B130', .20) 1 row created.
• SQLgt savepoint in_book_authorSavepoint created.
• SQLgt INSERT INTO BOOK VALUES ('B130', 'P002',
  'easy oracle sql', 'miscellaneous', 9.95, 1000,
  15, 0, '', to_date ('02-20-2005','MM-DD-YYYY'))
  1 row created.
• SQLgt rollback to in_authorRollback complete.

37
Commit and Rollback

• If you want to make your update permanent, use
  COMMIT
• The COMMIT command is the command used to save
  changes invoked by a transaction to the database.
• The COMMIT statement erases any savepoints you
  marked since the last commit or rollback.
• You can see the changes when you query the tables
  you modified, but other users cannot see the
  changes until you COMMIT the work.
• If you change your mind or need to correct a
  mistake, you can use the ROLLBACK statement to
  roll back (undo) the changes.
• The ROLLBACK statement is the inverse
  of COMMIT statement. It undoes some or all
  database changes made during the current
  transaction.

38
Example
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

• SQLgt DELETE FROM CUSTOMER WHERE AGE 25
• 1 rows deleted
• SQLgt COMMIT
• SQLgt DELETE FROM CUSTOMER WHERE AGE 30
• 1 rows deleted
• SQLgt ROLLBACK
• SQLgt SELECT FROM CUSTOMER

custNo custName custSt custCity age
1 C1 Olaya St Jeddah 20
2 C2 Mains St Riyadh 30
4 C4 Mains Rd Dammam
5 C5 Mains Rd Riyadh
39
(No Transcript)
40
Inserting Data Using Queries

• You can insert the result of a query into a table
• For example, if you have a table Briscustomer
  which has the same structure as Customer, then
  you can use
• insert into Briscustomer
• select
• from customer
• where custcity Riyadh'

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
Riyadhustomer table
custNo custName custSt custCity age
2 C2 Mains St Riyadh 30
3 C3 Mains Rd Riyadh 25
5 C5 Mains Rd Riyadh
41
Create Table Using Queries

• You can create a new table using the result of a
  query.
• Example
• create table Briscustomer AS
• select custno, custName, custSt, age
• from customer where custcity Riyadh'
• will create a table Briscustomer which
  contains the custno, custName, custSt and age of
  customers from Riyadh.

42
SQL data loader

• For a table containing a large data set, INSERT
  command is not efficient to populate the table
• Oracle provides a data loader utility SQLLOADER
  which can be used to load data
• The data can be loaded from any text file and
  inserted into the database.
• SQLLoader reads a data file and a description of
  the data which is defined in the control file

43
SQL data loader

• runs in OS , not in SQLplus
• table must be created first
• A typical SQLLoader session takes as input a
  control file, which controls the behavior of
  SQLLoader, and one or more datafiles. The output
  of SQLLoader is an Oracle database (where the
  data is loaded), and a log file

44
Table names and Column names

• Table name can be prefixed with the owner name.
• eg, if table product is owned by user John, you
  can use
• SELECT FROM John.product
• Column names can be prefixed with table name,
• eg
• SELECT product.prodNo
• FROM product

45
Alias ( important note )

• SQL aliases are used to temporarily rename a
  table or a column heading.
• Syntax for Columns
• Syntax for Tables

SELECT column_name AS alias_nameFROM table_name
SELECT column_name(s)FROM table_name AS
alias_name
46
Alias ( important note )

• Columns Alias
• For example, you might wish to know how the
  combined total salary of all employees whose
  salary is above 25,000 / year.
• SELECT SUM(salary) AS "Total Salary" FROM
  employees WHERE salary gt 25000
• In this example, we've aliased the sum(salary)
  field as "Total Salary". As a result, "Total
  Salary" will display as the field name when the
  result set is returned.
• Table Alias
• SELECT o.OrderID, o.OrderDateFROM Orders AS o

47
Exercise

• create table count_null ( a number, b number )
• insert into count_null values ( 1, 5)
• insert into count_null values ( null, 7)
• insert into count_null values ( null, null)
• insert into count_null values ( 8, 2)
• select count(a) as "count_a_not_null", count(b)
  as "count_b_not_null", count() as "count_all
• from count_null

• Output

48
(No Transcript)
49
References

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