Introduction to SQL
The language used to access data within Oracle databases
Developed in a prototype RDBMS, 'System R', by IBM in mid- 1970s
Oracle Corporation introduced the first commercially available implementation of SQL
ANSI adopted SQL as a standard language for RDBMS, in Oct. 1986
Key features of SQL are:
Non-procedural Language
Unified Language
Common Language for all relational databases
Oracle Data types
The information in a database is maintained in the form of tables
Each table consists of rows and columns to store the data
A particular column in a table must contain similar data, which is of a particular type
The different Data types available are:
CHAR To store character type of data
VARCHAR2 Similar to CHAR but can store variable number of characters
VARCHAR Synonymous with VARCHAR2 however likely to change in future versions of Oracle. It is suggested to use VARCHAR2.
NUMBER Stores fixed and floating point numbers
DATE Stores point-in-time values (i,e. date and time) in a table
LONG Can store up to 2 GB of characters. Similar to MEMO fields in FoxPro
RAW Used to store binary data such as graphics, sound etc.
LONGRAW Contains raw binary data otherwise the same as a LONG column. The values entered must be in hex notation.
CLOB Character Large Objects
BLOB Binary Large Objects
NCLOB National Language Support Character Large Object
BFILE Binary File
CHAR(n)
Fixed length character data of length n, Fixed for every row in the table (with trailing spaces); maximum size is 255 bytes per column/row.
VARCHAR2 (n)
Variable length character data. A maximum 'n' must be specified. Variable for each row, up to 2000 bytes per column/row.
NUMBER(p,s)
Variable length numeric data. Maximum precision (p) and/or scale (s) is 38. Variable for each row. The maximum space required for a given column is 21 bytes per row.
DATE
Fixed length date and time data, ranging from January 1, 4712 BC to December 31, 4712 AD. Default format: DD-MON-YY.
LONG
Variable length character data, Variable for each row in a table, up to 2000 bytes per row.
RAW(n)
Variable length raw binary data. A maximum "n" must be specified, Variable for each row in a table, up to 2000 bytes per row.
LOGIN
USER NAME : scott
password : tiger
host string:
Data definition
create table emp
(empno number not null,
ename varchar2(10),
b varchar2(9),
mgr number,
hiredate date,
sal number(7,2),
comm number (7,2),
deptno number not null);
desc emp; for seeing the structure of the table.
Rules for naming tables
Your table name should be unique Names may not duplicate oracle-reserved words. Column names must be unique with in a table.
Characters
First character must be A-Z , a - z
Subsequent character can be number or $ # @ . Commas are not allowed
You can have up to 30 characters.
Querying Database Tables
The SELECT statement instructs the database to retrieve
information from a table.
The syntax for SELECT statement is
SELECT
<column-list>
FROM <table-name>
[WHERE <condition>]
[GROUP BY <column-name(s)>]
[HAVING <condition>]
[ORDER BY <expression>];
Example
SELECT ename FROM emp; The above command will display all the employee name from the table emp.
SELECT * FROM EMP; all cols of emp;
SELECT EMPNO,ENAME, JOB FROM EMP; Only the cols mentioned will be selected.
Conditional Retrieval of Rows
The WHERE clause is used along with the SELECT statement to specify the condition, based on which the rows will be extracted from a table with SELECT
Comparison operators: =, <, <=, >, >=
<>, != these two are not equal to
logical operators: AND, OR, NOT
Special operators:
IN Checking a value in a set
BETWEEN Checking a value within a range
LIKE Matching a Pattern from a column
SELECT * FROM emp WHERE deptno = 20; List the name and salary of the employees whose salary is more than 1000:
SELECT ename, sal FROM emp WHERE sal > 1000; List the employee number and name of managers:
SELECT empno, ename FROM emp WHERE job = 'MANAGER'; List the names of the clerks working in the department 20:
SELECT ename FROM emp WHERE job = 'CLERK' AND deptno = 20; List the names of analysts and salesmen:
SELECT ename FROM emp WHERE job = 'SALESMAN' OR job = 'ANALYST'; List the details of employees who joined before end of September' 81:
SELECT * FROM emp WHERE hiredate <= TO_DATE('30-SEP-1981',’DD-MON-YYYY’); List the names of employees who are not managers:
SELECT ename FROM emp WHERE job <> 'MANAGER'; List name of employees whose employee no are 7369, 7521, 7839 7934, 7788:
SELECT ename FROM emp WHERE empno = 7369 OR empno = 7521 OR empno = 7839 OR empno = 7934 OR empno = 7788;
The above query can be written using the 'IN' operator:
SELECT ename FROM emp WHERE empno IN (7369,7521,7839,7934,7788); List the employee details not belonging to the department 10, 30 and 40:
SELECT * FROM emp WHERE deptno NOT IN (10,30,40); List the employee name and salary, whose salary is between 1000 and 2000:
SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 2000; List employee names, who joined before 30th June '81 & after December '81:
Working with NULL values
NULL values are not 0 or a blank.
It represents an unknown or inapplicable value
It cannot be compared using the relational and/or logical operators
The special operator 'IS' is used with the keyword 'NULL' to locate NULL values
Examples:
SELECT ename FROM emp WHERE comm IS NULL; List the employee names, who are not eligible for commission:
SELECT ename, job FROM emp WHERE mgr IS NULL; List name of employee & designation of employee, who don't report to anybody (mangers is NULL)
SELECT ename FROM emp WHERE deptno IS NULL; List the employees not assigned to any department:
SELECT ename FROM emp WHERE comm IS NOT NULL; List the employees who are eligible for commission:
SELECT * FROM emp WHERE sal > 2000 AND comm IS NULL; List the details of employees, whose Sal is greater than 2000 & commission is NULL:
The LIKE operator is used only with CHAR and VARCHAR2 to match a pattern
'%' represents a sequence of zero or more characters
'_' (underscore) stands for any single character
Both '%' and '_' are used with the LIKE operator to specify a pattern
Examples:
SELECT ename FROM emp WHERE ename LIKE 'S%'; List the employees whose names start with an "S" (not "s"):
The output will be:
ENAME
-------
SMITH
SCOTT
SELECT ename FROM emp WHERE ename LIKE '%S'; List the employee names ending with an 'S':
SELECT ename FROM emp WHERE ename LIKE '_____'; List the names of employees whose names have exactly 5 characters:
SELECT ename FROM emp WHERE ename LIKE '_I%'; List the employee names having 'I' as the second character:
Using expressions with columns
Arithmetic computations can be done on numeric columns
Alias names can be given to columns and/or expressions on query outputs
Alias names are displayed in place of column names
Alias names are given to the right of a column name, enclosed within quotes
Examples:
SELECT ename FROM emp WHERE (SYSDATE - hiredate) > (2 * 365); List the names of employees, who are more than 2 years old in the organization:
SELECT ename, sal, sal *.1 FROM emp; List name, salary & PF amt of all employees (PF is calculated as 10% of salary):
SELECT ename, sal, sal * .1 "PF" FROM emp; We can provide an alias to the column 'sal *.1' as "PF"
Ordering the Results of a query
SQL uses the ORDER BY clause to impose an order on the result of a query.
ORDER BY clause is used with SELECT statement
The syntax is
SELECT[DISTINCT] <column list> | <expr>
FROM <table>[,<table>] [WHERE condition]
[ORDER BY <columns>] [ASC|DESC]
One or more columns and/or expressions can be specified in
ORDER BY clause.
Examples:
SELECT empno, ename, sal FROM emp ORDER BY sal; List the empno, ename, sal in ascending order of salary:
SELECT ename, hiredate AS date_of_joining FROM emp ORDER BY date_of_joining DESC; List the employee name & hire date in desc order of hire date:
SELECT DEPTNO, JOB, ENAME, SAL FROM EMP ORDER BY DEPTNO, SAL DESC; List the ename, sal, Job & Dept no desc order of Dept No & sal:
SELECT ename, sal, sal *.1 "PF", sal *.5 "HRA", sal *.3 "DA",sal + (sal *.5) + (sal *.3) - (sal *.1) "GROSS" FROM emp ORDER BY 6;
List the employee name, salary, PF, HRA, DA and gross; order the result in
ascending order of gross. HRA is 50% of salary and DA is 30 % of salary.
Aggregate Functions
The aggregate functions produce a single value for an entire group or table
COUNT determine the number of rows or non NULL column values
SUM determines the sum of all selected columns
MAX determines the largest of all selected values of a column
MIN determines the smallest of all selected values of a column
AVG determines the average of all selected values of a column
In all the above functions, NULLs are ignored
COUNT
The syntax is
COUNT (*|[Distinct]|ALL|column name)
SELECT COUNT(*) FROM emp; List the number of employees working with the company:
SELECT COUNT(DISTINCT job) FROM emp; List the number of jobs available in the emp table:
SUM
The syntax is
SUM([DISTINCT |ALL]column name)
SELECT SUM(sal) FROM emp; List the total salaries payable to employees:
MAX
The syntax is:
MAX (column name)
SELECT MAX(sal) FROM emp WHERE job = 'SALESMAN'; List the maximum salary of employee working as a salesman:
MIN
The syntax is
MIN(Column name)
SELECT MIN(sal) FROM emp; List the minimum salary from emp table
AVG
The syntax is:
AVG ([DISTINCT | ALL]Column name)
SELECT AVG(sal), COUNT(*) FROM emp WHERE deptno = 20; List the average salary and number of employees working in the department 20:
Grouping the Result of a query
The GROUP BY clause is used to divide the rows in a table into smaller groups
The GROUP BY clause is used with SELECT clause
SQL groups the result after it retrieves the rows from a table
Conditional retrieval of rows from a grouped result is possible with the HAVING clause
The syntax for GROUP BY clause is
SELECT[DISTINCT] <column list> | <expr>
FROM <table>[,<table>] [WHERE condition]
GROUP BY <col | expr>
[HAVING <cond>]
ORDER BY clause can be used to order the final result
Examples
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno; List the department numbers & number of employees in each department:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno; List the department number & total salary payable in each department:
SELECT job, COUNT(*) FROM emp GROUP BY job ORDER BY 2 DESC; List jobs & no of emp in each job. The result should be in desc order of no of emp's:
SELECT job, AVG(sal) FROM emp WHERE job !='MANAGER' GROUP BY job; List the average salary fro each job excluding managers:
SELECT job, SUM(sal), AVG(sal), MAX(sal), MIN(sal) FROM emp WHERE deptno = 20 GROUP BY job;
List the total salary, maximum and minimum
salary and the average salary of
employees job wise, for department number 20 only:
Groups within groups
SELECT deptno, job, AVG(sal) FROM emp GROUP BY deptno, job; List the average monthly salary for each job type within department.
The HAVING Clause
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING COUNT (*) > 5 List average salary for all departments employing more than five people.
SELECT job, SUM(sal), AVG(sal), MAX(sal), MIN(sal) FROM emp
WHERE deptno = 20 GROUP BY job HAVING AVG(sal) > 1000; salary greater than 1000:
SELECT job, SUM(sal), AVG(sal), MAX(sal), MIN(sal) FROM emp
WHERE deptno = 20 GROUP BY job HAVING AVG(sal) > 1000
ORDER BY SUM(sal) ; Adding an order by
Order of Execution
It chooses rows based on the where clause
It groups those rows together based on the group by.
It calculates the results of the group functions for each group.
It chooses and eliminates groups based on the having clause
It orders the groups based on the results of the group functions in the order by. The order by must use either a group function or a column
in the group by clause.
Difference between Group By, Having Clause.
Group by clause groups the rows into smaller groups according to the given query and then shows the output in a particular sequence.
Having Clause is used for conditional retrieval of rows from a grouped result.
Difference between Order By, Where Clause.
Order by clause imposes an order on the result of a query.
Where Clause with Order by is used for conditional retrieval of individual rows.
Collating Information
· Joins are used to combine columns from different tables
· The connection between tables is established through the WHERE clause
· Types of Joins: Equi Joins, Cartesian Joins, Outer Joins, Self Joins
The syntax for the select statement where we join two tables:
FROM <table1>, <table2>, ......, <tableN>
WHERE <table1.column1> = <table2.column2> and
<table2.column3> = <tableN.columnN>
additional -conditions
The variables are defined as follows:
· <select-list> is the set of columns and expressions from <table1> through <tableN>.
· <table1> through <tableN> are the tables from which column values are retrieved.
· <Column1> through <columnN> are the columns in <table1> through <tableN> that are related.
· Additional conditions are optional query criteria.
The structure of the INCR table is:
EMPNO NUMBER(4)
AMT NUMBER(7,2)
DATEINCR DATE
Equi Joins
SELECT empno, ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno;
List the employee numbers, names, department numbers and the department name
Using Table Aliases
SELECT e.empno, e.ename, e.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
Consider the following tables and the data present:
Tab1 : Holds the principal amount.
Tab2 : Holds year and rate of interest.
SELECT PRINCIPAL, YEAR, RATE, PRINCIPAL *POWER(1+ (RATE/100),YEAR) FROM TAB1, TAB2;
Outer Joins
SELECT empno, ename, emp.deptno, dname, loc FROM emp, dept
WHERE emp.deptno ( + ) = dept.deptno;
Rules to place (+) operator
The outer join symbol ( + ) can not be on both the sides.
· · We can not "outer join" the same table to more than one other table in a single SELECT statement.
· · A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator.
Self Join
SELECT WORKER.ename, MANAGER.ename “Manager”
FROM emp WORKER, emp MANAGER WHERE WORKER.mgr = MANAGER.empno;
* List all employees who joined the company before their manager.
SELECT e.ename, e.hiredate, m.ename manager, m.hiredate
FROM emp e, emp m WHERE e.mgr = m.empno
and e.hiredate < m.hiredate
SET Operators
The types of SET operators in ORACLE are :
UNION Rows of first query plus rows of second query, less duplicate rows
INTERSECT Common rows from all the queries
MINUS Rows unique to the first query
UNIONThe syntax of UNION operator is
select <stmt1>
UNION
select <stmt2>
[order-by-clause]
The variables are defined as follows:
select stmt1 and select stmt2 are valid SELECT statements.
order-by-clause is optional and it references the columns by number rather than by name.
Examples
SELECT job FROM emp WHERE deptno = 20
UNION
SELECT job FROM emp WHERE deptno = 30; Display the different designations in department 20 and 30:
The output will be:
JOB
---------
CLERK
SALESMAN
MANAGER
ANALYST
Points to be kept in mind while using UNION operator
· The number of columns retrieved by first select must be equal to number of columns retrieved by second select.
· · The data types of columns retrieved by the select statements should be same.
The optional order by clause differs from the usual ORDER BY clause in a SELECT statement because the columns used for ordering must be referenced by a number rather than name. The reason that the columns must be referenced by number is the SQL does not require that the column name retrieved by first select be identical to the columns names retrieved by second select.
Example
select empno, ename from emp where deptno=10
UNION
select empno, ename from emp where deptno=30 order by 1;
INTERSECT
The syntax is :
select stmt1
INTERSECT
select stmt2
[order-by-clause]
SELECT
job FROM emp WHERE deptno = 20
INTERSECT
SELECT job FROM emp WHERE deptno = 30; List the jobs common to department 20 and 30:
The syntax is :
select stmt1
MINUS
select stmt2
[order-by-clause]
Example
SELECT job FROM emp WHERE deptno = 20
MINUS
SELECT job FROM emp WHERE deptno = 10
MINUS
SELECT job FROM emp WHERE deptno = 30; List the jobs unique to department 20:
Nested Queries· · The result of inner query is dynamically substituted in the condition of outer query
· · There is no practical limitation to the level of nesting of queries in Oracle 8i
· · When using relational operators, ensure that the sub query returns a single column output
· · In some cases, the DISTINCT clause can be used to ensure single valued output
SELECT deptno FROM emp WHERE ename = 'MILLER'; List the employees belonging to the department of MILLER:
Combining the above two queries:
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'MILLER');
SELECT ename FROM emp WHERE sal = (SELECT MAX (sal) FROM emp); List the names of the employee drawing the highest salary
Using Aggregate Functions In Subqueries
SELECT * from emp where sal > (select avg(sal) from emp
where hiredate< to_date('01-APR-1981','DD-MON-YYYY'));
Sub queries
in Having
* List the employee number, name, total number of increments and total increment
amount for the employee who has got maximum number of increments:
SELECT incr.empno, ename, COUNT(*), SUM (amt) FROM emp, incr
WHERE incr.empno = emp.empno
GROUP BY incr.empno, ename
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) from incr
GROUP BY empno);
* List the job with highest average salary.
SELECT job, AVG(sal) FROM emp GROUP BY job
HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp GROUP BY job);
Distinct Clause with Subqueries
Select * from dept
where deptno=(Select distinct deptno from emp where mgr='7698');
Subqueries that return more than one row
* List the names of the employees, who have got an increment:
SELECT ename FROM emp WHERE empno IN (SELECT empno FROM incr);
Here, the inner query returns multiple values, hence, the IN operator is used instead of a relational operator.
* List the names of the employees, who earn lowest salary in each department:
SELECT ename, sal, deptno FROM emp
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);
Here the inner query has a GROUP BY clause. This means it may return more than one value. In this case, the IN operator must be used because it expects a list of values.
The following points should be kept in mind while writting subqueries
· · The inner query must be enclosed in parentheses.
· · The inner query must be on the right hand side of the condition.
· · The subquery may not have an order by clause.
· · The ORDER BY clause appears at the end of the main select statement.
· · Subqueries are always executed from the most deeply nested to the least deeply nested, unless they are correlated subqueries.
Correlated Subquery
* List the employee numbers and names, who have got more than 1 increments:
SELECT empno, ename FROM emp WHERE 1 < ( SELECT COUNT (*) FROM
incr WHERE empno = emp.empno );
* List employee details who earn salary greater than the average salary for their
department.
SELECT empno, ename, sal, deptno FROM emp e
WHERE sal > (Select AVG(sal) FROM emp WHERE deptno=e.deptno)
Using Special Operators in Subqueries
Some Special operators used in subqueries are:
· · EXISTS
· · ANY
· · SOME
· · ALL Operators
EXISTS
· · This operator is used to check for the existence of values
· · This operator produces a Boolean result
· · It takes a subquery as an argument and evaluates it to True, if it produces any output or False, if it does not
ANY, SOME and ALL
· · Used along with the relational operators
· · Similar to IN operator, but only used in subqueries
· · The SOME and ANY operator can be used interchangeably EXISTS operator
* List all employees who have atleast one person reporting to them.
SELECT empno, ename, job, deptno FROM emp e
WHERE EXISTS (SELECT empno from emp WHERE emp.mgr = e.empno)
ORDER BY empno;
* List the employee details if and only if more than 10 employees are present in department number 10:
SELECT * FROM emp
WHERE DEPTNO = 10 AND EXISTS (SELECT COUNT(*) FROM emp
WHERE deptno = 10 GROUP BY deptno HAVING COUNT(*) > 10);
* List the names of employees from the employee table where the increment amount is greater than 1000 and the number of employees receiving the same increment is greater than 5:
SELECT ename FROM emp WHERE empno IN (SELECT empno FROM incr
WHERE amt > 1000 AND EXISTS (SELECT COUNT(*)
FROM incr GROUP BY amt HAVING count (*) > 5));
* List all the employees details who do not manage any one.
SELECT ename, job from emp e
where not exists (select mgr frm emp where mgr=e.empno)
ANY operator
* List the employee names whose salary is greater than the lowest salary of an employee belonging to department number 20:
SELECT ename FROM emp
WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 20);
* List the employee details of those employees whose salary is greater than any of the managers:
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > ANY (
SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
ALL Operator
* List the employee names whose salary is greater than the highest salary of all employee belonging to department number 20:
SELECT ename FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 20);
The inner query returns salary of all employees who belong to department number 20. The outer query selects employee name of that employee whose salary is greater than all the employees' salary who belong to department number 20.
* List the details of the employee earning more than the highest paid MANAGER:
SELECT empno, ename, sal FROM emp WHERE sal >
ALL (SELECT sal FROM emp WHERE job = 'MANAGER');
Advantages of Functions
· Functions can be used to perform complex calculations on data
· Functions can modify individual data items
· Functions can very easily manipulate output for group of rows.
· Functions can alter date formats for display
Types of Functions
· Single Row Function
· Group Function
Single Row Functions
The single row functions supported by Oracle can be classified into:
· Arithmetic Functions
· Character Functions
· Date Functions
· General Functions
Arithmetic Functions
ABS(n)
CEIL(n)
FLOOR(n)
MOD(m, n)
POWER(m, n)
SIGN(n)
SQRT(n)
TRUNC(m, [n])
ROUND(m, [n])
EXP(n)
ABS(n)
select ABS(-65) from dual;
ABS(-65)
--------
65
CEIL (n)
SELECT CEIL(SAL), CEIL(88.9) FROM emp WHERE sal BETWEEN 3000 AND 5000;
CEIL(SAL) CEIL(88.9)
-------- ----------
3000 89
5000 89
3000 89
FLOOR(n)
SELECT FLOOR(SAL), CEIL(88.9) FROM emp WHERE sal BETWEEN 3000 AND 5000;
FLOOR(SA CEIL(88.9)
-------- ----------
3000 88
5000 88
3000 88
MOD(m, n)
SELECT MOD(200,30) FROM DUAL;
MOD(200,30)
-----------
20
POWER(m, n)
SELECT sal, POWER(sal,2) FROM emp where deptno=10;
sal POWER(sal,2)
--------- - -----------
2450.00 6002500
5000.00 25000000
1300.00 1690000
SIGN(n)
SELECT comm-sal, SIGN(comm-sal) FROM emp WHERE deptno=30;
comm-sal SIGN(comm-sal)
-------- --------------
150 1
-1300 -1
SQRT(n)
SELECT sal, SQRT(sal)FROM emp where deptno=10;
SAL SQRT(SAL)
--------- ---------
6000 77.459667
2450 49.497475
1300 36.055513
TRUNC(m, [n])
SELECT TRUNC(90.723, 1), TRUNC(90.723, -1), TRUNC(90.723) FROM dual;
TRUNC(90.723,1) TRUNC(90.723,-1) TRUNC(90.723)
--------------- ---------------- -------------
90.7 90 90
SELECT sal, TRUNC(SQRT(sal),2)FROM emp where deptno=10;
SAL TRUNC(SQRT(SAL),2)
--------- ------------------
6000 77.45
2450 49.49
1300 36.05
ROUND(m, [n])
SELECT ROUND(90.723, 1), ROUND(90.723, -1), ROUND(90.723) FROM dual;
ROUND(90.723,1) ROUND(90.723,-1) ROUND(90.723)
--------------- ---------------- -------------
90.7 90 91
SELECT sal, ROUND(SQRT(sal),2)FROM emp where deptno=10;
SAL ROUND(SQRT(SAL),2)
--------- ------------------
6000 77.46
2450 49.5
1300 36.06
EXP(n)
SELECT EXP(4)FROM DUAL;
EXP(4)
---------
54.59815
Character Functions
CHR(x)
CONCAT(string1, string2)
INITCAP(string)
LOWER(string)
UPPER(string)
LPAD(char1, n [, char2])
RPAD(char1, n [, char2])
LTRIM(string,'char/s')
RTRIM(string,'char/s')
REPLACE(String, search_str [,replace_str])
SUBSTR(string, m [, n])
TRANSLATE(string, from_str, to_str)
Character Functions Returning Numeric Values
ASCII(string)
INSTR(string, char)
LENGTH(string)
CHR(x)
SELECT CHR (37) a, CHR (100) b, CHR (101) c FROM dual;
A B C
- - -
% d e
CONCAT(string1, string2)
SELECT CONCAT('Alphabet', 'Soup') "Dinner" From dual;
Dinner
------------
AlphabetSoup
INITCAP(string)
SELECT INITCAP(DNAME)FROM DEPT;
INITCAP(DNAME)
--------------
Accounting
Research
Sales
Operations
LOWER(string)
SELECT LOWER(DNAME), LOWER(‘XYZ’)FROM DEPT;
LOWER(DNAME) LOWE
------------ ----
accounting xyz
research xyz
sales xyz
operations xyz
UPPER(string)
SELECT UPPER(DNAME), UPPER(‘abc’) FROM DEPT;
UPPER(DNAME) UPP
-------------- ---
ACCOUNTING ABC
RESEARCH ABC
SALES ABC
OPERATIONS ABC
LPAD(char1, n [, char2])
SELECT LPAD(dname,15,’$’), LPAD(dname,15,' ') FROM dept;
RPAD(char1, n [, char2])
SELECT RPAD(dname,15,’$’), RPAD(dname,15,' ')FROM dept;
LTRIM(string,'char/s')
Select dname, LTRIM(dname), LTRIM(dname,'R') from dept;
RTRIM(string,'char/s')
Select dname, RTRIM(dname), RTRIM(dname,'S') from dept;
REPLACE(string, search_str[,replace_str])
SELECT REPLACE ('This and That', 'Th', 'B') "First" FROM dual;
First
-----------
Bis and Bat
SELECT REPLACE ('This and That', 'Th') "Second" FROM dual;
Second
---------------
is and at
SELECT REPLACE ('This and That', NULL) "Third" FROM dual;
Third
-------------
This and That
SUBSTR(string, m [, n])
Select dname, SUBSTR(dname,2,4), SUBSTR(dname,4) from dept;
TRANSLATE(string, from_str, to_str)
SELECT TRANSLATE ('abcdefghij', 'abcdef', '123456') FROM dual;
TRANSLATE(
----------
123456ghij
SELECT TRANSLATE ('abcdefghij', 'abcedfghij','123456') FROM dual;
TRANSL
------
123546
Character Functions Returning Numeric Values
ASCII(string)
SELECT ASCII (' ') FROM dual;
ASCII(' ')
----------
32
SELECT ASCII ('a') FROM dual;
ASCII ('A')
-----------
97
INSTR(string, char)
Select DNAME, INSTR(DNAME,’e’) from dept;
LENGTH(string)
Select dname, LENGTH(dname) from dept;
Combination of character functions in single SQL statements
SELECT UPPER(ename), LOWER(ename), INITCAP(ename), LENGTH(ename)
FROM emp;
UPPER(ENAME) LOWER(ENAME) INITCAP(ENAME) LENGTH(ENAME)
SMITH smith Smith 5
ALLEN allen Allen 5
SELECT ename, INSTR(ename,'A'), SUBSTR(job, 1, 3),
LPAD(ename, 10, '.'), RPAD(ename, 10, '.') FROM emp;
ENAME INSTR(ENAME,'A') SUB LPAD(ENAME RPAD(ENAME
KING 0 PRE KING KING
BLAKE 3 MAN BLAKE BLAKE
Date Functions
SYSDATE
ADD_MONTHS(d, n)
ROUND(d [,format])
TRUNC(d [,format])
MONTHS_BETWEEN(d1, d2)
LAST_DAY(d)
NEXT_DAY(date, day)
TO_CHAR(d, f)
TO_DATE(char, 'f')
SYSDATE
SELECT SYSDATE FROM SYS.DUAL;
ADD_MONTHS(d, n)
SELECT HIREDATE, ADD_MONTHS(HIREDATE,4), ADD_MONTHS(HIREDATE,-4)
FROM EMP WHERE DEPTNO=10;
ROUND(d [,format])
SELECT ROUND (TO_DATE ('12-APR-71'), 'MM') "Nearest Month" FROM dual;
Nearest M
---------
01-APR-71
TRUNC(d [,format])
SELECT TRUNC (TO_DATE ('12-APR-71 13:21:00','DD-MON-YY HH24:MI:SS'),
'Year') "First Day" FROM dual;
First Day
---------
01-JAN-71
MONTHS_BETWEEN(d1, d2)
SELECT MONTHS_BETWEEN('05-Jan-98','05-Mar-98'),
MONTHS_BETWEEN('05-Mar-98','05-Jan-98') From dual;
MONTHS_BETWEEN('05-Jan-98','05-Mar-98') MONTHS_BETWEEN('05-Mar-98','05- Jan-98')
-------------------------------------- ---------------------------------------
-2 2
LAST_DAY(d)
SELECT SYSDATE, LAST_DAY(SYSDATE) From dual;
SYSDATE LAST_DAY(
--------- ---------
07-MAY-98 31-MAY-98
NEXT_DAY(date, day)
SELECT SYSDATE, NEXT_DAY(SYSDATE,'WEDNESDAY') From dual;
SYSDATE NEXT_DAY(
--------- ---------
07-MAY-98 13-MAY-98
TO_CHAR(d, f)
SELECT SYSDATE, TO_CHAR(SYSDATE,'DAY') From dual;
SYSDATE TO_CHAR(S
07-MAY-98 THURSDAY
TO_DATE(char, 'f')
Format Description Example:
MM Number of month 12
RM Roman numeral month XII
MON Three-letter abbreviation DEC
MONTH Month fully spelt out DECEMBER
DDD Number of days since Jan 1 347
DD Number of the day in the month 13
D Number of days in week 2
DY Three-letter abbreviation of day WED
DAY Day fully spelt out WEDNESDAY
YYYY Full 4 digit year 1995
SYYYY Signed year 1000, BC = -1000
YYY Last three digits of year 995
YY Last 2 digits of year 95
Y Last digit of year 5
YEAR Year spelt out NINTEEN-NINTY-FIVE
HH Hours of day 9:10
HH12 Same as HH 9:10
HH24 Hours of day, 24 hour clock 23:29
MI Minutes of hour 45
SS Seconds of minute 23
TH Suffix to a number 13th
SELECT TO_DATE('20-MAR-98','RM') FROM dual;
TO_DATE('20-MAR-98','RM')
-------------------------
III
General Functions
GREATEST(expr1 [,expr2]...)
LEAST(expr1 [, expr2] ...)
NVL(col, value)
TRANSLATE(char, find, new)
DECODE(C,V1,S1,V2,S2,...,D)
UID
USER
GREATEST(expr1 [,expr2]...)
SELECT GREATEST (10, '7', -1) FROM dual;
GREATEST (10, '7', -1)
----------------------
10
LEAST(expr1 [, expr2] ...)
SELECT LEAST ('abcd', 'ABCD', 'a', 'xyz') "Least" FROM dual;
Leas
ABCD
NVL(col, value)
SELECT ename, sal, comm, sal+comm Gross,
sal+NVL(comm, 0) “New Gross” FROM emp;
* NVL(exp1, exp2) : If exp1 is null returns exp2; if exp1 is not null returns exp1. the exp1 and exp2 can be of any data type
SELECT ENAME, NVL( TO_CHAR( COMM) , 'NOT APPLICABLE' ) "COMMISSION" FROM EMP WHERE DEPTNO= 30;
TRANSLATE(char, find, new)
Select dname, TRANSLATE(dname,'e','1') from dept;
DECODE(C,V1,S1,V2,S2,...,D)
SELECT ename,job,DECODE( job, 'CLERK', 'EXECUTIVE',
'MANAGER', 'RM', job) FROM emp;
ENAME JOB DECODE(JO
----- --- ---------
SMITH CLERK EXECUTIVE
ALLEN SALESMAN SALESMAN
WARD SALESMAN SALESMAN
JONES MANAGER RM
MARTIN SALESMAN SALESMAN
BLAKE MANAGER RM
CLARK MANAGER RM
SCOTT ANALYST ANALYST
KING PRESIDENT PRESIDENT
TURNER SALESMAN SALESMAN
ADAMS CLERK EXECUTIVE
JAMES CLERK EXECUTIVE
FORD ANALYST ANALYST
MILLER CLERK EXECUTIVE
CUSTOMER Table
Table structure:
Name Null? Type
-------------------- -------- ---------
CODE NUMBER(4)
AMT NUMBER(7,2)
DUEDATE DATE
Using DECODE function, this report can be produced with ease:
SELECT code,
DECODE(months_between(sysdate,duedate),1,amt,null) "30-60 days",
DECODE(months_between(sysdate,duedate),2,amt,null) "60-90 days",
DECODE(months_between(sysdate,duedate),3,amt,null) "90-120 days",
DECODE(months_between(sysdate,duedate),4,amt,null) ">120 days"
FROM customer ORDER BY code;
* List the employee name, job and decode the job types MANAGER and CLERK to WORKER and BOSS respectively. Other job types becomes UNDEFINED by default.
SELECT ename, job,DECODE(job,'CLERK','WORKER','MANAGER',
'BOSS','UNDEFINED') NEWJOB FROM emp;
UID
connect scott/tiger
Connected.
SELECT UID FROM dual;
UID
---------
8
connect system/manager
Connected.
SELECT UID FROM dual;
UID
---------
5
USER
SQL> Connect scott/tiger
Connected.
SQL> SELECT USER FROM dual;
USER
SCOTT
SQL> Connect sys/change_on_install
Connected.
SQL> SELECT USER FROM dual;
USER
SCOTT
Group Functions
COUNT
SUM([DISTINCT |ALL]column name)
MAX (column name)
MIN(Column name)
AVG([DISTINCT | ALL]Column name)
COUNT
COUNT (*|[Distinct]|ALL|column name)
* List the number of employees working with the company:
SELECT COUNT(*) FROM emp;
* List the number of jobs available in the emp table:
SELECT COUNT(DISTINCT job) FROM emp;
SUM
SUM([DISTINCT |ALL]column name)
* List the total salaries payable to employees:
SELECT SUM(sal) FROM emp;
MAX
MAX (column name)
* List the maximum salary of employee working as a salesman:
SELECT MAX(sal) FROM emp WHERE job = 'SALESMAN';
MIN
MIN(Column name)
* List the minimum salary from emp table
SELECT MIN(sal) FROM emp;
AVG([DISTINCT | ALL]Column name)
* List the average salary and number of employees working in the department 20:
SELECT AVG(sal), COUNT(*) FROM emp WHERE deptno = 20;
Practice
List the job, average salary of employees with the same job;
List min salary of employees in each department
List the department having max no of employees.
List employees having min sal in their deptatmant
List total salary of each department
find present user and userid
List the employee name, job and decode the job types MANAGER and CLERK to WORKER and BOSS respectively. Other job types becomes UNDEFINED by default.
find and replace all C with c in loc of department
display sal and comm. From emp, comm. If null display as not applicable
Find the least of a,b,c
find the greatest of a,b,c
find the joining year of employees
find the date of next Wednesday
find the lastday of this month
find the no of months to your next birthday
find the starting date of this month
add 4 months to present date
display ename with width of 15 aligned right, job with width of 3, department name with width of 15 aligned left , sal
try replace, substring, initcap, lower, upper, ltrim, rtrim,ascii, instr,length concat,char functions and abs(n), ceil(n), floor(n), mod(m, n), power(m, n), sign(n), sqrt(n), trunc(m, [n]), round(m, [n]), exp(n)
Solution
1. select job, avg(sal) from emp group by job;
2. select deptno, min(sal) from emp group by deptno;
3. select deptno,count(*) from emp group by deptno having count(*)= (select max(count(*)) from emp group by deptno);
4. select deptno,ename, sal from emp where sal in (select min(sal) from emp group by deptno)
5. select deptno,sum( sal) from emp group by deptno
6. select uid, user from dual;
7. select ename, job,decode(job,'CLERK','WORKER','MANAGER', 'BOSS','UNDEFINED') newjob from emp;
8. Select loc, TRANSLATE(loc,'C','c') from dept
9. select ename, nvl( to_char( comm) , 'not applicable' ) "commission" from emp
10. select least('a','b','c') from dual;
11. select greatest('a','b','c') from dual;
12. select to_char(hiredate,'YYYY') from emp
13. select sysdate, next_day(sysdate,'wednesday') from dual;
14. select sysdate, last_day(sysdate) from dual;
15. select round(months_between('18-nov-03',sysdate)) from dual
16. select trunc (sysdate,'month') "first day" from dual
17. select add_months (sysdate,4) "4 months after" from dual
18. select lpad(ename, 15, ' '), substr(job, 1, 3),rpad(dname, 15, ' '),sal from emp,dept where emp.deptno=dept.deptno
Constructing an English sentence with data from table
Select ename ||’ works in ’||dname||’ department’ from emp, dept where emp.deptno=dept.deptno;
ENAME||'WORKSIN'||DNAME||'DEPARTMENT'
--------------------------------------
SMITH works in RESEARCH department
ALLEN works in SALES department
WARD works in SALES department
Data Definition Language (DDL)
Creating Tables
· Tables are created using the CREATE TABLE command
· Tables are owned by the user who creates them
· The names of tables owned by a given user must be unique
· The column names in the table must be unique
· Column names can be duplicated across tables
Naming Conventions of a table
· The table name must begin with a letter A-Z or a-z.
· It may contain letters, numerals and special characters.
· It may be upto 30 characters long.
· The table name must not be a SQL reserved word.
· The name must not be same as the name of any other object in your schema.
· Table name is not case sensitive.
Syntax
CREATE TABLE <table-name> (<column-name> <datatype(size)>,......)
* Create a table ITEMMAST, which has the following columns:
Itemno number 4
Name varchar2 20
Qoh number 4 quntity on hand
class character 1 category
uom character 4 unit of measure
rol number 5 reorder level
roq number 5 reorder quantity
rate number 8,2
CREATE TABLE itemmast
(
ITNO NUMBER(4),
NAME VARCHAR2(20),
QOH NUMBER(5),
CLASS CHAR(1),
UOM CHAR(4),
ROL NUMBER(5),
ROQ NUMBER(5),
RATE NUMBER(8, 2)
);
Applying column constraints
Constraints are a part of the table definition that are used to limit
the values entered into its columns
· NOT NULL : Prevent a column from accepting NULL values
· UNIQUE : Ensures uniqueness of the values in a column
· PRIMARY KEY: Same as UNIQUE, but only one column per table is allowed
· CHECK : Controls the value of a column(s) being inserted
· DEFAULT : Assigns a default value for the column(s), at the time of insertion when no value is given for that column
· REFERENCES : Assigns a Foreign Key constraint to maintain "Referential integrity"
NOT NULL
CREATE TABLE itemmast
(ITNO NUMBER(4),
NAME VARCHAR2(20),
QOH NUMBER(5),
CLASS CHAR(1),
UOM CHAR(4),
ROL NUMBER(5) NOT NULL,
ROQ NUMBER(5) NOT NULL,
RATE NUMBER(8, 2) NOT NULL);
UNIQUE
CREATE TABLE itemmast
(
ITNO NUMBER(4) NOT NULL CONSTRAINT IT_UN UNIQUE,
NAME VARCHAR2(20) NOT NULL CONSTRAINT IT_NA UNIQUE,
QOH NUMBER(5),
CLASS CHAR(1),
UOM CHAR(4),
ROL NUMBER(5) NOT NULL,
ROQ NUMBER(5) NOT NULL,
RATE NUMBER(8, 2) NOT NULL
);
CREATE TABLE itemmast
(
ITNO NUMBER(4) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
QOH NUMBER(5),
CLASS CHAR(1),
UOM CHAR(4),
ROL NUMBER(5) NOT NULL,
ROQ NUMBER(5) NOT NULL,
RATE NUMBER(8, 2) NOT NULL,
UNIQUE (ITNO, NAME)
);
PRIMARY KEY
CREATE TABLE itemmast
(ITNO NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL UNIQUE,
QOH NUMBER(5),
CLASS CHAR(1),
UOM CHAR(4),
ROL NUMBER(5) NOT NULL,
ROQ NUMBER(5) NOT NULL,
RATE NUMBER(8, 2) NOT NULL
);
CREATE TABLE itemmast
(ITNO NUMBER(4),
NAME VARCHAR2(20),
QOH NUMBER(5),
CLASS CHAR(1),
UOM CHAR(4),
ROL NUMBER(5) NOT NULL,
ROQ NUMBER(5) NOT NULL,
RATE NUMBER(8, 2) NOT NULL,
PRIMARY KEY (ITNO, NAME));
CHECK
CREATE TABLE itemmast
(ITNO NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
QOH NUMBER(5),
CLASS CHAR(1) CHECK (CLASS IN ('A', 'B', 'C')),
UOM CHAR(4),
ROL NUMBER(5) CHECK (ROL >0),
ROQ NUMBER(5) CHECK (ROQ >0),
RATE NUMBER(8, 2) NOT NULL
);
CREATE TABLE itemmast
(
ITNO NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
QOH NUMBER(5),
CLASS CHAR(1) NOT NULL,
UOM CHAR(4),
ROL NUMBER(5),
ROQ NUMBER(5),
RATE NUMBER(8, 2) NOT NULL,
CHECK (((CLASS = 'A' AND RATE <1000)
OR (CLASS = 'B' AND RATE >1000 AND RATE <4500)
OR (CLASS = 'C' AND RATE > 4500))
AND (ROL >0 AND ROQ > 0))
);
DEFAULT
CREATE TABLE itemmast
(
ITNO NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
QOH NUMBER(5) DEFAULT 100,
CLASS CHAR(1) NOT NULL,
UOM CHAR(4),
ROL NUMBER(5),
ROQ NUMBER(5),
RATE NUMBER(8, 2) NOT NULL,
CHECK (((CLASS = 'A' AND RATE <=1000)
OR (CLASS = 'B' AND RATE <=4500)
OR (CLASS = 'C' AND RATE > 4500))
AND
(ROL >0 AND ROQ > 0)
)
);
REFERENCES
* Create an item transaction table with Referential Integrity:
CREATE TABLE ittran (
itno NUMBER(4) REFERENCES ITEMMAST(itno),
trantype CHAR(1)check (Trantype IN ('I','R')),
trandate DATE,
qty NUMBER(5) );
ON DELETE CASCADE OPTION
* Create the table ITTRAN with ON DELETE CASCADE option
CREATE TABLE ittran (
itno NUMBER(4) REFERENCES ITEMMAST(itno)
ON DELETE CASCADE,
trantype CHAR(1)check (Trantype IN ('I','R')),
trandate DATE,
qty NUMBER(5));
SELECT * FROM TAB;
Creating a Table with Rows from Another table
The syntax is
CREATE TABLE Tablename
[(Column name ,........,....)]
AS SELECT statement.
· The table will be created with the specified columns and the rows retrieved by the SELECT statement inserted into it.
· If all the columns in the SELECT statement have well-defined names, (that is, no expressions, and so on), the column specifications may be omitted.
· If column specifications are given, the number of columns must equal the number of items in the SELECT list.
· Constraints information is inherited from the selected table.
· Data type for the column cannot be specified.
* Create a table emp2 from emp table having employee number, name, job, mgr, sal of employees in department 10 and 20.
CREATE TABLE emp2
AS SELECT empno,ename,job,mgr,sal FROM emp WHERE deptno in (10,20);
* Create a table Salary from table emp having ename, salary, grade details.
CREATE TABLE salary(Name, Salary)
AS SELECT ename,sal FROM emp;
Inserting Values into a Table
· Insert command is used to insert rows in the table.
· Values can be inserted for all the columns or for the selected columns
· To the INSERT INTO command, values can also be given through a query, however, the columns of the table being inserted must match the columns output by the subquery
· In place of values, parameter substitution can also be used with INSERT
Syntax
INSERT INTO <table-name> VALUES (<list-of-values>);
INSERT INTO emp
VALUES (7311,'TIMOTHY','CLERK',7001,'18-APR-95',3000,NULL,20);
Inserting the result of a query
* Insert records of 'MANAGER's into INCR table with an increment amount of Rs. 500/-:
INSERT INTO incr
SELECT empno, 500, SYSDATE FROM emp WHERE job = 'MANAGER';
Inserting through Parameter substitution
* Insert a row into emp table using parameter substitution:
INSERT INTO emp
VALUES (&1, '&2', '&3', &4, '&5', &6, NULL, &7);
Enter value for 1: 7311
Enter value for 2: TIMOTHY
Enter value for 3: CLERK
Enter value for 4: 7001
Enter value for 5: 18-APR-95
Enter value for 6: 3000
Enter value for 7: 20
old 2 : VALUES (&1, '&2', '&3', &4, '&5', &6, NULL, &7);
new 2 : VALUES (7311, 'TIMOTHY', 'CLERK', 7001,' 18-APR-95',3000, NULL, 20)
Updating Column(s) of a Table
· UPDATE command is used to update the columns in a table
· Values of a single column or a group of columns can be updated
· Updating can be carried out for all the rows in a table or selected rows
Syntax:
UPDATE <table-name> SET <col-name> = <value> [,col-name=value,...]
[WHERE <condition>]
* Give everybody a commission of Rs. 500:
UPDATE emp SET comm = 500;
· Expressions can also be used with UPDATE
* Increase everybody's salary by 10%:
UPDATE emp SET sal = sal + (sal *.1);
· Where Clause with Update command
* Change the department of KING to 40:
UPDATE emp SET deptno = 40 WHERE ename = 'KING';
· If where clause is omitted here, all the rows in the table will be updated.
Subqueries in the UPDATE command
* All employees who have more than 2 people reporting to them, are to directly report to the PRESIDENT:
UPDATE emp SET mgr = 7839
WHERE mgr <> 7839 AND empno IN ( SELECT mgr FROM emp
GROUP BY mgr HAVING COUNT (*) > 2);
* Double the commission for employees, who have got atleast 2 increments:
UPDATE emp SET comm = comm * 2
WHERE 2 <= (SELECT COUNT (*) FROM incr
WHERE incr.empno = emp.empno GROUP BY empno);
Deleting Row(s) From a Table
· DELETE command is used to delete rows from a table.
· The entire row is deleted from the table
· Specific columns cannot be deleted from a table
· CASCADE Delete rule instructs DBMS to automatically set the foreign key values to null in the child rows, if the parent row is deleted. The deletion thus cascades from the parent to the child.
Syntax
DELETE FROM <table-name> [WHERE <condition>]
* Delete all records from emp
DELETE FROM emp;
* Delete the records of clerks:
DELETE FROM emp WHERE job = 'CLERK';
* Delete the records of employees who have got no increment:
DELETE FROM emp
WHERE empno NOT IN (SELECT empno FROM incr);
Dropping Columns
· Oracle 8i enables you to drop columns from rows in a table.
· You can drop a column from a table by using the DROP COLUMN option of the ALTER TABLE command. This removes the column from the table description and removes the column length and data from each row of the table, thus freeing space in the data block. alter table semp drop column comm;
· The statement that you can use to drop the unused columns from table EMP is :
alter table semp set unused (comm)
ALTER TABLE emp DROP UNUSED COLUMNS;
· You can even mark the columns to be dropped at a future time when there is less demand on your system resources. This is the quicker alternative since dropping a column in a large table takes a considerable amount of time. Mark the column as unused with the SET UNUSED option of the ALTER TABLE command. The column data now becomes unavailable, although the data still remains in each row of the table. After marking a column as unused, you can add another column that has the same name to the table. The unused column can then be dropped at a later time when you want to reclaim the space occupied by the column data.
· There are some restrictions that apply to drop column operation. They are :
Column cannot be dropped from an object type table
Column cannot be dropped from nested tables
All the columns cannot be dropped from a table
Partitioning key column cannot be dropped
Parent key column cannot be dropped
Columns from tables that are owned by SYS cannot be dropped.
ADDING COLS TO A TABLE
CREATE TABLE BONUS
AS SELECT ENAME, JOB, SAL, COMM FORM EMP WHERE JOB= 'SALESMAN';
ALTER TABLE BONUS
ADD (BONUS NUMBER, BONUS_DATE DATE);
ALTER TABLE BONUS
MODIFY( BONUS NUMBER(7,2));
DROP TALBE
CREATE TABLE XYZ AS SELECT * FROM EMP;
SELECT * FROM XYZ;
DROP TABLE XYZ;
AUTO COMMIT
SET AUTOCOMMIT ON;
SET AUTOCOMMIT OFF;
UPDATE BONUS
SET ENAME ='MILLER'
WHERE ENAME =' MARTIN';
SELECT * FROM BONUS;
ROLLBACK;
SELECT * FROM BONUS;
Introduction to VIEWs
· VIEWS are database objects whose contents are derived from another table
· A VIEW contains no data of its own
· The command for creating VIEW is CREATE VIEW command
· The changes in the tables are automatically reflected in the VIEWs
Syntax
CREATE [OR REPLACE ] VIEW <view name>[(column1, column2,......)]
AS <select statement>
* Create a VIEW for the employees belonging to department 20:
CREATE VIEW dept20 AS
SELECT * FROM emp WHERE deptno = 20;
· After creating a VIEW, it can be queried just like querying a table.
SELECT * FROM dept20;
The OR REPLACE Option.
* Create a VIEW empview, which will contain the empno, ename, sal, deptno and dname:
CREATE OR REPLACE VIEW empview as
SELECT empno, emp.deptno, ename, sal, dname FROM emp, dept
WHERE emp.deptno = dept.deptno;
The above VIEW definition can be written as:
CREATE OR REPLACE VIEW empview (eno, dno, ename, sal, dname) as
SELECT empno, emp.deptno, ename, sal, dname FROM emp, dept
WHERE emp.deptno = dept.deptno;
SELECT * FROM empview WHERE dno = 20;
Group By Clause in VIEW creation.
* Create a VIEW for the INCR table containing the number of increments and total increment amount:
CREATE OR REPLACE VIEW empincr (empno, noicr, amount) as
SELECT empno, COUNT (*), SUM (amt) FROM incr GROUP BY empno;
To query this VIEW we can simply write:
SELECT * FROM empincr;
· The VIEW can also be joined to another VIEW and/or table
SELECT * FROM empview, empincr WHERE eno = empno;
* To find out the employee name, who has got the maximum increment amount so far:
SELECT empincr.empno, ename, amount FROM empincr, emp
WHERE empincr.empno = emp.empno
AND amount = (SELECT MAX (amount) FROM empincr);
Manipulating the Basetable(s) through VIEWs
· The INSERT, UPDATE and DELETE commands can also be used with VIEWs
· Using these commands with VIEWs is an indirect way of manipulating tables
· WITH CHECK OPTION clause allows integrity constraints and data validation checks to be enforced on data being INSERTED or UPDATED.
· Join view can also be modified
· Key–preserved table is used to understand the restrictions on modifying join views
CREATE VIEW empview(empno,tot) AS
SELECT empno, COUNT (*) FROM incr GROUP BY empno;
CREATE VIEW empsal (empno,newsal) AS
SELECT empno, sal *.10 FROM emp;
· The WITH CHECK OPTION clause
CREATE VIEW empdept AS
SELECT empno, ename, deptno FROM emp WHERE deptno = 30;
INSERT INTO empdept VALUES (1234, 'JAMILA', 20);
The above record gets inserted even though dept is 20.
CREATE or replace VIEW empdept AS
SELECT empno, ename, deptno FROM emp WHERE deptno = 30 WITH CHECK OPTION;
INSERT INTO empdept VALUES (1235, 'VASAN', 20);
Gives an error where clause violation. Change deptno to 30 it is ok.
· Constraint name to the WITH CHECK OPTION.
CREATE or replace VIEW EMP_DET (id_no, name, position, dept)
As SELECT empno, ename, job, deptno FROM emp
WHERE deptno In (SELECT DISTINCT deptno FROM dept)
WITH CHECK OPTION CONSTRAINT dept_cons;
Modifying a Join View
CREATE VIEW emp_view AS
SELECT ename, empno, deptno FROM emp;
· This view does not involve a join operation. If you issue the SQL statement:
UPDATE emp_view SET ename = ’CAESAR’ WHERE empno = 7839;
CREATE VIEW emp_dept AS
SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
FROM emp e, dept d /* JOIN operation */
WHERE e.deptno = d.deptno
AND d.loc IN (’DALLAS’, ’NEW YORK’, ’BOSTON’);
UPDATE emp_dept_view SET ename = ’JOHNSON’
WHERE ename = ’SMITH’;
DISTINCT operator
· Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
· Set operators: UNION, UNION ALL, INTERSECT, MINUS GROUP BY or HAVING clauses
· START WITH or CONNECT BY clauses
· ROWNUM pseudocolumn
CREATE TABLE dept (
deptno NUMBER(4) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13));
CREATE TABLE emp (
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
job varchar2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));.
Key–Preserved Tables
CREATE VIEW EMP_VIEW AS
SELECT EMPNO,ENAME,EMP.DEPTNO,DNAME,LOC
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
Rules for DML Statements on Join Views
· UPDATE Statements
UPDATE emp_dept SET sal = sal * 1.10 WHERE deptno = 10;
UPDATE emp_dept SET loc = ’BOSTON’ WHERE ename = ’SMITH’;
UPDATE emp_dept SET deptno = 10 WHERE ename = ’SMITH’;
DELETE Statements
DELETE FROM emp_dept WHERE ename = ’SMITH’;
CREATE VIEW emp_emp AS
SELECT e1.ename, e2.empno, deptno
FROM emp e1, emp e2 WHERE e1.empno = e2.empno;
CREATE VIEW emp_mgr AS
SELECT e1.ename, e2.ename mname
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno
WITH CHECK OPTION;
INSERT Statements
INSERT INTO emp_dept (ename, empno, deptno) VALUES(’KURODA’, 9010, 40);
INSERT INTO emp_dept (ename, empno, deptno) VALUES(’KURODA’, 9010, 77);
INSERT INTO emp_dept (empno, ename, loc) VALUES (9010, ’KURODA’, ’BOSTON’);
Dropping a VIEW
· DROP VIEW Command is used to delete a view from a database
The syntax is
DROP VIEW <viewname>
· Only the creator of the view can DROP it.
DROP VIEW v1;
Materialized Views
Materialized views are the generic objects which you can use to summarize, precompute, replicate, and distribute data. The materialized views are suitable for various computing environments like data warehousing, decision support, mobile computing or distributed computing.
· Need for Materialized Views
* Materialized views are used in data warehouses so as to increase the speed of queries on very large databases. In large databases, the queries often involve joins between tables and aggregation like SUM etc.
* Materialized views can be used to replicate data. Earlier the data was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as a synonym for CREATE SNAPSHOT.
* Query performance is improved using the materialized views as these views precalculate expensive join and aggregate operations on the database prior to the execution time.
* If you are not able to decide which materialized view you have to create, Oracle offers a set of advisory functions in the DBMS_OLAP package. The DBMS_OLAP package will help you in designing and evaluating materialized views.
* Materialized views can be accessed directly using the SQL statement. They can also be accessed directly in an INSERT, UPDATE, or DELETE statement depending on the types of refresh that are required.
Practice
* create a table salary from emp table using empno and sal fields.
* insert values to department table through parameter substitution.
* increment the salary of managers having more than 2 employees under them.
* All employees who have more than 2 people reporting to them, are to directly report to the PRESIDENT:
* Double the commission for employees, who have got atleast 2 increments:
* what is commit, rollback, autocommit?
* Create a VIEW for the employees belonging to department 20:
* Create a VIEW empview, which will contain the empno, ename, sal, deptno and dname:
* Create a VIEW for the INCR table containing the number of increments and total increment amount:
* using the above view find out the employee name, who has got the maximum increment amount so far:
* what is the difference in creating view with check option?
* drop all the views created by you
* create a view showing no of employees in each dept
* create a view showing the total salary of each dept
* create a view showing the total salary job wise;
* create a view for employees who are elegible for comm.
* Add 500 to all comm.
* Increase sal of all mangers by 500
* What is primary key, foreign key, check constraint
* Create ord_Master, prod_master and order_detail table using primary key, foreign key, check constraint for price.
Database Security and Privileges
· SQL is mostly executed in environments that require it to recognize and differentiate between the various users of the system
· Each user in an ORACLE database has a specific authorization
· Commands are interpreted and permitted ( or prohibited) on the basis of information associated with the authorization associated with the ID of the user issuing the command
· The Database Administrator (DBA) creates the users and gives them privileges
· Privileges determine whether or not a user can execute a given Command or a command when acting on specific groups of data
· There are different types of privileges corresponding to different types of operations
Making user
Login as system/manager
CREATE TABLESPACE tabsp2
DATAFILE 'tabfile.dat' SIZE 20M
CREATE TABLESPACE temp_ts
DATAFILE 'tempts.dat' SIZE 20M
CREATE USER mandar
IDENTIFIED BY oke
DEFAULT TABLESPACE tabsp2
QUOTA 5M ON tabsp2
QUOTA 5M ON temp_ts
QUOTA 5M ON system ;
grant connect, resource to mandar;
GRANT Command
· The GRANT command is used to grant access to the database.
· A user can grant access to his database object(s) to other user(s)
The syntax for GRANT command is :
GRANT <database_priv [, database_priv...]>
TO <user-name> [, <user-name>...]
IDENTIFIED BY <password> [, <password...];
GRANT <object_priv> | ALL
ON <object> TO <user|PUBLIC>
[WITH GRANT OPTION];
The privileges to be granted on tables
Privilege Object
SELECT data in a table or view
INSERT rows in a table or view
UPDATE rows or specific columns in a table or view
DELETE rows from table or view
ALTER column definition in a table
INDEX index to a table
REFERENCES refer to a table named within a table or column constraint
ALL refers to all the privileges shown above
* Grant all permissions on the EMP table to everybody:
GRANT ALL ON emp TO PUBLIC;
* Grant SELECT permission on the DEPT table to user 'Mandar' and allow him to give further grants:
GRANT SELECT ON dept TO Mandar WITH GRANT OPTION;
connect mandar/oke;
SELECT * FROM scott.dept;
Connect system/manger;
Create another user Tomson/tom;
Grant connect ,resource to tomson;
Connect mandar/oke;
Grant select on scott.dept to tomson;
connect tomson/tom
select * from scott.dept;
* Grant only the SELECT privilege to everybody on the columns empno, ename and sal of EMP table:
Connect scott/tiger
revoke all on emp from public;
Step 1: Create a VIEW for the above table:
CREATE VIEW empview AS
SELECT empno, ename, sal FROM emp;
Step 2: Grant SELECT privilege on the VIEW empview:
GRANT SELECT ON empview TO PUBLIC;
Step 1: Create a VIEW for the rows of dept 20:
CREATE VIEW empdept AS
SELECT empno, ename, sal FROM emp WHERE deptno = 20 WITH CHECK OPTION;
Step 2: Grant all the privileges to everybody:
GRANT ALL ON empdept
REVOKE Command
· Using the REVOKE command, a DBA can revoke database privileges from the user(s)
The syntax of REVOKE command is:
REVOKE <database_priv> FROM <user [, user]>;
REVOKE <object_priv>|ALL ON <object> FROM <user|PUBLIC>;
* Revoke UPDATE privilege from everybody on the EMP table
REVOKE UPDATE ON emp FROM PUBLIC;
Granting Privileges on Columns
· INSERT, UPDATE, or REFERENCES privileges can be granted on individual columns in a table.
The syntax for granting privileges on columns is :
GRANT <Column _priv> |
ON <object> TO <user|PUBLIC>
* Grant INSERT privilege on the ACCT_NO column of the ACCOUNTS table to SCOTT:
GRANT INSERT (acct_no) ON accounts TO scott;
Application Privileges Management
· ROLES are used to simplify the administrative tasks involved in the management of privileges.
· ROLE is a collection of related system privileges
· ORACLE provides three standard roles: CONNECT, RESOURCE and DBA
The syntax for creating a ROLE is
CREATE ROLE <role-name> [IDENTIFIED BY <password>];
· Privileges can be granted to a role.
· Role can be granted to user(s) or to other Role(s)
The syntax is
GRANT <role-name> TO <user-list>
· SET ROLE <role-name> command is used to enable/disable roles.
* If all the users in the HRD department required access to the EMP table, the access grant must be given to each individual users in that department. Alternatively, a role (say, hrd) is created and the privilege can be granted to the role, and this role in turn can be granted to all the users of the HRD department.
CREATE ROLE hrd;
GRANT SELECT, UPDATE ON emp TO hrd;
GRANT hrd TO caroline, annie;
· If several roles have been granted then one can switch between them during a session to enable or disable the associated privileges, using SET ROLE command.
The syntax for enabling role is :
SET ROLE <rolename>
The syntax for enabling all ROLE except one is
SET ROLE ALL EXCEPT <rolename>
* The command for enabling role hrd is :
SET ROLE hrd;
· The command for disabling all roles:
SET ROLE NONE.
* To display the list of ROLES created by user, DATA dictionary is USER_ROLES TABLE.
SELECT * FROM USER_ROLES;
Enhancing Performance
· Indexing and Clustering are two common ways of enhancing performance in retrieving information from a table
· These database objects are transparent to the user so SQL syntax is not changed with these methods.
Indexing
· Indexes are used to speed up the processes
· Indexes can also be used to ensure that no duplicate values are entered into a column
· Indexes do not have to be activated or deactivated
· ORACLE does not limit the number of indexes on a table
· When using multiple columns, upto 16 columns or a maximum of 255 characters of column space can be indexed
The syntax for creating INDEX is :
CREATE [UNIQUE] INDEX <index-name> ON <table-name> (<column-names>);
* Create a unique index for the EMP table on employee number:
CREATE UNIQUE INDEX empidx ON emp(empno);
* Selecting the record of an employee from the emp table providing the empno will run faster:
SELECT empno, ename, sal FROM emp WHERE empno = 7788;
* Create an index on deptno and dname columns of the dept table:
CREATE INDEX deptno ON dept(deptno, dname);
DROPping an INDEX
· DROP INDEX command is used to remove an index definition from the data dictionary.
DROP INDEX <indexname>
* Delete the index deptno:
DROP INDEX deptno;
SELECT * FROM USER_INDEXES;
Clustering
· Clusters are used for performance improvement
· Clustering is a method of storing tables that are related and often accessed together
· A cluster is a group of rows from separate tables stored in the same disk block
· Clustered tables must have a common column called the cluster column
· To cluster tables, the user must own the tables
Clustered Table Data
Clustered Key (ITNO)
ITNO Desc QOH
101 PEN 2000
Trantype Trandate
R 2-2-98
D 3-2-98
ITNO Desc QOH
102 PENCIL 3000
Trantype Trandate
R 4-5-98 Clustered Tables
D 5-5-98 Related data stored together, more efficiently
Unclustered Tables Related data stored apart, taking up more space
ITEMMAST
ITNO Desc QOH
101 PEN 2000
102 PENCIL 3000
ITTRAN
ITNO Trantype
101 R
101 D
102 R
102 D
Steps for Creating Clusters
Clusters are created in three steps:
· Creating Cluster
· Creating Clustered Tables
· Creating Cluster Index
Creating Cluster
Step 1: Create the cluster item:
* Clustering ITEMMAST and ITTRAN tables:
CREATE CLUSTER item (itno number(4));
* Creating Clustered Tables
Step 2: Create the ITEMMAST table:
CREATE TABLE itemmast
(
ITNO NUMBER(4) NOT NULL PRIMARY KEY,
DESCR VARCHAR(20) NOT NULL,
QOH NUMBER(5) DEFAULT 100,
CLASS CHAR(1),
UOM CHAR(4),
ROL NUMBER(5),
ROQ NUMBER(5),
RATE NUMBER(8, 2) NOT NULL
)
CLUSTER item (itno);
Step 3: Create the ITTRAN table:
CREATE TABLE ittran (
itno NUMBER(4) REFERENCES ITEMMAST(itno),
trantype CHAR(1),
trandate DATE,
tranqnty NUMBER(5) )
CLUSTER item(itno);
Creating Cluster Indexes
· In a clustered index, the actual data is sorted in the indexed order
· Each distinct cluster key value is stored only once in each data block
· They save disk space and improve performance for many operations
Step 4: Create the cluster index:
CREATE INDEX item_index ON CLUSTER item;
DROPing a CLUSTER
DROP CLUSTER command is used to remove a Cluster from the data dictionary.
The syntax is
DROP CLUSTER <cluster name>
* Delete the cluster item:
DROP CLUSTER item;
* Delete the cluster item which contains ITEMMAST and ITTRAN table:
DROP CLUSTER item INCLUDING TABLES;
* Delete the cluster item which contains ITEMMAST and ITTRAN table and the primary key of ITEMMAST table is also referenced by FOREIGN KEY of table X which is not part of the cluster ITEM.:
DROP CLUSTER item INCLUDING TABLES CASCADE CONSTRAINTS;
SELECT * FROM USER_CLUSTERS;
Sequences
· A sequence is a database object used to generate unique integers for use as primary keys
· A sequence is created through the CREATE SEQUENCE command
· The pseudo-column NEXTVAL is used to extract successive sequence numbers from a specified sequence.
· The pseudo-column CURRVAL is used to refer to a sequence number that is the current sequence number.
· Sequences can be altered and dropped.
The syntax for generating sequence is:
CREATE SEQUENCE <seq-name> [INCREMENT by <n>] [START WITH <m>]
[MAXVALUE N | NOMAXVALUE] [MINVALUE N | NOMINVALUE]
CREATE SEQUENCE empnumber INCREMENT BY 1 START WITH 8890;
· Generating Sequence numbers with Nextval
When you reference to the pseudo column NEXTVAL a new sequence number is generated.
SELECT empnumber.NEXTVAL FROM DUAL;
NEXTVAL
8891
INSERT INTO emp (empno, ename, sal)
VALUES (empnumber.NEXTVAL, 'HANS', 6000);
· Using Sequence Numbers with Currval
INSERT INTO emp (empno, ename, sal)
VALUES (empnumber.CURRVAL, 'AJAY', 7000);
This will insert 8892 in the column empno, as the CURRVAL IS 8892.
· Points to be remembered while using NEXTVAL and CURRVAL
NEXTVAL and CURRVAL can be used within
* SELECT clause of a SELECT statement
* VALUES list of an INSERT statements.
* SET clause of UPDATE statements
NEXTVAL and CURRVAL can not be used
* Within the SELECT list of VIEW
* With the keyword DISTINCT
* With the ORDER BY, GROUP BY, or HAVING clause of a SELECT statement.
* With the set operators - UNION, INTERSECT, MINUS
* Within a subquery.
Altering a Sequence
· Alter Sequence command is used to modify an existing sequence.
The command syntax is :
ALTER SEQUENCE <seq-name>
[INCREMENT by <n>]
[MAXVALUE N | NOMAXVALUE]
[MINVALUE N | NOMINVALUE]
ALTER SEQUENCE empnumber INCREMENT BY 2;
Removing a Sequences:
· A sequence definition is removed using DROP SEQUENCE command.
The syntax is:
DROP SEQUENCE <sequence name>;
DROP SEQUENCE empnumber
Maintaining Database Objects
· Database objects are created and stored in a database
· Tables, Clusters, Views, Indexes etc., are examples of database objects
· Maintenance of database objects involves creating, altering and/or deleting them from the database
· Modifications are done using the ALTER <database-obj-name> command
· Deleting is done using the DROP <database-obj-name> command
ALTER TABLE
· To change the definition of a table ALTER TABLE command is used
The syntax for ALTER table command is
ALTER TABLE <table-name>
[ADD (<col-element> | <constraints>...]
[MODIFY <col-element>..]
[DROP <options>];
· The ADD Option
ADD key word is used to add column or constraints to the table
Constraints can be:
PRIMARY KEY
CHECK
REFERENCES
The syntax is
ALTER TABLE <table-name>
[ADD (<col-element> | <constraints>...];
* Add a column to the existing table EMP, which will hold the grades for each employee:
ALTER TABLE emp ADD grade CHAR(2);
* Modify the emp table, add a PRIMARY KEY constraint ‘emp_C’:
ALTER TABLE emp ADD CONSTRAINT emp_C PRIMARY KEY (empno);
* Modify the emp table, add constraint REFERENCES to deptno of table emp refering deptno of table dept.
ALTER TABLE emp ADD CONSTRAINT
FK_C FOREIGN KEY (deptno) REFERENCES dept(deptno);
* Modify the emp table, add constraint CHECK for SAL of TABLE EMP (condition will be salary should be greater than 2500).
ALTER TABLE emp ADD CONSTRAINT
The MODIFY option
· The MODIFY option changes the following of an existing column
Datatype
Column width
Constraints i.e., DEFAULT, NOT NULL or NULL.
The syntax is:
ALTER TABLE <table-name> [MODIFY <col-element>..];
* Modify the sal column of the emp table to NOT NULL and increase its size to 10:
ALTER TABLE emp MODIFY sal NUMBER(10,2) NOT NULL;
* Modify the ename column. Increase its width to varchar(35).
ALTER TABLE emp MODIFY ename varchar(35);
The DROP option
The DROP option removes constraints from a table
The syntax is:
ALTER TABLE <table-name> [DROP <constraints>..];
ALTER TABLE EMP DROP CONSTRAINT EMP_SAL;
ALTER TABLE EMP DROP PRIMARY KEY;
The ENABLE/DISABLE clause
· Constraints are enabled or disabled using the ENABLE/DISABLE clause.
The syntax is:
ALTER TABLE <table-name> [DISABLE] <constraints>..]
[ENABLE] <constraints>..];
· Constraints can be enabled or disabled without dropping them or recreating them.
ALTER TABLE EMP DISABLE CONSTRAINT EMP_COMM CASCADE.
* Display columns and constraints of table emp
SELECT COLUMN_NAME, CONSTRAINT_NAME FROM
USER_CONS_COLUMNS WHERE TABLE_NAME='EMP';
DROP TABLE
DROP TABLE command removes the definition of an ORACLE table
The syntax is:
DROP TABLE <table_name >
* Delete the table ITEMMAST:
DROP TABLE itemmast;
· The CASCADE CONSTRAINTS clause with DROP TABLE command is used to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If this option is omitted, and such referential integrity constraints exist, Oracle returns an error and does not drop the table.
COMMIT and ROLLBACK
The COMMIT command is used to make changes to data
(inserts, updates, deletes) permanent,
The ROLLBACK command is used to discard parts or all the
work the user has done in the current transaction
Savepoints statements are used to discard or commit all the
changes upto a point.
INSERT INTO dept VALUES (50, 'CURRICULUM DEVP.', 'NEW DELHI');
SELECT * FROM dept;
ROLLBACK;
SELECT * FROM dept;
UPDATE dept SET dname = 'ACCT' WHERE deptno = 10;
SELECT * FROM dept;
COMMIT;
SELECT * FROM dept;
UPDATE dept SET loc = 'NEW DELHI' WHERE deptno = 40;
SELECT * FROM dept;
ROLLBACK;
SELECT * FROM dept;
Save Points
INSERT INTO dept VALUES (60, 'PURCHASE', 'NEW DELHI');
SAVEPOINT s1;
UPDATE emp SET deptno = 60 WHERE ename = 'SMITH';
ROLLBACK TO SAVEPOINT s1;