Categories
Uncategorized

Query(3)

1. Perform below specified DDL operations:

  • Create a table of all the students attending this class which contains their student_id, name, department code and email id. The department code should be a valid one having reference in the existing dept table in the college schema.Ensure that the email id of all the students should be distinct in the table.And there should be one unique_id for every student. It can’t be null.
  • Create another table by name NEWDEPT from DEPT table’s deptno, dname Columns and another column by name dept_head.

Apply all the given constraints properly.

o Perform below specified operations with these tables.

      • Rename the table.
      • Add one column (sex) to that table which contains either M or F.
      • Drop column from the table.
      • Drop the table.

Queries

sql>create table stu(student_id varchar2(20) primary key,name varchar2(20),department_code varchar2(20), email varchar2(20) unique,foreign key(department_code) references colldept(deptno);

sql>desc stu;

SQL>create table newdept( dname, deptno,dept_head) as select dname,deptno, hod from colldept;

SQL>rename stu to stud;

SQL>alter table stud add constraints column sex varchar2(20) check (sex in (‘Male’,’Female’);

SQL>alter table stud drop column sex;

SQL>drop table stud;