UrbanPro
true

Find the best tutors and institutes for Oracle Training

Find Best Oracle Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

Learn Oracle Training with Free Lessons & Tips

Ask a Question

Post a Lesson

All

All

Lessons

Discussion

Answered on 29 Jun IT Courses/WorkDay SaaS IT Courses/Oracle Training/Oracle Fusion HCM

Ariviga Techonoligies

SAP SuccessFactors is one of the best options. It provides total HCM solutions; it can be implemented alone or integrated with any other ERP products, including ECC and S4Hana from SAP.
Answers 3 Comments
Dislike Bookmark

Answered on 26 Mar IT Courses/Oracle Training/Oracle Database

Sandeep Tiwari

Physics Lecturer with 5 years of experience in IIT-JEE & NEET, CBSE, HSC

There are many educational websites where you enhance your skills, and the best part is UrbanPro, where you find good teachers who help you switch your job in the database.
Answers 4 Comments
Dislike Bookmark

Lesson Posted on 02/12/2020 IT Courses/Oracle Training

How to use joins in SQL

Sagar Waikar

Overall strong 17 years of IT industry experience and I have a strong experience on PeopleSoft HRMS for...

What are Joins in SQL? Joins in SQL are used to combine rows between two or more tables. Joins are used in SQL when user need to extract data between two or more tables based on a related column. Joins returns the data between the tables based on the type of join used. There are different types... read more

What are Joins in SQL?

  • Joins in SQL are used to combine rows between two or more tables.
  • Joins are used in SQL when user need to extract data between two or more tables based on a related column.
  • Joins returns the data between the tables based on the type of join used.
  • There are different types of joins available in SQL

What are the types of joins?

  • Inner Join or Simple Join
  • Left Join
  • Right Join
  • Full Join

Let’s discuss Inner join or simple join in detail.

Inner Join

  • Inner join is also called as Simple Join.
  • Inner join gives matching data from both the tables based on a related column.
  • Inner Join is used by mentioning INNER JOIN keyword in the query.

How to join?

  • Use Keyword INNER JOIN
  • Mention INNER JOIN after FROM in the query.
  • Mentioning INNER keyword is not mandatory.
  • Mention ON keyword before the join condition

Syntax:

SELECT column1, column2 from table1 INNER JOIN table2 ON table1.fieldname = table2.fieldname

LEFT Join

What is left join?

  • Left join returns all the rows from the left table.
  • Left join returns the rows even if there are no matching rows available in the right table.
  • Left join returns null to the columns of the rows where there are no matches found from the right table.
  • Left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join condition.

How to use a LEFT join

  • Use Keyword LEFT
  • Mention LEFT JOIN after FROM in the query.
  • Mentioning LEFT keyword is mandatory.
  • Mention ON keyword before the join condition

Syntax:

SELECT column1, column2 From table1 LEFT JOIN table 2

ON table1.common_field = table2.common_field

RIGHT JOIN

What is Right, Join?

  • Right, join returns all the rows from the right table.
  • Right, join returns the rows even if there are no matching rows available in the left table.
  • Right, join returns null to the columns of the rows where there are no matches found from the left table.
  • Right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join condition.

How to use a RIGHT join

  • Use Keyword RIGHT
  • Mention RIGHT JOIN after FROM in the query.
  • Mentioning the RIGHT keyword is mandatory.
  • Mention ON keyword before the join condition

Syntax:

SELECT column1, column2 From table1 RIGHT JOIN table 2

ON table1.common_field = table2.common_field

FULL JOIN

What is Full Join?

  • Full join returns all the rows from the right and left the table.
  • Full join returns the nulls if there is no matching in the left or right table.
  • FULL JOIN can potentially return very large result-sets!

How to Join?

  • Use Keyword FULL
  • Mention FULL OUTER JOIN after FROM in the query.
  • Mentioning FULL OUTER keyword is mandatory.
  • Mention ON keyword before the join condition

Syntax:

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name

WHERE condition;

read less
Comments
Dislike Bookmark

Looking for Oracle Training

Find best Oracle Training in your locality on UrbanPro.

FIND NOW

Answered on 21/03/2020 IT Courses/Oracle Training

Sakshi Saxena

IT Professional Trainer with 7 years of experience in Teaching

Oracle is a for backend support.
Answers 18 Comments
Dislike Bookmark

Lesson Posted on 05/01/2020 IT Courses/SQL Programming IT Courses/Oracle Training IT Courses/PL/SQL +2 IT Courses/Oracle Training/Oracle PL/SQL IT Courses/Database Training less

Interview questions based on "level", a pseudocolumn

Gavi Yatnalli

It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.

1. Write a query to get the below output, 1 11 21 31 .. .. 91 2 12 22 32 .. .. 92 3 13 23 33 .. .. 93 .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. 10 20 30 40 100 Ans: select level,level+10,level+20,... read more

1. Write a query to get the below output,

1 11 21 31 .. .. 91
2 12 22 32 .. .. 92
3 13 23 33 .. .. 93
            ..
.. .. .. .. .. .. ..
.. .. .. .. .. .. ..
10 20 30 40     100

Ans:

select level,level+10,level+20,
         level+30,level+40,level+50,
         level+60,level+70,level+80,
         level+90
     from dual connect by level <=10;

 

2. Write a query to generate tables from 7 to 70.

Like,  7,14,21....8,16,24...9,18,27......70,140,210.

Ans:

Select a.a*b.b from
               (select level+6 a from dual connect by level <=64) a,
               (select level b from dual connect by level <=10)     b;

And there are many more.............

read less
Comments
Dislike Bookmark

Lesson Posted on 05/01/2020 IT Courses/PL/SQL IT Courses/Oracle Training/Oracle PL/SQL IT Courses/SQL Programming +2 IT Courses/Oracle Training IT Courses/Database Training less

How does a SQL Query Execute inside Oracle? What are the steps followed in the background?

Gavi Yatnalli

It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.

The above snapshot says it all. Hence it is said, 'Picture says it all'. Basically, there are 4-5 steps. There are two possible ways of query being executed. 1. Soft Parse- Improves performance. This is also called as 'Library Cache Hit'. 2. Hard Parse- Degrades performance. This is... read more

 

The above snapshot says it all. Hence it is said, 'Picture says it all'.

Basically, there are 4-5 steps. There are two possible ways of query being executed.
      1. Soft Parse-  Improves performance. This is also called as 'Library Cache Hit'.
      2. Hard Parse- Degrades performance. This is also called as 'Library Cache Miss'.

Let us understand the steps one by one briefly.
There is a parser inside Oracle, which parses the SQL Query for further processing.
The first 3 steps of SQL Query Execution are taken care of by the parser.

Step 1: Syntax check

       This checks only for syntax errors. A pure syntax check and nothing else.
Examples: 1. Are the keywords correct?
                      Like, 'seelect', 'form', 'groupby', 'orderby' are few common spelling mistakes.
                 2. Does SQL Query end with a semicolon (;)?
                 3. Are column names in the 'Select' Clause are separated by commas (,)?
                 4. Does 'Select' clause include only the colmuns which are present in the 'Group by' clause?
                 5. Does 'Group by' clause appear after 'Where clause'?
                 6. Is 'Order by' clause the last clause in the query?
                 etc. etc....

            In each of these cases, if the answer is 'No', oracles throws an error stating the same.

Step 2: Sematic chck

      Once the query passes the Syntax check, next step is Semantic check.
Here, the references of all objects present in the query are checked along with user privileges. The check happens against metadata maintained in the Oracle.
Examples: 1. Is table name valid or such a table exists?
                 2. Are columns names correct?
                 3. Does user have select/update/delete/insert/create privilege?
                 etc. etc. etc.

So during Syntax check, it doesn't check for the validity of table names, column names, privileges etc.

Let's say, I am running this... "select * from epm;" 

This passes Syntax check, though I am writing a wrong table name. Instead of "emp", I have written "epm". But, this query is fine syntax-wise and hence passes the Syntax check. But it fails in the next step of Semantic check where the object names are verified.

But we will not be able to notice whether a query failed at Syntax check or at Semantic check when we run a query from SQL*Plus or any other GUI. Because, everything is handled in one go from user point of view and the errors are sent if the Query fails at any step. Otherwise, we get the final output.

 Step 3: Shared pool check

This is an important check. I am planning to write a separte lesson on this. Let us understand briefly here.

Basically, once the query passed both Syntax check and Semantic check, it goes for third check called 'Shared pool check'

Just to put it briefly, 'Shared pool' is a memory segment present inside every Oracle instance, which retains the recently executed SQL and PLSQL code inside its 'Library Cache' based on LRU algorithm.

So, if parser finds that the query is present inside 'Library Cache' or 'Shared pool', then it is called 'Soft Parse'. It goes to step 5, executes the query and sends the output to whoever requested it.

As simple as that. But, if parser finds that such a query is not present inside 'Shared pool' or 'Library Cache', then it is called 'Hard Parse'. So, Oracle must carry out step 4 first to prepare the query and then go to step 5 finally to execute the query and then send the output to the requestor.

So, the first three steps are always carried out for every SQL Query and they are less expensive.

Step 4:

      The step 4 is very expensive or costly. Meaning, it consumes lot of resources to complete this, like CPU, I/O reads, physical reads, extra algorithms to generate mutliple queries and select the best one etc.

Hence, 'Hard Parse' degrades performance. We should keep this in mind always before writing any query. To put it simply for now, avoid hard coding and use bind variables to take the advantage of 'Soft Parse'.

Step 4 happens only in case of 'Hard parse', which is decided in step 3 as we discussed above.

We can split this into two sub-steps.
      a. Optimization: The Optimizer generates mutliple plans using various combinations of joins                      (Nested loop/Sort Merge/Hash), access paths (full table scan/index range scan/index skip scan                etc) and join orders.
          Finally selects the best plan based on cost. The lower the cost, the better the performance.

      b. Row source generation: Query execution plan is generated for the best query
          selected in the above step. This is what we see in Explain plan.
          We can further make modifications to the query, force the Optimizer to select an even lower cost            query by changing access paths, joins, join orders or using Hints. And we can verify if the                  execution plan got changed by again looking at its Explain plan.
         This is called 'Performance tuning' or 'Query Tuning'. Let's not go deep into it now.

None of this happens in case of 'Soft Parse' and hence improves performance.

Step 5: Query Execution

      Step 5 is again common for each query irresepctive of whether it is 'Soft Parse' or 'Hard Parse'. 

As we already discussed, it executes the SQL Query and sends the output
to the requested program or user.

 

So this is about it. To wrap up, there are two ways of execution or parsing namely- 'Hard parse' and 'Soft Parse'. And there are 5 steps totally. Steps 1 to 3 are common for every query or for each type of execution/parsing. Step 4 happens only for 'Hard Parse' way of execution and not for 'Soft Parse' way. Step 5 is the final and common step, which finally executes the SQL Query. Use Explain plan to check the execution plan selected by Optimizer and tune the query. Use Bind variables to enable 'Soft Parse'.

read less
Comments
Dislike Bookmark

Lesson Posted on 11/06/2019 IT Courses/Oracle Training/Oracle GoldenGate

GoldenGate Connectivity to Kafka

Ankit Goyal

Have around 11 years of I.T. industry experience. Oracle GoldenGate Implementation certified professional....

Oracle GoldenGate (OGG) connects to Apache/Confluent kafka using GoldenGate for Big Data Adapter. OGG uses the big data for kafka properties file to connect to kafka servers/cluster and stream the data in real time. All Inserts,Updates, Deletes are streamed as Inserts in Kafka Consumer. Big Data adapter... read more

Oracle GoldenGate (OGG) connects to Apache/Confluent kafka using GoldenGate for Big Data Adapter. OGG uses the big data for kafka properties file to connect to kafka servers/cluster and stream the data in real time.

All Inserts,Updates, Deletes are streamed as Inserts in Kafka Consumer. Big Data adapter is powerful enough to stream before and after images in case of an update.

You can download the OGG for big data software from Oracle e-delivery website. for more details on Big data connectors, do reach out to me.

Thanks,

Ankit

read less
Comments
Dislike Bookmark

Lesson Posted on 29/12/2016 IT Courses/Oracle Training/Oracle Developer IT Courses/Oracle Training/Oracle PL/SQL IT Courses/Oracle Training/Oracle Developer/Oracle Developer OCA +1 IT Courses/Oracle Training less

SQL - Structured Query Language - DQL Commands - SELECT

Kasyap Kasyap

My Experience : 10+ years experience on Oracle Applications / E-Business Suite Implementation Projects 6+...

Structured Query Language allows to store , manipulate, control and retrieve the information from Relational Database Management Systems. Following are some RDBMS products. Oracle Databse, Microsoft SQL Server, MySQL, PostgreSQL etc. SQL commands can be categorized under DDL - Data Definition Language... read more

Structured Query Language allows to store , manipulate, control and retrieve the information from Relational Database Management Systems. Following are some RDBMS products. Oracle Databse, Microsoft SQL Server, MySQL, PostgreSQL etc.

SQL commands can be categorized under

  1. DDL - Data Definition Language      (CREATE, DROP, ALTER)
  2. DML- Data Manipulation Language   (INSERT, DELETE, UPDATE)
  3. DCL- Data Control Language            (GRANT, REVOKE)
  4. DQL- Data Query Language              (SELECT)

In this session we will focus on DQL SELECT Statement

SELECT T2. DepartmentName, T1. StudentName, SUM(MARKS)

FROM Students T1, Departments T2, MARKS T3

WHERE T1.DEPTCODE=T2.DEPTCODE  

AND T1.STUDENTID=T3.STUDENTID

AND T3.EXAMNAME='SEMESTER 1'

GROUP BY  T2. DepartmentName,T1. StudentName

HAVING SUM(MARKS)> 900

ORDER BY T1.StudentName

 This is old fashioned way of writing clean SQL statements, New way of writing statements to achieve the same result will be discussed in the actual course.

SELECT Clause: Here one has to list the columns in a table(s) to display as a result of the query. columns can be from the list of tables mentioned in FROM clause. A from clause can have a sub query instead of a physical table or view. we will cover this in detail, don't get lost. We can also use Oracle functions or custom functions to return single value return functions.

FROM Clause: This is to include the tables and optionally subqueries (logical table with in memory)

WHERE Clauase: This is to filter the results of the entire select statement. you can exclude or include certain criteria. Apart from this you also join other tables with appropriate unique column to match appropriate record/row with appropriate record/ row in other table.

GROUP BY Clause: This is to group set of columns to use aggregate functions. example Average sales by city or state etc.

HAVING Clause: This is to filter the group results, unlike where cluase it is applied at the group result. example Average Sales > 10000.

ORDER BY Clause: This is to sort the results using specific selected list of the columns in SELECT clause.

read less
Comments 1
Dislike Bookmark

Looking for Oracle Training

Find best Oracle Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 24/12/2016 IT Courses/Database Training IT Courses/Oracle Training/Oracle Database IT Courses/PL/SQL +1 IT Courses/Oracle Training/Oracle PL/SQL less

Interview questions on Packages in Oracle

Gavi Yatnalli

It's going to be a multidimensional experience when you attend the classes. Take a chance and enjoy the ride.

Let me list few standard questions asked in the interviews on plsql package. 1. Why packages are used? What are it's advantages? 2. Name few bulin-in Oracle packages. 3. Can we have a function or procedure declared in Package specification and not defined in package body and why? 4. Can we have a... read more

Let me list few standard questions asked in the interviews on plsql package.

1. Why packages are used? What are it's advantages?

2. Name few bulin-in Oracle packages.

3. Can we have a function or procedure declared in Package specification and not defined in package body and why?

4. Can we have a function or procedure not declared in Package Specs and defined in Package body alone?

5. What are private components in Plsql packages?

6. Can we have only a Package specification and no body at all? If yes, under what circumstances? Such a Package specification would be compiled without errors?

7. Can we have Package body alone and no Specs? Will it be compiled without errors?

8. What is overloading and how is it accomplished in Oracle?

9. What are restrictions on overloading?

10. What are differences between and advantages/disadvantages of stand alone and packaged procedures/Functions?

11. What is forward declaration?

12. What are the disadvantages of Pacakges?

13. What is session varibale and how is it defined in package?

14. How Packages improve or degrade performance?

15. What is pragma serially_reusable and why is it used?

These questions actually cover the entire lesson on Plsql Packages.

 

 

 

read less
Comments 1
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best Oracle Training in India. Post Your Requirement today and get connected.

Overview

Questions 980

Lessons 39

Total Shares  

+ Follow 28,592 Followers

Related Topics

Top Contributors

Connect with Expert Tutors & Institutes for Oracle Training

x

Ask a Question

Please enter your Question

Please select a Tag

X

Looking for Oracle Training Classes?

Find best tutors for Oracle Training Classes by posting a requirement.

  • Post a learning requirement
  • Get customized responses
  • Compare and select the best

Looking for Oracle Training Classes?

Find best Oracle Training Classes in your locality on UrbanPro

Post your learning requirement

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more