Assignment 7 (SQL / DDL / Constraints)

SQL / DDL / Constraints

✅ DDL Commands

These commands modify the structure of the database.

• CREATE – create tables, views, etc.

• ALTER – modify existing table structure.

• DROP – delete table or column.

• RENAME – rename table or column.

• TRUNCATE – remove all rows quickly.

✅ Constraints

Used to enforce rules on table columns.

📌 USER_CONSTRAINTS

To view constraints applied to your tables:

SELECT constraint_name, constraint_type, table_name 
FROM user_constraints;

📝 Assignment – Clean & Complete Solutions

Create The emp_new table

Create the dept table

1️⃣ Create emp_new table

CREATE TABLE emp_new (
    id        NUMBER(7),
    last_name VARCHAR2(25),
    first_name VARCHAR2(25),
    dept_id   NUMBER(7)
);

2️⃣ Create dept table

CREATE TABLE dept (
    dept_id NUMBER(10),
    dname   VARCHAR2(20)
);

3️⃣ Modify emp_new: increase last_name by 50

ALTER TABLE emp_new
MODIFY last_name VARCHAR2(75);

4️⃣ Drop first_name column

ALTER TABLE emp_new
DROP COLUMN first_name;

✔️ Check table description:

DESC emp_new;

5️⃣ Add new column salary to emp_new

ALTER TABLE emp_new
ADD salary NUMBER(10);

6️⃣ Rename dept_id column to deptno

ALTER TABLE emp_new
RENAME COLUMN dept_id TO deptno;

7️⃣ Rename emp_new table to employee

RENAME emp_new TO employee;

8️⃣ Create primary key on dept table (name: my_dept_id_pk)

ALTER TABLE dept
ADD CONSTRAINT my_dept_id_pk PRIMARY KEY (dept_id);

9️⃣ Add foreign key in employee table

Constraint name: my_emp_dept_id_fk

ALTER TABLE employee
ADD CONSTRAINT my_emp_dept_id_fk
FOREIGN KEY (deptno) 
REFERENCES dept(dept_id);

🔟 Modify last_name & first_name to NOT NULL

Since first_name was dropped earlier, we only update last_name.

ALTER TABLE employee
MODIFY last_name VARCHAR2(75) NOT NULL;

1️⃣1️⃣ Confirm all constraints

SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name IN ('EMPLOYEE', 'DEPT');