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.
-
Giovanni.docx
33 minutes ago
Giovanni Ortiz
RE: Week 8 Discussion Top of Form
Subqueries are Select queries within an INSERT, MERGE, UPDATE, or DELETE statement. The benefits of having subqueries is that it makes logical sense to read and thus makes it easier for users to understand what a query is doing. The downside to implementing subqueries in your DML’s is that they take longer to execute because of the way the database optimizer works. Although a join is more efficient to use than a subquery there are specific instances when to use a subquery. The first instance we would use a subquery over a Join is when there is more than one query to return and each subquery provides a subset of the table involved in the query. Another instance where subqueries are used over queries is if you specifically have to use the NOT EXISTS or EXISTS condition which is native to only subqueries. A general rule of thumb when guessing the performance impact of running subqueries is finding out if it is an independent or dependent subquery.
Resources:
1. Bellamkonda, S., Ahmed, R., Witkowski, A., Amor, A., Zait, M., & Lin, C. C. (2009). Enhanced subquery optimizations in oracle. Proceedings of the VLDB Endowment, 2(2), 1366-1377.
2. Zuzarte, C., Pirahesh, H., Ma, W., Cheng, Q., Liu, L., & Wong, K. (2003, June). Winmagic: Subquery elimination using window aggregation. In Proceedings of the 2003 ACM SIGMOD international conference on Management of data (pp. 652-656).
USEFUL NOTES FOR:
Describe the importance of using sub queries in a database system. Provide at least two business case scenarios to support your response.
Introduction
A subquery is a SQL query within a SELECT statement. It can be used to determine whether data exists in a column or to find the exact data to be manipulated by other statements, including UPDATE and DELETE statements. The subquery can also be nested inside another subquery.
A subquery is a SQL query within another SQL query.
Oops! Click Regenerate Content below to try generating this section again.
These queries are used to determine whether data exists in a column, or to find the exact data to be manipulated by other statements, including UPDATE and DELETE statements
Oops! Click Regenerate Content below to try generating this section again.
The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery
Oops! Click Regenerate Content below to try generating this section again.
Subqueries are used in the WHERE clause of each of these statements
Oops! Click Regenerate Content below to try generating this section again.
Subqueries can return individual values or a list of records;
Oops! Click Regenerate Content below to try generating this section again.
The general format for subqueries that return a single value is “operator (SELECT expression FROM table WHERE conditions)” where you compare the result of the subquery with the expression using one of several comparison operators.
Oops! Click Regenerate Content below to try generating this section again.
The general format for subqueries that return multiple values is “(SELECT column1, column2, … FROM table1 WHERE [conditions]) [operator] (SELECT expression1, expression2, … FROM table2 WHERE [conditions])
Oops! Click Regenerate Content below to try generating this section again.
With this you can use Subquerries to make sure that your database’s integrity is maintained.
Oops! Click Regenerate Content below to try generating this section again.
Conclusion
To recap, subqueries are extremely useful because they provide an additional level of security against SQL injection attacks. They can also be used to return multiple values as well as single values, which gives you more flexibility when designing your database systems. A subquery can be nested inside a SELECT statement or another subquery, but the syntax must follow this format: “operator (SELECT expression FROM table WHERE conditions)” where you compare the result of the subquery with the expression using one of several comparison operators.