SQL SELECT QUERY
The SELECT (SQL) statement retrieves data from a database and returns it to you in the form of query results. At its simplest, the SELECT requires you to tell it which columns you want and from what table you want to obtain data. The basic syntax is as follows:
SELECT column1, column2,.....columnx FROM table_name;
The order of the rows in the result table usually reflects the order in which the records were first entered into the database. SQL also provides a shorthand way of selecting all the columns without having to type out all their names. To get all columns, just type an asterisk (*):
Example:
The following will get data for all columns of employee table, output does not show all column because of space restriction:
SELECT * FROM employee;
Output:
Select (SQL) statement could be combinations of following keywords, some of which are mandatory and other are optional:
- Select (mandatory): SELECT clause lists the columns to retrieve data from.
- From (mandatory): FROM clause lists the tables the columns are located in.
- Where (optional): WHERE clause specifies result values must match certain criteria.
- Group By (optional): GROUP BY clause specifies groups for summary results.
- Having (optional): HAVING clause specifies filter conditions for summary results.
- Order By (optional): ORDER BY clause specifies the sort order.
SELECT:
The SELECT (SQL) clause contains the SELECT keyword and the list of database objects or expressions to include in the result set. You can use DISTINCT keywords to remove duplicate values from results.
FROM:
The FROM clause consists of the keyword FROM, followed by a list of tables separated by commas. Each table specification separated by comma (,) identifies a table or view containing data to be retrieved by the query. These tables are called the source tables of the query (and of the SELECT statement) because they are the source of all of the data in the query results.
Example:
The following query list the name and hire date of employeesfrom employee table.
SELECT full_name, hire_date
FROM employee;
Output:
SQL WHERE:
The WHERE (SQL) clause can be used to specify conditions. Only those rows will be returned in result table which satisfies conditions in WHERE clause. Following can be used for comparison in SQL WHERE clause:
Relational Operators: Following relational operators can be used:
>, >=, <=, <, =, and <>.
The relational operator condition is satisfied when the expression on either side of the relational operator fulfills the relation set up by the operator.
Example:
The following query list the name and hire date of employees with salary over $50,000.
SELECT full_name, salary
FROM employee
WHERE salary > 50000;
Output:
x BETWEEN y AND z;
SQL BETWEEN condition satisfies when values of x lies between y and z.
Example:
The following list query the name and hire date of employees with salary between $25,000 and 50,000.
SELECT full_name, salary
FROM employee
WHERE salary between 25000 and 50000;
Output:
x IN (y, z, ...);
IN (SQL) condition is satisfied when the value of x in included in the list of values to the right of the IN keyword.
Example:
The following query list the name of employees working in department number 10 & 20.
SELECT full_name
FROM employee
WHERE dept_no IN (1,4);
Output:
IS NULL: IS NULL condition is satisfied when the value or expression to the left of the IS NULL keywords is a NULL value.
Example:
The following query list all employee names that do not have any mgr_id defined.
SELECT full_name
FROM employee
WHERE mgr_id is NULL;
Output:
IS NOT NULL: IS NOT NULL condition is satisfied when the value or expression to the left of the IS NOT NULL; means it contains a value other than a NULL value.
Example:
The following query list all employee names in that have mgr_id defined
SELECT full_name
FROM employee
WHERE mgr_id is NOT NULL;
Output:
LIKE (SQL): LIKE (SQL) condition is satisfied when the string value or expression to the left of the LIKE (SQL) keyword meets the criteria specified in the case-sensitive quoted string to the right of the keyword.
Example:
The following query list names of all employee whose name starts with letter ‘A’.
SELECT full_name
FROM employee
WHERE full_name LIKE ‘A%’;
Output:
Two wildcard characters are used in LIKE (SQL) operator:
Percentage(%): specifies any number of characters.
Underscore(_): specifies single character.
To understand better consider the following example:
Example
The following query list name of all employee whose name has ‘A’ as second letter.
SELECT full_name
FROM employee
WHERE full_name like ‘_A%’;
Output:
SQL DISTINCT:
The (SQL) DISTINCT keyword can be added to the SQL SELECT statement’s column listing, directly after the SELECT keyword.
Example:
The following query list all department number that employees are working for:
SELECT DISTINCT dept_no FROM employee;
Output:
SQL ALIAS:
An (SQL) alias is simply a symbolic name for a collection of data.
Example:
The following query will return column lname from employee table under heading Surname:
SELECT last_name AS Surname FROM employee;
Output:
GROUP BY clause in SQL:
The Group by clause in SQL can be used to summarize data under a specific column:
Example:
The following query calculates salary sum for each department:
SELECT dept_no, SUM(salary)
FROM employee
GROUP BY dept_no;
Output:
Example 2:
The following query count number of employee in each department
SELECT dept_no, COUNT(*) number_of_employees
FROM employee
GROUP BY dept_no;
Output:
HAVING clause in SQL:
Just as the WHERE clause can be used to select and reject the individual rows that participate in a query, the HAVING clause can be used to select and reject row groups. The format of the HAVING clause parallels that of the WHERE clause, consisting of the keyword HAVING followed by a search condition. The HAVING clause thus specifies a search condition for groups.
Example:
The following query list all department numbers which have more that 2 employee in it.
SELECT dept_no, COUNT(*) number_of_employees
FROM employee
GROUP BY dept_no
HAVING count (*) > 2;
Output:
SQL ORDER BY:
SQL ORDER BY can be used specify the sort order for result.
Example:
The following query list name of all employees in alphabetic order:
SELECT full_name
FROM employee
ORDER BY full_name;
Output:
The default sort is an ascending sort. You can use the keywords (SQL ORDER BY) ASCENDING and (SQL ORDER BY) DESCENDING which you can abbreviate as ASC and DESC to control the sort on each column.
Example 2:
The following is a modification of the previous sort, but this time, it sort employee names in reverse order:
SELECT full_name
FROM employee
ORDER BY full_name DESC;
Output:
