need study partner for pl/sql

Adding more to the differences between where and having, with respect to performance, where clause is better and fast. By Using having we calculate avg/max/min/whtever function even for unwanted records thus increasing time and decreasing performance, while where clause only performs for those specific records of interest!

Also, it is more logical to use having after group by, as first the group by clause performs the grouping and then the condition is applied to those groups! Hope this helps!

Thanks

 

So, here are few questions related to Sub Queries etc

1.What is the purpose of a sub query?
2. How is correlated sub query different from a regular sub query?
3.How do you compare when the inner/sub query returns a null value?
4.What are sub select statements?
5.How can you handle multiple row sub queries?

We should also start writing queries, this is more theoeretical so far! The next day topic are really important and must and should knowledge of INDEXES!
I may be not be covering all the questions, please do ask questions incase you come across!

Thanks
Anu

 

Here are the answers to Sub Queries:

1.What is the purpose of a sub query?
A Sub Query is practically very useful when we need to select rows from a table with a condition that on the data in the table itself. It solves the problem by combining two queries, placing one inside the other one. while the inner query returns a value that is used by the outer query to execute! While this is not true always and can be different in cases such a correlated subquery.

2. How is correlated sub query different from a regular sub query?
In a correlated sub query, the condition on the outer select stmt participates as a condition for the inner select stmt. So, here first the outer query is executed, passes the qualified column value to the inner where clause, then the inner query is executed and the result is passed to the outer query where clause and then it is executed.

ex: select dname,deptno from dept d where exists (select * from emp e where d.deptno=e.deptno)

3.How do you compare when the inner/sub query returns a null value?
If one of the value returned by the sub/inner query is null then the entire query returns no rows as null cannot be compared. So, we cannot use IN or NOT IN operators!

I am not sure how to solve this situation, if anybody is aware of a solution, please do let us all know


4.What are sub select statements?
A select statement declared as part of the select list is called a sub select stmt.

ex: select empno, (select count(empno) from emp) "total employees" from emp;

5.How can you handle multiple row sub queries?
BY using operators such as IN, ANY,ALL we can compare multiple values returned by the sub query!

 

Questions from INDEXES, PARTITIONS, HINTS, COMMENTS

1. Why is an index used?
2. What is the difference between clustered and non clustered index?
3. What are the various types of indexed used?
4. Why do we use partitions?
5. What are the types of partitions?
6. How can you create indexes on partitions?
7. What are hints? Name a few frequently used hints?

From tomorrow, the target is to get started with solving queries, I would aim atleast 25-30! So, I was thinking of considering the base EMP, DEPT and salgrade from the scott scheme as the generalised one so that we can all work on these tables and data ! If you have any ideas/suggestions, please let the group know, so we can consider it and make changes!

Thanks
8. What are comments and how are the created?

 

Hello all, Things r getting delayed, I was busy for the last few days, so couldn't post, but I was expecting some questions and answers from the study group and suprised to see none! I hope the journey so far is good for everyone. WE should b done with SQl by now and should be geared up with basics to start "THE PL/SQL".
However, let me type in the questions as per our plan and will answer them as soon as I can....

Todays questions for OLAP functions

1.what is the difference between roll up and cube?
2.what is a decode and grouping function?
3.explain case expressions?
4.explain grouping_id?
5.explain the categories of analytic functions?
6.explain lead and lag functions
7.Explain about querying top n analysis.

 

I know we are still lagging behind to complete the SQL successfully, but we have to do more and more in order to complete the PL/SQL and keep up with the time

The following is the schedule for PL/SQL

Day 1L/SQL basics
Day 2: PL/SQL with control structures, loops, if then else,case
Day 3: Composite Datatype, Pl/SQL records, Pl/SQL table
Day 4: Cursors- implicit, explicit, where current of a clause, for update, cursor variables
Day 5: Exception handling
Day 6: sub programming-Procedures
Day 7: Packages
Day 8: Triggers
Day 9: Misc

 

Hi KKusuma, I sent you the material, and maheswari, if you can send me your email address, then I will forward you the material. Today is the wrapping up of SQL topics including the MISC and giving a start to PL/SQL, so atleast 3-4 hrs of study time! I wil post the previous solutions soon and then we can discuss abt DDL,DML,DCL,TCL & Views, M.views and Misc topics!
Misc topics would include: set operators, locks,pseudo columns and object oriented concepts in Oracle