Create department table with the following structure. Name Type Deptno Number Deptname Varchar2(10) location Varchar2(10)

Create department table with the following structure.
Name Type
Deptno Number
Deptname Varchar2(10)
location Varchar2(10)

1. Add column designation to the department table.
2. Insert values into the table.
3. List the records of dept table grouped by deptno.
4. Update the record where deptno is 9.
5. Delete any column data from the table.

Answer:

create table department(Deptno Number, Deptname Varchar2(10), location Varchar2(10) );

1.

 Alter table department add designation varchar2(20);

2. 

insert into department (Deptno,Deptname,location,designation) values(1,'Coding','Ludhiana','Developer');

insert into department (Deptno,Deptname,location,designation) values(5,'Softskill','Ludhiana','Professor');

insert into department (Deptno,Deptname,location,designation) values(3,'Apptitude','Ludhiana','Professor');

insert into department (Deptno,Deptname,location,designation) values(9,'Database','Ludhiana','Creater');

insert into department (Deptno,Deptname,location,designation) values(10,'IOT','Ludhiana','Learners');

3.

SELECT Deptno,COUNT(*) AS "Total Workers" FROM department where location='Ludhiana' GROUP BY Deptno;

4.

Select * from department;

UPDATE department SET Deptname = 'DBMS', designation = 'Developer' WHERE Deptno=9;

Select * from department;

 

Select * from department;

ALTER TABLE department DROP COLUMN designation;

Select * from department;