Primary key constraints
This constraint avoids duplication of rows and does not allow NULL values, when enforced in a column or set of columns. A a result it is used to identify a row. A table can have only one primary key. If a primary key constraint is assigned to more than one column(i.e) or combination of columns it is said to be a composite primary key (or simply a composite key), which can contain a maximum of 16 columns. Primary key constraint cannot be defined in an alter table command when the table contains rows having NULL values.
Run and Check your out put
- select * from emp;
- select deptno, dname, loc from dept;
- select empno, ename, salary, hiredate from emp;
- select ename,salary, salary+100 from emp;
- select ename, salary, (salary*12)+100 from emp;
- select ename,slary, (salary*12)+comm. from emp;
- select ename,salary, (salary*12)+nvl(comm,0) from emp;
- select ename “Full Name”, salary*12 “Annual Salary” from emp;
- select ename||job from emp;
- select ename||’ is a ‘||job “Emp Detail” from emp;
- select deptno from emp;
- select distinct deptno from emp;
- select distinct deptno, job from emp;
- In SQL, we can display the structure of a table using the DESCRIBE command. The command shows the column names and data types, as well as whether a column must contain data or not.
- desc emp;
- describe emp;
Comparison Conditions
= > >= < <= <> BETWEEN…AND… IN(set) LIKE IS NULL
We can display rows based on a range of values using the BETWEEN range condition. The range that we specify contains a lower limit and an upper limit.
To test for values in a specified set of values, use the IN condition. The IN condition is also known as the membership condition.
We may not always know the exact value to search for. We can select rows that match a character pattern by using the LIKE condition. The character pattern-matching operation is referred to as a wildcard search. Two symbols can be used to construct the search string. Ie. %(zero or more characters) and _ (single character).
The NULL conditions include the IS NULL condition and the IS NOT NULL condition. The IS NULL condition tests for nulls. A null value means the value is unavailable, unassigned, unknown, or inapplicable. Therefore, we cannot test with = because a null cannot be equal or unequal to any value.
- select empno, ename,job, deptno from emp where deptno=20;
- select empno, ename, salary from emp where lastname=’Lama’;
- select * from emp where hiredate=’01-JAN-95’ and salary>=5000 and lastname=’Smith’;
- select ename,slary from emp where salary between 2300 and 3500;
- select empno,ename,salary,mgr from emp where deptno in(20,30,40);
- select ename,hiredate from emp where hiredate like ‘%95’;
- select ename from emp where ename like ‘_e%’;
- select ename,mgr from emp where mgr is null;
- select ename,salary,comm. from emp where comm. is null;
- select empno, ename,job,salary from emp where salary>=1000 and job like ‘SYS%’;
- select empno, ename,job,salary from emp where salary>=1000 or job like ‘SYS%’;
- select empno, ename from emp where job in (‘MANAGER’, ‘CLERK’, ‘PEON’);
The order of rows returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. If we use the ORDER BY clause, it must be the last clause of the SQL statement. We can specify an expression, or an alias, or column position as the sort condition. Syntax:
SELECT expression
FROM table
WHERE conditions
ORDER BY columns [ASC|DESC];
If the ORDER BY clause is not used, the sort order is undefined, and the oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.
To reverse the order in which rows are displayed, specify the DESC keyword after the column name in the ORDER BY clause.
- select ename, job, depatno, hiredate from emp order by hiredate;
- select ename, job ,deptno, hiredate from emp order by hiredate desc;
- select empno, ename, salary, salry*12 Annsal from emp order by annsal;
- select ename, deptno, salary from emp order by deptno,salary desc;
Displaying records from multiple tables.
Sometimes we need to use data from more than one table. Here we can fetch record using SELECT statement from two or more than two tables using equality and nonequality joins.
Cartesian Products:
When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. All rows in the first table are joined to all rows in the second table.
A Cartesian product tends to generate a large number of rows, and the result is rarely useful. You should always include a valid join condition in a WHERE clause, unless you have a specific need to combine all rows from all tables. Cartesian products are useful for some tests when you need to generate a large number of rows to simulate a reasonable amount of data.
A Cartesian product is generated if a join condition is omitted. The example to display employee name and department name, we write the statements like:
> select ename, dname from emp, dept;
JOIN: 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 common values existing in corresponding columns, that is, usually primary and foreign key columns. Syntax:
……. Where table1.column=table2.column;
Remember
• 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 a name. (………where emp.deptno=dept.deptno; )
• To join n tables together, we need a minimum of n-1 join conditions.
Equi-Join:
To determine an employee’s department name, we compare the value in the deptno column in the emp tablw with the deptno values in the dept table. The relationship between the emp and dept tables is an equi-join, ie., values in the deptno column on both tables must be equal. Frequently , this type of join involves primary anf foreign key complements.
Note: Equijoins are also called simple joins or inner joins.
select emp.empno, emp.ename, emp.deptno, dept.deptno, dept. dname, dept.loc from emp, dept
where emp.deptno=dept.deptno;
Here; the SELECT clause specifies the column names to retrieve, the FROM clause specifies the two tables that the database must access and the WHERE clause specifies how the table are to be joined.
You need to qualify the names of the column in the WHERE clause with the table name to avoid ambiguity. Without the table prefixes, the deptno column could be from either the dept table or the emp table. It is necessary to add the table prefix to execute you query.
If there are not common column names between the two tables, there is no need to qualify the column. However, using the table prefix improves performance, because you tell the Oracle Server exactly where to find the columns.
Qualifying column names with table names can be very time consuming, particularly if table names are lengthy.
select empno,ename,dname,dloc from emp e, dept d where e.deptno=d.deptno
select empno,ename,dname,dloc from emp e, dept d where e.deptno=d.deptno
and lastname=’Smith’;
Non-Equi Join:
An non-equi join is a join condition containing something other than an equality operator. The relationship between the emp table and the salgrade table has an example of a non-equijoin. A relationship between the two tables is that the salary column in the emp table must be between the value in the lowsal and hisal columns of the salgrade table. The relationship is obtained using an operator other than equals(=).
select e.ename,e.salary,s.grade from emp e,salgrade s
where e.salary between s.losal and s.hisal;
Here the example creates a non-equijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges.
Outer Join
The missing rows can be returned if an outer join operator is used in the join condition. The operator is a plus sign enclosed in parentheses(+), and it is placed on the “side” of the join that is deficient in information. This operator has the effect of creating one or more null rows, to which one or more rows from the non-deficient table can be joined. Syntax:
SELECT column1,column2,column3,..
FROM table1,table2,..
WHERE table1.column(+)=table2.column; or WHERE table1.column=table2.column(+);
Here; table1.column(+), is the outer join symbol, which can be placed on either side of the WHERE clause condition, but not on both sides.
SELECT e.ename,e.deptno,d.deptno FROM emp e, dept d WHERE e.deptno(+)=d.deptno;
The deficient value form employee table will be displayed. Ie. There will be null value at emp table under which department no there is no employee.
Here; The outer join operator can appear on only one side of the expression- the side that has iformatio0n missing. It returns those rows from one table that have no direct match in the other table.
Self Join (Joining a table to Itself or Reflexive Join)
Sometimes we need to join a table to itself. To find the name of each employee’s manager, we need to join the EMP table to itself, or perform a self join. For example, to find the name of SMITH, we need;
- find SMITH in the EMP table by looking at the ename columns.
- Find the manager number for SMITH by looking at the MGR column.
- And find the name of the manager with empno by looking at the empno column. TO DO SO…
SELECT E.emono,E.ename,M.empno,M.ename FROM emp E, emp M WHERE E.mgr=M.empno;
Now again; (check output)
Select worker.ename ||’ works for ‘||manager.ename from emp worker,emp manager
where worker.mgr=manager.empno;
Here; The example joins the emp table to itself. To simulate two tables in the FROM clause, there are two aliases, namely worker and manager, for the same table emp. In this example, the WHERE clause contains the join that means “where a worker’s manager number matches the employee number for the manager”.
Practise
1. Create a query that displays employee name, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label.
2. Create a query that displays the name,job, department name, salary and grade for all employees.
3. Create a query to display the name and hiredate of any employee hired after employee SMITH.
Aggregate Function (Group Function)
Group functions operate on sets of rows to give one result per group. Types of Group functions.
Syntax: groupfunction([DISTINCT|ALL]exp)
AVG(n)): average value of n, ignoring null values
COUNT(): number of rows, *-including duplicate records count, DISTINCT count non duplicate records
MAX(): maximum value of expression, ignoring null values
MIN(): minimum value of expression, ignoring null values
SUM(): sum values of n, ignoring null values.
DISTINCT makes the function consider only non-duplicate values, ie. To suppress the counting of any duplicate values within a column. ALL makes it consider even value including duplicates. The default is ALL and therefore does not need to be specified. Here all group functions ignore null values.
select avg(comm.) from emp; this will not give right answer
select avg(nvl(comm.,0)) from emp; this will the right answer
All group functions ignore null values in the column. In the example above, the average is calculated based only on the rows in the table where a valid value is stored in the comm. column.
All group functions have treated the table as one large group of information. At times we need to divide the table of information into smaller groups. This can be done by using the GROUP BY clause.
We can use the GROUP BY clause to divide the rows in a table into groups. We can then use the group functions to return summary information for each group. The syntax is
select statement group by ;
Remember:
- if we include a group function in a SELECT clause, we cannot select individual results as well, unless the individual column appears in the GROUP BY clause.
- Using a where clause, we can exclude rows before dividing them into groups.
- We cannot use a column alias in the GROUP BY clause.
- When using the GROUP BY clause, make sure that all columns in the SELECT list that are not group functions are included in the GROUP BY clause.
select avg(sal) from emp group by deptno; display each department’s average salary
select deptno,avg(sal) from emp group by deptno; display deptno with their average salary.
Select deptno, job, sum(sal) from emp group by deptno,job; groups within group
Select deptno, count(ename) from emp; this statement is wrong
Select deptno, avg(sal) from emp where avg(sal) >5000 group by deptno; this is wrong
The WHERE clause cannot be used to restrict groups. The SELECT statement on the example results is an error because it uses the WHERE clause to restrict the display of average salaries of those deparements that have an average salary greater than 5000. here, we use the HAVING clause to specify which group are to be displayed, and thus, we further restrict the groups on the basis of aggregate information.
Select deptno, avg(sal) from emp having avg(sal)>5000 group by deptno;
Select deptno, max(sal) from emp group by deptno having max(sal)>5000;
Select job,sum(sal) from emp where job <> ‘MANAGER’ group by job having sum(sal)>5000 order by sum(sal); this is extra, check it out what is it for.
Subqueries (queries within queries)
A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. We can build powerful statements out of simple ones by using subqueries. We can place the subquery in a number of SQL clauses including: The WHERE clause, The GAVING clause, The FROM clause.
- Enclose subqueries in parentheses.
- Place subqueires on the right side of the comparison condition.
Types of subqueries
1. Single-Row subqueries: Queries that return only one row from the inner SELECT statement.
Use single-row comparison operators: = > >= < <= <>
2. Multiple-Row subqueries: Queries that return more than one row from the inner SELECT statement.
Suppose we want to write a query to find out who earns a salary greater than SMITH’s slary. To solve this problem we need two queries: one to find what SMITH earns, and a second query to find who earns more than that amount. We can solve this problem by combining the two queries, placing one query inside the other query. Here 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 perform two sequential queries and using the result of the first query as the search value in the second query.
select ename from emp where sal> (select sal from emp where ename=’SMITH’); display emp’s whose salary is greater than that of SMITH’s salary.
Select ename, job from emp where job=(select job from emp where empno=2324); display the employee names whose job is like that of empno 2324’s job.
Select ename,job,sal from emp where sal=(select Min(sal) from emp); shows emp name job with salary with sal is the minimum salary from all.
Return more than one row. Use multiple-row comparison operators.
IN- Equal to any member in the list.
ANY- Compare value to each value returned by the subquery.
ALL- Compare value to every value returned by the subquery.
Suqueries that return more than one row are called multiple row subqueries. We use a multiple-row operator, instead of a single row operator, with a multiple row subquery. The multiple row operator expects one or more values.
Find the employees who earn the same salary as the minimum salary for each department.
Select ename,sal, deptno from emp where sal in(select min(sal) from emp group by deptno);
Here ANY operator compares a value to each value returned by subquery. The example displays employees who are not MANAGER and whose slary is less than that of any MANAGER.
Select empno, ename,job, sal from emp where sal < any (select sal from emp
where job=’MANAGER’)
and job<>’MANAGER’;
The ALL operator compares a value to every value returned by a subquery. The example displays employees whose salary is less than the salary of all employees with a job ie MANAGER and whose job is not MANAGER.
Select empno, ename,job, sal from emp where sal < all (select sal from emp
where job=’MANAGER’)
and job<>’MANAGER’;
Data Manipulation Language (DML)
- all new rows to a table INSERT
- modify existing rows in a table UPDATE
- remove existing rows from a table DELETE
DML is a core pare of SQL, when we want to add, update or delete data in the database, we execute a DML statement. A collection of DML statements that form a logical unit of work is called a transaction.
Consider a banking database. When a bank customer transfers money from a saving account to a checking account, the transaction might consist of three separate operation; decrease the savings account, increase the checking account, and record the transaction in the transaction journal. The Oracle server must guarantee that all three SQL statements are perform to maintain the accounts proper balance. When something prevents one of the statements in the transaction from executing other statements of the transaction must be undone.
Add new rows to a table by using the INSERT statement. Syntax: only one row at a time.
INSERT INTO table ([column1, column2,column3,…]) VALUES (value1,value2, value3,….);
Eg.
While entering values enclose character and date values within single quotation marks. List values in the default order of the columns in the table.
insert into dept (deptno, dname, loc) values(50, ‘Computer Dept’,’Pokhara’);
insert into dept (deptno,dname) values(60,’Extra Dept’); only two values are inserted here.
Insert into emp (emno,ename,sal,comm,hiredate,job,mgr,deptno)
values(1111, ‘Ramesh’, 2200,10,’12-JAN-91’,MANAGER,2222,30);
if column order is in sequence as per entered value; write like this
Insert into emp values(1111, ‘Ramesh’, 2200,10,’12-JAN-91’,MANAGER,2222,30);
If you want to enter date value of your own type style
Insert into emp
values(1111,‘Ramesh’,2200,10,to_date(’DEC-3-1991’,’MON-DD-YYYY’),MANAGER,22,30);
Coping value from another table and insert into other
Insert into employee select * from emp;
Insert into employee select * from emp where deptno=20;
Common errors that can occur during user input:
- Mandatory value missing for a NOT NULL column
- Duplicate value violates uniqueness constraint.
- foreign key constraint violated
- CHECK constraint violated
- Data type mismatch
- Value too wide to fit in column.
Update statement (to modify existing records into the table)
Syntax: update table set column=value, column=value,… where condition;
update emp set deptno=70 where empno=2222; update information for selected records
update employe set deptno=20; update information for all employee
Extras
update employee set job=(select job from employee where empno=2222);
update employee set job=(select job from employee where empno=2222) where empno=3333;
Removing a Row or Delete statement (to remove existing rows from a table )
Syntax: delete [from] table where [condition];
delete from dept where deptno=20;
delete from dept where dname=’FINANCE’;
delete from emp; to delete all records
delete from emp where sal>5000 and deptno=20;
we cannot delete a row that contains a primary key that is used as a foreign key I another table.
Delete from department where deptno=20;
Data Control Language (DCL)
A transaction begins when the first DML statement is encountered and ends when one of the following occurs:
- A COMMIT or ROLLBACK statement is issued.
- A DDL statement, such as CREATE, is issued.
- A DCL statement is issued.
- The user exits from SQL
Statement Description
COMMIT Ends the current transaction by making all pending data changes permanent.
SAVEPOINT name Marks a savepoint within the current transaction
ROLLBACK ROLLBACK ends the current transaction by discarding all pending data changes.
ROLLBACK TO SAVEPAOINT name ROLLBACK TO SAVEPOINT rolls back the current transaction to the specified savepoint, there by discarding any changes and or savepoints created after the savepoint to which we are rolling back.
Creating a New Table
Data Definition Language (DDL)
Database objects are TABLE, VIEW, SEQUENCE, INDEX, SYNONYM.
An Oracle database can contain multiple data structures. Each structure should be outlined in the database design so that it can be created during the build stage of database development.
Table: stores data
View: subset of data from one or more tables.
Index: improves the performance of some queries
In Database; table can be created at any time, even while users are using the database. We need to name database tables and columns according to the standard rules for naming any Oracle database object.
- table and column names must being with a letter and be 1 to 30 characters long.
- Names must contain only the characters A-Z, a-z, 0-9, _ (underscores) $ and # are legal character.
- Names must not duplicate
Create table to store data by executing the SQL CREATE TABLE statement. This statement is one of the data definition language (DDL) statements, which are used to create, modify, or remove database structure.
The Syntax:
CREATE TABLE tablename
( column1 datatype,
column2 datatype,
…….
……..
);
Eg.
CREATE TABLE dept
( deptno number(2),
dname varchar2(14),
loc varchar2(12));
here the example creates the dept table, with three attributes/columns deptno, dname and loc. It further confirms the creation of the table by issuing the DESCRIBE command.
Ie. > desc dept; or
> describe dept;
Oracle Datatypes: varchar2(size), char(size), number(p,s), date, long, clob, row , bfile, rowid...
create table newtable as select * from emp;
create table newtable1 as select * from emp where deptno=20;
alter table tablename add (column datatype(size) )
alter table modify (column datatype(size))
alter table drop(column)
drop table employee;
rename dept to department;
truncate table tablename;
What is Views?
Ie. Logical subsets or combinations of data by creating views of tables. A view is a logical table based on a table or another view. A view contains no data of its won but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. View is for
- to restrict data access because the view can display selective column from the table.
- Views can be used to make simple queries to retrieve the result of complicated queries. For example, views can be used to query information from multiple tables without the user knowing how to write a join statement.
- to provide data independence ie. One view can be used to retrieve data from several tables.
- to present different views of the same data.
Advantages:
- views restrict access to the data because the view can display selective columns from the table.
- Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing how to write a join statements.
- Views provide groups of users access to data according to their particular criteria.
Syntax
CREATE OR REPLACE [FORCE|NOFORCE] VIEW view-name [alias1,alias2,…..]
AS SELECT statements
WITH CHECK OPTION
WITH READ ONLY;
CREATE OR REPLACE:- creates or re-creates the view if it already exists
FORCE:- creates the view regardless of whether or not the base tables exists
NOFORCE:- creates the vie only if the base tables exist ie. Default
view-name:- is the name of the view
alias:- specifies names for the expressions selected by the view’s query. The number of aliases must match the number of expressions selected by the view.
WITH CHECK OPTION:- specifies that only rows accessible to the view can be inserted or updated. Ie. To perform referential integrity checks through vies. Ie. Integrity level check
WITH READ ONLY:- ie. Ensures that no DML operations can be performed on this view.
Eg.
create or replace view myview20 as select empno,ename,sal,deptno from emp where deptno=20;
There are two classifications for views: simple and complex. The basic difference is related to DML (INSERT, UPDATE, and DELETE) operation.
A simple view is one that: - derives data from only one table. Contains no functions or group of data.
A complex view is one that:- derives data from many tables, contains functions or group of data.
Eg. To create a view, empview20, that contains details of employees in department 20.
> create view empview20 as select empno, ename, sal from emp where deptno=20;
> desc view20; to describe the structure o the view created.
> select * from tab; to check whether created or not.
> select * from empview20;
create or replace view empview30 (idno,name, salary, dno) as select empno, ename, sal, deptno from emp where deptno=30;
To modify the view created, we can create the same view name again with new structure. Now to create a complex view. Ie. Create a complex view that contains group functions to display values from two tabes.
create view deptnosum (name,minsal,maxsal, avgsal)
as select d.dname,min(e.sal),max(e.sal), avg(e.sal) from emp e, dept d
where e.deptno=d.deptno group by d.dname;
Modifying Views; With the OR REPLACE option, a view can be created even if one exists with this name already, thus replacing the old version of the view for its owner. That means that the view can be altered without dropping, re-creating, and regranting object privileges.
Set Operators
( to combine multiple queries into a single query)
UNION, INTERSECT and MINUS are useful in constructing queries that refer to different tables. They combine the results of two or more select statements into one result. A query many therefore consist of two or more SQL statements linked by one or more set operators. Set operators are often called vertical joins, because the join is not according to rows between two tables, but columns.
It is possible to construct queries with many set operators. If multiple set operators are used, the execution order for the SQL statements is from top to bottom.
The SET Operatos combine the results of two or more component queires into one result. Queries containing SET operators are called compound queries.
UNION INTERSECT MINUS
Operator Returns
UNION All distinct rows selected by either query
UNION ALL All rows selected by either query, including all duplicates
INTERSECT All distinct rows selected by both queries
MINUS All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement
All SET operators have equal precedence. If a SQL statement contains multiple SET operators, the Oracle server evaluates them from left (top) to right (bottom).
The UNION Operator
The UNION operator returns all rows selected by either query. Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows.
Remember:
- The number of columns and the data types of the columns being selected must be identical in all the SELECT statements used in the query. The names for the columns need not be same.
- UNION operates over all of the columns being selected.
- NULL values are not ignored during duplicate checking.
- By default the output is sorted in ascending order.
UNION ALL operator to return all rows from multiple queries. Unlike UNION, duplicate rows are not eliminated and the output is not sorted by default. The DISTINCT keyword can’t be used.
Eg.
To return all distinct rows retrieved by either of the queries, enter
SELECT JOB FROM EMP WHERE DEPTNO=10
UNION
SELECT JOB FROM EMP WHERE DEPTNO=30;
To return all rows (including duplicates) retrieved by either of the queries,
SELECT JOB FROM EMP WHERE DEPTNO=10
UNION ALL
SELECT JOB FROM EMP WHERE DEPTNO=30;
The INTERSECT Operator
This operator returns all rows common to multiple queries.
Remember:
- the number of columns and the data types of the columns being selected must be identical in all the SELECT statements used in the query. The names for the columns need not be same.
- Not ignore the NULL values.
Eg.
To return only rows retrieved by both of the queries, enter:
SELECT JOB FROM EMP WHERE DEPTNO=10
INTERSECT
SELECT JOB FROM EMP WHERE DEPTNO=30;
The MINUS operator
Use the MINUS operator to return rows returned by the first query that are not present in the second query ( the first SELECT statement MUNUS the second SELECT statement).
Remember:-
- The number of columns and the data types of the columns being selected by the SELECT statements in the queries must be identical in all the ELECT statements used in the query. The names of the columns need not be identical.
Eg.
To return all rows retrieved by first query that are not in the second, enter:
SELECT JOB FROM EMP WHERE DEPTNO=10
MINUS
SELECT JOB FROM EMP WHERE DEPTNO=30;
Order By
ORDER BY can only be used once in a query that employs set operators. I used, the ORDER BY clause must be placed at the end of the query. Also, because you may select different columns in each SELECT you cannot name the columns in the ORDER by clause. Instead, columns in the ORDER BY must be referred to by their relative positions in the SELECT list.
SELECT EMPNO,ENAME,SAL FROM EMP
UNION
SELECT ID,NAME,SALARY FROM EMP_HISTORY
ORDER BY 2;
Notice that on the ORDER BY clause a numeral (2) is used to represent the position of the ENAME column on the SELECT list. This means that rows will be sorted in ascending employee name order.
Rules when using Set Operators
1 SELECT statements must select the same number of columns
2 Corresponding columns must be of the same data type
3 Duplicate rows are automatically eliminated (DISTINCT can’t be used)
4 Column names from the first query appear in the result.
5 ORDER BY clause appears at the end of statements.
Subqueries
A subquery is a SELECT statement that is embedded in a clause of another SQL statement, called parent statement.
The subquery (inner query) returns a value that is used by the parent statement. Using a nested subquery is equivalent to performing two sequential queires and using the result of the inner query as the search value in the outer query (main query).
A sub query is a SELECT statement that is nested within another SELECT statement and which returns intermediate results.
Syntax:
SELLECT column1, column2,….
FROM table
WHERE column=(SELECT column FROM table WHERE condition);
The subquery is often referred to as a subselect or inner select; it generally executes first and its output is used to complete the query condition for the main or outer query. Using subqueries allows a developer to build powerful commands out of simple once. The nested subquery can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.
Single Row Subqueries
To find the employee who earns the minimum salary in the company, two steps must be taken. Ie
Find the minimum salary
select min(sal) from emp;
Find the employee who earns the minimum salary.
select ename,job, sal from emp where sal=(lowest salary which is unknown);
We can combine the two commands as a nested subquery:
Select ename,jon,sal from emp where sal=(select min(sal) from emp);
select ename,job,sal from emp where sal < (select avg(sal) from emp;
How are nested subqueries processed?
A SELECT statement can be considered as a query block. The example above consists of two query blocks- the main query and the inner query.
The inner select statement or query block is executed first, producing a query result: 800. The main query block is then processed and uses the value returned by the inner query to complete its search condition. In essence, the main query would finally look like this:
SELECT ENAME,SAL, DEPTNO FROM EMP WHERE SAL=800;
In the above example, the 800 is a single value. The subquery that returned the value 0f 800 is called a single row subquery. When a subquery returns only one row, a single row comparison or logical operator should be used. For example =,<,>,<=..etc.
To find all employee who have the same job as BLACK we would enter:
SELECT ENAME,JOB FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME=’BLAKE’);
The inner query returns BLAKE’s job title which is then used on the right hand side of the main query’s WHERE clause.
Subqueries that return more than one row?
The following query attempts to find the employees who earn the lowest salary in each department.
SELECT ENAME,SAL, DEPTNO FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);A
Here; notice that the inner query has a GROUP BY clause. This means that it may return more than one value. We therefore need to use a multi-row comparison operator. In this case, the IN operator MUST be used because it expects a list of values.
The result obtained does not show the department in which the qualifying employees work. In addition, because we are only comparing salary values, the inner query could return a value simply because it matches the lowest salary for one of the departments, not necessarily the employee’s own department. Therefore, the query should be rewritten in order to match the combination of employee’s salary and department number with the minimum salary and department number:
Errors Encountered
- When a subquery returns more than one row and a single row comparison operator is used, SQL *Plus issues the error message.
Select ename,sal,deptno from emp where sal=(select min(sal) from emp group by deptno);
- If the inner query returns no rows, you get the error.
Select ename,job from emp where job=(select job from emp where ename=’om’);
SOME/ANY or ALL Operators
The ANY or ALL operators may be used for subqueries that return more than one row. They are used on the WHERE or HAVING clause in conjunction with the logical operators (=,!=,<,>,>=,<=).
ANY (or its synonym SOME) compares a value to each value returned by a subquery.
To display employees who earn more than the lowest salary in Department 30.
SELECT ENAME,SAL,JOB,DEPTNO
FROM EMP
WHERE SAL>SOME(SELECT DISTINCT SAL FROM EMP WHERE DEPTNO=30);
The lowest salary in Department 30 is $950 (JAMES’s). the main query has returned employees who earn a salary that is greater than the lowest salary in department 30. So ‘>NY’ means more than the minimum. ‘=ANY’ is equivalent to IN.
When using SOME/ANY, the DISTINCT keyword is frequently used to prevent rows from being selected several times.
ALL compares a value to every value returned by a subquery.
The following query finds employees who earn more than every employee in Department 30.
SELECT ENAME,SAL,JOB,DEPTNO
FROM EMP
WHERE SAL>ALL(SELECT DISTINCT SAL FROM EP WHERE DEPTNO=30);
The highest salary in Department 30 is $2850 (BLAKE’s), so the query has returned those employees whose salary is higher than $2850. That is , greater than the highest salary for Department 30, and consequently more than every salary in that department.
The NOT operator can not be used within, ANY or ALL.
Index
(a table or other data structure used to determine the location of rows in a file that satisfy some condition)
Index is a database object. Its purpose is to increase the performance of data retrieval thereby provide faster access path to the table data. Indexes can be created on more than one column of a table. The syntax is given below.
create index on (column1, column2,….)
Eg. SQL> create index ind1 on price(prodid);
Note:-
- indexes are independent of data. They can be dropped or created whenever needed and their presence or absence will not affect the table.
- Columns of type long, long row cannot be index.
- An index may contain upto 16 columns.
Oracle(DB) indexes have two main purposes
1). To speed up the retrieval of rows via a particular key.
2). To enforce uniqueness on values in a column, usually primary key values.
The use of indexes is strongly recommended to achieve better performance, and generally one of the first indexes to be created on a table is the index on the primary key columns(s).
