[spring 2013] cs-217: database systems lab-2 manual · pdf file[spring 2013] cs-217: database...

[Spring 2013] CS-217: Database Systems

Lab-2 Manual Data Selection and Filtering using the SELECT Statement

V1.0 4/12/2016

GIFT Department of

Computing Science

Lab-2 Manual 2016

Spring 2016 CS-217: Database Systems (Lab) Page 1

Introduction to Lab-2

This lab reinforces the concepts introduced in Lab-1 and extends the usage of the SELECT

statement for data selection and data filtering. Is also introduces the usage of common arithmetic

function using the SELECT statement. Finally, students are introduced to the usage of WHERE

clause for the selection of data using the SELECT statement.

The main topics of this lab include:

1. Introduction to Structured Query Language (SQL)

2. Capabilities of SQL SELECT Statements

3. Basic SELECT Statement

4. Selecting ALL Columns

5. Selecting Specific Columns

6. Writing SELECT Statements

7. Column Heading Defaults

8. Arithmetic Expressions

9. Using Arithmetic Operators

10. Operator Precedence & Parenthesis

11. Column Aliases

12. Null Values

13. Concatenation Operator ( || )

14. Literal Character Strings

15. Eliminating Duplicate Rows

16. Limiting Rows Using Selection: the WHERE Clause

17. Character Strings and Dates

18. Comparison Conditions (=, >, <, >=, <=, <>, BETWEEN, IN, LIKE, IS NULL)

19. String Wildcards (%, _)

20. Using the NULL Conditions

21. Logical Conditions (AND, OR, NOT)

22. ORDER BY Clause

23. Practice SQL Statements

24. SOLUTIONS: Practice SQL Statements

Objectives of this Lab

At the end of this lab, students should be able to:

Understand the usage of SELECT statement for displaying selected rows

Understand the usage of various conditional and logical operators in the SELECT

statement

Understand the usage of simple string matching using LIKE and string wildcards

Lab-2 Manual 2016


Understand how to use the SQL SELECT statement for creating queries having multiple

selection criteria

Understand how to display data in sorted order using the SELECT statement

1. Introduction to Structured Query Language (SQL)

SQL (Structured Query Language) is the standard relational query language with statements to

create, remove, retrieve and maintain data in a relational database. SQL contains three main

types of commands, Data Definition language (DDL), Data Manipulation language (DML), and

Data Control language (DCL) statements.

2. Capabilities of SQL SELECT Statements

The SELECT statement is the most frequently used statement in SQL; it is used to retrieve data

from one or more tables. The names of the tables used in the statement must be listed in the

statement.

Using a SELECT statement, you can do the following:

Projection: Choose the columns in a table that are returned by a query. Choose as few or as

many of the columns as needed

Selection: Choose the rows in a table that are returned by a query. Various criteria can be

used to restrict the rows that are retrieved.

Lab-2 Manual 2016


Joining: Bring together data that is stored in different tables by specifying the link between

them. SQL joins are covered in more detail in a later lab.

3. Basic SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;

In its simplest form, a SELECT statement must include the following:

A SELECT clause, which specifies the columns to be displayed

A FROM clause, which identifies the table containing the columns that are listed in the

SELECT clause

In the syntax:

SELECT is a list of one or more columns

* selects all columns

DISTINCT suppresses duplicates

column|expression selects the named column or the expression

alias gives selected columns different headings

FROM table specifies the table containing the columns

Note: Throughout these labs, the words keyword, clause, and statement are used as follows:

A keyword refers to an individual SQL element.

For example, SELECT and FROM are keywords.

A clause is a part of a SQL statement.

For example, SELECT employee_id, last_name, ... is a clause.

A statement is a combination of two or more clauses.

For example, SELECT * FROM employees is a SQL statement.

4. Selecting ALL Columns

SELECT *

FROM dept;

You can display all columns of data in a table by following the SELECT keyword with an

asterisk (*).You can also display all columns in the table by listing all the columns after the

SELECT keyword.

5. Selecting Specific Columns

You can use the SELECT statement to display specific columns of the table by specifying the

column names, separated by commas. This example below displays all the employee numbers,

employee names, and salaries from the EMP table.

SELECT empno, ename, sal

FROM emp;

Lab-2 Manual 2016


Note: In the SELECT clause, specify the columns that you want, in the order in which you want

them to appear in the output.

6. Writing SELECT Statements

Using the following simple rules and guidelines, you can construct valid statements that are both

easy to read and easy to edit:

SQL statements are not case-sensitive (unless indicated).

SQL statements can be entered on one or many lines.

Keywords cannot be split across lines or abbreviated.

Clauses are usually placed on separate lines for readability and ease of editing.

Indents should be used to make code more readable.

Keywords typically are entered in uppercase; all other words, such as table names and

columns, are entered in lowercase.

7. Column Heading Defaults

In SQL+, column headings are displayed as uppercase. The column names are used for the

default headings. For example,

SELECT empno, ename, job

FROM emp;

Note: Character and Date column headings are left-aligned. Number column headings are right-

aligned.

8. Arithmetic Expressions

You may need to modify the way in which data is displayed, or you may want to perform

calculations or look at what-if scenarios. These are all possible using arithmetic expressions. An

arithmetic expression can contain column names, constant numeric values, and the arithmetic

operators.

Arithmetic Operators

The following arithmetic operators are available in SQL. You can use arithmetic operators in any

clause of a SQL statement (except the FROM clause).

Operator Description

+ Add

- Subtract

* Multiply

/ Divide

Lab-2 Manual 2016


Note: With DATE and TIMESTAMP data types, you can use the addition and subtraction

operators only.

9. Using Arithmetic Operators

You can create expressions using date and numeric data using mathematical operators (+, -, *, /).

SELECT ename, sal, sal + 300

FROM emp;

This example uses the addition operator to calculate a salary increase of $300 for all employees.

This example also displays a SAL+300 column in the output.

Note that the resultant calculated column SAL+300 is not a new column in the EMP table; it is

for display only. By default, the name of a new column comes from the calculation that

generated it—in this case, sal+300.

Note: The Oracle server ignores blank spaces before and after the arithmetic operator.

10. Operator Precedence & Parenthesis

In mathematical expressions, multiplication and division takes priority over addition and

subtraction. If operators within an expression are of same priority, then evaluation is done from

left to right. You can use parenthesis to force the expression within parenthesis to be evaluated

first.

SELECT ename, sal, 12*sal+100

FROM emp;

You can override the rules of precedence by using parentheses to specify the desired order in

which operators are to be executed.

SELECT ename, sal, 12*(sal+100)

FROM emp;

Rules of Precedence:

Multiplication and division occur before addition and subtraction.

Operators of the same priority are evaluated from left to right.

Parentheses are used to override the default precedence or to clarify the statement.

Lab-2 Manual 2016


11. Column Aliases

You can override a column heading display with a column alias. It renames a column heading

into something that is specified by the user. It is useful for reporting and calculations. The alias

immediately follows the column name and is enclosed in double quotation marks. There could

also be an optional AS keyword between the column name and the alias name. For example,

1. SELECT ename AS “Employee Name” FROM emp;

2. SELECT ename “Employee Name”, mgr “Manager” FROM emp;

3. SELECT ename “Employee Name”, sal*12 “Annual Salary” FROM emp;

12. Null Values

A null is a value that is unavailable, unassigned, unknown, or inapplicable. If a column is

missing a value, then that column is said to be null, or contains null. A null is not the same as a

zero or a space. A zero is a number, and a space is a character.

SELECT ename, sal, comm

FROM emp;

Applying a mathematical operator on a null value does not change its value.

SELECT ename, sal, comm + 100 AS “New Commission”

FROM emp;

Note: Columns of any data type can contain nulls. However, some constraints (NOT NULL and

PRIMARY KEY) prevent nulls from being used in the column.

In the COMM column in the EMP table, notice that only a sales manager or sales representative

can earn a commission. Other employees are not entitled to earn commissions. A null represents

that fact.

13. Concatenation Operator ( || )

You can link columns to other columns, arithmetic expressions, or constant values to create a

character expression by using the concatenation operator (||). Columns on either side of the

operator are combined to make a single output column.

1. SELECT ename || job FROM emp;

2. SELECT ename || ‘ ‘ || job AS “New Data” FROM emp;

Lab-2 Manual 2016


Null Values with the Concatenation Operator

If you concatenate a null value with a character string, the result is a character string.

ENAME || NULL results in ENAME.

14. Literal Character Strings

A literal value is a character, a number, or a date that is included in the SELECT list and that is

not a column name or a column alias. It is printed for each row returned. Date and character

literals must be enclosed in single quotation marks (‘ ‘). Number literals need not.

1. SELECT “Employee ” || ename || ‘ works as a ‘ || job “Employee Details”

FROM emp;

2. SELECT ‘Employee ‘ || ename || ‘ was hired on ‘ || hiredate || ‘ is working as || job || ‘ and is getting ‘ || sal AS

“New Data” FROM emp;

15. Eliminating Duplicate Rows

The default display of queries is all rows, including duplicate rows.

SELECT deptno

FROM emp;

To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause

immediately after the SELECT keyword.

SELECT DISTINCT deptno

FROM emp;

You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier

affects all the selected columns, and the result is every distinct combination of the columns.

SELECT DISTINCT deptno, job

FROM emp;

16. Limiting Rows Using Selection: the WHERE Clause

You can restrict the rows returned from the query by using the WHERE clause. A WHERE

clause contains a condition that must be met, and it directly follows the FROM clause. If the

condition is true, the row meeting the condition is returned.

SELECT <criteria>

FROM <table>

[WHERE condition(s)];

Lab-2 Manual 2016


The WHERE clause can compare values in columns, literal values, arithmetic expressions, or

functions.

SELECT empno, ename, job, deptno

FROM emp

WHERE deptno = 30;

17. Character Strings and Dates

Character strings and dates in the WHERE clause must be enclosed in single quotation marks

(‘‘). Number constants should not be enclosed in single quotation marks. All character searches

are case sensitive.

1. SELECT empno, job, deptno FROM emp

WHERE ename = ‘WARD’;

2. SELECT * FROM emp

WHERE hiredate = ’17-12-1980’;


WHERE ename = ‘king’;

4. SELECT empno, ename, job, deptno FROM emp

WHERE deptno = 30;

Note: Oracle databases store dates in an internal numeric format, representing the century, year,

month, day, hours, minutes, and seconds. The default date display is DD-MON-RR.

18. Comparison Conditions

Comparison conditions are used in conditions that compare one expression to another value or

expression.

Operator Meaning

= Equal to

> Greater than

>= Greater than or equal to

< Less than

Lab-2 Manual 2016


<= Less than or equal to

<>, !=, ^= Not equal to

BETWEEN … AND … Between two values inclusive

IN (set) Match any of a list a values

LIKE Match a character pattern

IS NULL Is a null value

1. SELECT empno, ename, job, sal FROM emp

WHERE sal <= 3000;


WHERE hiredate > ’17-12-1980’;

3. SELECT ename, sal FROM emp

WHERE sal BETWEEN 2500 AND 3500;

4. SELECT empno, ename, sal, deptno FROM emp

WHERE mgr IN (7698, 7566, 7782);

5. SELECT empno, ename FROM emp

WHERE ename BETWEEN ‘KING’ AND ‘SMITH’;

6. SELECT empno, ename FROM emp

WHERE ename IN (‘KING’, ‘SMITH’);

Note:

An alias cannot be used in the WHERE clause.

Values that are specified with the BETWEEN condition are inclusive. You must specify

the lower limit first.

You can also use the BETWEEN condition on character values.

The IN condition can be used with any data type.

Lab-2 Manual 2016


19. String Wildcards ( %, _ )

You may select rows that match a character pattern by using the LIKE condition. The character

pattern-matching operation is referred to as wildcard search. Two symbols can be used to

construct the search string:

Symbol Description

% Represents any sequence of zero or more characters

_ Represents any single character


WHERE ename LIKE ‘S%’;


WHERE ename LIKE ‘_I%’;


WHERE ename LIKE ‘%N’;

The LIKE condition can be used as a shortcut for some BETWEEN comparisons. The following

example displays the last names and hire dates of all employees who joined between January

1995 and December 1995:

SELECT ename, hiredate

FROM emp

WHERE hiredate LIKE '%95';

20. Using the NULL Conditions

The NULL conditions include the IS NULL condition and the IS NOT NULL condition. The

IS NULL condition tests for nulls. We cannot use a = to test nulls.


WHERE mgr IS NULL;


WHERE comm IS NOT NULL;

Lab-2 Manual 2016



WHERE comm IS NULL;


WHERE comm IS NOT NULL;

21. Logical Conditions (AND, OR, NOT)

A logical condition combines the result of two component conditions to produce a single result

based on them or inverts the result of a single condition. A row is returned only if the overall

result of the condition is true.

Operator Meaning

AND Returns TRUE if both component conditions are true

OR Returns TRUE if either component condition is true

NOT Returns TRUE if the following condition is false


WHERE sal >= 2000

AND job LIKE ‘%MAN%’;


WHERE sal >= 2000

OR job LIKE ‘%MAN%’;


WHERE job NOT IN (‘PRESIDENT’, ‘CLERK’);

22. ORDER BY Clause

The order of rows returned in a query result is undefined. The ORDER BY clause can be used to

sort the rows. If you use the ORDER BY clause, it must be the last clause of the SELECT

statement. The value ASC will be used to sort data in ascending order (default order), and DESC

will sort the data in descending order.

1. SELECT ename, job, sal FROM emp

ORDER BY hiredate;

2. SELECT ename, job, sal

Lab-2 Manual 2016


FROM emp

ORDER BY empno DESC;

You can also sort using the column alias in the ORDER BY clause.

SELECT ename, sal, 12 * sal AS NewSal

FROM emp

ORDER BY NewSal;

You can also sort query result on more than one column.

SELECT empno, ename, sal, deptno

FROM emp

ORDER BY deptno, sal DESC;

You can also sort by a column that is not included in the SELECT list.

23. Practice SELECT Statements

Write SELECT statements for the following:

1. List the names and jobs for all employees.

2. List the employee ids, hire dates, and department numbers of all employees.

3. List the names, salaries and hire dates of all employees.

4. List the department number and names of all departments.

5. List the locations of all departments.

6. List the names and locations of all departments.

7. List all the grades and the low salary figures.

8. List all the low and high salary figures.

9. List all data from the department table.

10. List all data from the bonus table.

11. List all data from the salgrade table.

12. Create a query to display unique jobs from the employee table.

13. Display the name concatenated with job, separated by a comma and space, and name the

column Employee and Title.

14. Create a query to display all the data from the employee table. Separate each column by a

comma. Name the column The_Output.

15. Create a query to display the name and salary of employees earning more than 1000.

16. Create a query to display the name and department number for employee number 7566.

17. Display the employee name, job, and start date of employees hired between June 9, 1981 and

July 13, 1987.

18. Display the name and department number of all employees in departments 20 and 30 in

alphabetical order by name.

Lab-2 Manual 2016


19. Display the name and hiredate of every employee who was hired on July 13, 1987.

20. Display the name and job title of all employees who do not have a manager.

21. Display the name, salary and commission of all employees who earn commission. Sort the

data in descending order of salary and commissions.

22. Display the names of all employees where the third letter of the name is A.

23. Display the names of all employees who have an A and E in their names.

24. Display the name, job, and salary of all employees whose job is salesman or clerk and whose

salary is not equal to 1000, 1500, or 2000.

25. Due to budget issues, the HR department needs a report that displays the name and salary of

employees who earn more than $2,000. Place your SQL statement in a text file named

lab_02_01.sql. Run your query.

26. The HR departments needs to find high-salary and low-salary employees. Modify

lab_02_01.sql to display the name and salary for any employee whose salary is not in the

range of $1,500 to $3,000. Place your SQL statement in a text file named lab_02_03.sql.

27. Create a report to display the last name, job ID, and start date for the employees with the

names of Allen and Blake. Order the query in ascending order by start date.

28. Display the last name and department number of all employees in departments 10 or 30 in

ascending alphabetical order by name.

29. Modify lab_02_03.sql to display the last name and salary of employees who earn between

$2,200 and $2,900 and are in department 20 or 30. Label the columns Employee and

Monthly Salary, respectively. Resave lab_02_03.sql as lab_02_06.sql. Run the statement in

lab_02_06.sql.

30. The HR department needs a report that displays the last name and hire date for all employees

who were hired in 1994.

31. Modify lab_02_06.sql to display the last name, salary, and commission for all employees

whose commission amount is 20%. Resave lab_02_06.sql as lab_02_15.sql. Rerun the

statement in lab_02_15.sql.

Lab-2 Manual 2016


24. SOLUTIONS: Practice SELECT Statements

1. SELECT ename, job FROM emp;

2. SELECT empno, hiredate, deptno FROM emp;

3. SELECT ename, sal, hiredate FROM emp;

4. SELECT deptno, dname FROM dept;

5. SELECT loc FROM dept;

6. SELECT dname, loc FROM dept;

7. SELECT grade, losal FROM salgrade;

8. SELECT losal, hisal FROM salgrade;

9. SELECT * FROM dept;

10. SELECT * FROM bonus;

11. SELECT * FROM salgrade;

12. SELECT DISTINCT(job) FROM emp;

13. SELECT ename | ‘ , ‘ | job AS “Employee and Title” FROM emp;

14. SELECT empno | ‘ , ‘ | ename | ‘ , ‘ | job | ‘ , ‘ | mgr | ‘ , ‘ | hiredate | ‘ , ‘ | sal | ‘ , ‘ | comm | ‘ , ‘ | deptno

AS “The_Output” FROM emp;

15. SELECT ename, sal FROM emp WHERE sal > 1000;

16. SELECT ename, deptno FROM emp WHERE empno = 7566;

17. SELECT ename, job, hiredate FROM emp WHERE hiredate BETWEEN ‘9-JUN-81’ AND ’13-JUL-87’;

18. SELECT ename, deptno FROM emp WHERE deptno IN(20, 30) ORDER BY ename;

19. SELECT ename, hiredate FROM emp WHERE hiredate = ’13-JUL-87’;

20. SELECT ename, job FROM emp WHERE mgr IS NULL;

21. SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL ORDER BY sal DESC, comm DESC;

22. SELECT ename FROM emp WHERE ename LIKE ‘__A%’;

23. SELECT ename FROM emp WHERE ename LIKE ‘%A%E%’;

24. SELECT ename, job, sal FROM emp WHERE job IN(‘SALESMAN’, ‘CLERK’) AND sal NOT IN(1000, 1500, 2000);

Lab-2 Manual 2016


25. SELECT ename, sal FROM emp WHERE sal > 2000;

Copy this query in a text file, named as lab_02_01.sql. Open the .sql file using @ for query

execution.

26. SELECT ename, sal FROM emp WHERE sal NOT BETWEEN 1500 AND 3000;

Place this query in a file named lab_02_03.sql.

27. SELECT ename, job, hiredate FROM emp WHERE ename IN(‘ALLEN’, ‘BLAKE’) ORDER BY hiredate;

28. SELECT ename, deptno FROM emp WHERE deptno IN(10, 30) ORDER BY ename;

29. SELECT ename AS “Employee”, sal AS “Salary” FROM emp WHERE ( sal BETWEEN 2200 AND 2900 ) AND ( deptno IN(20, 30) ) ;

Place this query in a file named lab_02_06.sql. Open the .sql file using @ for query

execution.

30. SELECT ename, hiredate FROM emp WHERE hiredate LIKE ‘%94’;

31. SELECT ename, sal, comm FROM emp WHERE comm = 0.2;

Place this query in a file named lab_02_15.sql. Open the .sql file using @ for query

execution.

[spring 2013] cs-217: database systems lab-2 manual · pdf file[spring 2013] cs-217: database...

Documents