Assignment 3
Name : Rohan Subhash Gadakh
Roll No : 82
mysql> CREATE TABLE Cust_Info (
-> C_Id INT PRIMARY KEY,
-> Cname VARCHAR(50),
-> City VARCHAR(50)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE Acc_Info (
-> C_Id INT,
-> Acc_Type VARCHAR(50),
-> Amount INT,
-> FOREIGN KEY (C_Id) REFERENCES Cust_Info(C_Id)
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO Cust_Info (C_Id, Cname, City)
-> VALUES
-> (1, 'John', 'Nashik'),
-> (2, 'Seema', 'Aurangabad'),
-> (3, 'Amita', 'Nagar'),
-> (4, 'Rakesh', 'Pune'),
-> (5, 'Samata', 'Nashik'),
-> (6, 'Ankita', 'Chandwad'),
-> (7, 'Bhavika', 'Pune'),
-> (8, 'Deepa', 'Mumbai'),
-> (9, 'Nitin', 'Nagpur'),
-> (10, 'Pooja', 'Pune');
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Acc_Info (C_Id, Acc_Type, Amount)
-> VALUES
-> (1, 'Current', 5000),
-> (2, 'Saving', 20000),
-> (3, 'Saving', 70000),
-> (4, 'Saving', 50000),
-> (6, 'Current', 35000),
-> (7, 'Loan', 30000),
-> (8, 'Saving', 50000),
-> (9, 'Saving', 90000),
-> (10, 'Loan', 8000),
-> (11, 'Current', 45000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`empinfo`.`acc_info`,
CONSTRAINT `acc_info_ibfk_1` FOREIGN KEY (`C_Id`) REFERENCES `cust_info` (`C_Id`))
mysql> INSERT INTO Acc_Info (C_Id, Acc_Type, Amount)
-> VALUES
-> (1, 'Current', 5000),
-> (2, 'Saving', 20000),
-> (3, 'Saving', 70000),
-> (4, 'Saving', 50000),
-> (6, 'Current', 35000),
-> (7, 'Loan', 30000),
-> (8, 'Saving', 50000),
-> (9, 'Saving', 90000),
-> (10, 'Loan', 8000);
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SELECT c.Cname, a.Acc_Type, a.Amount
-> FROM Cust_Info c
-> INNER JOIN Acc_Info a ON c.C_Id = a.C_Id
-> WHERE a.Acc_Type = 'Saving';
+--------+----------+--------+
| Cname | Acc_Type | Amount |
+--------+----------+--------+
| Seema | Saving | 20000 |
| Amita | Saving | 70000 |
| Rakesh | Saving | 50000 |
| Deepa | Saving | 50000 |
| Nitin | Saving | 90000 |
+--------+----------+--------+
5 rows in set (0.00 sec)
mysql> SELECT *
-> FROM Cust_Info NATURAL JOIN Acc_Info;
+------+---------+------------+----------+--------+
| C_Id | Cname | City | Acc_Type | Amount |
+------+---------+------------+----------+--------+
| 1 | John | Nashik | Current | 5000 |
| 2 | Seema | Aurangabad | Saving | 20000 |
| 3 | Amita | Nagar | Saving | 70000 |
| 4 | Rakesh | Pune | Saving | 50000 |
| 6 | Ankita | Chandwad | Current | 35000 |
| 7 | Bhavika | Pune | Loan | 30000 |
| 8 | Deepa | Mumbai | Saving | 50000 |
| 9 | Nitin | Nagpur | Saving | 90000 |
| 10 | Pooja | Pune | Loan | 8000 |
+------+---------+------------+----------+--------+
9 rows in set (0.00 sec)
mysql> SELECT *
-> FROM Cust_Info LEFT JOIN Acc_Info ON Cust_Info.C_Id = Acc_Info.C_Id;
+------+---------+------------+------+----------+--------+
| C_Id | Cname | City | C_Id | Acc_Type | Amount |
+------+---------+------------+------+----------+--------+
| 1 | John | Nashik | 1 | Current | 5000 |
| 2 | Seema | Aurangabad | 2 | Saving | 20000 |
| 3 | Amita | Nagar | 3 | Saving | 70000 |
| 4 | Rakesh | Pune | 4 | Saving | 50000 |
| 5 | Samata | Nashik | NULL | NULL | NULL |
| 6 | Ankita | Chandwad | 6 | Current | 35000 |
| 7 | Bhavika | Pune | 7 | Loan | 30000 |
| 8 | Deepa | Mumbai | 8 | Saving | 50000 |
| 9 | Nitin | Nagpur | 9 | Saving | 90000 |
| 10 | Pooja | Pune | 10 | Loan | 8000 |
+------+---------+------------+------+----------+--------+
10 rows in set (0.00 sec)
mysql> SELECT *
-> FROM Cust_Info RIGHT JOIN Acc_Info ON Cust_Info.C_Id = Acc_Info.C_Id;
+------+---------+------------+------+----------+--------+
| C_Id | Cname | City | C_Id | Acc_Type | Amount |
+------+---------+------------+------+----------+--------+
| 1 | John | Nashik | 1 | Current | 5000 |
| 2 | Seema | Aurangabad | 2 | Saving | 20000 |
| 3 | Amita | Nagar | 3 | Saving | 70000 |
| 4 | Rakesh | Pune | 4 | Saving | 50000 |
| 6 | Ankita | Chandwad | 6 | Current | 35000 |
| 7 | Bhavika | Pune | 7 | Loan | 30000 |
| 8 | Deepa | Mumbai | 8 | Saving | 50000 |
| 9 | Nitin | Nagpur | 9 | Saving | 90000 |
| 10 | Pooja | Pune | 10 | Loan | 8000 |
+------+---------+------------+------+----------+--------+
9 rows in set (0.00 sec)
mysql> SELECT *
-> FROM Cust_Info
-> WHERE City = (SELECT City FROM Cust_Info WHERE Cname = 'Pooja');
+------+---------+------+
| C_Id | Cname | City |
+------+---------+------+
| 4 | Rakesh | Pune |
| 7 | Bhavika | Pune |
| 10 | Pooja | Pune |
+------+---------+------+
3 rows in set (0.01 sec)
mysql> SELECT *
-> FROM Acc_Info
-> WHERE Amount < (SELECT AVG(Amount) FROM Acc_Info);
+------+----------+--------+
| C_Id | Acc_Type | Amount |
+------+----------+--------+
| 1 | Current | 5000 |
| 2 | Saving | 20000 |
| 6 | Current | 35000 |
| 7 | Loan | 30000 |
| 10 | Loan | 8000 |
+------+----------+--------+
5 rows in set (0.00 sec)
mysql> SELECT C_Id
-> FROM Acc_Info
-> WHERE Amount = (SELECT MAX(Amount) FROM Acc_Info);
+------+
| C_Id |
+------+
| 9|
+------+
1 row in set (0.00 sec)
mysql> SELECT Amount, Acc_Type
-> FROM Acc_Info a
-> WHERE Amount = (
-> SELECT MIN(Amount)
-> FROM Acc_Info b
-> WHERE a.Acc_Type = b.Acc_Type
-> );
+--------+----------+
| Amount | Acc_Type |
+--------+----------+
| 5000 | Current |
| 20000 | Saving |
| 8000 | Loan |
+--------+----------+
3 rows in set (0.00 sec)
mysql> SELECT Amount
-> FROM Acc_Info
-> WHERE Amount > (SELECT MAX(Amount) FROM Acc_Info WHERE Acc_Type = 'Saving');
Empty set (0.00 sec)
mysql>
