This page was exported from EDUGRABS [ http://www.edugrabs.com ] Export date:Fri Apr 27 0:59:06 2018 / +0000 GMT ___________________________________________________ Title: Query and Subquery in SQL --------------------------------------------------- Query and Subquery in SQL SQL Queries : A query is an inquiry into the database using the select command. Consider the following relations Employee Eno Name Age Salary 1000 Ankit 24 30000 1001 Pooja 24 20000 1002 Komal 19 10000 1003 Surender 49 40000 Work_IN Eno Dno 1000 10 1001 10 1002 11 1003 10 Department Dno Dname City 10 Landline Bhiwani 11 Phone Rohtak Example of SQL Queries : Query 1 : Get full details of all employees. Solution : SELECT * FROM Employee; Query 2 : Get the age of all the employees. Solution : SELECT Age FROM Employee; Query 3 : Get the age of all the employees with no duplicates. Solution : SELECT DISTINCT Age FROM Employee; Query 4 : Get the employee number and name of the employees whose Salary is greater than 25000. Solution : SELECT Eno, Name FROM Employee WHERE Salary>25000; Division of SQL Queries : Based on the filtering Activity, the SQL queries are divided into 5 groups : Queries that uses basic search operations. Queries that uses range operations. (Using BETWEEN & NOT BETWEEN operator) Queries that uses composition operations.  (Using AND & OR operator) Queries that uses pattern matching.  (Using %, _, and /(escape symbol)) Query that uses null and not null. Sub-Queries or Nested Queries : A subquery is a query within another query i. e. A query embedded in another query is called a subquery. It is also known as nested query. Subquery is used to return data that will be used in the main query as a condition, to further restrict the data to be retrieved. Subqueries are of 2 types : Independent Nested Query : Inner query doesn't depend on outer query. Co-Related Nested Query : Inner query uses attributes of outer query, hence dependent on outer query. Examples of SubQueries : Subquery 1: Find names of employees who work in Department number 11. Solution : SELECT Name FROM Employee WHERE Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN WHERE Dno =11); Subquery 2: Find name of employee whose basic pay is Greater then all basic pay of the employees working in Department number 10. Solution : SELECT Name FROM Employee WHERE Salary > ALL(SELECT Salary FROM Employee WHERE Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN WHERE Dno = 10)) Subquery 3 : Find the names of employees whose basic pay is greater than the average basic pay. Solution : SELECT Name From Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee); Characteristics of subquery: The inquiry will run first and substitute its results in outer query. The variable from outer query can be used in the inner query but reverse is not true. The subquery can be used in where and having clauses and sometimes in from clause also. Classification of sub query Uncorelated subquery : Inner query will run always only once. If it is independent of outer query then it is called uncorrelated subquery. For Example : SELECT Name FROM Employee WHERE Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN); Corelated Subquery : Inner query will run some time as many times as the number of rows are available in `the outer query. If it is refer a variable in outer query then it is called correlated subquery. For Example : SELECT Name FROM Employee WHERE Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN WHERE Dno =11); Scalar subquery : It returns only one row and one column Row subquery : It returns multiple columns but only one row Table subquery :  It retrive multiple rows and multiple columns Possible ways of writing sub query The possible ways of writing sub query are : By using IN predicate : (BETWEEN, LIKE IS NULL cannot be used with the subqueries). By using quantifiers comparison predicates : (ANY, ALL) By using exist and not exist Restrictions for subquery The following operators cannot be used in between main query and subquery: BETWEEN and NOT BETWEEN operator LIKE and NOT LIKE operator IS NULL and IS NOT NULL operator The following operators are normally used between query and subquery: IN predicate : IN or NOT IN Quantified comparison predicates : ANY, ALL, SOME EXIST predicates: EXIST and NOT EXIST Previous Home Next Complete Format of SELECT Command Operators in SQL with Example --------------------------------------------------- Images: http://www.edugrabs.com/wp-content/uploads/2015/08/Types-of-Subquery.png --------------------------------------------------- --------------------------------------------------- Post date: 2015-08-24 07:07:39 Post date GMT: 2015-08-24 07:07:39 Post modified date: 2015-12-17 05:42:15 Post modified date GMT: 2015-12-17 05:42:15 ____________________________________________________________________________________________ Export of Post and Page as text file has been powered by [ Universal Post Manager ] plugin from www.gconverters.com