More SQL

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

2. Grades StudID [PK] char(9) Lastname char(30) NN Firstname char(20) NN Major char(4) DOB date NN Quiz1 number(6,2) Quiz2 number(6,2) Quiz3 number(6,2) grade char(1) StudID LastName FirstName Major DOB Quiz1 Quiz2 Quiz3 Grade 345678901 567890123 456789012 678901234 234567890 123456789 Hammett Grisham Allende Tse Hansberry Vonnegut Dashiell Lorraine John Lao Kurt Isabel CIS CIS FIN ACCT CIS ACCT 03/22/32 09/06/68 01/07/76 06/23/70 08/12/82 07/13/80 57.00 72.50 84.00 80.00 86.00 74.25 84.50 82.00 74.00 52.00 91.00 73.25 55.00 78.50 87.00 88.00 62.00 87.00 • Let’s 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 student’s 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 student’s 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. 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 • There are a number of Date Arithmetic procedures that can be applied: (Remember I made these slides on 03/07/2003)

11. Gets the no. of months between 2 dates Adds calendar months to a date Finds next occurrence of a day Returns the last day of the month Truncate to nearest day, month or year Round to the nearest day, month or year • There are also a number of Date functions: (The output has been formatted to make it fit the slide) 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; ADD_MONTH 07-NOV-03 SQL> select next_day(sysdate,'MON') from dual; NEXT DAY(SYSDATE,’MON’) 10-MAR-03 SQL> select last_day(sysdate) from dual; 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; TRUNC(SYS 01-MAR-03

12. Let’s 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. 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 • 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, let’s 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 < ANY Less than the Maximum Value = ANY Similar to IN > ANY More than the minimum value > ALL More than the Maximum value < ALL Less than the Minimum value

22. 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‘ ) )

23. Class ClassID CourseID InstructID Semester Days Times Room Cap 100 MWF 10:30 AM 1 40 5001 102 345678901 100 MWF 9:30 AM 2 50 5002 100 123456789 100 MWF 9:30 AM 1 30 5003 101 123456789 100 TR 9:00 AM 3 35 5000 100 123456789 123456789 102 TR 10:30 AM 2 25 456789012 102 TR 1:30 PM 4 40 10:30 AM 5 50 234567890 102 TR 5004 5006 5005 5004 102 100 100 103 123456789 102 TR 10:30 AM 2 25 • Subqueries • Our select will now choose only those records meeting the restrictions: (There is only 1) • And only the classid (5004) is returned

24. ••• B A D ••• B B Grade C C A 109876543 765432109 5004 Classid ••• 5004 5004 5004 5004 5004 ••• 5004 5004 432109876 ••• StudentID 987654321 ••• 109876543 556677889 223344556 543210987 321098765 765432109 • 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. StudentID StudentName Major ••• ••• ••• ••• ••• ••• ••• ••• ••• 109876543 765432109 Abdul-Jabbar, Kareem Lopez, Jennifer 102 100 • 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. PL/SQL

35. Procedural Language for SQL (PL/SQL) is an extension of Oracle SQL • The basic intent of PL/SQL is • increase the expressiveness of SQL • process query results in a tuple-oriented way • develop modular database application programs • reuse program code, and • reduce the cost for maintaining and changing applications. • The basic construct of PL/SQL is a block • constants and variables can be declared, and variables can be used to store query results. Statements in a PL/SQL block include: • SQL statements • Control structures (loops) • Condition statements (if-then-else) • Exception handling • Calls of other PL/SQL blocks.

36. Each block builds a (named) program unit, and blocks can be nested. • The structure of a PL/SQL looks as follows: (brackets [ ] enclose optional parts) [<Block header>] [declare <Constants> <Variables> <Cursors> <User defined exceptions>] begin <PL/SQL statements> [exception <Exception handling>] end;

37. Consider the following simple code: Needed to display output Our local variable of data type DATE Get today’s date and store it in our variable Display the contents of the variable Program Output

38. Numeric operations in PL/SQL:

39. Bind Variables: • Host variables: • Declared in the SQL “Host Environment” • Remain active for the length of the SQL Session • Can be displayed with the SQL Print Command • Can only be accessed in the program when prefaced with a colon (:)

40. Interactive Input:

41. Control Structures: IF-THEN:

42. Control Structures: IF-THEN-ELSE:

43. Control Structures: IF-THEN-ELSIF: (Note Spelling)

44. Control Structures: Basic Loop:

45. Control Structures: While:

46. Control Structures: For:

47. SQL in PL: • We must be careful about single record/field queries and multiple return queries

48. Single Return Queries: The data type applied to field studentname is automatically applied

49. Multiple Record Queries:

50. Remember our problem about calculating a student grade? • Variable Declarations: /* This program calculates a grade */ declare cursor studentgrade is select lastname, firstname, quiz1, quiz2, quiz3 from grades; studentlastname grades.lastname%type; studentfirstname grades.firstname%type; q1 grades.quiz1%type; q2 grades.quiz2%type; q3 grades.quiz3%type; average number; sgrade grades.grade%type; nblanks number; blanks char(5);