Relations पर Operations और Joins

SELECT * FROM CHESS;

+--------+--------+-------+
| RollNo | Name   | Class |
+--------+--------+-------+
|     11 | Arnav  | XII   |
|     14 | Bhavya | XII   |
|     17 | Charu  | XI    |
|     21 | Daksh  | XI    |
+--------+--------+-------+

SELECT * FROM QUIZ;

+--------+--------+-------+
| RollNo | Name   | Class |
+--------+--------+-------+
|     14 | Bhavya | XII   |
|     19 | Esha   | XII   |
|     21 | Daksh  | XI    |
+--------+--------+-------+

SELECT Name FROM CHESS UNION SELECT Name FROM QUIZ ORDER BY Name;

+--------+
| Name   |
+--------+
| Arnav  |
| Bhavya |
| Charu  |
| Daksh  |
| Esha   |
+--------+

SELECT Name FROM CHESS INTERSECT SELECT Name FROM QUIZ ORDER BY Name;

+--------+
| Name   |
+--------+
| Bhavya |
| Daksh  |
+--------+

SELECT Name FROM CHESS WHERE RollNo IN (SELECT RollNo FROM QUIZ);

+--------+
| Name   |
+--------+
| Bhavya |
| Daksh  |
+--------+

SELECT Name FROM CHESS EXCEPT SELECT Name FROM QUIZ ORDER BY Name;

+-------+
| Name  |
+-------+
| Arnav |
| Charu |
+-------+

SELECT Name FROM CHESS WHERE RollNo NOT IN (SELECT RollNo FROM QUIZ);

+-------+
| Name  |
+-------+
| Arnav |
| Charu |
+-------+

SELECT COUNT(*) FROM CHESS, QUIZ;

+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+

SELECT C.Name, Q.Name FROM CHESS C, QUIZ Q WHERE C.Class = 'XI' AND Q.Class = 'XII' ORDER BY C.Name, Q.Name;

+-------+--------+
| Name  | Name   |
+-------+--------+
| Charu | Bhavya |
| Charu | Esha   |
| Daksh | Bhavya |
| Daksh | Esha   |
+-------+--------+

SELECT * FROM SUPPLIER;

+-------+---------------+--------+
| SupID | SupName       | City   |
+-------+---------------+--------+
| S1    | Kiran Traders | Delhi  |
| S2    | Om Supplies   | Indore |
| S3    | Star Mart     | Kochi  |
+-------+---------------+--------+

SELECT * FROM PRODUCT;

+-----+------------+-------+-------+
| PID | PName      | Price | SupID |
+-----+------------+-------+-------+
|   1 | Pencil Box |   120 | S1    |
|   2 | Crayons    |    80 | S2    |
|   3 | Stapler    |   150 | S1    |
|   4 | Glue Stick |    35 | S3    |
+-----+------------+-------+-------+

SELECT PName, Price, SupName FROM PRODUCT, SUPPLIER WHERE PRODUCT.SupID = SUPPLIER.SupID ORDER BY PName;

+------------+-------+---------------+
| PName      | Price | SupName       |
+------------+-------+---------------+
| Crayons    |    80 | Om Supplies   |
| Glue Stick |    35 | Star Mart     |
| Pencil Box |   120 | Kiran Traders |
| Stapler    |   150 | Kiran Traders |
+------------+-------+---------------+

SELECT P.PName, S.City FROM PRODUCT AS P JOIN SUPPLIER AS S ON P.SupID = S.SupID WHERE P.Price > 100 ORDER BY P.PName;

+------------+-------+
| PName      | City  |
+------------+-------+
| Pencil Box | Delhi |
| Stapler    | Delhi |
+------------+-------+

SELECT PName, SupName FROM PRODUCT NATURAL JOIN SUPPLIER ORDER BY PName;

+------------+---------------+
| PName      | SupName       |
+------------+---------------+
| Crayons    | Om Supplies   |
| Glue Stick | Star Mart     |
| Pencil Box | Kiran Traders |
| Stapler    | Kiran Traders |
+------------+---------------+

SELECT S.SupName, COUNT(*) AS Items, SUM(P.Price) AS Total FROM PRODUCT P, SUPPLIER S WHERE P.SupID = S.SupID GROUP BY S.SupName ORDER BY S.SupName;

+---------------+-------+-------+
| SupName       | Items | Total |
+---------------+-------+-------+
| Kiran Traders |     2 |   270 |
| Om Supplies   |     1 |    80 |
| Star Mart     |     1 |    35 |
+---------------+-------+-------+