DBMS Experiment No.4

 Schema for the Given table in Database Exp4S1.db, Exp4S2.db, Exp4S3.db

CREATE TABLE EMP (

    EMPNO INT PRIMARY KEY,

    ENAME VARCHAR(50),

    DNAME VARCHAR(50),

    JOB VARCHAR(50),

    HIREDATE DATE,

    LOC VARCHAR(50)

);

Solution:

1. What is a CHECK constraint in DBMS?
A CHECK constraint ensures that all values in a column meet a specific condition (e.g., age > 18).

---

2. What is referential integrity constraint in DBMS?
Referential integrity ensures that a foreign key value in one table matches a primary key in another table, maintaining data consistency.

---

3. What are the differences between DELETE, DROP, and TRUNCATE commands in SQL?

• DELETE: Removes specific rows; can be rolled back.

• TRUNCATE: Removes all rows; faster than DELETE; cannot be rolled back in most DBMSs.

• DROP: Deletes the entire table structure and data permanently.

Solution:

1. How can you insert the given employee data into the EMP table using different INSERT command syntaxes?

Answer:

-- Using full column list
INSERT INTO EMP (EMPNO, ENAME, DNAME, JOB, HIREDATE, LOC)
VALUES (7876, 'ADAMS', 'RESEARCH', 'CLERK', '1987-05-23', 'DALLAS');

-- Using partial column list
INSERT INTO EMP (EMPNO, ENAME, DNAME, JOB, HIREDATE, LOC)
VALUES (7499, 'ALLEN', 'SALES', 'SALESMAN', '1981-02-20', 'CHICAGO');

-- Inserting using positional values (assuming correct column order)
INSERT INTO EMP 
VALUES (7698, 'SMITH', 'SALES', 'MANAGER', '1981-05-01', 'CHICAGO');

-- Using full column list again
INSERT INTO EMP (EMPNO, ENAME, DNAME, JOB, HIREDATE, LOC)
VALUES (7782, 'CLARK', 'ACCOUNTING', 'MANAGER', '1981-06-09', 'NEW YORK');

Solution:
2. How can you insert multiple employee records into the EMP table using a single INSERT command?
Answer:
INSERT INTO EMP (EMPNO, ENAME, DNAME, JOB, HIREDATE, LOC)
VALUES 
(7902, 'FORD', 'RESEARCH', 'ANALYST', '1981-12-03', 'DALLAS'),
(7900, 'JAMES', 'SALES', 'CLERK', '1981-12-03', 'CHICAGO'),
(7566, 'JONES', 'RESEARCH', 'MANAGER', '1981-04-02', 'DALLAS'),
(7839, 'KING', 'ACCOUNTING', 'PRESIDENT', '1981-11-17', 'NEW YORK');




3. How do you delete the record of SMITH from the EMP table?
DELETE FROM EMP WHERE ENAME = 'SMITH';


4. How do you change the job of ADAMS to MANAGER in the EMP table?
UPDATE EMP SET JOB = 'MANAGER' WHERE ENAME = 'ADAMS';


5. How do you display the contents of EMPNO and SAL columns from the EMP table?
SELECT EMPNO, SAL FROM EMP;