GROUP BY clause in SQL

GROUP BY clause in SQL is used data arrangement or sorting by specific condition. When GROUP BY clause is used, first the data gets divided into groups based on the column specified in the class then the group function gets applied once on each group to get the final result.

Example-GROUP BY clause on single column:

Select Deptnumber, Job, Min (Sal) from BANK GROUP BY Job

Above query retrieves least salary corresponding to each job.

Note: We can apply the GROUP BY clause on one or more columns.

Example-GROUP BY clause on multiple columns:

Select Deptnumber, Job, Max (Sal) from BANK GROUP BY Deptnumber, Job

Above query retrieves the highest salary corresponding to each job of each department.

In the above query, data first gets grouped based on the first column of the clause and then each group is sub-divided based on the second column of the clause. Now the group function gets applied on each inner group to get the final result.

While using the GROUP BY clause the select list can contain only the following unit:

1.        Group functions
2.        Columns that are present in GROUP BY clause
3.        Constants

Example:

Select GetDate (), Deptnumber, Max (Sal) from EMP GROUP BY Deptnumber

Select Count (*) from EMP GROUP BY Deptnumber

HAVING clause in SQL

HAVING clause in SQL used for arrangement or sorting by specific condition just like WHERE clause whereas these two clauses will be used as scenarios as following:

1. Use WHERE clause if restriction or data filtering is before grouping and use HAVING clause if the restriction or data filtering is after grouping.
2. Use WHERE clause for restricting a row and use HAVING clause for restricting a group.
3. WHERE clause can be used individually whereas HAVING clause can be used only in conjunction with GROUP BY clause.
4. WHERE clause could not be used with aggregate functions whereas HAVING can be used with aggregate functions.

Example:

1. Select Deptnumber, Count (*) from EMP GROUP BY Deptnumber HAVING Count (*) > 5
2. Select Count (*) from EMP WHERE Job=’Clerk’ GROUP BY Deptnumber Having Count (*) > 1

Query 1 retrieves the data of number of employees working in each department having count > 5.

Query 2 retrieves the data of number of Clerks working in each department having count > 1.

WHERE clause in SQL

The SQL WHERE clause is used to filter data records. It is used to fetch necessary records that fulfill a specified condition.

WHERE clause syntax:

SELECT column1, column2,…columnN FROM table_name WHERE column_name [operator value]

Example:

Select CustomerId, Cname from BANK where Salary >=7000

Select * from Bank where Salary >=7000

Select CustomerId, Cname from BANK where City=’Mumbai’

Query 1 retrieves all the rows for Customerid and Cname columns which satisfies the condition employee having Salary greater than or equal to 7000.

Query 2 retrieves all the rows for all the columns available in table which satisfies the condition employee having Salary greater than or equal to 7000.

Query 3 retrieves all the rows for Customerid and Cname columns which are from city Mumbai.

Note: The character or string field value need to be quoted in single quote.

We can use the =, <>, >, =>, <, <=, IN, BETWEEN, LIKE operators in WHERE clause.

CHECK constraint in SQL

CHECK constraint in SQL verifies the value in the column to be according to specification.

Example:

1. CHECK (Balance >= 1000)
2. CHECK (Balance BETWEEN 1000 and 7000)
3. CHECK (Balance IN (1000, 2000, 4000, 7000, 9000)

In Query 3, the Balance can have value 1000 or 2000 or 4000 or 7000 or 9000 only.

Note:

1.  We can impose one or more constraint on a column.
2.  Except NOT NULL constraint, UNIQUE, CHECK and FOREIGN KEY allow NULL values in to column. To restrict them you can explicitly impose a NOT NULL constraint on those columns.

CREATE Table BANK (CustomerId Int Primary Key, Cname Varchar (50), Balance Decimal (7,2) NOT NULL Constraint Bal_Chk  CHECK (Balance >=1000)

PRIMARY KEY in SQL

PRIMARY KEY is a combination UNIQUE and NOT NULL which does not allow the duplicate and NULL values in to columns on which it is imposed or applied.

Table can be imposed with any number of UNIQUE or NOT NULL constraints but can have only a single PRIMARY KEY constraint which can be imposed either on a single column or multiple columns (COMPOSITE PRIMARY KEY).

As per specification of Dr.F.C.Codd, a table can have only one key or identity value for uniquely identifying or picking the complete row. The column or columns on which you imposed the PRIMARY KEY will be considered as identity of your table.

Example:

Create Table BANK (CustomerId Int PRIMARY KEY, Cname Varchar (50), Balance Decimal (7,2) NOT NULL)

We can also give a constraint name at column or table level.

Example-constraint name at column level:

Create Table BANK (CustomerId Int CONSTRAINT CID_PK PRIMARY KEY, Cname Varchar (50), Balance Decimal (7, 2) NOT NULL)

Example-constraint name at table level:

Create Table BANK (CustomerId Int, Cname Varchar (50), Balance Decimal (7, 2) NOT NULL, CONSTRAINT CID_PK PRIMARY KEY)

UNIQUE key in SQL

If UNIQUE constraint is imposed on a column or column’s the column will not allow duplicate values in to it.

Syntax:

Create Table <Table_name> <column 1><data type> [width] [UNIQUE],… <column N><data type> [width] [UNIQUE]

Example:

Create Table BANK (Customerid Int UNIQUE, Cname varchar (50), Balance Decimal (7,2) NOT NULL)

Note:

a) When a UNIQUE constraint is imposed on a column the column will not allow duplicate value but allow NULL value.

b) UNIQUE constraint in MS-SQL Server allows to store single NULL value in it but Oracle allows multiple NULL values.

UNIQUE constraint can be applied on multiple columns combined or separately also, NOT NULL can be applied on multiple columns separately.

UNIQUE key on multiple column's Example:

Create Table BranchDetails (Citycode varchar (10), Branchcode varchar (10), Location varchar (20), constraint CC_BC_UQ UNIQUE (Citycode, Branchcode)

NOT NULL constraint

A constraint is a restriction or business rule which can be imposed on the data for managing data integrity. When a business rule is imposed on the data, the data cannot violate the rule.

When NOT NULL constraint is imposed on a column the column will not allow NULL values into it.

Syntax:

Create Table <table_name> <column 1><data type> [width][NOT NULL],…. <column N>< data type>[width][NOT NULL]

Example:

Create Table BANK (Customerid Int NOT NULL, Cname varchar (50), Balance Decimal (7, 2) NOT NULL)

Note: When a NOT NULL constraint is imposed on a column the column will not allow NULL values but allows duplicate values.

ALTER command in SQL

ALTER command in SQL is used to change or modify the structure of existing objects available in the database.

Using ALTER command we can:

1. Increase /decrease width of column
2. Change the data type of column
3. Change NOT NULL to NULL and vice-versa
4. Add a new column to a table
5. Drop a column from a table
6. Delete a column from a table
7. Add a new table constraint
8. Drop a table constraint
9. Set a default for a column
10. Drop a default for a column

When a table doesn’t contain any data in it, we can perform any type of action on it. But, if it contains data in it, we can perform the actions with restriction.

Syntax:

ALTER Table <table_name> ALTER column <column_name> <data type> [<width>] [NOT NULL |NULL]

Examples:

ALTER table Students Cname varchar (200)

ALTER table Students ADD City varchar (100)

ALTER table Students ADD Fees_ck Check (Fees Between 5000 to 7000)

ALTER table Students DROP CONSTRAINT Balance

ALTER table Student MODIFY Balance SET DEFAULT 2000

ALTER table Students Balance NOT NULL

Changing NULL to NOT NULL is same as imposing NOT NULL constraint on column. While changing NULL to NOT NULL, the NULL column should not contain any NULL values in it.