Introduction to SQL

Introduction to SQL

To make interaction between data base and user we use sql

Sublanguages of sql are

1. Data Definition Language
2. Data Manipulation language
3. Transaction control language
4. Data query language
5. Data control language

Data Definition Language: It is used define table structure

Commands of the DDL are

create

alter

rename

drop

truncate

1. 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.

1. 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.

1. renaming a columnname:

Syntax: Alter table <table name> rename column<old name> to <new name>;

1. Drop a column:

Syntax: Alter table <table name> column name;

Ex: alter table emp drop column loc;

desc emp;

O/p:

1. 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:

Update:

Syntax: Update <tablename> set column1,column2 ,….. [where<condition>];

Ex: Update emp set esal=19999 where eno=10;

O/P:

Delete:

Syntax: delete from <tablename> [where<condition>];

Ex: delete from emp where ename='anvesh';

O/P:

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

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:

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:

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:

14 rows selected.

JOINS :

1. Query on cross join ?

sql>select emp.ename,emp.job,emp1.sal

from emp cross join emp1

output:

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:

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:

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:

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:

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:

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:

4. Write a Query to display all the 30th dept salesman data

Sql > Select * from emp1 where deptno=30 and job='SALESMAN';

output:

5. Write a Query to display all Employees data in descending order of

Salaries

Sql > Select * from emp1 order by Sal desc;

output:

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:

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:

Table 2:

1. UNION ALL: select job from set1 where deptno=10

union all

select job from set2 where deptno=20;

output:

2. UNION: select job from set1 where deptno=10

union

select job from set2 where deptno=20;

output :

3. INTERSECT: select job from set1 where deptno=10

intersect

select job from set2 where deptno=20;

output :

4. MINUS : select job from set1 where deptno=10

minus

select job from set2 where deptno=20;

output :

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');

2.NOT IN

This does opposite of IN.

EX: Select * from emp1

Where ename not in('SMITH','BLAKES','SCOTT');

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;

4.NOT BETWEEN

It does the opposite of BETWEEN.

. EX: select * from emp1

where sal not between 2000 and 3000;

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%';

.

. EX2: select * from emp1

where ename like '_A%';

EX3: select * from emp1

where ename like '_____';

6. NOT LIKE

It does the opposite of LIKE.

EX: Select * from emp where ename not like 'S%';

9.NULL

EX: select * from emp1

where comm is NULL;

10.NOT NULL

it does opposite of NULL.

EX: select * from emp1

where comm is NOT NULL;

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;

Sql > select * from e_v1;

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;

9 rows selected.