Scribd
Upload
14 views4 pages

Assignment SQL

The document outlines an assignment on Oracle SQL covering DDL, DML, and constraints. It includes tasks such as creating tables with specific constraints, inserting and deleting records, and understanding constraint behaviors. Additionally, it discusses the differences between DELETE and TRUNCATE commands and the importance of constraints in maintaining data integrity in a database.

Uploaded by

sohamhazra0005
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views4 pages

Assignment SQL

The document outlines an assignment on Oracle SQL covering DDL, DML, and constraints. It includes tasks such as creating tables with specific constraints, inserting and deleting records, and understanding constraint behaviors. Additionally, it discusses the differences between DELETE and TRUNCATE commands and the importance of constraints in maintaining data integrity in a database.

Uploaded by

sohamhazra0005
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Assignment: Oracle SQL (DDL, DML & Constraints)

Part A – DDL & Constraints

Question 1

Create a table STUDENT with the following constraints:

Column Data Type Constraint


RollNo NUMBER PRIMARY KEY
Name VARCHAR2(50) NOT NULL
Email VARCHAR2(100) UNIQUE
Age NUMBER CHECK (Age ≥ 18)
DeptID NUMBER FOREIGN KEY

Solution

CREATE TABLE Department (


DeptID NUMBER PRIMARY KEY,
DeptName VARCHAR2(30)
);

CREATE TABLE Student (


RollNo NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Email VARCHAR2(100) UNIQUE,
Age NUMBER CHECK (Age >= 18),
DeptID NUMBER,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

Question 2

Add a column Phone to STUDENT table and ensure it is unique.

Solution

ALTER TABLE Student


ADD (Phone VARCHAR2(15) UNIQUE);

Question 3

Drop the column Phone from STUDENT table.

Solution

ALTER TABLE Student DROP COLUMN Phone;


Part B – DML Operations

Question 4

Insert the following records into Department table:

DeptID DeptName
10 CSE
20 ECE

Solution

INSERT INTO Department VALUES (10, 'CSE');


INSERT INTO Department VALUES (20, 'ECE');

Question 5

Insert three student records.

Solution

INSERT INTO Student VALUES (101, 'Anita', 'anita@[Link]', 20, 10);


INSERT INTO Student VALUES (102, 'Rahul', 'rahul@[Link]', 22, 20);
INSERT INTO Student VALUES (103, 'Meena', 'meena@[Link]', 19, 10);

Question 6

Display all students from CSE department.

Solution

SELECT *
FROM Student
WHERE DeptID = 10;

Question 7

Update marks of student with RollNo = 101 to 90


(Assume Marks column exists).

Solution

UPDATE Student
SET Marks = 90
WHERE RollNo = 101;

Question 8
Delete student whose RollNo is 103.

Solution

DELETE FROM Student


WHERE RollNo = 103;

Part C – Constraint Behavior

Question 9

Try inserting a student with Age = 16. What happens?

Solution

INSERT INTO Student VALUES (104, 'Kiran', 'kiran@[Link]', 16, 10);

Result:
Error due to CHECK constraint violation
(Age must be ≥ 18)

Question 10

Try inserting two students with the same Email.

Solution

INSERT INTO Student VALUES (105, 'Asha', 'asha@[Link]', 20, 10);


INSERT INTO Student VALUES (106, 'Ravi', 'asha@[Link]', 21, 20);

Result:
Error due to UNIQUE constraint violation

Part D – Conceptual Questions

Question 11

Differentiate between DELETE and TRUNCATE.

Solution

DELETE TRUNCATE
DML command DDL command
Can be rolled back Cannot be rolled back
Deletes selected rows Deletes all rows
Question 12

Why are constraints important in DBMS?

Solution

Constraints enforce data integrity, prevent invalid data entry, and maintain
consistency in the database.

Practice Tasks

1. Create a table EMPLOYEE with DEFAULT and NOT NULL constraints


2. Use COMMIT and ROLLBACK with DML commands
3. Create a composite primary key
4. Add ON DELETE CASCADE to a foreign key

You might also like