Introduction to SQL
To make interaction between data base and user we use sql
Sublanguages of sql are
- Data Definition Language
- Data Manipulation language
- Transaction control language
- Data query language
- Data control language
Data Definition Language: It is used define table structure
Commands of the DDL are
create
alter
rename
drop
truncate
- creating a table:
Syntax: create table<table name> (columnname1 Data type (size), columnname2 Data type (size)………..);
Ex: create table emp(eno number(10),ename varchar(10),sal number(5));
O/p: Table created.
Name / Null? / TypeENO / NUMBER(10)
ENAME / VARCHAR2(10)
SAL / NUMBER(5)
- ALTERING A TABLE:
Syntax: alter table<table name add (columnname data type(size)……….);
Ex: alter table emp add(edeptno number(10),loc varchar(10));
o/p: Table altered.
Name / Null? / TypeENO / NUMBER(10)
ENAME / VARCHAR2(10)
SAL / NUMBER(5)
EDEPTNO / NUMBER(10)
LOC / VARCHAR2(10)
- renaming a columnname:
Syntax: Alter table <table name> rename column<old name> to <new name>;
- Drop a column:
Syntax: Alter table <table name> column name;
Ex: alter table emp drop column loc;
desc emp;
O/p:
Name / Null? / TypeENO / NUMBER(10)
ENAME / VARCHAR2(10)
SAL / NUMBER(5)
EDEPTNO / NUMBER(10)
- TRUNCATE A TABLE:
Syntax: Truncate table <table name>;
Ex: truncate table emp;
O/p:
Table truncated
Data Manipulation Language (DML):
Oracle’s DML commands are used to add,update,delete,,and save values in the database.five commands are used:
INSERT : Adds records to a table.
UPDATE : Changes values in a record.
DELETE : Removes records from a table.
Insert:
Syntax: Insert into <tablename> (column1,column2,…..)Values(value1,value2,……….);
Ex: insert into emp values(7861,'sai',25000);
O/P:
ENO / ENAME / ESAL10 / siva / 200
1 / mohan / 300
5 / anvesh / 370
4 / naresh / 330
98 / sravan / 324
21 / vikram / 900
7861 / sai / 25000
Update:
Syntax: Update <tablename> set column1,column2 ,….. [where<condition>];
Ex: Update emp set esal=19999 where eno=10;
O/P:
ENO / ENAME / ESAL10 / siva / 19999
1 / mohan / 300
5 / anvesh / 370
21 / vikram / 900
7861 / sai / 25000
Delete:
Syntax: delete from <tablename> [where<condition>];
Ex: delete from emp where ename='anvesh';
O/P:
ENO / ENAME / ESAL10 / siva / 19999
1 / mohan / 300
4 / naresh / 330
98 / sravan / 324
21 / vikram / 900
7861 / sai / 25000
Constraints
1. Write a simple program to create a table with column level constraints without name ?
sql >create table d_p_t1 (dno varchar(12) not null);
sql > desc d_p_t1;
output:constraint created
Name / Null? / TypeDNO / NOT NULL / VARCHAR2(12)
2. Write a program to create a table with column level constraints with name ?
Sql > create table dept_const (dno varchar(12) constraint dept_pk primary key, dname varchar(12) constraint dept_uq unique);
Sql > desc dept_const;
output:
Name / Null? / TypeDNO / NOT NULL / VARCHAR2(12)
DNAME / VARCHAR2(12)
3. Write a program to create a table with Table level constraints with name ?
sql > create table stdept (dno number(5) , dname varchar(10),loc varchar(5), constraint cpk primary key(dno), constraint cuq unique(dname));
4. Write a program to create a table with Table level constraints without name ?
sql > create table stdept (dno number(5) , dname varchar(10),loc varchar(5), primary key(dno), unique(dname));
5. Write a program to drop and delete the constraints of the table. And list the all constraints of a user ?
Sql > select *from user_constraints;
Sql > desc user_constraints;
Sql > drop user_constraints;
Sql > delete user_constraints;
Date functions
5. Write a program to describe various DATE functions ?
Sql > select sysdate from dual;
Sql > select add_months('27-aug-2008','2') from dual;
Sql > select months_between('27-aug-2009','27-aug-2008') from dual;
Sql > select add_months(sysdate,'2') from dual;
Sql > select last_day('27-aug-2008') from dual;
Sql > select next_day('27-aug-2008','fri') from dual;
Sql > select to_char('23-jan-1988') from dual;
output:
SYSDATE05-NOV-08
ADD_MONTH
27-OCT-08
MONTHS_BETWEEN('27-AUG-2009','27-AUG-2008')
12
ADD_MONTH
05-JAN-09
LAST_DAY(
31-AUG-08
NEXT_DAY(
29-AUG-08
Group by functions
6. Write programs to describe group by functions ?
Sql > select deptno,sum(sal),avg(sal),count(*), count(comm) from semp group by deptno;
Sql > select min(sal) min_sal,max(sal) max_sal from emp;
7. Write a program to select max, min salaries of employees who are CLERKS ?
Sql > select min(sal),max(sal),deptno
from emp where job='CLERK' group by deptno ;
Sql > select * from emp;
output:
MIN(SAL) / MAX(SAL) / DEPTNO1300 / 1300 / 10
800 / 1100 / 20
950 / 950 / 30
EMPNO / ENAME / JOB / MGR / HIREDATE / SAL / COMM / DEPTNO
7369 / SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 20
7499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 30
7521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7788 / SCOTT / ANALYST / 7566 / 19-APR-87 / 3000 / 20
7839 / KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7934 / MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
14 rows selected.
JOINS :
1. Query on cross join ?
sql>select emp.ename,emp.job,emp1.sal
from emp cross join emp1
output:
ENAME / JOB / SALSree / DBA / 600
Ravi / enge / 600
Smith / man / 600
Sree / DBA / 2975
Ravi / enge / 2975
Smith / man / 2975
Sree / DBA / 2450
Ravi / enge / 2450
Smith / man / 2450
Sree / DBA / 3000
Ravi / enge / 3000
Smith / man / 3000
Sree / DBA / 5000
Ravi / enge / 5000
ENAME / JOB / SAL
Smith / man / 5000
Sree / DBA / 1100
Ravi / enge / 1100
Smith / man / 1100
Sree / DBA / 3000
Ravi / enge / 3000
Smith / man / 3000
Sree / DBA / 1300
Ravi / enge / 1300
Smith / man / 1300
24 rows selected.
2. Queries on simple joins ?
Sql>select em.ename,emp1.empno,em.job,emp1.hiredate
from em,emp1 where em.job=emp1.job
output:
ENAME / EMPNO / JOB / HIREDATESCOTT / 7788 / ANALYST / 19-APR-87
FORD / 7788 / ANALYST / 19-APR-87
SCOTT / 7902 / ANALYST / 03-DEC-81
FORD / 7902 / ANALYST / 03-DEC-81
SMITH / 7369 / CLERK / 17-DEC-80
ADAMS / 7369 / CLERK / 17-DEC-80
MILLER / 7369 / CLERK / 17-DEC-80
JAMES / 7369 / CLERK / 17-DEC-80
SMITH / 7876 / CLERK / 23-MAY-87
ADAMS / 7876 / CLERK / 23-MAY-87
MILLER / 7876 / CLERK / 23-MAY-87
JAMES / 7876 / CLERK / 23-MAY-87
SMITH / 7934 / CLERK / 23-JAN-82
ADAMS / 7934 / CLERK / 23-JAN-82
ENAME / EMPNO / JOB / HIREDATE
MILLER / 7934 / CLERK / 23-JAN-82
JAMES / 7934 / CLERK / 23-JAN-82
JONES / 7566 / MANAGER / 02-APR-81
CLARK / 7566 / MANAGER / 02-APR-81
BLAKE / 7566 / MANAGER / 02-APR-81
JONES / 7782 / MANAGER / 09-JUN-81
CLARK / 7782 / MANAGER / 09-JUN-81
BLAKE / 7782 / MANAGER / 09-JUN-81
KING / 7839 / PRESIDENT / 17-NOV-81
23 rows selected.
3. Query on INNER JOIN ?
sql>select em.ename,emp1.empno,em.job,emp1.hiredate
from em inner join emp1 on em.job=emp1.job
output:
SCOTT / 7788 / ANALYST / 19-APR-87FORD / 7788 / ANALYST / 19-APR-87
SCOTT / 7902 / ANALYST / 03-DEC-81
FORD / 7902 / ANALYST / 03-DEC-81
SMITH / 7369 / CLERK / 17-DEC-80
ADAMS / 7369 / CLERK / 17-DEC-80
MILLER / 7369 / CLERK / 17-DEC-80
JAMES / 7369 / CLERK / 17-DEC-80
SMITH / 7876 / CLERK / 23-MAY-87
ADAMS / 7876 / CLERK / 23-MAY-87
MILLER / 7876 / CLERK / 23-MAY-87
JAMES / 7876 / CLERK / 23-MAY-87
SMITH / 7934 / CLERK / 23-JAN-82
ADAMS / 7934 / CLERK / 23-JAN-82
ENAME / EMPNO / JOB / HIREDATE
MILLER / 7934 / CLERK / 23-JAN-82
JAMES / 7934 / CLERK / 23-JAN-82
JONES / 7566 / MANAGER / 02-APR-81
CLARK / 7566 / MANAGER / 02-APR-81
BLAKE / 7566 / MANAGER / 02-APR-81
JONES / 7782 / MANAGER / 09-JUN-81
CLARK / 7782 / MANAGER / 09-JUN-81
BLAKE / 7782 / MANAGER / 09-JUN-81
KING / 7839 / PRESIDENT / 17-NOV-81
23 rows selected.
4 . Query on NON EQUI JOIN ?
sql>select empno,ename,sal,grade from emp1,salgrade where emp1.sal >=salgrade.lowsal and emp1.sal<=salgrade.highsal
5. Queries on outer joins ?
LEFT OUTER JOIN:
sql> select em.ename,emp1.empno,em.job,emp1.hiredate
from em,emp1 where em.job=emp1.job(+)
(or)
sq1> select em.ename,emp1.empno,em.job,emp1.hiredate
from em left outer join emp1 on em.job=emp1.job
output:
ENAME / EMPNO / JOB / HIREDATEMILLER / 7369 / CLERK / 17-DEC-80
JAMES / 7369 / CLERK / 17-DEC-80
ADAMS / 7369 / CLERK / 17-DEC-80
SMITH / 7369 / CLERK / 17-DEC-80
CLARK / 7566 / MANAGER / 02-APR-81
BLAKE / 7566 / MANAGER / 02-APR-81
JONES / 7566 / MANAGER / 02-APR-81
CLARK / 7782 / MANAGER / 09-JUN-81
BLAKE / 7782 / MANAGER / 09-JUN-81
JONES / 7782 / MANAGER / 09-JUN-81
FORD / 7788 / ANALYST / 19-APR-87
SCOTT / 7788 / ANALYST / 19-APR-87
KING / 7839 / PRESIDENT / 17-NOV-81
MILLER / 7876 / CLERK / 23-MAY-87
ENAME / EMPNO / JOB / HIREDATE
JAMES / 7876 / CLERK / 23-MAY-87
ADAMS / 7876 / CLERK / 23-MAY-87
SMITH / 7876 / CLERK / 23-MAY-87
FORD / 7902 / ANALYST / 03-DEC-81
SCOTT / 7902 / ANALYST / 03-DEC-81
MILLER / 7934 / CLERK / 23-JAN-82
JAMES / 7934 / CLERK / 23-JAN-82
ADAMS / 7934 / CLERK / 23-JAN-82
SMITH / 7934 / CLERK / 23-JAN-82
TURNER / SALESMAN
MARTIN / SALESMAN
WARD / SALESMAN
ALLEN / SALESMAN
RIGHT OUTER JOIN:
sql>select em.ename,emp1.empno,em.job,emp1.hiredate
from em,emp1 where em.job(+)=emp1.job
(or)
sql> select em.ename,emp1.empno,em.job,emp1.hiredate
from em right outer join emp1 on em.job=emp1.job
output:
ENAME / EMPNO / JOB / HIREDATESMITH / 7934 / CLERK / 23-JAN-82
SMITH / 7876 / CLERK / 23-MAY-87
SMITH / 7369 / CLERK / 17-DEC-80
JONES / 7782 / MANAGER / 09-JUN-81
JONES / 7566 / MANAGER / 02-APR-81
BLAKE / 7782 / MANAGER / 09-JUN-81
BLAKE / 7566 / MANAGER / 02-APR-81
CLARK / 7782 / MANAGER / 09-JUN-81
CLARK / 7566 / MANAGER / 02-APR-81
SCOTT / 7902 / ANALYST / 03-DEC-81
SCOTT / 7788 / ANALYST / 19-APR-87
KING / 7839 / PRESIDENT / 17-NOV-81
ADAMS / 7934 / CLERK / 23-JAN-82
ADAMS / 7876 / CLERK / 23-MAY-87
ENAME / EMPNO / JOB / HIREDATE
ADAMS / 7369 / CLERK / 17-DEC-80
JAMES / 7934 / CLERK / 23-JAN-82
JAMES / 7876 / CLERK / 23-MAY-87
JAMES / 7369 / CLERK / 17-DEC-80
FORD / 7902 / ANALYST / 03-DEC-81
FORD / 7788 / ANALYST / 19-APR-87
MILLER / 7934 / CLERK / 23-JAN-82
MILLER / 7876 / CLERK / 23-MAY-87
MILLER / 7369 / CLERK / 17-DEC-80
FULL OUTER JOIN:
sql> select em.ename,emp1.empno,em.job,emp1.hiredate
from em full outer join emp1 on em.job=emp1.job
output:
ENAME / EMPNO / JOB / HIREDATEMILLER / 7369 / CLERK / 17-DEC-80
JAMES / 7369 / CLERK / 17-DEC-80
ADAMS / 7369 / CLERK / 17-DEC-80
SMITH / 7369 / CLERK / 17-DEC-80
CLARK / 7566 / MANAGER / 02-APR-81
JONES / 7782 / MANAGER / 09-JUN-81
FORD / 7788 / ANALYST / 19-APR-87
SCOTT / 7788 / ANALYST / 19-APR-87
KING / 7839 / PRESIDENT / 17-NOV-81
MILLER / 7876 / CLERK / 23-MAY-87
ENAME / EMPNO / JOB / HIREDATE
JAMES / 7876 / CLERK / 23-MAY-87
ADAMS / 7876 / CLERK / 23-MAY-87
SMITH / 7876 / CLERK / 23-MAY-87
SCOTT / 7902 / ANALYST / 03-DEC-81
MILLER / 7934 / CLERK / 23-JAN-82
JAMES / 7934 / CLERK / 23-JAN-82
ADAMS / 7934 / CLERK / 23-JAN-82
Select with where clause:
1. Write a Query to display all Records from Employee table
SqlSelect * from Emp1;
2. Write a Query to display ename, deptno, from Employee table
SqlSelect Ename, Deptno from emp1;
3. Write a Query to display all the 30th and 20th dept Employees
SqlSelect * from emp1 where deptno=20 or deptno=30;
output:
EMPNO / ENAME / JOB / MGR / HIREDATE / SAL / COMM / DEPTNO7369 / SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 20
7499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 30
7521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7788 / SCOTT / ANALYST / 7566 / 19-APR-87 / 3000 / 20
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
4. Write a Query to display all the 30th dept salesman data
Sql > Select * from emp1 where deptno=30 and job='SALESMAN';
output:
EMPNO / ENAME / JOB / MGR / HIREDATE / SAL / COMM / DEPTNO7499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 30
7521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
5. Write a Query to display all Employees data in descending order of
Salaries
Sql > Select * from emp1 order by Sal desc;
output:
EMPNO / ENAME / JOB / MGR / HIREDATE / SAL / COMM / DEPTNO7839 / KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7788 / SCOTT / ANALYST / 7566 / 19-APR-87 / 3000 / 20
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 30
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
7934 / MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
7521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7369 / SMITH / CLERK / 7902 / 17-DEC-80
6. Write a Query to display all Managers data in descending order of
Hire date
Sql > Select * from emp1 where job='MANAGER' order by HIREDATE Desc;
output:
EMPNO / ENAME / JOB / MGR / HIREDATE / SAL / COMM / DEPTNO7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
Set operators: Set operators are keywords that are used to combine records from different record or result sets.
The set operators are :
1. union all
2. union
3. intersect
4. minus
Table 1:
DEPTNO / JOB10 / manager
10 / clerk
10 / salesman
Table 2:
DEPTNO / JOB20 / manager
20 / analyst
20 / president
1. UNION ALL: select job from set1 where deptno=10
union all
select job from set2 where deptno=20;
output:
JOBmanager
clerk
salesman
analyst
president
2. UNION: select job from set1 where deptno=10
union
select job from set2 where deptno=20;
output :
JOBanalyst
clerk
manager
president
salesman
3. INTERSECT: select job from set1 where deptno=10
intersect
select job from set2 where deptno=20;
output :
JOBmanager
4. MINUS : select job from set1 where deptno=10
minus
select job from set2 where deptno=20;
output :
JOBclerk
salesman
SPECIAL OPERATORS:
Special operators will facilitate us to retrival of data based on operators. Viz.,
1.IN
2.NOT IN
3.BETWEEN
4.NOT BETWEEN
5.LIKE
6.NOT LIKE
7.NULL
8.NOT NULL
1. IN :
the IN operator is used to evaluate a column value against a set of values.
EX: select * from emp1
where job in('CLERK','MANAGER','ANALYST');
7369 / SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 207566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7788 / SCOTT / ANALYST / 7566 / 19-APR-87 / 3000 / 20
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7934 / MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
2.NOT IN
This does opposite of IN.
EX: Select * from emp1
Where ename not in('SMITH','BLAKES','SCOTT');
7499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 307521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7839 / KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7934 / MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
3.BETWEEN
The BETWEEN operator compares the column value with two arguments. If the column value is within the range of arguments, the condition is true. If the column value equals either the high-or-low range value, the condition is also true. The condition is false if the value falls outside this range.
EX: select * from emp1
where sal between 2000 and 3000;
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 207698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7788 / SCOTT / ANALYST / 7566 / 19-APR-87 / 3000 / 20
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
4.NOT BETWEEN
It does the opposite of BETWEEN.
. EX: select * from emp1
where sal not between 2000 and 3000;
7369 / SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 207499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 30
7521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7839 / KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7934 / MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
5.LIKE
The LIKE operator contains one or more characters, along with some special symbols. If the column value contains this pattern, condition is true.
EX1: select * from emp1
where ename like 'S%';
7369 / SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 207788 / SCOTT / ANALYST / 7566 / 19-APR-87 / 3000 / 20
.
. EX2: select * from emp1
where ename like '_A%';
7521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 307654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
EX3: select * from emp1
where ename like '_____';
7369 / SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 207499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 30
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7788 / SCOTT / ANALYST / 7566 / 19-APR-87 / 3000 / 20
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
6. NOT LIKE
It does the opposite of LIKE.
EX: Select * from emp where ename not like 'S%';
7499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 307521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7839 / KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7934 / MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
9.NULL
EX: select * from emp1
where comm is NULL;
7369 / SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 207566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7788 / SCOTT / ANALYST / 7566 / 19-APR-87 / 3000 / 20
7839 / KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7876 / ADAMS / CLERK / 7788 / 23-MAY-87 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7934 / MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
10.NOT NULL
it does opposite of NULL.
EX: select * from emp1
where comm is NOT NULL;
7499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 307521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
Subqueries:
A.Single row sub queries :
1. Write a sub query which displays all employees whose job is same as ALLEN’s job ?
Sql > select * from emp where job = (select job from emp where ename=’ALLEN’);
2. Write a query to display all employees whose salaries same as maximum salary of employees table ?
sql > *from emp where sal=(select max(sal) from emp);
3. Write a query to display all employees if salary of employee is more than max sal of 30th department ?
sql > select * from emp where sal >=(select max(sal) from emp where deptno=30 group by deptno );
B. Multiple row sub queries :
- in
- any
- all
- exists
in :
4. write a query to display all employees data if employee acting as a MANAGER for the other employee ?
sql > select * from emp where empno in (select distinct mgr from emp);
5. write a query to display all employees data if employee not acting as a MANAGER for the other employee ?
sql > select * from emp where empno in(select emplno from emp –(select distinct mgr from emp));
> any :
6.write write a query to display all employees data if salary of epmloyee is more than min sal of 10th department ?
sql > select * from emp where sal>any(select sal from emp where deptno=10);
< any :
7.write write a query to display all employees data if salary of epmloyee is less than min sal of 20th department ?
sql > select * from emp where sal < any(select sal from emp where deptno=20);
> all :
8. write write a query to display all employees data if salary of epmloyee is more than max sal of 10th department ?
sql > select * from emp where sal > all(select sal from emp where deptno=10);
< all :
9. write write a query to display all employees data if salary of epmloyee is less
than max sal of 10th department ?
sql > select * from emp where sal < all(select sal from emp where deptno=10);
10. write a query to display all the clerks data if number of clerks in emp table is more than or equal to 3 ?
sql > select * from emp where job=’CLERK’ and exists(select count(*) from emp where job=’CLERK’ havimg count(*)>=3);
Queries on Views:
1. Create a simple view ?
Sql > Create view e_v1 as select ename, job from emp;
Sql > desc e_v1;
Name / Null? / TypeENAME / VARCHAR2(10)
JOB / VARCHAR2(9)
Sql > select * from e_v1;
ENAME / JOBSMITH / CLERK
ALLEN / SALESMAN
WARD / SALESMAN
JONES / MANAGER
MARTIN / SALESMAN
BLAKE / MANAGER
SCOTT / ANALYST
TURNER / SALESMAN
ADAMS / CLERK
JAMES / CLERK
FORD / ANALYST
2. create a read only view ?
Sql > create view e_view as select empno,ename from emp with read only;
Sql > create view e_vw as select empno,ename,job from emp;
3. delete views?
Sql > delete from e_vw where empno=7369;
EMPNO / ENAME / JOB7499 / ALLEN / SALESMAN
7521 / WARD / SALESMAN
7566 / JONES / MANAGER
7654 / MARTIN / SALESMAN
7698 / BLAKE / MANAGER
7788 / SCOTT / ANALYST
7844 / TURNER / SALESMAN
7876 / ADAMS / CLERK
7900 / JAMES / CLERK
9 rows selected.