SQL basics 2¶

Aggregate functions¶

• In SQL language numerical data can be processed with aggregate functions

• The following aggregate functions are supported:

  • AVG → Average of values
  • SUM → Total from addition
  • MIN → Smallest value
  • MAX → Greatest value
  • COUNT → Amount of values

• Important: NULL value will not be counted unless COUNT(*) is used!

SELECT MAX(salary) AS maximum, MIN(salary) AS minimum, AVG(salary) as average
FROM Employee;

SELECT COUNT(*) AS count
FROM Employee
WHERE salary > 2900;

SELECT SUM(salary)
FROM Employee;

String functions¶

• String functions are used to manipulate stored string values for the result set
• Some of string functions supported in SQL language are listed below
  • SUBSTRING → Select part of original string
  • RIGHT → Select given amount of characters from string starting from right
  • LEFT → Select given amount of characters from string starting from left
  • UPPER → Transforms lower-case letters to upper-case letters
  • LOWER → Transforms upper-case letters to lower-case letters
  • LENGTH → Get the length of the string
  • CONCAT → Combine several strings into one

SELECT surname, SUBSTRING(surname, 2, 3) AS surname_part
FROM Employee
ORDER BY surname;

SELECT surname, SUBSTRING(surname FROM 2 FOR 3) AS surname_part
FROM Employee
ORDER BY surname;

SELECT CONCAT(surname, ', ', firstname) AS fullname
FROM Employee
ORDER BY surname;

Numeric functions¶

• There are many numeric functions available for SQL languages
• On this course we'll use the following numeric function:
  • ROUND → Returns a number rounded to a certain number including given decimal places. Syntax: ROUND(number, decimals)

SELECT ROUND(AVG(salary/30),2) AS daily_salary
FROM Employee;

Convert the data type of a value (CAST)¶

• In SQL language data types can be converted if needed using CAST
• Conversion can be done from numeric value to string and vice versa
• Below are some examples of changing value data type with CAST

CAST(date AS CHAR(10))          -- converts date to string
CAST(num AS INT)                -- converts string consisting of numbers to integer
CAST(salary/30 AS DECIMAL(7,2)) -- converts daily salary into format 99999.99
CAST(dep_num AS CHAR(5))        -- converts numeric dep_num field to string

CASE statement¶

• Conditions can be formed in SQL with CASE statement
• CASE statement returns a value when first condition is met and the reading of conditions is stopped
• If no conditions are True, value from the ELSE clause is returned
• New column will be created at the end of CASE statement using END AS clause
• CASE statement syntax is presented below

CASE column
    WHEN condition THEN value1
    ELSE value2
END AS aliasname;

SELECT firstname, surname,
    CASE city
        WHEN 'Jyväskylä' THEN 'Central Finland'
        WHEN 'Helsinki' THEN 'Southern Finland'
        ELSE 'Other parts of Finland'
    END AS Area,
    CASE
        WHEN salary <= 2000 THEN 'Low'
        WHEN salary >= 3000 THEN 'High'
        ELSE 'Normal'
    END AS Salary_grade
FROM Employee;

• Important: As can be seen from the example above, when comparing numeric values the column name is included in WHEN clause (Not after CASE clause!).

Dates¶

• In MySQL dates are in format yyyy-mm-dd
• The difference in two dates can be calculated with DATEDIFF function

SELECT firstname, surname, date, CURRENT_DATE as today, DATEDIFF(CURRENT_DATE, date) AS difference
FROM Employee;

Grouping the information (GROUP BY)¶

• Data in result set can be grouped with GROUP BY clause
• All rows having the same value will be grouped
• Usually we want to group results from aggregate functions (AVG, SUM, MIN, MAX, COUNT)
• Important: If SELECT statement includes both regular columns and columns created with aggregate functions, all regular columns must be listed in GROUP BY clause!

SELECT city, AVG(salary) AS average_salary
FROM Employee
GROUP BY city;

• Grouped results can be further filtered by using HAVING clause
• The order should always be like this:
  • WHERE → filter results before grouping
  • GROUP BY → filtered results are grouped
  • HAVING → grouped results can be filtered

SELECT vendor, COUNT(*)
FROM Product
WHERE price > 100
GROUP BY vendor
HAVING COUNT(*) > 2;