Lecture 8: SQL Select Part 2 and RA

Question 1

Explain Aggregate Functions

Answer 1

Often require to perform some form of summation or aggregation of data, similar to the totals at the bottom of a report

there are 5 aggregate functions, in SQL that we can use in SELECT command

each operates on a single column of a table and returns a single value

apart from count(*), each function eliminates nulls first and operates only on remaining non-null values

Question 2

list the aggregate functions

Answer 2

COUNT
SUM
AVG
MIN
MAX

Question 3

which two aggregate functions are only used on NUMERIC fields

Answer 3

SUM and AVG

Question 4

List three aggregate functions that can apply to non-numeric and numeric fields

Answer 4

COUNT
MIN
MAX

Question 5

What does count(*) do?

Answer 5

counts all rows of a table, regardless of whether nulls or duplicates values occur

Question 6

What does distinct have an effect on?

Answer 6

no effect with : MIN/MAX

effect with
SUM/AVG

Question 7

where are aggregate functions location?

Answer 7

can only be used in select list and in having clause

Question 8

give an example of a meaningless aggreate function call

Answer 8

select staffNo, COUNT(salary)

from staff;

Question 9

how to use count

Answer 9

SELECT COUNT(*) AS myCount	 
FROM PropertyForRent		WHERE rent > 350

Question 10

how to use count and distinct

Answer 10

SELECT COUNT(DISTINCT propertyNo)
AS myCount FROM viewing;

Question 11

use of count and sum?

Answer 11

SELECT COUNT(staffNo) AS myCount,SUM(salary) AS mySum FROM Staff WHERE position = ‘Manager’

Question 12

use of min, max and avg?

Answer 12

SELECT 
MIN(salary) AS myMin,
MAX(salary) AS myMax,
AVG(salary) AS myAvg	
FROM Staff;

Question 13

How to use group by

Answer 13

SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;

Question 14

Describe the having clause

Answer 14

having clause is designed for use with group by to restrict groups that appear in final result table

similar to where, but where filters individual rows whereas HAVING filters groups

column names in HAVING clause must also appear in the GROUP BY list or be contained within an aggregate function

Question 15

Example of using having

Answer 15

select branchNo, 
count (staffNo) as mycount
sum(salary) as mysum
from staff
group by branchNo
having count (staffNo) > 1
order by branchoNo;

Question 16

explain subqueries

Answer 16

some sql statements can have a select embedded within them

a subselect can be used in WHERE and HAVING clauses of an outer select, where it is called a subquery or nested query

subselects may also appear in INSERT, UPDATE and DELETE statements

Question 17

Provide a subquery example

Answer 17

SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo =

(SELECT branchNo FROM Branch
WHERE street = ‘163 Main St’);

Question 18

example of subquery with aggregate

Answer 18

SELECT staffNo, fName, lName, position,
salary –

(SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >

(SELECT AVG(salary)
FROM Staff);

Question 19

is this possible?

WHERE salary > AVG (salary) ?

Answer 19

NO

instead:
SELECT staffNo, fName, lName, position, salary – 17000 As salDiff
FROM Staff
WHERE salary > 7000;

Question 20

List the subquery rules

Answer 20

• order by clause may not be used n a subquery
• subquery select list must consist of a single column, name or expression unless using EXIST
• must appear on RHS
• cannot be used as an operand in an expression

Question 21

What are set operations?

Answer 21

union
intersection,
difference
except /not in (symbol : -)

two tables must be union compatible

Question 22

Union?

Answer 22

union of two tables A and B, it table containing all rows in either A or B or both

Question 23

intersection /JOIN

Answer 23

is table containing all rows common to both a and b

mySQL does not use intersect. use join instead

Question 24

difference

Answer 24

is table containing all rows in a but not in b