Using the Group Functions

What Are Group Functions ?

• Group functions operate on sets of rows to give one result per group.

Types of Group Functions :

• AVG
• COUNT
• MAX
• MIN
• SUM

Group Functions : Syntax

• SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];

Nesting Functions

SELECT ename, UPPER(SUBSTR (ENAME, 1, 8) || '_US') FROM emp WHERE deptno = 10;
The slide example displays the last names of employees in department 10.

The evaluation of the SQL statement involves three steps:

• – The inner function retrieves the first eight characters of the last name.

• Result1 = SUBSTR (ENAME, 1, 8)
• – The outer function concatenates the result with _US.

• Result2 = Result1 || '_US'
• – The outermost function converts the results to uppercase.
  

Using the TO_CHAR Function with Numbers

TO_CHAR(number, 'format_model')

• These are some of the format elements that you can use with the TO_CHAR function to display a number value as a character :
• SELECT empno, TO_CHAR(sal, '$99,999.00') as Month_Hired FROM emp;

Using the TO_NUMBER and TO_DATE Functions :

Convert a character string to a number format using the TO_NUMBER function:

• TO_NUMBER(char[, 'format_model'])

Convert a character string to a date format using the TO_DATE function:

• TO_DATE(char[, 'format_model'])
  

Using the TO_CHAR Function with Dates

TO_CHAR(date, 'format_model')

The format model:
– Must be enclosed by single quotation marks
– Is case-sensitive
– Can include any valid date format element
– Has an fm element to remove padded blanks or suppress leading zeros
– Is separated from the date value by a comma

Example:
SELECT empno, TO_CHAR(hiredate, 'MM/YY') as Month_Hired FROM emp

Elements of the Date Format Model :
Time elements format the time portion of the date:
HH24:MI:SS AM 15:45:32 PM

Add character strings by enclosing them in double quotation marks:
DD "of" MONTH 12 of OCTOBER

Number suffixes spell out numbers: (Not Yet Implemented)
ddspth fourteenth

Example: SELECT empno, TO_CHAR(hiredate, 'ddspth') Month_Hired FROM emp;

Elements of the Date Format Model :

• Use the formats that are listed in the following tables to display time information and literals and to change numerals to spelled numbers
• SELECT empno, TO_CHAR(hiredate, 'fmDD Month YYYY') As Month_Hired FROM emp;
• The SQL statement in the slide displays the last names and hire dates for all the employees. The hire date appears as 17 December 1980.
  

Conversion Functions

Conversion Functions :

• In some cases, the PostgreSQL server uses data of one data type where it expects data of a different data type. When this happens, the PostgreSQL server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the PostgreSQL server or explicitly by the user
• Conversion functions convert a value from one data type to another

• Implicit data type conversions work according to the rules that are explained in the next two slides

Implicit Data Type Conversion :

• The assignment succeeds if the PostgreSQL server can convert the data type of the value used in the assignment to that of the assignment target

• example, the expression hiredate > '01-JAN-90' results in the implicit conversion from the string '01-JAN-90' to a date

Explicit Data Type Conversion :

• Explicit data type conversions are done by using the conversion functions

Number Functions

ROUND :

• Rounds value to specified decimal

TRUNC :

• Truncates value to specified decimal

MOD :

• Returns remainder of division

ROUND Function

• ROUND function rounds the column, expression, or value to n decimal places. If the second argument is 0 or is missing, the value is rounded to zero decimal places. If the second argument is 2, the value is rounded to two decimal places. Conversely, if the second argument is –2, the value is rounded to two decimal places to the left (rounded to the nearest unit of 10).
• The ROUND function can also be used with date functions.

Using the ROUND Function :

• SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1);
• 45.92 46 50
  

TRUNC Function :

• TRUNC function truncates the column, expression, or value to n decimal places.
• TRUNC function works with arguments similar to those of the ROUND function. If the second argument is 0 or is missing, the value is truncated to zero decimal places. If the second argument is 2, the value is truncated to two decimal places. Conversely, if the second argument is –2, the value is truncated to two decimal places to the left. If the second argument is –1, the value is truncated to one decimal place to the left.
• Like the ROUND function, the TRUNC function can be used with date functions.

Using the TRUNC Function :

• SELECT TRUNC(45.923,2), TRUNC(45.923,0),TRUNC(45.923,-1);
• 45.92 45 50

Using the MOD Function :

• The MOD function finds the remainder of the first argument divided by the second argument.
  
• For all employees with job title of Sales Representative, calculate the remainder of the salary after it is divided by 5,000.
• SELECT ename, sal, MOD(sal, 5000) FROM emp WHERE job = 'SALESMAN';
• The above example calculates the remainder of the salary after dividing it by 5,000 for all employees whose job ID is SALESMAN
  

Working with Dates and Date Functions

CURRENT_DATE Function :

• _DATE is a date function that returns the current database server date and time.
  

You can use :

• CURRENT_DATE just as you would use any other column name.
• For example, you can display the current date by selecting CURRENT_DATE from a table.

Example :
Display the current date using the DUAL table.
» SELECT CURRENT_DATE;

Output
» date
» "2009-07-15"

Arithmetic with Dates :

• Add or subtract a number to or from a date for a resultant date value
• Subtract two dates to find the number of days between those dates
• Add hours to a date by dividing the number of hours by 24

Using Arithmetic Operators with Dates :

• SELECT ename, (CURRENT_DATE-hiredate)/7 AS WEEKS FROM emp WHERE deptno = 20;
• The above example displays the last name and the number of weeks employed for all employees in department 90. It subtracts the date on which the employee was hired from the current date (CURRENT_DATE) and divides the result by 7 to calculate the number of weeks that a worker has been
  employed.

Date Functions :
All date functions return a value of DATE data type:

• age(timestamp, timestamp) : Subtract arguments, producing a “symbolic” result that uses years and Months
• date_part(text, timestamp) : Get subfield (equivalent to extract);
• extract(field from timestamp) : The extract function retrieves sub fields such as year or hour from date/timevalues. source must be a value expression of type timestamp, time, or interval.
• date_trunc(’field’, source) : Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

Using Date Functions :

Character Functions

Case-Manipulation Functions :

• These functions convert case for character strings

LOWER :

• Converts mixed-case or uppercase character strings to lowercase

UPPER :

• Converts mixed-case or lowercase character strings to uppercase

INITCAP :

• Converts the first letter of each word to uppercase and remaining letters to lowercase

SELECT 'The job id for '||UPPER(ename)||' is ‘ ||LOWER(job) AS "EMPLOYEE DETAILS“ FROM employees;

Using Case-Manipulation Functions :

• Display the employee number, name, and department number for employee Scott
• SELECT empno, ename, deptno FROM emp WHERE ename = ‘Scott';
  no rows selected
• SELECT empno, ename, deptno FROM emp WHERE INITCAP(ename) = ‘Scott’;
  

Character-Manipulation Functions :

• These functions manipulate character strings

Using the Character-Manipulation Functions :

SELECT empno, 'NAME IS: ‘||ename as NAME, job, LENGTH (ename), POSITION(‘A’ IN ename) AS "Contains 'a'?" FROM emp WHERE SUBSTR(job, 1,3) = 'MAN';

The slide example displays employee first names and a string joined together, the length of the employee name, and the numeric position of the letter a in the employee name for all employees who have the string MAN contained in the job ID starting at the first position of the job ID.

Single-Row Functions

Single-row functions:

• Manipulate data items
• Accept arguments and return one value
• Act on each row that is returned
• Return one result per row
• May modify the data type
• Can be nested
• Accept arguments that can be a column or an expression

SQL functions

Functions are a very powerful feature of SQL

They can be used to do the following:

• Calculations on data
• Modify individual data items
• Manipulate output for groups of rows
• Format dates and numbers for display
• Convert column data types
• SQL functions sometimes take arguments and always return a value

Two Types of SQL Functions :

• Single-Row Functions
  – These functions operate on single rows only and return one result per row. There are different types of single-row functions.
• future posts covers the following ones:
  • Character
  • Number
  • Date
  • Conversion
  • General
• Multiple-Row Functions
  – Functions can manipulate groups of rows to give one result per group of rows. These functions are also known as group functions
  

Sorting retrieved rows in postgresql

Using the ORDER BY Clause :

• Sort retrieved rows with the ORDER BY clause
  • ASC: ascending order, default
  • DESC: descending order
• The ORDER BY clause comes last in the SELECT statement

• SELECT ename, job, deptno, hiredate FROM emp ORDER BY hiredate ;
  

Sorting in descending order :

• SELECT ename, job, deptno, hiredate FROM emp ORDER BY hiredate DESC ;
  

Rules of Precedence in SQL

Rules of Precedence :

1. Arithmetic operators
   
2. Concatenation operator
3. Comparison conditions
4. IS [NOT] NULL, LIKE, [NOT] IN
5. [NOT] BETWEEN
6. Not equal to
7. NOT logical condition
8. AND logical condition
9. OR logical condition

Using Logical Conditions in SQL Statement

Logical Conditions :



Operator Meaning

• OR Returns TRUE if either component condition is true
• AND Returns TRUE if both component conditions are true
• NOT Returns TRUE if the following condition is false

Using the AND Operator :

• AND requires both conditions to be true
• SELECT empno, ename, sal FROM emp WHERE sal >=1000 AND job LIKE '%MAN%' ;

Using the OR Operator :

• OR requires any condition to be true
• SELECT empno, ename, sal FROM emp WHERE sal >=1000 OR job LIKE '%MAN%' ;

Using the NOT Operator :

• SELECT empno, ename, job FROM emp WHERE sal >=1000 AND job NOT IN (‘CLERK’,’SALESMAN’) ;
  

Using Comparison Conditions in SQL Statement

Comparison Conditions :



Operator Meaning

• < Less than
• <= Less than or equal to
• >= Greater than or equal to
• > Greater than
• = Equal to
• <> Not equal to
• BETWEEN Between two values (inclusive) ...AND...
• IN(set) Match any of a list of values
• LIKE Match a character pattern
• IS NULL Is a null value

Using Comparison Conditions :

• select*from emp where sal<3000;

Using the BETWEEN Condition :

• select*from emp where sal between 1 and 2999;

Using the IN Condition :

• Use the IN membership condition to test for values in a list:
• select*from emp where mgr in(7902,7698);
  

Using the LIKE Condition :

• Use the LIKE condition to perform wildcard searches of valid search string values
• Search conditions can contain either literal characters or numbers
  • % denotes zero or many characters
  • _ denotes one character
• SELECT * FROM emp WHERE ename LIKE 'S%' ;
  

Using the NULL Conditions :

• Test for nulls with the IS NULL operator
• SELECT ename, mgr FROM emp WHERE mgr IS NULL ;

Limit the rows that are retrieved by a query

Limiting Rows Using a Selection :

• Restrict the rows that are returned by using the WHERE clause
• The WHERE clause follows the FROM clause
• SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];

• eg : select*from emp where deptno=10;

Character Strings and Dates :

• Character strings and date values are enclosed by single quotation marks
• Character values are case-sensitive, and date values are format-sensitive

• eg : select*from emp where ename='CLARK';

Important Meta Commands in PostgreSQL

\c database name [username]

• to connect to a database

\l

• will return you the list of available databases

\du

• will return you the list of databases users

\dt

• will return you the list of tables in connected database

\d table name

• will returns you the structure of the given table

\e

• opens the sql query in editor or opens an editor to write query

show port

• will returns you the port no on which postgres is running

few notations which are supposed to use while parsing these inputs :

• -d : database name
• -U : User name
• -h : Host name
• -p : Port No.
• -f : File name (sql)
• -w : Password

Working with Duplicate Rows in SQL

Duplicate Rows :

• The default display of queries is all rows, including duplicate rows
• postgres=# select deptno from emp;
  deptno
  --------
  20
  30
  30
  20
  30
  30
  10
  20
  10
  30
  20
  30
  20
  10
  (14 rows)

Selecting Unique Rows (Deleting Duplicates) :

• postgres=# select distinct deptno from emp;

deptno
--------
10
20
30
(3 rows)

Using Literal Character Strings in SQL

A concatenation operator :

• Links columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression

Literal Character Strings :

• A literal is a character, a number, or a date that is included in the SELECT statement
• Date and character literal values must be enclosed by single quotation marks
• Each character string is output once for each row returned

Example :

• postgres=# select ename||' earns '||sal as Report from emp;

report
----------------------
SMITH earns 800.00
ALLEN earns 1600.00
WARD earns 1250.00
JONES earns 2975.00
MARTIN earns 1250.00
BLAKE earns 2850.00
CLARK earns 2450.00
SCOTT earns 3000.00
(8 rows)

postgres=#

here as you can see we have added a string "earns" to the two different fields of the table and the displayed result in one single column.

Concatination is done using ||

Defining a Column Alias

A column alias :

• Renames a column heading
• Is useful with calculations
• AS keyword is mandatory between the column name and alias
• Requires double quotation marks if it contains spaces or special characters or if it is case-sensitive

Example :

• postgres=# select ename, sal, comm as "commission" from emp;

ename | sal | commission
--------+---------+------------
SMITH | 800.00 |
ALLEN | 1600.00 | 300.00
WARD | 1250.00 | 500.00
JONES | 2975.00 |
MARTIN | 1250.00 | 1400.00
BLAKE | 2850.00 |
CLARK | 2450.00 |
SCOTT | 3000.00 |

In the previous post we have seen when we performed a mathematical operation the resultant column was labeled with ?Column? automatically... but here the same column has labeled as commission.

This is how we can name/rename the column name in SQL as per our requirement.

Defining a Null Value

Null Value :

• A null is a value that is unavailable, unassigned, unknown, or inapplicable
• A null is not the same as a zero or a blank space

Example :

• postgres=# select ename, sal, comm from emp;

ename | sal | comm
--------+---------+---------
SMITH | 800.00 |
ALLEN | 1600.00 | 300.00
WARD | 1250.00 | 500.00
JONES | 2975.00 |
MARTIN | 1250.00 | 1400.00
BLAKE | 2850.00 |
CLARK | 2450.00 |

Using Arithmetic Operators with SQL SELECT

Using Arithmetic Operators & Expressions :

Create expressions with number and date data by using arithmetic operators

• + : Add
• - : Subtract
• * : Multiply
• / : Divide

Example :

postgres=# select ename, sal, sal+100 from emp;

ename | sal | ?column?
--------+----------+---------
SMITH | 800.00 | 900.00
ALLEN | 160.00 | 1700.00
WARD | 1250.00 | 1350.00

• The example in the slide uses the addition operator to calculate a salary increase of $100 for all employees. The slide also displays a SAL+100 column in the output
• Note that the resultant calculated column SAL+100 is not a new column in the EMP table; it is for display only

Operator Precedence :

• postgres=# select ename, sal, 12*sal+100 from emp limit 3;

ename | sal | ?column?
-------+---------+----------
SMITH | 800.00 | 9700.00
ALLEN | 1600.00 | 19300.00
WARD | 1250.00 | 15100.00
(3 rows)

Writing SQL Statements

Writing SQL Statements :

• SQL statements are not case-sensitive
• SQL statements can be on one or more lines
• Keywords cannot be abbreviated or split across lines
• Clauses are usually placed on separate lines
• Indents are used to enhance readability
• In SQL Query Tool(PGADMIN3), SQL statements can optionally be terminated by a semicolon (;)
• Semicolons are required if you execute multiple SQL statements
• In PSQL Client, you are required to end each SQL statement with a semicolon (;)

SQL SELECT Statement

Capabilities of SQL SELECT Statements :



Basic SELECT Statement :

• SELECT * | {[DISTINCT] column | expression [alias],...} FROM table;
• SELECT identifies the columns to be displayed
• FROM identifies the table containing those columns
• * specifies Selecting All Columns
• column name specifies : selecting Specific Columns
• eg1 : postgres=# select*from dept; (will return the all the columns of table dept)
  
• eg2 : edb=# select deptno, dname from dept; (will return the specific columns of table dept)