Transcript Distributed Databases

MIT5314: Database Applications
Slide # 1
More SQL
More SQL
(With a little more on Database Design)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 2
More SQL
 Let’s Start with some simple single table queries
 Assume the following Table:
Grades
StudID [PK]
Lastname
Firstname
Major
DOB
Quiz1
Quiz2
Quiz3
grade
 With the following data:
char(9)
char(30) NN
char(20) NN
char(4)
date NN
number(6,2)
number(6,2)
number(6,2)
char(1)
StudID
LastName
FirstName
Major
DOB
Quiz1
Quiz2
Quiz3
123456789
Hammett
Dashiell
CIS
08/12/82
72.50
74.00
62.00
234567890
Hansberry
Lorraine
ACCT
01/07/76
86.00
91.00
88.00
345678901
Vonnegut
Kurt
CIS
06/23/70
80.00
84.50
87.00
456789012
Tse
Lao
FIN
03/22/32
74.25
73.25
78.50
567890123
Allende
Isabel
CIS
09/06/68
84.00
82.00
87.00
678901234
Grisham
John
ACCT
07/13/80
57.00
52.00
55.00
Dr. Peeter Kirs
Grade
Fall, 2003
MIT5314: Database Applications
Slide # 3
More SQL
 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:
(I have changed the data in the tables)
Dr. Peeter Kirs
?? How ???
Fall, 2003
MIT5314: Database Applications
Slide # 4
More SQL
 We can also find the minimum column values:
 Or the Maximum column values:
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 5
More SQL
 We can also sum or average across tables:
What if we want each student’s average grade ???
 We could try and include student name:
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 6
More SQL
 We need to rewrite our command so that we get multiple
lines of output:
 Group Function Summary:
Group Function
SUM (column)
AVG (column)
MAX (column)
MIN (column)
COUNT (column)
Dr. Peeter Kirs
Usage
Find column sum (NULL Values ignored)
Find column Average (NULL Values ignored)
Find MAX column value (NULL Values ignored)
Find MIN column value (NULL Values ignored)
Count the number values in a column
(NULL Values ignored)
Fall, 2003
MIT5314: Database Applications
Slide # 7
More SQL
 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 ???
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 8
More SQL
 We can use it in calculations. For example, to calculate each
of our student’s age:
That makes no sense !!!
 Actually, it does:
 Those are our student ages in DAYS
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 9
More SQL
 To find out our student ages in years:
 Notice we have cleaned-up our output a little
(More on that in a little while)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 10
More SQL
 There are a number of Date Arithmetic procedures that
can be applied:
(Remember I made these slides on 03/07/2003)
Adds 24 days to a date
Subtracts 14 days from a date
Adds 48 hours to a date
Returns the number
of days between 2
dates
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 11
More SQL
 There are also a number of Date functions:
(The output has been formatted to make it fit the slide)
Gets
the no. of months between
2 dates
SQL> 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
SQL> select add_months(sysdate,8) from dual;
Adds calendar months to a date
ADD_MONTH
07-NOV-03
SQL> select next_day(sysdate,'MON') from dual;
Finds next occurrence of a day
NEXT DAY(SYSDATE,’MON’)
10-MAR-03
SQL> select last_day(sysdate) from dual;
Returns the last day of the month
LAST_DAY(
31-MAR-03
SQL> select round(to_date('10/12/2002','MM-DD-YYYY'),'Year') from dual;
ROUND(TO_
01-JAN-03
SQL> select trunc(sysdate,'month') from dual;
Round to the
nearest day, month or year
Truncate to nearest day, month or year
TRUNC(SYS
01-MAR-03
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 12
More SQL
 Let’s go over a little more on formatting output. Consider:
 There are a number of commands we need to consider
individually
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 13
More SQL
upper(trim(firstname)) (as well as)
upper(trim(lastname))
 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‘
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 14
More SQL
 The notation: || Is a concatenation operator
(it will join two strings together as one)
upper(trim(firstname)) || ‘ ‘ || upper(trim(lastname))
‘DASHIELL‘
+
‘‘
+ ‘ HAMMETT‘ = ‘DASHIELL HAMMETT‘
round((sysdate – DOB)/365.25,2)
(to two decimal pts. of precision)
319113.1930 - 311600.015 = 7513.1915/365.25 = 20.56997
= 20.57
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 15
More SQL
to_char(DOB, ‘fmMonth DD, YYYY’)
 Our old function
(Remember?)
‘August 12, 1982’
(the ‘fm’ is used to remove unnecessary spaces or zeros)
 A date format mask:
 There are a large number of numeric and date/time
formats available
(Which we are NOT going to go over here)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 16
More SQL
 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:
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 17
More SQL
 Grouping Data
 Grouping works only on segregated groups of data:
 We need to explicitly state how we wish to group:
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 18
More SQL
 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 ???)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 19
More SQL
 Subqueries
 Each of the tables contain the following data:
Table
Tuples
Columns
Bytes/Row
Total Bytes
Student
17
3
43
731
Enrollment
44
3
48
2112
Class
7
8
74
518
Course
6
3
28
168
Semester
3
4
27
81
 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)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 20
More SQL
 Subqueries
 If we used the subquery:
 We would save a lot of time and space
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 21
More SQL
 Subqueries
 Before going further, however, let’s review some subquery operators
Operator
IN
ALL
ANY
Use
Equal to any of the values in the list
Compare the given values to EVERY value in
returned by the subquery
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
< ANY
= ANY
> ANY
> ALL
< ALL
Dr. Peeter Kirs
Use
Less than the Maximum Value
Similar to IN
More than the minimum value
More than the Maximum value
Less than the Minimum value
Fall, 2003
MIT5314: Database Applications
Slide # 22
More SQL
 Subqueries
 Now let’s 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‘ ) )
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 23
More SQL
 Subqueries
 Our select will now choose only those records meeting the
restrictions:
Class
ClassID CourseID InstructID Semester
5000
100
123456789 100
5001
102
345678901 100
5002
100
123456789 100
Days
MWF
MWF
MWF
Times
10:30 AM
9:30 AM
9:30 AM
3
2
4
5
35
25
40
50
2
25
5003
5004
5005
101
100
102
123456789 100
123456789 102
456789012 102
TR
TR
TR
5006
103
234567890 102
TR
9:00 AM
10:30 AM
1:30 PM
10:30 AM
5004
100
123456789 102
TR
10:30 AM
Room Cap
1
40
2
50
1
30
(There is only 1)
 And only the classid (5004) is returned
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 24
More SQL
 Subqueries
 The next inner subquery produces a list of sudents in class 5004
AND received an ‘A’ in the class:
Classid
StudentID Grade
•••
5004
5004
•••
109876543
223344556
•••
A
B
5004
5004
5004
5004
5004
5004
321098765
432109876
543210987
556677889
765432109
987654321
D
C
B
B
A
C
•••
•••
•••
109876543
765432109
(Only the StudentID list is returned)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 25
More SQL
 Subqueries
 Our outermost query produces the list of student names based on
the list returned from the subquery on class:
StudentID
StudentName
Major
•••
109876543
•••
•••
•••
Abdul-Jabbar, Kareem
•••
•••
•••
102
•••
•••
765432109
Lopez, Jennifer
100
StudentName
Abdul-Jabbar, Kareem
Lopez, Jennifer
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 26
More SQL
 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
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 27
More SQL
 Oracle Objects
 We have already seen some Oracle Objects:
 Tables
 Views
 There are some others:
 Sequence
 Synonym
 Index
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 28
More SQL
 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
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 29
More SQL
 Sequence
 The next time that we add a class, we can have it autonumbered:
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 30
More SQL
 Sequence
 Some of the options available include:
Option
INCREMENT BY n
START WITH s
MAXVALUE x
NOMAXVALUE
MINVALUE m
NOMINVALUE
CYCLE
NOCYCLE
CACHE c
Meaning
The increment value for number generation is n
Start incrementing with the number s
The maximum value allowed
1027 = 1,000,000,000,000,000,000,000,000,000
The minimum value allowed
1 if a ascending sequence and -1026 if descending
Sequence continues after reaching Max Value
No continuation after reaching Max Value
Oracle generates c numbers in advance and stores
them in advance for improved system performance
NOCACHE
The system does not generate numbers in advance
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 31
More SQL
 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:
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 32
More SQL
 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)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 33
More SQL
 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
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 34
More SQL
Dr. Peeter Kirs
Fall, 2003