SQL Subqueries: Dependencies
- the inner subquery is completely independent from the outer query
WITH dept_average AS (SELECT dept, AVG(salary) AS avg_dept_salary FROM employee GROUP BY employee.dept ) SELECT E.eid, E.ename, D.avg_dept_salary FROM employee E JOIN dept.average D ON E.dept = D.dept WHERE E.salary > D.avg_dept_salary
- the inner subquery is dependent on a clause in the outer query
SELECT employee_id, name FROM employees_db emp WHERE salary > (SELECT AVG(salary) FROM employees_db WHERE department = emp.department );
The second concept to consider before writing any code is the dependency of your subquery to the larger query. A subquery can either be simple or correlated. In my experience, it’s better to keep subqueries simple to increase readability for other users that might leverage your code to run or adjust.
Simple Subquery: The inner subquery is completely independent of the larger query.
Correlated Subquery: The inner subquery is dependent on the larger query.
When to use Correlated Query
However, sometimes, it’s slick to include a correlated subquery, specifically when the value of the inner query is dependent on a value outputted from the main query (e.g., the filter statement constantly changes). In the example below, you’ll notice that the value of the inner query — average GPA — keeps adjusting depending on the university the student goes to. THAT is a great use case for the correlated subquery.
SELECT first_name, last_name, GPA, university FROM student_db outer_db WHERE GPA > (SELECT AVG(GPA) FROM student_db WHERE university = outer_db.university);
Views in SQL
Tables in SQL reside in the database persistently. In contrast, views are the virtual tables that are derived from one or more base tables. The term virtual means that the views do not exist physically in a database, instead, they reside in the memory (not database), just like the result of any query is stored in the memory.
The syntax for creating a view is:
CREATE VIEW <VIEW_NAME> AS SELECT … FROM … WHERE …
The query above is called a view-definition. Once created, you can query a view just like you’d query a normal table, by using its name. The tuples in a view are created as an outcome of a SQL query that selects the filtered data from one or more tables.
The larger the sample size and the more diverse your dataset is, the more confident you’ll be in your results.
Example 1 – Consider the same Parch & Posey database schema again, where the
sales_reps table contains details about sales representatives and the
region table contains the list of regions.
Suppose you are managing sales representatives who are looking after the accounts in the Northeast region only. The details of such a subset of sales representatives can be fetched from two tables, and stored as a view:
create view v1 as select S.id, S.name as Rep_Name, R.name as Region_Name from sales_reps S join region R on S.region_id = R.id and R.name = 'Northeast';
The query above will store the result as a view (virtual table) with the name “V1” that can be queried later.
Example 2 – Consider another example from Parch & Posey database schema again, where you have practiced the following query in the “Joins” lesson:
Provide the name for each region for every order, as well as the account name and the unit price they paid (total_amt_usd/total) for the order. Your final result should have 3 columns: region name, account name, and unit price.
The query would be
CREATE VIEW V2 AS SELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price FROM region r JOIN sales_reps s ON s.region_id = r.id JOIN accounts a ON a.sales_rep_id = s.id JOIN orders o ON o.account_id = a.id;
You can save the result set of the query as a view (virtual table) with the name “V2” that can be queried later.
Note – You can use any SELECT query in the CREATE VIEW query. The above two examples show a join query, whereas the next example shows a subquery used in creating a view.
Example 3 – The subquery you saw earlier, can be also stored as a view.
Show the report which channels send the most traffic per day on average to Parch and Posey.
CREATE VIEW V3 AS SELECT channel, AVG(events) AS average_events FROM (SELECT DATE_TRUNC('day',occurred_at) AS day, channel, COUNT(*) as events FROM web_events GROUP BY 1,2) sub GROUP BY channel
Now, this view can be queried for any information that it contains. For example, you can see the maximum value of
select max(average_events) from v3;
How I understand the CREATE VIEW?
It will create a table in the database, which I could query later on. It should not be on the same query.
Query I made for Example 1:
select Rep_Name, Region_Name FROM v1
For Example 2
SELECT region, unit_price FROM v2
Can we update the base tables by updating a view?
Since views do not exist physically in the database, it is may or may not be possible to execute UPDATE operations on views. It depends on the SELECT query used in the view definition. Generally, if the SELECT statement contains either an AGGREGATE function, GROUPING, or JOIN, then the view may not update the underlying base tables.
Can we insert or delete a tuple in the base table by inserting or deleting a tuple in a view?
Again, it depends on the view definition. If a view is created from a single base table, then yes, you can insert/delete tuples by doing so in the view.
Can we alter the view definition?
Most of the databases allow you to alter a view. For example, Oracle and IBM DB2 allows us to alter views and provides
CREATE OR REPLACE VIEW option to redefine a view.
Leave a Reply