Scribd
Upload
9 views5 pages

Oracle SQL Exam Guide: Procedures & Functions

The document provides a comprehensive guide on Oracle SQL, detailing the creation of database schema for Student, Course, and Enrollment tables. It includes examples of stored procedures, functions, views, indexes, cursors, and triggers to manage student data and track changes. Additionally, it demonstrates how to execute these SQL commands to manipulate and retrieve data effectively.

Uploaded by

madhumathis23cb
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
9 views5 pages

Oracle SQL Exam Guide: Procedures & Functions

The document provides a comprehensive guide on Oracle SQL, detailing the creation of database schema for Student, Course, and Enrollment tables. It includes examples of stored procedures, functions, views, indexes, cursors, and triggers to manage student data and track changes. Additionally, it demonstrates how to execute these SQL commands to manipulate and retrieve data effectively.

Uploaded by

madhumathis23cb
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

Oracle SQL Practical Exam Guide

1. Database Schema

------------------

CREATE TABLE Student (

student_id NUMBER PRIMARY KEY,

name VARCHAR2(50),

department VARCHAR2(50)

);

CREATE TABLE Course (

course_id NUMBER PRIMARY KEY,

course_name VARCHAR2(50),

credits NUMBER

);

CREATE TABLE Enrollment (

enrollment_id NUMBER PRIMARY KEY,

student_id NUMBER REFERENCES Student(student_id),

course_id NUMBER REFERENCES Course(course_id),

grade CHAR(1)

);

2. Stored Procedure

--------------------

CREATE OR REPLACE PROCEDURE AddStudent (


p_id IN NUMBER,

p_name IN VARCHAR2,

p_dept IN VARCHAR2

) AS

BEGIN

INSERT INTO Student(student_id, name, department)

VALUES (p_id, p_name, p_dept);

END;

EXEC AddStudent(1, 'John Doe', 'Computer Science');

3. Function

-----------

CREATE OR REPLACE FUNCTION GetTotalCredits (

p_student_id IN NUMBER

) RETURN NUMBER IS

total_credits NUMBER;

BEGIN

SELECT SUM([Link]) INTO total_credits

FROM Enrollment e

JOIN Course c ON e.course_id = c.course_id

WHERE e.student_id = p_student_id;

RETURN NVL(total_credits, 0);

END;

/
SELECT name, GetTotalCredits(student_id) AS total_credits FROM Student;

4. View

--------

CREATE OR REPLACE VIEW StudentCourseView AS

SELECT [Link], c.course_name, [Link]

FROM Student s

JOIN Enrollment e ON s.student_id = e.student_id

JOIN Course c ON e.course_id = c.course_id;

SELECT * FROM StudentCourseView;

5. Index

--------

CREATE INDEX idx_student_name ON Student(name);

SELECT * FROM Student WHERE name = 'John Doe';

6. Cursor

---------

DECLARE

CURSOR student_cursor IS

SELECT [Link], c.course_name, [Link]

FROM Student s

JOIN Enrollment e ON s.student_id = e.student_id

JOIN Course c ON e.course_id = c.course_id;

v_name [Link]%TYPE;

v_course Course.course_name%TYPE;
v_grade [Link]%TYPE;

BEGIN

OPEN student_cursor;

LOOP

FETCH student_cursor INTO v_name, v_course, v_grade;

EXIT WHEN student_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_name || ' - ' || v_course || ' - ' || v_grade);

END LOOP;

CLOSE student_cursor;

END;

7. Trigger

----------

CREATE TABLE Student_Log (

log_id NUMBER GENERATED ALWAYS AS IDENTITY,

action_date DATE,

student_id NUMBER,

action VARCHAR2(50)

);

CREATE OR REPLACE TRIGGER trg_log_student_insert

AFTER INSERT ON Student

FOR EACH ROW

BEGIN

INSERT INTO Student_Log(action_date, student_id, action)

VALUES (SYSDATE, :NEW.student_id, 'INSERT');


END;

INSERT INTO Student VALUES (2, 'Alice', 'ECE');

SELECT * FROM Student_Log;

Common questions

Powered by AI

The 'GetTotalCredits' function calculates the total number of credits a student has earned by summing up the credits of all courses they are enrolled in. It achieves this by joining the 'Enrollment' and 'Course' tables on the course_id, filtering the results where the student_id matches the input parameter, and summing the credits from the 'Course' table. The function returns 0 if the student has no enrollments to ensure a valid result is returned .

Foreign key constraints in the 'Enrollment' table enforce referential integrity by ensuring that student_id and course_id must match existing entries in the 'Student' and 'Course' tables, respectively. This prevents the entry of invalid data and maintains consistent and valid relationships between tables. It is a crucial aspect of maintaining the logical accuracy of relational databases, ensuring that enrollments always reflect legitimate students and courses .

The trigger 'trg_log_student_insert' automatically logs every insertion into the 'Student' table by creating an entry in the 'Student_Log' table with the current date, student ID, and action type 'INSERT'. This contributes to database auditing by maintaining a historical record of student additions, which can be reviewed for auditing purposes, helping track changes and ensuring accountability in database operations .

Using a cursor like 'student_cursor' allows for sequential processing of query results within a PL/SQL block, thereby enabling operations to be performed on each row individually in a controlled loop. This is beneficial when complex processing is needed for each row returned by a query, accommodating row-by-row logic that static SQL statements cannot handle efficiently. It provides flexibility for operations based on procedural logic .

The 'StudentCourseView' is constructed by joining the 'Student', 'Enrollment', and 'Course' tables. It selects the student's name, course name, and grade for each enrollment entry. The purpose of this view is to provide a consolidated and easily accessible representation of the enrollment records, showing who is taking which course and what grade they received. It simplifies querying for information that spans multiple tables .

Triggers like those used for logging actions, while useful for auditing purposes, can introduce complications such as performance overhead due to additional operations executed synchronously with the triggering event. This can lead to longer transaction times and potential deadlocks if not managed carefully. Designing triggers requires careful consideration of transaction management and potential impacts on database performance .

If multiple students have the same name, the effectiveness of the index 'idx_student_name' may be diminished because the index helps primarily with unique or highly selective queries. To address this, a composite index on both the name and another distinguishing attribute, such as student_id, could be employed. This would enhance performance by narrowing searches with additional criteria beyond the name, leading to quicker retrieval of the targeted records .

The 'AddStudent' stored procedure adds a new student to the 'Student' table by inserting the provided student ID, name, and department values into the corresponding fields in the table. The key components of the procedure include parameters p_id, p_name, and p_dept, which are used to pass the student's ID, name, and department information, respectively. The SQL INSERT statement within the procedure executes the action of adding a record with the specified values into the 'Student' table .

The index 'idx_student_name' improves query performance by optimizing the search process for queries filtering by student name, such as 'SELECT * FROM Student WHERE name = 'John Doe''. Indexes work by providing a data structure that can be searched more efficiently than scanning each row in the table. This reduces the time complexity of searches, particularly for large tables, allowing queries that rely on the indexed columns to execute faster .

The NVL function in 'GetTotalCredits' ensures that the function returns 0 instead of NULL when a student is not enrolled in any courses. This enhances the robustness of the query by providing a default value that can be used in calculations or further logic without causing null-reference errors. It ensures users and applications receive a meaningful result, even in edge cases where no data might otherwise be returned .

You might also like