Standard SQL Functions

Table of contents

1. 1. Intro Quiz

   0/7

   In this course we assume that you know the basics of SQL: you know how to select information from a single table, you know how to join queries and group by query results. Take the test to find out if you know SQL well enough to follow the course.

   List of exercises Start now
   1. Introductory Quiz
      1. Introduction
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Exercise 5
      7. Congratulations

2. 2. Pretty Printing

   0/22

   Learn the basic SQL text functions to transform text values and make your reports look pretty.

   List of exercises Start now
   1. Introduction
      1. Introduction
      2. Table copywriter
      3. Table slogan
   2. Concatenation
      1. Concatenation operator ||
      2. Concatenation operator || continued
      3. Concatenation operator || further practice
   3. Simple text functions
      1. Function length
      2. Function length – practice
      3. Function lower
      4. Function upper
      5. Function initcap
      6. Functions lower, upper, initcap – usage
      7. Text functions – practice
   4. How to modify strings
      1. Function substring
      2. Function substring continued
      3. Function replace
      4. Function replace – practice
   5. Review
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Congratulations

3. 3. Numeric functions

   0/28

   Master numeric functions in SQL. Learn about the caveats of mathematical operators in SQL, various rounding functions in SQL and computing proportions in SQL.

   List of exercises Buy course
   1. Reviewing SQL and numbers
      1. Introduction
      2. Get to know table character
      3. Numbers – quick review
      4. Numbers – concatenation
   2. So you think you can count
      1. Problems with subtraction
      2. Binary arithmetic is not exact
      3. Problems with division
      4. Integer division
      5. New keyword CAST
      6. Division by zero
      7. Division by zero continued
      8. Getting rid of division by 0
      9. Function mod()
   3. Round and other functions
      1. Function round()
      2. Precision with round()
      3. Problems with round()
      4. Round in Postgres – explanation
      5. Other rounding functions
      6. Function floor() – practice
      7. Function trunc() – practice
      8. Function abs()
      9. Function sqrt()
   4. Review and practice
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Congratulations

4. 4. Date and time functions

   0/46

   Study date and time functions and learn how to select events from a period of time.

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. Table route
      3. Table aircraft
      4. Table flight
   2. Dates
      1. Dates in SQL
      2. Date format explained
      3. Dates with logical operators
      4. Dates with BETWEEN
      5. Dates with ORDER BY
      6. Dates – additional practice
   3. Time
      1. Time in SQL
      2. Time with logical operators
      3. Time with BETWEEN
      4. Time with ORDER BY
      5. TIME – additional practice
   4. Timestamps
      1. Timestamps
      2. Timestamps explained
      3. Timestamps with constant values
      4. Timestamps with BETWEEN
      5. Timestamps with ORDER BY
      6. Timestamps – additional practice
   5. Extract functions
      1. EXTRACT(field FROM column)
      2. EXTRACT – example
      3. EXTRACT with a wrong data type
      4. EXTRACT with a wrong data type – continued
   6. Timezone conversion
      1. AT TIME ZONE
      2. AT TIME ZONE – additional practice
   7. Intervals
      1. Difference between timestamps
      2. INTERVAL YEAR TO MONTH
      3. INTERVAL DAY TO SECOND
      4. INTERVAL 'x' FIELD
      5. INTERVAL with constant values
      6. INTERVAL with constant values – continued
      7. INTERVALs with comparison operators
      8. INTERVALs with comparison operators – additional practice
   8. Current date and time
      1. Current_date, current_time and current_timestamp
      2. Current date & time with intervals
      3. Current date & time with intervals – additional practice
   9. Review
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Exercise 5
      7. Exercise 6
      8. Congratulations

5. 5. Nulls

   0/30

   NULLs are tricky. Learn how to avoid common mistakes with NULL and what SQL functions there are to deal with NULL.

   List of exercises Buy course
   1. Review
      1. Introduction
      2. Get to know the table
      3. NULL – quick review
      4. NULL – quick review #2
   2. Comparisons with NULL
      1. IS NULL or = NULL?
      2. NULL with non-equality
      3. NULL with non-equality – practice
      4. NULL with comparisons
      5. NULL with operator IN
      6. Never trust a NULL
   3. Functions with NULL
      1. NULL with other functions
      2. NULL with mathematical operators
      3. NULL with function length
   4. COALESCE to the rescue
      1. New keyword: COALESCE
      2. Data types in COALESCE
      3. Conversion for COALESCE
      4. COALESCE with other functions
      5. COALESCE with calculations
      6. COALESCE with more than two parameters
   5. NULLIF
      1. New function NULLIF
      2. NULLIF – practice 1
      3. NULLIF – practice 2
   6. Review and practice
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Exercise 5
      7. Exercise 6
      8. Congratulations

6. 6. Aggregate functions

   0/37

   Deepen your understanding of SQL aggregate functions: COUNT, AVG and SUM. Practice which variant of COUNT or AVG to choose in typical use cases.

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. Table translator
      3. Table project
   2. Function COUNT
      1. Count all rows
      2. COUNT with column
      3. COUNT(*) vs. COUNT(column) – practice
      4. COUNT with expression
      5. COUNT DISTINCT
      6. COUNT DISTINCT with expression
      7. COUNT and GROUP BY
      8. COUNT and GROUP BY – additional practice
      9. COUNT DISTINCT and GROUP BY
      10. COUNT DISTINCT with expression and GROUP BY
      11. Common mistake: COUNT(*) and LEFT JOIN
      12. COUNT – additional exercise 1
      13. COUNT – additional exercise 2
   3. Function AVG
      1. Function AVG
      2. AVG and GROUP BY
      3. AVG and NULLs
      4. AVG and NULLs only
      5. AVG with COALESCE
      6. COALESCE with AVG
      7. AVG and DISTINCT
      8. ROUND and AVG
      9. AVG – additional exercise
   4. Functions SUM, MAX, MIN
      1. Function SUM
      2. SUM with NULLs only
      3. SUM and COALESCE
      4. SUM and DISTINCT
      5. MAX and MIN
      6. SUM, MAX and MIN – exercise
   5. Review
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Congratulations

7. 7. CASE WHEN

   0/29

   Learn how to include conditions in your SQL queries. Practice computing simple summary reports.

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. Table candidate
      3. Table application
   2. Simple CASE WHEN
      1. Simple CASE WHEN
      2. Simple CASE WHEN continued
      3. Simple CASE WHEN with NULLs
      4. Simple CASE WHEN – practice
   3. Searched CASE WHEN
      1. Searched CASE WHEN
      2. Searched CASE WHEN with various conditions
      3. Searched CASE WHEN with NULLs
      4. Searched CASE WHEN with NULLs – continued
      5. Searched CASE WHEN – additional practice
   4. CASE WHEN with aggregates
      1. CASE WHEN with SUM
      2. Counting rows with CASE WHEN and SUM
      3. CASE WHEN with SUM continued
      4. CASE WHEN with COUNT
      5. CASE WHEN with COUNT – practice
      6. CASE WHEN with COUNT DISTINCT
   5. CASE WHEN with GROUP BY
      1. CASE WHEN with GROUP BY
      2. CASE WHEN with GROUP BY – practice
      3. CASE WHEN with GROUP BY – practice 2
      4. Another use of CASE WHEN with GROUP BY
      5. CASE WHEN in GROUP BY
   6. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Congratulations

8. 8. Final Quiz

   0/12

   Check the knowledge of SQL functions in the quiz.

   List of exercises Buy course
   1. Final Quiz
      1. Introduction
      2. Table person
      3. Exercise 1
      4. Exercise 2
      5. Exercise 3
      6. Exercise 4
      7. Exercise 5
      8. Exercise 6
      9. Exercise 7
      10. Exercise 8
      11. Exercise 9
      12. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more