Skip to main content

SQL Interview Questions



Some more Online Questions.

 On WIZIQ.COM -- 66 Questions and its answers Found by Neha Abhay Kumar
 On SCRIBD.COM -- 235 Questions and its answers



                                              

Comments

hema said…
hi sir,
1) create table products(
proid number(4),
pname varchar(10) not null,
qty number(4));
while creating the table i forgot
to proid as primary key & qty as check constraint.how to add these constraints to these columns
2)are foreign key & reference key one & the same ?
hema said…
hi sir,
1) create table products(
proid number(4),
pname varchar(10) not null,
qty number(4));
while creating the table i forgot
to declare proid as primary key & qty as check constraint.how to add these constraints to these columns
Mithun Ashok said…
Hi Hema,

1. Any constraint can be added or dropped at any point in time.

To add the constaints,

ALTER TABLE products ADD PRIMARY KEY(proid);


ALTER TABLE products ADD CONSTRAINT checkqty CHECK(qty > 1);

To drop use,

ALTER TABLE products DROP PRIMARY KEY;

ALTER TABLE products drop CONSTRAINT checkqty;

2. No Foreign Key and Reference Key are not same. Though none of the authors/books on DBMS define reference key(as far as my knowledge is concerned), I differentiate foreign and reference key as,

A foreign key is a referential integrity constraint that creates relationship between tables. Foreign is one of the constaints.

A Reference key is the primary key of table referred by a table(could be same table or another table) . Reference key is just a name given and not a constraint.

Look at the example below,

CREATE TABLE emp1(empno number(4) primary key,ename varchar(10) not null,age number(10) check (age >14), edeptno number(2) references dept(deptno));

Here emp.edeptno is a foreign key and dept.deptno is a reference key which is dept primary key.
This comment has been removed by the author.
Shweta said…
hello sir,
i have a problm with 'edit'. its not executing and giving an error like "SP2-0110: Cannot create save file "afiedt.buf"
What can i do for that. Please help me out.
thank you.
Mithun Ashok said…
Hi Shweta,

In your Start --> All Programs Right Click on sqlplus icon and then click on Properties.

Under Shortcut tab, change "Start In" directory to either "C:\" or to "D:\" or any custom directory where you can store files.

Save/Apply the settings. Now login to fresh sqlplus from Start --> All Programs and try to use edit command.

Let me know if this helps.

Regards,
Mithun
shweta said…
hi sir,
No, its not working, when i change the drive i mena path and click on apply it gave me an error pop up window. that "the folder specified satrt inbox is not valid".
shweta said…
hi sir,
thanx. its working now.
Sundar said…
Hi sir,
Please discuss all SQL questions in your blog on the final day of our class! (If possible at your comfortness)
Also please discuss what type of difficulties we'll face while writing a sql query & how to overcome it!

Thanks & Regards,
Sundar
rahul said…
gud mng sir can u tell me d important sql interview questions?
Mithun Ashok said…
Hi Rahul,

You can refer to,

Interview Questions

Regards,
Mithun
hema said…
1)display the employees who are earning more than the avg sal of all the employees ......write query using self joins...
select * from emp
where sal > (select avg(sal) from emp);this ans is using subqueries...how to write using joins


2)display the dept names whose total salary(job wise) is more than 4500.pls give me solution using joins.
select b.dname
from emp a join dept b
on a.detno=b.deptno
group by b.dname
having sum(sal)> 4500...i think this query is wrong....pls send me correct ans.
already i posted these 2 qtns on aug 4th...but till now i didnt get reply....
Mithun Ashok said…
Hi Hema,

1. Using join,

SELECT e.empno, e.ename,e.sal, e.deptno, b.avgsal
FROM emp e
JOIN
(SELECT ROUND(AVG(sal)) AS avgsal
FROM emp
) b ON e.sal > b.avgsal;

2. select b.dname,a.job, sum(sal)
from emp a join dept b
on a.deptno=b.deptno
group by b.dname,a.job
having sum(sal)> 4500;

Regards,
Mithun
hema said…
hi sir,
thanks for sending ans...
pankaj kumar said…
hello sir,
can i use distinct with conditional operator or where clause???

select distict ename,sal from emp where sal=3000;
select distinct ename,sal from emp where sal< 3000;
i am still getting more than 2 values in my result set
Mithun Ashok said…
Hi Pankaj,

Yes you can you distinct with where conditions.

When you use distinct like below,

select distinct ename,sal from emp where sal=3000;

ENAME SAL
---------- ----------
SCOTT 3000
FORD 3000

Here if you notice, SCOTT3000 and FORD3000 are distinct values together.

Regards,
Mithun
uday woodyar said…
sir i need a complete notes of sql
can i get it from your web site
Mithun Ashok said…
Hi Uday,

You can refer to these documents and notes but you will not be able to download it. All the notes here are only for reference not for download.

Regards,
Mithun
Smruti said…
What is the difference between count(*),count(1),count(0),count(-1),count(100),count(-100). Why these all are returning same value.
Amit Jha said…
life cycle of WinXP...
"http://technologizer.com/2011/10/24/windows-xp/"
Amit Jha said…
This is worth Reading...
"http://arstechnica.com/microsoft/news/2011/10/ten-years-of-windows-xp-how-longevity-became-a-curse.ars"
Anonymous said…
sir ,
i want to know how index work.
i mean how it is useful to access database in faster way.
Anonymous said…
sir,
i want to know how index work in retrieving the data in faster way.
i executed the statment
CREATE INDEX salary ON emp (sal);
but i don't know how to use it.
AMBESH THAKUR said…
SIR,
HOW TO DISPLAY THE NTH ROW
AMBESH THAKUR said…
count of any thing returns number of row in table(ie. select count(*) from emp;) for example refer attached image
Siree Gnr said…
sir,
How to delete duplicate rows from a table using DELETE
Alok Gogoi said…
Sir,
While I try to create a new user by following statement:
create user QP identified by xx;
It comes as 'insufficient privileges'
Please let me know why?
Sreenivasamoorthy said…
Hai sir This is Sreenivasamoorthy,your sql student
please send me 3+ years exp cv's to my mail:
sreenivasamoorthy67@gmail.com
Mithun Ashok said…
Sreenivas,

You need to prepare your resume by yourself. I can only tell you how to prepare your resume and I do not have any resumes with me.
aniket said…
hi sir,

how can i find second highest salary in sql without using max function.
anajn said…
hi sir i want query for this

Write a query to get 2nd & 6th max salary from EMP table
Kollul Baruah said…
hi sir
whats exactly happen in this two query
select 'abc'from emp;
select 'abc'abc from emp;
why in the first query in column heading c is not coming.u can xplain in evening 4 o clock jspider batch.i am from dere
Kollul Baruah said…
hi sir
whats exactly happen in this two query
select 'abc'from emp;
select 'abc'abc from emp;
why in the first query in column heading c is not coming.u can xplain in evening 4 o clock jspider batch.i am from dere
Kollul Baruah said…
hi sir
whats exactly happen in this two query
select 'abc'from emp;
select 'abc'abc from emp;
why in the first query in column heading c is not coming.u can xplain in evening 4 o clock jspider batch.i am from dere
shibin ps said…
This comment has been removed by the author.
hariom said…
hi sir, how to create the table with a date format column for ex:table name is potential and column name is start date and closing date.
CREATE TABLE POTENTIAL(START DATE DD-MM-YY,CLOSED DATE DD-MM-YY)
hi sir for Question NO. 4
SELECT * FROM EMP
WHERE SUBSTR(JOB,1,3)='MAN';
--will select all the employees who are mangers
SELECT * FROM EMP
WHERE SUBSTR(JOB,1)='MAN';
--says no rows selected
why is it so?
surabhi swarnim said…
Hi Sir,
Can u include questions on joins as well as rownum & rowid?
Also i have doubt that why SELECT* from emp where rownum=2; gives no rows selected and select* from emp where rownum<=2 returns two rows from emp table.
Thanks
Ritika Raj said…
one of the bestest teacher one could ever have is you... the way you taught us sql cannot be matched by anyone in this world..... hats off to you sir.......I really admire you.
select *,ename from emp; sir why it is showing error
vinayak gadag said…
hi sir , how to write query for this
display the columns which has no null values
Shiva Kumar said…
U can't use both * and column name

Soln

Select emp.*,ename from emp;

Shiva Kumar said…
Hi sir
How to write a query to display
1st row- Max sal
2nd row -main sal
3rd row- 2nd Max sal
4th row - 2nd main sal
Amar G said…
Nice set of interview questions and answers.i have gone through it very helpful one thanks for sharing...
SQL Interview Questions & Answers.

Popular posts from this blog

SQL Interview Questions on Subqueries

SUB Queries:
1. List the employees working in research department 2. List employees who are located in New York and Chicago
3. Display the department name in which ANALYSTS are working
4. Display employees who are reporting to JONES
5. Display all the employees who are reporting to Jones Manager
6. Display all the managers in SALES and ACCOUNTING department
7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them
8. Display all employees who do not have any reportees
9. List employees who are having at least 2 reporting
10. List the department names which are having more than 5 employees
11. List department name having at-least 3 salesman
12. List employees from research and accounting having at-least 2 reporting
13. Display second max salary
14. Display 4th max salary
15. Display 5th max salary  -- Answer for nth Max Salary
Co-Related Subqueries:
16. Write a query to get 4th max salary from EMP table
17. Write a query to get 2nd…

Answers for SUB Queries

1. SQL> select empno, ename from emp where deptno=(select deptno from dept where dname='RESEARCH');


2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));

3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST');

4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES');

5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES')

6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING'))

7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp)

8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not null)

9. select…

'Linux-x86_64 Error: 28: No space left on device' While trying to start the database -- Error

SQL> startup mount pfile='/tmp/initdlfasp12.ora'
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device


This as you can see is on Linux x86 with 64 bit processor. We got this error after we changed SGA on 10gR2 database. So was sure that this is something to do with the OS.

Parameters to check for this are shmall.

shmall is the total amount of shared memory, in pages, that the system can use at one time.

$cat /proc/sys/kernel/shmmax
53687091200

$ getconf PAGE_SIZE
4096

As per Oracle SHMALL should be set to the total amount of physical RAM divided by page size.

Our system has 64GB memory, so change kernel.shmall = 1024 * 1024 * 1024 * 64 / 4096 = 16777216


Once this value is calculated you can modify Linux system configuration file directly.

$ su - root
vi /etc/sysctl.conf file:
kernel.shmall=16777216

and

# sysctl -p

Once this is done the database was started without any problem.