PRACTICAL NO:4
AIM: sub queries, set operation, joins
THEROY
• Subqueries:- Subqueries, also known as nested queries or subselects, aim
to retrieve data from a database by using the result of one query as input
for another. Subqueries can be used in various ways, such as to filter, sort,
or aggregate data based on the results of inner queries.Subqueries are
essentially queries within queries. They can be used in the WHERE clause,
FROM clause, or as part of an expression in SQL statements. The theory
behind subqueries involves understanding how to structure and nest
queries to retrieve specific data based on a set of conditions or criteria.
• Set Operations:-Set operations in a DBMS aim to combine the results of
multiple queries, producing a single result set. The primary set operations
are UNION, INTERSECT, and EXCEPT (or MINUS, depending on the
DBMS). These operations allow you to work with data as if they were sets,
combining or comparing data from different queries.
Set operations are based on set theory principles. The UNION operation
combines the results of two or more queries, removing duplicates, while
INTERSECT returns rows that are common to both queries. EXCEPT (or
MINUS) returns rows in the first query but not in the second.
Understanding set theory and how these operations work is crucial for
effectively combining and comparing data.
• Joins:- Joins in a DBMS aim to combine data from two or more database
tables based on a related column, allowing you to retrieve information from
multiple tables in a single result set. The primary aim is to create a
comprehensive view of the data by associating related records. Joins are
based on the theory of relational databases, which emphasizes the
relationships between tables. Common types of joins include INNER JOIN
(or EQUI JOIN), LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or
RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).
Understanding the relationships between tables, how to join them, and the
differences between join types is essential for effective database querying.
Code:
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| becse |
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
| university |
+--------------------+
7 rows in set (0.01 sec)
mysql> use student
Database changed
mysql> show tables ;
+-------------------+
| Tables_in_student |
+-------------------+
| becse |
| fecse |
| secse |
| sycse |
| tecse |
+-------------------+
5 rows in set (0.01 sec)
mysql> select * from becse;
+-----+-----------------+-------+
| rno | Name | Marks |
+-----+-----------------+-------+
| 1 | Vicky | 25 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
| 6 | Pankaj Deshmukh | 67 |
| 7 | Gaurav Kumar | 84 |
| 8 | Sanket Jain | 61 |
| 9 | Sahil Wagh | 90 |
| 10 | Saurabh Singh | 54 |
| 1 | Vicky | 25 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
| 6 | Pankaj Deshmukh | 67 |
| 7 | Gaurav Kumar | 84 |
| 8 | Sanket Jain | 61 |
| 9 | Sahil Wagh | 90 |
| 10 | Saurabh Singh | 54 |
| 1 | Vicky | 25 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
| 6 | Pankaj Deshmukh | 67 |
| 7 | Gaurav Kumar | 84 |
| 8 | Sanket Jain | 61 |
| 9 | Sahil Wagh | 90 |
| 10 | Saurabh Singh | 54 |
+-----+-----------------+-------+
30 rows in set (0.01 sec)
mysql> select * from secse;
+------+------+-----------+
| rno | Name | Subject |
+------+------+-----------+
| 1 | aaa | SeminarII |
+------+------+-----------+
1 row in set (0.01 sec)
mysql> insert into secse (rno,'name','subject')vlaues(2,bbb,Seminar);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ''name','subject')vlaues(2,bbb,Seminar)' at line 1
mysql> select * from sycse;
+-----+-----------------+-------+
| rno | Name | Marks |
+-----+-----------------+-------+
| 1 | Vicky | 20 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
| 6 | Pankaj Deshmukh | 67 |
| 7 | Gaurav Kumar | 84 |
| 8 | Sanket Jain | 61 |
| 9 | Sahil Wagh | 90 |
| 10 | Saurabh Singh | 54 |
+-----+-----------------+-------+
10 rows in set (0.00 sec)
mysql> select * from fecse;
Empty set (0.00 sec)
mysql> insert into secse (rno,name,subject)vlaues(2,'bbb','Seminar');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'vlaues(2,'bbb','Seminar')' at line 1
mysql> insert into secse (rno,name,subject)vlaues(2,"bbb","Seminar");
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'vlaues(2,"bbb","Seminar")' at line 1
mysql> insert into secse (rno,name,subject)values(2,"bbb","Seminar");
Query OK, 1 row affected (0.01 sec)
mysql> select * from sycse;
+-----+-----------------+-------+
| rno | Name | Marks |
+-----+-----------------+-------+
| 1 | Vicky | 20 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
| 6 | Pankaj Deshmukh | 67 |
| 7 | Gaurav Kumar | 84 |
| 8 | Sanket Jain | 61 |
| 9 | Sahil Wagh | 90 |
| 10 | Saurabh Singh | 54 |
+-----+-----------------+-------+
10 rows in set (0.00 sec)
mysql> select * from secse;
+------+------+-----------+
| rno | Name | Subject |
+------+------+-----------+
| 1 | aaa | SeminarII |
| 2 | bbb | Seminar |
+------+------+-----------+
2 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| becse |
| fecse |
| secse |
| sycse |
| tecse |
+-------------------+
5 rows in set (0.00 sec)
mysql> select * from secse;
+------+------+-----------+
| rno | Name | Subject |
+------+------+-----------+
| 1 | aaa | SeminarII |
| 2 | bbb | Seminar |
2 rows in set (0.00 sec)
mysql> select * from tecse;
Empty set (0.00 sec)
mysql> desc tecse;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| rno | int | YES | | NULL | |
| Name | varchar(25) | YES | | NULL | |
| Marks | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> INSERT INTO student(rno, Name, marks) VALUES (5, "Rahul Khanna", 90), (6, "Pankaj Deshmukh", 60), (7,
"Gaurav Kumar", 80), (8, "Sanket Jain", 60), (9, "Sahil Wagh", 91), (10, "Saurabh Singh", 50);
ERROR 1146 (42S02): Table 'student.student' doesn't exist
mysql> INSERT INTO tecse(rno, Name, marks) VALUES (5, "Rahul Khanna", 90), (6, "Pankaj Deshmukh", 60), (7, "Gaurav
Kumar", 80), (8, "Sanket Jain", 60), (9, "Sahil Wagh", 91), (10, "Saurabh Singh", 50);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from tecse;
+------+-----------------+-------+
| rno | Name | Marks |
+------+-----------------+-------+
| 5 | Rahul Khanna | 90 |
| 6 | Pankaj Deshmukh | 60 |
| 7 | Gaurav Kumar | 80 |
| 8 | Sanket Jain | 60 |
| 9 | Sahil Wagh | 91 |
| 10 | Saurabh Singh | 50 |
+------+-----------------+-------+
6 rows in set (0.00 sec)
mysql> select * from sycse;
+-----+-----------------+-------+
| rno | Name | Marks |
+-----+-----------------+-------+
| 1 | Vicky | 20 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
| 6 | Pankaj Deshmukh | 67 |
| 7 | Gaurav Kumar | 84 |
| 8 | Sanket Jain | 61 |
| 9 | Sahil Wagh | 90 |
| 10 | Saurabh Singh | 54 |
+-----+-----------------+-------+
10 rows in set (0.00 sec)
mysql> DELETE FROM student_name
->
-> DELETE FROM student_name
-> WHERE id = 003; ^C
mysql> delete from sycse where rno =6;
Query OK, 1 row affected (0.00 sec)
mysql> delete from sycse where rno =7;
Query OK, 1 row affected (0.00 sec)
mysql> delete from sycse where rno =8;
Query OK, 1 row affected (0.00 sec)
mysql> delete from sycse where rno =9;
Query OK, 1 row affected (0.00 sec)
mysql> delete from sycse where rno =10;
Query OK, 1 row affected (0.00 sec)
mysql> select * from sycse;
+-----+-----------------+-------+
| rno | Name | Marks |
+-----+-----------------+-------+
| 1 | Vicky | 20 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
+-----+-----------------+-------+
5 rows in set (0.00 sec)
mysql> select * from tecse;
+------+-----------------+-------+
| rno | Name | Marks |
+------+-----------------+-------+
| 5 | Rahul Khanna | 90 |
| 6 | Pankaj Deshmukh | 60 |
| 7 | Gaurav Kumar | 80 |
| 8 | Sanket Jain | 60 |
| 9 | Sahil Wagh | 91 |
| 10 | Saurabh Singh | 50 |
+------+-----------------+-------+
6 rows in set (0.00 sec)
mysql> select * from sycse union select * from tecse;
+------+-----------------+-------+
| rno | Name | Marks |
+------+-----------------+-------+
| 1 | Vicky | 20 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
| 5 | Rahul Khanna | 90 |
| 6 | Pankaj Deshmukh | 60 |
| 7 | Gaurav Kumar | 80 |
| 8 | Sanket Jain | 60 |
| 9 | Sahil Wagh | 91 |
| 10 | Saurabh Singh | 50 |
+------+-----------------+-------+
11 rows in set (0.00 sec)
mysql> select name from sycse union select id from tecse;
ERROR 1054 (42S22): Unknown column 'id' in 'field list'
mysql> select name from sycse union select rno from tecse;
+-----------------+
| name |
+-----------------+
| Aakash Deshmukh |
| Aaryan Jaiswal |
| Dev Dixit |
| Rahul Khanna |
| Vicky |
|5 |
|6 |
|7 |
|8 |
|9 |
| 10 |
+-----------------+
11 rows in set (0.00 sec)
mysql> select name from sycse union select name from tecse;
+-----------------+
| name |
+-----------------+
| Aakash Deshmukh |
| Aaryan Jaiswal |
| Dev Dixit |
| Rahul Khanna |
| Vicky |
| Pankaj Deshmukh |
| Gaurav Kumar |
| Sanket Jain |
| Sahil Wagh |
| Saurabh Singh |
+-----------------+
10 rows in set (0.00 sec)
mysql> ^C
mysql> ^C
mysql>
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| becse |
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
| university |
+--------------------+
7 rows in set (0.01 sec)
mysql> use student;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| becse |
| fecse |
| secse |
| sycse |
| tecse |
+-------------------+
5 rows in set (0.01 sec)
mysql> select * from tecse;
+------+-----------------+-------+
| rno | Name | Marks |
+------+-----------------+-------+
| 5 | Rahul Khanna | 90 |
| 6 | Pankaj Deshmukh | 60 |
| 7 | Gaurav Kumar | 80 |
| 8 | Sanket Jain | 60 |
| 9 | Sahil Wagh | 91 |
| 10 | Saurabh Singh | 50 |
+------+-----------------+-------+
6 rows in set (0.00 sec)
mysql> select * from sycse;
+-----+-----------------+-------+
| rno | Name | Marks |
+-----+-----------------+-------+
| 1 | Vicky | 20 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
+-----+-----------------+-------+
5 rows in set (0.00 sec)
mysql> select * from sycse union all select * from tecse;
+------+-----------------+-------+
| rno | Name | Marks |
+------+-----------------+-------+
| 1 | Vicky | 20 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
| 5 | Rahul Khanna | 90 |
| 6 | Pankaj Deshmukh | 60 |
| 7 | Gaurav Kumar | 80 |
| 8 | Sanket Jain | 60 |
| 9 | Sahil Wagh | 91 |
| 10 | Saurabh Singh | 50 |
+------+-----------------+-------+
11 rows in set (0.00 sec)
mysql> select * from sycse intersection select * from tecse;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'select * from tecse' at line 1
mysql> select * from sycse intersect select * from tecse;
Empty set (0.00 sec)
mysql> select rno from sycse intersect select rno from tecse;
+-----+
| rno |
+-----+
| 5|
+-----+
1 row in set (0.00 sec)
mysql> select rno, name from sycse intersect select rno from tecse;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select rno, name from sycse intersect select rno, name from tecse;
+-----+--------------+
| rno | name |
+-----+--------------+
| 5 | Rahul Khanna |
+-----+--------------+
1 row in set (0.00 sec)
mysql> select rno, name, marks from sycse intersect select rno, name, marks from tecse;
Empty set (0.00 sec)
mysql> select marks from sycse intersect select marks from tecse;
+-------+
| marks |
+-------+
| 50 |
| 90 |
+-------+
2 rows in set (0.00 sec)
mysql> select rno from sycse union select rno from tecse;
+------+
| rno |
+------+
| 3|
| 4|
| 2|
| 5|
| 1|
| 6|
| 7|
| 8|
| 9|
| 10 |
+------+
10 rows in set (0.00 sec)
mysql> select rno, name from sycse union select rno, name from tecse;
+------+-----------------+
| rno | name |
+------+-----------------+
| 3 | Aakash Deshmukh |
| 4 | Aaryan Jaiswal |
| 2 | Dev Dixit |
| 5 | Rahul Khanna |
| 1 | Vicky |
| 6 | Pankaj Deshmukh |
| 7 | Gaurav Kumar |
| 8 | Sanket Jain |
| 9 | Sahil Wagh |
| 10 | Saurabh Singh |
+------+-----------------+
10 rows in set (0.00 sec)
mysql> select * from sycse minus select * from tecse;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'select * from tecse' at line 1
mysql> select * from sycse minus select * from tecse;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'select * from tecse' at line 1
mysql> select * from sycse MINUS select * from tecse;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'select * from tecse' at line 1
mysql> select rno from sycse MINUS select rno from tecse;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'select rno from tecse' at line 1
mysql> select rno from sycse MINUS select rno from becse;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'select rno from becse' at line 1
mysql> select rno from sycse except select rno from becse;
Empty set (0.00 sec)
mysql> select rno from sycse except select rno from tecse;
+-----+
| rno |
+-----+
| 3|
| 4|
| 2|
| 1|
+-----+
4 rows in set (0.00 sec)
mysql> select rno,name from sycse except select rno,name from tecse;
+-----+-----------------+
| rno | name |
+-----+-----------------+
| 3 | Aakash Deshmukh |
| 4 | Aaryan Jaiswal |
| 2 | Dev Dixit |
| 1 | Vicky |
+-----+-----------------+
4 rows in set (0.00 sec)
mysql> select rno,name,marks from sycse except select rno,name,marks from tecse;
+-----+-----------------+-------+
| rno | name | marks |
+-----+-----------------+-------+
| 1 | Vicky | 20 |
| 2 | Dev Dixit | 75 |
| 3 | Aakash Deshmukh | 50 |
| 4 | Aaryan Jaiswal | 90 |
| 5 | Rahul Khanna | 92 |
+-----+-----------------+-------+
5 rows in set (0.00 sec)
mysql> select * from secse;
+------+------+-----------+
| rno | Name | Subject |
+------+------+-----------+
| 1 | aaa | SeminarII |
| 2 | bbb | Seminar |
+------+------+-----------+
2 rows in set (0.00 sec)
mysql> insert into secse (rno, name,subject)values(3,'ccc','Engg Math III'),(4, 'ddd', 'Discrete Math'),(5,'eee','Data
Structures'),(6,'fff','Computer Arcitecture & Organization'),(7,'ggg','Elective 1');
ERROR 1406 (22001): Data too long for column 'Subject' at row 4
mysql> insert into secse (rno, name,subject)values(3,'ccc','Engg Math III'),(4, 'ddd', 'Discrete Math'),(5,'eee','Data
Structures'),(6,'fff','CAO'),(7,'ggg','Elective 1');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from secse;
+------+------+-----------------+
| rno | Name | Subject |
+------+------+-----------------+
| 1 | aaa | SeminarII |
| 2 | bbb | Seminar |
| 3 | ccc | Engg Math III |
| 4 | ddd | Discrete Math |
| 5 | eee | Data Structures |
| 6 | fff | CAO |
| 7 | ggg | Elective 1 |
+------+------+-----------------+
7 rows in set (0.00 sec)
mysql>
Conclusion: The stude of sub queries,set operations,
joins performs successfully are run the program in sub queries
,set operations,joins.
