Comparison of Oracle and Microsoft Access SQL

Oracle SQL>

select avg(alcohol_24hr) "Avg drunk : Clifton+Redland"
from VICTIM
where live_district=’Clifton’ or live_district=’Redland’;

Access

• Note that the grid will automatically change the 2 criteria on separate columns (since it is the same column) to 1 criterion on live_district as in the former query, but the SQL ends up with more round brackets since it puts them round each criteria column.

  field	avg drunk:Clifton+Redland	live_district
  total	avg	where
  sort
  show	X
  criteria		"Clifton" or "Redland"
  or

  
  SELECT Avg([alcohol_24hr]) AS [Avg drunk Clifton+Redland]
  FROM victim
  WHERE ((victim.live_district="Clifton" Or victim.live_district="Redland"))
  WITH OWNERACCESS OPTION;
  
  

field	avg drunk:Clifton+Redland	live_district	live_district
total	avg	where	where
sort
show	X
criteria		"Clifton"
or			"Redland"


SELECT Avg([alcohol_24hr]) AS [Avg drunk Clifton+Redland]
FROM victim
WHERE ((victim.live_district="Clifton")) OR ((victim.live_district="redland"))
WITH OWNERACCESS OPTION;

Matching a value in a list

Q7: Find the average number of alcohol units consumed by victims living in Clifton and Redland. This uses a different syntax to produce the same result as the previous query.

• The in operator gives a list of possible values for a field. In this example, it is shorter to use in than the or expression used in the previous example.
• If the alias column name is edited to be longer than the limit (Oracle 30 characters, Access 64) an error message is displayed. The approximate site of the error is indicated by an asterisk (*).

Oracle SQL>

select avg(alcohol_24hr) "Avg drunk : Clifton and Redland"
from VICTIM
where live_district in (’Clifton’,’Redland’);


select avg(alcohol_24hr) "Avg drunk : . . .
                         *
ERROR at line 1: ORA-0972:  identifier  . . .

Access

• get error message invalid bracketing of name if the heading is too large (64 characters which is more than the 30 allowed by Oracle)
• it does not matter if single or double quotes are used in Access SQL.
• Oracle uses between single quotes in SQL. If there is a special significance in the alias name then double quotes are used for example select avg(alcohol_24hr) AVERAGE will still internally identify the fieldname AVERAGE thdland live_district totalavgwhere sort   showX   criteria in ("Clifton","Redland") or  
  SELECT DISTINCTROW Avg([alcohol_24hr]) AS [Avg Clifton,Redland]
  FROM victim
  WHERE ((victim.live_district In ("Clifton","Redland")))
  WITH OWNERACCESS OPTION;
  
  

Specifying a range of values

Q8: Find how many victims consumed between 5 and 10 alcohol units.

• The between operator specifies a range of values (numeric, date or character). In the example, ‘alcohol_24hr between 5 and 10’ could have been written using less than and greater than signs, that is ‘alcohol_24hr>4 and alcohol_24hr<11’.




Oracle SQL>

select count(alcohol_24hr) "Number drinking 5-10 units"
from VICTIM
where alcohol_24hr between 5 and 10;


Access

using aggregate function in a field

field	number drinking 5-10 units:count(alcohol_24hr)	alcohol_24hr
sort
show	X
criteria		between 5 and 10
or


SELECT Count([alcohol_24hr]) AS [number drinking 5-10 units]
FROM victim
WHERE ((victim.alcohol_24hr Between 5 And 10))
WITH OWNERACCESS OPTION;



or using View/Totals

• The title comes from the name of the query and can not be switched off
• The heading of each field is from the text in front of the colon or the name of the field and similarly can not be turned off because it is used to identify the name of the field rather than its heading

  field	number drinking 5-10 units:alcohol_24hr	alcohol_24hr
  total	count	where
  sort
  show	X
  criteria		between 5 and 10
  or

  
  SELECT Count(victim.alcohol_24hr) AS [number drinking 5-10 units]
  FROM victim
  WHERE ((victim.alcohol_24hr Between 5 And 10))
  WITH OWNERACCESS OPTION;
  
  
  

Arithmetic expressions

Q9: Find how many victims claim to spend more than 75% of their weekly income on alcohol (assuming the cost of a unit of alcohol is 50p).
• Arithmetic expressions can be used in the select as well as the where clause.

Oracle

• To save SQL*Plus commands together with the SQL command a different buffer from the default one must be used.
• The save command saves the contents of the buffer in ‘Q9.SQL’.
• The commands are run from the file using the start command.
• The SQL*Plus ttitle command defines a heading at the top of every page (which may be longer than the 30 characters allowed by aliases). The headings are left-aligned in this example. They are centered by default.
• The SQL*Plus set command sets various options. set heading off turns off the default column headings to avoid having two headings.
• Remember to remove the page heading and restore the column headings before running a new query.




Oracle SQL>

ttitle left "Number spending>75% of income on alcohol"
set heading off
select count(vno)
from VICTIM
where (alcohol_wk*.50/income)>75/100 and income>0;
set heading on
ttitle off
save q9
start q9

Access

using aggregate function in the field
• Access uses poperties to set the format of output but not headings
• The title comes from the name of the query and can not be switched off
• The heading of each field is from the text in front of the colon or the name of the field and similarly can not be turned off because it is used to identify the name of the field rather than its heading

  field	number spending more than 75% of income on alcohol:alcohol_24hr	income	alcohol_24hr*.5/income
  sort
  show	X
  criteria		>0	>.75
  or

  
  SELECT Count([vno]) AS [number spending more than 75% of income on alcohol]
  FROM victim
  WHERE ((victim.income>0) AND (([alcohol_wk]*0.5/[income])>0.75))
  WITH OWNERACCESS OPTION;
  
  
  
or using View/Totals


field	number spending more than 75% of income on alcohol:alcohol_24hr	income	alcohol_24hr*.5/income
total	count	where	where
sort
show	X
criteria		>0	>.75
or


SELECT Count(victim.vno) AS [number spending more than 75% of income on alcohol]
FROM victim
WHERE ((victim.income>0) AND (([alcohol_wk]*0.5/[income])>0.75))
WITH OWNERACCESS OPTION;




Character matching

Q10: Find the districts whose names begin with ‘St’ in which people were assaulted.

• like allows comparisons between strings using wild card characters.

  Oracle

  • uses a percent sign (%) to signify any number of any characters
  • uses an underline character signifies any single character.
  • The unique or distinct operator makes sure that duplicate values are not printed.

  
  

  Oracle SQL>

  select unique assault_district
  from VICTIM
  where assault_district like ’St%’;
  

  Access

  • Access wild card is * for any number of characters and offers a good range of wild card characters

    Single character      "a?a"
    Single digit          "a#a"
    Range of characters   "[a-z]"
    Outside a range       "[!a-z]"
    Not a digit           "[!0-9]"
    

  • Access changes St* to like "St*"
  • to get a non-duplicated list of the districts ie without the district being shown for each record, there are two ways:
    • press the SQL button and replace DISTINCTROW by DISTINCT
    • look at the properties of the query by clicking in the grey of the table area and set unique values to yes

    field	assault_district
    sort
    show	X
    criteria	St*
    or

    
    SELECT DISTINCT victim.assault_district
    FROM victim
    WHERE ((victim.assault_district Like "St*"))
    WITH OWNERACCESS OPTION;
    
    

Q11: Find districts whose names begin with ‘st’ regardless of case.

Oracle

• The upper function converts values to upper case. This is useful for data which have not been stored in a consistent form.




Oracle SQL>

select unique assault_district
from VICTIM
where upper(assault_district) like ’ST%’;

Access

• Access does not distinguish between upper and lower case so the ucase function has no effect here in the criteria (so could test for either ST* or St*), just prints out the name in upper case.

  field	assault_district	ucase(assault_district)
  sort
  show	X
  criteria		ST*
  or

  
  SELECT DISTINCT victim.assault_district
  FROM victim
  WHERE (((UCase([assault_district])) Like "ST*"))
  WITH OWNERACCESS OPTION;
  
  

Ordering and grouping information

Q12: Find how many assaults took place in each district.

• The group by clause works in association with the aggregate functions (count in this case) to divide all the victims into groups based on the district in which they were assaulted. The function count is then applied to the rows in each group.
• The order by clause orders output, in ascending order by default. If the column to be ordered has an alias, the alias can be given in the order by clause.
• The order by clause must follow the group by clause.

Oracle SQL>

select assault_district, count(assault_district)
from VICTIM
group by assault_district
order by assault_district;


Access

• UseView/Totals option to group data. Note the appearance of the total row in the grid.
• note that the order clause has been added when the sort field has been chosen
• order is always the last clause of a select statement (unless the with option has been used)

  field	assault_district	assault_district>
  total	group by	count
  sort	asc
  show	X	X
  criteria
  or

  
  SELECT victim.assault_district, Count(victim.assault_district) AS CountOfassault_district
  FROM victim
  GROUP BY victim.assault_district
  ORDER BY victim.assault_district
  WITH OWNERACCESS OPTION;
  
  

Q13: Find how many assaults took place in each district. Display them in descending order of number of assaults.

• The order by clause is changed to order on the count, that is the second selected column. The query could be created by editing the previous query (typing the line number and new line), then giving the run command.
• The asc and desc operators specify whether the ordering is ascending or descending. The default is asc.
• If the ordering is to be done on more than one column, the column specifications are separated by commas.

Oracle SQL>

select assault_district, count(assault_district)
from VICTIM
group by assault_district
order by count(assault_district) desc, assault_district asc;


Access

• to force ascending on count first, add asssault_district as an expression but do not show it

  field	assault_district	countofassault_district:assault_district	assault_district
  total	group by	count	expression
  sort		descending	ascending
  show	X	X
  criteria
  or

  
  SELECT victim.assault_district, Count(victim.assault_district) AS CountOfassault_district
  FROM victim
  GROUP BY victim.assault_district
  ORDER BY Count(victim.assault_district) DESC , victim.assault_district
  WITH OWNERACCESS OPTION;
  
  

Q14: Find the districts in which more than 20 people had been assaulted.

• The having subclause of the group clause limits the number of groups used.

Oracle SQL>

select assault_district "District",
count(assault_district) "Over 20 assaulted"
from VICTIM
group by assault_district having count(assault_district)>20
order by assault_district;


Access

• the criteria works on the aggregate value not the individual record value hence adds the having clause rather than the where clause
• having clause always appears after the group clause and cannot be used without a group by clause

  field	assault_district	over 20:assault_district
  total	group by	count
  sort	ascending
  show	X	X
  criteria		>20
  or

  
  SELECT victim.assault_district, Count(victim.assault_district) AS [over 20 assaulted]
  FROM victim
  GROUP BY victim.assault_district
  HAVING (((Count(victim.assault_district))>20))
  ORDER BY victim.assault_district
  WITH OWNERACCESS OPTION;
  
  

Decoding output

Q15: Find how many of each sex had been assaulted.

• Use a function to expand the values ‘m’ and ‘f’ to ‘male’ and ‘female’ in the output.

  Oracle

  • The decode function changes the appearance of the selected data. decode requires the name of the column or an expression, followed by pairs of search criteria, and the value to be returned. Optionally a default value can be given that is returned if none of the criteria are satisfied. (The null value is returned if no default is given.) In the example, if sex is ‘m’, ‘male’ is returned; if sex is ‘f’, ‘female’ is returned; if sex is neither ‘m’ or ‘f’, the value ‘unknown’ is returned.

  Oracle SQL>

  select decode(sex, ’m’, ’male’, ’f’,
  ’female’, ’unknown’) "Sex",
  count(sex) "Number"
  from VICTIM
  group by sex;
  
  Sex Number
  ------ -------
  female 84
  male 455
  

  Access

  • Note that iif can only be used to give 2 different values unlike decode.
  • isnull function to test if a value is null eg
    
    iif(isnull(x),'nothing','something')

    field	gender:iif(sex="m","male","female")	sex
    total	group by	count
    sort
    show	X	X
    criteria
    or

    
    SELECT IIf([sex]="m","male","female") AS gender, Count(victim.sex) AS [number]
    FROM victim
    GROUP BY IIf([sex]="m","male","female")
    WITH OWNERACCESS OPTION;
    
    

  Q16: Find, for each district in which assaults took place, the average number of alcohol units consumed by each sex.

  Oracle

  • In the example below, the clause:

    decode(sex, ’m’, alcohol_24hr, null)

    selects the value of alcohol_24hr if sex = ‘m’; otherwise the value is set to the null value and is not included in the calculation of the average.

  • The column statement specifies the format for columns, in this case to give 2 decimal places. Note that the column aliases are used to specify the columns.

  Oracle SQL>

  ttitle "Average alcohol consumed"
  column Male format 99.99
  column Female format 99.99
  select assault_district,
  avg(decode(sex, ’m’, alcohol_24hr, null)) "Male",
  avg(decode(sex, ’f’, alcohol_24hr, null)) "Female"
  from VICTIM
  group by assault_district;
  
  Thu Nov 17 page 1
  Average alcohol consumed
  
  ASSAULT_DISTRIC Male Female
  --------------- ------ ------
  os Bristol 9.19 3.29
  unknown .50
  Avon 24.00
  Bedminster 10.80
  Bishopston 16.00
  Bishopsworth 4.63 .00
  Brislington 6.89 2.00
  Cabot 9.31 2.67
  .
  .
  ttitle off
  clear column

  Access

  • must use a different column header to the name of a field so use gender rather than sex
  • column properties are set by highlighting the appropriate field by clicking on the grey bar on the top of the field. Set number format to fixed number with 2 decimal places
    

    field	assault_district	male:Avg(IIf([sex]='m',[alcohol_24hr],Null))	female:Avg(IIf([sex]='f',[alcohol_24hr],Null))
    total	group by	expression	expression
    sort
    show	X
    criteria
    or

    
    SELECT victim.assault_district, Avg(IIf([sex]='m',[alcohol_24hr],Null)) AS male, Avg(IIf([sex]='f',[alcohol_24hr],Null)) AS female
    FROM victim
    GROUP BY victim.assault_district
    WITH OWNERACCESS OPTION;
    
    
  or created using the crosstab wizard
  • note the new transform and pivot clauses. These are not standard SQL clauses. syntax:
    TRANSFORM aggfunction
    selectstatement
    PIVOT pivotfield [IN (value1[, value2[, ...]])]
  • transform is optional but when included is usually the first statement in an SQL string. It precedes a SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally, you can include other clauses, such as WHERE. The values returned in pivotfield are used as column headings in the query's datasheet. For example, pivoting the sex figures on the in a crosstab query would create 2 columns. You can restrict pivotfield to create headings from fixed values (value1, value2) listed in the optional IN clause. You can also include fixed values for which no data exists to create additional columns. Using a PIVOT clause is equivalent to setting the ColumnHeadings property in the query property sheet in Design view.

    field	assault_district	expr1:IIf([sex]='m','male','female')	alcohol_24hr
    total	group by	group by	avg
    crosstab	row heading	column heading	value
    sort
    show	X	X	X
    criteria
    or

    
    TRANSFORM Avg(victim.alcohol_24hr) AS AvgOfalcohol_24hr
    SELECT victim.assault_district
    FROM victim
    GROUP BY victim.assault_district
    PIVOT IIf([sex]='m','male','female')
    WITH OWNERACCESS OPTION;
    
    

  Set operators

  Q17: Find the districts in which no one was assaulted.

  • there are about 10 different ways of doing this query using
    set operators
    exists operator
    not in operator
    outer joins

  set operators method

  Oracle

  • Set operators (minus, union, intersect) are used to link up the results of two separate SQL commands. In this case, the first select clause selects all the districts in the district table. The second select selects all the districts in which assaults had taken place. The effect of the minus operator is to find which districts were selected by the first select but not the second.
  • The unique or distinct operator must be used when set operators are used. Only columns of the same kind can be operated on.

  Oracle SQL>

  ttitle "Districts in which nobody was assaulted"
  set heading off
  select unique district
  from DISTRICT
  minus
  select unique assault_district
  from VICTIM; ttitle off
  set heading on

  Access

  Not possible

  Outer joins:

  
  

  ORACLE

  SELECT district.district
  FROM district
  WHERE district.district = victim.assault_district(+)
  AND ((victim.assault_district Is Null));

  Access

  • do an unmatched query wizard since only the union operators is available to Access
  • note the left join on victim since this is the table which does not contain all the districts
  • Double licking on a joining line between two tables presents the three options-
    only join if there are matching values in both tables
    join all values of the first table even if there are not matching values in the second
    join all values of the second table even if there are not matching values in the first

    field	district	assault_district
    sort
    show	X
    criteria		is null
    or

    
    SELECT DISTINCTROW district.district
    FROM district LEFT JOIN victim ON district.district = victim.assault_district
    WHERE ((victim.assault_district Is Null));

    not exists operator

    The not existsoperator can also be used as can be seen in the next example (far more efficient than the not in operator since it uses indexes

    Oracle

    select unique assault_district
    from victim
    where not exists (select * from district where district=assault_district)
    

    Access

    • if you put not exists in the criterion you get a type mismatch error since generate
      
      ((not district.district=exists(select * from victim where assault_district=district)))

      field	district	exists(select * from victim where assault_distict=district)
      sort
      show	X
      criteria		false
      or

      
      select distinct assault_district
      from victim
      where not exists (select * from district where district=assault_district)
      

    not in method

  • The not in method is very inefficient as no indexes are used
    select district
    from district
    where not in (select assault_district from victim)

Date handling

Q18: Find the dates of assaults which occurred before 1986.

Oracle

• The to_char function specifies the date format using keywords, for example ‘Day’ for day of e week, ‘yyyy’ for a four-digit year. The case of the keyword specifies th e case of the output, for example ‘MONTH’ outputs the month name in capita letters. Reference D describes date formats in more detail. If the to_char function is not used, dates must be specified in the format ‘DD-MON-YY’, that is 2 digits for the day, 3 characters for the month, and 2 digits for the year, separated by hyphens (see line 4).

Oracle SQL>

ttitle "Dates of assaults before 1986"
set heading off
select unique
to_char(assault_date, ’Day dth Month yyyy’)
from VICTIM
where assault_date<’01-JAN-86’; ttitle off
set heading on

• d for weekday number, dd month number, yyyy for year.

Access

• has the following date formats which can be used in the format function and any of the date functions

  d      Display the day as a number without a leading zero (1-31).
  dd     Display the day as a number with a leading zero (01-31).
  ddd    Display the day as an abbreviation (Sun-Sat).
  dddd   Display the day as a full name (Sunday-Saturday).
  ddddd  Display a date serial number as a complete date 
         (including day, month, and year) formatted according to the Short 
         Date Format setting in the International section of the Control Panel.
         The default Short Date Format is m/d/yy.
  dddddd Display a date serial number as a complete date (including day, month,
         and year) formatted according to the Long Date Format setting in the 
         International section of the Control Panel.  
         The default Long Date Format is mmmm dd, yyyy.
  w      Display the day of the week as a number (1-7).
  ww     Display the week of the year as a number (1-54).
  m      Display the month as a number without a leading zero (1-12).  
         If m immediately follows h or hh, the minute rather than 
         the month is displayed.
  mm     Display the month as a number with a leading zero (01-12).
         If m immediately follows h or hh, the minute rather than the 
         month is displayed.
  mmm    Display the month as an abbreviation (Jan-Dec).
  mmmm   Display the month as a full name (January-December)
  

• note the # is used to enclose a date field, just as " is used to enclose text

  field	format:format(assault_date,"dddd dd mmmm yyyy")	wkdayno:datepart('w',assault_date)	dayno:datepart('d',assault_date)	monthno:datepart('m',assault_date)	year:datepart('yyyy',assault_date)	assault_date
  sort
  show	X	X	X
  criteria						<#01/1/86#
  or

  
  SELECT DISTINCT Format(assault_date,"dddd dd mmmm yyyy") AS format,
  DatePart('w',assault_date) AS wkdayno,
  DatePart('d',[assault_date]) AS dayno,
  DatePart('m',assault_date) AS monthno,
  DatePart('yyyy',assault_date) AS year,
  victim.assault_date
  FROM victim
  WHERE ((victim.assault_date<#01/1/86#))
  WITH OWNERACCESS OPTION;
  
  
Q19: Find the age of the youngest and the oldest victim.

Oracle

• The date function months_between returns the difference in months between two dates. If 2 dates are subtracted using the minus operator (-), the answer is given in days.

Oracle SQL>

select
min(months_between(assault_date, birth_date)/12) "Youngest",
max(months_between(assault_date, birth_date)/12) "Oldest"
from VICTIM;


Access

• use the datediff function with the date format set as shown in Q18 ie 'm' for month number

  field	youngest:datediff('m',assault_date/12)	oldest:datediff('m',assault_date/12)
  total	min	max
  sort
  show	X	X
  criteria
  or

  
  SELECT Min(DateDiff('m',birth_date,assault_date)/12) AS youngest,
  Max(DateDiff('m',birth_date,assault_date)/12) AS oldest
  FROM victim
  WITH OWNERACCESS OPTION;
  
  
Q20: Find how many assaults took place on each day of the week.
• A column number can be used to specify the order. In the example, order by 2 orders by the second column defined in the select clause, that is count(to_char(assault_date, ’Day’)).

Oracle SQL>

column day format a10
column number format 999
select to_char(assault_date, ’Day’) "Day",
count(to_char(assault_date, ’Day’)) "Number"
from VICTIM
group by to_char(assault_date, ’Day’)
order by 2 desc;

Access

• added extra field which is not shown to force sorting by weekday number rather than workday name
• cannot specify the column number to be sorted only the field name

  field	assault_day:format(assault_date,"dddd")	count:*	datepart('w',assault_date)
  total	group by	expression	group by
  sort			ascending
  show	X	X
  criteria
  or

  
  SELECT Format([assault_date],"dddd") AS [Assault day],
  Count(*) AS count
  FROM victim
  GROUP BY Format([assault_date],"dddd"), DatePart('w',assault_date)
  ORDER BY DatePart('w',assault_date)
  WITH OWNERACCESS OPTION;
  
  

Joining tables

Q21: Find which bones were fractured when the victim had consumed more than 20 alcohol units.

• When data from more than one table is selected by a query, the tables must be joined using the common linking field. In this example, data is selected from both the VICTIM and the FRACTURE table. The common field is ‘vno’.
• It is more efficient to specify the join conditions before any other conditions in the where clause.
• If the column name is unique to a particular table (for example ‘alcohol_24hr’), the table name need not b specified. If the column name appears in more than one table being queried (for example ‘vno’ in this example), it must be prefixed by the table name.

Oracle SQL>

select weapon, bone, alcohol_24hr
from VICTIM, FRACTURE
where VICTIM.vno=FRACTURE.vno and
alcohol_24hr>20;

Access

• note the inner join clause- this has come from linking the tables by drawing a line between the two tables. It signifies an equi-join (only display the combined record if there are matching columns in both records). This is a non-standard SQL clause instead of the where clause in the ORACLE SQL above to link the two tables. If one wanted to use standard SQL the additional where condition would be added if one did not link by drawing a line between the linking columns, but by using the criteria box instead ie in the fracture.vno field specification add the criteria [victim].[vno]. You must add the square brackets else Access assumes that you are looking for text and helpfully adds double qotes!
• enforce referencial integrity by clicking on the relationship between fracture and victim table. The joined enforces that only victims who sustained fractures are shown, and that no fractures are sustained by unknown victims.

  field	weapon	bone	alcohol_24hr	vno
  sort
  show	X	X	X	X
  criteria			>20
  or

  
  SELECT DISTINCTROW victim.weapon, FRACTURE.bone, victim.alcohol_24hr, victim.vno
  FROM victim
  INNER JOIN FRACTURE ON victim.vno = FRACTURE.vno
  WHERE ((victim.alcohol_24hr>20))
  WITH OWNERACCESS OPTION;
  
  

Creating a table and inserting data

Q22: Find how many victims had consumed the amount of alcohol units in specified ranges.

• A new table is constructed to specify the required ranges of alcohol. Data is inserted into the table using the SQL insert command. The victims are then grouped into each range. The table is removed by the SQL drop table command.
• The column name used in the order by clause must appear in either the select or the group by clause.
• You would normally enter data by creating a form.

Oracle

• When inserting data into a table, use the SQL*Plus commit command at regular intervals to save the data in the database. Alternatively, use the SQL*Plus command set autocommit on to specify that the records are saved automatically after each insert command.

Oracle SQL>

set autocommit on
create table DRINKRANGE
(alc_range varchar(13),
alc_min number(2), alc_max number(2));

insert into DRINKRANGE values ('None', 0, 0);
insert into DRINKRANGE values ('1-5', 1, 5);
insert into DRINKRANGE values ('6-10', 6, 10);
insert into DRINKRANGE values ('11-15', 11, 15);
insert into DRINKRANGE values ('16-20', 16, 20);
insert into DRINKRANGE values ('gtr 20', 21, 99);

select alc_range "Alcohol range",
count(alc_range) "Number"
from VICTIM, DRINKRANGE
where alcohol_24hr between alc_min and alc_max
group by alc_range, alc_min
order by alc_min;


Alcoh  Number
----- -------
None      149
1-5        90
6-10      121
11-15      67
16-20      36
>20        24

6 records selected.

drop table DRINKRANGE;


Access

• Note that no join line is needed on the diagram between drinkrange and victim because the join is expressed by the where clause on alcohol_24hr

  field	alc_range	number:alc_range	alc_min	alcohol_24hr
  total	group by	count	group by	where
  sort			ascending
  show	X	X
  criteria				between [alc_min] and [alc_max]
  or

  
  SELECT drinkrange.alc_range, Count(drinkrange.alc_range) AS [number]
  FROM victim, drinkrange
  WHERE ((victim.alcohol_24hr Between [alc_min] And [alc_max]))
  GROUP BY drinkrange.alc_range, drinkrange.alc_min
  ORDER BY drinkrange.alc_min
  WITH OWNERACCESS OPTION;
  
  

Nested queries

Q23: Find the weapon used on the person who consumed the greatest number of alcohol units and how much was consumed.

• In this example, SQL carries out the select clause within the parentheses first. The single value produced as a result of this selection is then used by the first select clause.

Oracle SQL>

select weapon, alcohol_24hr
from VICTIM
where alcohol_24hr =
(select max(alcohol_24hr) from VICTIM);


Access

field	weapon	alcohol_24hr
sort
show	X	X
criteria		(select max(alcohol_24hr) from victim)
or


SELECT DISTINCTROW victim.weapon, victim.alcohol_24hr
FROM victim
WHERE ((victim.alcohol_24hr=(select max(alcohol_24hr) from victim)));

Q24: Find the weapons used on the victims who had consumed the greatest number of alcohol units in each assault district.

• The select clause within the parentheses returns the maximum value of alcohol_24hr for each value of assault_district. The first select clause then compares assault_district and alcohol_24hr of each record with these selected values and displays the values of weapon, assault_district and alcohol_24hr for the records that match.

Oracle

• Parentheses are required in the where clause on line 3 when comparing more than one column.




Oracle SQL>

ttitle left "Weapons used on victims who had drunk most"
set heading off
select weapon, assault_district, alcohol_24hr
from VICTIM
where (assault_district, alcohol_24hr) in
(select assault_district, max(alcohol_24hr)
from VICTIM
group by assault_district)
order by weapon;
ttitle off
set heading on


Access

• can not compare two columns in nested where clause

Creating views and calculating percentages

Q25: Find the number of assaults that have taken place in each district, and the percentage of the total number.

• Although it is straightforward to give the counts for each district, it is less easy to give the percentage, since each count must be divided by the total. A way of simplifying complex queries like this is to use views (Access queries).
• Views are an alternative way of looking at data without the expense of creating new tables. Data is not duplicated and so is always consistent.
• Having created a view, it can be treated in the same way as a table. For this reason views are also useful for letting other people examine part of your database, and for changing the names of columns.
• Like tables, views can be created and dropped at any time.
• The view is given a name, for example EACH_DIST in this example. The names of the columns may also be given, for example ‘count_district’ is an alias for ‘count(assault_district)’. The select clause in line 2 of the example specifies which rows are to held in the view.
• The second view ALL_DIST holds the total number of assaults for all districts.
• No join is needed with the two views, because there is only one value in the ALL_DIST view.

Oracle

• The SQL*Plus column command is used to format the percent column to 2 decimal places. Note it refers to the alias in this case.

Oracle SQL>

create view EACH_DIST (assault_district, count_district)
as
select assault_district, count(assault_district)
from VICTIM
group by assault_district;

create view ALL_DIST(count_all) as
select count(assault_district)
from VICTIM;
column percent format 99.99
select assault_district "District", count_district "Count",
(count_district/count_all)*100 "Percent"
from ALL_DIST, EACH_DIST;

drop view EACH_DIST;
drop view ALL_DIST;

Access

create query all_dist



field	count_all:count(vno)
sort
show	X
criteria
or


SELECT DISTINCTROW Count([vno]) AS count_all
FROM victim;

create query each_dist



field	assault_district	district_count:*
total	group by	count
sort
show	X	X
criteria
or


SELECT victim.assault_district, Count(*) AS district_count
FROM victim
GROUP BY victim.assault_district
WITH OWNERACCESS OPTION;




field	assault_district	district_count	percent:district_count/count_all
sort
show	X	X	X
criteria
or


SELECT each_dist.assault_district, each_dist.district_count, [district_count]/[count_all] AS [percent]
FROM all_dist, each_dist
WITH OWNERACCESS OPTION;


or
the following method avoids creating a second query, and uses field district_count defined in the query to work out the percent

field	assault_district	district_count:*	percent:district_count/count_all	count_all
total	group by	expression	expression	group by
sort
show	X	X	X
criteria
or


SELECT victim.assault_district, Count(*) AS district_count, [district_count]/[count_all] AS [percent]
FROM victim, all_dist
GROUP BY victim.assault_district, all_dist.count_all
WITH OWNERACCESS OPTION;



Adding columns and updating data

Q26: Create a new table containing the district names and an estimate of the percentage unemployment (assuming that 60% of the population are of employable age). Add a new column ‘comments’ to the table. Update the dat a in the table, save the changes using commit, then check.

Oracle SQL>

create table UNEMP (district, pc_unemp)
as select district,
((m_unemp+f_unemp)/(population*0.6)*100
from DISTRICT where population > 0;

alter table UNEMP
add (comments char(22));

update UNEMP
set comments = ’High unemployment area’
where pc_unemp > 25;

commit;
select district, pc_unemp
from UNEMP
where comments like ’High%’
order by pc_unemp desc;

Access

• this is an append query. Note that it creates the SQL insert command

  field	district	expr1:([m_unemp]+[f_unemp])	population
  table	district		district
  append to	district	pc_unemp
  criteria			>0
  or

  
  INSERT INTO unemp ( district, pc_unemp )
  SELECT DISTINCTROW district.district,
  ([m_unemp]+[f_unemp])*100/([population]*0.6) AS Expr1
  FROM district
  WHERE ((district.population>0))
  WITH OWNERACCESS OPTION;
  
  
• this is an update query. Note that it creates the SQL update command

  field	comments	pc_unemp
  update to	"High unemployment area"
  criteria		>20
  or

  
  UPDATE unemp SET unemp.comments = 'High unemployment area'
  WHERE ((unemp.pc_unemp>20))
  WITH OWNERACCESS OPTION;
  
  

Creating a simple report

Q27: Produce a simple report that lists, for each district where an assault took place, the reference number and amount of alcohol drunk for each victim, the total number of assaults, and the average alcohol consumption.

Oracle

• Subtotals can be introduced into reports at specified breakpoints.
• The SQL*Plus set pagesize command sets the page length.
• The SQL*Plus set newpage command outputs a form feed, and specifies the line where the new page is to start (line 1 by default).
• The SQL*Plus break command specifies a breakpoint where any subtotals, subcounts etc should be given, followed by a page throw. ‘on report’ specifies that the overall totals should be given at the end of the report. ‘on assault_district’ specifies that subtotals should be given whenever th value of assault_district changes. ‘page’ skips to a w page after the break.
• The SQL*Plus column command changes the format of the alcohol_24hr column, and gives it a new heading. Note that it works on the alias of the column, if one has been given. The format must be wide enough to accommodate the heading, otherwise the heading will be truncated. A vertical bar (|) indicates a new line.
• The SQL*Plus compute command calculates an aggregate function (for example count, sum, max) on the specified column and breakpoint. ‘on report’ gives an overall aggregate.
• There is no way of getting two separate aggregates on the same line even if they are for different columns. SQL*Report must be used for more complex reports (see Reference G).
• The SQL*Plus spool command directs all output to a file. In the example they would be sent to file ‘results.lst’.
• The SQL*Plus clear command turns off the page headers etc.

Oracle SQL>

set pagesize 66
set newpage 0
break on report on assault_district page
ttitle center "Assault summary per district"
column assault_district heading "District"
column alcohol_24hr format 999
column alcohol_24hr heading "Alcohol units|in 24 hours"
column vno heading "Victim"
compute count of vno on assault_district
compute count of vno on report
compute avg of alcohol_24hr on assault_district
compute avg of alcohol_24hr on report
spool results

select assault_district, vno, alcohol_24hr
from VICTIM
where assault_district like ’B%’
order by assault_district, vno;

spool off
clear break
clear compute
clear column
ttitle off


Access

• create a report using a wizard.
• ttitle is set in the report header section
• breaks are set when groupings are given- in this case assault_district will create assault_district header and footer sections
• column headings are given in assault_district header section by means of labels
• the data is shown in the detail section
• by default all numeric fields will be summed in calculated fields, change sum to avg in the report footer and assault_district footer
• click on properties to see the format of any field

  field	assault_district	vno	alcohol_24hr
  sort	ascending	ascending
  show	X	X	X
  criteria	B*
  or

  
  SELECT victim.assault_district, victim.vno, victim.alcohol_24hr
  FROM victim
  WHERE ((victim.assault_district Like 'B*'))
  ORDER BY victim.assault_district, victim.vno
  WITH OWNERACCESS OPTION;
  
  

Exporting data to another package

Q28: Produce a file containing data values required by another package.

Oracle

• The SQL file ‘q28.sql’ contains SQL*Plus commands to:

  suppress echoing of commands
  suppress display of results (for commands given from an SQL file)
  switch off display of number of records selected
  suppress pause after each page
  suppress display of command after variable substitution
  set line width to 132 (maximum width allowed is 500)
  switch off page headings
  start spooling output to a file, prompting for the filename

Oracle SQL>

set echo off
set pagesize 0
set termout off
set feedback off
set pause off
set verify off
set linesize 132
set headings off
spool &filename

The SQL file ‘q29.sql’ contains commands to reset the environment:

spool off
set pagesize 24
set termout on
set pause on
set feedback 1
set verify on
set linesize 80
set headings on
set echo on

The example creates a file named ‘bristol.lst’ containing two columns of data. The file also contains lines with the start commands at the beginning and the end which would have to be edited out before using it as a data file.
start q28
Enter value for filename: bristol
start q1
start q29

Access

select export from the file menu on the appropriate table or query, choose the format of file and whether it is to contain the field labels the file. By default the name of the file reflects the type of file being created- thus a delimitered file will have the name based on the name of the table/ query, with a file type txt.

Creating a sequence

Q29: Generate unique numbers. These numbers may be used to generate primary keys automatically.
• The increment, starting value, minimum and maximum values can all be specified

Oracle

• The pseudo-column ‘nextval’ is used to generate a sequence number from the specified sequence

Oracle SQL>

create sequence victim_seq; insert into victim (vno,...) values (victim_seq.nextval,...);

Access

• use a counter data type
• note that vno must be long integer type in fracture since for referential integrity it must match the vno of the victim. Setting vno as counter in fracture would merely generate an automatic counter for each fracture and it would be impossible to tell who sustained which fracture

Standard SQL syntax

SELECT [predicate] { * | table.* | [table.]field1 [, [table.]field2.[, ...]]}
[AS alias1 [, alias2 [, ...]]]
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
where predicate is any or distinct

Access SQL syntax

[TRANSFORM]aggfn(cell value)
SELECT [predicate] { * | table.* | [table.]field1 [, [table.]field2.[, ...]]}
[AS alias1 [, alias2 [, ...]]]
FROM tableexpression [, ...] [IN externaldatabase]
[INNER JOIN table 2 on table1=table2.field1 and table2 on table1.field2=tabl2.field2...]
[RIGHT JOIN table 2 on table1=table2.field1 and table2 on table1.field2=tabl2.field2...]
[LEFT JOIN table 2 on table1=table2.field1 and table2 on table1.field2=tabl2.field2...]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[PIVOT columnheading]
[WITH OWNERACCESS OPTION]
predicate is all, distinct, distinctrow, top
(eg select top 10 gives first 10 records)

A 3 table join: from (district
inner join victim on district.district=victim.assault_district
inner join fracture on victim.vno=fracture.vno