An inline view replaces a table in the from clause of your query. In this query, "select * from bricks" is an inline view
#Oracle
#Inline_views

You use inline views to calculate an intermediate result set. For example, you can count the number of bricks you have of each colour using this particular code.
#Oracle
#Inline_views_2

You can then join the result of this to the colours table. This allows you to find out which colours you have fewer bricks of than the minimum needed defined in colours.
#Oracle
#Inline_views_3

Nested subqueries go in your where clause. The query filters rows in the parent tables. For example, to find all the rows in colours where you have a matching brick, you could write the following.
#Oracle
#Nested_Subqueries

You can also use exists in a nested subquery. The following is the same as the previous query
#Oracle
#Nested_Subqueries_2

You can filter rows in a subquery too. To find all the colours that have at least one brick with a brick_id less than 5,
#Oracle
#Oracle_Subqueries_3

This leads to a difference between IN and EXISTS returns rows from the parent query, as long as the subquery finds at least one row. So the following uncorrelated EXISTS returns all the rows in colours
#Oracle
#Correleted_vs_Uncorrelated

You can do the reverse of IN & EXISTS by placing NOT in front of them. This returns you all the rows from the parent which don't have a match in the subquery.

For example to find the rows in colours without colour in bricks, you can use NOT EXISTS.
#Oracle
#Not_In vs #Not_Exists

But do the same with NOT IN
#Oracle
#Not_In vs #Not_Exists

#Oracle
#Not_in vs #Not_Exits
#Part_3

For remember NOT IN condition to be true, comparing all its elements to the parent table must return false.

But remember that comparing anything to null gives unknown ! So the whole expression is unknown and you get no data.

To resolve this, either use NOT EXISTS or add a where clause to stop the subquery returning null values
#Oracle
#Not_In vs #Not_Exists

Scalar subqueries return one column and at most one row. You can replace a column with a scalar subquery in most cases.

For example, to return a count of the number of bricks matching each colour, you could do the following
#Oracle
#Scalar_Subqueries

Note the colours with no matching bricks return null. To show zero instead, you can use NVL or coalesce. This needs to go around the whole subquery.
#Oracle
#Scalar_Subqueries_2

For remember NOT IN condition to be true, comparing all its elements to the parent table must return false.

But remember that comparing anything to null gives unknown ! So the whole expression is unknown and you get no data.

To resolve this, either use NOT EXISTS or add a where clause to stop the subquery returning null values
#Oracle
#Not_In vs #Not_Exists

You also need to join bricks to colours in the subquery. If you don't, it will return four rows (one for each different value for colour in bricks). This leads to an ORA-01427 error
#Oracle
#Scalar_Subqueries_3

Usually you will correlate a scalar subquery with a parent table to give the correct answer.

You can also use scalar subqueries in your having clause. So instead of a join, you could write the query in part 1 to find those bricks you have less than the minimum needed like so
#Oracle
#Scalar_Subqueries_4

Common table expressions (CTEs) enable you to name subqueries. You then refer to these like normal tables elsewhere in your query. This can make your SQL to write and understand later.

CTEs go in the with clause above the select statement. The following defines a CTE that counts how many rows of each colour there are in the bricks table
#Oracle
#Common_Table_Expressions

Because you access CTEs in the same way as a regular table, you can use it many times in your query. This can help if you want to use this subquery many times. For example if you want to find:

Which colours you have more bricks of than the minimum needed
The average number of bricks you have of each colour

You need to group the bricks by colour. Then filter the colours table where this count is greater than the minimum_bricks_needed for that colour. And compute the mean of the counts.

You can do the filtering with a nested subquery. And show the average in a scalar subquery. This looks like
#Oracle
#Reusable_Subqueries

Note that "group by colour" appears twice in the statement. This creates maintenance problems. If you need to change this, say to join bricks to another table, you have to do this in two places.

Using CTEs, you can do the group by once. Then refer to it in your select
#Oracle
#Reusable_Subqueries_2