SUBQUERIES Database Management System

SUBQUERIES Database Management System

SUBQUERIES

Using a Subquery to Solve a Problem.

SUBQUERIES Database Management System : You can solve this problem by combining the two queries, placing one query inside the other query.

The inner query or the subquery returns a value that is used by the outer query or the main query.

Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the Second query.

Subquery Syntax

SELECT select_list FROM table

WHERE 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).

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.

Types of Subqueries

  • Single-row subqueries: Oueries that return only one row from the inner SELECT statement
  • Multiple-row subqueries: Oueries that return more than one row from the inner SELECT Statement

Single-Row Subqueries

A single-row subquery is one that returns one row from the inner SELECT statement. This type of Subquery uses a Single-row operator. The slide gives a list of Single-row operators.

Example

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

SELECT Ename, job

FROM emp WHERE job =(SELECT job FROM emp WHERE empno = 141);

Executing Single-Row Subqueries

SELECT Ename, job, sal FROM emp WHERE job =

(SELECT job FROM emp WHERE empno = 141)

AND Sal >

(SELECT sal FROM emp WHERE empno = 143)

Using Group Functions in a Subquery

You can display data from a main query by using a group function in a subquery to return a single row. The subquery is in parentheses and is placed after the comparison condition.

The HAVING Clause with Subqueries

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

SELECT deptno, MIN(sal) FROM emp

GROUP BY deptno

HAVING MIN(sal) >

(SELECT MIN(sal) FROM emp WHERE deptno = 50);

 

The HAVING Clause with Subqueries

You can use subqueries not only in the WHERE clause, but also in the HAVING clause. The Oracle server executes the subquery, and the results are returned into the HAVING clause of the main query.

The SOL statement on the slide displays all the dept that have a minimum Salgreater than that of dept 50.

Example

Find the job with the lowest average sal.

SELECT job, AVG(sal) FROM emp GROUP BY job

HAVING AVG(sal) = (SELECT MIN(AVG(sal) )FROM emp

GROUP BY job);

Multiple-Row Subqueries

Multiple-Row Subqueries

Subqueries that return more than one row are called multiple-row subqueries. You use a multiple-row operator, IN ANY ALL instead of a single-row operator, with a multiple-row subquery. The multiple-row operator expects one or more values.

Manipulating Data

Data Manipulation Language

A DML statement is executed when you:

  • Add new rows to a table
  • Modify existing rows in a table
  • Remove existing rows from a table

A transaction consists of a collection of DML statements that form a logical unit of work. Data Manipulation Language

Data manipulation language (DML) is a core part of SOL. When you want to add, update, or delete data in the database, you execute a DML statement. A collection of DML statements that form a logical unit of Work is called a transaction.

The INSERT Statement Syntax

  • Add new rows to a table by using the INSERT statement.
  • Only one row is inserted at a time with this Syntax.

INSERT INTO table [(column [, column…])] VALUES (value [, value…]);

Inserting New Rows

INSERT INTO dept(deptno, dname, mgr, loc)

VALUES (70, ‘Public Relations’, 100, 1700);

The UPDATE Statement Syntax

  • Modify existing rows with the UPDATE statement.
  • Update more than one row at a time, if required.

UPDATE table SET column = value [,column = value, …)

(WHERE condition);

The DELETE Statement

DELETE [FROM] table [WHERE condition];

DELETE FROM dept WHERE dname = ‘Finance’;

DELETE FROM dept WHERE deptno IN (30, 40);

Types of Joins

The Oracle9i database offers join syntax that is SOL: 1999 compliant. Prior to the 9i release, the join syntax was different from the ANSI standards. The new SOL: 1999 compliant join syntax does not offer any performance benefits over the Oracle proprietary join Syntax that existed in prior releases.

  • Use a join to query data from more than one table.
  • 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.

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column 1 = table2.column2;

Defining joins

When data from more than one table in the database is required, a join condition is used. Rows in one table can be joined to rows in another table according to commisionon values existing in Corresponding columns, that is, usually primary and foreign key columns.

To display data from two or more related tables, write a simple join condition in the WHERE clause. in the Syntax:

table 1.Column denotes the table and Column from which data is retrieved

table1.column 1 = is the condition that joins (or relates) the tables together

table2. Column.2

Guidelines

When writing a SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance database access.

If the same column name appears in more than one table, the column name must be prefixed with the table name.

To join n tables together, you need a minimum of n-1join conditions. For example, to join four tables, a minimum of three joins is required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.

Equijoins

To determine an employee’s deptname, you compare the value in the DEPTNO column in the EMP table ith the DEPTNO values in the DEPT table.

The relationship between the EMP and DEPT tables is an equijoin-that is, values in the DEPTNO Column on both tables must be equal. Frequently, this type of join involves primary and foreign key complements.

Note: Equijoins are also called simple joins or inner joins.

Retrieving Records with Equijoins

SELECT emp.empno, emp. Ename,

emp…deptno, dept.deptno,

dept.loc

FROM emp, dept

WHERE emp..deptno = dept.deptno;

 

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.
  • Using Table Aliases
  • Simplify queries by using table aliases.
  • improve performance by using table prefixes.

Table Aliases

Qualifying Column names with table names can be very time consuming, particularly if table names are lengthy. You can use table aliases instead of table names. Just as a Column alias gives a column another name, a table alias gives a table another name. Table aliases help to keep SQL code smaller, therefore using less memory. Notice how table aliases are identified in the FROM clause in the example. The table name is specified in full, followed by a space and then the table alias. The EMP table has been given an alias of e, and the DEPT table has an alias of d.

Guidelines

  • Table aliases can be up to 30 characters in length, but shorter is better.
  • If a table alias is used for a particular table name in the FROM clause, then that table alias must be substituted for the table name throughout the SELECT statement.
  • Table aliases should be meaningful.
  • The table alias is valid only for the current SELECT statement.

SELECT e.empno, e. Ename, e.deptno,

d.deptno, d. loc

FROM emp e, dept d       

WHERE e.deptno = d.deptno;

SUBQUERIES Database Management System

SUBQUERIES Database Management System

FROM emp e JOIN dept d

USING (deptno);

Retrieving Records with the USING Clause

The example shown joins the DEPTNO Column in the EMP and DEPT tables, and thus shows the location where an employee works.

This can also be written as an equijoin:

SELECT empno, Ename, emp.deptno, loc

FROM emp, dept

WHERE emp…deptno = dept.deptno,

Creating Joins with the ON Clause

Joins – Comparing SQL:1999 to Oracle Syntax

Oracle SOL: 1999 Equi-Join Natural/inner Join

Outer-Join Left Outer Join

Self-join Join ON

Non-Equi-join join USING

Cartesian Product CrOSS Join

LEFT OUTER JOIN SELECT e. Ename, e.deptno, d.diname FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno); RIGHT OUTER JOIN

SELECT e. Ename, e.deptno, d.dname FROM emple RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno); RIGHT OUTER JOIN

SUBQUERIES Database Management System

SUBQUERIES Database Management System

  • The join condition for the natural join is basically an equijoin of all columns with the same name.
  • To specify arbitrary conditions or specify columns to join, the ON clause is used.
  • The join condition is separated from other search conditions.
  • The ON clause makes Code easy to understand.
  • SELECT e.empono, e. Ename, e.deptno,

d.deptno, d. loc

FROM empe JOIN dept d

ON (e.deptno = d.deptno;

INNER Versus OUTER joins

  • In SQL:1999, the join of two tables returning only matched rows is an inner join.
  • A join between two tables that returns the results of the inner join as well as unmatched rows left(or right) tables is a left (or right) outer join.
  • A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join.