bsl sql training part i

7/28/2019 BSL SQL Training Part I

1/127

Introduction to SQL

- Brajesh Kumar - DWBI COE


2/127

Company Confidential

2

Agenda

Database Concepts

Retrieving Data (Select statement) Restricting and Sorting DataSingle-Row FunctionsDisplaying Data from Multiple Tables

Aggregating Data Using Group FunctionsSubqueriesManipulating DataCreating and Managing TablesIncluding ConstraintsCreating ViewsControlling User AccessUsing SET OperatorsPractice Exercise


3/127

Database Concepts


4/127


4

Database Terminology

Database Logical structure to store dataDatabase management system (DBMS)

Software used to create and interact with thedatabase


5/127


5

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


6/127


6

Components Example


7/127Company Confidential

7

Database Example



8

Database Management System

Data storage : Manage the physical structure of the databaseSecurity: Control user access and privilegesMultiuser access: Manage concurrent data access

Backup: Enable recovery options for database failuresData access language: Provide a language that allows database accessData integrity: Enable constraints or checks on dataData dictionary: Maintain information about database structure



9

Relational Database Concepts

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



10

Database Design

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



11

Systems Development Life Cycle (SDLC)

Systems investigation Understanding the problemSystems analysis Understanding the solutionSystems design Creating the logical and physical components

Systems implementation Placing completed system intooperationSystems maintenance and review Evaluating theimplemented system



12

Entity-Relationship Model (E-R Model)

Key ComponentsEntity : A thing of significance about which informationneeds to be known. Examples are departments,employees, and orders.Attribute : Something that describes or qualifies anentity. For example, for the employee entity, theattributes would be the employee number, name, jobtitle, hire date, department number, and so on. Eachof the attributes is either required or optional. Thisstate is called optionality .Relationship : A named association between entitiesshowing optionality and degree. Examples areemployees and departments, and orders and items.



13

Entity-Relationship Model (E-R Model)

Used to depict the relationship that exists amongentitiesModel symbols:



14

Relationships

The following relationships can be included in an E-RModel: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


15/127


16/127



17

Database Normalization

Normalization is the process of efficientlyorganizing data in a database. There are twogoals 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 reducethe amount of space a database consumes andensure that data is logically stored.



18

Normalization Steps

1NF: Eliminate repeating groups, identify the primary key2NF: Table is in 1NF and partial dependencies are eliminated3NF: 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


19/127


19

First-Normal Form (1NF)

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


20/127


20

Second-Normal Form (2NF)

Remove subsets of data that apply to multiplerows of a table and place them in separatetables.Create relationships between these new tablesand 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 datain a table by extracting it, placing it in new table(s)and creating relationships between those tables.


21/127


21

Third-Normal Form (3NF)

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


22/127


22

Structured Query Language (SQL)

By definition, Structured Query Language , is a database computer declarativelanguage designed for managing data in relational database managementsystems (RDBMS)Used to: Create or modify tables Add data to tables

Edit data in tables Retrieve data from tablesANSI and ISO standards

An RDBMS is the software program used to create the database and it allows you toenter, manipulate, and retrieve data


23/127


23

SQL Statements


24/127


24

Summary

A DBMS is used to create and maintain a databaseA database is composed of a group of interrelated tablesA file is a group of related records; a file is also called a table in the physicaldatabaseA record is a group of related fields regarding one specific entity; a record is alsocalled a rowA record is considered unnormalized if it contains repeating groupsA record is in first-normal form (1NF) if no repeating groups exist and it has aprimary keySecond-normal form (2NF) is achieved if the record is in 1NF and has no partialdependenciesAfter a record is in 2NF and all transitive dependencies have been removed, then itis in third-normal form (3NF), which is generally sufficient for most databases


25/127

Retrieving Data(Select statement)


26/127

B i SELECT S


27/127


27

Basic SELECT Statement

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

FROM table;

SELECT identifies what columnsFROM identifies which table

S l b d l


28/127


28

Select can be used at many places

Example to display select capabilities


29/127


30/127


31/127


32/127


33/127

N ll V l i A ith ti E i


34/127


34

Null Values in Arithmetic Expressions

Arithmetic expressions containing a nullvalue evaluate to null.


35/127


36/127


37/127


38/127


39/127


40/127

Conditions using Character Strings and Dates


41/127


41

Conditions using Character Strings and Dates

Character strings and date values are enclosedin single quotation marks.Character values are case sensitive, and datevalues 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


42/127


43/127


44/127


45/127

Rules of Precedence


46/127

Company Confidential46

Rules of Precedence


47/127


48/127

Single-Row Functions


49/127


50/127

Single-Row Functions


51/127


Single Row Functions

Character Functions


52/127


Character Functions


53/127

Character Functions


54/127


Character Functions

Case Manipulation Functions


55/127


Case Manipulation Functions

Character-Manipulation Functions


56/127


Character Manipulation Functions


57/127

Working with Dates


58/127


g

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 daysbetween those dates.

Add hours to a date by dividing the number of hours by 24.


59/127

NVL Function


60/127


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)

NVL2 Function


61/127


NVL2(expr1, expr2, expr3)

In the syntax:expr1 is the source value or expression that may contain nullexpr2 is the value returned if expr1 is not nullexpr3 is the value returned if expr2 is null

NVL2(commission_pct,SAL+COMM, SAL)income

NULLIF Function


62/127


NULLIF (expr1, expr2)In the syntax:expr1 is the source value compared to expr2expr2 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 CASEexpression.

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

Conditional Expressions


63/127


p

Provide the use of IF-THEN-ELSE logic withina SQL statementUse two methods: CASE expression DECODE function (specific to oracle)


64/127

The CASE Expression


65/127


p

SELECTlast_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;


66/127


67/127


68/127

Displaying Data from Multiple Tables

Cartesian Products


69/127


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 tableTo avoid a Cartesian product, always includea valid join condition in a WHERE clause.

Generating a Cartesian Product


70/127


SELECT employee_id, department_id, location_idFROM employees, departments;

Joining Tables


71/127


SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column1 = table2.column2;

Write the join condition in the WHERE clause.Prefix the column name with the table namewhen the same column name appears in morethan one table.

Exercise


72/127


Join two tables and get the output from boththe tablesAdd search condition

Qualifying Ambiguous Column Names


73/127


Use table prefixes to qualify column namesthat are in multiple tables.Improve performance by using table prefixes.Distinguish columns that have identical namesbut reside in different tables by using columnaliases.

Using Table Aliases


74/127


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 dWHERE e .department_id = d .department_id;

Non-Equijoins


75/127


Outer Joins


76/127



77/127

Self Joins


78/127


Self Joins


79/127


SELECTworker.last_name || works for || manager.last_name

FROM

employees worker,employees manager

WHEREworker.manager_id = manager.employee_id ;

Exercise


80/127


1. The following SELECT statement executes successfully:

SELECT last_name, job_id, salary AS SalFROM 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_namesal x 12 ANNUAL SALARYFROM employees;

Exercise.


81/127


4. Display the name and job title of all employeeswho 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.


82/127

Aggregating Data Using Group

Functions

What Are Group Functions?


83/127


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

Types of Group Functions


84/127


AVGCOUNTMAXMINSTDDEVSUMVARIANCE

Group Functions


85/127


Group Functions Syntax


86/127


SELECT [column,] group_function(column), ...FROMtable[WHERE condition] [GROUP BY column][ORDER BYcolumn];

Guidelines for Using Group Functions


87/127


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

Using the AVG and SUM Functions


88/127


88

You can use AVG and SUM for numeric data.

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

Using the MIN and MAX Functions


89/127


89

You can use MIN and MAX for any data type.

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

COUNT Function


90/127


90

The COUNT function has three formats: COUNT(*) COUNT(expr) COUNT(DISTINCTexpr)

COUNT(*) returns the number of rows in a table that satisfy the

criteria of the SELECT statement, including duplicate rows androws containing null values in any of the columns. If a WHEREclause is included in the SELECT statement, COUNT(*) returns thenumber 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(DISTINCTexpr) returns the number of unique, non-nullvalues in the column identified by expr.

Using the COUNT Function


91/127


91

COUNT(*) returns the number of rows in atable.

SELECT COUNT(*)FROM employeesWHERE department_id = 50;

Using the COUNT Function


92/127


92

COUNT(expr) returns the number of rows withnon-null values for the expr.Display the number of department values in theEMPLOYEES table, excluding the null values.

SELECT COUNT(commission_pct)FROM employeesWHERE department_id = 80;

Using the DISTINCT Keyword


93/127


93

COUNT(DISTINCT expr) returns the number of distinct non-null values of the expr.Display the number of distinct departmentvalues in the EMPLOYEES table.

SELECT COUNT(DISTINCT department_id)FROM employees;


94/127

Using the NVL Function with Group Functions


95/127


95

The NVL function forces group functions toinclude null values.

SELECT AVG(NVL(commission_pct, 0))FROM employees;

Creating Groups of Data


96/127


96

Creating Groups of Data : The GROUP BY Clause Syntax


97/127


97

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 byusing the GROUP BY clause.

Guidelines


98/127


98

If you include a group function in a SELECT clause, youcannot select individual results as well, unless theindividual column appears in the GROUP BY clause. Youreceive an error message if you fail to include thecolumn list in the GROUP BY clause.

Using a WHERE clause, you can exclude rows beforedividing 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 thecolumns included in the GROUP BY list. You canoverride this by using the ORDER BY clause.

Using the GROUP BY Clause


99/127


99

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

SELECT department_id, AVG(salary)FROM employees

GROUP BY department_id ;

Using the GROUP BY Clause


100/127


100

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

SELECT AVG(salary)FROM employeesGROUP BY department_id ;

Grouping by More Than One Column


101/127


101

GROUP BY Clause on Multiple Columns


102/127


102

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

Wrong Queries Using Group Functions


103/127


103

Any column or expression in the SELECT list that isnot an aggregate function must be in the GROUP BYclause.

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

Wrong Queries Using Group Functions


104/127


104

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 employeesWHERE AVG(salary) > 8000GROUP 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

Excluding Group Results: The HAVING Clause


105/127


105

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_functionFROM table[WHERE condition]

[GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];

Nesting Group Functions


106/127


106

Display the maximum average salary.

SELECT MAX(AVG(salary))FROM employees

GROUP BY department_id;

Exercise


107/127


107

1. Group functions work across many rows toproduce 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

Exercise


108/127


108

4. Display the highest, lowest, sum, and averagesalary of all employees. Label the columnsMaximum, Minimum, Sum, and Average,respectively. Round your results to the nearest

whole number.


109/127

Subqueries

Using a Subquery to Solve a Problem


110/127


110

Who has a salary greater than Abels?

Subquery Syntax


111/127


111

SELECT select_listFROM tableWHERE expr operator

(SELECT select_listFROM table);

The subquery (inner query) executes once before the

main query.The result of the subquery is used by the main query(outer query).

Using a Subquery


112/127


112

Guidelines for Using Subqueries


113/127


113

Enclose subqueries in parentheses.Place subqueries on the right side of thecomparison condition.The ORDER BY clause in the subquery is notneeded unless you are performing Top-Nanalysis.Use single-row operators with single-rowsubqueries and use multiple-row operatorswith multiple-row subqueries.

Types of Subqueries


114/127


114

Single-Row Subqueries


115/127


115

Return only one rowUse single-row comparison operators

Example


116/127


116

Display the employees whose job ID is the sameas that of employee 141.


117/127


117

SELECT last_name, job_idFROM employeesWHERE job_id =

(SELECT job_id

FROM employeesWHERE employee_id = 141);

Using Group Functions in a Subquery


118/127


118

The HAVING Clause with Subqueries


119/127


119

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

What is Wrong with this Statement?


120/127


120

SELECT employee_id, last_name

FROM employeesWHERE salary =

(SELECT MIN(salary)FROM employees

GROUP BY department_id);

What is Wrong with this Statement?


121/127


121

SELECT employee_id, last_name

FROM employeesWHERE 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

Will this Statement Return Rows?


122/127


122


(SELECT job_idFROM employeesWHERE last_name = Haas);

Will this Statement Return Rows?


123/127


123


(SELECT job_idFROM employeesWHERE last_name = Haas);

no rows selectedSubquery returns no values

Multiple-Row Subqueries


124/127


124

Return more than one rowUse multiple-row comparison operators

ExampleFind the employees who earn the same salaryas the minimum salary for each department.

SELECT last_name, salary, department_idFROM employeesWHERE salary IN (SELECT MIN(salary)

FROM employeesGROUP BY department_id);

Null Values in a Subquery


125/127


125

SELECT emp.last_nameFROM employees empWHERE emp.employee_id NOT IN

(SELECT mgr.manager_idFROM employees mgr);

no rows selected

Exercise


126/127


126

Write a query to display the last name and hiredate of any employee in the same departmentas Zlotkey. Exclude Zlotkey.


127/127

Thank You

bsl sql training part i

Documents