ALTER TABLE Statement

ALTER TABLE Statement :

• Use the ALTER TABLE statement to :
  – Add a new column
  – Modify an existing column
  – Define a default value for the new column
  – Drop a column

Dropping a Table :

• All data and structure in the table are deleted.
• Any pending transactions are committed.
• All indexes are dropped.
• All constraints are dropped.
• You cannot roll back the DROP TABLE statement.
• DROP TABLE dept30;
  

Creating a Table by Using a Subquery

Creating a Table by Using a Subquery :

• Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
• Match the numeric of specified columns to the numeric of subquery columns.
• Define columns with column names and default values.
• CREATE TABLE table [(column, column...)] AS subquery;

Violating Constraints

Query :

• UPDATE emp SET department_id = 29 WHERE department_id = 30;

Error :

• insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"

DETAIL :

• Key (deptno)=(29) is not present in table "dept"
• Department 29 does not exist

You cannot delete a row that contains a primary key that is used as a foreign key in another table.

Query :

• DELETE FROM departments WHERE department_id = 60;

Error :

• update or delete on table "dept" violates foreign key constraint "emp_re f_dept_fk" on table "emp";

Detail :

• Key (deptno)=(10) is still referenced from table "emp".
  

CHECK Constraint

CHECK Constraint :

Defines a condition that each row must satisfy

• The following expressions are not allowed :
• References to CURR_VAL, NEXT_VAL and CTID pseudocolumns
• Queries that refer to other values in other rows
• ..., salary numeric(2)CONSTRAINT emp_salary_minCHECK (salary > 0),...

CREATE TABLE : Example

FOREIGN KEY Constraint

• 
• Defined at either the table level or the column level
• CREATE TABLE emp(employee_id numeric(6), last_name varchar(25) NOT NULL, email varchar(25), salary numeric(8,2), commission_pct numeric(2,2), hire_date DATE NOT NULL, ... department_id numeric(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES dept(deptno), CONSTRAINT emp_email_uk UNIQUE(email));

FOREIGN KEY Constraint - Keywords :

• FOREIGN KEY : Defines the column in the child table at the table-constraint level
• REFERENCES : Identifies the table and column in the parent table
• ON DELETE CASCADE : Deletes the dependent rows in the child table when a row in the parent table is deleted
  
• ON DELETE SET NULL : Converts dependent foreign key values to null
  

Adding Constraints in SQL

NOT NULL Constraint :

• Ensures that null values are not permitted for the column
• CREATE TABLE emp(employee_id numeric(6)CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name varchar(20) NOT NULL,...);

UNIQUE Constraint :

• Unique constraint allow only unique values to be inserted in the column.
• No duplicates allowed e.g emailIDs
• CREATE TABLE emp(employee_id numeric(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name varchar(20) NOT NULL, Email_id varchar(20) UNIQUE,...);

PRIMARY KEY Constraint :

• Don’t allow duplicate values
  
• Don’t allow null values
• Only one primary key can be created per table
• CREATE TABLE emp(employee_id numeric(6) CONSTRAINT emp_emp_id_pk RIMARY KEY, first_name varchar(20),...);

FOREIGN KEY Constraint :

• Defined at either the table level or the column level : continued in the next post
  

Including Constraints in SQL

Constraints :

• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there are dependencies.

The following constraint types are valid :

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK

Constraint Guidelines :

• You can name a constraint, or the PostgreSQL server generates a name automatically
• Create a constraint at either of the following times :
  • At the same time as the table is created
  • After the table has been created
• Define a constraint at the column or table level
• View a constraint in the data dictionary

Defining Constraints :

Syntax :

• CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]);

Column-level constraint :

• column [CONSTRAINT constraint_name] constraint_type,
• CREATE TABLE emp(employee_id numeric(6) CONSTRAINT emp_emp_id_pk RIMARY KEY, first_name varchar(20), ...);
  

Table-level constraint :

• column,... [CONSTRAINT constraint_name] constraint_type (column, ...),
• CREATE TABLE emp(employee_id numeric(6), first_name varchar(20), ...
  job_id varchar(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY EMPLOYEE_ID));
  

Data Types in SQL

Data Types :


Datetime Data Types :

• The TIMESTAMP data type is an extension of the DATE data type.
• It stores the year, month, and day of the DATE data type plus hour, minute, and second values as well as the fractional second value.
• You can optionally specify the time zone.

• TIMESTAMP[(fractional_seconds_precision)]
• TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE
  

DEFAULT Option in SQL

DEFAULT Option :

• Specify a default value for a column during an insert.
• ... hire_date DATE DEFAULT CURRENT_DATE, ...
• Literal values, expressions, or SQL functions are legal values.
• Another column’s name or a pseudo column are illegal values.
• The default data type must match the column data type.
• CREATE TABLE hire_dates (id numeric(8), hire_date DATE DEFAULT CURRENT_DATE);

Create the table :

• CREATE TABLE dept_info (deptno numeric(2), dname varchar(14), loc varchar(13), create_date DATE DEFAULT CURRENT_DATE);
  

Confirm table creation :

• \d dept_info
  

Referencing Another User’s Tables

• Tables belonging to other users are not in the user’s schema.

• You should use the schema name as a prefix to those tables.
• 
  

CREATE TABLE Statement

You must have:

• storage area

CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);

You specify:

• Table name
• Column name, column data type, and column size
• 
• 

Schema

Schema :

• A PostgreSQL database cluster contains one or more named databases.
• A database contains one or more named schemas, which in turn contain tables.
• The same object name can be used in different schemas without conflict;
• A user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.

Why Schemas :

• There are several reasons why one might want to use schemas :
  – To allow many users to use one database without interfering with each other.
  – To organize database objects into logical groups to make them more manageable.
  – Third-party applications can be put into separate schemas so they cannot collide with the names of other objects.

Creating a Schema :

• To create a schema, use the CREATE SCHEMA command.
• Give the schema a name preferably same as the user name so that it gets associated
  automatically.
• For example: CREATE SCHEMA training;
• Objects in this schema can be referred using schema.table for e.g training.students.
  

Using DDL Statements to Create and Manage Tables

Database Objects :


Naming Rules :

Table names and column names:
– Must begin with a letter
– Must be 1–30 characters long
– Must contain only A–Z, a–z, 0–9, _, and $
– Must not duplicate the name of another object owned by the same user
– Must not be an PostgreSQL server reserved word

Read Consistency

Read Consistency :

• Read consistency guarantees a consistent view of the data at all times.
• Changes made by one user do not conflict with changes made by another user.
• Read consistency ensures that on the same data :
• Readers do not wait for writers
• Writers do not wait for readers

Implementation of Read Consistency :

State of the Data Before COMMIT or ROLLBACK

• The previous state of the data can be recovered.
• The current user can review the results of the DML operations by using the SELECT statement.
• Other users cannot view the results of the DML statements by the current user.
• The affected rows are locked; other users cannotchange the data in the affected rows.

State of the Data After COMMIT :

• Data changes are made permanent in the database.
• The previous state of the data is permanently lost.
• All users can view the results.
• Locks on the affected rows are released; those rows are available for other users to
  manipulate.
• All savepoints are erased.

Committing Data :

Make the changes :

• BEGIN
  
• DELETE FROM emp WHERE empno = 7090;
• INSERT INTO dept VALUES (290, 'Corporate Tax', NULL);

Commit the changes :

• COMMIT;

State of the Data After ROLLBACK :
Discard all pending changes by using the ROLLBACK statement :

• Data changes are undone.
• Previous state of the data is restored.
• Locks on the affected rows are released.

• BEGIN;
• DELETE FROM copy_emp;
• DELETE 20
• ROLLBACK ;
• Rollback

Implicit Transaction Processing

Controlling Transactions :



An automatic commit occurs under the following circumstances :

• DML is issued
• DDL statement is issued
• DCL statement is issued
• Normal exit from psql, without explicitly issuing COMMIT or ROLLBACK statements
• An automatic rollback occurs under an abnormal termination of psql or a system failure.

System Failures :

• When a transaction is interrupted by a system failure, the entire transaction is automatically rolled back. This prevents the error from causing unwanted changes to the data and returns the tables to their state at the time of the last commit. In this way, the PostgreSQL server protects the integrity of the tables.
  

Database Transactions

A database transaction consists of one of the following :

• DML statements that constitute one consistent change to the data
• One DDL statement
• One data control language (DCL) statement
• PostgreSQL commit automatically when you perform a transaction.
• You can explicitly start a transaction using BEGIN keyword.
• Transaction that are explicitly started using BEGIN end with one of the following events:
  
  • A COMMIT or ROLLBACK statement is issued.
  • The user exits psql.
  • The system crashes.

Advantages of COMMIT and ROLLBACK Statements :
With COMMIT and ROLLBACK statements, you can :

• Ensure data consistency
• Preview data changes before making changes permanent
• Group logically related operations

Controlling Transactions :

TRUNCATE Statement

• Removes all rows from a table, leaving the table empty and the table structure intact
• Is a data definition language (DDL) statement rather than a DML statement; cannot easily be undone
• Syntax : TRUNCATE TABLE table_name;
• Example : TRUNCATE TABLE copy_emp;
  

DELETE Statement

You can remove existing rows from a table by using the DELETE statement :

• DELETE [FROM] table [WHERE condition];

Deleting Rows from a Table :
Specific rows are deleted if you specify the WHERE clause :

• DELETE FROM dept WHERE dname = 'Finance';

All rows in the table are deleted if you omit the WHERE clause :

• DELETE FROM copy_emp;

Deleting Rows Based on Another Table :

• Use subqueries in DELETE statements to remove rows from a table based on values from another table :
• DELETE FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname =‘SALES’);

UPDATE Statement Syntax

Modify existing rows with the UPDATE statement :

• UPDATE table SET column = value [, column = value, ...] [WHERE condition];
• Update more than one row at a time (if required).

Updating Rows in a Table :

Specific row or rows are modified if you specify the WHERE clause :

• UPDATE emp SET deptno = 40 WHERE empno = 7654;

All rows in the table are modified if you omit the WHERE clause :

• UPDATE copy_emp SET deptno = 110;

Updating Two Columns with a Subquery :

• Update employee 7654’s job and sal to match that of employee 7698

UPDATE emp SET job = (SELECT job FROM emp WHERE empno = 7698), sal = (SELECT sal FROM emp WHERE empno = 7698) WHERE empno = 7654;

DML - Inserting Special Values

Inserting Special Values :

• The CURRENT_DATE function records the current date and time.
• INSERT INTO emp (empno, ename,hiredate, job, sal, comm, mgr,deptno) VALUES (1132, 'Louis',CURRENT_DATE, 'AC_ACCOUNT', 7000, NULL, 7768, 70);

Inserting Specific Date Values :

Add a new employee :

• INSERT INTO emp (empno, ename,hiredate, job, sal, comm, mgr,deptno) VALUES ( 1114,'Den', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 3600, NULL, 7900, 40);

Verify your addition :

• Select*from emp;

Creating a Script :

• Open vi editor using vi command
• Type in your insert command
• Save your file using :wq filename
• Use psql –f option to execute the stored file in training database.
• E.g
  • ./psql –f in.sql –d training –U postgres –p 5432

Copying Rows from Another Table :

Write your INSERT statement with a subquery :

• INSERT INTO sales_reps(id, name, sal, comm)
  SELECT empno, ename, sal, comm
  FROM emp WHERE job=‘SALESMAN’;
• Do not use the VALUES clause.
• Match the number of columns in the INSERT clause to those in the subquery.
  

DML Statements

Manipulating Data using DML

Data Manipulation Language :
A DML statement is executed when you :

• Add new rows to a table
• Modify existing rows in a table
• Remove existing rows from a table

• A transaction consists of a collection of DML statements that form a logical unit of work.

INSERT Statement Syntax :
Add new rows to a table by using the INSERT statement :

• INSERT INTO table [(column [, column...])] VALUES (value [, value...])[,(value [,value..])];
  

• With this syntax, only multiple rows are inserted at a time.

Inserting New Rows :

• Insert a new row containing values for each column.
• List values in the default order of the columns in the table.
• Optionally, list the columns in the INSERT clause.
• Enclose character and date values in single quotation marks.
• INSERT INTO dept(deptno, dname, loc) VALUES (50, 'Public Relations',’EDMONTON’);

Inserting Rows with Null Values :
Implicit method :

• Omit the column from the column list.
• INSERT INTO dept (deptno, dname ) VALUES (60, 'Purchasing');

Explicit method :

• Specify the NULL keyword in the VALUES clause.
• INSERT INTO dept VALUES (70, 'Finance', NULL);

Multiple-Row Subqueries

• Return more than one row
• Use multiple-row comparison operators

Using the ANY Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';

Using the ALL Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';
Null Values in a Subquery :

SELECT emp.ename FROM emp emp WHERE emp.empno NOT IN (SELECT mgr.mgr FROM emp mgr);

• The SQL statement in the slide attempts to display all the emp who do not have any subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and hence the entire query returns no rows
• Here we have to use IS NOT NULL in the inner query

Single-Row Subqueries

• Return only one row
• Use single-row comparison operators

Executing Single-Row Subqueries :

SELECT ename, job, sal FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7698) AND sal > (SELECT sal FROM emp WHERE empno = 7900);

Using Group Functions in a Subquery :

SELECT ename, job, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

The HAVING Clause with Subqueries :

• The PostgreSQL server executes subqueries first
• The PostgreSQL server returns results into the HAVING clause of the main query

SELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);

What Is Wrong with This Statement ?

SELECT empno, ename FROM emp WHERE sal = (SELECT MIN(sal) FROM emp GROUP BY deptno);

• ERROR : more than one row returned by a subquery used as an expression
• Single-row operator with multiple-row subquery
  

Using Subqueries to Solve Queries

• Who has a sal greater than Scott’s ?

• 

Subquery Syntax :

• SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

• The subquery (inner query) executes once before the main query (outer query)
• The result of the subquery is used by the main query

Using a Subquery :

• SELECT ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = ‘Scott');

Guidelines for Using Subqueries :

1. Enclose subqueries in parentheses
2. Place subqueries on the right side of the comparison condition
3. The ORDER BY clause in the Subquery is not needed unless you are performing Top-N analysis
4. Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries

Types of Subqueries :

• Single-row subquery

• Multiple-row subquery

INNER Versus OUTER Joins

• In SQL the join of two tables returning only matched rows is called an inner join
• A join between two tables that returns the results of the inner join as well as the unmatched rows from the left (or right) tables is called a left (or right) outer join
• A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join

LEFT OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

RIGHT OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

FULL OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

Non-Equijoins

Retrieving Records with Non-Equijoins :

• SELECT e.ename, e.sal, j.gra FROM emp e JOIN job_grd j ON e.sal BETWEEN j.lowest_sal AND j.highest_sal;

Outer Joins :

Self-Joins Using the ON Clause

• SELECT e.ename as emp, m.ename as mgr FROM emp e JOIN emp m ON (e.mgr = m.empno);

• 

Applying Additional Conditions to a Join :

• SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno) AND e.mgr = 7698 ;
  

Creating Joins with the ON Clause

• The join condition for the natural join is basically an equijoin of all columns with the same name
• Use the ON clause to specify arbitrary conditions or specify columns to join
• The join condition is separated from other search conditions
• The ON clause makes code easy to understand

Retrieving Records with the ON Clause :

• SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno);

• 

Creating Joins with the USING Clause

• If several columns have the same names but the data types do not match, the NATURALJOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin
• Use the USING clause to match only one column when more than one column matches
• Do not use a table name or alias in the referenced columns
• The NATURAL JOIN and USING clauses are mutually exclusive

Joining Column Names :



Retrieving Records with the USING Clause :

• SELECT emp.empno, emp.ename, dept.loc, deptno FROM emp JOIN dept USIN (deptno) ;

Few Important Points reg Joins

Qualifying Ambiguous Column Names :

• Use table prefixes to qualify column names that are in multiple tables
• Use table prefixes to improve performance
• Use column aliases to distinguish columns that have identical names but reside in different tables
• Do not use aliases on columns that are identified in the USING clause and listed elsewhere in the SQL statement

Using Table Aliases :

• Use table aliases to simplify queries
• Use table aliases to improve performance

SELECT e.empno, e.ename, d.loc, deptno FROM emp e JOIN dept d USING (deptno) ;

Creating Natural Joins

• The NATURAL JOIN clause is based on all columns in the two tables that have the same name
• It selects rows from the two tables that have equal values in all matched columns
• If the columns having the same names have different data types, an error is returned

Retrieving Records with Natural Joins :

• SELECT empno, dname,loc FROM emp NATURAL JOIN dept ;

• 

Creating Joins with the USING Clause :

• If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin
• Use the USING clause to match only one column when more than one column matches
• Do not use a table name or alias in the referenced columns
• The NATURAL JOIN and USING clauses are mutually exclusive

Joining Column Names :



Retrieving Records with the USING Clause :

• SELECT emp.empno, emp.ename, dept.loc, deptno FROM emp JOIN dept USIN (deptno) ;

Displaying Data from Multiple Tables

Obtaining Data from Multiple Tables :



Types of Joins :

• Joins that are compliant with the SQL:1999 standard include the following :

1. Cross joins
2. Natural joins
3. USING clause
4. Full (or two-sided) outer joins
5. Arbitrary join conditions for outer joins

Joining Tables Using SQL : 1999 Syntax

• Use a join to query data from more than one table

SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2];

Restricting Group Results

• In the same way that you use the WHERE clause to restrict the rows that you select, you use the HAVING clause to restrict groups

• To find the maximum salary in each of the departments that have a maximum salary greater than $10,000, you need to do the following :

1. Find the average salary for each department by groupingby department number
2. Restrict the groups to those departments with a maximum salary greater than $10,000

Restricting Group Results with the HAVING Clause :

• When you use the HAVING clause, the PostgreSQL server restricts groups as follows:

1. Rows are grouped
2. The group function is applied
3. Groups matching the HAVING clause are displayed

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
Example :

• SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>4000 ;
• SELECT job, SUM(sal) as PAYROLL FROM emp WHERE job NOT LIKE '%MAN%' GROUP BY job HAVING SUM(sal) > 4000 ORDER BY SUM(sal);

SELECT column, group_function FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Illegal Queries - Using Group Functions

• Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause

• SELECT deptno, COUNT(ename) FROM emp;
• ERROR : column "emp.deptno" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803
• Column missing in the GROUP BY clause

• You cannot use the WHERE clause to restrict groups
• You use the HAVING clause to restrict groups
• You cannot use group functions in the WHERE clause

• SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 8000 GROUP BY deptno;
• WHERE AVG(salary) > 8000
  ERROR : aggregates not allowed in WHERE clause
• Cannot use the WHERE clause to restrict groups
  

Creating Groups of Data

GROUP BY Clause Syntax :

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

• You can divide rows in a table into smaller groups by using the GROUP BY clause

Using the GROUP BY Clause :

• All columns in the SELECT list that are not in group functions must be in the GROUP BY
  clause
• SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ;

• The GROUP BY column does not have to be in the SELECT list
• SELECT AVG(sal) FROM emp GROUP BY deptno ;

Grouping by More Than One Column :

• You can also group by multiple columns
• Example : select deptno, job, avg(sal) from emp group by deptno, job;
  

Cross Joins

Cartesian Products :

• A Cartesian product is formed when:
  • A join condition is omitted
  • A join condition is invalid
  • All rows in the first table are joined to all rows in the second table

• To avoid a Cartesian product, always include a valid join condition

Creating Cross Joins :

• The CROSS JOIN clause produces the cross-product of two tables
• This is also called a Cartesian product between the two tables

• SELECT ename, dname FROM emp CROSS JOIN dept ;

• 

Using Group Functions - Avg, Sum, Min, Max and Count

Using the AVG and SUM Functions :

• You can use AVG and SUM for numeric data
• SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal) FROM emp WHERE job LIKE '%MAN%';

Using the MIN and MAX Functions :

• You can use MIN and MAX for numeric, character, and date data types
• SELECT MIN(hiredate), MAX(hiredate) FROM emp;

Using the COUNT Function :

• COUNT(*) returns the number of rows in a table
  
• SELECT COUNT(*) FROM emp WHERE deptno = 10;
• COUNT(expr) returns the number of rows with non-null values for the expr
• SELECT COUNT(comm) FROM emp WHERE deptno = 30;

Using the DISTINCT Keyword :

• COUNT(DISTINCT expr) returns the number of distinct non-null values of the expr
• To display the number of distinct department values in the EMP table
• SELECT COUNT(DISTINCT deptno) FROM emp;
  

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 ;