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.

Bsic Unix commands

Cat: - it is used to create new files (or) to Open existing files (Or) Append data to the existing files

Syntax: - Cat > File Name

                Ctrld [save & close]

How to open a file

Cat < filename (or) cat filename

Appending data to the file

Cat>> filename

          Ctrl D

It append data at the end of the file

How to open multiple files

Cat file1 file2------file N

Ex: - cat emp1 emp2 emp3

Touch: - it is used to create empty file [0 byte file]

Syntax: - Touch filename

Creating multiple files

Syntax: - Touch file1 file2 file3

By using cat>> we can enter data into the files which were created by touch

How to delete files

Syntax: - rm filename

Rm –I file name [it deletes files with conformation message] delete file Y/N

Rm –f filename [it delete file F-forcibly]

Deleting multiple files

Syntax: rm file1 file2 file3 ----- file N

Working with directories

Syntax:- Mkdir dirname    [it is used to create a new directory

PWD [present working directories]

CD: - to change the directory

Syntax:- cd dirname

Cd abc

    Pwd

Cd..

To come out from current directory

Cut: - it is used to retrieve specific fields and specific characters in a given file

Cut –f 2, 4 filename [it displays 2^nd field to 5^th field file details

Cut –d “,” –f 2, 4 filename [here ‘,’ is the delimiter, it display 2 and 4 fields from file]

Cut –c 1-5 filename [c means character to character, it displays every line first character to fifth character]

Paste:-It is used to join two (or) more files horizontally by using delimiter

Syntax: - Paste filename1 filename2

Grep :- it is used to search a string (or) regular expression in a file

Syntax: - Grep techno file    [ grep pattern filename(s)]

Grep sachin file1 file2 file3

Grep sachin* [it searches in current directory all files]

Note: - if string contains more than one word it must enclosed with in double quotes, otherwise second word becomes file name

Ex: - “sachin sehwag” filename

Grep command options:-

1) Grep –i Sachin filename [to ignore case sensitive]

2) Grep –c Filename

3) Grep –n Sachin filename [it prints lines with line numbers containing the given pattern]

4) Grep-l Sachin* [it gives only filename containing given pattern

5) Grep –V “Sachin Sehwag” filename [it prints the line do not containing the given pattern

6) Grep –O Sachin filename [print only the pattern]

Any String contains wildcard character known as regular expression (or) pattern

The Patterns are classified into 3 types

1. Character pattern

The default pattern is character pattern

1)       Grep “Sachin*” filename

2)      Grep “b[aeiou]ll” sample

Output: - Ball, bell, bill, boll, bull,[this word are matched]  bfll [it is wrong]

3)      Grep “b..d” filename

Output: - band, bold, b$#d, bad

Note: - “.” Is a wild card character

2. Word Pattern

4)      Grep “\<Samsung\> filename [starting of the word]

(or)

            Grep-w “samsung” filename

5)      Grep “Samsung\> filename [ending of the word]

3. Line Pattern

^ => Start of the line

$ => end of the line

1). Grep “^d” filename                                 3). Grep “[bkt]” filename

Output: - do, dog, doing                               Output: - b - -, k- -, t- -

2). Grep “^the” filename             4). Grep “^ [^bkt]” filename [if ^ is inside then it means as not]

Output: - the, then, there            Output: - a--, c--, u--

5). Grep “t$” filename [it shows lines ending with ‘T’]

6). Grep “[0-9] $” filename [it shows lines ending with “digit”]

7). Grep “^…$” filename [line having 3 characters]

8) Grep “\$$” filename [displays line ending with $]

9) Grep “^$” filename [it prints no of empty lines

How to delete empty lines from file

10) Grep –v “^$” filename >temp

                Mv temp filename

F Grep [faster Grep]:-

*It is used to search multiple strings but it doesn’t used to search regular expression

*it searches the strings faster than the grep command.

11) Fgrep “unix

                     Perl

                   Oracle Filename

*Every string must separated by enter key

E Grep [extended Grep]:-

It is a combination of grep and f grep plus additional some regular expressions.

Additional Regular Expressions:-

*[;] :- it matches any one string in the given list

E Grep “(unix:oracle:perl)” filename

*{M} :- it matches exact occurrence of it’s proceeding character

Ex: - ab{3}c

Output: - abbbc

Sed [Stream editor]

It is used to replace a string and it is used for multipurpose filter command

1)      Sed “s/oldstring/newstring/g” filename

2)      Sed “s|^$|I like unix|” filename [it will replace all empty lines with “I like unix”

3)      Sed “s|unix||gi” filename [ it delete “unix” word from file]

4)      Sed –n ‘3p’ filename [How to print 3^rd row/record/line from file]

5)      Sed –n ‘2,5p’ filename [it prints 2^nd record to 5^th records from file]

6)      Sed –n ‘$p’ filename [it prints last record from file]

7)      Sed –n ‘1p

$p’ filename [it prints first and last record from file]

8)      Sed ‘3d’ filename [it deletes 3^rd record from file]

9)      Sed ‘2,5w file1’ file2 [ it copies 2-5 records from file2 to file1

How to add a header line say “Employee, EmpId” to this file using sed?

Sed -i ‘1a or (1i) Employee, EmpId’ empFile {‘1a’ means after it’ll insert ‘1i’ it’ll insert 1st record, like we can add any record any were}

Print lines which contain the character 'u' or 'x' 

sed -n '/[ux]/p' file

Print every alternate line:

Sed ‘n;d' file  N command prints the current line, and immediately reads the next line into pattern space. D command deletes the line present in pattern space. In this way, alternate lines get printed

To add something to the beginning of a every line in a file, say to add a word Fruit:
sed 's/^/Fruit:/’filename

Similarly, to add something to the end of the file

sed 's/$/Fruit:/’filename

To replace or substitute a particular character, say to replace 'a' with 'A'.

sed 's/a/A/' FileName

To replace only the 2nd occurrence of a character

sed 's/a/A/2g’filename

To replace 'a' only in a specific line say 3rd line, not in the entire file:

sed '3s/a/A/g’filename

To replace or substitute 'a' on a range of lines, say from 1st to 3rd line:

sed '1,3s/a/A/g’filename

Using sed, we can also do multiple substitutions. For example, say to replace all 'a' to 'A', and 'p' to 'P':

sed 's/a/A/g; s/p/P/g’ filename  (or) sed 's/a/A/g’ -e ‘s/p/P/g’ filename

Find sum of all columns / numbers in a line

1, Sed ‘s/ /+/g’ FileName | bc example:- 10 20 30 OutPut:- 60

2, Sed ‘s/,/+/g’ FileName | bc  example:- 10,20,30 OutPut:- 60

Sort:-

*It is used to sort the file contents

*By default it sorts the file contents based on ASCII values

*The default order is ascending order

1) Sort filename

2) sort –r filename (reverse ex:- descending)

3) sort –u filename [it displays unique lines in the file

Unique:-

It displays unique lines in the file but the file contents must be in sorted order

1)       Uniq filename

2)       Uniq –u filename [ it displays non duplicated lines

3)       Uniq –d filename [it displays only duplicated lines

4)       Uniq –c filename [it counts how many times each line repeated in the file]

5)       Uniq –u filename>temp

Mv temp >filename [how to delete duplicated rows fromfile]

Tee:-

It is used to write data to the file as well as to the screen

1)      Cat emp !tee file