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. Wri
Mithun Ashok's Experience, Summary, Trainings and Knowledge Sharing on Oracle Database, Oracle Applications, Fusion Middleware, SQL, PL/SQL and Database Testing
Comments
The slides are not in sync with the presentation,
plz check and rectify.
vijjubs@gmail.com
For few tables in online oracle database, SELECT statement is not working or not giving the output. But, describe table_name; is working for all table.
I am unable / dont know to post the screenshot here. If possible, pls check in online database for table named "EMPLOYEE"
Any how i've copied & pasted my SQL execution below. Pls have a look:
describe employee;
Results Explain Describe Saved SQL History
Object Type TABLE Object EMPLOYEE
Table Column Data Type Length Precision Scale Primary Key Nullable Default Comment
EMPLOYEE EMPID NUMBER - 5 10 1 - - -
EMPNAME CHAR 50 - - - - - -
EMPPHNO NUMBER - 10 20 - nullable - -
EMPADDR VARCHAR2 100 - - - - - -
EMPEMAIL VARCHAR2 30 - - - nullable - -
1 - 5
Save SQL
Value RequiredName
Description
Cancel Save
& pls see this too:
select * from employee;
Results
Explain
Describe
Saved SQL
History
no data found
PLEASE EXPLAIN REGARDING THIS!
Thanks & Regards,
Sundar
Please say whether the following sql statements are true or false & also say why if it is false...
1. SELECT * From *;
(Here, tablename is *)
2. SELECT * From linesize;
(Here, tablename is linesize)
3. SELECT * From SELECTFROM;
(Here, tablename is SELECTFROM)
Thanks & Regards,
Sundar
i hv installed oracle 10g.and i can't do the edit option and as well as how to spool.
(Here, tablename is *)
False, you cannot have a table by name *.
2. SELECT * From linesize;
(Here, tablename is linesize)
True, linesize is a sql*plus keyword not sql keyword.
3. SELECT * From SELECTFROM;
(Here, tablename is SELECTFROM)
True, as there is key word in SQL as SELECTFROM.
This could be because you do not have write permissions on the default path. Use the following command to set the buffer file.
SET EDITFILE 'location and filename'
This location and file should be the path where you can create files on your OS.
To spool use,
SPOOL 'location and filename'
This location and file should be the path where you can create files on your OS.
I hope you are able to query table online and your issue is resolved.
Regards,
Mithun
1. select * from selectfrom
(Here, 'select' is a keyword & 'from' is a keyword, whereas "'selectfrom'(no space in between 'select' and 'from')" is not a keyword right?
U've said that the statement is true, but y u said 'selectfrom' is a keyword? If it is so, then the statement should be false na?
2. Online database works only partially. Sometimes giving result & sometimes not giving the required output (even for select * from table_name). So, i've stopped writing queries in online database & working with the downloaded one which is very fine!
Had been waiting for ur answers for a long time & finally thanks for answering to all of them.
Thanks & Regards,
Sundar
1. That was a typo, I meant there is no keyword as selectfrom.
select * from selectfrom; will work.
2. SQL*Plus commands will not work on online database. About giving output sometimes and not giving output sometimes, I guess there is some network problem as Online database works absolutely fine, there are many projects and online applications which have been developed using this application and it uses the same database that you have installed on your machine. If you have noticed something that is not working then let me know I can help you solve the problem.
Regards,
Mithun
"display details of all employee in the following format.
ename as scott,job as manager,sal>1000.(today's test 20th question)
Regards,Rahul.
Ans for the question is,
select ename || ' is a ' || job || ' getting salary ' || sal from emp;
Regards,
Mithun
I want to revise SQL Classes again coz I missed few classes previously.So Will you tell me when you r going to start new batch of your's coz I am interested in your's class only..
I want to revise SQL Classes again coz I missed few classes previously.So Will you tell me when you r going to start new batch of your's coz I am interested in your's class only..
sir this link downloads oracle database 10g express edition. But i haven't tried yet. Because im not so sure that this is the same tool which u r using in the class. And also my internet speed is dam slow.
Do not download 10g Express edition.
Mithun
These slides are not for download. Its only for reference.
Projection is only selecting columns. But SELECT is a clause used to both projection and selection (selecting rows)
this is sindhu
I could not create file but while
executing statement it shows file created but while editing the file will not open .