basic SQL commands and interview Q and A

Sql Basic Commands

DRL/DQL: - Select

Distinct Clause:-

Syntax: - Select Distinct <columnname1>,<columnname2> from <table1>,<table2>

Order by Clause:-

Syntax: - Select Distinct <columnname1>,<columnname2> from <table1>,<table2> order by <Columnname1>[ASC/DESC], <columname2>[ASC/DESC]

Where Clause:-

Syntax: - Select Distinct <columnname1>,<columnname2> from <table1>,<table2> Where <Condition> Order by  <Columnname1>[ASC/DESC], <columname2>[ASC/DESC]

In Operator:-

Syntax: - select *from TableName where deptno in (10, 20, 30)

Between Operator:-

Syntax: - select * from table where <columnname> between 2000 and 4000

Like Operator:-

Syntax: - select *from tablename where columnname like ‘M%’

      Like ‘—M—‘    (or)    Like ‘S-----‘(Or)

DDL: - Alter, Create, Drop, Rename, Truncate

Keywords: - Modify, Add, Rename, drop, Disable, enable

Alter:-

Syntax: - Alter table <TableName> Keyword <columnName> Datatype (width)

Modify:-

Syntax: - Alter table <TableName> Modify <ColumnName> Datatype (width);

Add:-

Alter Table <TableName> Add <ColumnName> Datatype (width)

Rename:-

Alter Table <TableName> Rename <Old ColumnName> To <NewColumnName>

Rename <oldtablename> to <new tablename> [for table rename]

Drop:-

Alter table <tableName> drop column1, colum2

Alter table <tableName> drop constraint <constraint name>

 Truncate:-

Truncate table <tablename>

10g New Features

How to see recycle bin contents

Ans: - show recyclebin

How to retrieve table from recyclebin

Ans:- Flashback table <original tablename>

How to delete recyclebin

Ans:- Purge recyclebin 

Order of Execution   1.From Clause   2.where Clause   3.Group by Clause

            4. Having Clause 5.Distinct Clause 6.Order by clause7. Select

Having Clause

Select [distinct] <col1>, <col2>------from <tab1>, <tab2> [where <condition> group by <col1>, <col2>----- [Having <condition>] Order by <col1> [asc/desc] ----] ---;

Group By Clause
it is Used to divide the data into multiple groups based on one (or) more Columns
Syntax	select [distinct] <col1>,<col2>------from <tab1>,<tab2> [where <condition> group by <col1>,<col2>----- [order by <col1>[asc/desc]----]---;

SQL Important queries

SQL: - How to Delete Duplicate from Table

Delete from tablename where rowed not in (Select Max (rowid) from Tablename group by ColumnName);

SQL: - Display 6^th – 8^th records from EMP table

Select *from EMP where rownum <=8

Minus

Select *from EMP where rownum<=6;

SQL: - Display Top 5 Record in table

Select *from EMP where rownum<=5;

SQL: - Display bottom 5 Record in table

 Select *from (select *from EMP order by rowid desc) where rownum <=5;

SQL: - Display 2^nd, 5th, 9^th records from EMP table

Select *from (select rownum as rno, emp. * from EMP) where rno in (2, 5, 9);

SQL: - Display department wise sum salary

Select deptno, sum (sal) from EMP group by deptno;

SQL: - display department wise and ename max (sal)

Select deptno, ename, max (sal) from EMP group by rollup (deptno, ename);

SQL: - Display EMP table first record

Select *from EMP where rownum=1;

SQL: - Display EMP table last record

Select *from EMP where rowid= (select max (rowid) from EMP);

SQL: - Display alternative rows from EMP table

Select *from EMP where (rowid, 0) in (select rowid, mod (rownum, 2) from EMP);

SQL: - display top 3 salaries

Select sal from(select *from emp order by sal desc) where rownum<4 or (<=3)

Display duplicate records from table;

Select deptno, count (*) from EMP group by deptno having count (*)>1

SQL: - display unique records from table

Select distinct deptno from EMP

SQL: - display ranks for EMP based on salaries

Select rank () over (order by sal desc nulls last) as rnk, sal from EMP;

SQL: - display dense_ rank () for EMP based on salaries

Select dense_rank () over (order by sal desc nulls last) as rnk, sal from EMP;

SQL: - Display ranks based on experiences

Select rank () over (order by hiredate asc) as rnk, ename, hiredate from EMP;

SQL: - Display ranks department wise based on salaries

Select dense_rank () over (partition by deptno order by sal desc nulls last) as rnk, deptno, ename, sal from EMP;

How do I display row number with records? 

SELECT ROWNUM, ENAME FROM EMP;

Subqueries               SQL: - Display details employes whose deptno same as SMITH

Select *from EMP where deptno= (select deptno from EMP where ename=’SMITH’);

SQL:- Display Odd/ Even number of records 
 Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
OUTPUT:- 1,3,5,7,9,11,13
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
OUTPUT:- 2,4,6,8,10,12,14

SQL: - How to copy of structure table

Create table <newtablename> as select *from <oldtablename>=1

How to delete duplicate.

Create new table based on the exiting table structure

Load data into new table using distinct clause

Rename the table

Insert into blg (select distinct *from emp2)

Delete last 3 days data

Delete from emp where current date=sysdate-3

Data partitioning & collecting methods Examples

1. Select suitable configurations file (nodes depending on data volume)   
2. Select buffer memory correctly 
3. Select proper partition 
4. Turn off Runtime Column propagation wherever it’s not required 
5. Taking care about sorting of the data.
6. Handling null values (use modify instead of transformer)  
7. Try to decrease the use of transformer. (Use copy, filter, modify)  
8. Use dataset instead of sequential file in the middle of the vast jobs 
9. Take maximum 20 stages for a job for best performance.
10. Select Join or Lookup or Merge (depending on data volume) 
11. Stop propagation of unnecessary metadata between the stages.