HOME

GROUP BY vs ORDER BY

Lately, when I was designing a query for a website, I was wondering about the exact difference between group by and order by in a query. The easiest explanation is that order by is doing the sorting of a table and the group by clause is used for aggregation of a field. Ok, this is for the theory, let's see an example: The following table contains the items bought by several people last week: cust_id item total price 1 balloon 1 2 apple 3 1 apple 4 1 pillow 25 3 plastic bag 1 Order By Let's see the following query: SELECT * FROM shopping ORDER BY total_price The output will be: cust_id item total price 1 balloon 1 3 plastic bag 1 2 apple 3 1 apple 4 1 pillow 25 as you can see the fiels have been ordered by the price. The default order is ascending. If you want to specify how the data is ordered, write either ASC or DESC at the end of your query. Example: SELECT * FROM shopping ORDER BY total_price DESC will give the same table, but starting with the pillow. Group By Now we are going to use the group by statement. The group by statement, as said before, is especially useful for aggregating, meaning to apply some function. Let's see an example: SELECT cust_id, SUM(total_price) FROM shopping GROUP BY cust_id This query returns the total amount of money spent by each customer during all their shoppings. The table returned looks like this: cust_id SUM(total_price) 1 30 2 3 3 1 The way you have to understand the query is that we compute the sum of all amounts for each customer. This is expressed by the GROUP BY cust_id. Now, if we would try to do this for each product. This would correspond to the total money gained per product. The query looks like this: SELECT item, SUM(total_price) FROM shopping GROUP BY item This query returns the following table: item SUM(total_price) apple 7 balloon 1 pillow 25 plastic bag 1

HAVING Clause

having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention condition in Group based SQL functions, just like WHERE clause.

Syntax for having will be,

select column_name, function(column_name)

FROM table_name

WHERE column_name condition

GROUP BY column_name

HAVING function(column_name) condition

---

Example of HAVING Statement

Consider the following Sale table.

oid	order_name	previous_balance	customer
11	ord1	2000	Alex
12	ord2	1000	Adam
13	ord3	2000	Abhi
14	ord4	1000	Adam
15	ord5	2000	Alex

Suppose we want to find the customer whose previous_balance sum is more than 3000.

We will use the below SQL query,

SELECT *

from sale group customer

having sum(previous_balance) > 3000

Result will be,

oid	order_name	previous_balance	customer
11	ord1	2000	Alex

Distinct keyword

The distinct keyword is used with Select statement to retrieve unique values from the table. Distinctremoves all the duplicate records while retrieving from database.

---

Syntax for DISTINCT Keyword

SELECT distinct column-name from table-name;

---

Example

Consider the following Emp table.

eid	name	age	salary
401	Anu	22	5000
402	Shane	29	8000
403	Rohan	34	10000
404	Scott	44	10000
405	Tiger	35	8000

select distinct salary from Emp;

The above query will return only the unique salary from Emp table

salary

5000

8000

10000

---

AND & OR operator

AND and OR operators are used with Where clause to make more precise conditions for fetching data from database by combining more than one condition together.

---

AND operator

AND operator is used to set multiple conditions with Where clause.

---

Example of AND

Consider the following Emp table

eid	name	age	salary
401	Anu	22	5000
402	Shane	29	8000
403	Rohan	34	12000
404	Scott	44	10000
405	Tiger	35	9000

SELECT * from Emp WHERE salary < 10000 AND age > 25

The above query will return records where salary is less than 10000 and age greater than 25.

eid	name	age	salary
402	Shane	29	8000
405	Tiger	35	9000

---

OR operator

OR operator is also used to combine multiple conditions with Where clause. The only difference between AND and OR is their behaviour. When we use AND to combine two or more than two conditions, records satisfying all the condition will be in the result. But in case of OR, atleast one condition from the conditions specified must be satisfied by any record to be in the result.

---

Example of OR

Consider the following Emp table

eid	name	age	salary
401	Anu	22	5000
402	Shane	29	8000
403	Rohan	34	12000
404	Scott	44	10000
405	Tiger	35	9000

SELECT * from Emp WHERE salary > 10000 OR age > 25

The above query will return records where either salary is greater than 10000 or age greater than 25.

402	Shane	29	8000
403	Rohan	34	12000
404	Scott	44	10000
405	Tiger	35	9000

SQL Constraints

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints can be divided into following two types,

·         Column level constraints : limits only column data

·         Table level constraints : limits whole table data

Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most used constraints that can be applied to a table.

·         NOT NULL

·         UNIQUE

·         PRIMARY KEY

·         FOREIGN KEY

·         CHECK

·         DEFAULT

---

NOT NULL Constraint

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value. One important point to note about NOT NULL constraint is that it cannot be defined at table level.

---

Example using NOT NULL constraint

CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will not take NULL value.

---

UNIQUE Constraint

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. UNIQUE constraint can be applied at column level or table level.

---

Example using UNIQUE constraint when creating a Table (Table Level)

CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will only have unique values and wont take NULL value.

---

Example using UNIQUE constraint after Table is created (Column Level)

ALTER table Student add UNIQUE(s_id);

The above query specifies that s_id field of Student table will only have unique value.

---

Primary Key Constraint

Primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table.

---

Example using PRIMARY KEY constraint at Table Level

CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);

The above command will creates a PRIMARY KEY on the s_id.

---

Example using PRIMARY KEY constraint at Column Level

ALTER table Student add PRIMARY KEY (s_id);

The above command will creates a PRIMARY KEY on the s_id.

---

Foreign Key Constraint

FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables. To understand FOREIGN KEY, let's see it using two table.

Customer_Detail Table :

c_id	Customer_Name	address
101	Adam	Noida
102	Alex	Delhi
103	Stuart	Rohtak

Order_Detail Table :

Order_id	Order_Name	c_id
10	Order1	101
11	Order2	103
12	Order3	102

In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table. The value that is entered in c_id which is set as foreign key in Order_Detail table must be present in Customer_Detail table where it is set as primary key. This prevents invalid data to be inserted into c_id column of Order_Detail table.

---

Example using FOREIGN KEY constraint at Table Level

CREATE table Order_Detail(order_id int PRIMARY KEY,

order_name varchar(60) NOT NULL,

c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id));

In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column of Customer_Detail.

---

Example using FOREIGN KEY constraint at Column Level

ALTER table Order_Detail add FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

---

Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when a particular record is deleted in main table. When two tables are connected with Foriegn key, and certain data in the main table is deleted, for which record exit in child table too, then we must have some mechanism to save the integrity of data in child table.

·         On Delete Cascade : This will remove the record from child table, if that value of foriegn key is deleted from the main table.

·         On Delete Null : This will set all the values in that record of child table as NULL, for which the value of foriegn key is deleted from the main table.

·         If we don't use any of the above, then we cannot delete data from the main table for which data in child table exists. We will get an error if we try to do so.

ERROR : Record in child table exist

---

CHECK Constraint

CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. Its like condition checking before saving data into a column.

---

Example using CHECK constraint at Table Level

create table Student(s_id int NOT NULL CHECK(s_id > 0),

Name varchar(60) NOT NULL,

Age int);

The above query will restrict the s_id value to be greater than zero.

---

Example using CHECK constraint at Column Level

ALTER table Student add CHECK(s_id > 0);

SQL Functions

SQL provides many built-in functions to perform operations on data. These functions are useful while performing mathematical calculations, string concatenations, sub-strings etc. SQL functions are divided into two catagories,

·         Aggregrate Functions

·         Scalar Functions

---

Aggregrate Functions

These functions return a single value after calculating from a group of values.Following are some frequently used Aggregrate functions.

---

1) AVG()

Average returns average value after calculating from values in a numeric column.

Its general Syntax is,

SELECT AVG(column_name) from table_name

---

Example using AVG()

Consider following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SQL query to find average of salary will be,

SELECT avg(salary) from Emp;

Result of the above query will be,

avg(salary)

8200

---

2) COUNT()

Count returns the number of rows present in the table either based on some condition or without condition.

Its general Syntax is,

SELECT COUNT(column_name) from table-name

---

Example using COUNT()

Consider following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SQL query to count employees, satisfying specified condition is,

SELECT COUNT(name) from Emp where salary = 8000;

Result of the above query will be,

count(name)

2

---

Example of COUNT(distinct)

Consider following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SQL query is,

SELECT COUNT(distinct salary) from emp;

Result of the above query will be,

count(distinct salary)

4

---

3) FIRST()

First function returns first value of a selected column

Syntax for FIRST function is,

SELECT FIRST(column_name) from table-name

---

Example of FIRST()

Consider following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SQL query

SELECT FIRST(salary) from Emp;

Result will be,

first(salary)

9000

---

4) LAST()

LAST return the return last value from selected column

Syntax of LAST function is,

SELECT LAST(column_name) from table-name

---

Example of LAST()

Consider following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SQL query will be,

SELECT LAST(salary) from emp;

Result of the above query will be,

last(salary)

8000

---

5) MAX()

MAX function returns maximum value from selected column of the table.

Syntax of MAX function is,

SELECT MAX(column_name) from table-name

---

Example of MAX()

Consider following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SQL query to find Maximum salary is,

SELECT MAX(salary) from emp;

Result of the above query will be,

MAX(salary)

10000

---

6) MIN()

MIN function returns minimum value from a selected column of the table.

Syntax for MIN function is,

SELECT MIN(column_name) from table-name

---

Example of MIN()

Consider following Emp table,

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SQL query to find minimum salary is,

SELECT MIN(salary) from emp;

Result will be,

MIN(salary)

6000

---

7) SUM()

SUM function returns total sum of a selected columns numeric values.

Syntax for SUM is,

SELECT SUM(column_name) from table-name

---

Example of SUM()

Consider following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SQL query to find sum of salaries will be,

SELECT SUM(salary) from emp;

Result of above query is,

SUM(salary)

41000

---

Scalar Functions

Scalar functions return a single value from an input value. Following are soe frequently used Scalar Functions.

---

1) UCASE()

UCASE function is used to convert value of string column to Uppercase character.

Syntax of UCASE,

SELECT UCASE(column_name) from table-name

---

Example of UCASE()

Consider following Emp table

eid	name	age	salary
401	anu	22	9000
402	shane	29	8000
403	rohan	34	6000
404	scott	44	10000
405	Tiger	35	8000

SQL query for using UCASE is,

SELECT UCASE(name) from emp;

Result is,

UCASE(name)

ANU

SHANE

ROHAN

SCOTT

TIGER

---

2) LCASE()

LCASE function is used to convert value of string column to Lowecase character.

Syntax for LCASE is,

SELECT LCASE(column_name) from table-name

---

Example of LCASE()

Consider following Emp table

eid	name	age	salary
401	anu	22	9000
402	shane	29	8000
403	rohan	34	6000
404	scott	44	10000
405	Tiger	35	8000

SQL query for converting string value to Lower case is,

SELECT LCASE(name) from emp;

Result will be,

LCASE(name)

anu

shane

rohan

scott

tiger

---

3) MID()

MID function is used to extract substrings from column values of string type in a table.

Syntax for MID function is,

SELECT MID(column_name, start, length) from table-name

---

Example of MID()

Consider following Emp table

eid	name	age	salary
401	anu	22	9000
402	shane	29	8000
403	rohan	34	6000
404	scott	44	10000
405	Tiger	35	8000

SQL query will be,

select MID(name,2,2) from emp;

Result will come out to be,

MID(name,2,2)

nu

ha

oh

co

ig

---

4) ROUND()

ROUND function is used to round a numeric field to number of nearest integer. It is used on Decimal point values. Syntax of Round function is,

SELECT ROUND(column_name, decimals) from table-name

---

Example of ROUND()

Consider following Emp table

eid	name	age	salary
401	anu	22	9000.67
402	shane	29	8000.98
403	rohan	34	6000.45
404	scott	44	10000
405	Tiger	35	8000.01

SQL query is,

SELECT ROUND(salary) from emp;

Result will be,

ROUND(salary)

9001

8001

6000

10000

8000

THIS IS TESTING PAGE

---