SQL Functions and Joins

• Aggregate function 


  Performs a calculation on a set of values and returns a single value.
• Aggregate functions:

  • MIN
  • MAX
  • COUNT
  • SUM
  • AVG
• MIN


  The minimum attribute value encountered in a given column
• MAX


  The maximum attribute value encountered in a given column
• COUNT 


  The number of rows containing non-null values
• SUM


  The sum of all values for a given column
• AVG


  The arithmetic mean (average) for a specified column
• Basic syntax of an aggregate function:

  SELECT FUNCTION(column_name) FROM table_name;
• GROUP BY


  Is often used with aggregate functions to group the result-set by one or more columns
• GROUP BY Syntax:

  SELECT columns FROM table_name GROUP BY columns;
• Use an alias to assign a temporary name to the column. It can also be given to tables.
• Alias Syntax:

  SELECT column_name AS alias FROM table_name;
• HAVING


  Is used to restrict the output of a GROUP BY query by applying conditional criteria to the grouped rows.
• HAVING syntax:

  SELECT columns FROM table_name GROUP BY columns HAVING condition;
• The most commonly used date functions are as follows:

  • YEAR
  • MONTH
  • DAY
• YEAR 


  Returns a four-digit year
• MONTH – returns the number of the month

  DAY – returns the number of the day
• GETDATE 


  Returns the current date and time
• Syntax: SELECT GETDATE();
• DATEADD 


  Adds a number of selected time/date periods to a date then returns the date
• Syntax: SELECT DATEADD(datepart, number, date);

  Example 1: SELECT DATEADD(month, 2, '2020/12/14');

  Result: 2021-02-14 00:00:00.000
• DATEDIFF 


  Returns the difference between two (2) dates
• Syntax: DATEDIFF(datepart, date1, date2);

  Example: SELECT DATEDIFF(month, '1989-07-25', '2020-02- 14');

  Result: 367
• ABS 


  Returns the absolute value of a number
• Example: SELECT ABS(-234.5);

  Result: 234.5
• ROUND 


  Rounds a number to a specified number of decimal places
• Syntax: ROUND(number, decimal places);

  Example 1: SELECT ROUND(234.459, 1);

  Result: 234.500
• CEILING 


  Returns the smallest integer value that is greater than or equal to a number
• Example: SELECT CEILING(234.1);

  Result: 235
• CONCAT 


  Joins two or more strings together
• Syntax: CONCAT(string1, string2, …);

  Example: SELECT CONCAT(FirstName + ' ' , LastName) FROM Customers WHERE CustomerID = 3446;

  Result: Marco Velasco
• LOWER


  Returns a string in lowercase letters
• UPPER


  Returns a string in all capital letters
• Example: SELECT UPPER('sql') + ' ' + LOWER('FUNCTIONS');

  Result: SQL functions
• SUBSTRING 


  Returns a part of a string
• Syntax: SUBSTRING(string, start position, length);

  Example: SELECT SUBSTRING('SQL Functions', 1, 3);

  Result: SQL
• LEN 


  Returns the number of characters in a string
• Example 1: SELECT LEN('SQL Functions');

  Result: 13
• TRIM


  Removes the spaces or specific characters from start or end of a string
• Syntax: TRIM([characters FROM ]string);

  Example: SELECT TRIM('<!> ' FROM ' <Functions! >');

  Result: Functions