Oracle SQL Part 3

Oracle Database, SQL Day 3 Trainers: Gurpreet Singh

Oracle Database, SQL2 Day 3 Agenda

Oracle Database, SQL3 Joins An SQL JOIN clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-table) or more tables by using values common to each. Let’s say we have two sets of data in our relational database: table A and table B, with some sort of relation specified by primary and foreign keys. The result of joining these tables together can be visually represented by the following diagram:

Oracle Database, SQL4 Joins The extent of the overlap, if any, is determined by how many records in Table A match the records in Table B. Depending on what subset of data we would like to select from the two tables, the four join types can be visualized by highlighting the corresponding sections of the Venn diagram:

Oracle Database, SQL5 Joins The extent of the overlap, if any, is determined by how many records in Table A match the records in Table B. Depending on what subset of data we would like to select from the two tables, the four join types can be visualized by highlighting the corresponding sections of the Venn diagram:

Oracle Database, SQL6 JoinsImagine you’re running a store and would like to record information about your customers and their orders. By using a relational database, you can save this information as two tables that represent two distinct entities: customers and orders. CUSTOMERS ORDERS

Oracle Database, SQL7 Joins Note that the orders table contains two keys: one for the order and one for the customer who placed that order. In scenarios when there are multiple keys in a table, the key that refers to the entity being described in that table is called the Primary Key (PK) and other key is called a Foreign Key (FK). In our example, order_id is a primary key in the orders table, while customer_id is both a primary key in the customers table, and a foreign key in the orders table. Primary and foreign keys are essential to describing relations between the tables, and in performing SQL joins.

Oracle Database, SQL12 Sub Queries A sub query is a SQL query nested inside a larger query. A sub query may occur in : - A SELECT clause - A FROM clause - A WHERE clause The sub query can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another sub query. A sub query is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple- row operator, such as IN, ANY, or ALL. A sub query is also called an inner query or inner select, while the statement containing a sub query is also called an outer query or outer select. The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query. You can use a sub query in a SELECT, INSERT, DELETE, or UPDATE statement to perform the following tasks:  Compare an expression to the result of the query.  Determine if an expression is included in the results of the query.  Check whether the query selects any rows.

Oracle Database, SQL13 Sub Queries • The sub query (inner query) executes once before the main query (outer query) executes. • The main query (outer query) use the sub query result.

Oracle Database, SQL14 Sub Queries There are some guidelines to consider when using sub queries :  A sub query must be enclosed in parentheses.  A sub query must be placed on the right side of the comparison operator.  Sub queries cannot manipulate their results internally, therefore ORDER BY clause cannot be added into a sub query. You can use an ORDER BY clause in the main SELECT statement (outer query) which will be the last clause.  Use single-row operators with single-row sub queries.  If a sub query (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.

Oracle Database, SQL15 Sub Queries Types of Sub Queries:  Single row sub query : Returns zero or one row.  Multiple row sub query : Returns one or more rows.  Multiple column sub queries : Returns one or more columns.  Correlated sub queries : Reference one or more columns in the outer SQL statement. The sub query is known as a correlated sub query because the sub query is related to the outer SQL statement.  Nested sub queries : Sub queries are placed within another sub query.

Oracle Database, SQL16 Sub Queries A single row sub query returns zero or one row to the outer SQL statement. You can place a sub query in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement.

Oracle Database, SQL17 Sub Queries Multiple row sub query returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a sub query that returns multiple rows. You can use the ANY operator to compare a value with any value in a list. You must place an =, <>, >, <, <= or >= operator before ANY in your query. The following example uses ANY to check if any of the agent who belongs to the country 'UK'.

Oracle Database, SQL18 Sub Queries Multiple column sub query returns more than one columns to the outer SQL statement.

Oracle Database, SQL19 Sub Queries SQL Correlated Sub queries are used to select data from a table referenced in the outer query. The sub query is known as a correlated because the sub query is related to the outer query. In this type of queries, a table alias (also called a correlation name) must be used to specify which table reference is to be used. The alias is the pet name of a table which is brought about by putting directly after the table name in the FROM clause. This is suitable when anybody wants to obtain information from two separate tables.

Oracle Database, SQL20 Sub Queries A sub query can be nested inside other sub queries. SQL has an ability to nest queries within one another. A sub query is a SELECT statement that is nested within another SELECT statement and which return intermediate results. SQL executes innermost sub query first, then next level.

CONTACTS 21 Gurpreet Singh Senior Software Engineer CUG. 2142 M. +91-9803723925 Gurpreet.singh2@soprasteria.com Oracle Database, SQL

Oracle SQL Part 3

More Related Content

What's hot

Similar to Oracle SQL Part 3

More from Gurpreet singh

Recently uploaded

Oracle SQL Part 3