a)  How many towers has the mobile phone company got?

SELECT COUNT(DISTINCT towerid)
FROM CONNECT

b)  What IS the most expensive connection fee OF ALL the plans?

SELECT MAX(connectfee)
FROM plan

c)  How many mobile phones OF each brand name have been sold TO customers OF the mobile phone company?

SELECT brandname,COUNT(*)
FROM mobile
GROUP BY brandname

d)  SHOW a list OF the tower usage.  Display the tower ID AND the NUMBER OF calls connected through each tower. (Hint: GROUP BY query USING the CONNECT TABLE)

SELECT towerid, COUNT(callsid) AS Number_of_Calls_connected
FROM CONNECT
GROUP BY towerid

e)  SHOW the total NUMBER OF female AND male customers the company has.

SELECT sex AS Gender, COUNT( * )
FROM customer
GROUP BY sex

f)  List the NUMBER OF mobile phones per mobile phone colour.  ONLY SHOW the colours which have more than 25 phones.

SELECT phonecolour, COUNT( * )
FROM mobile
GROUP BY phonecolour
HAVING COUNT( mobileid ) >25

g)  SHOW the COUNT OF customers FOR each suburb, WITH the highest numbered suburb (COUNT) FIRST AND lowest COUNT LAST ON the list.

SELECT Suburb, COUNT( * )
FROM customer
GROUP BY Suburb
ORDER BY COUNT( * ) DESC

h)  SHOW the oldest AND youngest DATE OF birth OF ALL customers.

SELECT MAX( dob ) AS Youngest, MIN( dob ) AS Oldest
FROM customer

i)  What IS the average age OF ALL customers ?

SELECT TRUNCATE(avg(datediff(sysdate(),dob)/365.25),0) AS AverageAge
FROM customer

j)  What IS the total annual salary being paid OUT TO CURRENT active staff?

SELECT SUM( rateperhour *40 *52 ) AS AnnualSalary
FROM staff
WHERE resigned IS NULL

k)  SHOW a list OF brand names AND a COUNT OF the UNIQUE customers FOR each brand.

SELECT brandname, COUNT( DISTINCT customerid )
FROM mobile
GROUP BY brandname

*** (IN process)
l)  FOR calls made IN 2006, display how many calls were made ON each DAY OF the week.
Hint: You can USE the functions dayofweek(), dayname() AND YEAR()

SELECT DISTINCT dayname(calldate), COUNT(*)
FROM calls
WHERE YEAR(calldate)=2006
GROUP BY dayname(calldate)
ORDER BY dayofweek(calldate)