Skip to main content

SQL Basics -- Answers

Comments

shridatta said…
sir plz post the ans for question on functions...
Mithun Ashok said…
Hi Shridatta,

Answers posted.

Regards,
Mithun
Shweta said…
hi sir
can you please explain thin ans. bec'z me getting confused by this Ans For the same Q.
19. List emp working as managers and clerks with Salary atleast 2000 except in dept 10 and 20
SQL> SELECT * FROM emp WHERE job = 'MANAGER' OR job = 'CLERK' AND sal >=2000 AND deptno NOT IN (10,20);
Mithun Ashok said…
Hi Shweta,

When I say working as manager and clerk, does not mean that an employee can work as both manager and clerk, my question is..

Listing employees who are either managers or clerks and whose salary is greater than or equal to 2000 and should not belong to department 20 or 30.

Hope this helps. Let me know incase if still have issues understanding this question.

Regards,
Mithun
Shweta Arora said…
Thank you sir. I got it.
Shweta Arora said…
Hello sir,
Q. display all rows from emp table,the system should not wait after every screen full of information.

What should be the asswer of this Question?
Mithun Ashok said…
Hi Shweta,

I guess the question should be,

Q. Display all rows from emp table,the system should wait after every screen full of information.

You can make SQL*PLUS screen wait once the screen is full until you press enter key.

Try this,

SELECT * FROM USER_TABLES;

This will display the result within few seconds, here the display did not stop until you reached SQL command prompt.

Now try this,

SET PAUSE ON
SELECT * FROM USER_TABLES;

Now the screen gets paused everytime it gets full.

SET PAUSE OFF to return to default setting. SET PAUSE OFF is an SQL*PLUS command.

Hope this helps.
Shweta Arora said…
Hi Sir,

Thank you it's working for me. n thatks again now i got the sub quaries...
pavan said…
sir plz post the answr for sub and corelated queries and also for joins
Mithun Ashok said…
Hi Pavan,

Answers to Sub queries posted.

Mithun
hema said…
hi sir
display dept_wise total salaries for all the managers and analysts,only if the average salaries for the same is greater than or equal to 3000.
deptno job sal
20 manager 2975
20 analyst 3000
20 analyst 3000
30 manager 2850
10 manager 2450
select deptno,sum(sal)
from emp
where job in ('MANAGER','ANALYST')
group by deptno,sal
having avg(sal)>=3000;
output is
deptno sum(sal)
20 6000
Actually i should not get output.pls correct the query
Mithun Ashok said…
Hi Hema,

Here is the query,

select deptno,job, sum(sal)
from emp
where job in ('MANAGER','ANALYST')
group by job, deptno
having avg(sal) >=3000;


Mithun
hema said…
display the employees who are earning more than the avg sal of all the employees.
using subquery:
select * from emp where sal>(
select avg(sal) from emp;
how to write the query using self joins.pls tell me solution.
hema said…
display the dept names whose total salary(job wise) is more than 4500.pls give me solution using joins.
hema said…
hi sir,
pls send me answers for qtns which i have posted on 4th aug.on saturday i am attending mock.pls answer my qtns.
Mithun Ashok said…
Hi Hema,

Sorry for the delayed response. It was quite hectic for last 1 week.

Am not sure if this is possible using joins nevertheless am trying to get an answer for your questions. Will post this as soon as I form an answer for this.

Regards,
Mithun
rahul said…
sir where u gone i miss ur classes u r d best d rockstar..i enjoyed studin ur classes learnt a lot..like 2 dedicate couple of songs....oh pal hume yaad aayenge..zindegi ke safar main gujar jate hain jo makam oh phir nahi aate..em gettin emotional..t.c sir all d best!
Anonymous said…
Its been 2 days of your class @ BGudi, i feel better Offtopics can be skipped and better to start with the topics.
Mithun Ashok said…
Hi Rahul,

Thanks for ur message and the songs.... It feels good... :-)

Regards,
Mithun
Mithun Ashok said…
Hi Anonymous, hope your query on offtopics has been addressed.
Mithun
shalini said…
hi sir,
wat is d differnce between nvl and nvl2?
shalini said…
hi sir,
wat is d differnc between nvl d nvl2?m getting confused...
baba said…
sir i created this table
SI FIRST_NAME LASTNAME
1 hello how
2 how hello
then i m trying to insert the values
using this query

insert into customerr
(&SI,&LASTNAME,&FIRST_NAME)
VALUES('A113','MUDASEER','AHMED');
it displayed output as
Enter value for si:
Enter value for lastname:
Enter value for first_name:

it gives error can u plz tel me how
to insert into the table in such a way that i need not type insert into command again and again is it possible
baba said…
is it possible to delete a row using
update command?
baba said…
sir how this query works


select sal
from (select rownum as "hhh",sal
from (select sal from emp order by sal desc)
where rownum<=6)
order by sal desc
/
Mithun Ashok said…
Hi Shalini,

Refer to the link below for difference between NVL and NVL2.

NVLandNVL2

Regards,
Mithun
Mithun Ashok said…
Hi Baba,

1. Your statement below is wrong,

insert into customerr
(&SI,&LASTNAME,&FIRST_NAME)
VALUES('A113','MUDASEER','AHMED');

Write it this way.

insert into customerr
(SI,LASTNAME,FIRST_NAME)
VALUES('&SI','&LASTNAME','&FIRST_NAME');


2. As far as I know you cannot use update statement to delete a row from a table.

3. In the query below,

select sal
from (select rownum as "hhh",sal
from (select sal from emp order by sal desc)
where rownum<=6)
order by sal desc
/

You are trying to use subqueries inside from. Consider each subquery as a table. This statement lists salaries in an order.

Regards,
Mithun
Sharath said…
Hi sir,
Your website gives us lots of info and questions to go thru wit.. Pls keep updating as we would be following it up very closely.

Thank You.
Pramodnkul90 said…
thanks sir,for ur nice answers......
Pramodnkul90 said…
sir, ur site is the best for sql learning students.........
Varadakorti said…
Hi sir,
This is varada(back Up faculty QSP). Sir i have following doubts
1)How to create DB and User
2)How to give access to the User to create the table or a view
3)Write query to display employees who are getting salary more than there respective manager
RAhul Gupta said…
select a.empno,a.ename,a.sal,b.ename,b.sal
from emp a,emp b
WHERE a.mgr=b.empno
AND a.sal>b.sal;
Ayush Kabra said…
hi sir,
your website is awesome..............
Ayush Kabra1 said…
how can i got the second highest sal
ayush said…
please sir answer this question........

Increase the salaries of all the employees by 10% for the departments that are common in both of these tables.

Table1


Deptno
Empno
Sal
10
1
1000
10
2
3000
20
3
2000
30
4
6000
20
5
1000
60
6
9000
10
7
1000


Table2


Deptno
10
40
geeta said…
Hi Sir,

i am not able to execute mutli-row funs in online database
its giving invalid character.

plz help me how to execute.
rohit said…
rly dis web site is vry hlp ful 4 us keep updatin sir,,,,
Ajay said…
Hello Sir
Sir my question is
Q)I want to retrieve only 100th no row from a table?
vasanth kumar said…
how to retrieve data from two tables of two databases of same DB vendor
(i have 2 tables
1:student_info in college database
2:placement_info in placement database

regno is primary key in both the tables)
Vishal Chivate said…
hi sir how to check whether the given date is leap year
rupali namdeo said…
sir in one interview they asked how u will update all employ suffix.writ the query?sir plz help me
Mithun Ashok said…
Use the following link for the answer,

http://www.mithunashok.com/2014/07/finding-leap-year-using-sql.html
Mithun Ashok said…
Suffix by what value, can you post an example.
Anonymous said…
Hello sir,
Please explain me steps to install oracle database 12c on my ubuntu 14.04
please sir plz help me
Unknown said…
Hi Sir,
I want to know how to create the new connection in SQL Developer. I am getting some error messages while creating the new connection like 'IO error : The network adapter could not establish the connection'. Please help me out.

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…

'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.

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…