CHAPTER NINE

9. SQL CLAUSES
9.1. Where Clause
WHERE Clause is used with SELECT, INSERT, UPDATE and DELETE clause to filter the results.
It specifies a specific position where you have to do the operation. 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.
Syntax: SELECT column1, column 2, ... column n FROM table-name WHERE conditions;
= Equal
> greater than
< less than
>= greater than or equal
<= less than or equal
<> not equal to
WHERE Clause with single condition: Let's retrieve data from a table "Student".
Example: SELECT * FROM Student WHERE address = 'Wolkite';

9.2. Where Clause with AND Condition

AND condition is used a query to create two or more conditions to be met. It is used in SELECT,
INSERT, UPDATE and DELETE statements.
• SELECT columns FROM tables WHERE condition1 AND condition2;
• The AND condition requires that both conditions should be met.
• The AND condition also can be used to join multiple tables in a SQL statement.
Example to retrieve data from the table “Student” with AND condition.
Example: SELECT * FROM Student WHERE address = 'Addis Ababa' AND ID < 5;
9.2.1. AND with Update Statement
This is how the "AND" condition can be used in the SQL UPDATE statement. For example:
UPDATE Student SET FirstName= 'Abebe' WHERE FirstName = 'Zekarias' AND City = ‘Adama’;
9.2.2. AND with Delete Statement
This is how an "AND" condition can be used in the DELETE statement.
Example: DELETE FROM Student WHERE FirstName = 'Ermias' AND LastName = 'Tomas';

1
By: Worku Muluye
 9.3. Where Clause with OR Condition
OR condition is used in a query to create a SQL statement where records are returned when any one
of the condition met. It can be used in a SELECT statement, INSERT statement, UPDATE statement
or DELETE statement.
Syntax: SELECT columns FROM tables WHERE condition 1 OR condition 2;
Example: SELECT * FROM Student WHERE address = 'Addis Ababa' OR address = 'Wolkite';
9.3.1. OR with Select Statement
Example: SELECT * FROM Student WHERE city = 'Bahir Dar' OR StudentID >= 3;
9.3.2. OR with Update Statement
Example: UPDATE Student SET FirstName = 'Abebe' WHERE FirstName = 'Dawit'
OR StudentID >3;
9.3.3. OR with Delete Statement
Example: DELETE FROM Student WHERE FirstName = 'Abebe' OR StudentID <=2;

9.4. Where Clause with Combination of AND & OR Conditions

You can also use the AND & OR conditions altogether with the WHERE clause.
Example: SELECT * FROM Student WHERE (address = 'Wolkite' AND FirstName = 'Abay') OR
(ID < 20);

9.5. Distinct Clause

MySQL DISTINCT clause is used to remove duplicate records from the table and fetch only the
unique records. The DISTINCT clause is only used with the SELECT statement.
Syntax: SELECT DISTINCT expressions FROM tables WHERE conditions;
9.6. 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 WITH clause using a single sub-query alias.
WITH <alias_name> AS (sql_sub
query_statement) SELECT column_list FROM <alias_name> [table name] [WHERE <join_con
dition>]
In WITH clause you can use multiple sub-query aliases.

2
By: Worku Muluye
 9.7. Order by Clause
ORDER BY clause is used for sorting data in ascending and descending order based on one or more
columns. Some databases sort query results in ascending order by default.
Syntax: SELECT expressions FROM tables WHERE conditions ORDER BY expression ASC |
DESC;
Expressions: It specifies the columns that you want to retrieve.
Tables: It specifies the tables, from where you want to retrieve records. There must be at least one
table listed in the FROM clause.
WHERE conditions: It specifies conditions that must be fulfilled for the records to be selected.
ASC: It sorts the result set in ascending order by expression (default, if no modifier is provider).
DESC: It sorts the result set in descending order by expression.
Note: You can use MySQL ORDER BY clause in a SELECT statement, SELECT LIMIT statement,
and DELETE LIMIT statement.
Example: SELECT * FROM Student WHERE address = 'Addis Ababa' ORDER BY FirstName;
Let us take a Student table having the following records:
STUDENT
StudentID FirstName LastName CITY
1 Abebe Dawit Addis Ababa
2 Ermias Yonas Adama
3 Sara Abraham Bahir Dar
4 Ermias Elias Adama
This is an example that would sort the result in ascending order by FirstName and City.
Example: SELECT * FROM Student ORDER BY FirstName, City;
StudentID FirstName LastName CITY
1 Abebe Dawit Addis Ababa
4 Ermias Elias Adama
2 Ermias Yonas Adama
3 Sara Abraham Bahir Dar
This is an example to sort the result in descending order by FirstName.
Example: SELECT * FROM Student ORDER BY FirstName DESC;
StudentID FirstName LastName CITY
3 Sara Abraham Bahir Dar
2 Ermias Yonas Adama
4 Ermias Elias Adama
1 Abebe Dawit Addis Ababa

3
By: Worku Muluye
 9.8. Order by Clause with Ascending Order
This statement is used to sort data in ascending order. If you miss the ASC attribute, SQL ORDER
BY query takes ascending order by default.
Example: SELECT FirstName FROM Student WHERE FirstName = 'Ermias' ORDER BY City;
StudentID FirstName LastName CITY
2 Ermias Yonas Adama
4 Ermias Elias Adama
9.9. Order by Clause with Descending Order
This statement is used to sort data in descending order. You should use the DESC attribute in your
ORDER BY clause as follows.
Example: SELECT FirstName FROM Student ORDER BY City DESC;
StudentID FirstName LastName CITY
1 Abebe Dawit Addis Ababa
2 Ermias Yonas Adama
4 Ermias Elias Adama
3 Sara Abraham Bahir Dar
9.10. Order by Clause with both ASC and DESC Order
Example: SELECT FirstName, address FROM Student WHERE StudentID< 5 ORDER BY
FirstName DESC, address ASC;
9.11. Sorting on Multiple Columns
Let's take an example of customer table which has many columns, the following SQL statement
selects all customers from the table named "Student", stored by the "city" and "FirstName" columns:
SELECT * FROM Student ORDER BY City, FirstName;

9.12. Group by Clause

The GROUP BY Clause is used to collect data from multiple records and group the result by one or
more column. It is generally used in a SELECT statement. You can also use some aggregate functions
like COUNT, SUM, MIN, MAX, AVG etc. on the grouped column.
Syntax: SELECT expression1, expression2, ... expression_n, aggregate_function (expression)
FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
Expression1, expression2, ... expression_n: It specifies the expressions that are not encapsulated
within an aggregate function and must be included in the GROUP BY clause.

4
By: Worku Muluye
Aggregate_function: It specifies a function such as SUM, COUNT, MIN, MAX, or AVG etc.
Tables: It specifies the tables, from where you want to retrieve the records. There must be at least
one table listed in the FROM clause.
WHERE conditions: It specifies the conditions that must be fulfilled for the records to be selected.
GROUP BY Clause with COUNT function:
let's count repetitive number of cities in the column address.
Example: SELECT address, COUNT(*) FROM Student GROUP BY address;
GROUP BY Clause with SUM function:
Using the SUM function and return the FirstName and total working hours of each employee.
Example: SELECT FirstName, SUM(working_hours) AS "Total working hours" FROM employee
GROUP BY FirstName;
GROUP BY Clause with MIN function: The following example specifies the minimum working
hours of the employees form the table "employee".
Example: SELECT FirstName, MIN(working_hours) AS "Minimum working hour" FROM
employee GROUP BY FirstName;
GROUP BY Clause with MAX function: The following example specifies the maximum working
hours of the employees form the table "employee".
Example: SELECT FirstName, MAX (working_hours) AS "Minimum working hour" FROM
employee GROUP BY FirstName;
GROUP BY Clause with AVG function: The following example specifies the average working
hours of the employees form the table "employees".
Example: SELECT FirstName, AVG(working_hours) AS "Average working hour" FROM employee
GROUP BY FirstName;

9.13. Having Clause

HAVING Clause is used with GROUP BY clause. It always returns the rows where condition is true.
Syntax: SELECT expression1, expression2, ... expression_n, aggregate_function (expression)
FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n
HAVING condition;
aggregate_function: It specifies any one of the aggregate function such as SUM, COUNT, MIN….
expression1, expression2, ... expression_n: It specifies the expressions that are not encapsulated
within an aggregate function and must be included in the GROUP BY clause.

5
By: Worku Muluye
WHERE conditions: It specifies the conditions for the records to be selected.
HAVING condition: It is used to restrict the groups of returned rows. It shows only those groups in
result set whose conditions are TRUE.
HAVING Clause with SUM function
Consider a table "employee" table having the following data. Here, we use the SUM function with
the HAVING Clause to return the emp_name and sum of their working hours.
Example: SELECT FirstName, SUM(working_hours) AS "Total working hours" FROM employee
GROUP BY FirstName HAVING SUM(working_hours) > 5;
Simply, it can also be used with COUNT, MIN, MAX and AVG functions.
9.14. Order by Random
Sometimes you may want to display random information like articles, links, pages etc. to your user.
If you want to fetch random rows from any of the databases, you have to use some queries which are
altered according to the databases. If you want to return a random row with MySQL:
Syntax: SELECT column FROM table ORDER BY RAND () LIMIT 1;

6
By: Worku Muluye