1. Relational Databases From Data to Information

2. Re-cap • Over the last 2 weeks we have looked at basic aggregation and simple sub-queries as methods to convert data into information • Basic aggregation requires grouping the data into related clusters and performing a range of actions on each cluster • count() - counts a number of rows • sum() - compute sum • avg() - compute average • min() - compute minimum • max() - compute maximum • Sub-Queries can be used to filter data using the results of another query.

3. Walk Through refresh • Which ITMB students have got higher in COMP1021 than the average mark of all remaining students in that subject? Build the query up. • Write SQL to determine the average mark for students studying COMP1021 but exclude the ITMB students • Write SQL to select the name of the ITMB students where the mark is greater than x • Replace the number x with the code from 1

4. Restricting the group Often when the data is grouped and aggregated we only want to display certain elements, for example: SELECT subjectid, count(studentid) FROM enrolled GROUP BY subjectid; Will display the numbers of students on each subject, modules which have less than 16 students are not financially viable so it would be useful to filter the results by count(studentid)

5. HAVING • The WHERE clause restricts the rows in the SQL query • The HAVING clause works on the groups returned, allowing filters to be applied to aggreate functions while the WHERE clause cannot filter out the aggregate. • Rows are grouped • The group function is applied to the group (sum, avg etc) • Only grouped data that matches the criteria in the HAVING clause is displayed.

6. HAVING • The HAVING clause allows filtering of the group by Group by subjectid having count(studentid) < 16 Subject A, 15 Subject B, 22 Subject A, 15 Subject C, 20 Subject D, 16

7. Using functions - Trim • The TRIM function will remove leading and trailing spaces/blanks from a string • LTRIM • RTRIM • Useful in cleansing data that contains leading and trailing blanks. • LTRIM(‘ Spock ‘) = ‘Spock ‘ • RTRIM(‘ Spock ‘) = ‘ Spock’

8. Using functions - Substr • SUBSTRING is a function which can be used to format the output OR filter the data • Substr(string,start,number) • Substr(‘legend of a seeker’,11,3) returns ‘a s’ 3 characters starting at position 11 • For example in the univ db the module number could be stripped from the subject id by extracting the 4 numbers from the attribute starting a position 5 • Substr(subjectid, 5,4) • COMP1001 becomes 1001 • Substr is useful when accessing a column that consists of meaningful subcomponents, such as a telephone number that contains area code, prefix and phone number body

9. Using functions - INSTR • It is often useful to find the position of a specific character such as * or ‘ ‘ etc, this can then be used to split a string based on this value (allows to split stuname into first and surname) • The INSTRfunction returns the location of a substring in a string. • INSTR(stringA, stringB, start,nth appearance) • Start and nth appearance are optional elements • INSTR(stuname, ‘ ‘) will return the location of the break between the names in stuname • INSTR(‘Relational Databases’,’a’,1,2) will return 9 as this is the location of the 2nd occurrence of the letter ‘a’

10. Combining functions • It is possible to combine the functions, such as using one function to identify the location of ‘ ‘ and then use this value as the starting point of a substring function • Where you are nesting the functions you should build up the functions one at a time to aid debugging.

11. Using function - Length • The Length function in SQL is used to get the length of a string. • Length(string) - finds the length of a string • Length(subjectid) - returns 8 as are subjectid’s are 8 characters long

12. Task Working with the person alongside you (or alone) carry out the following task • A list of student names have been generated for students who have been referred and the modules they have been referred in. Which functions can be used to do the following (may be more than one way possible): • Display the stuname as 2 separate fields • Select just the module number, not the preceding COMP string

13. Solution • Display the stuname as 2 separate fields • Find the position of a space and then substr to that position or from that position • What would happen if LTRIM and RTRIM were used? • Select just the module number, not the preceding COMP string • Substr the subjectid from position 5 for 4 places. Any other functions?

14. Solution cont select substr (stuname, 1, instr(stuname, ' ')) "FirstName", substr (stuname, length(substr (stuname, 1, instr(stuname, ' ')))) "LastName", substr (subjectid, 5, 4) "Code" from student natural join marks where mark < 40;

15. Data v information • Databases hold raw data, business and business systems require information and formatted information, this requires aggregation and formatted output. • The university requires students be collated by degree program (major), programs which have subjects running with less than 5 students on are to be flagged to the exec. • Working in pairs/alone draft the SQL code that will generate the above information

16. Draft solution Select major, subjectid, count(studentid) From enrolled natural join student Group by major, subjectid Having count(studentid) < 5