CIST 1220: SQL – Assign #12 (40 Points) [More SubQueries – Chap 8] (10 Points) P
ID: 3741088 • Letter: C
Question
CIST 1220: SQL – Assign #12
(40 Points)
[More SubQueries – Chap 8]
(10 Points) Please describe just how a “Correlated Subquery “ actual works. How does the inner query interact with the outer query? Just how many times does the inner query actually execute, etc.
(4 Points) Explain how ‘EXISTS’ works with Subqueries.
(4 Points) Which is better, joins or Subqueries? Maybe give some advantages and disadvantages of each…
(4 Points) Please explain how ‘any’ and ‘all’ maybe used with subqueries.
(4 Points) Can Subqueries be used in Inserts, Deletes and/or Updates? Please explain.
Explanation / Answer
Answer is as follows :
a) Correlated /subquery is a type of subquery that uses the value from outer query because the subquery may be evalueted once for each row provided by outer query i.e. inefficient.
For learn the process of such subqueries, let us suppose following query :
Where Outer query is :
and the inner query or correlated query is
In the above nested query the inner query has to be re-executed for each employee.
Correlated subqueries may appear elsewhere besides the WHERE clause; for example, this query uses a correlated subquery in the SELECT clause to print the entire list of employees with the average salary for each employee's department. Again, because the subquery is correlated with a column of the outer query, it must be re-executed for each row of the result.
b) EXIST with Subqueries
EXIST clause is used to test for the existance of any records in subquery. It returns TRUe if subquery returns more than one query. It is mostly used with correlated queries.
Syntax for using EXIST in subquery :
SELECT column-names
FROM table-name
WHERE EXISTS
(SELECT column-name
FROM table-name
WHERE condition)
c) Better Joins or Subqueries :
From both Joins and Subqueries , the JOINS are better to use than subqueries.
Advantages and Disadvantage of Subquery :
Advantages and Disadvantage of Joins :
d) ANY and ALL with Subqueries :
ANY operator is used for atleast one value in the table. There can be more than one value.
ALL operator is used for all the values present in the table.
When any is used with <, >, or = with a subquery, it returns results when any value retrieved in the subquery matches the value in the where or having clause of the outer statement.
When all is used with < or > in a subquery, it returns results when all values retrieved in the subquery match the value in the where or having clause of the outer statement.
Syntax to used is :
e) Insert, Delete and Updation with Subquery
Yes , one can use the Subqueries to Insert , Update and Deletee the records.
You can use a subquery in a select, insert, delete or update statement to perform the following tasks:
Syntax for Insert :
Syntax for Update :
Syntax for Delete :
if there is any query or you want more explanation,please ask in comments...