Week 8 Discussion – Advanced SQL and PL/SQL
47 Unread replies47 Replies
Extracting and interpreting data can be very valuable to an organization.
- Describe the importance of using sub queries in a database system. Provide at least two business case scenarios to support your response.
- Be sure to respond to at least one of your classmates’ posts.
Reply to student
Quinn McLean
2:15pm May 28 at 2:15pm
Hello Class,
A subquery is a SELECT statement within another SQL statement. The SQL statement can be SELECT, WHERE clause, FROM clause, JOIN, INSERT, UPDATE, DELETE, SET, DO, or another subquery. Subqueries form a complex query into secluded parts so that a complex query can be separated into a series of steps for easy comprehension and code maintenance. Subqueries allow you to use the outcome of another query in the outer query.
Two examples of why they are important are:
If the company wanted a list of items that are priced more than the average price. The code would be
SELECT name, listed_price
FROM items
WHERE listed_price > (
SELECT AVG(listed_price)
FROM items
); The subquery is in the where clause and it gives the results of the prices, then the price is compared to the value and the items that are priced above the average will be in the final output. Now let us say we wanted to get a list of all the customers that purchased an item. We would use a multirow subquery to output all the proper information needed. SELECT last_name, first_name
FROM customers
WHERE id IN (
SELECT customers_id
FROM sales
); Sources: https://www.tutorialspoint.com/sql/sql-sub-queries.htm#:~:text=A%20Subquery%20or%20Inner%20query,the%20data%20to%20be%20retrieved. Reply
