Database Testing

Software testing is a method to check whether the actual software product matches with the
expected requirement and to ensure that the software product is defect free.

Data- Collection of Meaningful information

Database- Collection Of Meaningful information in Computerized Form

Database testing- In Database testing we have to check the impact of backend (Database) due to the
Front end Operation.

DBMS (Database Management System)

DBMS store the data in File system.

There is no relation between two tables

Used for storing small amount/volume of data

We cannot use SQL language in DBMS.

eg XML File ,Notepad++(for understanding wordfile,excel)

RDBMS (Relational Database Management System)

DBMS store the data in Tabular Format.

there is relation between two tables

Used For Storing Large amount/volume of data

We used SQL language in RDBMS.

eg Oracle Version 10g/11g/12g

Microsoft SQL server(SSMS)

Sybase

MySQL

note
Oracle Version 10g/11g/12g (Backend)

Oracle Sql Developer(Frontend)

Database Testing is a Test Type in which Data manipulations, Data Integrity, Data
Comparison etc… Operations can be verified

Structured Query Language

SQL is a structured query language which is a Computer language used for storing, manipulating and
retrieving the data from relational database.

Basically it is a language which is used in RDBMS to Communicate with database.

SQL is divided into the following:

 Data Definition Language (DDL) (which is used to define the data structure)

 Data Manipulation Language (DML)(which is used to manipulate data itself)

 Transaction Control Language (TCL)

 Data Control Language (DCL)

DDL -- create, alter, drop, truncate, rename

DML -- insert, update, delete,select

TCL -- commit, rollback, savepoint

DCL -- grant, revoke

CREATE (it is used to create a table )

SYNTAX

Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);

Eg:

CREATE table PersonalInfo (Fname varchar2(12),Lname varchar2(12),Mob number(10));

DESCRIBE (it is used to display the table information)

SYNTAX

Describe or DESC <table_name>

eg. Describe PersonalInfo

INSERT

This will be used to insert the records into table.

we can insert the data in two ways

[Link] VALUE METHOD

Syntax:

insert into <table_name) values (value1, value2, value3 …. Valuen);

eg.

Insert into PersonalInfo values ('Mayur','Mishra','12345678');

[Link] DATA INTO SPECIFIED COLUMNS USING VALUE METHOD

Syntax:

insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….

Valuen);

eg . Insert into PersonalInfo (Fname,Lname,Mob)values(('Mayur','Mishra','12345678');

SELECT( The SELECT statement is used to display the data from a database)

Syntax:

Select * from <table_name>; -- here * indicates all columns

eg. Select * From PersonalInfo

or

Select col1, col2, … coln from <table_name>;

eg. Select Fname,Lname from PeronalInfo

Grant: to allow the permission to access the tables and database

Revoke:to remove the permission of database and tables.

Commit: to permanently save the data

Rollback: rollback is an operation which returns the database to some previous state

Database Tester Roles and Responsibilities-----

1. Table structure validation

- Validate table name as per srs document.

- Validate total columns as per srs.

- Validate each column name from table as per srs.

- Validate the sequence of column

- Validate data type of each column

- Validate data type size/length of each column.

2. Will check the impact of front end application on backend.

4. Constraint Testing

3. Null Validation

Create Command

create table Testing15Demo (eid number(7),ename varchar2(25),Emob varchar2(14),Esal

number(6),Ecity varchar2(30))

Q. How to display table info?----Ineterview Qusetion

We use Describe command to display table info.

describe TESTING15DEMO;

Insert Command- insert the rows/records into table.

First Approach to insert the rows.....

insert into TESTINGDEMO values (101,'Yusuf',9766732458,80000,'Pune');

insert into TESTING15DEMO values (102,'Priya',9866732458,90000,'Mum');

insert into TESTING15DEMO values (103,'stacey','+91-8787878787',80000,'Newyork');

insert into TESTING15DEMO values (104,'Bond007','+1-8787878787',80000,'Newyork');

insert into TESTING15DEMO values (1001,'Bond','+1-8787878787',80000,'Newyork');

insert into TESTING15DEMO values (105,'amit','+91-8787878787',80000,'Nagpur');

insert into TESTING15DEMO values (107,'Asmit','+1-8787878787',80000,'Nagpur');

INSERT into TESTING15DEMO values (109,'yusuf','+91-9787878787',80000,'Nagpur');

Select Command- display table data/selects records/rows from table

20/11/2021

How to dispaly table data?

select * from TESTING15DEMO

*-----All data/rows/records from table

Table data is case sensitive.

YusuF

yusuf

YUSUF

YuSUF

YUSuf

All above are different enames.

***** Database engine/Sql engine is responsible for executing the query.******

*** How to display particular columns from table***

select eid,ename,esal from testing15demo;

select ecity,eid,emob from testing15demo;

SQL Clauses----Where,group by ,having,order by

where (Conditional clause)---- It applies filter on table rows based on specific conditions.

select * from testing15demo;

I want to display the rows from table testing15demo whoes eid is 102.
select * from testing15demo where eid=102;

select ename,esal,ecity from testing15demo where eid = 102;

I want to display the rows from table testing15demo whoes eid is 107.

select * from testing15demo where eid = 107;

I want to display the rows from table testing15demo whoes ename is 'Yusuf'.

select * FRoM testing15demo where ename='Yusuf';

SELECT * from testing15demo where ecity='Newyork';

SELECT * from testing15demo where esal=90000;

insert into testing15demo values(110,'Nik',8766722458,90000);----It will not run.....shows error as 'not

enough values'

Second way of inserting the rows using insert command....

insert into testing15demo (eid,ename,emob,esal) values(110,'Nik',8766722458,90000);

insert into empinfo12345 (eid,ename,emob,esal) values(110,'Nik',8766722458,90000);

insert into empinfo1234576 (eid,ename,emob,esal, ecity) values(111,'Nikhil',766722458,95000,'Pune');

insert into testing15demo (eid) values(112);

insert into testing15demo (ecity,esal,eid) values('Pune',85000,115);

select * from testing15demo

Null---- It is value which we dont know what it is.----Unknown value

@@@

Update Command

Q. How to update salary with 100000 from table whose ename is 'Sachin'.------Capgemini 3+years

Interview Quest.

update testing15demo

set esal=100000

where ename='Asmit';
select * from testing15demo

update testing15demo set ecity='Mumbai' where eid=110;

update testing15demo

set ecity='Washington'

where ecity='Newyork';

update empinfo1234576

set esal=120000

where eid= 111;

update empinfo12345

set eid=110

where ecity='Mum';

select * from testing15demo;

update emp

where ename='Sachin'

set esal=60000;-----------Wrong Query

Question- Update multiple column values from table?

update testing15demo

set esal=200000,ename='Abhishek'

where eid=110;

update testing15demo

set esal=100000

where ename='Akash';

Question How to update all salaries from table?

update testing15demo

set ename = 'ABC';

Q- How to update single column value from table with null value?/ want to delete particular column

values of a one row from table?

select * from testing15demo;

update testing15demo

set ecity=''

where ecity='Newyork';

update empinfo1234576

set esal= 60000

where emob='9766732458'

Delete Command-- It will delete the data fromtable with or without condition.

delete from testing15demo where eid=112;

delete from testing15demo;

delete * from testing15demo where eid =103;---Wrong Query

select * from testing15demo;

delete from empinfo1234576;

Is null keyword----It is used to identify null values from particular column and display its resp. records

from table.

How to identify null values from your table?

select * from testing15demo;

select * from testing15demo where ecity is null;

select * from testing15demo where ename is null;

How to delete null values rows from your table?

delete from testing15demo where emob is null;

How to update null values rows from your table?

update testing15demo
set ename='Abhishek'

where ecity is null

ALTER USER HR IDENTIFIED BY HR

ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK

select * from COUNTRIES

select * From DEPARTMENTS

Select * from EMPLOYEES

SQL Operators-----

Compariosn

Arithmatic

Logical

Concatanition

Like

set

Select * from EMPLOYEES

select * from EMPLOYEES where SALARY = 4800

select * from EMPLOYEES where SALARY < 80000

select * from EMPLOYEES where SALARY <= 80000

select * from EMPLOYEES where SALARY > 90000

select * from EMPLOYEES where SALARY >= 4000

select * from EMPLOYEES where SALARY != 66000

Between Operator---- It displays the possible range of values from column. It applies on Numeric and
date values.

select * from EMPLOYEES where SALARY between 5000 and 80000;

Not Between

select * from EMPLOYEES where SALARY not between 50000 and 80000;

select * from testing15demo where esal not between 50000 and 60000;

In -----It displays the particular set of values from column. It applies on Numeric, alphabets and date
values.

select * from EMPLOYEES where SALARY in(3400);

select * from EMPLOYEES where SALARY not in(50000,80000,53000);

select * from EMPLOYEES where FIRST_NAME in ('Steven');

select * from EMPLOYEES where FIRST_NAME not in ('Pooja','Tom');

Concatinition---||---It combines the values of 2 columns.

select * from EMPLOYEES

select FIRST_NAME||LAST_NAME as FULLNAME from EMPLOYEES;

select (FIRST_NAME|| '_ '|| LAST_NAME) from EMPLOYEES

select (FIRST_NAME|| ' '||LAST_NAME) as Fullname from EMPLOYEES

select EMPLOYEES.*, (FIRST_NAME|| '_ '||LAST_NAME) as Fullname from EMPLOYEES

select (firstname||'__'||oamt) from cust_info;

describe cust_info

Q. How to concat firstname and lastname from table?

Riskfactors 1st and 2nd name alieasing uniqueid

select (firstname || lastname) as Fullname from tablename

select (firstname || ' ' || lastname) as Fullname from emp-----Citius Tech

Logical Operator

And or not

And

i1 i2 o/p

TTT

TFF

FTF

FFF

select * from EMPLOYEES where SALARY=170 and FIRST_NAME ='Neena';

select * from EMPLOYEES where FIRST_NAME ='Neena';

select * from employee_info2 where eid=112 and ecity ='unknown';

Or

i1 i2 o/p

TTT

TFT

FTT

FFF

select * from EMPLOYEES where SALARY=17000 or FIRST_NAME ='Neena';

select * from EMPLOYEES where SALARY=17 or FIRST_NAME ='Neena';

select * from EMPLOYEES where SALARY=17000 or FIRST_NAME ='Nee';

select * from EMPLOYEES where EMPLOYEES=1700 or FIRST_NAME ='Neen';

select * from employee12 where esal=80000 or esal=53000 or esal =50000

Not

i/p o/p

TF

FT

select * from EMPLOYEES where SALARY not in (80000)

----I want salaries other than 80000.

select * from employee_info2 where ename='unknown' or (eid=181 and ecity ='J and K');

select * from employee_info2 where esal is null;

Like Operator----
It is used for pattern/String search. It is case sensitive.
There are 2 wild card keys.
1. %---unknown length of string
2. _ ---- One Unknown character.
select * from EMPLOYEES where SALARY like '%11%'
select * from EMPLOYEES where FIRST_NAME like '%r%'
select * from EMPLOYEES where LAST_NAME like '________'
Q1. display the rows whose ename starts with '%r%'
select * from employee_info2 where ename like 'R%';
Riya 'R%'
Electrical_wires 'Electrical%'
select * from employee_info2 where ename like '%Electrical%';--Real time
Q2. display the rows whose ename ends with 'h'
select * from employee_info2 where ename like '%h'

Q3. display the rows whose ename's second char starts with 'a'----- _a% '__j%' ___e%
select * from employee_info2 where ename like '_a%'

Q4. display the rows whose ename's second last char is 'y'--- '%y_'
select * from employee_info2 where ename like '%y_'

[Link] exactly 5 char string from ename column.

Arithmatic Operators + -* /
select * from EMPLOYEES
Select (EMPLOYEE_ID+SALARY) FROM EMPLOYEES;
Select (EMPLOYEE_ID-SALARY) FROM EMPLOYEES;
Select (EMPLOYEE_ID*SALARY) FROM EMPLOYEES;
Select (SALARY/EMPLOYEE_ID) FROM EMPLOYEES;
Select (EMPLOYEE_ID+SALARY)as FULLPAYMENT FROM EMPLOYEES;
Select (EMPLOYEE_ID+SALARY),FIRST_NAME,LAST_NAME FROM EMPLOYEES;
select EMPLOYEES.*, (EMPLOYEE_ID+SALARY) as Total from EMPLOYEES
-----SQL Functions-----

[Link] Functions

[Link] Functions

[Link] Functions

Conversion Function

1. Aggregate Function---

Q- Do u know aggregate functions?

Max ,min ,avg ,sum ,count

select * from employee_info2

select min(esal) from employee_info2

select max(esal)from employee_info2

select avg(esal)from employee_info2

select sum(esal)from employee_info2

select * from employee_info2

select count(eid) from employee_info2

select count(ecity) from employee_info2

select count(emob) from employee_info2

select count(*) from employee_info2 ----It will display count of total no of table records/rows Seperate
Concept Distinct Keywords ---

How to display unique records from perticular column?

It will display unique records from perticular column.

Its not a aggregate func.

select * from employee11

select distinct(edept) from employee11;

How to dispay count of unique records?

select count (distinct(edept)) from employee11; 2.

Character Function

Case Manipulation/scalar Character Manipulation

lower upper initcap substr length INSTR

Q. Do you know scalar functions? ---CI havent heard abt this terminology. Please little bit eaborate on
this so i can recall if i have worked

these are the case related functions. In our organization we call them as Case Manipulation functions.

select lower(ename) from employee11

select ename,lower(ename) from employee11

select upper(ename) from employee11

select ename,upper(ename) from employee11

select initcap(ename) from employee11

select ename,initcap(ename) from employee11

select * from employee11 select upper('Vrushali') from dual

Length--- select * from employee11

select length (ename) from employee11

select ename, length (ename) from employee11

select length ('Vrushali') from dual;

select length ('Sangharsh') from dual;

Dual-- It is pseudo table/virtual table/system generated table.

Substr----Substring It displays the specific characters from string.

substr(columnname,x,y)

substr(stringname,x,y)

Dual- It is Pseudo table/Virtual table

x-- starting position of char where substring starts.

y---length of substtring from that staring char

123456789

Sangharsh

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SangharshRajdev

select substr('Sangharshrajdev',10,6) from dual

select substr('Sangharshrajdev',10) from dual;

select substr('Sangharshrajdev',-3,2) from dual;

1 2 3 4 5 6 7 8 -8 -7 -6 -5 -4 -3 -2 -1

Vrushali

select substr('Vrushali',4,4) from dual;

select substr('Vrushali',2,4) from dual;

select substr('Vrushali',1,5) from dual;

select substr('Vrushali',-6,4) from dual;

select substr('Vrushali',-7)

from dual; select substr('Vrushali',-3,-3) from dual;

--- It will not work as length should not be negative

select * from employee11

select ename,substr(ename,1,3) from employee11;

select ename,substr(ename,3,2),upper(ename) from employee11;

select ename,substr(ename,5,3) from employee11;

select ename,substr(ename,3) from employee11;

select ename,substr(ename,-5,3) from employee11;

select substr('Vrushali',-3) from dual;

select cname,substr(cname,-5,5) from customer_info1777;

select cname,substr(cname,5,1) from customer_info1777;

select substr(ename,-4,-3) from employee11;---will this work or not?

Explain why--- No. Length sould not be the negative value. ASK IN Interview Question_

Table name- Product_info---

pid pname pamount add pname TV123456789LG101 TV198765435 Samsung199

TV198765435Samsung201 O/p

Productno_Modelno 123456789_101 198765435_199 -+

select substr (product_name,3,9) || '_' || substr (product_name,-3,3) as Productno_Modelno from

product_info select * from product_info

Date Functions-----

add_months

months_between

next_day

last_day

sysdate

systimestamp

select * from employees

select * from JOB_History;

Select add_months(START_DATE,6)from JOB_HIstory

Select JOB_HIstory.*, add_months(START_DATE,6)from JOB_HIstory

Select add_months(END_DATE,4)from JOB_HIstory

Select JOB_HIstory.*, add_months(end_DATE,6)from JOB_HIstory

select months_between(start_date,End_date)from job_history

select months_between(end_date,start_date)from job_history

select sysdate from job_history

select sysdate from dual

select months_between(sysdate,start_date)from job_history

select job_history.*, next_day(start_date,'mon')from job_history

select next_day('08-Dec-2021','Fri') from dual

select last_day(start_date)from job_history

select last_day('08-Dec-2021') from dual

select systdate from dual

select systimestamp from dual

Conversion Function

nvl-it will convert the null value into actual value

select * from employees

select nvl(manager_id,'901')from employees

select employees.*, nvl(manager_id,'901')from employees

select employees.*, nvl(commission_pct,'999')from employees

Decode- Convert any small abbreviation into full name

mum nom pus

select * from employees

select
employees.*,decode(first_name,'Steven','Mayurmishra','Neena','Neenasaraswati','Lex','Laxman','unkno
wn')from employees

to_char --it will convert the date value into string value

select * from job_history

select to_char(start_date,'yy')from job_history--in numeric 09-12-2021 yyyy- year(numeric)

select to_char(start_date,'year')from job_history--- in word year(word) mm -- numeric

mon month(word) DD- day(numeric) day day(word)

select to_char(start_date,'yy-mon-dd')from job_history---numeric/word/numeric

select to_char(start_date,'yy-mm-day')from job_history

select to_char(start_date,'year-mon-dd')from job_history word- word- numeric

can you display the data who join in particular month?

select * from job_history where to_char(start_date,'mm')=09

can you display the data who join in particular year?

select * from job_history where to_char(start_date,'yyyy')=1989

can you display the data who join in particular quarter?

4quarters in year jan to mar 1 apr to jun 2 jul to sept 3 and oct to dec=4

select * from job_history where to_char(start_date,'q')=3

To_date---it will convert the character value into date value

select to_date('2021-12-08','yyyy-dd-mm')from dual

SELECT to_date('01-JAN-2009') FROM dual

SELECT to_date('01-JANUARY-2009') FROM dual

SELECT to_date('33-JAN-2009') FROM dual-- error

89-sep-thursday

SQL Clauses---

Where

group by

having

order by
Where---- It filters the rows from table based on specific conditions and display the

records.

select * from employees

select * from employees where first_name='Steven';

select last_name,salary,email from employees where first_name='Steven';

Group by---It divides the multiple rows into identical groups.

We can use only group by column or aggregate fun column(with any column from table) in select

statement.

Or both group by column and aggregate fun column (with any column from table).

select Count(*) from employees

select JOB_ID from employees group by JOB_ID

select count(*) from employees group by JOB_ID

select JOB_ID,count(*) from employees group by JOB_ID

select JOB_ID,count (JOB_ID)from employees group by JOB_ID

select * from employees;

select JOB_ID,count (salary)from employees group by JOB_ID;

select Salary from employees group by JOB_ID;---- It will not run the query

select JOB_ID,salary from employees group by JOB_ID---It will not run the query

select FIRST_NAME,count(JOB_ID) from employees group by JOB_ID-----It will not run the query

Interview Questions on Group by Clause---

----Display deptwise Count of employee---

select * from employees

 select JOB_ID,count(*) from employees group by JOB_ID

----Display deptwise highest salary---vvvvvvimp

select JOB_ID, max(salary) from employees group by job_Id

----Display deptwise total salary---

select JOB_ID, sum(salary) from employees group by job_Id

----Display deptwise avg salary---

select JOB_ID, avg(salary) from employees group by job_Id

----Display deptwise min salary---

select JOB_ID, min(salary) from employees group by job_Id

-----Display avg sal and sum of sal

select job_ID,avg(salary),sum(salary) ,max (salary)from employees group by job_ID

having Clause------It applies filter on results which are generated by group by clause using specific

aggregate functions.

select * from employees

select job_id ,count(job_id) from employees

group by job_id

having count (job_id) > 1

 select job_id,count(*) from employees

group by job_id

having count(*) > 1

------how to display duplicate records-------vvvvimp----Asked in 95% interviews

Ans1

select edept,count(*) from employee11

group by edept

having count(*) > 1

Ans2

select edept,count(edept) from employee11

group by edept

having count(edept) > 1

Order by--- Sorting the records in asc or desc order.

asc---0 to 9,A to Z,a to z

number---capital letter---small letters

desc---9 to 0, Z to A ,z to a
Reverse of the asc order

select * from employees order by salary desc;

select * from employees order by salary asc;

select * from employees order by salary

select * from employees order by first_name desc;

select * from employees order by commission_pct desc nulls last;

select * from employees order by commission_pct asc nulls first;

Execution Order of any query

from select * from tablename where columnname, groupby column name having order by

Where

group by

having

order by

***** Drop Truncate Alter Rename *******

Drop--- Drop is DDL statement command.

It will delete table strucure and table data.

(After Select Statement)-- User will not find perticular table when he apply drop commad on that

table.i.e Rollback is not possible.

we cant use where clause in drop command

select * from tablename

drop table tablename

Rename--- We can change table or column name using this command.

rename existing tablename to new tablename

select * from newtablename

Truncate-- It DDL statement command.

It will delete only table data and keep table structure as it is.

(After Select Statement)-- User will find particular table with its strutcure but it will not dispaly table

data.i.e Rollback is not possible

we cant use where clause in truncate command

select * from customer_info11

truncate table customer_info11

Alter--It DDL statement command. types/statement/subset

It deals with table [Link] is used to modify the table structure.

Rollback is not possible.

1. How to add 1 new column to existing table.

desc TestingDemo11

select * from TestingDemo11

alter table TestingDemo11 add (estate varchar2(7));

2. How to add multiple columns to existing table.

desc Testing1234

alter table TestingDemo11 add (edept varchar2(20),eclass varchar2(15));

3. How to drop 1 column from existing table.

desc Testing1234

alter table TestingDemo11 drop (eclass);

4. How to drop multile columns from existing table.

desc demo6

alter table TestingDemo11 drop (emob,esal);

5. How to rename a column?

desc TestingDemo11

alter table TestingDemo11 rename column ename to empname;

------Subquery-------Query within query----

There are 2 parts in subquery.

1. Inner Query 2. Outer Query

Execution Process-- First it will execute inner query and then will execute outer query.

And while executing outer query, output of inner query will be used as input for outer query.

1. Find out all employee details with its highest salary.

select * from employees

select max (Salary) from employees;

Find out all employee details with its highest salary.

1. select * from employees where Salary = (select max(Salary) from employees)

2 select * from employees where Salary in (select max(Salary) from employees)

2. Find out all employee details with its lowest salary.

1. select * from employees where Salary = (select min(Salary) from employees)

2 select * from employees where Salary in (select min(Salary) from employees)

select * from employee11 where esal in (select min(esal) from employee11)

3. How to display Second max salary -------VVIMP with resp to inetrview----asked in almost all
organisation

select max(salary) from employees where salary < (select max (salary) from employees); --- prefer this

17000 24000 24000

***Secoond way to find 2nd max sal

select * from employees where salary not in (24000)

select max(salary) from employees where salary not in (select max(salary) from employees))

220000 24000

4. How to display Second min salary -------VVIMP with resp to inetrview----asked in almost all

organisation

select min(salary) from employees where salary > (select min(salary) from employees)--- prefer this

2200 2100

***Secoond way to find 2nd min sal

select min(salary) from employees where salary not in (select min(salary) from employees)

2200 2100 2100 lowest record

5. Second max salary with all details

select * from employees where salary in (select max(salary) from employees where salary not in (select
max(salary) from employees))

6. Second min salary with all details

select * from employees where salary in (select min(salary) from employees where salary not in (select

min(salary) from employees))

Analytical Fucction - Rank,dense rank row num ,rowid

Rank and Dense Rank

there the virtual columns which will be applied on the table externally

Rank and Dense Rank both are functions which canused to calculate the ranks

Rank Functions generating the gaps while giving the rank

Dense rank not generating gaps while giving the rank

Salary Rank Dense Rank

5000 1 1

4500 2 2

4300 3 3

4300 3 3

4000 5 4

3500 6 5

3500 6 5

3100 8 6

select tablename.*,Rank ()over (order by Columnname desc)from tablename

select employees.*,Rank()over (order by (Salary) asc) from employees;

select employees.*,Rank()over ( order by Salary asc) from employees;

Dense Rank

select tablename.*,Dense_Rank ()over (order by Columnname desc)from tablename

(select employees.*,Dense_Rank()over (order by (Salary) asc)as ranking from employees)

select employees.*,Rank()over ( order by Salary asc)as Ranking from employees;

(select employees.*, rank() over (order by salary desc) as Ranking from employees)

-----vvimp que------

select * from ( select employees.* , dense_rank()over(order by salary desc ) as

ranking from employees) where ranking between 1 and 10

Interviwe Qustions on Highest and Lowest Salaries

[Link] Salary with emp details

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking=1

2.2nd max sal whit emp details

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking=2

3.3rd max sal

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking=3

4.64 th max sal

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking=64

5.100th max sal

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking=100

[Link] 5 highest sal

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking<=5

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking between 1 and 5

[Link] between 3rd max to 6th max

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking between 3 and 6

[Link] not between 3rd max to 6th max

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking not between 3 and 6

[Link] salaries 3rd max,6th max,8th max

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking in( 3,6,8)

[Link] salaries which are not 3rd max,6th max,8th max

select * from ( select employees.*,dense_rank() over ( order by salary desc ) as ranking from

employees) where ranking not in( 3,6,8)

[Link] Salary

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking=1

2.2nd lowest sal

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking=2
3.3rd Lowest sal

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking=3

4.64 th Lowest sal

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking=64

5.1000th Lowest sal

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking=1000

[Link] 5 Lowest sal

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking<=5

[Link] between 3rd Lowest to 6th Lowest

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking between 3 and 6

[Link] not between 3rd Lowst to 6th lowest

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking not between 3 and 6

[Link] salaries 3rd Low,6th Low,8th Low

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking in( 3,6,8)

[Link] salaries which are not 3rd Low,6th Low,8th Low

select * from ( select employees.*,dense_rank() over ( order by salary ) as ranking from employees)

where ranking not in( 3,6,8)

---Rownum------

it will generate the temporary sequence number

select employees.*,rownum from employees

Shows particular range of records

Display first 5 records

select employees.*,rownum from employees where rownum <= 5

select employees.*,rownum from employees (order by desc )where rownum <= 5

select employees.*,rownum from employees where rownum <=15

Display lastt 5 records

select * from(select employees.*,rownum from employees order by rownum desc) where rownum <=10

------Rowid____

it is the unique id for each row

select employees.*,rowid from employees

select * from employees where rowid not in(

select max(rowid)from employees group by JOb_id)

Joins--- it is used to display the data from multiple tables

Inner Join

left outer Join

Right Outer Join

Full Outer Join

Equi Join

Non equi koin

create table custinfo1 (cid number(6),cname varchar2(15),ccity varchar2(20))

create table invoiceinfo1 (oid number(6),oname varchar2(15),billamount varchar2(20),custid

number(7));

select * from custinfo1

select * from invoiceinfo1

insert into custinfo1 values (101,'Yusuf','Pune'); 101,'Yusuf','Pune' 1,'mayur',500,100);,(5)

insert into custinfo1 values (102,'Priya','Mum'); 101,'Yusuf','Pune' 2,'snehal',600,102

insert into custinfo1 values (103,'stacey','ytl'); 101,'Yusuf','Pune' 3,'shubham',700,107)

insert into custinfo1 values (104,'Bond007','Nag'); 101,'Yusuf','Pune' (4,'bhagyshri',800,103);

101,'Yusuf','Pune'
(5,'pranav',900,109);
insert into invoiceinfo1 values (1,'mayur',500,100); 102,'Priya','Mum');total count(5)

insert into invoiceinfo1 values (2,'snehal',600,102); (103,'stacey','ytl') count(5)

insert into invoiceinfo1 values (3,'shubham',700,107);

insert into invoiceinfo1 values (4,'bhagyshri',800,103);

insert into invoiceinfo1 values (5,'pranav',900,109);

1. Inner join

It dispalys the matching records from both side table based on specific condition.

select * from table1 inner join table 2 on [Link] = [Link]

select * from custinfo1 inner join invoiceinfo1 on [Link]=[Link]

select oname,billamount,cname,ccity from invoiceinfo1 inner join custinfo1 on

[Link]=[Link]

2. Left outer join/ Left Join-------It displays matching records from 2 tables and remaining records from

left side table.

For that remaining records it will display null values to right side columns.

select * from custinfo1 left outer join invoiceinfo1 on [Link]=[Link]

select * from invoiceinfo1 left join custinfo1 on [Link]=[Link]

3.. Right outer join-----it displays matching records from 2 tables and remaining records from right side

table.

For that remaining records it will display null values to left side columns
 select * from custinfo1 right outer join invoiceinfo1 on [Link]=[Link]

select * from custinfo1 right join invoiceinfo1 on [Link]=[Link]

4. Full outer join----it displays matching records from 2 tables and remaining records from both side

table.

For that remaining records it will display null values to left and right side columns.

select * from custinfo1 full outer join invoiceinfo1 on [Link]=[Link]

select * from custinfo1 full join invoiceinfo1 on [Link]=[Link]

Cross join---It is based on cartesian method. Each row of one table will be mapped with every row of

second table.

select * from custinfo1 cross join invoiceinfo1

Equi Join

select * from custinfo1,invoiceinfo1 where [Link]=[Link]

Non Equi Join( same as Cross Join but it will except the matchig records)

select * from custinfo1,invoiceinfo1 where [Link]!=[Link]

select * from custinfo1,invoiceinfo1 where [Link]<[Link]

select * from custinfo1,invoiceinfo1 where [Link]>[Link]

create table A1(a1 number(5),a2 number(5));

create table b1(b1 number(5),b2 number(5));

insert into A1 values(1,1);

insert into A1 values('','');

insert into A1 values(1,1);

insert into A1 values(1,1);

insert into b1 values(1,1);

insert into b1 values(1,1);

insert into b1 values(1,1);

insert into b1 values(1,1);

select * from a

select * from b

select * from A inner join B on A.A1=b.b1

select * from A left join B on A.A1=b.b1

select * from a cross join b

select * from a

select * from a1

select * from b1

select * from A1 inner join B1 on A1.A1=b1.b1

select * from A1 left join B1 on A1.A1=b1.b1

select * from A left join B on A.A1=b.b1

select * from a cross join b

select * from a inner join

Homework

Q1.

AB

a1 a2 b1 b2

11 11 count= 1111

11 11 1111

11 11 1111

11 11 1111

Q2. 11111(4),4,4

AB

a1 a2 b1 b2

1 1 null 1

1 null 1 null

null 1 1 1

1 1 1 null