Assignment 2B
Name : Rohan Subhash Gadakh
Roll No : 82
mysql> CREATE TABLE Employee (
-> Eid INT PRIMARY KEY,
-> EName VARCHAR(50),
-> Address VARCHAR(50),
-> Salary INT,
-> Commission INT
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE Project (
-> PrNo INT PRIMARY KEY,
-> Addr VARCHAR(50)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT DISTINCT Address FROM Employee;
Empty set (0.01 sec)
mysql> INSERT INTO Employee (Eid, EName, Address, Salary, Commission)
-> VALUES
-> (1, 'Amit', 'Pune', 35000, 5000),
-> (2, 'Sneha', 'Pune', 25000, NULL),
-> (3, 'Savita', 'Nasik', 28000, 2000),
-> (4, 'Pooja', 'Mumbai', 19000, NULL),
-> (5, 'Sagar', 'Mumbai', 25000, 3000);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Project (PrNo, Addr)
-> VALUES
-> (10, 'Mumbai'),
-> (20, 'Pune'),
-> (30, 'Jalgaon');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT DISTINCT Address FROM Employee;
+---------+
| Address |
+---------+
| Pune |
| Nasik |
| Mumbai |
+---------+
3 rows in set (0.00 sec)
mysql> SELECT MAX(Salary) AS Max_Salary, MIN(Salary) AS Min_Salary FROM Employee;
+------------+------------+
| Max_Salary | Min_Salary |
+------------+------------+
| 35000 | 19000 |
+------------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Employee ORDER BY Salary ASC;
+-----+--------+---------+--------+------------+
| Eid | EName | Address | Salary | Commission |
+-----+--------+---------+--------+------------+
| 4 | Pooja | Mumbai | 19000 | NULL |
| 2 | Sneha | Pune | 25000 | NULL |
| 5 | Sagar | Mumbai | 25000 | 3000 |
| 3 | Savita | Nasik | 28000 | 2000 |
| 1 | Amit | Pune | 35000 | 5000 |
+-----+--------+---------+--------+------------+
5 rows in set (0.00 sec)
mysql> SELECT EName FROM Employee WHERE Address IN ('Nasik', 'Pune');
+--------+
| EName |
+--------+
| Amit |
| Sneha |
| Savita |
+--------+
3 rows in set (0.00 sec)
mysql> SELECT EName FROM Employee WHERE Commission IS NULL;
+-------+
| EName |
+-------+
| Sneha |
| Pooja |
+-------+
2 rows in set (0.00 sec)
mysql> UPDATE Employee SET Address = 'Nashik' WHERE EName = 'Amit';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM Employee WHERE EName LIKE 'A%';
+-----+-------+---------+--------+------------+
| Eid | EName | Address | Salary | Commission |
+-----+-------+---------+--------+------------+
| 1 | Amit | Nashik | 35000 | 5000 |
+-----+-------+---------+--------+------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM Employee WHERE Address = 'Mumbai';
+----------+
| COUNT(*) |
+----------+
| 2|
+----------+
1 row in set (0.00 sec)
mysql> SELECT Address, COUNT(*) AS EmployeeCount FROM Employee GROUP BY Address;
+---------+---------------+
| Address | EmployeeCount |
+---------+---------------+
| Nashik | 1|
| Pune | 1|
| Nasik | 1|
| Mumbai | 2|
+---------+---------------+
4 rows in set (0.00 sec)
mysql> SELECT DISTINCT [Link] FROM Employee E INNER JOIN Project P ON [Link] =
[Link];
+---------+
| Address |
+---------+
| Pune |
| Mumbai |
+---------+
2 rows in set (0.00 sec)
mysql> SELECT Address, MIN(Salary) AS MinSalary FROM Employee GROUP BY Address;
+---------+-----------+
| Address | MinSalary |
+---------+-----------+
| Nashik | 35000 |
| Pune | 25000 |
| Nasik | 28000 |
| Mumbai | 19000 |
+---------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT Address, MAX(Salary) AS MaxSalary
-> FROM Employee
-> GROUP BY Address
-> HAVING MAX(Salary) > 26000;
+---------+-----------+
| Address | MaxSalary |
+---------+-----------+
| Nashik | 35000 |
| Nasik | 28000 |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> DELETE FROM Employee WHERE Salary > 30000;
Query OK, 1 row affected (0.00 sec)
mysql>
