Ex-1.md

Ex-1 CREATING DATABASE TABLE

Department table

Q1) Create the tables DEPT and EMP as described below

CREATE TABLE DEPT (
	DEPTNO int,
	DNAME varchar(255),
	LOC varchar(50)
);

CREATE TABLE EMP (
	EMPNO int,
	ENAME varchar(255),
	JOB char(30),
	MGR int,
	HIREDATE DATE,
	SAL int,
	COMM int,
	DEPTNO int
);

Q2) Confirm table creation

DESC DEPT;

DESC EMP;

Q7) Add new columns COMNT and MISCEL in DEPT table of character type.

ALTER TABLE DEPT ADD COMNT char(50);

ALTER TABLE DEPT ADD MISCEL char(50);

Q8) Modify the size of column LOC by 15 in the DEPT table

ALTER TABLE DEPT MODIFY LOC varchar(15);

Q9) Set MISCEL column in the DEPT table as unused

ALTER TABLE DEPT SET UNUSED(MISCEL);

Q10) Drop the column COMNT from the table DEPT

ALTER TABLE DEPT DROP COLUMN COMNT;

Q12) Rename the table DEPT to DEPT12

ALTER TABLE DEPT RENAME TO DEPT12;

Q13) Remove all the rows in the table DEPT12 (Presently no records in DEPT12)

TRUNCATE TABLE DEPT12;

Q11) Drop unused columns in DEPT table

DROP TABLE DEPT

Employee table

create table Employee(name varchar(20), empid int, dept varchar(20), phone_num int);

alter table Employee add(DOJ date,position varchar(20));

alter table Employee modify name varchar(25);

desc Employee;

ALTER TABLE Employee rename column position to Designation;

desc Employee;

alter table Employee rename to Emp_table;