Download<\/a><\/div>\n\n\n\n<\/p>\n\n\n\n
\n\n\n\n<\/p>\n\n\n\n
Introduction to Oracle SQL <\/h1>\n\n\n\n
Fri 4-11-2025<\/p>\n\n\n\n
Hands on workshop<\/p>\n\n\n\n
Intro to Oracle Security features of privileges, grant and revoke<\/h1>\n\n\n\n
— Examples of grant privileges:<\/p>\n\n\n\n
GRANT SELECT ON JUAN.EXCEL_COURSE TO STUDENT06;<\/p>\n\n\n\n
GRANT SELECT ON JUAN.EXCEL_COURSE TO STUDENT01, STUDENT02, STUDENT03, STUDENT04, STUDENT05, STUDENT06;<\/p>\n\n\n\n
REVOKE SELECT ON JUAN.EXCEL_COURSE FROM STUDENT05;<\/p>\n\n\n\n
GRANT INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO STUDENT01;<\/p>\n\n\n\n
–grant option to create a view<\/p>\n\n\n\n
GRANT CREATE VIEW TO STUDENT 01;<\/p>\n\n\n\n
–Grant privilege on view<\/p>\n\n\n\n
GRANT select ON V_Course_Offerings to STUDENT06;<\/p>\n\n\n\n
Practice Activity: Select Query<\/h1>\n\n\n\n
–view all the records in the table<\/p>\n\n\n\n
SELECT *<\/p>\n\n\n\n
FROM JUAN.Excel_Instructor;<\/p>\n\n\n\n
Now, view each table with a SELECT statement to see the data in each table.<\/p>\n\n\n\n
The Versatility of VIEWS<\/h1>\n\n\n\n
–View the course offering by running a select statement on a view:<\/p>\n\n\n\n
select * from V_Course_Offerings;<\/p>\n\n\n\n
— This is the code to create a view. Views are essentially saved and shareable queries. The view can be created by one user and that user can grant select access to others.<\/p>\n\n\n\n
CREATE OR REPLACE VIEW V_Course_Offerings AS<\/p>\n\n\n\n
SELECT<\/p>\n\n\n\n
s.section_id,<\/p>\n\n\n\n
c.course_id,<\/p>\n\n\n\n
c.title AS course_title,<\/p>\n\n\n\n
t.term_id,<\/p>\n\n\n\n
i.first_name || ‘ ‘ || i.last_name AS instructor_name,<\/p>\n\n\n\n
r.room_number,<\/p>\n\n\n\n
b.name AS building,<\/p>\n\n\n\n
ts.days_of_week || ‘ ‘ || ts.start_time || ‘-‘ || ts.end_time AS time_slot,<\/p>\n\n\n\n
s.max_enrollment<\/p>\n\n\n\n
FROM Excel_Section s<\/p>\n\n\n\n
JOIN Excel_Course c ON s.course_key = c.course_key<\/p>\n\n\n\n
JOIN Excel_Term t ON s.term_id = t.term_id<\/p>\n\n\n\n
JOIN Excel_Instructor i ON s.instructor_key = i.instructor_key<\/p>\n\n\n\n
JOIN Excel_Room r ON s.room_id = r.room_id<\/p>\n\n\n\n
JOIN Excel_Building b ON r.building_id = b.building_id<\/p>\n\n\n\n
JOIN Excel_Timeslot ts ON s.timeslot_id = ts.timeslot_id;<\/p>\n\n\n\n
Practice Activity: Insert Data using INERST INTO<\/h1>\n\n\n\nAdd two courses<\/h2>\n\n\n\n
–ACTIVITY – Exercise —<\/p>\n\n\n\n
–insert a three new course by modifying the insert query script below<\/p>\n\n\n\n
INSERT INTO<\/p>\n\n\n\n
Excel_Course (course_id, title, credits, department)<\/p>\n\n\n\n
VALUES<\/p>\n\n\n\n
(‘Oracle101’, ‘Introduction to Oracle’, 4, ‘Computer Science’);<\/p>\n\n\n\n
Add one instructor<\/h2>\n\n\n\n
INSERT INTO Excel_Instructor (instructor_id, first_name, last_name, email)<\/p>\n\n\n\n
VALUES<\/p>\n\n\n\n
(‘I001’, ‘Prof’, ‘Smith’, ‘smith@example.edu’);<\/p>\n\n\n\n
Practice Activity: Update a record using UPDATE query<\/h1>\n\n\n\n
–update the name of an instructor by modifying this code<\/p>\n\n\n\n
UPDATE Excel_Instructor<\/p>\n\n\n\n
SET last_name = ‘Astrab’<\/p>\n\n\n\n
WHERE instructor_key = 4;<\/p>\n\n\n\n
–ACTIVITY – Exercise — now update an email<\/p>\n\n\n\n
Practice Activity: Run this Aggregate query to get Enrollment per course<\/h1>\n\n\n\n
SELECT<\/p>\n\n\n\n
c.course_id,<\/p>\n\n\n\n
c.title,<\/p>\n\n\n\n
COUNT(e.enrollment_id) AS total_enrollments<\/p>\n\n\n\n
FROM<\/p>\n\n\n\n
Excel_Course c<\/p>\n\n\n\n
JOIN<\/p>\n\n\n\n
Excel_Section s ON s.course_key = c.course_key<\/p>\n\n\n\n
JOIN<\/p>\n\n\n\n
Excel_Enrollment e ON e.section_id = s.section_id<\/p>\n\n\n\n
GROUP BY<\/p>\n\n\n\n
c.course_id, c.title<\/p>\n\n\n\n
ORDER BY<\/p>\n\n\n\n
total_enrollments DESC;<\/p>\n\n\n\n
Practice Activity: Insert new sections for the next semester by modifying the following code<\/h1>\n\n\n\n
–ACTIVITY – Exercise \u2013 <\/p>\n\n\n\n
INSERT INTO Excel_Section(section_id, course_key, term_id, instructor_key, room_id, timeslot_id, max_enrollment)<\/p>\n\n\n\n
VALUES (‘SEC_000001’, 1, ‘2024FA’, 1, ‘R101’, ‘TS1’, 30);<\/p>\n\n\n\n
Practice Activity: Deleting Records<\/h1>\n\n\n\n
ADD instructor Z<\/p>\n\n\n\n
INSERT INTO Excel_Instructor (instructor_id, first_name, last_name, email)<\/p>\n\n\n\n
VALUES<\/p>\n\n\n\n
(‘I00Z’, ‘Prof’, ‘Z’, ‘Z@example.edu’);<\/p>\n\n\n\n
DELETE FROM Excel_Instructor<\/p>\n\n\n\n
WHERE last_name = ‘Z’<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Introduction to Oracle SQL <\/h1>\n\n\n\n
12-18-2024<\/p>\n\n\n\n
Data is at the core of this data management system. This class will focus on tables, SQL queries, data access, and the Oracle SQL Developer Web Interface.<\/strong><\/p>\n\n\n\n