# Advance DBMS

### `Practical – 01`

### `Name – Tarun Joshi`

### `Student ID – 23711144`

### `Course – MCA.`

### `Semester – II.`

### `Date – 20-Feb-2024.`

**Objective – Demonstrate the command in Data Definition Language (Create Drop, Alter, Rename, Truncate, Backup).**

**mysql> create table students(id int, Name varchar(20), course varchar(10));**

**mysql> describe students;**

**+--------+-------------+------+-----+---------+-------+**

**| Field  | Type        | Null | Key | Default | Extra |**

**+--------+-------------+------+-----+---------+-------+**

**| id     | int         | YES  |     | NULL    |       |**

**| Name   | varchar(20) | YES  |     | NULL    |       |**

**| course | varchar(10) | YES  |     | NULL    |       |**

**+--------+-------------+------+-----+---------+-------+**

**mysql> alter table students add Age int;**

**mysql> insert into students values(1,'Bhavesh','MCA',20);**

**mysql> insert into students values(2,'Neha','MCA',21);**

**mysql> insert into students values(3,'Laxman','MCA',22);**

**mysql> insert into students values(4,'Hema','MCA',22);**

**mysql> select \* from students;**

**+------+---------+--------+------+**

**| id   | Name    | course | Age  |**

**+------+---------+--------+------+**

**|    1 | Bhavesh | MCA    |   20 |**

**|    2 | Neha    | MCA    |   21 |**

**|    3 | Laxman  | MCA    |   22 |**

**|    4 | Hema    | MCA    |   22 |**

**+------+---------+--------+------+**

**mysql> alter table students modify column id int not null unique;**

**mysql> alter table students modify column id int not null unique;**

**mysql> describe students;**

**+--------+-------------+------+-----+---------+-------+**

**| Field  | Type        | Null | Key | Default | Extra |**

**+--------+-------------+------+-----+---------+-------+**

**| id     | int         | NO   | PRI | NULL    |       |**

**| Name   | varchar(20) | YES  |     | NULL    |       |**

**| course | varchar(10) | YES  |     | NULL    |       |**

**| Age    | int         | YES  |     | NULL    |       |**

**+--------+-------------+------+-----+---------+-------+**

**mysql> select \* from students where Age='22';**

**+----+--------+--------+------+**

**| id | Name   | course | Age  |**

**+----+--------+--------+------+**

**|  3 | Laxman | MCA    |   22 |**

**|  4 | Hema   | MCA    |   22 |**

**+----+--------+--------+------+**

**2 rows in set (0.00 sec)**

**mysql> select \* from students where Age>21;**

**+----+--------+--------+------+**

**| id | Name   | course | Age  |**

**+----+--------+--------+------+**

**|  3 | Laxman | MCA    |   22 |**

**|  4 | Hema   | MCA    |   22 |**

**|  5 | Lax    | MCA    |   23 |**

**|  6 | Abhi   | MCA    |   24 |**

**|  7 | Naman  | MBA    |   25 |**

**+----+--------+--------+------+**

**5 rows in set (0.00 sec)**

**mysql> select \* from students where Age<21;**

**+----+---------+--------+------+**

**| id | Name    | course | Age  |**

**+----+---------+--------+------+**

**|  1 | Bhavesh | MCA    |   20 |**

**+----+---------+--------+------+**

**1 row in set (0.00 sec)**

**mysql> select \* from students where Age<>23;**

**+----+---------+--------+------+**

**| id | Name    | course | Age  |**

**+----+---------+--------+------+**

**|  1 | Bhavesh | MCA    |   20 |**

**|  2 | Neha    | MCA    |   21 |**

**|  3 | Laxman  | MCA    |   22 |**

**|  4 | Hema    | MCA    |   22 |**

**|  6 | Abhi    | MCA    |   24 |**

**|  7 | Naman   | MBA    |   25 |**

**+----+---------+--------+------+**

### `PRACTICAL – 7`

### `Name:Tarun Joshi`

### `Course: MCA`

### `Student ID:2371114`

### `Objective:- Write SQL queries  using logical operations and operators`

### `Code:-`

**AND Operator-**

 **SELECT \* FROM employees WHERE City = "Mumbai" AND Designation = "Project Manager";**  

| **E\_ID** | **Name** | **Salary** | **City** | **Designation** | **Date\_of\_Joining** | **Age** |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Sakshi Kumari | 50000 | Mumbai | Project Manager | 2021-06-20 | 24 |
| 10 | Mayuri Patel | 60000 | Mumbai | Project Manager | 2020-10-02 | 24 |

**2\. SQL BETWEEN Operator**

**SELECT \* FROM employees WHERE Salary BETWEEN 50000 AND 90000;**  

| **E\_ID** | **Name** | **Salary** | **City** | **Designation** | **Date\_of\_Joining** | **Age** |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Sakshi Kumari | 50000 | Mumbai | Project Manager | 2021-06-20 | 24 |
| 2 | Tejaswini Naik | 75000 | Delhi | System Engineer | 2019-12-24 | 23 |
| 4 | Anushka Tripathi | 90000 | Mumbai | Software Tester | 2021-06-13 | 24 |
| 6 | Rutuja Deshmukh | 60000 | Bangalore | Manager | 2019-07-17 | 26 |
| 7 | Swara Baviskar | 55000 | Jaipur | System Engineer | 2021-10-10 | 24 |
| 9 | Swati Kumari | 50000 | Pune | Software Tester | 2021-01-01 | 25 |
| 10 | Mayuri Patel | 60000 | Mumbai | Project Manager | 2020-10-02 | 24 |
| 12 | Shivani Wagh | 50500 | Delhi | Software Developer | 2016-09-10 | 25 |
| 13 | Kiran Maheshwari | 50000 | Nashik | HR | 2013-12-12 | 23 |

**3\. SQL OR Operator-**

**SELECT \* FROM employees WHERE Designation = "System Engineer" OR City = "Mumbai";**  

| **E\_ID** | **Name** | **Salary** | **City** | **Designation** | **Date\_of\_Joining** | **Age** |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Sakshi Kumari | 50000 | Mumbai | Project Manager | 2021-06-20 | 24 |
| 2 | Tejaswini Naik | 75000 | Delhi | System Engineer | 2019-12-24 | 23 |
| 4 | Anushka Tripathi | 90000 | Mumbaipp | Software Tester | 2021-06-13 | 24 |
| 7 | Swara Baviskar | 55000 | Jaipur | System Engineer | 2021-10-10 | 24 |
| 10 | Mayuri Patel | 60000 | Mumbai | Project Manager | 2020-10-02 | 24 |

**4\. SQL IN Operator-**

 **SELECT \* FROM employees WHERE City IN ("Mumbai", "Bangalore", "Pune");**  

| **E\_ID** | **Name** | **Salary** | **City** | **Designation** | **Date\_of\_Joining** | **Age** |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Sakshi Kumari | 50000 | Mumbai | Project Manager | 2021-06-20 | 24 |
| 4 | Anushka Tripathi | 90000 | Mumbai | Software Tester | 2021-06-13 | 24 |
| 5 | Rucha Jagtap | 45000 | Bangalore | Project Manager | 2020-08-09 | 23 |
| 6 | Rutuja Deshmukh | 60000 | Bangalore | Manager | 2019-07-17 | 26 |
| 8 | Sana Sheik | 45000 | Pune | Software Engineer | 2020-09-10 | 26 |
| 9 | Swati Kumari | 50000 | Pune | Software Tester | 2021-01-01 | 25 |
| 10 | Mayuri Patel | 60000 | Mumbai | Project Manager | 2020-10-02 | 24 |
| 15 | Mohini Shah | 38000 | Pune | Software Developer | 2019-03-05 | 20 |

  

**5\. SQL NOT Operator-**

**SELECT \* FROM employees WHERE NOT Designation = "Project Manager";**

| **E\_ID** | **Name** | **Salary** | **City** | **Designation** | **Date\_of\_Joining** | **Age** |
| --- | --- | --- | --- | --- | --- | --- |
| 2 | Tejaswini NaikPractical – 01 | 75000 | Delhi | System Engineer | 2019-12-24 | 23 |
| 3 | Anuja Sharma | 40000 | Jaipur | Manager | 2021-08-15 | 26 |
| 4 | Anushka Tripathi | 90000 | Mumbai | Software Tester | 2021-06-13 | 24 |
| 6 | Rutuja Deshmukh | 60000 | Bangalore | Manager | 2019-07-17 | 26 |
| 7 | Swara Baviskar | 55000 | Jaipur | System Engineer | 2021-10-10 | 24 |

### `PRACTICAL – 5`

### `Name: Tarun Joshi`

### `Course: MCA`

### `Student ID:23711144`

### `Objective:- Write a query to understand the concept for Rollback,Commit and Check points`

### `Code:-`

**RollBack:-**

**select\* from Emp1;**

**+----------+--------+---------+**

**| emp\_name | salary | dept\_id |**

**+----------+--------+---------+**

**| Pradeep     |  25000 |     301 |**

**| Yogesh  |  30000 |     302 |**

**| Anchl    |  20000 |     304 |**

**+----------+--------+---------+**

**DELETE FROM Emp1 WHERE dept\_id = 301;**

**ROLLBACK;**

**select \* from Emp1;**

**+----------+--------+---------+**

**| emp\_name | salary | dept\_id |**

**+----------+--------+---------+**

**| Pradeep    |  25000 |     301 |**

**| Yogesh   |  30000 |     302 |**

**| Anchl   |  20000 |     304 |**

**+----------+--------+---------+**

**COMMIT;**

**DELETE FROM Emp1 WHERE dept\_id = 304;**

**select \* from Emp1;**

**+----------+--------+---------+**

**| emp\_name | salary | dept\_id |**

**+----------+--------+---------+**

**| Pradeep    |  25000 |     301 |**

**| Yogesh   |  30000 |     302 |**

**| Anchl   |  20000 |     304 |**

**+----------+--------+---------+**

### `PRACTICAL – 9`

### `Name: Tarun Joshi`

### `Course: MCA`

### `Student ID:23711144`

### `Objective:- Demonstrate  the aggregate function (AVG , MIN, MAX , SUM , COUNT  etc).`

### `Code:-`

**MAX:-**

**select max(sallary) from student;**

**+--------------+**

**| max(sallary) |**

**+--------------+**

**|       530000 |**

**+--------------+**

**Average:-**

**select  avg(sallary) from student;**

**+--------------+**

**| avg(sallary) |**

**+--------------+**

**|  264166.6667 |**

**Sum:-**

**select  sum(sallary) from student;**

**+--------------+**

**| sum(sallary) |**

**+--------------+**

**|      1585000 |**

**Count:-**

**select  count(ID) from student;**

**+-----------+**

**| count(ID) |**

**+-----------+**

**|         6 |**

**Min:-**

**select  min(sallary) from student;**

**+--------------+**

**| min(sallary) |**

**+--------------+**

**|        25000 |**

### `PRACTICAL – 11`

### `Name: TarunJoshi`

### `Course: MCA`

### `Student ID:23711144`

### `Objective:- Demonstrate the Union ,Intersection and Difference in tables.`

### `Code:-`

**Union:-**

**SELECT \* FROM First UNION**

**SELECT \* FROM Second;**

| **ID** | **NAME** |
| --- | --- |
| **1** | **Yogesh** |
| **2** | **Akanksha** |
| **3** | **tarun** |
| **4** | **Pradeep** |
| **5** | **Nimisha** |

**Inersection:-**

**SELECT \* FROM First INTERSECT**  

**SELECT \* FROM Second;**  

| **ID** | **NAME** |
| --- | --- |
| **3** | **tarun** |

**Minus:-**

    **SELECT column\_name FROM table1**  

    **MINUS**  

    **SELECT column\_name FROM table2;**

| **ID** | **NAME** |
| --- | --- |
| **1** | **Yogesh** |
| **2** | **Akanksha** |

### `PRACTICAL – 12`

### `Name: Tarun Joshi`

### `Course: MCA`

### `Student ID:23711144`

### `Objective:- Demonstrate the Inner join,Left outer join ,Right outer join`

### `Code:-`

**Inner JOIN:-**

**select \* from stu inner join enroll on** [**stu.id**](http://stu.id)**\=**[**enroll.id**](http://enroll.id)**;**

**+----+---------+------+------+------+--------+**

**| id | name    | age  | id   | cid  | course |**

**+----+---------+------+------+------+--------+**

**|  1 | nimi    |   22 |    1 |    1 | mca        |**

**|  3 | yogesh  |   23 |    3 |    2 | bca      |**

**|  4 | pradeep |   24 |    4 |    1 | mca    |**

**+----+---------+------+------+------+--------+**

**Left Outer join:-**

**select \* from stu left outer join enroll on** [**stu.id**](http://stu.id)**\=**[**enroll.id**](http://enroll.id)**;**

**+----+----------+------+------+------+--------+**

**| id | name     | age  | id   | cid  | course |**

**+----+----------+------+------+------+--------+**

**|  1 | tarun|   22 |    1 |    1 | mca    |**

**|  2 | akanksha |   22 | NULL | NULL | NULL   |**

**|  3 | yogesh   |   23 |    3 |    2 | bca    |**

**|  4 | pradeep  |   24 |    4 |    1 | mca    |**

**+------+---------+------+------+------+--------+**

**Right outer join:-**

**select \* from stu right outer join enroll on** [**stu.id**](http://stu.id)**\=**[**enroll.id**](http://enroll.id)**;**

**+------+---------+------+------+------+--------+**

**| id   | name    | age  | id   | cid  | course |**

**+------+---------+------+------+------+--------+**

**|    1 | tarun|   22 |    1 |    1 | mca    |**

**|    3 | yogesh  |   23 |    3 |    2 | bca    |**

**|    4 | pradeep |   24 |    4 |    1 | mca    |**

**+------+---------+------+------+------+--------+**