SQL Queries and Subqueries, A query is an operation that retrieves data from one or more tables or views. In this reference, a top-level SELECT statement is called a query, and a query nested within another SQL statement is called a subquery.
SQL Queries and Subqueries
SQL Queries:
The SQL queries are the most common and essential SQL operations. Via an SQL query, one can search the database for the information needed. SQL queries are executed with the “SELECT” statement. An SQL query can be more specific, with the help of several clauses like
FROM – it indicates the table where the search will be made.
WHERE – it’s used to define the rows, in which the search will be carried. All rows, for which the WHERE clause is not true, will be excluded.
ORDER BY – this is the only way to sort the results in SQL. Otherwise, they will be returned in a random order.
Some of the simple Sql queries are,
1. Display all information in the tables EMP and DEPT.
SELECT * FROM emp;
SELECT * FROM dept;
2. Display only the hire date and employee name for each employee.
SELECT hiredate, ename FROM EMP;
3. Display the hire date, name and department number for all clerks.
SELECT hiredate, ename, deptno FROM emp WHERE job = ’CLERK’;
4. Display the names and salaries of all employees with a salary greater than 2000.
SELECT ename, sal FROM emp WHERE sal > 2000;
5. Display the names of all employees with an ‘A’ in their name.
SELECT ename FROM emp WHERE ename LIKE ’%A%’;
6. Display the names of all employees with exactly 5 letters in their name.
SELECT ename FROM emp WHERE ename LIKE’ ’;
7. Display the names and hire dates of all employees hired in 2012 or 2013
SELECT ename, hiredate FROM emp
WHERE hiredate LIKE ’%2012’ OR hiredate LIKE ’%2013’;
Or
SELECT ename, hiredate FROM emp
WHERE hiredate >= ’1/1/2012’ AND hiredate <= ’31/12/2013’;
8. Display the names and dates of employees with the column headers “Name” and “Start Date”
SELECT ename AS “Name”, hiredate AS “Start Date” FROM emp;
9. Display the names and hire dates of all employees in the order they were hired.
SELECT ename, hiredate FROM emp ORDER BY hiredate;
10. Display the names and salaries of all employees in reverse salary order.
SELECT ename, sal FROM emp ORDER BY sal DESC;
11. Display the department numbers of all departments employing a clerk.
SELECT DISTINCT deptno FROM emp WHERE emp. Job = ’CLERK’;
12. Display the maximum, minimum and average salary and commission earned.
SELECT max (sal), min (sal), avg (sal), max (comm), min (comm), avg (comm) FROM emp;
13. Display the department number, total salary payout and total commission payout for each department.
SELECT deptno, sum (sal), sum (comm) FROM emp GROUP BY deptno;
14. Display the department number, total salary payout and total commission payout for each department that pays at least one employee commission.
SELECT deptno, sum (sal), sum (comm) FROM emp GROUP BY deptno HAVING sum (comm) > 0;
15. Display the department number and number of clerks in each department.
SELECT deptno, count (job) FROM emp WHERE job = ’CLERK’ GROUP BY deptno;
16. Display the department number and total salary of employees in each department that employs four or more people.
SELECT deptno, sum (sal) FROM emp GROUP BY deptno HAVING count (empno) >= 4;
17. Display the employee number of each employee who manages other employees with the number of people he or she manages.
SELECT mgr, count (mgr) FROM emp WHERE mgr IS NOT NULL GROUP BY mgr;
18. Display the name of each employee with his department name.
SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
19. Display a list of all departments with the employees in each department.
SELECT dname, ename FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno;
SQL Sub queries:
A sub query is a SELECT query that is nested within another SELECT, INSERT, UPDATE, or DELETE statement. A sub query can also be nested inside another sub query. Sub queries can often be re-written into regular JOINs, however sometimes an existence sub query can perform better than equivalent non-sub query methods.
There are a few rules that sub queries must follow, they are
• Sub queries must be enclosed within parentheses.
• A sub query can have only one column in the SELECT clause, unless multiple columns are in the main query for the sub query to compare its selected columns.
• An ORDER BY cannot be used in a sub query, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a sub query.
• Sub queries that return more than one row can only be used with multiple value operators, such as the IN operator.
• The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
• A sub query cannot be immediately enclosed in a set function.
• The BETWEEN operator cannot be used with a sub query; however, the BETWEEN can be used within the sub query.
The complete syntax of a sub query is,
(SELECT [DISTINCT] subquery_select_argument
FROM {table_name | view_name}
{table_name | view_name}…
[WHERE search_conditions]
[GROUP BY aggregate_expression [, aggregate_expression] …]
[HAVING search_conditions])
There are three types of Sub queries, they are
• Sub queries that operate on lists by use of the IN operator or with a comparison operator modified by the ANY or ALL optional keywords. These sub queries can return a group of values, but the values must be from a single column of a table.
• Sub queries that use an unmodified comparison operator (=, <, >, <>) – these sub queries must return only a single, scalar value.
• Sub queries that use the EXISTS operator to test the existence of data rows satisfying specified criteria.
Sub query and IN operator: A sub query can be used with the IN operator as “expression IN (sub query)”. The sub query should return a single column with one or more rows to form a list of values to be used by the IN operation.
Sub queries that are introduced with the keyword IN take the general form:
WHERE expression [NOT] IN (sub query)
The only difference in the use of the IN operator with sub queries is that the list does not consist of hard-coded values.
Example: SELECT emp_last_name “Last Name”,
emp_first_name “First Name”
FROM employee
WHERE emp_ssn IN
(SELECT dep_emp_ssn
FROM dependent
WHERE dep_gender = ‘M’);
This statement is evaluated in two steps.
• First, the inner query returns the identification numbers of those employees that have male dependents.
• Next, these social security number values are substituted into the outer query as the listing that is the object of the IN operator. So, from a conceptual perspective, the outer query now looks like the following.
SELECT emp_last_name “Last Name”,
emp_first_name “First Name”
FROM employee
WHERE emp_ssn IN (999444444, 999555555, 999111111);
Like the IN operator, the NOT IN operator can take the result of a subquery as the operator object.
SELECT emp_last_name “Last Name”, emp_first_name “First Name”
FROM employee
WHERE emp_ssn NOT IN
(SELECT dep_emp_ssn
FROM dependent);
• The subquery shown above produces an intermediate result table containing the social security numbers of employees who have dependents in the dependent table.
• Conceptually, the outer query compares each row of the employee table against the result table. If the employee social security number is NOT found in the result table produced by the inner query, then it is included in the final result table.
Sub queries and EXISTS operator:
• When a subquery uses the EXISTS operator, the subquery functions as an existence test.
• The WHERE clause of the outer query tests for the existence of rows returned by the inner query.
• The subquery does not actually produce any data; rather, it returns a value of TRUE or FALSE.
• The general format of a subquery WHERE clause with an EXISTS operator is shown here.
• The NOT operator can also be used to negate the result of the EXISTS operator.
WHERE [NOT] EXISTS (subquery)
Example:
SELECT emp_last_name “Last Name”, emp_first_name “First Name”
FROM employee
WHERE EXISTS
(SELECT *
FROM dependent
WHERE emp_ssn = dep_emp_ssn);
Sub queries using an EXISTS operator are a bit different from other sub queries, in the following ways:
1. The keyword EXISTS is not preceded by a column name, constant, or other expression.
2. The SELECT clause list of a subquery that uses an EXISTS operator almost always consists of an asterisk (*). This is because there is no real point in listing column names since you are simply testing for the existence of rows that meet the conditions specified in the subquery.
3. The subquery evaluates to TRUE or FALSE rather than returning any data.
4. A subquery that uses an EXISTS operator will always be a correlated subquery.
• The EXISTS operator is very important, because there is often no alternative to its use.
• All queries that use the IN operator or a modified comparison operator (=, <, >, etc. modified by ANY or ALL) can be expressed with the EXISTS operator.
• However, some queries formulated with EXISTS cannot be expressed in any other way.
• The NOT EXISTS operator is the mirror-image of the EXISTS operator.
• A query that uses NOT EXISTS in the WHERE clause is satisfied if the subquery returns no rows.
Sub queries and Comparison operators:
The general form of the WHERE clause with a comparison operator is similar to that used thus far in the text. Note that the subquery is again enclosed by parentheses.
WHERE <expression> <comparison_operator> (subquery)
Example:
SELECT emp_salary
FROM employee
WHERE emp_salary > 40000;
The aggregate functions (AVG, SUM, MAX, MIN, and COUNT) always return a scalar result table.
Thus, a subquery with an aggregate function as the object of a comparison operator will always execute provided you have formulated the query properly.
Example:
SELECT emp_last_name “Last Name”,
emp_first_name “First Name”,
emp_salary “Salary”
FROM employee
WHERE emp_salary >
(SELECT AVG (emp_salary)
FROM employee);
Correlated Sub queries:
A correlated subquery is one where the inner query depends on values provided by the outer query. This means the inner query is executed repeatedly, once for each row that might be selected by the outer query.
Example:
SELECT emp_last_name “Last Name”,
emp_first_name “First Name”,
emp_dpt_number “Dept”,
emp_salary “Salary”
FROM employee e1 WHERE emp_salary =
(SELECT MAX (emp_salary)
FROM employee
WHERE emp_dpt_number = e1.emp_dpt_number);
• The subquery in this SELECT statement cannot be resolved independently of the main query.
• Notice that the outer query specifies that rows are selected from the employee table with an alias name of e1.
• The inner query compares the employee department number column (emp_dpt_number) of the employee table to the same column for the alias table name etc.
SQL Tutorial for Beginners
Follow me on social media:
