1.
Create a Table with Constraints
Question: Create a table students with student_id as the primary key, name as NOT
NULL, and age with a check constraint (age > 18).
Solution:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT CHECK (age > 18)
);
2. Insert Data into a Table
Question: Insert a new student into the students table with student_id = 1, name =
'John Doe', and age = 20.
Solution:
sql
CopyEdit
INSERT INTO students (student_id, name, age) VALUES (1, 'John Doe', 20);
3. Select Data
Question: Retrieve all students who are older than 21.
Solution:
sql
CopyEdit
SELECT * FROM students WHERE age > 21;
4. Update Data
Question: Update the age of John Doe to 22.
Solution:
sql
CopyEdit
UPDATE students SET age = 22 WHERE student_id = 1;
5. Delete Data
Question: Delete a student with student_id = 1.
Solution:
sql
CopyEdit
DELETE FROM students WHERE student_id = 1;
6. Create a View
Question: Create a view that displays only student_id and name from students.
Solution:
sql
CopyEdit
CREATE VIEW student_view AS
SELECT student_id, name FROM students;
7. Use of GROUP BY
Question: Find the count of students in each age group.
Solution:
sql
CopyEdit
SELECT age, COUNT(*) FROM students GROUP BY age;
8. Use of HAVING Clause
Question: Find age groups with more than 2 students.
Solution:
sql
CopyEdit
SELECT age, COUNT(*) FROM students GROUP BY age HAVING COUNT(*) > 2;
9. Create a Stored Procedure
Question: Create a stored procedure to insert a new student.
Solution:
sql
CopyEdit
CREATE PROCEDURE add_student (
p_student_id INT,
p_name VARCHAR,
p_age INT
) AS
BEGIN
INSERT INTO students (student_id, name, age) VALUES (p_student_id,
p_name, p_age);
END;
10. Execute a Stored Procedure
Question: Execute the stored procedure add_student.
Solution:
sql
CopyEdit
EXEC add_student(2, 'Alice', 21);
11. Create a Trigger
Question: Create a trigger to prevent deleting students under age 20.
Solution:
sql
CopyEdit
CREATE TRIGGER prevent_delete
BEFORE DELETE ON students
FOR EACH ROW
WHEN (OLD.age < 20)
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Cannot delete students under 20');
END;
12. Encrypt a Column
Question: Use Oracle's DBMS_CRYPTO to encrypt name.
Solution:
sql
CopyEdit
SELECT DBMS_CRYPTO.ENCRYPT('John Doe', DBMS_CRYPTO.DES_CBC_PKCS5,
'mysecretkey') FROM DUAL;
13. Create a PL/SQL Function
Question: Create a function to return student name given student_id.
Solution:
sql
CopyEdit
CREATE FUNCTION get_student_name (p_student_id INT) RETURN VARCHAR IS
v_name VARCHAR(100);
BEGIN
SELECT name INTO v_name FROM students WHERE student_id = p_student_id;
RETURN v_name;
END;
14. Use of Foreign Key
Question: Create a courses table with a foreign key referencing students.
Solution:
sql
CopyEdit
CREATE TABLE courses (
course_id INT PRIMARY KEY,
student_id INT REFERENCES students(student_id),
course_name VARCHAR(50)
);
15. Use of UNION
Question: Get a list of unique students and teachers.
Solution:
sql
CopyEdit
SELECT name FROM students
UNION
SELECT name FROM teachers;
16. Create an Index
Question: Create an index on name.
Solution:
sql
CopyEdit
CREATE INDEX idx_name ON students(name);
17. Use of INNER JOIN
Question: Get student names with their enrolled courses.
Solution:
sql
CopyEdit
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.student_id = courses.student_id;
18. PL/SQL Exception Handling
Question: Handle division by zero error.
Solution:
sql
CopyEdit
BEGIN
DECLARE x INT := 10;
DECLARE y INT := 0;
DECLARE result INT;
BEGIN
result := x / y;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;
END;
19. Implement Role-Based Access Control
Question: Create a role and grant SELECT on students.
Solution:
sql
CopyEdit
CREATE ROLE student_viewer;
GRANT SELECT ON students TO student_viewer;
20. XML Data Storage
Question: Store student data in XML format.
Solution:
xml
CopyEdit
<students>
<student>
<id>1</id>
<name>John Doe</name>
<age>20</age>
</student>
</students>
21. DTD for Student Data
Solution:
xml
CopyEdit
<!DOCTYPE students [
<!ELEMENT students (student+)>
<!ELEMENT student (id, name, age)>
<!ELEMENT id (#PCDATA)>
<!ELEMENT name (#PCDATA)>
<!ELEMENT age (#PCDATA)>
]>
22. SQL Query with EXISTS
Question: Find students who have enrolled in courses.
Solution:
sql
CopyEdit
SELECT * FROM students
WHERE EXISTS (SELECT 1 FROM courses WHERE students.student_id =
courses.student_id);
23. Use of Public Key Encryption
Solution:
sql
CopyEdit
SELECT DBMS_CRYPTO.ENCRYPT('SecretMessage', DBMS_CRYPTO.RSA_PKCS1,
'public_key') FROM DUAL;
24. Difference Between Data Warehouse and View
Answer:
Data Warehouse: Stores large historical data optimized for analytics.
View: A virtual table that displays a subset of data.
25. Use of Flow Control in SQL
Question: Write an IF condition in PL/SQL.
Solution:
sql
CopyEdit
DECLARE v_age INT;
BEGIN
SELECT age INTO v_age FROM students WHERE student_id = 1;
IF v_age > 21 THEN
DBMS_OUTPUT.PUT_LINE('Student is above 21');
ELSE
DBMS_OUTPUT.PUT_LINE('Student is 21 or younger');
END IF;
END;
26. Create a Composite Primary Key
Question: Create a table enrollments where student_id and course_id together act as the
primary key.
Solution:
sql
CopyEdit
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enrollment_date DATE,
PRIMARY KEY (student_id, course_id)
);
27. Use of LEFT JOIN
Question: Retrieve all students and their enrolled courses, including students who haven't
enrolled in any course.
Solution:
sql
CopyEdit
SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.student_id = courses.student_id;
28. Use of RIGHT JOIN
Question: Retrieve all courses and their enrolled students, including courses with no
students.
Solution:
sql
CopyEdit
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.student_id = courses.student_id;
29. Use of FULL OUTER JOIN
Question: Retrieve all students and courses, including students without courses and courses
without students.
Solution:
sql
CopyEdit
SELECT students.name, courses.course_name
FROM students
FULL OUTER JOIN courses ON students.student_id = courses.student_id;
30. PL/SQL Loop Example
Question: Print numbers from 1 to 5 using a loop in PL/SQL.
Solution:
sql
CopyEdit
DECLARE i INT := 1;
BEGIN
WHILE i <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
END LOOP;
END;
31. Create a Sequence
Question: Create a sequence student_seq to auto-increment student IDs.
Solution:
sql
CopyEdit
CREATE SEQUENCE student_seq START WITH 1 INCREMENT BY 1;
32. Use of Sequence in INSERT Statement
Question: Insert a new student using student_seq for auto-increment.
Solution:
sql
CopyEdit
INSERT INTO students (student_id, name, age) VALUES (student_seq.NEXTVAL,
'Bob', 22);
33. Use of SUBQUERY
Question: Retrieve students who are enrolled in 'Math' course.
Solution:
sql
CopyEdit
SELECT * FROM students
WHERE student_id IN (SELECT student_id FROM courses WHERE course_name =
'Math');
34. PL/SQL Cursor Example
Question: Fetch and display all student names using a cursor.
Solution:
sql
CopyEdit
DECLARE
CURSOR student_cursor IS SELECT name FROM students;
v_name students.name%TYPE;
BEGIN
OPEN student_cursor;
LOOP
FETCH student_cursor INTO v_name;
EXIT WHEN student_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE student_cursor;
END;
35. Create a User and Grant Privileges
Question: Create a new database user exam_user with privileges to SELECT and INSERT on
students.
Solution:
sql
CopyEdit
CREATE USER exam_user IDENTIFIED BY password;
GRANT SELECT, INSERT ON students TO exam_user;
36. Use of CASE Statement
Question: Write an SQL query to classify students based on their age:
‘Minor’ if age < 18
‘Adult’ if age between 18 and 25
‘Senior’ if age > 25
Solution:
sql
CopyEdit
SELECT name, age,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age BETWEEN 18 AND 25 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM students;
37. Use of COALESCE
Question: Replace NULL course names with 'Not Assigned' in the courses table.
Solution:
sql
CopyEdit
SELECT student_id, COALESCE(course_name, 'Not Assigned') AS course_name
FROM courses;
38. Create a Table with Default Values
Question: Create a students table where enrollment_date has a default value of the
current date.
Solution:
sql
CopyEdit
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT CHECK (age > 18),
enrollment_date DATE DEFAULT SYSDATE
);
39. Use of DISTINCT
Question: Retrieve unique course names from the courses table.
Solution:
sql
CopyEdit
SELECT DISTINCT course_name FROM courses;
40. Use of IN Operator
Question: Retrieve students who are in either age 20, 21, or 22.
Solution:
sql
CopyEdit
SELECT * FROM students WHERE age IN (20, 21, 22);
41. Use of NOT IN Operator
Question: Retrieve students who are not in age 20, 21, or 22.
Solution:
sql
CopyEdit
SELECT * FROM students WHERE age NOT IN (20, 21, 22);
42. Find the Second Highest Age
Question: Retrieve the second highest age from the students table.
Solution:
sql
CopyEdit
SELECT MAX(age) FROM students WHERE age < (SELECT MAX(age) FROM students);
43. Find the Nth Highest Age
Question: Retrieve the 3rd highest age from the students table.
Solution:
sql
CopyEdit
SELECT age FROM (
SELECT age, RANK() OVER (ORDER BY age DESC) AS rnk FROM students
) WHERE rnk = 3;
44. PL/SQL IF-ELSE Condition
Question: Write a PL/SQL block to check if a student is an adult (age >= 18).
Solution:
sql
CopyEdit
DECLARE v_age INT;
BEGIN
SELECT age INTO v_age FROM students WHERE student_id = 1;
IF v_age >= 18 THEN
DBMS_OUTPUT.PUT_LINE('Student is an adult');
ELSE
DBMS_OUTPUT.PUT_LINE('Student is a minor');
END IF;
END;
45. PL/SQL FOR LOOP
Question: Print numbers from 1 to 10 using a FOR loop.
Solution:
sql
CopyEdit
BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
46. Use of EXISTS
Question: Retrieve students who are enrolled in at least one course.
Solution:
sql
CopyEdit
SELECT * FROM students
WHERE EXISTS (SELECT 1 FROM courses WHERE students.student_id =
courses.student_id);
47. Delete All Data from a Table Without Dropping It
Question: Remove all records from students while keeping the table structure.
Solution:
sql
CopyEdit
TRUNCATE TABLE students;
48. Use of LIKE Operator
Question: Retrieve students whose names start with 'A'.
Solution:
sql
CopyEdit
SELECT * FROM students WHERE name LIKE 'A%';
49. Create a BEFORE INSERT Trigger
Question: Create a trigger that sets a default enrollment date before inserting a student.
Solution:
sql
CopyEdit
CREATE TRIGGER set_enrollment_date
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
:NEW.enrollment_date := SYSDATE;
END;
50. Create an AFTER UPDATE Trigger
Question: Create a trigger to log student age updates.
Solution:
sql
CopyEdit
CREATE TRIGGER log_age_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
INSERT INTO student_logs (student_id, old_age, new_age, update_time)
VALUES (:OLD.student_id, :OLD.age, :NEW.age, SYSDATE);
END;
51. Query to Check If a Table Exists
Question: Check if the students table exists in the database.
Solution:
sql
CopyEdit
SELECT table_name FROM user_tables WHERE table_name = 'STUDENTS';
52. Use of ROWNUM to Get First N Records
Question: Retrieve the first 5 students from the students table.
Solution:
sql
CopyEdit
SELECT * FROM students WHERE ROWNUM <= 5;
53. PL/SQL Block with EXCEPTION Handling
Question: Handle an exception when selecting a non-existent student.
Solution:
sql
CopyEdit
DECLARE v_name VARCHAR(100);
BEGIN
SELECT name INTO v_name FROM students WHERE student_id = 1000;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No student found with that ID');
END;
54. Create a Synonym
Question: Create a synonym stud for the students table.
Solution:
sql
CopyEdit
CREATE SYNONYM stud FOR students;
55. Drop a Column from a Table
Question: Remove the age column from the students table.
Solution:
sql
CopyEdit
ALTER TABLE students DROP COLUMN age;
