DML Statements - Retrieving Data || DBMS Tutorial 7

DML Statements - Retrieving Data

DML Statements - Retrieving Data
DBMS Tutorial

Data Manipulation Language (DML):

Data Manipulation Language (DML) enables users to access or manipulate data in a relational database. DML provides commands for retrieval, creation, deletion, and modification of information in a database. DML requires a user to specify what data are needed without specifying how to get it. The database engine is left to figure out effective means of retrieving data.

Select Query Syntax:

SELECT query allows us to retrieve data from one or more tables in a relational database. It can be represented as:

Let us understand some illustrative algorithms for fetching data from the database.

Query:

SELECT *
FROM Employee

SELECT *
FROM Employee
WHERE Salary > 40000;

Illustrative Algorithm to fetch data:

for each tuple t in Employee
Add t to the result

for each tuple t in Employee
if Salary > 40000
Add t to the result

-
-

Select Clause:

Employee Table

All Columns:

Use * to fetch all attributes from the table. It is equivalent to specifying all columns.

Query:

SELECT * FROM Employee

Output:

Filter Columns:

Use comma-separated attribute names to fetch specific columns from the input table.

Query:

SELECT Id, EName, Salary FROM Employee

Output:

Alias without AS keyword:

Aliases are used to change column names in result. They must be provided immediately after an aliased column.

Query:

SELECT Id EmpId, EName EmpName, Salary FROM Employee

Output:

Alias with AS keyword:

An optional AS keyword can be provided between the alias and the aliased column.

Query:

SELECT Id AS EmpId, EName AS EmpName, Salary FROM Employee

Output:

A common error with Alias:

A common mistake while writing select statements is to forget a comma between two columns. In that case, the second column is treated as an alias and only one column is displayed.

Query:

SELECT Id EName, Salary FROM Employee

Output:

Expression:

Expressions can be used to calculate values.

Query:

SELECT EName, Salary * 2 AS Double_Salary FROM Employee

Output:

Constant:

A hard-coded value in the select clause will appear as an additional column in the result with the same value on all records.

Query:

SELECT EName, 30 AS Value FROM Employee

Output:

Distinct:

Let us now learn how to remove duplicates in SELECT Clause.

Duplicates:

The Select clause does not remove duplicates from the result even if multiple rows have the same values.

Query:

SELECT Dept FROM Employee

Output:

-
-

Distinct - Single Column:

Use DISTINCT clause to remove duplicates. Usage of DISTINCT should be avoided as far as possible as it can lead to performance issues.

Query:

SELECT DISTINCT Dept FROM Employee

Output:

Distinct - Multiple Column:

If DISTINCT is used with multiple columns then two rows are considered equal only if all their columns match.

Query:

SELECT DISTINCT Dept, Manager FROM EMPLOYEE

Output:

Distinct - Incorrect Usage:

DISTINCT must be used immediately after SELECT. Any attempt to use it between columns will result in an error.

Query:

SELECT Dept, DISTINCT Manager FROM EMPLOYEE

Output:

ORA-00936: missing expression

DISTINCT NULL:

Distinct can also be used with NULL columns to remove duplicate NULL values.

Query:

SELECT DISTINCT Bonus FROM Employee

Output:

-
-

Quiz 62: Which one of these is used with SELECT clause to fetch all columns from a table?

a) ALL
b) * ---
c) DISTINCT
d) AS

Quiz 63: Is it mandatory for the alias of a column to have a name different than the name of the column being aliased?

a) Yes
b) No ---

Quiz 64: Which of the following statements are TRUE about DISTINCT keyword?

a) The DISTINCT keyword can be used in SELECT and WHERE clauses
b) DISTINCT removes duplicates based on column immediately following DISTINCT
c) DISTINCT removes duplicates based on all the columns in the SELECT clause ---
d) Usage of DISTINCT should be avoided as far as possible due to performance issues ---

Exercise 12: Select Clause
Write a query to list all products from the Product table shown below.

Exercise 13: Select Clause
Write a query to list product id, price, and category for all products from the Product table.

Exercise 14: Select Clause
Write a query to list all product categories from the Product table.

-
-

WHERE Clause:

Employee Table

Comparison Operator 1:

Use comparison operators to restrict rows. The filter criteria can be on attributes that are not on the select clause.

Query:

SELECT ID, ENAME FROM Employee WHERE SALARY > 40000

Output:

Comparison Operator 1:

Text values need to be enclosed in single quotes.

Query:

SELECT ID, ENAME FROM Employee WHERE ENAME = 'James Potter'

Output:

AND Operator:

AND operator can be used to combine multiple conditions when all conditions must evaluate to true.

Query:

SELECT ID, ENAME FROM Employee WHERE SALARY >= 30000 AND DEPT = 'ETA'

Output:

OR Operator:

OR operator can be used to combine multiple conditions when only one of the conditions must evaluate to true.

Query:

SELECT ID, ENAME FROM Employee WHERE SALARY > 75000 OR DEPT = 'ICP'

Output:

BETWEEN:

BETWEEN operator is used to checking for values within a range. The result includes both the boundary values.

Query:

SELECT ID, ENAME FROM Employee WHERE SALARY BETWEEN 30000 and 50000

Output:

IN:

IN operator is used to check for multiple values of an attribute. It is equivalent to OR operation on multiple equality condition on the attribute on individual values.

Query:

SELECT ID, ENAME FROM Employee WHERE ID IN (2,3)

Output:

IN with duplicates:

If IN clause contains duplicate values then the database server will remove duplicates before executing the query.

Query:

SELECT ID, ENAME FROM Employee WHERE DEPT IN ('ETA', 'ETA')

Output:

NOT IN:

NOT operator is used to negate the condition.

Query:

SELECT ID, ENAME FROM Employee WHERE ID NOT IN (2,3)

Output:

= NULL:

Equal to operator cannot be used to check for NULL values

Query:

SELECT ID, EName FROM Employee WHERE BONUS = NULL

Output:

no rows selected.

IS NULL:

IS NULL operator is used to checking NULL values in columns.

Query:

SELECT ID, EName FROM Employee WHERE BONUS IS NULL

Output:

IS NOT NULL:

IS NOT NULL operator is used to check if not null values are present in a column.

Query:

SELECT ID, EName FROM Employee WHERE BONUS IS NOT NULL

Output:

IN NULL:

If NULL is used with IN clause then the conditioned behavior is similar to = NULL (not is NULL)

Query:

SELECT ID, ENAME FROM Employee WHERE BONUS IN (NULL)

Output:

no rows selected.

CHAR:

CHAR data types are stored with trailing spaces. While filtering them using equality operator you need not provide trailing spaces.

Query:

SELECT Id, EName, Designation FROM Employee WHERE Designation = 'PM'

Output:

CHAR with trailing spaces:

Trailing spaces are ignored for CHAR data type.

Query:

SELECT Id, EName, Designation FROM Employee WHERE Designation = 'PM '

Output:

CHAR with leading spaces:

Leading spaces are not ignored for CHAR data type. If you use them you will not fetch any records.

Query:

SELECT Id, EName, Designation FROM Employee WHERE Designation = ' PM'

Output:

no rows selected.

VARCHAR2:

You can also filter for VARCHAR2 columns using the equality operator.

Query:

SELECT Id, EName, Designation FROM Employee WHERE EName = 'James Potter'

Output:

VARCHAR2 with trailing spaces:

Trailing spaces are not ignored for the VARCHAR2 data type.

Query:

SELECT Id, EName, Designation FROM Employee WHERE EName = 'James Potter '

Output:

no rows selected.

VARCHAR2 with leading spaces:

Leading spaces are not ignored for a VARCHAR2 data type.

Query:

SELECT Id, EName, Designation FROM Employee WHERE EName = ' James Potter'

Output:

no rows selected.

LIKE:

LIKE operator is used to matching a character pattern. It allows us to use wild cards. SQL supports two wild cards: '%' which matches with any number of characters and '_' which matches exactly one character.

Employee Table

Start Pattern:

Query:

SELECT ID, ENAME FROM Employee WHERE ENAME LIKE 'E%'

Output:

End Pattern:

Query:

SELECT ID, ENAME FROM Employee WHERE ENAME LIKE '%r'

Output:

Anywhere Pattern:

Query:

SELECT ID, ENAME FROM Employee WHERE ENAME LIKE '%m%'

Output:

-
-

Dates End Pattern:

Query:

SELECT ID, ENAME, DOJ FROM Employee WHERE DOJ LIKE '%14'

Output:

Fixed Pattern:

Query:

SELECT ID, ENAME, DOJ FROM Employee WHERE DOJ LIKE '__-___-__'

Output:

Mixed pattern:

Query:

SELECT ID, ENAME FROM Employee WHERE ENAME LIKE '_a%'

Output:

SELECT Syntax Errors:

We will now look at some common errors while using the SELECT statement.

Employee Table

Table name incorrect:

The table name must be correctly specified.
Query:

SELECT ID, EName FROM Employe

Output:
ORA-00942: table or view does not exist

Column name incorrect:

Column name must be correctly specified.
Query:

SELECT ID, Name FROM Employee

Output:
ORA-00904: "NAME": invalid identifier

WHERE condition incorrect:

If the WHERE clause has multiple conditions on the same column name then it has to be specified in each condition. Shortcut version allowed by some languages is not permitted.

Query:

SELECT ID, EName FROM Employee WHERE Designation = 'PM' OR 'SE'

Output:
ORA-00920: invalid relational operator

WHERE condition correct:

If WHERE clause has multiple conditions on the same column then the column name must be specified in all the conditions.

Query:

SELECT ID, EName FROM Employee WHERE Designation = 'PM' OR Designation = 'SE'

Output:

Quiz 65: Which of the following statement is true regarding the application of the WHERE clause?

a) Can only be used on columns that are present in SELECT clause
b) Is used to choose the columns that need to be fetched in the result
c) Can be used on all columns ---
d) None of the above

Quiz 66: Which of the following conditions are equivalent?

a) "C1 >= 10" and "NOT C1 < 10" ---
b) "C1 LIKE E%" and "C1 LIKE E_"
c) "C1 IN (1,2)" and "C1 = 1 OR C1 = 2" ---
d) "C1 IN (10,20)" and "C1 BETWEEN 10 AND 20"

Quiz 67: Consider a table Emp(Name,Salary)

SELECT Name FROM Emp WHERE Salary BETWEEN 1000 AND 2000;

The above query will display the names from the Emp table whose __________

a) Salary is >1000 and Salary <= 2000
b) Salary is >=1000 and Salary <= 2000 ---
c) Salary is >=1000 and Salary < 2000
d) Salary is >1000 and Salary < 2000

--------------------------------------
Let us look at some examples
--------------------------------------
-
-

Order of Query Execution:

A SELECT statement can have many clauses so it is important to understand the order in which these are executed to provide the result. However, for ease of understanding, we can refer to the execution order by FJWGHSDO.

A quick way to remember this is to use the mnemonic "Frank John's Wicked Grave Haunts Several Dull Owls". In this section we will focus on FROM, WHERE, SELECT and DISTINCT keywords.

The first step is always the FROM clause as we need to identify the tables from which data has to be fetched.

SELECT must be always be executed after the WHERE clause, e.g. we can have a query

Query:

SELECT EName FROM Employee WHERE Id = 1.

Here the filtering needs to happen on an Id column which is not included in the SELECT clause. Unless SELECT executes after WHERE this functionality cannot be supported.

DISTINCT removes duplicates based on all columns of the SELECT clause. These columns could be a subset of all columns of the table OR may even contain derived columns through the use of the expression. Thus DISTINCT is dependent on SELECT clause and its execution must happen after SELECT clause.

Exercise 15: Where ClauseWrite a query to display product id, description, category and discount of all Apparel products from the Product table.

Exercise 16: Where Clause

Write a query to display product id, description, category and discount of all products that do not have any description.

Exercise 17: Where ClauseWrite a query to display product id, description, category and discount of all Apparel products that have discount more than 5 percent.