• +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Live Chat
Follow Us:

SQL Function Homework Help

Part4. SQL Function

Introduction

SQL Aggregate Functions

SQL Scalar Functions

SQL Function :

SQL has many built-in-functions for performing processing on string or numeric data.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Some useful aggregate functions:

  • SQL AVG() : The SQL AVG aggregate function selects the average value for certain table column.
  • SQL MIN() : The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
  • SQL SUM() : The SQL SUM aggregate function allows selecting the total for a numeric column.
  • SQL COUNT() : The SQL COUNT aggregate function is used to count the number of rows in a database table.
  • SQL FIRST() : Returns the first value
  • SQL LAST() : Returns the last value
  • SQL MAX() : The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.

1. SQL AVG() : The SQL AVG aggregate function selects the average value for certain table column.

Syntax:

SELECT AVG(colm-name) FROM tble-name

For example:

SELECT AVG(Salary) FROM TEACHER;

sql functions help

2. SQL SUM() : The SQL SUM aggregate function allows selecting the total for a numeric column

Syntax:

SELECT SUM(colm-name) FROM tble-name;

For example;

SELECT SUM(Salary) FROM TEACHER;

sql functions help

3. SQL COUNT() : The SQL COUNT aggregate function is used to count the number of rows in a database table.

Syntax:

SELECT COUNT(colm-name) FROM tble-name;

For Example:

SELECT COUNT(TEACHER_ID) FROM TEACHER;

sql functions help

4. SQL FIRST() : Returns the first value

Syntax:

SELECT FIRST(colm-name) FROM tble-name;

Or

SELECT TOP 1 colm-name FROM tble-name
ORDER BY colm-name ASC;

For example:

SELECT TOP 1 Teacher_Name FROM TEACHER

ORDER BY Teacher_Name ASC;

sql functions help

5. SQL LAST() : Returns the last value

Syntax:

SELECT LAST(colm-name) FROM tble-name;

Or

SELECT TOP 1 colm-name FROM tble_name
ORDER BY colm-name DESC;

For example:

SELECT TOP 1 Teacher_Name FROM TEACHER

ORDER BY Teacher_Name DESC;

sql functions help

6. SQL MAX() : The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.

Syntax:

SELECT MAX(column-name) FROM table-name;

For example:

SELECT MAX(Salary) FROM TEACHER;

sql functions help

7. SQL MIN() : The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

Syntax:

SELECT MIN(colm-name) FROM tble-name;

For example:

SELECT MIN(Salary) FROM TEACHER;

sql functions help

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Some useful scalar functions:

  • SQL UCASE() : Synonym for UPPER()
  • SQL LCASE() : LCASE() is a synonym for LOWER().
  • SQL ROUND() : Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points
  • SQL NOW() : Returns the current system date and time
  • SQL FORMAT() : Returns a numeric expression rounded to a number of decimal places.
  • SQL MID() : MID(str, pos, len) is a synonym for SUBSTRING(str, pos, len).
  • SQL LEN() : Returns the length of a text field

1. SQL UCASE() : Converts a field to upper case

Syntax;

SELECT UCASE(colmn-name) FROM tble-name;

Or

SELECT UPPER (colmn-name) FROM tble-name;

For example:

SELECT UPPER(Teacher_Name) FROM TEACHER;

sql functions help

2. SQL LCASE() : Converts a field to lower case

Syntax:

SELECT LCASE(colm-name) FROM tble-name;

Or

SELECT LOWER(colm-name) FROM tble-name;

For example:

SELECT LOWER(Teacher_Name) FROM TEACHER;

sql functions help

3. SQL ROUND() : Rounds a numeric field to the number of decimals specified

Syntax:

SELECT ROUND(colm-name, decimals) FROM tble-name;

For example:

SELECT ROUND(Salary,0) FROM TEACHER;

sql functions help

4. SQL NOW() : Returns the current system date and time

Syntax:

SELECT NOW() FROM tble-name;

Or

SELECT getdate() FROM tble-name;

For example:

SELECT Salary, GETDATE() PerDate FROM TEACHER;

sql functions help

5. SQL FORMAT() : Formats how a field is to be displayed

Syntax:

SELECT FORMAT(colm-name, format) FROM tble-name;

For example:

6. SQL MID() : Extract characters from a text field

Syntax:

SELECT MID(colm-name, start[,length]) AS some-name FROM tble-name;

Or

SELECT SUBSTRING(colm_name, start, length) AS some_name FROM tble_name;

For example:

SELECT SUBSTRING(Teacher_Name,1,4) Name FROM TEACHER;

sql functions help

7. SQL LEN() : Returns the length of a text field

Syntax:

SELECT LEN(colm-name) FROM tble-name;

For example:

SELECT LEN(Salary) FROM TEACHER;

sql functions help
Keywords: Data Relations homework help, SQL homework help, SQL project Help, Oracle Homework Help, XML homework help, XML and Relational Algebra assignment help, SQL Queries and Updates homework assignment help, XML Queries and Transformations tutoring help, Relational Database Design concept, Higher-Level Design help in UML and ERD, Data Warehousing and Data Mining, help with SQL 2008 database, support for Database help, Microsoft access and mysql database programmer, database development and xml database support, homework helper for asp database and uml modeling, uml sequence diagram and uml class diagram, database design development and software SQL server 2008.