Rowed from emp mgr and their salary emp table count
Data Control Language (DCL)
Data Query Language (DQL)
DDL (Data Definition Language) used to define the data. For example, CREATE TABLE
DML (Data Manipulation Language) such as, INSERT and DELETE are used to manipulate data.
DDL
Data Definition Language allows to add or modify or delete the logical structures which contain the data or which allow users to access or maintain the data.
Some examples:
| ALTER TABLE table_name ALTER COLUMN column_name datatype |
|---|
| TRUNCATE TABLE table_name; |
|---|
COMMENT - add comments to the data dictionary
RENAME - rename an object
| ALTER TABLE oldtable RENAME TO newtable; |
|---|
DML
INSERT INTO table_name
(column1,column2,column3,...) |
|---|
UPDATE - updates existing data within a table
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
DCL
Some examples:
GRANT - gives user's access privileges to database
TCL
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
select * from emp
where rowid in (select decode( mod( rownum, 2), 0, rowid, null)
from emp);
Count MGR and their salary in emp table.
from emp;
Select any salary <3000 from emp table.
where sal > all (select sal from emp where sal < 3000 );
Select all the employee group by deptno and sal in descending order.
Create table emp1 as select * from emp
where 1=2;
select distinct sal from emp e1
where 3 = (select count (distinct sal )
where 3 = (select count(distinct sal)
from emp e2
Select LAST n records from a table
select * from emp
select * from dept
where deptno not in (select deptno from emp);
select empno,ename,b.deptno,dname from emp a, dept b
where a.deptno(+) = b.deptno and empno is null;
where a.sal <= b.sal) order by a.sal
desc;
where a.sal >= b.sal);
How to get nth max salaries ?
Select DISTINCT RECORDS from emp table.
select * from emp a
delete from emp a
where rowid != (select max(rowid)
where a.deptno(+)=b.deptno
group by b.deptno,dname;
where deptno=30 or deptno=10;
Select all record from emp table where deptno=30 and sal>1500.
where job not in ('SALESMAN','CLERK');
Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
where ename like'S____';
Select all records where ename may be any no of character but it should end with ‘R’.
where exists(select * from dept where emp.deptno=dept.deptno)
If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp)
Intersect
(Select * from emp1)
Count the totalsa deptno wise where more than 2 employees exist.