This file contains the procedure for 2nd Practical of Computer.

practical_2.sql

USE C4042;

CREATE TABLE Employee(
Empno INT,
FirstName VARCHAR(20),
LastName VARCHAR(20),
Address VARCHAR(100),
City VARCHAR(25),
Desgn VARCHAR(25),
DeptId INT,
Salary DECIMAL(9,2),
CONSTRAINT PK PRIMARY KEY(Empno)
);
CREATE TABLE Department(
DeptId INT PRIMARY KEY,
DeptName VARCHAR(25),
City VARCHAR(25) 
);

 
DESC Employee;
DESC Department;
INSERT INTO Department VALUES(1,'IT','Pune');
INSERT INTO Department VALUES(2,'Research','Pune');
INSERT INTO Department VALUES(3,'Accounts','Bengaluru');



INSERT INTO Employee VALUES(1,'Abhay','Prajapati','123 Main St','Boston', 'IT Person',1,1500);
INSERT INTO Employee VALUES(2,'Ravi','Meh','Dadar','Mumbai', 'Programmer',1,2424);
INSERT INTO Employee VALUES(3,'Rahul','Shah','Anderi','Mumbai', 'Network Instructor',1,3500);
INSERT INTO Employee VALUES(4,'Raj','Singh','Bandra','Mumbai', 'Design',1,2000);
INSERT INTO Employee VALUES(5,'Rajesh','Kumar','Koramangalam','Bengaluru', 'Electronics',2,200);
INSERT INTO Employee VALUES(6,'Ayush','Saini','Electronic','City', 'Frontend',2,400);
INSERT INTO Employee VALUES(7,'Ramesh','Kapoor','Pune','Pune', 'Sales',2,1000);
INSERT INTO Employee VALUES(8,'Anshu','Maheshwari','12t street','Pune', 'Accountant',3,2560); 
INSERT INTO Employee VALUES(9,'Tony','Stark','Main St','New York', 'Tax',3,3000);
INSERT INTO Employee VALUES(10,'Naval','Ravikant','Shinaginagar','Bangalore', 'Auditor',3,2000);


UPDATE Employee SET LastName='Mehta' WHERE Empno=3;

UPDATE Department SET City='Nasik' WHERE DeptId=2;

SELECT * FROM Employee;
SELECT * FROM Department;

Questions for Practical 2:

Title : Creating table and Working with the data

Open the already created database and Create table called Employee containing following columns

EmpInteger
FirstName Varchar(20)
LastName Varchar(20)
Address Varchar(100)
City Varchar(25) should not be empty
Desgn Varchar(25) should not be empty
DeptId Integer
Salary Decimal(9,2)
Add Primary key constraint to Empno.

Create table called Department containing following columns

DeptId Integer Primary Key
DeptName Varchar(25) should be unique
City Varchar(25) should not be empty

Aims & Objectives

Write MySQL statements for the following

• Display the structure of both the tables.
• Add 3 rows to the Department table.
• Add 10 rows to the Employee table.
• Change the last name of Empno 3 to Mehta in the table employee.
• Change the City of Deptid 2 to Nasik in the table Department.
• View the data from both the tables.

Procedure: