lab1

1.

CREATE TABLE department(
deptid NUMBER(3) CONSTRAINT dept_id_pk PRIMARY KEY,
dept_name VARCHAR (6) CONSTRAINT name_ck CHECK(dept_name IN('CSE','EEE','BBA','Eng','Ach')),
budget NUMBER(6) DEFAULT 0
)

2.
CREATE TABLE course(
crs_id NUMBER(4) CONSTRAINT crs_id_pk PRIMARY KEY,
crs_name VARCHAR2(6) NOT NULL,
deptid NUMBER(3) CONSTRAINT fk_deptid REFERENCES department(deptid)
)

EDIT PART

1.
CREATE TABLE test(
s_id    NUMBER(3),
s_name  VARCHAR2(20),
Phone NUMBER(11),
Address VARCHAR2(50),
Email   VARCHAR2(30),
credit_completed    NUMBER(3),
course_completed    NUMBER(2),
Cgpa    NUMBER(3,2),
Deptno  NUMBER(5),
Gender  VARCHAR2(6)
)

2.ALTER TABLE test ADD CONSTRAINT pk_s_id PRIMARY KEY (s_id)
3.ALTER TABLE test MODIFY s_name VARCHAR2(20) NOT NULL
4.ALTER TABLE test MODIFY email VARCHAR2(30) UNIQUE
5.ALTER TABLE test ADD CONSTRAINT ck_dptid foreign KEY(Deptno) REFERENCES department(deptid)
6.ALTER TABLE test ADD CONSTRAINT ck_gender CHECK(gender IN('M','F'))
7.ALTER TABLE test disable CONSTRAINT pk_s_id
8.ALTER TABLE test DROP CONSTRAINT ck_gender
9.SELECT constraint_name, column_name FROM user_cons_columns WHERE
table_name = 'TEST'
10.ALTER TABLE test enable CONSTRAINT pk_s_id