Scribd
Upload
7 views4 pages

Program 2-1

The document outlines a series of SQL commands to manage an Employee table in a MySQL database. It includes steps for creating the table, adding a commission column, inserting records, updating job details, renaming a column, and deleting a specific employee. Each operation is accompanied by the corresponding SQL syntax and execution commands.

Uploaded by

fujitora2503
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views4 pages

Program 2-1

The document outlines a series of SQL commands to manage an Employee table in a MySQL database. It includes steps for creating the table, adding a commission column, inserting records, updating job details, renaming a column, and deleting a specific employee. Each operation is accompanied by the corresponding SQL syntax and execution commands.

Uploaded by

fujitora2503
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Program 2

Create a table called Employee that contain attributes EMPNO,ENAME,JOB,


MGR,SAL & execute the following.
1. Add a column commission with domain to the Employee table.
2. Insert any five records into the table.
3. Update the column details of job
4. Rename the column of Employee table using alter command.
5. Delete the employee whose Empno is 105.

Solution:

Before performing any operations, we need to log in to MySQL, create a database, and switch
to that database.

Open the Terminal. Enter the following command to log in:


mysql -u root -p
OR
sudo mysql
Enter the password when prompted.

Before creating the Employee table, a database must be created.


CREATE DATABASE Database_Name;

To use this database, execute:


USE Database_Name;
Create table as follow:
CREATE TABLE Employee (
EMPNO INT,
ENAME VARCHAR(50),
JOB VARCHAR(50),
MANAGER_NO INT,
SAL DECIMAL(10,2)
);

Part 1:

Execute the following command to check the schema of the table:


describe Employee;

Syntax to add a column in already existing relation:


ALTER TABLE table_name ADD COLUMN column_name datatype;

ALTER TABLE Employee ADD COLUMN Commission DECIMAL(10, 2);

Execute the following command to check the updates:


describe Employee;

Part 2:

INSERT INTO Employee VALUES (101, 'Radha Bai', 'Manager',


NULL, 5000.00, 1000.00), (102, 'Krishna Kumar', 'Developer',
101, 4000.00, NULL), (103, 'Abdul Sattar', 'Salesperson', 102,
3000.00, 500.00), (104, 'Bob Johnson', 'Accountant', 101,
4500.00, NULL), (105, 'Amartya Sen', 'HR Manager', 101,
4800.00, 800.00);

Execute:
select * from Employee;
Part 3:

Syntax to update a column value in already existing relation:


UPDATE table_name SET column1=value1, column2=value2,… WHERE condition;

UPDATE Employee SET JOB = 'Senior Developer' WHERE EMPNO =


102;

Execute:
select * from Employee;

Part 4:

Syntax to rename column in already existing relation:


ALTER TABLE table_name RENAME COLUMN old_column_name TO new_table_name
datatype;

ALTER TABLE Employee RENAME COLUMN MANAGER_NO TO MGR;

Execute the following command to check the updates:


describe Employee;
Part 5:

Syntax to delete a row:


DELETE FROM table_name WHERE condition;

DELETE FROM Employee WHERE EMPNO = 105;

Execute:
select * from Employee;

You might also like