Skip to main content

SQL Basics -- Answers

Comments

Unknown 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...
Unknown 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 Kumar Das said…
Hello Sir
Sir my question is
Q)I want to retrieve only 100th no row from a table?
Unknown 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)
Unknown said…
hi sir how to check whether the given date is leap year
Unknown 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…

Basics of RDBMS

Data
Small set of information becomes data, this set of information helps make decision. Data is always some useful information.


Database
Place where you store the data. Database represents some aspect of the real world called "miniworld". A database is designed, built and populated with data for a specific purpose. It has intended group of users and some preconceived applications in which these users are interested.

In other words, a database has some source from which data is derived, some degree of interaction with events in the real world and an audience that is actively interested in the contents of the database.

Database can also be defined as collection of one or more tables.

Ex: Mobile, human brain etc



DBMS (Database Management System)
Is a program that stores retrieves and modifies data in the database on request.

Study of different techniques of design, development and maintenance of the database

Types of DBMS
These types are based upon their management of database s…

Answers for SQL Functions

1. SQL> SELECT empno, ename FROM emp WHERE Length(ename) = 4;

2. SQL> SELECT empno, ename, job FROM emp where Length(job)=7;

3. SQL> SELECT Length('qspiders') - Length(replace('qspiders','s','')) FROM dual;

4. SQL>  SELECT empno, ename, job FROM emp WHERE Instr(job,'MAN') >0;

5. SQL> SELECT empno, ename, job FROM emp WHERE Instr(job, 'MAN') =1;

6. SQL> SELECT empno, ename, job FROM emp WHERE (Length(ename) - Length(Replace(ename, 'L',''))) = 1;

7. SQL> SELECT * FROM dept WHERE Instr(dname,'O') > 0;

8. SQL> SELECT Concat(ename,' working as a ') || Concat(job, ' earns ') || Concat(sal, '  in ') || Conc
at('dept ',deptno) AS text from emp;

OR

SQL> SELECT Concat(Concat(Concat(Concat(Concat(Concat(Concat(ename,' working as a '), job),' earns '), sal),'  in '),'dept '), deptno) AS text FROM emp;

9. SQL> SELECT empno, ename…