SQL BASICS

Andrey Avtomonov | Dubna | April 2013 [email protected]

AGENDA • Introduction to SQL • JOINS • Advanced queries • • • •

Subqueries Common table expressions Hierarchical queries Analytic functions

SQL LANGUAGE Structured Query Language • Programming language designed to manage data in relational databases (RDBMS).

DDL – Data Definition Language • Adding, changing or deleting database objects • Example : DROP TABLE [TABLE];

DML – Data Manipulation Language • Inserting, deleting and updating data in a database • Example : DELETE FROM [TABLE];

DCL – Data Control Language • authorizes users to access and manipulate data • Example : GRANT SELECT ON [TABLE] TO [USER];

SELECT STATEMENT  A query is an operation that retrieves data from one or more tables or views.  May contain subqueries  May perform aggregation  Several tables may be joined together to retrieve data.

JOINS

EQUIJOIN

Values in the two corresponding columns of the different tables must be equal

NON-EQUIJOIN

The relationship between the columns of the different tables must be other than equal

OUTERJOIN (LEFT, RIGHT, FULL)

It returns also the rows that do not satisfy the join condition

SELF-JOIN

Joining data in a table to itself

JOINS

OUTER JOINS An outer join does not require each record in the two joined tables to have a matching record. DAVID

1

1

IT

JAMES

2

2

FINANCE

ADAM

3

5

HR

There are 3 kinds of outer joins :

LEFT

FULL

RIGHT

DAVID

1

1

IT

DAVID

1

1

IT

DAVID

1

1

IT

JAMES

2

2

FINANCE

JAMES

2

2

FINANCE

JAMES

2

2

FINANCE

ADAM

3

5

HR

5

HR

ADAM

3

CARTESIAN PRODUCTS

Cartesian product is a result of a so-called CROSS JOIN. In Oracle if query has no join condition, then Cartesian product is returned

Select * From OneTwoThree t1, OneTwoThree t2

© http://dotnetslackers.com

EQUIJOINS  uses equality only comparisons in the join-predicate Example : Show department name for all employees. Select e.emp_name, e.emp_deptno, d.dept_name From emp e, dept d Where e.emp_deptno = d.deptno

NON-EQUIJOIN, SELF-JOIN  Self-join allows compare different values against other rows in the same table

Example : Show the list of employees that have less salary. Select e1.ename,e1.sal,e2.ename,e2.sal From emp e1, emp e2 Where e1.sal > e2.sal ENAME KING KING KING KING FORD FORD FORD JONES JONES CLARK

SAL

ENAME 4500 FORD 4500 JONES 4500 CLARK 4500 MILLER 3000 JONES 3000 CLARK 3000 MILLER 2975 CLARK 2975 MILLER 2450 MILLER

SAL 3000 2975 2450 1300 2975 2450 1300 2450 1300 1300

JOINS SYNTAX ANSI VS ORACLE  Equi-joins: • ANSI syntax SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.dept_id=d.dept_id;

• Oracle SELECT e.name, d.name FROM employees e, departments d WHERE e.dept_id=d.dept_id;

 Outer-joins :

• ANSI syntax (LEFT, RIGHT, FULL) SELECT e.name, d.name FROM employees e RIGHT OUTER JOIN departments d ON e.dept_id=d.dept_id;

• Oracle SELECT e.name, d.name FROM employees e, departments d WHERE e.dept_id(+)=d.dept_id;

ADVANCED SQL QUERIES Types

Question

SUBQUERIES

Who works in the same department as Clark?

Correlated SUBQUERIES

Who are the employees that receive more than the average salary of their department?

Inline Views

What are the employees salary and the minimum salary in their department?

Common table expression Hierarchical QUERIES

What are the people I work under starting with CEO

Analytic functions

What is the difference in currency exchange rates between yesterday an today

SUBQUERIES  A subquery is a query within a query and it is used to answer multiple-part questions.

 A subquery can reside in the WHERE clause, the FROM clause or the SELECT clause.  Can contain another subquery

* Can be correlated

NESTED SUBQUERY (NS) If a subquery is in the WHERE (or HAVING) clause of main query it is called a NESTED SUBQUERY.

Example : Which employees receive the least salary? Select * From emp e Where e.sal = (Select Min(sal) From emp)

 NS does not reference a main query  NS runs only once for entire outer query  NS is evaluated in “bottom-to-top” manner, i.e. inner most query is evaluated first

CORRELATED SUBQUERY (CS) A CS is a subquery that uses values from the outer query in its WHERE clause.  It is evaluated FOR EACH ROW produced by the parent query. Example : Which employees receive more then the average salary of their department? Select * From emp e Where sal > (Select Avg(sal) From emp i Where i.deptno = e.deptno)

 CS is evaluated in “top-to-bottom” manner, the order is : − − − −

The outer query receives a row For each candidate row a CS is executed once Depending on CS row is included into the result set The process repeats for all rows

INLINE VIEWS  AKA derived table in MSSQL , and subselect in Postgres  An In-line view is a subquery in the FROM clause of a SQL statement just as if it was a table. It acts as a data source!

 What are the employees salary and the MINIMAL salary in their department? Select e.ename,e.deptno, e.sal, t.msal From emp e, (Select i.deptno, Min(sal) msal From emp i Group By i.deptno) t Where e.deptno = t.deptno Order By e.deptno, e.sal  Inline views can be used to define a set of rows for UPDATE or DELETE statements Update (Select * from emp_new Where sal = 3000) Set sal = 3100 Delete From (Select * From emp_new Where job = 'ANALYST')

SUBQUERIES There are 4 types of subqueries :

 Who works in the same department as KING?

Select * From emp Where deptno = (Select deptno From emp Where ename = 'KING')  Who works in the same department as KING or SCOTT? Select * From emp Where deptno In ( Select deptno From emp Where ename In ('KING','SCOTT'))  Who works in the same department and under the same boss as SCOTT? Select * From emp Where (deptno,mgr) = (Select deptno, mgr From emp Where ename = 'SCOTT')  Who works in the same department and under the same boss as SCOTT or ALLEN? Select * From emp Where (deptno,mgr) In (Select deptno, mgr From emp Where ename In ('SCOTT','ALLEN'))

SUBQUERIES USAGE Subqueries can be used to:

 Define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement Create Table emp_new As Select * From emp Where 1=0

Insert Into emp_new (Select * From emp Where deptno = 20)

 Define one or more values to be assigned to existing rows in an UPDATE statement Update emp Set (sal, deptno) = (Select sal,deptno From emp Where ename = 'KING') Where ename = 'SCOTT'

COMMON TABLE EXPRESSION. (SUBQUERY FACTORING) CTE - a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT,UPDATE, or DELETE statement.

WITH subquery_name AS (the aggregation SQL statement) SELECT (query naming subquery_name);

 WITH clause is part of SQL-99 standard, Oracle supports CTE since version 9iR2  CTE can be considered as views within one specific query.  Single WITH section can contain several temporary result set definitions.  Optimizer may treat it as an inline view or as a temporary table.  Developer can influence the decision by using hints : MATERIALIZE or INLINE.

Example: Show each employees manager name and the number of people in the managers department.

SELECT e.ename, dc1.dept_count, m.ename, dc2.dept_count FROM emp e, (SELECT deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno) dc1, emp m, (SELECT deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno) dc2 WHERE e.deptno = dc1.deptno AND e.mgr = m.empno AND m.deptno = dc2.deptno;

WITH dept_count AS ( SELECT deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno) SELECT e.ename, dc1.dept_count, m.ename, dc2.dept_count FROM emp e, dept_count dc1, emp m, dept_count dc2 WHERE e.deptno = dc1.deptno AND e.mgr = m.empno AND m.deptno = dc2.deptno;

HIERARCHICAL QUERY  Recursive CTE  Oracle-specific CONNECT BY

Recursive CTE A recursive CTE requires four elements : 1. 2. 3. 4.

Anchor query (runs once and results form the top node in the hierarchy) Recursive query (runs multiple times, forms remaining nodes) UNION ALL statement to bind the Anchor and Recursive queries together. INNER JOIN statement to bind the Recursive query to the results of the CTE.

CONNECT BY Oracle has its own syntax to retrieve hierarchical data :

  

START WITH – specifies the root row(s) of the hierarchy. CONNECT BY – specifies the relationship between parent rows and child rows of the hierarchy. Reference between rows is defined by PRIOR operator. There could be several PRIOR conditions.

Select empno,lpad(ename,Level*8,' ') ename,mgr,Level From emp Connect By mgr = Prior empno Start With ename = 'JONES'

empno ename mgr level 7566 JONES 7839 7788 SCOTT 7566 7876 ADAMS 7788 7902 FORD 7566 7369 SMITH 7902

1 2 3 2 3

LOOPS AND PSEUDOCOLUMNS Stored data may contain infinite loops, Oracle detects such cases and returns runtime error. The NOCYCLE parameter in the CONNECT BY condition causes Oracle to return the rows in spite of the loop.

NB: Hierarchical queries can be used to generate specific number of rows. The query below generates 10 sequence numbers :

Select Level From dual Connect By Level