SQL Queries Yet Another Set of Query Features

SQL Queries Yet Another Set of Query Features Creative Commons License – Curt Hill

Features to Consider • • Arithmetic operations Functions Set operations Nested queries Creative Commons License – Curt Hill

Arithmetic • Numeric fields and constants may be part of an arithmetic expression – In Where or Select • These include the standard: * / + • My. SQL and SQL Server accept the % as modulo • Standard precedence exists and parentheses may override • There also other many functions Creative Commons License – Curt Hill

Arithmetic Example • Considering the age at which a faculty member started: select f_name, (f_age - f_years) "Starting age" from faculty where f_age - f_years > 40 • The parentheses clarify the statement but are not needed Creative Commons License – Curt Hill

Functions • There a number of functions that may be used anywhere a field name be be used • The classifications include: – Mathematical functions – String functions Creative Commons License – Curt Hill

Mathematical • ABS – Absolute value – Takes one numeric parameter – Usually a field • Round – See next screen • Trig functions – Sin, cos, tan are available • Others – Each DBMS may have others as well Creative Commons License – Curt Hill

Round • The first parameter is a numeric expression – This is the only required parameter • The second is the number of digits to right of decimal – This one is optional • Thus: Round(s_balance) Round(avg(s_balance), 2) • Oracle has a Trunc, SQL Server Creative Commons License – Curt Hill Floor

String functions • Substr(field, start, length) – SQL Server – Sub. String – Substr(f_name, 1, 4) gives first four characters of the name • Length – Gives the length of a character field – Oracle gives field width for fixed length fields and actual length for variable length fields – My. SQL disregards trailing blanks for fixed length – SQL Server: Len Creative Commons License – Curt Hill

More String Functions • Instr – Find a string in an expression – Returns position – Instr(crs_title, ’Econ’)>0 is true for titles containing this string – Oracle and My. SQL • Lower and Upper – Convert letters to lower or upper case Creative Commons License – Curt Hill

Word Operators for Where • Between – Allows use of a range • Null – Checks for missing value • Like – A partial rather than full match • In – Set Membership Creative Commons License – Curt Hill

Between • Allows a range: Where s Between 25 And 50 • The range is inclusive • This is the same as: Where s >= 25 AND s <= 50 • The values in the range could involve operators or other fields • This can be negated as well Creative Commons License – Curt Hill

Example Between • Find the mid-career faculty: select f_name, f_age from faculty where f_age between 30 and 50 • This produces 13 rows Creative Commons License – Curt Hill

Negated Between • Find those outside a range select f_name, f_age from faculty where f_age not between 30 and 50 • Or select f_name, f_age from faculty where not f_age between 30 and 50 • My. SQL must have parentheses around range in last one Creative Commons License – Curt Hill

Null • Special value that means there is no value for this field • Not the same as: – Zero – Blank – Empty string • Usually obtained by an Insert statement with fewer values than the maximum • Possible to require a column have a value • A Primary Key may not be null Creative Commons License – Curt Hill

Example • Is there a value? select * from students where s_address is Null • The Not may also be applied before Null or before s_address – My. SQL requires: Where not (s_address is Null) in latter case Creative Commons License – Curt Hill

Like • Expression Like Pattern • Pattern allows two wild cards – % Any number of any character – _ Any one character – Most other characters appear as themselves – SQL Server also allows – [ ] contains allowables – [^ ] any but these Creative Commons License – Curt Hill

Example • This will find any course with an I in title: select * from course where crs_title like '% I%' • Results in 5 rows • Leaving out the trailing % gives only 3 rows • This should be examined in the demonstration! Creative Commons License – Curt Hill

Finally on Like • The Not may precede the Like to negate the whole condition • There is a trailing clause that allows selection of an escape character: crs_title like ‘%_%’ escape ‘’ • Any character may be in the final string • It is then used to precede one of the wild cards Creative Commons License – Curt Hill

Set Membership • • • In is reserved word Produces a boolean S in (1, 4, 5, 10) It is true if s is any of the items Much easier than many equal clauses connected with Ors • The set items do not have to be numeric Creative Commons License – Curt Hill

Example of In • Find faculty in Math, CIS or MGMT: select * from faculty where upper(f_dept) in ('MATH', 'CIS', 'MGMT') • Results in 6 rows • The Not may be applied before the In or before the Upper – My. SQL requires parentheses if before upper Creative Commons License – Curt Hill

Sub-Queries or Nested Queries • An in needs a set – Often a constant parenthesized set • A query produces a set • A nested query is a full query inside the Where clause of a Select • Generally the sub-query produces a set of tuples with a single field used in an IN • Query is inside parentheses Creative Commons License – Curt Hill

Example Sub-query • MS Divisional classes • Select * From course Where crs_dept IN (Select dp_acronym From depart Where dp_division = 'S') Creative Commons License – Curt Hill

Sub-Query Notes • The sub-query is never seen – It is resource for the outer query • The sub-query may not use any of the following clauses: – Into, order by, group by – Why is there need to use these? • Besides IN there is also – All - All the sub-query meets some test • The for all ( ) – Exists – One meets the test – These come from the relational calculus Creative Commons License – Curt Hill

Another example • Students who got only As or Bs in CS classes select s_name, s_id from students where 80 <= ALL( Select g_score From grades WHERE s_id = g_naid AND g_dept = 'CS') • The problem is that this gives way too many: 67 Creative Commons License – Curt Hill

Problem: Nulls • Any student who took no classes in CS gets an empty table • The comparison becomes 80 <= Null • The DBMS does not know what to do with this so calls it true – Which is not what we want • How do we fix this? Creative Commons License – Curt Hill

Use an exists • Disallow empty sub-query tables select s_name, s_id from students where 80 <= ALL( Select g_score From grades WHERE s_id = g_naid AND g_dept = 'CS') AND Exists (Select g_score From grades WHERE s_id = g_naid AND g_dept = 'CS') Creative Commons License – Curt Hill

What’s wrong with that? • Lots of coding • Will the two sub-queries cost more than one? • If the query optimizer is good then no – It will recognize that the two subqueries are the same and only do once • Otherwise yes • There may be other ways as well Creative Commons License – Curt Hill

Relationship of sub-query to main query • The scope of names in the parentheses is just the parentheses • Nothing but the set produced in the sub-query may be used outside • Things in the outside query may be used inside • The nested query may be independent • It may be correlated Creative Commons License – Curt Hill

Additionally • A sub-query can also be used in the From clause • It generates a new temporary table • This can be used as if it were an existing table • This is similar to using join in the FROM • See next for an example Creative Commons License – Curt Hill

Example select f_name, f_degree, f_years, f_age from (select * from faculty where f_age > 30) where f_years > 10 order by f_degree Unlike the Where sub-queries this does not give us much extra capabilities Creative Commons License – Curt Hill

Examples • Independent • It has no values that are related to the main query • Previous example of finding MS Divisional classes • Correlated • The main query injects values into the sub-query • Previous example of finding students who got an A or B in a CS class Creative Commons License – Curt Hill