Introduction to SQL

- Brajesh Kumar - DWBI COE

Agenda

2
Company Confidential

Database Concepts Retrieving Data (Select statement) Restricting and Sorting Data Single-Row Functions Displaying Data from Multiple Tables Aggregating Data Using Group Functions Subqueries Manipulating Data Creating and Managing Tables Including Constraints Creating Views Controlling User Access Using SET Operators Practice Exercise

Database Concepts

Database Terminology

Database Logical structure to store data Database management system (DBMS)

Software used to create and interact with the database

4
Company Confidential

Database Components
Character :
Basic unit of data, Can be a letter, number, or special symbol

Field :
A group of related characters Represents an attribute or characteristic of an entity Corresponds to a column in the physical database

Record :
A collection of fields for one specific entity Corresponds to a row in the physical

File :
A group of records about the same type of entity

5
Company Confidential

Components Example

6
Company Confidential

Database Example

7
Company Confidential

Database Management System

Data storage : Manage the physical structure of the database Security: Control user access and privileges Multiuser access: Manage concurrent data access Backup: Enable recovery options for database failures Data access language: Provide a language that allows database access Data integrity: Enable constraints or checks on data Data dictionary: Maintain information about database structure

8
Company Confidential

Relational Database Concepts

A relational database is a collection of relations or two-dimensional tables. The relational model consists of the following:
Collection of objects or relations Set of operators to act on the relations Data integrity for accuracy and consistency

9
Company Confidential

Database Design

Systems Development Life Cycle (SDLC) Entity-Relationship Model (E-R Model) Normalization

10
Company Confidential

Systems Development Life Cycle (SDLC)

Systems Systems Systems Systems

operation

investigation Understanding the problem analysis Understanding the solution design Creating the logical and physical components implementation Placing completed system into

Systems maintenance and review Evaluating the

implemented system

11
Company Confidential

Entity-Relationship Model (E-R Model)

Key Components Entity: A thing of significance about which information needs to be known. Examples are departments, employees, and orders. Attribute: Something that describes or qualifies an entity. For example, for the employee entity, the attributes would be the employee number, name, job title, hire date, department number, and so on. Each of the attributes is either required or optional. This state is called optionality. Relationship: A named association between entities showing optionality and degree. Examples are employees and departments, and orders and items.
12
Company Confidential

Entity-Relationship Model (E-R Model)

Used to depict the relationship that exists among entities Model symbols:

13
Company Confidential

Relationships
The following relationships can be included in an E-R Model:
1) One-to-one
Each occurrence of data in one entity is represented by only one occurrence of data in the other entity Example: Each individual has just one Social Security Number (SSN) and each SSN is assigned to just one person

2) One-to-many

Each occurrence of data in one entity can be represented by many occurrences of the data in the other entity Example: A class has only one instructor, but each instructor can teach many classes

3) Many-to-many
Data can have multiple occurrences in both entities Example: A student can take many classes and each class is composed of many students Can not be included in the physical database

14
Company Confidential

Examples : E-R Model

15
Company Confidential

Relating Multiple Tables

Each row of data in a table is uniquely identified by a primary key (PK). You can logically relate data from multiple tables using foreign keys (FK).
Table Name: EMPLOYEES
Table Name: DEPARTMENTS

Primary Key
16
Company Confidential

Foreign Key

Primary Key

Database Normalization
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process:
eliminating redundant data (for example, storing the same data in more than one table) ensuring data dependencies make sense (only storing related data in a table).

Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
17
Company Confidential

Normalization Steps
1NF: Eliminate repeating groups, identify the primary key 2NF: Table is in 1NF and partial dependencies are eliminated 3NF: Table is in 2NF and transitive dependencies are eliminated

Relating Tables within the Database Once tables are normalized, certain tables are linked Tables are linked through a common field A common field is usually a primary key in one table and a foreign key in the other table

Composite Primary Key

More than one column is required to uniquely identify a row Can lead to partial dependency - a column is only dependent on a portion of the primary key

18
Company Confidential

First-Normal Form (1NF)

Eliminate duplicative columns from the same table. Create separate tables for each group of related data and identify each row with a unique column (the primary key).

19
Company Confidential

Second-Normal Form (2NF)

Remove subsets of data that apply to multiple rows of a table and place them in separate tables. Create relationships between these new tables and their predecessors through the use of foreign keys.
These rules can be summarized in a simple statement: 2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.

20
Company Confidential

Third-Normal Form (3NF)

Already meet the requirements of both 1NF and 2NF Remove columns that are not fully dependent upon the primary key.

21
Company Confidential

Structured Query Language (SQL)

By definition, Structured Query Language, is a database computer declarative language designed for managing data in relational database management systems (RDBMS) Used to: Create or modify tables Add data to tables Edit data in tables Retrieve data from tables ANSI and ISO standards An RDBMS is the software program used to create the database and it allows you to enter, manipulate, and retrieve data

22
Company Confidential

SQL Statements

23
Company Confidential

Summary
A DBMS is used to create and maintain a database A database is composed of a group of interrelated tables A file is a group of related records; a file is also called a table in the physical database A record is a group of related fields regarding one specific entity; a record is also called a row A record is considered unnormalized if it contains repeating groups A record is in first-normal form (1NF) if no repeating groups exist and it has a primary key Second-normal form (2NF) is achieved if the record is in 1NF and has no partial dependencies After a record is in 2NF and all transitive dependencies have been removed, then it is in third-normal form (3NF), which is generally sufficient for most databases

24
Company Confidential

Retrieving Data
(Select statement)

Select

26
Company Confidential

Basic SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; SELECT identifies what columns FROM identifies which table

27
Company Confidential

Select can be used at many places

Example to display select capabilities

28
Company Confidential

Exercise
Select all columns and all rows of a table Select specified columns Select distinct values for a particular column

29
Company Confidential

Writing SQL Statements

SQL statements are not case sensitive. SQL statements can be on one or more lines. Keywords cannot be abbreviated or split across lines. Clauses are usually placed on separate lines. Indents are used to enhance readability.

30
Company Confidential

Arithmetic Expressions

Create expressions with number and date data by using arithmetic operators.

SELECT emp_name, salary, salary + 300 FROM employees;

31
Company Confidential

Operator Precedence

Multiplication and division take priority over addition and subtraction. Operators of the same priority are evaluated from left to right. Parentheses are used to force prioritized evaluation and to clarify statements.

32
Company Confidential

Defining a Null Value

If a row lacks the data value for a particular column, that value is said to be null, or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as zero or a space. Zero is a number, and a space is a character.

33
Company Confidential

Null Values in Arithmetic Expressions

Arithmetic expressions containing a null value evaluate to null.

34
Company Confidential

Defining a Column Alias

A column alias: Renames a column heading Is useful with calculations Immediately follows the column name - there can also be the optional AS keyword between the column name and alias Requires double quotation marks if it contains spaces or special characters or is case sensitive

35
Company Confidential

Concatenation Operator
A concatenation operator: Concatenates columns or character strings to other columns Is represented by two vertical bars (||) Creates a resultant column that is a character expression SELECT first_name||last_name AS "Employees" FROM employees;
36
Company Confidential

Literal Character Strings

A literal is a character, a number, or a date included in the SELECT list. Date and character literal values must be enclosed within single quotation marks. Each character string is output once for each row returned. SELECT emp_name || is a || job_title AS "Employee Details" FROM employees;
37
Company Confidential

Displaying Table Structure DESC[RIBE] tablename Display all tables Select * from tab;

38
Company Confidential

Restricting and Sorting Data

Limiting the Rows Selected

Restrict the rows returned by using the WHERE clause.
SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];

The WHERE clause follows the FROM clause. WHERE restricts the query to rows that meet a condition condition is composed of column names, expressions, constants, and a comparison operator
40
Company Confidential

Conditions using Character Strings and Dates

Character strings and date values are enclosed in single quotation marks. Character values are case sensitive, and date values are format sensitive. The default date format is DD-MON-RR. Examples: ... WHERE hire_date=01-JAN-95 ... WHERE salary>=6000 ... WHERE last_name=Smith
41
Company Confidential

Comparison Conditions

42
Company Confidential

Other Comparison Conditions

43
Company Confidential

WHERE salary BETWEEN 2500 AND 3500; WHERE manager_id IN (100, 101, 201); WHERE first_name LIKE _S%;
You can use the ESCAPE identifier to search for the actual % and _ symbols WHERE job_id LIKE %SA\_% ESCAPE \;

WHERE manager_id IS NULL;

44
Company Confidential

Logical Conditions

45
Company Confidential

Rules of Precedence

46
Company Confidential

ORDER BY Clause
Sort rows with the ORDER BY clause
ASC: ascending order, default DESC: descending order

The ORDER BY clause comes last in the SELECT statement. SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr} [ASC|DESC]];
47
Company Confidential

Single-Row Functions

SQL Functions

49
Company Confidential

Single row functions

Manipulate data items Accept arguments and return one value Act on each row returned Return one result per row May modify the data type Can be nested Accept arguments which can be a column or an expression

function_name [(arg1, arg2,...)]

50
Company Confidential

Single-Row Functions

51
Company Confidential

Character Functions

52
Company Confidential

Character Functions

53
Company Confidential

Character Functions

54
Company Confidential

Case Manipulation Functions

55
Company Confidential

Character-Manipulation Functions

56
Company Confidential

Number Functions
ROUND: Rounds value to specified decimal
ROUND(45.926, 2) 45.93

TRUNC: Truncates value to specified decimal

TRUNC(45.926, 2) 45.92

MOD: Returns remainder of division

MOD(1600, 300) 100

57
Company Confidential

Working with Dates

SYSDATE function returns the Current Date. Arithmetic with Dates
Add or subtract a number to or from a date for a resultant date value. Subtract two dates to find the number of days between those dates. Add hours to a date by dividing the number of hours by 24.

58
Company Confidential

General Functions
These functions work with any data type
NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2)

59
Company Confidential

NVL Function
Converts a null to an actual value.

Data types that can be used are date, character, and number. Data types must match:
NVL(commission_pct,0) NVL(hire_date,01-JAN-97) NVL(job_id,No Job Yet)

60
Company Confidential

NVL2 Function
NVL2(expr1, expr2, expr3)
In the syntax: expr1 is the source value or expression that may contain null expr2 is the value returned if expr1 is not null expr3 is the value returned if expr2 is null

NVL2(commission_pct,SAL+COMM, SAL) income

61
Company Confidential

NULLIF Function
NULLIF (expr1, expr2)
In the syntax: expr1 is the source value compared to expr2 expr2 is the source value compared with expr1. (If it is not equal to expr1, expr1 is returned.)

NULLIF(LENGTH(first_name), LENGTH(last_name)) result

The NULLIF function is logically equivalent to the following CASE expression. CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
62
Company Confidential

Conditional Expressions
Provide the use of IF-THEN-ELSE logic within a SQL statement Use two methods:
CASE expression DECODE function (specific to oracle)

63
Company Confidential

The CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr END

64
Company Confidential

The CASE Expression

SELECT last_name, job_id, salary, CASE job_id WHEN IT_PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;

65
Company Confidential

The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])

66
Company Confidential

SELECT last_name, job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, .15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARY FROM employees;

67
Company Confidential

Displaying Data from Multiple Tables

Cartesian Products
A Cartesian product is formed when:
A join condition is omitted A join condition is invalid All rows in the first table are joined to all rows in the second table

To avoid a Cartesian product, always include a valid join condition in a WHERE clause.

69
Company Confidential

Generating a Cartesian Product

SELECT employee_id, department_id, location_id FROM employees, departments;

70
Company Confidential

Joining Tables
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; Write the join condition in the WHERE clause. Prefix the column name with the table name when the same column name appears in more than one table.

71
Company Confidential

Exercise
Join two tables and get the output from both the tables Add search condition

72
Company Confidential

Qualifying Ambiguous Column Names

Use table prefixes to qualify column names that are in multiple tables. Improve performance by using table prefixes. Distinguish columns that have identical names but reside in different tables by using column aliases.

73
Company Confidential

Using Table Aliases

Simplify queries by using table aliases. Improve performance by using table prefixes.
SELECT

e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id;
74
Company Confidential

Non-Equijoins

75
Company Confidential

Outer Joins

76
Company Confidential

Outer Joins Syntax

You use an outer join to also see rows that do not meet the join condition. The Outer join operator is the plus sign (+).

SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column;

SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+);

77
Company Confidential

Self Joins

78
Company Confidential

Self Joins
SELECT worker.last_name || works for || manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;

79
Company Confidential

Exercise
1. The following SELECT statement executes successfully: SELECT last_name, job_id, salary AS Sal FROM employees;

True/False
2. The following SELECT statement executes successfully: SELECT * FROM job_grades; True/False 3. There are three coding errors in this statement. Can you identify them? SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees;
80
Company Confidential

Exercise.
4. Display the name and job title of all employees who do not have a manager. 5. Display the name, salary, and commission for all employees who earn commissions. Sort data in descending order of salary and commissions. 6. Display the last names of all employees where the third letter of the name is an a.

81
Company Confidential

Aggregating Data Using Group Functions

What Are Group Functions?

Group functions operate on sets of rows to give one result per group.

83
Company Confidential

Types of Group Functions

AVG COUNT MAX MIN STDDEV SUM VARIANCE

84
Company Confidential

Group Functions

85
Company Confidential

Group Functions Syntax

SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];

86
Company Confidential

Guidelines for Using Group Functions

DISTINCT makes the function consider only nonduplicate values; ALL makes it consider every value including duplicates. The default is ALL and therefore does not need to be specified. The data types for the functions with an expr argument may be CHAR, VARCHAR2, NUMBER, or DATE. All group functions ignore null values. To substitute a value for null values, use the NVL or NVL2functions. The Oracle server implicitly sorts the result set in ascending order when using a GROUP BY clause. To override this default ordering, DESC can be used in an ORDER BY clause.

87
Company Confidential

Using the AVG and SUM Functions

You can use AVG and SUM for numeric data.

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE %REP%;

88
Company Confidential

Using the MIN and MAX Functions

You can use MIN and MAX for any data type.

SELECT MIN(hire_date), MAX(hire_date) FROM employees;

89
Company Confidential

COUNT Function
The COUNT function has three formats:
COUNT(*) COUNT(expr) COUNT(DISTINCT expr)

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfies the condition in the WHERE clause. COUNT(expr) returns the number of non-null values in the column identified by expr. COUNT(DISTINCT expr) returns the number of unique, non-null values in the column identified by expr.

90
Company Confidential

Using the COUNT Function

COUNT(*) returns the number of rows in a table. SELECT COUNT(*) FROM employees WHERE department_id = 50;

91
Company Confidential

Using the COUNT Function

COUNT(expr) returns the number of rows with non-null values for the expr. Display the number of department values in the EMPLOYEES table, excluding the null values. SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

92
Company Confidential

Using the DISTINCT Keyword

COUNT(DISTINCT expr) returns the number of distinct non-null values of the expr. Display the number of distinct department values in the EMPLOYEES table. SELECT COUNT(DISTINCT department_id) FROM employees;

93
Company Confidential

Group Functions and Null Values

Group functions ignore null values in the column. SELECT AVG(commission_pct) FROM employees;

94
Company Confidential

Using the NVL Function with Group Functions

The NVL function forces group functions to include null values. SELECT AVG(NVL(commission_pct, 0)) FROM employees;

95
Company Confidential

Creating Groups of Data

96
Company Confidential

Creating Groups of Data : The GROUP BY Clause Syntax

SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; Divide rows in a table into smaller groups by using the GROUP BY clause.

97
Company Confidential

Guidelines
If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause. Using a WHERE clause, you can exclude rows before dividing them into groups. You must include the columns in the GROUP BY clause. You cannot use a column alias in the GROUP BY clause. By default, rows are sorted by ascending order of the columns included in the GROUP BY list. You can override this by using the ORDER BY clause.
98
Company Confidential

Using the GROUP BY Clause

All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

99
Company Confidential

Using the GROUP BY Clause

The GROUP BY column does not have to be in the SELECT list.

SELECT AVG(salary) FROM employees GROUP BY department_id ;

100
Company Confidential

Grouping by More Than One Column

101
Company Confidential

GROUP BY Clause on Multiple Columns

SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;

102
Company Confidential

Wrong Queries Using Group Functions

Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.

SELECT department_id, COUNT(last_name) FROM employees;

SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function

Column missing in the GROUP BY clause

103
Company Confidential

Wrong Queries Using Group Functions

You cannot use the WHERE clause to restrict groups. You use the HAVING clause to restrict groups. You cannot use group functions in the WHERE clause. SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;
WHERE AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here

Cannot use the WHERE clause to restrict groups

104
Company Confidential

Excluding Group Results: The HAVING Clause

Use the HAVING clause to restrict groups:
1. Rows are grouped. 2. The group function is applied. 3. Groups matching the HAVING clause are displayed. SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
105
Company Confidential

Nesting Group Functions

Display the maximum average salary.

SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;

106
Company Confidential

Exercise
1. Group functions work across many rows to produce one result per group.
True/False

2. Group functions include nulls in calculations.

True/False

3. The WHERE clause restricts rows prior to inclusion in a group calculation.

True/False

107
Company Confidential

Exercise
4. Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number.

108
Company Confidential

Subqueries

Using a Subquery to Solve a Problem

Who has a salary greater than Abels?

110
Company Confidential

Subquery Syntax
SELECT FROM WHERE select_list table expr operator (SELECT select_list FROM table);

The subquery (inner query) executes once before the main query. The result of the subquery is used by the main query (outer query).
111
Company Confidential

Using a Subquery

112
Company Confidential

Guidelines for Using Subqueries

Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition. The ORDER BY clause in the subquery is not needed unless you are performing Top-N analysis. Use single-row operators with single-row subqueries and use multiple-row operators with multiple-row subqueries.

113
Company Confidential

Types of Subqueries

114
Company Confidential

Single-Row Subqueries
Return only one row Use single-row comparison operators

115
Company Confidential

Example
Display the employees whose job ID is the same as that of employee 141.

116
Company Confidential

SELECT FROM WHERE

last_name, job_id employees job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

117
Company Confidential

Using Group Functions in a Subquery

118
Company Confidential

The HAVING Clause with Subqueries

The Oracle server executes subqueries first. The Oracle server returns results into the HAVING clause of the main query.

119
Company Confidential

What is Wrong with this Statement?

SELECT FROM WHERE employee_id, last_name employees salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);

120
Company Confidential

What is Wrong with this Statement?

SELECT FROM WHERE employee_id, last_name employees salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);

ERROR at line 4: ORA-01427: single-row subquery returns more than one row

Single-row operator w ith multiple-row subquery

121
Company Confidential

Will this Statement Return Rows?

SELECT FROM WHERE last_name, job_id employees job_id = (SELECT job_id FROM employees WHERE last_name = Haas);

122
Company Confidential

Will this Statement Return Rows?

SELECT FROM WHERE last_name, job_id employees job_id = (SELECT job_id FROM employees WHERE last_name = Haas);

no rows selected Subquery returns no values

123
Company Confidential

Multiple-Row Subqueries
Return more than one row Use multiple-row comparison operators Example Find the employees who earn the same salary as the minimum salary for each department.
SELECT FROM WHERE last_name, salary, department_id employees salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

124
Company Confidential

Null Values in a Subquery

SELECT FROM WHERE emp.last_name employees emp emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr);

no rows selected

125
Company Confidential

Exercise
Write a query to display the last name and hire date of any employee in the same department as Zlotkey. Exclude Zlotkey.

126
Company Confidential

Thank You