Practical No 02 A
Name : Siddhi Vinayak Narke
Roll No : 13267
Batch : B3
Database Management System Laboratory
Problem Statement : Write 10 SQL Query on SQL DDL Statements
show databases;
+--------------------+
| Database |
+--------------------+
| college |
| information_schema |
| priyadb1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
Mysql> create database Employeement;
mysql> use Employeement;
Database changed
mysql> create table Employee(Emp_Id int(23) PRIMARY KEY, Emp_Name
varchar(24),Emp_City varchar(24), Emp_DOB DATE, EMP_Department varchar(25),
Emp_Salary Decimal(7,2));
mysql> insert into Employee values(1,"kiran","Sambhajinagar",'2005-10-16',"IT",67000.90);
mysql> insert into Employee values(2,"Dhano","Jalgoan",'2005-08-
08',"Marketing",56000.90);
mysql> insert into Employee values(3,"Priya","Jalgoan",'2005-12-
01',"Production",46000.90);
mysql> insert into Employee values(4,"Aman","Naskik",'2005-03-06',"HR",86000.90);
mysql> insert into Employee values(5,"Rohan","Latur",'2005-03-
04',"Production",45000.90);
mysql> insert into Employee values(5,"Rohan","Latur",'2005-03-
04',"Production",45000.90);
mysql> insert into Employee values(6,"Sarvesh","Latur",'2002-11-07',"HR",1000.90);
mysql> insert into Employee values(7,"Prajyot","Latur",'2002-11-07',"Marketing",5000.90);
mysql> insert into Employee values(8,"Unnati","Nashik",'1999-11-07',"IT",7000.90);
mysql> select * from Employee;
+--------+------------+----------+------------+----------------+------------+
| Emp_Id | Emp_Name | Emp_City | Emp_DOB | EMP_Department | Emp_Salary |
+--------+------------+----------+------------+----------------+------------+
| 1 | kiran | Solapur | 2005-10-16 | IT | 67000.90 |
| 2 | Dhanashree | Mumbai | 2005-08-08 | Marketing | 56000.90 |
| 3 | Priya | Mumbai | 2005-12-01 | Production | 46000.90 |
| 4 | Aman | Naskik | 2005-03-06 | HR | 86000.90 |
| 5 | Rohan | Pune | 2005-03-04 | Production | 45000.90 |
| 6 | Sarvesh | Nagpur | 2002-11-07 | HR | 1000.90 |
| 7 | Prajyot | Pune | 2002-11-07 | Marketing | 5000.90 |
| 8 | Unnati | Kolhapur | 1999-11-07 | IT | 7000.90 |
+--------+------------+----------+------------+----------------+------------+
mysql> select Emp_name from Employee where Emp_city="Mumbai";
+------------+
| Emp_name |
+------------+
| Dhanashree |
| Priya |
+------------+
mysql> select Emp_name from Employee where Emp_city
IN('Kolhapur','Solapur','Nagpur');
+----------+
| Emp_name |
+----------+
| kiran |
| Sarvesh |
| Unnati |
+----------+
mysql> select Emp_Name from Employee where Post="Manager" AND
Emp_salary>30000.00;
+----------+
| Emp_Name |
+----------+
| kiran |
| Priya |
+----------+
mysql> select Emp_Name from Employee where Emp_city="Mumbai" OR
Emp_city="Pune";
+------------+
| Emp_Name |
+------------+
| Dhanashree |
| Priya |
| Rohan |
| Prajyot |
+------------+
mysql> select Emp_Name from Employee where Emp_salary >500.00 AND
Emp_Salary<56000.00;
+----------+
| Emp_Name |
+----------+
| Priya |
| Rohan |
| Sarvesh |
| Prajyot |
| Unnati |
+----------+
mysql> select SUM(Emp_salary) AS Total_Salary From Employee;
+--------------+
| Total_Salary |
+--------------+
| 313007.20 |
+--------------+
mysql> alter table Employee ADD Post varchar(25);
mysql> select * from Employee;
+--------+------------+----------+------------+----------------+------------+------+
| Emp_Id | Emp_Name | Emp_City | Emp_DOB | EMP_Department | Emp_Salary |
Post |
+--------+------------+----------+------------+----------------+------------+------+
| 1 | kiran | Solapur | 2005-10-16 | IT | 67000.90 | NULL |
| 2 | Dhanashree | Mumbai | 2005-08-08 | Marketing | 56000.90 | NULL |
| 3 | Priya | Mumbai | 2005-12-01 | Production | 46000.90 | NULL |
| 4 | Aman | Naskik | 2005-03-06 | HR | 86000.90 | NULL |
| 5 | Rohan | Pune | 2005-03-04 | Production | 45000.90 | NULL |
| 6 | Sarvesh | Nagpur | 2002-11-07 | HR | 1000.90 | NULL |
| 7 | Prajyot | Pune | 2002-11-07 | Marketing | 5000.90 | NULL |
| 8 | Unnati | Kolhapur | 1999-11-07 | IT | 7000.90 | NULL |
+--------+------------+----------+------------+----------------+------------+------+
mysql> update Employee set Post="Manager" where Emp_ID=3;
