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