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 :

Clause

Explanation

SELECT

It’s a keyword followed by at least one column from the desired table.

DISTINCT

It’s an optional clause which suppresses duplicates.

*

Asterisk is a wild card character which is used to select all columns from a table. The vertical bar sign (|) means that you can use either * or specific column(s).

column [alias]

A list of specific column(s) of a table with optional custom headings.

FROM table name

It is the name of table you wish to fetch data from.

WHERE condition(s)

By using this clause you specify the desired data. The condition can have column names, expressions, and comparison operators.

GROUP BY   expression

It divides the rows in a table into smaller groups.

HAVING  group condition

Used in conjunction with GROUP BY, it is used to return only those groups which are specified  in the condition.

ORDER BY

With this clause you specify the display order of the fetched data set.

ASC|DESC

Orders the fetched rows in ascending or descending order.

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.

Operator

Description

+

Add

-

Subtract

*

Multiply

/

Divide

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 :

Operator

Description

Example

=

Equal to

SELECT * FROM Contact where Name = ‘Riaz Ahmed’;

Returns record # 1

<> or != or ^=

Not Equal to

SELECT * FROM Contact where Name <> ‘Riaz Ahmed’;

Returns record # 2

Greater than

SELECT * FROM Contact where Age > 25;

Returns record # 1

>=

Greater than or equal

SELECT * FROM Contact where Age >= 25;

Returns record # 1 and 2

Less than

SELECT * FROM Contact where Id < 2;

Returns record # 1

<=

Less than or equal

SELECT * FROM Contact where Id <= 2;

Returns record # 1 and 2

BETWEEN … AND …

Data range between two values

SELECT * FROM Contact where Id BETWEEN 2 AND 100;

Returns record # 2and 99

LIKE

Matches a pattern

SELECT * FROM Contact where message LIKE ‘%feed%’;

Returns record # 1

IN

Search multiple values

SELECT * FROM Contact where name IN (‘Riaz Ahmed’, ’Daniel Clarke’);

Returns record # 1 and 2

IS NULL

Fetches null values

SELECT * FROM Contact where name IS NULL

Returns record # 3

    Logical Operators :

Operator

Description

Example

AND

Both conditions must evaluate to true

SELECT * FROM Contact where name=’Riaz Ahmed’ AND age=30;

Returns record # 1

OR

Either condition returns true

SELECT * FROM Contact where name=’Riaz Ahmed’ OR age=25;

Returns record # 1 and 2

NOT

Evaluates the opposite condition

SELECT * FROM Contact where name IS NOT NULL

Returns record # 1 and 2

AND

OR combined

Can be used in the same logical expression

SELECT * FROM Contact where name=’Riaz Ahmed’ AND

(age=25 OR age=30 OR age=99);

Returns record # 1

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.

Evaluation Order

Operators

1

All comparison Operators (=, <>, >, >=, <, <=, BETWEEN, LIKE, IN, IS NULL)

2

AND

3

OR

·         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 1st July 03 and 30th 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.