IM SQL Column Function

• Two types of SQL Functions

  Column functions - produce a summary row for a set of rows

  Scalar functions –returns a value for a row based on input argument
• Sa scalar functions isang row lang, it doesnt process several rows
• Most Common COLUMN FUNCTIONS

  SUM() - computes total

  AVG() - computes average

  MIN() - finds minimum value

  MAX() - finds maximum value

  COUNT(*) - determines the total number of

  rows
• SELECT SUM(column name)

  FROM table name;

  SELECT AVG(column name)

  FROM table name;

  SELECT MIN(column name)

  FROM table name;

  SELECT MAX(column name)

  FROM table name;

  SELECT COUNT(*) FROM table name
• Give the answer to the following:

  SELECT SUM(SALARY) AS ‘TOTAL SALARY’

  FROM EMPLOYEE;

  SELECT AVG(TAX) AS ‘Average Tax’

  FROM EMPLOYEE;

  SELECT MIN(TAX) AS ‘Minimum Tax’

  FROM EMPLOYEE;

  
• Give the syntax: Average the salary of all employees who were hired after ‘2000/1/1’

  SELECT AVG(SALARY)

  FROM EMPLOYEE

  WHERE HIREDATE > ‘2000-1-1’;
• Sum up the salary of the male employees

  SELECT SUM(SALARY)

  FROM EMPLOYEE

  WHERE SEX =“M”;
• Structure of an SQL Query

  SELECT

  FROM

  WHERE

  GROUP BY

  HAVING

  ORDER BY
• GROUP BY clause in SELECT

  
  

  This clause tells which rows in a table are to be grouped together. It divides a table into subsets (by groups)

  
  

  With GROUP BY, a column function results in a single value for each group
• Compute the average salary for each division

  SELECT AVG(salary)

  FROM table

  GROUP BY Division;

  output:

  6000

  4750

  3000
• Compute the average salary for each division, include the division in the output.

  SELECT Division, AVG(salary)

  FROM table

  GROUP BY Division;

  output:

  ISD 6000

  FIN 4750

  HR 3000
• Give the SQL code.

  SUm up the salaries for each job function. Include the job in the output.

  SELECT Job, SUM(Salary)

  FROM table

  GROUP BY Job;

  Output:

  MGR 5500

  RF 1750

  SMR 8500

  
• Give the SQL Code: Compute the average GWA for each course. Include course in the output.

  SELECT Course, AVG(GWA)

  FROM table

  GROUP BY Course;

  Output:

  BBF 2

  IT 2.16

  CS 1.87

  
• If a SELECT clause has a COLUMN function and fields not in COLUMN function, all fields not in COLUMN function must be included in GROUP BY clause

  Basically, kung ano yung nasa group by mo, dapat yun din yung nasa select.
• The HAVING clause is a search condition for a group or an aggregate. This is typically used with the GROUP BY clause.

  The HAVING clause tells which groups of information are to be processed based upon a group qualification criteria rather than rows.
• List the average GWA for each course if the average is higher than 2. Include course in the output.

  SELECT Course, AVG(gwa)

  FROM table

  GROUP BY Course

  HAVING AVG(gwa)<2;
• List the average salary which is greater than 5000 for each division. Include the division

  SELECT division, AVG(salary)

  FROM table

  GROUP BY division

  HAVING AVG(salary)>5000;
• The WHERE clause is used to filter the rows from a table, whereas the HAVING clause is typically used to filter the grouped result.
• Get the average salary by division and job for all non rank and file positions. Include only those averages which are greater than 30,000. Sort by average.

  SELECT DIV, JOB, AVG(SAL)

  FROM base table

  WHERE JOB <> 'RF'

  GROUP BY DIV, JOB

  HAVING AVG(SAL)>30000

  ORDER BY AVG(SAL) ASC;
• Find the smallest and biggest salary for each dept. include the dept in the output.

  SELECT dept MIN(salary), MAX(salary)

  FROM table

  GROUP BY dept;