EXPT4.md

INTEGRITY CONSTRAINTS

Q1)

CREATE TABLE DEPT (
  DEPT_NO NUMBER(5) PRIMARY KEY,
  DNAME VARCHAR(10),
  DLOC VARCHAR(10)
);

Q2)

CREATE TABLE EMP (
  EMP_NO NUMBER(5) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR(10),
  JOB VARCHAR(10),
  MANAGER_NAME VARCHAR(10) DEFAULT 'Mr.K. RAM',
  HIRE_DATE DATE,
  SALARY NUMBER(30),
  COMMISSION NUMBER(30),
  DEPT_NO NUMBER(5)
);

Q3)

ALTER TABLE DEPT ADD CONSTRAINT UQ_DNAME UNIQUE (DNAME);

Q4)

ALTER TABLE EMP MODIFY HIRE_DATE DATE NOT NULL;

Q5)

ALTER TABLE EMP ADD CONSTRAINT CHK_SALARY_RANGE CHECK (SALARY BETWEEN 10000 AND 20000);

Q6)

ALTER TABLE EMP ADD CONSTRAINT FK_DEPTNO_DEPT FOREIGN KEY (DEPT_NO) REFERENCES DEPT(DEPT_NO);

Q7)

ALTER TABLE EMP ADD CONSTRAINT CHK_COMMISSION CHECK (COMMISSION < 0.1 * SALARY);

Q8)

INSERT INTO DEPT (DEPT_NO, DNAME, DLOC) VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO EMP (EMP_NO, ENAME, JOB, HIRE_DATE, SALARY, COMMISSION, DEPT_NO) VALUES (7369, 'SMITH', 'CLERK', '17-DEC-80', 11800, NULL, 10);

Q9)

INSERT INTO EMP (EMP_NO, ENAME, JOB, HIRE_DATE, SALARY, COMMISSION, DEPT_NO) VALUES (7499, 'ALLEN', 'SALESMAN', '20-FEB-81', 11600, NULL, 30);

OUTPUT

Error: FOREIGN KEY constraint failed

Q10)

ALTER TABLE EMP DROP CONSTRAINT PK_EMP;