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.

UPDATE command in SQL

UPDATE command in SQL is used to update or make the changes to the existing data which is present in the table.

Syntax:

UPDATE <Table_name> set <columnlist>=< value>

Table name can be name of a base table or an updatable view.

WHERE clause is optional: if omitted, named columns are updated for all rows in table. If specified, only those rows that satisfy search condition are updated.

New value must be compatible with data type for corresponding column.

Example:

1. UPDATE Bank set Customername=”Johny” where Customerid=105
2. UPDATE Bank set Customername=”ABC”,Balance=7000 where Customerid=105

Query 2 update two columns Customername and Balance at a time. One can update single or multiple columns at a time.

SELECT command in SQL

SELECT command in SQL is used for retrieving the data either from a table or tables.

Syntax:

SELECT <columnlist> from <table name> [<condition>]

Example:

1.       SELECT * from Bank

Above query retrieves all the records (all rows and columns) from table Bank

2.       SELECT * from Bank where Custid=105

Above query retrieves the record of customer having customer id =105

3.       SELECT * from Bank where Cname= NULL

This is incorrect SQL statement as NULL is not comparable because NULL is infinite or unknown value.

4.       SELECT * from Bank where Cname is NULL

We can make use of NULL value in SQL as mentioned in above query.

Query 3 will not retrieve any data because NULL value cannot be compared with another NULL value. In terms of a database, NULL value is treated as infinite or unknown value as two infinite or unknown values cannot be compared with each other. Two NULL’s also cannot be compared with each other.

When you want to perform a comparison on NULL values using operators like =,! =, <,<=,>,=> is not possible. You can use only is ‘IS NULL’ or ‘IS NOT NULL’

INSERT command in SQL

INSERT command is used in SQL for inserting or adding the data into the table.

Syntax:

INSERT into <tname> [(<columnlist>)] values (<list of values>)

Table BANK:

Custid	Cname	Balance
1	John	5000
2	Rama	7000

Examples:

1.       INSERT into BANK values (101, ‘Ajay’,5000)

2.       INSERT into BANK (Custid,Cname,Balance) values(102,’Laura’,3500)

Note: String and Date values are enclosed into a single quote only

Query 2 is compiled and executed faster than Query 1.

If you want to INSERT some special values at special column then use query as below:

3.       INSERT into BANK (Custid, Balance) values (107,9000)

It is advised to use Query type 2 or Query type 3.

If the value is not supplied to a column while inserting, the default value is taken as NULL.

4.       INSERT into BANK values (111, NULL, 8700)

You can also explicitly insert NULL values into any column as you required as mentioned in Query 4.

CREATE command in SQL

CREATE command is used for creating any object on the database server.

Syntax:

CREATE Table<Table_name>(<columnlist1><datatype>[width],…<columnlistn><datatype>[width])

Rules for CREATE command:

1. Name of the table should always be unique under the database
2. A table name should not start with numeric’s or special characters
3. The maximum acceptable size for an object is 128 characters
4. Column name should be unique under the tables
5. A table can have maximum 1024 columns

Example:

CREATE table Bank (Custid int, Custname varchar (50), Bal Decimal (7, 2))

Note: SP_Help < Table_name> command is used to view the structure of a table under database

Example: SP_Help Bank

Types of View in SQL

A view is an object which is similar to a table. It is known as a "Logical table" because it will never occupy space in the memory for storing the data. It is a dependent object that can be created from an existing table. View allows you to perform query as well as DML operations.

There are four types of views in SQL.

Types of View

1. Updatable view: It is a view which allows manipulation on it.
2. Non-updatable view: It doesn’t allow manipulation on it.
3. Simple view: It is a view on single table that contains only the column or columns of the table in it.

By default simple views are updatable only insert operation will be restricted if at all not NULL column of the base table were not present in the view.

4. Complex view: It is a view based on more than one table. Complex views based on multiple tables were referred as non-updatable because here the DML operation can’t affect multiple base table but using a complex view you can perform DML operation referring to a single table. 

A complex view allows manipulation on it but with restriction.

A view based on single table can also be called as complex view if it contains any of the following in it.

• Group by clause
• Having clause
• Distinct
• Computed columns

What does a view stores in it?

The view only stores the select statement that is used for creating it. It never stores any data in it so view is also known as stored select statement or an imaginary table.

Whenever you query on a view, the view will internal execute the select statement what it has stored and retrieves data from base table which is presented to the end user.

Sometimes a view also allows you to perform changes on it.

The changes that are performed on view will be internally performed by the view on the table on which it was created.

Top 7 differences between Table and View in SQL

In order to undertand the database it is essential that one should know the difference between table and view. Below listed are few differences between table and view.

Table contains rows and columns, columns representing fields and rows containing the records whereas View is an imaginary or virtual table which contains rows and columns, just like a real table and is created from one or more than one table by joins, with selected columns.

Tables always occupy space in the memory database whereas Views never occupies space in the memory for storing data.

Table can have limited number of columns and unlimited number of rows whereas View is an extract from a database. For example, we have tables like Department, Technology, Sales, Personal Details, Academic details etc which has multiple columns and rows. But, we usually requires certain number of columns from each table to create a report in such cases rather than writing complex SQL query to fetch specific records  or columns from these tables, we create a View which saves time as requires less time to fetch data from View.

Tables are the actual database objects that hold rows whereas Views are "imaginary tables" or "Logical tables" that are created based on the actual tables.

Views are created to provide security mechanism. For example, we have a table which hold customers important personal details and various teams are working on this table. In such cases, we create View with selected number of records or fields so team can access the data for their operations. Only the users having the access to tables and modify the data, users having access to View can view data but cannot update it.

Views executes fasters than the Tables as Views are extract from tables and requires less time to execute or perform action.

Tables can be updated, deleted and modified whereas Views may be updated, modified and updated depending upon the permissions provided to user.

What is a need for View:

Views are used in two different scenarios.

1. Whenever you required to give permissions on some information in a table to other users without providing access directly to the table we create views and give access on the view which provides security. E.g. Team A, Team B and Team C is working on the same project. But, due to some security constraint we don’t want to give permission to Team B and C to see the Salary, DOB of an EMPLOYEE.
2. Sometimes we may be using complex queries for retrieving data from tables which may be complicated for us to write the query each time in such cases you can create a view using a query and then start querying on the view directly.

       E.g.  Create table EMPLOYEE

       (Emp_Idinteger not NULL, FName Varchar (25),LName  Varchar (25), Salary decimal (10, 2),Dept       Varchar (50), DOB date,Address Varchar (150))

        Create view EMPLOYEE_VIEW as

       (Select Emp_Id, FName, LName,Dept, Addressfrom EMPLOYEE)

SEQUEL

SEQUEL is a structured English query language used for interacting with a Relational database. SEQUEL is also known as SQL .

SEQUEL has five sub-languages in it like-

1. DDL (Data Definition Language):

This Language is used to define the structure of objects under the database with 4 commands in it.

Command	Description
Create	Crates a new table, view, any object in database
Alter	Modifies the table, view, existing database object
Drop	Destroy or delete the entire table
Truncate	Deletes all the data present under the table

2. DML(Data Manipulation Language):

This deals with the data under database with 3 commands in it.

Command	Description
Insert	Insert data in to table
Update	Modify or update tab
Delete	Destroy or delete row or rows from a table

3. DQL(Data Query Language):

this deals with data also but for retrieving of the data with 1 command in it.

Command	Description
Select	Retrieve the data either from table or tables

4. DCL(Data Control Language):

This is used for managing the security of objects using which one can Grant or Revoke permissions with 3 commands in it.

Command	Description
Grant	Gives a privilege to user
Revoke	Take back granted privileges from user
Deny	Reject or refuse the privilege to user

5. TCL(Transaction Control Language):

This is used for transaction management with 3 commands in it.

Command	Description
Commit	Update the changes in database
Rollback	Revert the changes database
Save	Save the changes database

Transaction is a set of actions which tells all the actions should be performed successfully or none of the action should be performed.