Q1. Write a SQL block to display electricity bill for the consumer. Database should consist of consumer number, address, and unit consume.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE consumer(
-> id INT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL,
-> mobile INT(10) NOT NULL,
-> address VARCHAR(20) NOT NULL,
-> unit_consume INT NOT NULL
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> desc consumer;
+--------------+----------+------+-----+---------+
| Field | Type | Null | Key | Default |
+--------------+----------+------+-----+---------+
| id | int | NO | PRI | NULL |
| name | char(20) | YES | | NULL |
| mobile | char(10) | NO | | NULL |
| address | char(30) | NO | | NULL |
| unit_consume | int | NO | | NULL |
+--------------+----------+------+-----+---------+
5 rows in set (0.00 sec)
mysql> INSERT INTO consumer VALUES(1,'Shiva',9534984563,'Delhi',80),(2,'Ram',8532824533,'Agra',50),(3,'Dj',8938870783,'Jaipur',100);
Query OK, 3 rows affected (0.03 sec)
mysql> SELECT *,unit_cunsume*6 as electric bill FROM consumer;
+----+-------+--------+---------+--------------+---------------+
| id | name | mobile | address | unit_consume | electric bill |
+----+-------+--------+---------+--------------+---------------+
| 1 | Shiva | 953563 | Delhi | 80 | 480 |
| 2 | Ram | 853533 | Agra | 50 | 300 |
| 3 | Dj | 893783 | Jaipur | 100 | 600 |
+----+-------+--------+---------+--------------+---------------+
3 rows in set (0.01 sec)
Q2. Write a SQl block to create a department table and add the constraints department name, department number and department head.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE department(
-> dept_id INT PRIMARY KEY NOT NULL,
-> email VARCHAR(20) NOT NULL,
-> address VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC department;
+---------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+---------+-------------+------+-----+---------+
| dept_id | int | NO | PRI | NULL |
| email | varchar(20) | NO | | NULL |
| address | varchar(20) | NO | | NULL |
+---------+-------------+------+-----+---------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE department
-> ADD COLUMN dept_name VARCHAR(10) NOT NULL,
-> ADD COLUMN dept_number INT NOT NULL,
-> ADD COLUMN dept_head VARCHAR(10) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC department;
+-------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------------+-------------+------+-----+---------+
| dept_id | int | NO | PRI | NULL |
| email | varchar(20) | NO | | NULL |
| address | varchar(20) | NO | | NULL |
| dept_name | varchar(10) | NO | | NULL |
| dept_number | int | NO | | NULL |
| dept_head | varchar(10) | NO | | NULL |
+-------------+-------------+------+-----+---------+
6 rows in set (0.00 sec)
Q3. Write a SQL block to create an employee table and insert the data of all employees, retrieve the date of birth and address of employee whose name is Sushant.
Solution:
mysql> CREATE DATABASE practical;
mysql> use practical;
Database changed
mysql> CREATE TABLE employee(
-> id INT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL,
-> address VARCHAR(20) NOT NULL,
-> dob DATE NOT NULL,
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> DESC employee;
+---------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+---------+-------------+------+-----+---------+
| id | int | NO | PRI | NULL |
| name | varchar(10) | NO | | NULL |
| address | varchar(20) | NO | | NULL |
| dob | date | NO | | NULL |
+---------+-------------+------+-----+---------+
5 rows in set (0.01 sec)
mysql> INSERT INTO employee VALUES(1,'Ram','Delhi','1982-02-10'),(2, 'Shiva','Jaipur','2002-12-01'),(3, 'Sushant','Mumbai','2000-08-06');
Query OK, 3 row affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employee;
+----+---------+---------+------------+
| id | name | address | dob |
+----+---------+---------+------------+
| 1 | Ram | Delhi | 1982-02-10 |
| 2 | Shiva | Jaipur | 2002-12-01 |
| 3 | Sushant | Mumbai | 2000-08-06 |
+----+---------+---------+------------+
3 rows in set (0.00 sec)
mysql> SELECT dob, address from employee WHERE name = 'Sushant';
+------------+---------+
| dob | address |
+------------+---------+
| 1982-02-10 | Delhi |
+------------+---------+
1 row in set (0.00 sec)
Q4. Write a SQL block to create an employee table and retrieve name, address of all employee who works for the research department.
Solution:
mysql> CREATE DATABASE practical;
mysql> use practical;
Database changed
mysql> CREATE TABLE employee(
-> id INT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL,
-> address VARCHAR(10) NOT NULL,
-> department TEXT NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC employee;
+------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+-------------+------+-----+---------+
| id | int | NO | PRI | NULL |
| name | varchar(10) | NO | | NULL |
| address | varchar(20) | NO | | NULL |
| department | text | NO | | NULL |
+------------+-------------+------+-----+---------+
4 rows in set (0.00 sec)
mysql> INSERT INTO employee VALUES(1,'Syam','101 Ram Bagh','IT'),(2,'Sagar','44A Saket Colony','Research'),(3,’Shubh','57A Khandari','Research'),(4,'ABH','Shiv Puram','Finance');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employee;
+----+-------+------------------+------------+
| id | name | address | department |
+----+-------+------------------+------------+
| 1 | Syam | 101 Ram Bagh | IT |
| 2 | Sagar | 44A Saket Colony | Research |
| 3 | Shubh | 57A Khandari | Research |
| 4 | ABH | Shiv Puram | Finance |
+----+-------+------------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT name, address FROM employee WHERE department = 'Research';
+-------+-----------------+
| name | address |
+-------+-----------------+
| Sagar | 5c Saket Colony |
+-------+-----------------+
1 row in set (0.00 sec)
Q5. Make a list of all project number for project that involve an employee whose last name is ‘smith’ as a worker or as a manager of the department that control the project.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE employee(
-> id INT PRIMARY KEY,
-> firstname VARCHAR(10) NOT NULL,
-> lastname VARCHAR(10) NOT NULL,
-> age INT NOT NULL,
-> department TEXT NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC employee;
+------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+-------------+------+-----+---------+
| id | int | NO | PRI | NULL |
| firstname | varchar(10) | NO | | NULL |
| lastname | varchar(10) | NO | | NULL |
| age | int | NO | | NULL |
| department | text | NO | | NULL |
+------------+-------------+------+-----+---------+
5 rows in set (0.01 sec)
mysql> INSERT INTO employee VALUES(1,'Dj','Upraity',19,'IT'),(2,'Krishan','Smith',21,'Service'),(3,'Aman','Smith',18,'Research'),(4,'Happy','Singh',25,'IT'),(5,'Shubh','Kul',18,'Service');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employee;
+----+-----------+----------+-----+------------+
| id | firstname | lastname | age | department |
+----+-----------+----------+-----+------------+
| 1 | Dj | Upraity | 19 | IT |
| 2 | Krishan | Smith | 21 | Service |
| 3 | Aman | Smith | 18 | Research |
| 4 | Happy | Singh | 25 | IT |
| 5 | Shubh | Kul | 18 | Service |
+----+-----------+----------+-----+------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM emp WHERE lastname = 'Smith';
+----+-----------+----------+-----+------------+
| id | firstname | lastname | age | department |
+----+-----------+----------+-----+------------+
| 2 | Krishan | Smith | 21 | Service |
| 3 | Aman | Smith | 18 | Research |
+----+-----------+----------+-----+------------+
2 rows in set (0.00 sec)
Q.6 Create Table student (stu_id numeric(10) primary key, stu_name varchar(10), fathername varchar(10), department varchar(10), gender, fees due numeric, date of birth.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE student(
-> stu_id NUMERIC(10) PRIMARY KEY,
-> stu_name VARCHAR(10),
-> fathername VARCHAR(10),
-> department VARCHAR(10),
-> gender VARCHAR(10),
-> fees_due NUMERIC,
-> dob DATE
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC student;
+------------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+---------------+------+-----+---------+
| stu_id | decimal(10,0) | NO | PRI | NULL |
| stu_name | varchar(10) | YES | | NULL |
| fathername | varchar(10) | YES | | NULL |
| department | varchar(10) | YES | | NULL |
| gender | varchar(10) | YES | | NULL |
| fees_due | decimal(10,0) | YES | | NULL |
| dob | date | YES | | NULL |
+------------+---------------+------+-----+---------+
7 rows in set (0.00 sec)
Q.7 Create table exam id, semester, no of pages, exam fess.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE exam(
-> exam_id INT PRIMARY KEY,
-> semester VARCHAR(20) NOT NULL,
-> no_of_pages INT,
-> exam_fess NUMERIC
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC exam;
+-------------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------------+---------------+------+-----+---------+
| exam_id | int | NO | PRI | NULL |
| semester | varchar(20) | NO | | NULL |
| no_of_pages | int | YES | | NULL |
| exam_fess | decimal(10,0) | YES | | NULL |
+-------------+---------------+------+-----+---------+
4 rows in set (0.00 sec)
Q8. Create a table of marks id, semester, sub1, sub2, sub3, sub4 and total number, average, status, grades.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE marks(
-> id INT PRIMARY KEY,
-> semester VARCHAR(10) NOT NULL,
-> sub1 INT,
-> sub2 INT,
-> sub3 INT,
-> sub4 INT,
-> TotalNumber INT,
-> Average DECIMAL(5,3),
-> Status VARCHAR(10),
-> Grades VARCHAR(10)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC marks;
+--------------+--------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+--------------+--------------+------+-----+---------+
| id | int | NO | PRI | NULL |
| semester | varchar(10) | NO | | NULL |
| sub1 | int | YES | | NULL |
| sub2 | int | YES | | NULL |
| sub3 | int | YES | | NULL |
| sub4 | int | YES | | NULL |
| TotalNumber | int | YES | | NULL |
| Average | decimal(5,3) | YES | | NULL |
| Status | varchar(10) | YES | | NULL |
| Grades | varchar(10) | YES | | NULL |
+--------------+--------------+------+-----+---------+
10 rows in set (0.00 sec)
Q9. Create a table and insert the values in department retrive the name and address of all students who are in IT department.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE students(
-> roll_no INT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL,
-> address VARCHAR(20) NOT NULL,
-> age INT,
-> department VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC students;
+------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+-------------+------+-----+---------+
| roll_no | int | NO | PRI | NULL |
| name | varchar(10) | NO | | NULL |
| address | varchar(20) | NO | | NULL |
| age | int | YES | | NULL |
| department | varchar(10) | YES | | NULL |
+------------+-------------+------+-----+---------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM STUDENTS;
+---------+--------+----------+------+------------+
| roll_no | name | address | age | department |
+---------+--------+----------+------+------------+
| 101 | Khushi | Sector 4 | 18 | IT |
| 111 | Tanu | Ram Bagh | 20 | Finance |
| 121 | Shubh | Patholi | 19 | IT |
+---------+--------+----------+------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM students WHERE department = 'IT';
+---------+--------+----------+------+------------+
| roll_no | name | address | age | department |
+---------+--------+----------+------+------------+
| 101 | Khushi | Sector 4 | 18 | IT |
| 121 | Shubh | Patholi | 19 | IT |
+---------+--------+----------+------+------------+
2 rows in set (0.00 sec)
Q10. Create table and insert the values in student table. Delete the student details where roll number = 3.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE student(
-> roll_no INT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL,
-> course VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC student;
+---------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+---------+-------------+------+-----+---------+
| roll_no | int | NO | PRI | NULL |
| name | varchar(10) | NO | | NULL |
| course | varchar(10) | NO | | NULL |
+---------+-------------+------+-----+---------+
3 rows in set (0.00 sec)
mysql> INSERT INTO student VALUES(2,'ABH','MCA'),(3,'DJ','BCA'),(5,'Abhishek','BBA'),(7,'Nishant','BA'),(9,'Ritu','Bsc');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM student;
+---------+----------+--------+
| roll_no | name | course |
+---------+----------+--------+
| 2 | ABH | MCA |
| 3 | DJ | BCA |
| 5 | Abhishek | BBA |
| 7 | Nishant | BA |
| 9 | Ritu | Bsc |
+---------+----------+--------+
5 rows in set (0.00 sec)
mysql> DELETE FROM stu WHERE roll_no = 3;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM student;
+---------+----------+--------+
| roll_no | name | course |
+---------+----------+--------+
| 2 | ABH | MCA |
| 5 | Abhishek | BBA |
| 7 | Nishant | BA |
| 9 | Ritu | Bsc |
+---------+----------+--------+
4 rows in set (0.00 sec)
Q11. Create table employee with employee name, salary. Find the sum of salary of all employee of manager department as well as maximum and minimum salary.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE employee(
-> id INT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL,
-> salary INT NOT NULL,
-> department VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC employee;
+------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+-------------+------+-----+---------+
| id | int | NO | PRI | NULL |
| name | varchar(10) | NO | | NULL |
| salary | int | NO | | NULL |
| department | varchar(10) | NO | | NULL |
+------------+-------------+------+-----+---------+
4 rows in set (0.00 sec)
mysql> INSERT INTO employee VALUES(501,'Tarun',35000,'Manager'),(502,'Mausam',25000,'HR'),(503,'Adi',35000,'Manager'),(504,'Bhumick',28000,'Finance'),(505,
'Abhay',51000,'CEO');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employee;
+-----+---------+--------+------------+
| id | name | salary | department |
+-----+---------+--------+------------+
| 501 | Tarun | 35000 | Manager |
| 502 | Mausam | 25000 | HR |
| 503 | Adi | 35000 | Manager |
| 504 | Bhumick | 28000 | Finance |
| 505 | Abhay | 51000 | CEO |
+-----+---------+--------+------------+
5 rows in set (0.00 sec)
mysql> SELECT department, SUM(salary) AS TotalSalary FROM employee WHERE department = 'Manager';
+------------+-------------+
| department | TotalSalary |
+------------+-------------+
| Manager | 70000 |
+------------+-------------+
1 row in set (0.01 sec)
mysql> SELECT MIN(salary) AS MinSalary, MAX(salary) AS MaxSalary FROM employee;
+-----------+-----------+
| MinSalary | MaxSalary |
+-----------+-----------+
| 25000 | 51000 |
+-----------+-----------+
1 row in set (0.00 sec)
Q12. Write an SQl query to print details of the workers whose salary between 100000 and 500000.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE worker(
-> name VARCHAR(10) NOT NULL,
-> address VARCHAR(20) NOT NULL,
-> salary INT NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC worker;
+---------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+---------+-------------+------+-----+---------+
| name | varchar(10) | NO | | NULL |
| address | varchar(20) | NO | | NULL |
| salary | int | NO | | NULL |
+---------+-------------+------+-----+---------+
3 rows in set (0.00 sec)
mysql> INSERT INTO worker VALUES('Satyam','Gokul Dham',250000),('Ruchi','Khandari',120000),('Rohit','Ram Bagh',340000),('Karan','Bodla',510000);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM worker;
+--------+------------+--------+
| name | address | salary |
+--------+------------+--------+
| Satyam | Gokul Dham | 250000 |
| Ruchi | Khandari | 120000 |
| Rohit | Ram Bagh | 340000 |
| Karan | Bodla | 510000 |
+--------+------------+--------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM worker WHERE salary BETWEEN 100000 AND 500000;
+--------+------------+--------+
| name | address | salary |
+--------+------------+--------+
| Satyam | Gokul Dham | 250000 |
| Ruchi | Khandari | 120000 |
| Rohit | Ram Bagh | 340000 |
+--------+------------+--------+
3 rows in set (0.00 sec)
Q13. Write an SQL query to print details of the workers who joined in feb 2021.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE workers(
-> name VARCHAR(10) NOT NULL,
-> salary INT NOT NULL,
-> address VARCHAR(20) NOT NULL,
-> join_date DATE NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC workers;
+-----------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------+-------------+------+-----+---------+
| name | varchar(10) | NO | | NULL |
| salary | int | NO | | NULL |
| address | varchar(20) | NO | | NULL |
| join_date | date | NO | | NULL |
+-----------+-------------+------+-----+---------+
4 rows in set (0.00 sec)
mysql> INSERT INTO worKers (NAME, ADDRESS, SALARY, JOIN_DATE) VALUES('Satyam','Gokul Dham',250000,'2020-03-09'),('Ruchi','Khandari',120000,'2021-02-02'),('Rohit','Ram Bagh',340000,'2021-09-01'),('Karan','Bodla',510000,'2022-02-10');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM workers;
+--------+--------+------------+------------+
| name | salary | address | join_date |
+--------+--------+------------+------------+
| Satyam | 250000 | Gokul Dham | 2020-03-09 |
| Ruchi | 120000 | Khandari | 2021-02-02 |
| Rohit | 340000 | Ram Bagh | 2021-09-01 |
| Karan | 510000 | Bodla | 2022-02-10 |
+--------+--------+------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM workers WHERE MONTH(join_date) = 2 AND YEAR(join_date) = 2021;
+-------+--------+----------+------------+
| name | salary | address | join_date |
+-------+--------+----------+------------+
| Ruchi | 120000 | Khandari | 2021-02-02 |
+-------+--------+----------+------------+
1 row in set (0.00 sec)
Q14. Write an SQL query to fetch the count of employee working in the department 'Admin'.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE workers(
-> name VARCHAR(10) NOT NULL,
-> salary INT NOT NULL,
-> address VARCHAR(20) NOT NULL,
-> department VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC workers;
+------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+-------------+------+-----+---------+
| name | varchar(10) | NO | | NULL |
| salary | int | NO | | NULL |
| address | varchar(20) | NO | | NULL |
| department | varchar(10) | NO | | NULL |
+------------+-------------+------+-----+---------+
4 rows in set (0.00 sec)
mysql> INSERT INTO worKers (name, address, salary, join_date, department) VALUES('Satyam','Gokul Dham',500000,'2020-03-09','admin'),('Ruchi','Khandari',120000,'2021-02-02','worker'),('Rohit','Ram Bagh',140000,'2021-09-01','worker'),('Karan','Bodla',510000,'2022-02-10','admin');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM workers;
+--------+--------+------------+------------+------------+
| name | salary | address | join_date | department |
+--------+--------+------------+------------+------------+
| Satyam | 500000 | Gokul Dham | 2020-03-09 | admin |
| Ruchi | 120000 | Khandari | 2021-02-02 | NULL |
| Rohit | 140000 | Ram Bagh | 2021-09-01 | worker |
| Karan | 510000 | Bodla | 2022-02-10 | admin |
+--------+--------+------------+------------+------------+
8 rows in set (0.00 sec)
mysql> SELECT COUNT(*) AS TotalWorkers FROM workers WHERE department = 'admin';
+--------------+
| TotalWorkers |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
Q15. Write an SQL query to fetch intersecting records of two tables.
Solution:
mysql> CREATE DATABASE practical;
mysql> USE practical;
Database changed
mysql> CREATE TABLE worker(
-> id INT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL,
-> address VARCHAR(10) NOT NULL,
-> department VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> DESC worker;
+------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+-------------+------+-----+---------+
| id | int | NO | PRI | NULL |
| name | varchar(10) | NO | | NULL |
| address | varchar(10) | NO | | NULL |
| department | varchar(10) | NO | | NULL |
+------------+-------------+------+-----+---------+
3 rows in set (0.01 sec)
INSERT INTO worker VALUES(71,'Sagar','Lohamandi','IT'),(81,'Shujal','Ujrai','Finance'),(91,'Iyer','Agra','HR');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM worker;
+----+--------+-----------+------------+
| id | name | address | department |
+----+--------+-----------+------------+
| 71 | Sagar | Lohamandi | IT |
| 81 | Shujal | Ujrai | Finance |
| 91 | Iyer | Agra | HR |
+----+--------+-----------+------------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE WorkerClone(
-> id INT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL,
-> address VARCHAR(10) NOT NULL,
-> department VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC WorkerClone;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| address | varchar(10) | NO | | NULL | |
| department | varchar(10) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> INSERT INTO WorkerClone VALUES(51,'Ram','Shiv Nagar','HR'),(61,'Krishna','Vrindravan','IT'),(71,'Sagar','Lohamandi','IT');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM WorkerClone;
+----+---------+------------+------------+
| id | name | address | department |
+----+---------+------------+------------+
| 51 | Ram | Shiv Nagar | HR |
| 61 | Krishna | Vrindravan | IT |
| 71 | Sagar | Lohamandi | IT |
+----+---------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM worker INTERSECT SELECT * FROM WorkerClone;
+----+-------+-----------+------------+
| ID | name | address | department |
+----+-------+-----------+------------+
| 71 | Sagar | Lohamandi | IT |
+----+-------+-----------+------------+
1 row in set (0.00 sec)