Chapter 9

MORE ON DATABASE AND SQL

Single Row functions - Single row functions are the one who work on single

row and return one output per row. For example, length and case conversion

functions are single row functions.

Multiple Row functions - Multiple row functions work upon group of rows

and return one result for the complete set of rows. They are also known as

Group Functions.

AGGREGATE (GROUP) FUNCTIONS

Group functions or Aggregate functions work upon groups of rows, rather

than on single rows. That is why, these function are also called multiple row

functions.

1. AVG – This function computes the average of given data.

Example – Calculate average salary of all employees listed in table empl

Q- to find the average salary in empl for job of salesman

2. COUNT – This function is used to counts the number of values in a

column. While counting it ignore the NULL values.

Count(*) – Returns the number of rows satisfying the condition, if any in

the table.

Count (Column Name) – Return Non-NULL values in the column.

Example 1 – Count number of records in table empl.

Example 2 – Count number of jobs in table empl.

Example 3 – Count number of commission in table empl.

Note: Ignore the null values while counting.

Example 4 – How many distinct jobs are listed in table empl?

3. MAX – This function returns the maximum value from a given column or

expression.

Example 1 – Display maximum salary from empl.

Q1 –

Display maximum salary in empl for job salesman.

Q2- Write the output of following query:

4. MIN - This function returns the minimum value from a given column or

expression.

Example 1 – Display the joining date of seniormost employee.

Q 1. Display minimum commission from empl.

5. SUM – This function returns the sum of value in given column or

expression.

Example 1 – Display total salary of all employees listed in table empl.

Q1. Display total salary for job salesman.

Aggregate functions and NULL values:

All aggregate functions ignore NULL values. The * is the only argument that

includes NULLs when it is used with COUNT function.

Example 1 –

Indicate that there are 5 records in the table empl.

Example 2 –

Output indicates that there are 3 values in the comm Column of empl table.

This feature of aggregate functions ensures that NULLs don’t play any role

in actual calculations. For example

The average comm has been calculated for all the 3 non NULL values.

GROUPING RESULT – GROUP BY

The GROUP BY clause combines all those records that have the same values

in a particular field or a group of fields. This statement divides the table into

groups.

Example 1 : Calculate the number of employees in each grade form table

empl

Example 2 :Display total salary department wise

Q1- Count total number of employees in each department.

Grouping on Multiple Columns

With GROUP BY we can create groups within groups. Such type of grouping is

called Nested Grouping.

Example – 1 : Count the number of employees in each department jobwise.

HAVING Clause : HAVING clause is used to specify condition with GROUP BY

clause . HAVING can include aggregate functions also.

Example 1: Count the number of employees belonging to department

number 20.

Example 2: To display the jobs where the number of employees is

less than 3.

Example 3 : To calculate the average commission and total commission

department wise where average commission is more than 500.

.

Example 4 : We can use an aggregate function in the HAVING even if it is not

in the SELECT list.

Example 5 : We can also use IN or BETWEEN operators with HAVING.

JOINS

A join is a query that combines rows from two or more tables.

In a join-query , more than one table are listed in FROM clause. The function

of combine data from multiple tables is called joining.

For example :

SELECT * FORM EMPL, DEPT;

Cartesian Product or Cross Join of Tables :

Def - The Cartesian Product (or Cross Join) is a very basic type of join that

simply matches each row from one table to every row from another table.

If two tables contain 3 rows and 2 rows respectively, then their Cartesian

product will contain 6 = (3 X 2) rows.

The number of columns in the Cartesian product is the sum of the number of

columns in both the tables.

Consider following tables:

Example of Cartesian Product:

SELECT * FROM order_table, product;

Equi Join of tables:

Def - The Join, in which column are compared for equality, is called Equi –

Join.

In Equi-join, all the columns from joining table appear in the output even if

they are identical.

Example 1 : Write query to find out the “Product Name” corresponding to

each Order placed.

SELECT * FROM Order_table, Product

WHERE p_code = code;

Internally the Cartesian product of the table is made. Then based on the

specified condition the meaningful data is extracted from this Cartesian

product and displayed.

Qualified Names

<table name>.<field name> is called qualified name. Qualified field

names are very useful in identifying a filed if the two joining table have

fields with same name.

Example 2 : Write a Query to display supplier name and address corresponding to each order placed.

Example 3- Write a query to produce the following output:

Ans –

Select Order_no,Product.name “Product”,Supplier.Name “Supplier”

From order_table, Product, Supplier

WHERE order_table.Sup_Code = Supplier.Sup_Code

AND P_Code = Code;

Foreign Key: A non-key attribute, whose values are derived from the

primary key of some other table. A foreign key in a table is used to ensure

referential integrity and to get Equi-Join of two tables.

Code is the Primary Key of Product table. In the order_table, P_Code is a

Foreign Key.

Referential Integrity: This property of a relational database which ensures

that no entry in a foreign key column of a table can be made unless it

matches a primary key value in the corresponding related table is called

Referential Integrity

Union

The UNION operator is used to combine the result-set of two or more SELECT statements.

Result-set can be combined only if each SELECT statement within the UNION

must have the same number of columns. The columns must also have similar

data types. Also, the columns in each SELECT statement must be in the same

order.

Syntax :

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

Suppose a Database contains two tables: XIISC and XIICOM. If it is required

to produce a combined list of all the students, then it can be done as follows:

SELECT rollno,name,class from xiisc

UNION

SELECT rollno,name,class from xiicom;

SQL Constraints Def – A Constraint is a condition or check applicable on a field or set of fields.

Types of constraint

1. Not Null – Makes sure that NULLs are not accepted in the specified

column. Example

CREATE TABLE CUSTOMER

( SID INTEGER NOT NULL,

FIRST_NAME VARCHAR(30) NOT NULL,

LAST_NAME VARCHAR(30) );

2. DEFAULT Constraint – The DEFAULT constraint provides a default value

to a column when the INSERT INTO statement does not provide a specific

value. Example

CREATE TABLE CUSTOMER

( SID INTEGER

FIRST_NAME VARCHAR(30) ,

LAST_NAME VARCHAR(30)

SCORE INTEGER DEFAULT 80);

and execute the following statement,

INSERT INTO CUSTOMER

(SID,FIRST_NAME,LAST_NAME)

VALUES (1, ’AJAY’, ’SHARMA’);

Then output will be

SID FIRST_NAME LAST_NAME SCORE

1 AJAY SHARMA 80

3. UNIQUE Constraint – Makes sure that duplicate values in the specified

column are not accepted. Example

4. PRIMARY KEY Constraint –

1. Defining Primary Key through Create Table Command

2. Composite Primary Key – When multiple fields are used as a primary

key, they are called a composite primary key.

CREATE TABLE CUSTOMER

( BRANCH INTEGER,

SID INTEGER ,

FIRST_NAME VARCHAR(30) ,

LAST_NAME VARCHAR(30)

PRIMARY KEY (BRANCH, SID));

1. Defining Primary Key through Alter Table Command

ALTER TABLE CUSTOMER

ADD PRIMARY KEY (SID);