ORACLE SQL PLSQL CLAUSE training institutes in Kukatpally Hyderabad

SQL WHERE

A WHERE clause in SQL is a data manipulation language statement.

WHERE clauses are not mandatory clauses of SQL DML statements. But it can be used to limit the number of rows affected by a SQL DML statement or returned by a query.

Actually. it filters the records. It returns only those queries which fulfill the specific conditions.

WHERE clause is used in SELECT, UPDATE, DELETE statement etc.

Let's see the syntax for sql where:

  1. SELECT column1, column 2, ... column n
  2. FROM    table_name
  3. WHERE [conditions]

WHERE clause uses some conditional selection

= equal
> greater than
< less than
>= greater than or equal
<= less than or equal
< > not equal to

SQL AND

  • The SQL AND condition is used in SQL query to create two or more conditions to be met.
  • It is used in SQL SELECT, INSERT, UPDATE and DELETE
  • Let's see the syntax for SQL AND:
  • SELECT columns FROM tables WHERE condition 1 AND condition 2;
  • The SQL AND condition require that both conditions should be met.
  • The SQL AND condition also can be used to join multiple tables in a SQL statement.
  • To understand this concept practically, let us see some examples.

Consider we have an employee table created into the database with the following data:

ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Pune
6 Roshni Jadhav Finance Bangalore
7 Sandhya Jain Finance Bangalore

SQL "AND" example with "SELECT" statement

This is how an SQL "AND" condition can be used in the SQL SELECT statement.

Example 1:

Write a query to get the records from emp tables in which department of the employee is IT and location is Chennai.

Query:

  1. mysql> SELECT *FROM emp WHERE Department = "IT" AND Location = "Chennai";
ID First_Name Last_Name Department Location
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai

In the emp table, there are three employees whose department is IT. But we have specified the AND condition according to which the employee's location should not be other than Chennai. So, there are only two employees whose department is IT and Location is Chennai.

Example 2:

Write a query to get the records from emp tables in which department of the employee is IT and location is Mumbai.

Query:

  1. mysql> SELECT *FROM emp WHERE Department = "IT" AND Location = "Mumbai";

 

ID First_Name Last_Name Department Location
2 Anurag Rajput IT Mumbai

In the emp table, there are three employees whose department is IT. Among these three employees, there is only one employee whose location is Mumbai. Due to the presence of the AND operator used in the query, a record must satisfy both conditions.

SQL "AND" example with "UPDATE" statement

This is how the "AND" condition can be used in the SQL UPDATE statement.

Example 1:

Write a query to update the records in emp tables in which department of the employee is Marketing, and the first name is Suraj. For that particular employee, set the updated value of the location as Delhi.

Query:

  1. mysql> UPDATE emp SET Location = "Delhi" WHERE Department = "Marketing" AND First_Name = "Suraj";

SQL AND

We will use the SELECT query to verify the updated record.

  1. mysql> SELECT *FROM emp;
ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Delhi
6 Roshni Jadhav Finance Bangalore
7 Sandhya Jain Finance Bangalore

In the emp table, there are three employees whose department is IT. Among these three employees, there is only one employee whose location is Mumbai. Due to the presence of the AND operator used in the query, a record must satisfy both conditions.

Example 2:

Write a query to update the records in the emp table in which department of the employee is Finance and ID is 7. For that particular employee, set the updated value of the department as HR.

Query:

  1. mysql> UPDATE emp SET Department = "HR" WHERE Department = "Finance" AND ID = 7;

SQL AND

We will use the SELECT query to verify the updated record.

  1. mysql> SELECT *FROM emp;

 

ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Delhi
6 Roshni Jadhav Finance Bangalore
7 Sandhya Jain HR Bangalore

In the emp table, there are two employees whose department is Finance. Among these two employees, there is only one employee whose ID is 7. Due to the presence of AND operator used in the query, a record must have the department as Finance and ID as 7.

SQL "AND" example with "DELETE" statement

This is how an SQL "AND" condition can be used in the SQL DELETE statement.

Example 1:

Write a query to delete the records from the emp table in which the last name of the employee is Jain, and the Location is Bangalore.

Query:

  1. mysql> DELETE FROM emp WHERE Last_Name = 'Jain' AND Location = 'Bangalore';

SQL AND

We will use the SELECT query to verify the deleted record.

  1. mysql> SELECT *FROM emp;

 

ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Delhi
6 Roshni Jadhav Finance Bangalore

There is only one record in the emp table whose last name is Jain. But still, due to the presence of AND operator, the second condition will also be checked according to which employee's location should be Bangalore. So, only that particular record is deleted.

Example 2:

Write a query to delete the records from the emp table in which department of the employee is IT and Location is Mumbai.

Query:

  1. mysql> DELETE FROM emp WHERE Department = 'IT' AND Location = 'Mumbai';

SQL AND

We will use the SELECT query to verify the deleted record.

  1. mysql> SELECT *FROM emp;

 

ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Delhi
6 Roshni Jadhav Finance Bangalore

There are three records in the emp table whose department is IT. But only one record is deleted from the emp table, which contains a total of 6 records. This happened because of the AND operator according to which the employee's location should mandatorily be Mumbai. Therefore there is only one record that satisfies both the conditions. Hence, it is deleted.

SQL OR

The SQL OR condition is used in SQL query to create a SQL statement where records are returned when any one condition met. It can be used in a SELECT statement, INSERT statement, UPDATE statement or DELETE statement.

Let's see the syntax for the OR condition:

  1. SELECT columns FROM tables WHERE condition 1 OR condition 2;
ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Pune
6 Roshni Jadhav Finance Bangalore
7 Sandhya Jain Finance Bangalore
  • SQL "OR" example with SQL SELECT

This is how an SQL "OR" condition can be used in the SQL SELECT statement.

Example 1:

Write a query to get the records from emp tables in which department of the employee is IT or location is Chennai.

Query:

  1. mysql> SELECT *FROM emp WHERE Department = "IT" OR Location = "Chennai";
ID First_Name Last_Name Department Location
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai

In the emp table, there are three employees whose department is IT. But there are only two records whose location is Chennai. Still, all three records are displayed. This happened because we have specified OR operator in the query, according to which the record will be considered in the result set even any one condition is met.

Example 2:

Write a query to get the records from emp tables in which department of the employee is Marketing or location is Noida.

Query:

  1. mysql> SELECT *FROM emp WHERE Department = "Marketing" OR Location = "Noida";
ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
5 Suraj Tripathi Marketing Pune
7 Sandhya Jain Finance Bangalore

There are two employees whose department is Marketing in the emp table, but still, three records are displayed. This happened because of the use of the OR operator in the query. Among the three records displayed above, the first two records satisfy condition 1; the second record satisfies both the conditions and the third record satisfies only condition 1. Due to the OR operator, even if anyone condition is satisfied, the record is considered in the result-set.

  • SQL "OR" example with SQL UPDATE

This is how the "OR" condition can be used in the SQL UPDATE statement.

Example 1:

Write a query to update the records in emp tables in which department of the employee is Marketing, or the last name is Tarle. For that particular employee, set the updated value of the location as Delhi.

Query:

  1. mysql> UPDATE emp SET Location = "Delhi" WHERE Department = "Marketing" OR Last_Name = "Tarle";

SQL ORWe will use the SELECT query to verify the updated record.

  1. mysql> SELECT *FROM emp;
ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Pune
6 Roshni Jadhav Finance Bangalore
7 Sandhya Jain Finance Bangalore

There are two employees whose department is 'Marketing' and one record whose last name is 'Tarle' in the emp table. Though only one condition is still met, that record is considered and updated in the table due to the OR operator.

Example 2:

Write a query to update the records in the emp table in which department of the employee is Finance, or the first name is Sandhya. For that particular employee, set the updated value of the department as HR.

Query:

  1. mysql> UPDATE emp SET Department = "HR" WHERE Department = "Finance" OR First_Name = "Sandhya";

SQL ORWe will use the SELECT query to verify the updated record.

  1. mysql> SELECT *FROM emp;
ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Delhi
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Delhi
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Delhi
6 Roshni Jadhav HR Bangalore
7 Sandhya Jain HR Noida

There are two employees whose department is 'Finance,' and among these two records, one record satisfies both the conditions in the emp table. However, both the records are considered and updated in the table due to the OR operator.

  • SQL "OR" example with SQL DELETE

This is how an SQL "OR" condition can be used in the SQL DELETE statement.

Example 1:

Write a query to delete the records from the emp table in which the last name of the employee is Jain or Location is Bangalore.

Query:

  1. mysql> DELETE FROM emp WHERE Last_Name = 'Jain' OR Location = 'Bangalore';

SQL ORWe will use the SELECT query to verify the deleted record.

  1. mysql> SELECT *FROM emp;
ID First_Name Last_Name Department Location
1 Harshad Kuwar Marketing Pune
2 Anurag Rajput IT Mumbai
3 Chaitali Tarle IT Chennai
4 Pranjal Patil IT Chennai
5 Suraj Tripathi Marketing Pune

There is only one record in the emp table whose last name is Jain and one record whose location is Bangalore. But still, due to the presence of an OR operator, even if anyone condition is satisfied, that particular record is deleted.

Example 2:

Write a query to delete the records from the emp table in which department of the employee is marketing and Location is Delhi.

Query:

  1. mysql> DELETE FROM emp WHERE Department = 'Marketing' OR Location = 'Delhi';

SQL ORWe will use the SELECT query to verify the deleted record.

  1. mysql> SELECT *FROM emp;
ID First_Name Last_Name Department Location
2 Anurag Rajput IT Mumbai
4 Pranjal Patil IT Chennai

There is only one record in the emp table whose department is Marketing and one record whose location is Delhi. But still, due to the presence of an OR operator, even if anyone condition is satisfied, that particular record is deleted.

SQL WITH CLAUSE

The SQL WITH clause is used to provide a sub-query block which can be referenced in several places within the main SQL query. It was introduced by oracle in oracle 9i release2 database.

There is an example of employee table:

Syntax for the SQL WITH clause -

This syntax is for SQL WITH clause using a single sub-query alias.

  1. WITH <alias_name> AS (sql_sub-query_statement)
  2. SELECT column_list FROM <alias_name> [table name]
  3. [WHERE <join_condition>]

When you use multiple sub-query aliases, the syntax will be as follows.

  1. WITH <alias_name_A>  AS (sql_sub-query_statement)
  2. <alias_name_B> AS (sql_sub-query_statement_from_alias_name_A
  3. Or sql_sub-query_statement)
  4. SELECT <column_list>
  5. FROM <alias_name_A >,< alias_name_B >, [tablenames]
  6. [WHERE < join_condition>]

SQL SELECT AS

  • SQL 'AS' is used to assign a new name temporarily to a table column or even a table.
  • It makes an easy presentation of query results and allows the developer to label results more accurately without permanently renaming table columns or even the table itself.
  • Let's see the syntax of select as:
  1. SELECT Column_Name1 AS New_Column_Name, Column_Name2  As New_Column_Name FROM Table_Name;

Here, the Column_Name is the name of a column in the original table, and the New_Column_Name is the name assigned to a particular column only for that specific query. This means that New_Column_Name is a temporary name that will be assigned to a query.

Assigning a temporary name to the column of a table:

Let us take a table named orders, and it contains the following data:

Day_of_order Customer Product Quantity
11-09-2001 Ajeet Mobile 2
13-12-2001 Mayank Laptop 20
26-12-2004 Balaswamy Water cannon 35

Example:

Advertisement

Suppose you want to rename the 'day_of_order' column and the 'customer' column as 'Date' and 'Client', respectively.

Query:

  1. SELECT day_of_order AS 'Date', Customer As 'Client', Product, Quantity FROM orders;

The result will be shown as this table:

Day_of_order Customer Product Quantity
11-09-2001 Ajeet Mobile 2
13-12-2001 Mayank Laptop 20
26-12-2004 Balaswamy Water cannon 35

From the above results, we can see that temporarily the 'Day_of_order' is renamed as 'date' and 'customer' is renamed as 'client'.

Note: SQL AS is the same as SQL ALIAS.

Let us take another example. Consider we have a students table with the following data.

Student_RollNo Student_Name Student_Gender Student_MobileNumber Student_HomeTown Student_Age Student_Percentage
1 Rohit More Male 9890786123 Lucknow 23 75
2 Kunal Shah Male 7789056784 Chandigarh 20 92
3 Kartik Goenka Male 9908743576 Ahemdabad 22 89
4 Anupama Shah Female 8890907656 Chennai 24 92
5 Snehal Jain Female 8657983476 Surat 21 94

Example 1:

Write a query to get the student name and the average of the percentage of the student under the temporary column name 'Student' and 'Student_Percentage', respectively.

Query:

  1. SELECT Student_Name AS Student, AVG (Student_Percentage) AS Average_Percentage FROM students;

Here, to calculate the average, we have used AVG () function. Further, the calculated average value of the percentage will be stored under the temporary name 'Average_Percentage'.

The result will be shown as this table:

Student Average_Percentage
Rohit More 88.4000

Example 2:

Write a query to get the student roll number and the student mobile number under the temporary column name 'Roll No' and 'Mobile Number', respectively.

Query:

  1. mysql> SELECT Student_RollNo AS 'Roll No', Student_PhoneNumber AS 'Mobile Number' FROM students;

The result will be shown as this table:

Roll No Mobile Number
1 9890786123
2 7789056784
3 9908743576
4 8890907656
5 8657983476

Example 3:

Write a query to get the student roll number and the student phone number, home town under the temporary column name 'Roll No' and 'Student_Info', respectively.

Query:

  1. mysql> SELECT Student_RollNo AS 'Roll No', CONCAT (Student_PhoneNumber, ', ', Student_HomeTown) AS Student_Info FROM students;

Here, the CONCAT () function combines two different columns, student phone number and the home town, together in a single column. Further, the combined values of both these columns are stored under the temporarily assigned name 'Student_Info'.

The result will be shown as this table:

Roll No Mobile Number
1 9890786123, Lucknow
2 7789056784, Chandigarh
3 9908743576, Ahemdabad
4 8890907656, Chennai
5 8657983476, Surat

Assigning a temporary name to a table

Instead of remembering the table names, we can create an alias of them. We can assign a temporary name to the columns of a table; similarly, we can create an alias of a table.

Let's understand it with the help of an example.

Write a query to create an alias of a table named 'students'.

Query:

  1. mysql> SELECT s.Student_RollNo, s.Student_Name, s.Student_Gender, s.Student_PhoneNumber, s.Student_HomeTown FROM students AS s WHERE s.Student_RollNo = 3;

Here, 's' is the alias, i.e., the temporary name assigned to the 'students' table.

The result will be shown as this table:

Student_RollNo Student_Name Student_Gender Student_MobileNumber Student_HomeTown
3 Kartik Goenka Male 9908743576 Ahemdabad

 

 

HAVING Clause in SQL

The HAVING clause places the condition in the groups defined by the GROUP BY clause in the SELECT statement.

This SQL clause is implemented after the 'GROUP BY' clause in the 'SELECT' statement.

This clause is used in SQL because we cannot use the WHERE clause with the SQL aggregate functions. Both WHERE and HAVING clauses are used for filtering the records in SQL queries.

Difference between HAVING and WHERE Clause

The difference between the WHERE and HAVING clauses in the database is the most important question asked during an IT interview.

The following table shows the comparisons between these two clauses, but the main difference is that the WHERE clause uses condition for filtering records before any groupings are made, while HAVING clause uses condition for filtering values from a group.

HAVING WHERE
1. The HAVING clause is used in database systems to fetch the data/values from the groups according to the given condition. 1. The WHERE clause is used in database systems to fetch the data/values from the tables according to the given condition.
2. The HAVING clause is always executed with the GROUP BY clause. 2. The WHERE clause can be executed without the GROUP BY clause.
3. The HAVING clause can include SQL aggregate functions in a query or statement. 3. We cannot use the SQL aggregate function with WHERE clause in statements.
4. We can only use SELECT statement with HAVING clause for filtering the records. 4. Whereas, we can easily use WHERE clause with UPDATE, DELETE, and SELECT statements.
5. The HAVING clause is used in SQL queries after the GROUP BY clause. 5. The WHERE clause is always used before the GROUP BY clause in SQL queries.
6. We can implements this SQL clause in column operations. 6. We can implements this SQL clause in row operations.
7. It is a post-filter. 7. It is a pre-filter.
8. It is used to filter groups. 8. It is used to filter the single record of the table.

Syntax of HAVING clause in SQL

  1. SELECT column_Name1, column_Name2, ....., column_NameN aggregate_function_name(column_Name) FROM table_name GROUP BY column_Name1 HAVING condition;

Examples of HAVING clause in SQL

In this article, we have taken the following four different examples which will help you how to use the HAVING clause with different SQL aggregate functions:

Example 1: Let's take the following Employee table, which helps you to analyze the HAVING clause with SUM aggregate function:

Emp_Id Emp_Name Emp_Salary Emp_City
201 Abhay 2000 Goa
202 Ankit 4000 Delhi
203 Bheem 8000 Jaipur
204 Ram 2000 Goa
205 Sumit 5000 Delhi

If you want to add the salary of employees for each city, you have to write the following query:

  1. SELECT SUM(Emp_Salary), Emp_City FROM Employee GROUP BY Emp_City;

The output of the above query shows the following output:

SUM(Emp_Salary) Emp_City
4000 Goa
9000 Delhi
8000 Jaipur

Now, suppose that you want to show those cities whose total salary of employees is more than 5000. For this case, you have to type the following query with the HAVING clause in SQL:

  1. SELECT SUM(Emp_Salary), Emp_City FROM Employee GROUP BY Emp_City HAVING SUM(Emp_Salary)>5000;

The output of the above SQL query shows the following table in the output:

SUM(Emp_Salary) Emp_City
9000 Delhi
8000 Jaipur

Example 2: Let's take the following Student_details table, which helps you to analyze the HAVING clause with the COUNT aggregate function:

Roll_No Name Marks Age
1 Rithik 91 20
2 Kapil 60 19
3 Arun 82 17
4 Ram 92 18
5 Anuj 50 20
6 Suman 88 18
7 Sheetal 57 19
8 Anuj 64 20

Suppose, you want to count the number of students from the above table according to their age. For this, you have to write the following query:

  1. SELECT COUNT(Roll_No), Age FROM Student_details GROUP BY Age ;

The above query will show the following output:

Count(Roll_No) Age
3 20
2 19
1 17
2 18

Now, suppose that you want to show the age of those students whose roll number is more than and equals 2. For this case, you have to type the following query with the HAVING clause in SQL:

  1. SELECT COUNT(Roll_No), Age FROM Student_details GROUP BY Age HAVING COUNT(Roll_No) >= 2 ;

The output of the above SQL query shows the following table in the output:

Count(Roll_No) Age
3 20
2 19
2 18

Example 3: Let's take the following Employee table, which helps you to analyze the HAVING clause with MIN and MAX aggregate function:

Emp_ID Name Emp_Salary Emp_Dept
1001 Anuj 9000 Finance
1002 Saket 4000 HR
1003 Raman 3000 Coding
1004 Renu 6000 Coding
1005 Seenu 5000 HR
1006 Mohan 10000 Marketing
1007 Anaya 4000 Coding
1008 Parul 8000 Finance

MIN Function with HAVING Clause:

If you want to show each department and the minimum salary in each department, you have to write the following query:

  1. SELECT MIN(Emp_Salary), Emp_Dept FROM Employee GROUP BY Emp_Dept;

The output of the above query shows the following output:

MIN(Emp_Salary) Emp_Dept
8000 Finance
4000 HR
3000 Coding
10000 Marketing

Now, suppose that you want to show only those departments whose minimum salary of employees is greater than 4000. For this case, you have to type the following query with the HAVING clause in SQL:

  1. SELECT MIN(Emp_Salary), Emp_Dept FROM Employee GROUP BY Emp_Dept HAVING MIN(Emp_Salary) > 4000 ;

The above SQL query shows the following table in the output:

MIN(Emp_Salary) Emp_Dept
8000 Finance
10000 Marketing

MAX Function with HAVING Clause:

In the above employee table, if you want to list each department and the maximum salary in each department. For this, you have to write the following query:

  1. SELECT MAX(Emp_Salary), Emp_Dept FROM Employee GROUP BY Emp_Dept;

The above query will show the following output:

MAX(Emp_Salary) Emp_Dept
9000 Finance
5000 HR
6000 Coding
10000 Marketing

Now, suppose that you want to show only those departments whose maximum salary of employees is less than 8000. For this case, you have to type the following query with the HAVING clause in SQL:

  1. SELECT MAX(Emp_Salary), Emp_Dept FROM Employee GROUP BY Emp_Dept HAVING MAX(Emp_Salary) < 8000 ;

The output of the above SQL query shows the following table in the output:

MAX(Emp_Salary) Emp_Dept
5000 HR
6000 Coding

Example 4: Let's take the following Employee_Dept table, which helps you to analyze the HAVING clause with AVG aggregate function:

Emp_ID Name Emp_Salary Emp_Dept
1001 Anuj 8000 Finance
1002 Saket 4000 HR
1003 Raman 3000 Coding
1004 Renu 6000 Coding
1005 Seenu 5000 HR
1006 Mohan 10000 Marketing
1007 Anaya 4000 Coding
1008 Parul 6000 Finance

If you want to find the average salary of employees in each department, you have to write the following query:

  1. SELECT AVG(Emp_Salary), Emp_Dept FROM Employee_Dept GROUP BY Emp_Dept;

The above query will show the following output:

AVG(Emp_Salary) Emp_Dept
7000 Finance
4500 HR
6500 Coding
10000 Marketing

Now, suppose that you want to show those departments whose average salary is more than and equals 6500. For this case, you have to type the following query with the HAVING clause in SQL:

  1. SELECT AVG(Emp_Salary), Emp_Dept FROM Employee_Dept GROUP BY Emp_Dept HAVING AVG(Emp_Salary) > 6500 ;

The above SQL query will show the following table in the output:

AVG(Emp_Salary) Emp_Dept
7000 Finance
6500 Coding
10000 Marketing