Distributed Databases

1
More SQL
(With a little more on Database Design)
2

• Lets Start with some simple single table queries

• Assume the following Table

• With the following data

3

• Group Functions are those which perform an
  operation on a column from a single table

• One of the simplest is the count() function

• Count() can also be applied to specific columns

?? How ???
(I have changed the data in the tables)
4

• We can also find the minimum column values

• Or the Maximum column values

5

• We can also sum or average across tables

What if we want each students average grade ???

• We could try and include student name

6

• We need to rewrite our command so that we get
  multiple lines of output

• Group Function Summary

Group Function
Usage
SUM (column)
Find column sum (NULL Values ignored)
AVG (column)
Find column Average (NULL Values ignored)
MAX (column)
Find MAX column value (NULL Values ignored)
MIN (column)
Find MIN column value (NULL Values ignored)
COUNT (column)
Count the number values in a column (NULL Values
ignored)
7

• Another useful function is SYSDATE, which returns
  the present date or time

(DUAL is a table owned by user SYS and available
to all users)
?? How is that useful ???
8

• We can use it in calculations. For example, to
  calculate each of our students age

That makes no sense !!!

• Actually, it does

• Those are our student ages in DAYS

9

• To find out our student ages in years

• Notice we have cleaned-up our output a little

(More on that in a little while)
10

• There are a number of Date Arithmetic procedures
  that can be applied

(Remember I made these slides on 03/07/2003)
11

• There are also a number of Date functions

(The output has been formatted to make it fit the
slide)
SQLgt select months_between(sysdate,(to_date('10/12
/2002','MM-DD-YYYY'))) from dual MONTHS_BETWEEN(
SYSDATE,(TO_DATE('10/12/2002','MM-DD-YYYY')))
4.85797006 SQLgt select
add_months(sysdate,8) from dual ADD_MONTH


07-NOV-03

SQLgt select next_day(sysdate,'MON') from
dual NEXT DAY(SYSDATE,MON)

10-MAR-03
SQLgt select
last_day(sysdate) from dual LAST_DAY(

31-MAR-03
SQLgt select
round(to_date('10/12/2002','MM-DD-YYYY'),'Year')
from dual ROUND(TO_
01-JAN-03

SQLgt select trunc(sysdate,'month')
from dual TRUNC(SYS 01-MAR-03


12

• Lets go over a little more on formatting output.
  Consider

• There are a number of commands we need to
  consider individually

13
upper(trim(firstname))
upper(trim(lastname))
(as well as)

• Put whatever string is passed into upper case

• Remove all leading and trailing spaces from the
  string passed

upper(trim(Dashiell ))
(Stored on a field of 20 characters)
(Returned as)
upper(Dashiell)
(Returned as)
DASHIELL
14

• The notation

Is a concatenation operator
(it will join two strings together as one)
upper(trim(firstname))
upper(trim(lastname))

HAMMETT
DASHIELL HAMMETT
DASHIELL
round((sysdate DOB)/365.25,2)
(to two decimal pts. of precision)
319113.1930
- 311600.015 7513.1915/365.25 20.56997
20.57
15
to_char(DOB, fmMonth DD, YYYY)

• Our old function

(Remember?)
August 12, 1982

• A date format mask

(the fm is used to remove unnecessary spaces or
zeros)

• There are a large number of numeric and date/time
  formats available

(Which we are NOT going to go over here)
16

• Grouping Data

• Rows in a table can be divided into different
  groups and treated separately

• A HAVING clause, similar to a WHERE clause, can
  be used in the GROUP BY clause

17

• Grouping Data

• Grouping works only on segregated groups of data

• We need to explicitly state how we wish to group

18

• Subqueries

• Suppose we wished to get a list of all students
  who toke DATABASE in Spring 2003 and received an
  A

• As we know, the result is the product of all of
  the tables

(Remember our discussion on Query Optimization
???)
19

• Subqueries

• Subqueries

• Each of the tables contain the following data

• The product of the tables is

17 44 7 6 3 15,708 Rows
and 3 3 8 3 4 21 Columns
For a total of 15708 21 (43 48 74 28
27)
15708 21 220 72,570,960 Bytes
(And this is a simple example)
20

• Subqueries

• If we used the subquery

• We would save a lot of time and space

21

• Subqueries

• Before going further, however, lets review some
  subquery operators

Operator
Use
IN
Equal to any of the values in the list
ALL
Compare the given values to EVERY value in
returned by the subquery
ANY
Compare the given values to EACH value in
returned by the subquery

• There are also a number of meanings for subquery
  operators when used in conjunction with the
  standard relational operators

Operator
Use
lt ANY
Less than the Maximum Value
ANY
Similar to IN
gt ANY
More than the minimum value
gt ALL
More than the Maximum value
lt ALL
Less than the Minimum value
22

• Subqueries

• Now lets analyze the results of our subquery,
  starting with the innermost queries

( Select courseID from course where coursename
Database)
Returns a 4-byte Integer Value
(CourseID 100)
( Select SemID from Semester where Semname
Spring 2003)
Returns a 4-byte Integer Value
(SemID 102)

• Remember, these subqueries are nested in the
  query

select classid from class where courseid IN (
select courseid from course where coursename
'Database ) and semester ( select semid from
semester where semname 'Spring 2003 ) )
23

• Subqueries

• Our select will now choose only those records
  meeting the restrictions

(There is only 1)

• And only the classid (5004) is returned

24

• Subqueries

• The next inner subquery produces a list of
  sudents in class 5004 AND received an A in the
  class

(Only the StudentID list is returned)
25

• Subqueries

• Our outermost query produces the list of student
  names based on the list returned from the
  subquery on class

StudentName
Abdul-Jabbar, Kareem
Lopez, Jennifer
26

• Subqueries

??? How Much of a RAM/Storage Savings is there ???

• We only work with single tables

• The largest table is Class

• 44 Rows
• 3 Columns/Attributes/Fields
• 48 Bytes per record
• 2112 total Bytes

• Which is the greatest amount of RAM required at
  any time

27

• Oracle Objects

• We have already seen some Oracle Objects

• Tables
• Views

• There are some others

• Sequence
• Synonym
• Index

28

• Sequence

• Sequences can be created for autonumbering of
  records

• In this case, just as with our views and
  constraints, we have added an object to our
  repository called class_classid_seq

29

• Sequence

• The next time that we add a class, we can have it
  autonumbered

30

• Sequence

• Some of the options available include

Option
Meaning
INCREMENT BY n
The increment value for number generation is n
START WITH s
Start incrementing with the number s
MAXVALUE x
The maximum value allowed
NOMAXVALUE
1027 1,000,000,000,000,000,000,000,000,000
MINVALUE m
The minimum value allowed
NOMINVALUE
1 if a ascending sequence and -1026 if descending
CYCLE
Sequence continues after reaching Max Value
NOCYCLE
No continuation after reaching Max Value
CACHE c
Oracle generates c numbers in advance and stores
them in advance for improved system performance
NOCACHE
The system does not generate numbers in advance
31

• Synonym

• Sometimes, object names can become very long
  (especially we follow standard naming conventions)

• Synonyms are added to the repository to shorten
  and clarify names

32

• Indices

• In order to speed processing, an index can be
  created

• When searching for a record, Oracle uses the
  index instead of scanning the entire database

(Implicit Indices are created when Primary Keys
or Unique constraints are established)
33

• Locking Records

• When a user enters a select command, the rows
  selected are not locked

• If a user wants to view AND lock the rows

• The NOWAIT clause tells any other users accessing
  the record that it is locked

34
(No Transcript)