SQL - The Shortest Route For Beginners (2015)

Chapter 3. Retrieve Data From Database

How Data is Extracted From Databases?

You use SQL’s SELECT command to extract data from your database. This command helps you fetch the desired data set, as well as specify the presentation order. In this chapter, you’ll execute all the options of this command to fetch data from the database.

Before you proceed, go through the following guidelines to write valid SQL statements that are easy both to read and to edit.

· SQL commands and statements are case insensitive.

· You can enter SQL statements on one line or can split them on multiple lines.

· Command words can neither be split across lines, nor can they be abbreviated.

· You can use tabs and indents for better readability.

· Place a semicolon (;) as a terminator before you execute a statement (not required in SQL Developer GUI).

· In SQL syntax, text defined under square brackets [ ] is optional, and that mentioned under curly braces { } is mandatory. Keywords (such as SELECT, FROM etc.) are presented in upper case letters, while user provided values (column/table names, conditions etc.) are displayed in lower case.

NOTE: In Chapter 1, you were introduced to the four command categories of SQL that you'll go through in this book, starting with SELECT, the sole data query command.

The SELECT Command

Syntax :

SELECT [DISTINCT] {* | column [alias], …}

FROM {table name}

[WHERE condition(s)]

[GROUP BY expression]

[HAVING group condition]

[ORDER BY {column, expression} [ASC|DESC]];

Syntax Explained :

Selecting All Data from a Table

You can use the SELECT command in its simplest form to retrieve all data from a table. For this purpose you use the asterisk (*) character to fetch data from all columns. In the following example, the SELECT statement requests data from all columns in all rows contained in the Departments table. Assuming that you’re connected to the HR schema using the Learn SQL connection, enter the following statement in the Worksheet pane – as shown in the figure below – and click the Run Statement button. The Query Result pane will appear, carrying the result for the executed query.

SQL Statement :

SELECT *

FROM departments;

Output :

Fetch Data from Selected Columns

The SELECT command also allows you to retrieve data from specific columns. By specifying the desired column names, separated by commas, you can restrict your query to display the result you wish to see. In the example present below, you restrict data from three columns (department number, last name of employee, and manager number) from the Employees table. Note that the result of your query displays columns in the same order as you specified them in the SELECT statement. Also note that all the three columns are separated by commas.

NOTE: In Oracle, you can use the DESCRIBE command to list columns in a table. For example, to see a list of all columns in the Employees table, you’ll enter:

DESCRIBE Employees

Use the following commands to get table definitions in other DBMSs:

SQL Server:

sp_help ‘employees’

DB2:

Describe table employees

MySQL;

Describe employees

MariaDB:

Show columns from employees;

PostgreSQL:

Id employees

SQLite:

Pragma table_info(employees);

SQL Statement :

SELECT department_id, last_name, manager_id

FROM employees;

Output :

Using Arithmetic Operators

You can create custom arithmetic expressions in your SELECT statement with the help of the common arithmetic operators defined in the table below. Besides SELECT, you are allowed to use these operators in any clause of a SQL statement except the FROM clause.

The following example uses the multiplication operator to display annual salary of all employees (by multiplying values in the table’s salary column with a constant value i.e. 12) along with their commission percentages. Note that the resultant salary column (SALARY * 12) is not actually created in the Employees table, but is generated for display purpose only.

SQL Statement :

SELECT last_name, salary * 12, commission_pct

FROM employees;

Output :

Arithmetic Operator Precedence

In the case of multiple arithmetic operators in a SQL statement, multiplication and division take precedence over addition and subtraction. If the operators are of the same priority, then they are evaluated from left to right. In the following example, the multiplication process is executed first and then the figure of 1000 is added to the result.

SQL Statement :

SELECT last_name, salary, 12 * salary + 1000

FROM employees;

Output :

Precedence with Parentheses

Parentheses are used to override the precedence rule. If you look at the previous example, the expression was executed in this order (for King): 12 * 24000 + 1000. In the current scenario, the result has changed because of the inclusion of the parentheses. In this case the expression is evaluated like this: (24000 + 1000) * 12.

SQL Statement :

SELECT last_name, salary, 12 * (salary + 1000)

FROM employees;

Output :

Change Column Headings

Usually when you query data from a table, the result is displayed with column names as the headings. Sometimes such headings are truncated and become difficult to understand. You encountered one such instance in the previous example, where you added an arithmetic expression to your query and the corresponding heading appeared as: 12 * (SALARY + 1000). You can change this heading into a meaningful title by using column alias clause. An alias is just an alternate name for a column. Add a column alias immediately after the column name (or expression), with a space between them. If an alias contains spaces, special characters (/ or #), or is case sensitive; it should be enclosed in double quotation marks. The AS keyword can also be included to comply ASNI SQL standards. The second statement uses a space between the two words and is therefore enclosed in double quotations.

SQL Statement :

SELECT last_name, salary, 12 * (salary + 1000) AS ANNUAL_SALARY

FROM employees;

SELECT last_name, salary, 12 * (salary + 1000) AS “Annual Salary”

FROM employees;

Output :

Joining Columns

In your SELECT statement you can join two or more columns, arithmetic expressions, or constant values into a single column using the concatenation operator which is represented by two vertical bars (II). The resultant column is generated as a character expression. In the following example, we combined first and last names of employees and presented the values under a new title: Employees.

SQL Statement :

SELECT first_name||last_name AS “Employees”

FROM employees;

Output :

Joining Columns (continued)

In the following example, you added a couple of constant values to your SELECT statement for better readability. First, you added a space between the two names with the help of a space character – represented by two single quotes having a single space between them. Secondly, you added a comma (also enclosed in single quotes) to separate the name of an employee from his/her job id.

SQL Statement :

SELECT first_name || ‘ ‘ || last_name || ‘, ’ ||job_id “Employees”

FROM employees;

Output :

NOTE

· Access and SQL Server use + for concatenation.

· Oracle, DB2, SQLite, and PostgreSQL support ||.

· MySQL and MariaDB provide Concat() function for this purpose.

NULL Values

If a table column lacks a value in it, that value is said to be NULL. Zeros or spaces cannot be defined as NULL values, because zero is a number, and a space is a character. A null value can be defined as: A value that is inapplicable, unavailable, unknown, or unassigned. If you look at the COMMISSION_PCT column’s data in the Employees table, you’ll notice that employees other than sales personnel have null values in this column and this is because the commission percentage is inapplicable to these employees.

The following example calculates a null commission for employees who have no commission percentage (null) mentioned against their names. Salaries multiplied with NULL commission percentages resulted in null commission values.

SQL Statement :

SELECT last_name, job_id, salary, commission_pct, salary * commission_pct/100 “Commission”

FROM employees;

Output :

Replacing NULL Values Using NVL Function

In the previous example, you saw that null values in the calculated commission column displayed (null) for employees other than sales representatives, which is not considered a good presentation of data. To override this default value with some acceptable text, you are provided with NVL function. Before we see a practical example, let’s first acquaint ourselves with its syntax:

Syntax :

NVL(expression1, expression 2)

According to the syntax, you put source column or expression containing null in expression1 position, and the target value that you wish to see instead of null is placed in expression2. NVL function can be used to convert any data type. One thing that you have to take care of is the use of expression2 which must be of same data type as that of expression1. For example, if you’re converting a numeric column, then you must use a number in expression2 position. Repeating the previous example, you’ll add the NVL function to convert null values – in COMMISSION_PCT (a table column) and COMMISSION (an arithmetic expression) – to zero in the following SQL statement.

SQL Statement :

SELECT last_name, job_id, salary,

nvl(commission_pct,0) "Percent", salary * nvl(commission_pct, 0)/100 "Commission"

FROM employees ;

Output :

Preventing Duplicates with DISTINCT

By default, a SELECT statement returns result of a query without eliminating duplicate records. For instance, if you execute select job_id from employees, you will get all records including some duplicate entries in the job_id column. In order to fetch unique job ids, you will have to use the DISTINCT clause just after the SELECT keyword, as shown in the following example.

SQL Statement :

SELECT distinct job_id

FROM employees;

Output :

Multiple Distinct Columns

In the previous example, you used just one column (JOB_ID) to display unique values. DISTINCT can also be applied to all the columns in a SELECT statement. With DISTINCT applied to multiple columns, the returned dataset displays distinct combination of the selected columns as shown in the following example, which shows all the different combinations of FIRST_NAME and JOB_ID. Since there are no two employees with the same first name and the same job id, the query fetches all rows from the table. If, for instance, there were two employees having Alexander as their first name enrolled under the job id IT_PROG, the result would have shown just one record.

SQL Statement :

SELECT distinct first_name, job_id

FROM employees;

Output :

Sorting Records

In all the previous examples you retrieved data without specifying any specific sort order. To sort the fetched data in some desired order, you use the ORDER BY clause. By default this clause sorts data in ascending order. You can use the DESC option to have the output in descending order. If used, this clause must be placed last in the SELECT statement. The following query is sorted on the last name of employees. Another alternate is to specify the position of the columns you wish to sort data on. For instance, rather than entering the column name (LAST_NAME), you can use its position like this: ORDER BY 1. Moreover, you can add as many columns to the ORDER BY clause as there are number of columns in a table. To sort by multiple columns, simply specify the column names separated by commas (just as you do when you are selecting multiple columns). For example, if you are displaying an employee list, you might want to display it sorted by last name and first name (first by last name, and then within each last name sort by first name).

NOTE: It is legal to sort the output by a column that is not retrieved in your query. In the case of multiple columns, the output is displayed exactly in the order of the sort sequence specified. For example, if you sort the query by last name and department id, the result will be sorted first by the last name and then by the department id.

SQL Statement :

SELECT last_name, department_id, hire_date

FROM employees

ORDER BY last_name;

Output :

Comparison and Logical Operators with Precedence Rules

Comparison and logical operators are used in the WHERE clause of a SQL statement (the WHERE clause is discussed in the next section). These operators assist in evaluating some conditions to fetch desired dataset. Suppose that you have a table named Contact having three records as shown in the following figure. These records are used in the “Example” column below to provide an overview about the comparison and logical operators that will be used thoroughly in upcoming exercises.

Comparison Operators :

Logical Operators :

NOTE: When used in conjunction with the four SQL operators (and with the arithmetic operator), the NOT logical operator produces negating results. For instance, if you add the NOT operator to the above examples as follows, you will get opposing records.

NOT Name = ‘Riaz Ahmed’

NOT BETWEEN 2 AND 100

NOT LIKE ‘%feed%’

NOT IN (‘Riaz Ahmed’, ’Daniel Clarke’)

name IS NOT NULL

In MariaDB, you can use the NOT operator to negate BETWEEN, IN, and EXISTS clauses as compared to other DBMS where it can be used to negate any conditions.

If the value being compared is a character string, (for example, Riaz Ahmed), or a date, then enclose it under single quotation marks; numbers should be entered without quotes.

Precedence Rules for Comparison and Logical Operators

Comparison and logical operators also follow some precedence rules as followed by the arithmetic operators. The following table lists the evaluation order for these operators.

· Comparison operators are evaluated first, even in negating expressions.

· AND has a higher precedence over OR.

· Equal precedence operators are evaluated from left to right.

Similar to the arithmetic operators, precedence for these operators can also be overridden by placing part of an expression in parentheses. Expression enclosed in parentheses are evaluated first, as demonstrated in the last example in the Logical Operators section above.

Filtering Data with the WHERE Clause

All the SELECT statements used in the previous examples were issued to retrieve all rows (records) from the defined tables. To limit the number of returned rows from the query, you use the WHERE clause which should be used immediately after the FROM clause. In this clause you specify a condition comprising three components: expression, comparison operator, and value.

Syntax :

… WHERE expression comparison operator value

Here, expression can be a table column, a constant value, or an expression itself. A condition is evaluated by comparing the data defined in the expression position with the value using the comparison operator. In subsequent exercises you will go through different flavors of the WHERE clause. But here, you are provided with couple of simple examples to show some basic usage of this clause.

In the first statement below, LOCATION_ID (a column name) performs as an expression, (=) is the comparison operator, and 1700 is the value which is being compared with the expression. The query retrieves all records (with all columns - *) for the departments established under location number 1700.

In the second example, we used the BETWEEN operator and specified a range of values to get a list of employees who are earning between 100 and 10000.

SQL Statements :

SELECT *

FROM departments

WHERE location_id = 1700;

SELECT *

FROM employees

WHERE salary between 100 and 10000

ORDER BY salary;

Comparing Character Strings in the WHERE Clause

To compare a character string in a WHERE clause, you have to enclose the string in single quotation marks (‘ ‘). The following example searches an employee whose first name is JOHN. When you execute the statement, no rows will be returned, because character strings are case sensitive and should be entered according to the data stored in the table. Since the first name of the searched employee is saved as John in the database, changing the character string from JOHN to John will fetch the match, as shown in the output screenshot.

Alternatively, you can use the UPPER built-in function to match the provided value, like this: WHERE UPPER(first_name)=’JOHN’. In this condition, the UPPER function is used to first convert the column value to upper case before matching it with the provided value. This function is discussed on page 53.

SQL Statement :

SELECT first_name, last_name, salary

FROM employees

WHERE first_name=’JOHN’;

Output :

The BETWEEN Operator

The BETWEEN operator is used in situations where you are searching records between, and inclusive of, a range. You provide a lower value just after the BETWEEN keyword, and put the higher value after the AND logical operator as demonstrated in the following example, where you are trying to fetch employees records whose join date is between 01-JAN-06 and 31-JAN-06, inclusive. Note that date values are also enclosed in single quotation marks and are defined in the default format as ‘DD-MON-YY’. See Page 57 for further details on changing default date formats.

SQL Statement :

SELECT first_name, last_name, hire_date

FROM employees

WHERE hire_date BETWEEN '01-JAN-06' AND '31-JAN-06';

Output :

The IN Operator

Suppose, you wish to see a list of departments under two different locations i.e. 1800 and 2700. If you use the BETWEEN operator, you’ll get a list of departments (including those falling between the two ranges) that you don’t intend to see. Another alternate will be to use a list of conditions like: location_id=1800 OR location_id=2700. Although it is a valid condition, but what if you add ten or more locations to your WHERE clause? Obviously, the statement will grow up in size and you won’t like typing such long statements that consequently enhances program code. To cope with the situation, you’re provided with the IN operator, where you just provide a list of desired values in parentheses, as shown in the following example. Since location_id is a numeric field, you provided the values without the quotation marks. Note that only characters and dates used in the IN list are enclosed within single quotes.

NOTE: The use of the IN operator is a cleaner way if you are working with long lists of conditions. Secondly, the IN operator can also contain another SELECT statement to form a more dynamic WHERE clause.

SQL Statement :

SELECT department_id, department_name, location_id

FROM departments

WHERE location_id IN (1800,2700);

Output :

The LIKE Operator

In many situations you search for records in your database whose exact values are unknown. Using the LIKE operator along with a character pattern (search string) you can easily find the match. The character pattern is constructed with the help of two special characters: % and _. The percent character (%) represents zero or more characters, while the underscore character (_) represents just one. The first example below searches all employees starting with the letter ‘A’. The second statement displays a list of all employees who do not contain ‘a’ within their names. The third example searches for employees whose first name has an ‘a’ as the second letter.

NOTE: Some DBMS are case sensitive, therefore you must take care of it while using the LIKE operator. For example, such DBMS would treat 'adam' and 'Adam' differently. Microsoft Access uses * instead of % and ? instead of _.

SQL Statement :

SELECT first_name

FROM employees

WHERE first_name LIKE ‘A%’;

Output :

SQL Statement :

SELECT first_name

FROM employees

WHERE first_name NOT LIKE ‘%a%’;

Output :

SQL Statement :

SELECT first_name

FROM employees

WHERE first_name LIKE ‘_a%’;

Output :

The IS NULL Operator

A null value, as mentioned earlier, is a value that is unavailable or inapplicable. It is neither said to be a zero nor can it be represented as a space. Also, you cannot use an equal to operator (=) in the WHERE clause to match null values. The valid procedure to find a null match is to use the IS NULL operator. In the first two examples below you won’t get any record. In the first statement the equal to (=) comparison operator is used, and in the second one, the statement is tried with an empty space (‘ ‘). In fact, the second query will also throw an “invalid number” error, because you are comparing a numeric column with an empty string. The third query is correct which uses the IS NULL operator to display records of sales representatives. To get negating records (other than sales personnel), use the NOT logical operator in the WHERE clause like this: commission_pct IS NOT NULL.

SQL Statements :

SELECT first_name, last_name, commission_pct

FROM employees

WHERE commission_pct=NULL;

SELECT first_name, last_name, commission_pct

FROM employees

WHERE commission_pct=’ ‘;

SELECT first_name, last_name, commission_pct

FROM employees

WHERE commission_pct IS NULL;

Output :

AND/OR Operators

Always remember the following rules for the AND and OR logical operators:

· AND will return rows only when both conditions are TRUE.

· OR requires either condition to be TRUE.

· AND has a higher precedence over OR.

Note that both these operators can be used together in the WHERE clause of a SQL statement to construct compound logical expression.

The WHERE clause in the following example is made up of two conditions, and the keyword AND is used to join them. AND instructs the database management system software to return only rows that meet all the conditions specified. If a record has department number 20, but the job id is not MK_MAN (Marketing Manager), it is not retrieved. Similarly, records having job id MK_MAN in other departments will not to be retrieved as well.

In the first example below, the AND operator is used to search employees working in department number 20 as MK_MAN. As you can see, the sole fetched record fulfills both conditions - mentioned before and after the AND operator. The example contains a single AND clause and is thus made up of two filter conditions. To narrow the result, you are allowed to add more filter conditions, each separated by an AND keyword like this: WHERE department_id=20 AND job_id=’MK_MAN’ AND first_name=’Michael’

SQL Statement :

SELECT first_name, department_id, job_id

FROM employees

WHERE department_id=20 AND job_id=’MK_MAN’;

Output :

The second logical operator that you can use in you SQL statement is the OR operator which is less restrictive and thus returns more rows. Most database management systems do not even evaluate the second condition in an OR WHERE clause if the first condition has already been met i.e. the rows are returned without considering the second condition if the first condition evaluates to true.

The query mentioned below, fetches all employees who are either in department number 20 OR who work as MK_MAN. The second record returned by the query doesn’t fulfills the job id part of the condition, but since it satisfies the first condition (department_id =20), it is picked by the query for display.

SQL Statement :

SELECT first_name, department_id, job_id

FROM employees

WHERE department_id=20 OR job_id=’MK_MAN’;

Output :

AND/OR Used Together

You can use any number of AND and OR operators together in a single WHERE clause to create complex filtering. But, putting these two together in a WHERE clause may also put you in trouble. Let's see an example of this. You are asked to provide a list of all employees working either in department number 10 or 20 and are earning more than or equal to 6000. In response, you created a query like this:

Select first_name||' '||last_name Employee, department_id, salary

from employees

where department_id=10 or department_id=20 and salary >= 6000

Output :

Look at the record of Jennifer Whalen. This should have been filtered out (because of the AND condition which says that the salary must be greater than or equal to 6000). Why this record appeared? The answer is the order of evaluation. As mentioned earlier, AND operator has higher precedence over OR, therefore the WHERE clause in the above statement was executed in the following order:

1. department_id = 20 and salary >= 6000 - returned second and third records.

2. department_id = 10 - returned record number 1 of Jennifer Whalen.

This happened because of the precedence rule that joined wrong operators together. Again, the solution to the problem lies in the use of parentheses to explicitly group related operators. Modify the statement by enclosing the first two filters within parentheses, as shown below.

Select first_name||' '||last_name Employee, department_id, salary

from employees

where (department_id=10 or department_id=20) and salary >= 6000

Output :

Since parentheses have a higher order of evaluation than either AND or OR operators, the OR condition (within the parentheses) was evaluated first like this:

1. (department_id=10 or department_id=20)

2. salary >= 6000

Now, the statement fetches records of employees working under department number 10 or department number 20 earning 6000 or more. Although Jennifer is working in department number 10, she doesn’t meet the second criterion and is thus filtered out.

Add Comments to SQL Statements

So far, you've been using very simple SQL statements to fetch the desired information from your DBMS. Once you become a guru, you start writing lengthy and complex statements to fulfill the needs of your application and ultimately your end user. Experienced coders usually add descriptive text to these complex statements for future reference. Such a text is known as comment, and is embedded before or within a SQL statement using two hyphens (--) or is entered within /* and */ character sets.

The first output below displays the use of embedded inline comments entered after two hyphens. The second one, which is used to create multi-line comments to comment out code, is enclosed within /* and */ characters.

Output :

Test Your Skill

1. How would you retrieve all records from the Locations table?

2. Show first name and salary for all employees.

3. Show first name and salary for all employees with salary appearing first.

4. Will the following statement execute successfully? Yes / No

Select first_name, job_id, salary Annual _Salary

From Employees

Where first_name = ‘Alexander’;

5. What about this one? Yes / No

Select *

From Employees

Where salary * 12 = 72000;

6. Identify the four errors in the following statement:

Select first_name, job_id, salary x 12 Annual Salary

From Employees

Where sal = 6000 and hire_date Like %05;

7. Create a query that displays first name, salary, and commission percent for all employees working as sales representatives.

8. Fetch first name, last name, and department number of all employees in department 10 and 20 in alphabetical order of last name. Also, join the two name columns, and set title to Employees.

9. Display a list of all employees containing ‘y’ in their last names.

10. Display list of employees availing no commission and are enrolled in department number 90.

11. Query the Employees table and fetch names of all employees along with hire dates employed between 1^st July 03 and 30^th September 03. Display result in ascending order on hire date.

12. Show names and salaries of all employees who are not making between 3000 and 10000.

13. Write a query that list names and salaries of employees working in department 10, 20, or 30 and earning more than 3000.

14. Display last_name, salary, and commission percent from the Employees table. Calculate annual commission of employees who earn a commission. Use the IN operator in the search condition. Also add an alias to the calculated column.

15. List the name of employees with their hiring dates who were not hired in 2003.

16. Retrieve the DEPARTMENT_ID column for all rows in the Employees table. Suppress duplicate values and sort the output in descending order.

17. Display last name, department number and salary columns from the Employees table for those employees who work either in department number 50 or 90 and also earn more than or equal to 5800.