Question

MySQL: What are the various SELECT statement clauses? Which ones are necessary and optional? How are the results filtered using COMPARISON and LOGICAL OPERATORS? Give one of these operators examples. Describe how parentheses can influence a query result.

Answer 1

The following are the various SELECT statement clauses:

• WHERE
• GROUP BY
• HAVING
• ORDER BY
• AS
• FROM
• SELECT

Every SELECT statement consists of SELECT and FROM clauses necessarily. Here, the role of the SELECT clause is to retrieve the columns from the particular table which is specified by the FROM clause.

Necessary clauses in SQL SELECT statement :-

These clauses are to present definitely to retrieve the required columns from the particular table.

The following are the Necessary clauses:-

• SELECT
• FROM

Optional clauses in SQL SELECT statement :-

These clauses are optionally used as per the requirement of the user in the SELECT statement. The columns can be retrieved even without the use of these clauses, hence they are optional.

The following are the Optional clauses:-

• WHERE
• GROUP BY
• ORDER BY
• HAVING
• AS

Filtering using Comparison Operators :-

The Comparison Operators usually contain two operands, here both operands may be the attributes of the table or one of the operand might be a number which is used to compare with the attribute while retrieving the data from table.

For example, consider a table Student(name,id,percent). Retrieve the Student records with percentages greater than 70.

Query:- Select * from Student where percent > 70;

Filtering using Logical Operators :-

The logical operators usually retrieves the columns after verifying the condition as true or false.

The main logical operators in SQL are AND, OR, NOT.

For example, consider the table Employee(ename,eid,age,salary). Retrieve the Employee records whose age is greater than 30 and salary is equal to 10000.

Query:- Select * from Employee where age > 30 AND salary = 10000;

Parenthesis in SQL :-

The parenthesis plays major role when there is some calculation part in the query. The parenthesis adds additional priority while evaluating the query. It clearly indicates that the attributes or calculations with attributes within the parenthesis are evaluated first followed by the calculation of other statement.