Delete command

==============
A delete command is used to delete the records from the database table.

syntax:
delete from <table_name> where condition;

ex:
delete from student where sno=106;

delete from student where sname='RAMULU'; // no rows deleted

delete from student where sname='ramulu';

delete from student where sname is null;

Note:
-----
All DML commands are temperory.

If we won't use where clause then all rows will be deleted.

Interview Queries
------------------
Q)Write a query to promote Lisa from Clerk to Salesman?

update emp set job='Salesman' where ename='Lisa';

Q)Write a query to terminate the employees those who are working in 30 department?

delete from emp where deptno=30;

Logical Operators
===================
Logical operators are used to declare more then one condition in a query.

We have three logical operators.

1)AND

2)OR

3)NOT

1)AND
------
It select the records from the database table only if all conditions are true.

In logical AND operator , all conditions must be from same row.

ex:
 select * from student where sno=101 AND sname='raja';

select * from student where sno=101 AND sname='ravi'; // no rows selected

select * from student where sname='raja' and sadd='hyd';

2) OR
-----
It select the records from the database table if and only if one conditions is
true.

In logical OR operator, condition can be from any row.

ex:

select * from student where sno=101 OR sname='raja'; // 1 record

select * from student where sno=101 OR sname='ravi'; // 2 records

3)NOT
-------
It is used to select the records except the condition.

A '<>' is denoted as not operator.

ex:
select * from student where NOT sno=101;

select * from student where sno<>101;

select * from student where sname<>'ramana';

select * from student where sname<>'ramana' AND sno=101;

Interview Queries
-----------------
Q)Write a query to display employees information whose employee id
is 201,202 and 203?

select * from emp where eid=201 OR eid=202 OR eid=203;

Q)Write a query to display employee information whose employee id is 201 and

select * from emp where eid=201 AND job='Clerk';

Q)Write a query to display all the employee information whose salary is greater
then 15000 AND less then 30000?

select * from emp where esal>15000 AND esal<30000;

Q)Write a query to display all the employees information those who are not working
in 10 department?

select * from emp where deptno <> 10;

IN operator
-----------
IN operator is a replacement of OR operator.

IN operator will return the records those who are matching in the list.

ex:
select * from student where sno IN(101,102,103,104);

select * from student where sname IN('raja','ravi','jose','ramana');

select * from emp where deptno IN (10,20,30);

Between Operator
----------------
It is used to return the records those who are in the range of values.

A between operator will take the support of AND operator.

In between operator first we need to declare lower limit then higher limit.

ex:
select * from student where sno between 101 and 103;

select * from student where sno between 103 and 101; // no rows selected

select * from emp where deptno between 10 and 30;

select * from emp where esal between 15000 and 30000;

Pattern Matching operators

Pattern matching operators will take the support like keyword.

We have two types of pattern matching operators.

1)Percentage (%)

2)Underscore (_)

1)Percentage (%)
----------------

Q)Write a query to display employees information whose employee name starts with
'J' letter?

select * from emp where ename like 'J%';

Q)Write a query to display employees information whose employee name ends with
 'n' letter?

select * from emp where ename like '%n';

Q)Write a query to display employees information whose employee name middle letter
is 'l' letter?

select * from emp where ename like '%l%';

2) Undescore (_)
--------------
Q)Write a query to select employee information whose employee name having second
letter as 'e' letter?

select * from emp where ename like '_e%';

Q)Write a query to select employee information whose employee name having second
last letter as 's' letter?

select * from emp where ename like '%s_';

Q)Write a query to select employee information whose employee name having third
letter as 'l' letter?

select * from emp where ename like '__l%';