Online Courses
Free Tutorials  Go to Your University  Placement Preparation 
0 like 0 dislike
627 views

Goeduhub's Top Online Courses @Udemy

For Indian Students- INR 360/- || For International Students- $9.99/-

S.No.

Course Name

 Coupon

1.

Tensorflow 2 & Keras:Deep Learning & Artificial Intelligence

Apply Coupon

2.

Natural Language Processing-NLP with Deep Learning in Python Apply Coupon

3.

Computer Vision OpenCV Python | YOLO| Deep Learning in Colab Apply Coupon
    More Courses

1 Answer

0 like 0 dislike
by Goeduhub's Expert (7.6k points)
edited by
 
Best answer

Write SQL queries for sub queries, nested queries

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A 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 can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

There are a few rules that subqueries must follow 

  • Subqueries must be enclosed within parentheses.
  • A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
  • An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
  • Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
  • The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
  • A subquery cannot be immediately enclosed in a set function.
  • The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.

Subqueries with the Select Statement

Syntax : SELECT column_name   FROM table_name  
WHERE column_name expression operator  
 ( SELECT column_name  from table_name WHERE ... )
  

 Table STUDENT-

student table

Example 

select *from student where class in(select class from student where class="XII");

Output

result set of student subquery

Subqueries with the INSERT Statement

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date or number functions. 

Syntax : INSERT INTO table_name (column1, column2, column3....)   

SELECT *  FROM table_name  WHERE VALUE OPERATOR  

Example 

insert into student select*from student where id in (select id from student);

 Output

output of query

Subqueries with the UPDATE Statement

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement. 

Syntax : UPDATE table  SET column_name = new_value WHERE 

VALUE OPERATOR   (SELECT COLUMN_NAME  FROM TABLE_NAME  

 WHERE condition);  

Consider the student and college table... i have joined both tables

 Example 

UPDATE student  SET marks=marks*0.25  WHERE id IN (SELECT id FROM college WHERE id >= 104); 

Output

Subqueries with the DELETE Statement

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

Syntax : DELETE FROM TABLE_NAME  WHERE VALUE OPERATOR 

 (SELECT COLUMN_NAME  

FROM TABLE_NAME  WHERE condition);

 Example 

table img

delete from college where clg_id in(select clg_id from student where clg_id >= 105);  

Output

output



For more Uttrakhand Technical University(UTU) CSE-IV Sem Database Management System Lab Experiments Click here


3.3k questions

7.1k answers

394 comments

4.6k users

Related questions

 Goeduhub:

About Us | Contact Us || Terms & Conditions | Privacy Policy || Youtube Channel || Telegram Channel © goeduhub.com Social::   |  | 
...