Below is an organized, tabulated list of essential SQL code examples using the My Three Sons Tutoring Group data model as the context. Each example demonstrates key SQL concepts, with explanations and sample code tailored for Oracle SQL Developer.

1. DDL (Data Definition Language)

DDL commands define and modify the structure of database objects (e.g., tables, constraints).

Concept	Example Code	Explanation
Create Table	CREATE TABLE Subjects ( Subject_ID NUMBER PRIMARY KEY, Subject_Name VARCHAR2(50) NOT NULL );	Creates a new table named Subjects with a primary key.
Alter Table	ALTER TABLE Students ADD Date_Of_Birth DATE;	Adds a new column Date_Of_Birth to the Students table.
Drop Table	DROP TABLE Feedback;	Deletes the Feedback table and all its data permanently.
Add Constraint	ALTER TABLE Payments ADD CONSTRAINT CHK_Amount CHECK (Amount > 0);	Adds a check constraint to ensure Amountin Payments is greater than 0.

2. DML (Data Manipulation Language)

DML commands manipulate data in database tables (e.g., INSERT, UPDATE, DELETE, MERGE).

Concept	Example Code	Explanation
Insert Data	INSERT INTO Tutors (First_Name, Last_Name, Subject, Hourly_Rate, Experience_Years) VALUES (‘John’, ‘Smith’, ‘Math’, 50, 5);	Adds a new tutor to the Tutors table.
Update Data	UPDATE Sessions SET Cost = Duration * 60 WHERE Tutor_ID = 1;	Updates the Cost for all sessions conducted by a specific tutor.
Delete Data	DELETE FROM Payments WHERE Amount < 50;	Deletes all payment records where the amount is less than 50.
Merge Data	MERGE INTO Students s USING (SELECT 5 AS Student_ID, ‘Sue’ AS First_Name, ‘Brown’ AS Last_Name , ‘Susan@gmail.com’ AS Email , ‘555-1357’ as Phone, ‘Grade 11’ as Grade_Level FROM dual) data ON (s.Student_ID = data.Student_ID) WHEN MATCHED THEN UPDATE SET First_Name = data.First_Name, Last_Name = data.Last_Name , Email = data.Email WHEN NOT MATCHED THEN INSERT (Student_ID, First_Name, Last_Name, Email , Phone, Grade_Level) VALUES (data.Student_ID, data.First_Name, data.Last_Name , data.Email, data.Phone, data.Grade_Level);	Merges data into Students: updates if the record exists, inserts if it doesn’t.

3. SELECT Queries (Basic and Advanced)

Concept	Example Code	Explanation
Basic SELECT	SELECT First_Name, Last_Name FROM Students;	Retrieves the first and last names of all students.
WHERE Clause	SELECT * FROM Sessions WHERE Duration > 2;	Retrieves sessions with a duration greater than 2 hours.
ORDER BY	SELECT * FROM Tutors ORDER BY Experience_Years DESC;	Retrieves all tutors, sorted by their years of experience in descending order.
GROUP BY	SELECT Grade_Level, COUNT(*) AS Student_Count FROM Students GROUP BY Grade_Level;	Groups students by grade level and counts the number of students in each grade level.
HAVING Clause	SELECT Tutor_ID, AVG(Cost) AS Avg_Cost FROM Sessions GROUP BY Tutor_ID HAVING AVG(Cost) > 75;	Retrieves tutors whose average session cost is greater than 75.
JOIN	SELECT s.Student_ID, st.First_Name, st.Last_Name, t.First_Name AS Tutor_Name FROM Sessions s JOIN Students st ON s.Student_ID = st.Student_ID JOIN Tutors t ON s.Tutor_ID = t.Tutor_ID;	Combines data from Sessions, Students, and Tutors to show which student attended which session with which tutor.
Subquery	SELECT * FROM Tutors WHERE Hourly_Rate > (SELECT AVG(Hourly_Rate) FROM Tutors);	Retrieves all tutors whose hourly rate is above the average hourly rate.
Window Function	SELECT Student_ID, First_Name, Last_Name, ROW_NUMBER() OVER (ORDER BY Student_ID) AS Row_Num FROM Students;	Assigns a row number to each student based on their Student_ID.

4. Aggregation Queries

Concept	Example Code	Explanation
Count Records	SELECT COUNT(*) AS Total_Students FROM Students;	Counts the total number of students in the Students table.
Sum Data	SELECT SUM(Amount) AS Total_Revenue FROM Payments;	Sums up all payment amounts to calculate total revenue.
Average	SELECT AVG(Duration) AS Avg_Session_Duration FROM Sessions;	Calculates the average session duration.
Min/Max Values	SELECT MIN(Hourly_Rate) AS Lowest_Rate, MAX(Hourly_Rate) AS Highest_Rate FROM Tutors;	Retrieves the lowest and highest hourly rates among tutors.

5. Joins and Relationships

Concept	Example Code	Explanation
INNER JOIN	SELECT s.First_Name, p.Amount FROM Students s INNER JOIN Payments p ON s.Student_ID = p.Student_ID;	Retrieves students along with their payment amounts.
LEFT JOIN	SELECT s.First_Name, p.Amount FROM Students s LEFT JOIN Payments p ON s.Student_ID = p.Student_ID;	Retrieves all students, including those who haven’t made any payments (with NULL for Amount).
RIGHT JOIN	SELECT p.Amount, s.First_Name FROM Students s RIGHT JOIN Payments p ON s.Student_ID = p.Student_ID;	Retrieves all payments, including those not linked to any students (with NULL for Student details).
FULL OUTER JOIN	SELECT s.First_Name, p.Amount FROM Students s FULL OUTER JOIN Payments p ON s.Student_ID = p.Student_ID;	Combines LEFT JOIN and RIGHT JOIN, showing unmatched rows from both tables.

6. Data Integrity and Validation

Concept	Example Code	Explanation
Check Constraint	ALTER TABLE Sessions ADD CONSTRAINT CHK_Duration CHECK (Duration > 0);	Ensures that Duration in the Sessions table is always greater than 0.
Unique Constraint	ALTER TABLE Students ADD CONSTRAINT UC_Student_Email UNIQUE (Email);	Ensures that student emails are unique.
Foreign Key	ALTER TABLE Sessions ADD CONSTRAINT FK_Student FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID);	Links the Sessions.Student_ID column to the Students.Student_ID column, enforcing referential integrity.

7. Analytical and Reporting Queries

Concept	Example Code	Explanation
Revenue by Tutor	SELECT t.Tutor_ID, t.First_Name \|\| ‘ ‘ \|\| t.Last_Name AS Tutor_Name, SUM(s.Cost) AS Total_Revenue FROM Tutors t JOIN Sessions s ON t.Tutor_ID = s.Tutor_ID GROUP BY t.Tutor_ID, t.First_Name, t.Last_Name ORDER BY Total_Revenue DESC;	This query calculates the total revenue generated by each tutor by summing up the Cost column for all tutoring sessions conducted by that tutor.
Top Students	SELECT st.First_Name, st.Last_Name, COUNT(s.Session_ID) AS Total_Sessions FROM Students st JOIN Sessions s ON st.Student_ID = s.Student_ID GROUP BY st.First_Name, st.Last_Name ORDER BY Total_Sessions DESC;	Lists students with the most sessions, in descending order.
Session Analysis	SELECT Tutor_ID, AVG(Cost) AS Avg_Cost, MAX(Cost) AS Max_Cost FROM Sessions GROUP BY Tutor_ID;	Provides average and maximum session costs per tutor.

8. Additional Features

Concept	Example Code	Explanation
Create View	CREATE VIEW Student_Payments AS SELECT s.First_Name, s.Last_Name, p.Amount FROM Students s JOIN Payments p ON s.Student_ID = p.Student_ID;	Creates a reusable view to quickly access student payment data.
Index	CREATE INDEX idx_sessions_date ON Sessions(Session_Date);	Creates an index on Session_Date to optimize query performance.

Summary

This tabulated list demonstrates key SQL concepts with practical examples tailored for the My Three Sons Tutoring Group LLC database. It covers foundational topics like DDL, DML, aggregations, and advanced SQL features like joins, constraints, and analytical queries, providing a comprehensive toolkit for database management and reporting.

Code to crate tables, primary key and foreigh key

001 Create the tables Download

Code for data insertion and sample business queries

002 Code for data insertion and sample business queries Download

Introduction to Oracle SQL

Fri 4-11-2025

Hands on workshop

Intro to Oracle Security features of privileges, grant and revoke

— Examples of grant privileges:

GRANT SELECT ON JUAN.EXCEL_COURSE TO STUDENT06;

GRANT SELECT ON JUAN.EXCEL_COURSE TO STUDENT01, STUDENT02, STUDENT03, STUDENT04, STUDENT05, STUDENT06;

REVOKE SELECT ON JUAN.EXCEL_COURSE FROM STUDENT05;

GRANT INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO STUDENT01;

–grant option to create a view

GRANT CREATE VIEW TO STUDENT 01;

–Grant privilege on view

GRANT select ON V_Course_Offerings to STUDENT06;

Practice Activity: Select Query

–view all the records in the table

SELECT *

FROM JUAN.Excel_Instructor;

Now, view each table with a SELECT statement to see the data in each table.

The Versatility of VIEWS

–View the course offering by running a select statement on a view:

select * from V_Course_Offerings;

— 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.

CREATE OR REPLACE VIEW V_Course_Offerings AS

SELECT

s.section_id,

c.course_id,

c.title AS course_title,

t.term_id,

i.first_name || ‘ ‘ || i.last_name AS instructor_name,

r.room_number,

b.name AS building,

ts.days_of_week || ‘ ‘ || ts.start_time || ‘-‘ || ts.end_time AS time_slot,

s.max_enrollment

FROM Excel_Section s

JOIN Excel_Course c ON s.course_key = c.course_key

JOIN Excel_Term t ON s.term_id = t.term_id

JOIN Excel_Instructor i ON s.instructor_key = i.instructor_key

JOIN Excel_Room r ON s.room_id = r.room_id

JOIN Excel_Building b ON r.building_id = b.building_id

JOIN Excel_Timeslot ts ON s.timeslot_id = ts.timeslot_id;

Practice Activity: Insert Data using INERST INTO

Add two courses

–ACTIVITY – Exercise —

–insert a three new course by modifying the insert query script below

INSERT INTO

Excel_Course (course_id, title, credits, department)

VALUES

(‘Oracle101’, ‘Introduction to Oracle’, 4, ‘Computer Science’);

Add one instructor

INSERT INTO Excel_Instructor (instructor_id, first_name, last_name, email)

VALUES

(‘I001’, ‘Prof’, ‘Smith’, ‘smith@example.edu’);

Practice Activity: Update a record using UPDATE query

–update the name of an instructor by modifying this code

UPDATE Excel_Instructor

SET last_name = ‘Astrab’

WHERE instructor_key = 4;

–ACTIVITY – Exercise — now update an email

Practice Activity: Run this Aggregate query to get Enrollment per course

SELECT

c.course_id,

c.title,

COUNT(e.enrollment_id) AS total_enrollments

FROM

Excel_Course c

JOIN

Excel_Section s ON s.course_key = c.course_key

JOIN

Excel_Enrollment e ON e.section_id = s.section_id

GROUP BY

c.course_id, c.title

ORDER BY

total_enrollments DESC;

Practice Activity: Insert new sections for the next semester by modifying the following code

–ACTIVITY – Exercise –

INSERT INTO Excel_Section(section_id, course_key, term_id, instructor_key, room_id, timeslot_id, max_enrollment)

VALUES (‘SEC_000001’, 1, ‘2024FA’, 1, ‘R101’, ‘TS1’, 30);

Practice Activity: Deleting Records

ADD instructor Z

INSERT INTO Excel_Instructor (instructor_id, first_name, last_name, email)

VALUES

(‘I00Z’, ‘Prof’, ‘Z’, ‘Z@example.edu’);

DELETE FROM Excel_Instructor

WHERE last_name = ‘Z’

Introduction to Oracle SQL

12-18-2024

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.

Structured Query Language (SQL) is a domain-specific language used to manage data, especially in a relational database management system (RDBMS).
Oracle Cloud Infrastructure (OCI) is a cloud computing service that provides a complete cloud infrastructure for building, testing, deploying, and managing applications and services:

OCI Features

High performance and predictability

On-demand throughput and storage provisioning

A flexible overlay virtual network

A variety of cloud services, including multi-cloud, public cloud, hybrid cloud, and dedicated cloud

OCI Benefits help users:

Run applications faster and more securely

Build and run a range of applications and services in a highly available hosted environment

Access services securely from their on-premises network

You should be aware of the key points as we focus on Oracle SQL Developer Web’s data management systems:

You will need an internet connection and the URL (uniform resource locator) to where the SQL database resides.
Everything is on your browser. OCI works well on tablets, too, for convenience and portability.
Oracle rest data services “Powered by ORDS” enables powerful state-of-the-art hooks into the web.
Many core functionalities, but not all desktop features, are available on the SQL developer web.

The goal is to use Oracle SQL developer with an internet connection. No software installation is required since it runs off web pages.

INTRODUCTION TO ORACLE SQL DEV WEB

DATA QUERIES AND DATABASE MANAGEMENT

12-18-2024

Prerequisites: proficient with a web browser and basic Microsoft Excel experience or equivalent

Oracle SQL developer on the web vs. Microsoft Access database application on the desktop. What are the differences between Oracle SQL Developer and MS Access?

Feature	Oracle SQL Developer (Web application)	MS Access (Desktop application)
Purpose	Oracle SQL Developer is a tool designed for managing and querying Oracle databases. It provides features to handle complex queries, PL/SQL development, and large-scale database management.	MS Access is a desktop-based relational database system ideal for small to medium-sized applications and users who need to build simple databases with basic queries and reports.
Scalability	Oracle SQL Developer handles large enterprise-level databases with millions of records and complex queries. It is ideal for robust, distributed environments.	MS Access is more suitable for small databases and personal use, with limits on the number of records and concurrent users.
Multi-user Support	Supports thousands of users simultaneously, with strong concurrency control and user management features.	Limited to a small number of concurrent users (around 10-20 depending on usage), with scalability issues in multi-user environments.
Query Language	Uses PL/SQL (Procedural Language for SQL) for more advanced database logic, which is more powerful than standard SQL alone.	Uses standard SQL with basic querying capabilities, and offers VBA for scripting tasks but lacks complex functionality.
Interface	Oracle SQL Developer has a web-based interface (SQL Developer Web) and a desktop version, providing extensive database management tools, including RESTful services, data modeling, and performance analysis.	MS Access is desktop-based with a user-friendly interface that focuses on building simple queries, forms, and reports, targeting non-technical users.
Export/Import	Extensive export options (CSV, Excel, XML, JSON), plus direct database links for data integration.	Export options are more limited, typically supporting CSV, Excel, and text files, with some support for ODBC connections.
Strengths	Highly scalable, supports advanced database logic, enterprise-grade security, and handles complex database systems efficiently.	Easy to use for beginners, especially for small-scale applications, with built-in GUI tools for designing tables and queries.
Weaknesses	More complex to learn for beginners, requires some knowledge of PL/SQL and Oracle database administration.	Not suitable for large-scale databases or multi-user environments; limited functionality for advanced users.

The hands-on workshop empowers participants to run SELECT queries.

Introduction to Oracle SQL Developer for the Web

12-18-2024

Lesson Plan: Introduction to Oracle SQL Developer Web: Data Queries and Database Management

Prerequisites:

Proficient with web browsers
Basic experience with Microsoft Excel or equivalent

Lesson Plan Overview

Objective:

By the end of this session, participants will be able to navigate Oracle SQL Developer Web, execute SQL queries, and import data

Session Breakdown

Introduction & Objectives

Interface navigation
Running SQL queries

Module 1: Getting Started with Oracle SQL Developer Web (20 mins)

Topics:

Logging in to Oracle SQL Developer Web
Exploring the Dashboard and interface components
- Navigation Menu
- SQL worksheet

Hands-on Activity:

Walkthrough of navigating the interface
Opening the worksheet for query execution

Module 2: Running SQL Queries and Scripts

Topics:

Writing basic SQL SELECT statements
Executing scripts to manage and manipulate data

Hands-on Activity:

Write and execute a simple SELECT query to retrieve data from an example table

SELECT employee_id, first_name, last_name, department_id

FROM employees

WHERE department_id = 10;

Module 3: Advanced Query Techniques

Topics:

Aggregation functions: SUM, COUNT, AVG, MAX, MIN
Grouping and filtering data using GROUP BY and HAVING

Hands-on Activity:

Example: Joining employees and departments tables to retrieve department names:

SELECT department_id, COUNT(employee_id) AS total_employees

FROM employees

GROUP BY department_id

HAVING COUNT(employee_id) > 5;

Module 4: Exporting Data

Topics:

Exporting query results to CSV, Excel, and JSON formats
Configuring export options and managing file formats

Hands-on Activity:

Example of exporting employee data to CSV:
- Run query:

SELECT employee_id, first_name, last_name, department_id

FROM employees;

Export the result to a CSV file:
- Navigate to “Export” in the worksheet
- Select format (CSV, XLS, etc.)
- Save the file

Conclusion & Q&A Recap of key concepts

Examples

1. Navigating Oracle SQL Developer Web

Login: Open Oracle SQL Developer Web via a web browser, log in with credentials.
Navigate: Use the left navigation menu to access different features (SQL, Data Models).
Worksheet: Open the worksheet from the toolbar to begin writing queries.

2. Running SQL Statements and Scripts

Basic Query: Retrieve employee names from the EMPLOYEES table:

SELECT first_name, last_name FROM employees;

3. Export Data

After running a query, export the result set by clicking the “Export” button.
Choose the export format: CSV, XLS, or JSON.
Save the exported data to your system for further use.

Work with the BBQ list Table

Select queries enable you to select all or specific columns

Example, show all columns of table bbq

SELECT

bbq.*

FROM

ADMIN.ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE_1 bbq;

Shortcut: CTL with ENTER enables you to run a query.

Example: Show specific columns

SELECT

bbq.DESCRIPTION,

bbq.CATEGORY,

bbq.QUANTITY,

bbq.UNIT_PRICE

FROM

ADMIN.ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE_1 bbq;

Example: Multiply two columns to calculate a CALC_subtotal

SELECT

bbq.DESCRIPTION,

bbq.CATEGORY,

bbq.QUANTITY,

bbq.UNIT_PRICE

bbq.QUANTITY * bbq.UNIT_PRICE CalculatedSubtotal

FROM

ADMIN.ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE_1 bbq;

Essential Oracle SQL Developer Web code examples

12-18-2024

1. DDL (Data Definition Language)

DDL commands define and modify the structure of database objects (e.g., tables, constraints).

Concept	Example Code	Explanation
Create Table	CREATE TABLE Subjects ( Subject_ID NUMBER PRIMARY KEY, Subject_Name VARCHAR2(50) NOT NULL );	Creates a new table named Subjects with a primary key.
Alter Table	ALTER TABLE Students ADD Date_Of_Birth DATE;	Adds a new column Date_Of_Birth to the Students table.
Drop Table	DROP TABLE Feedback;	Deletes the Feedback table and all its data permanently.
Add Constraint	ALTER TABLE Payments ADD CONSTRAINT CHK_Amount CHECK (Amount > 0);	Adds a check constraint to ensure Amountin Payments is greater than 0.

2. DML (Data Manipulation Language)

DML commands manipulate data in database tables (e.g., INSERT, UPDATE, DELETE, MERGE).

Concept	Example Code	Explanation
Insert Data	INSERT INTO Tutors (First_Name, Last_Name, Subject, Hourly_Rate, Experience_Years) VALUES (‘John’, ‘Smith’, ‘Math’, 50, 5);	Adds a new tutor to the Tutors table.
Update Data	UPDATE Sessions SET Cost = Duration * 60 WHERE Tutor_ID = 1;	Updates the Cost for all sessions conducted by a specific tutor.
Delete Data	DELETE FROM Payments WHERE Amount < 50;	Deletes all payment records where the amount is less than 50.
Merge Data	MERGE INTO Students s USING (SELECT 5 AS Student_ID, ‘Sue’ AS First_Name, ‘Brown’ AS Last_Name , ‘Susan@gmail.com’ AS Email , ‘555-1357’ as Phone, ‘Grade 11’ as Grade_Level FROM dual) data ON (s.Student_ID = data.Student_ID) WHEN MATCHED THEN UPDATE SET First_Name = data.First_Name, Last_Name = data.Last_Name , Email = data.Email WHEN NOT MATCHED THEN INSERT (Student_ID, First_Name, Last_Name, Email , Phone, Grade_Level) VALUES (data.Student_ID, data.First_Name, data.Last_Name , data.Email, data.Phone, data.Grade_Level);	Merges data into Students: updates if the record exists, inserts if it doesn’t.

3. SELECT Queries (Basic and Advanced)

Concept	Example Code	Explanation
Basic SELECT	SELECT First_Name, Last_Name FROM Students;	Retrieves the first and last names of all students.
WHERE Clause	SELECT * FROM Sessions WHERE Duration > 2;	Retrieves sessions with a duration greater than 2 hours.
ORDER BY	SELECT * FROM Tutors ORDER BY Experience_Years DESC;	Retrieves all tutors, sorted by their years of experience in descending order.
GROUP BY	SELECT Grade_Level, COUNT(*) AS Student_Count FROM Students GROUP BY Grade_Level;	Groups students by grade level and counts the number of students in each grade level.
HAVING Clause	SELECT Tutor_ID, AVG(Cost) AS Avg_Cost FROM Sessions GROUP BY Tutor_ID HAVING AVG(Cost) > 75;	Retrieves tutors whose average session cost is greater than 75.
JOIN	SELECT s.Student_ID, st.First_Name, st.Last_Name, t.First_Name AS Tutor_Name FROM Sessions s JOIN Students st ON s.Student_ID = st.Student_ID JOIN Tutors t ON s.Tutor_ID = t.Tutor_ID;	Combines data from Sessions, Students, and Tutors to show which student attended which session with which tutor.
Subquery	SELECT * FROM Tutors WHERE Hourly_Rate > (SELECT AVG(Hourly_Rate) FROM Tutors);	Retrieves all tutors whose hourly rate is above the average hourly rate.
Window Function	SELECT Student_ID, First_Name, Last_Name, ROW_NUMBER() OVER (ORDER BY Student_ID) AS Row_Num FROM Students;	Assigns a row number to each student based on their Student_ID.

4. Aggregation Queries

Concept	Example Code	Explanation
Count Records	SELECT COUNT(*) AS Total_Students FROM Students;	Counts the total number of students in the Students table.
Sum Data	SELECT SUM(Amount) AS Total_Revenue FROM Payments;	Sums up all payment amounts to calculate total revenue.
Average	SELECT AVG(Duration) AS Avg_Session_Duration FROM Sessions;	Calculates the average session duration.
Min/Max Values	SELECT MIN(Hourly_Rate) AS Lowest_Rate, MAX(Hourly_Rate) AS Highest_Rate FROM Tutors;	Retrieves the lowest and highest hourly rates among tutors.

5. Joins and Relationships

Concept	Example Code	Explanation
INNER JOIN	SELECT s.First_Name, p.Amount FROM Students s INNER JOIN Payments p ON s.Student_ID = p.Student_ID;	Retrieves students along with their payment amounts.
LEFT JOIN	SELECT s.First_Name, p.Amount FROM Students s LEFT JOIN Payments p ON s.Student_ID = p.Student_ID;	Retrieves all students, including those who haven’t made any payments (with NULL for Amount).
RIGHT JOIN	SELECT p.Amount, s.First_Name FROM Students s RIGHT JOIN Payments p ON s.Student_ID = p.Student_ID;	Retrieves all payments, including those not linked to any students (with NULL for Student details).
FULL OUTER JOIN	SELECT s.First_Name, p.Amount FROM Students s FULL OUTER JOIN Payments p ON s.Student_ID = p.Student_ID;	Combines LEFT JOIN and RIGHT JOIN, showing unmatched rows from both tables.

6. Data Integrity and Validation

Concept	Example Code	Explanation
Check Constraint	ALTER TABLE Sessions ADD CONSTRAINT CHK_Duration CHECK (Duration > 0);	Ensures that Duration in the Sessions table is always greater than 0.
Unique Constraint	ALTER TABLE Students ADD CONSTRAINT UC_Student_Email UNIQUE (Email);	Ensures that student emails are unique.
Foreign Key	ALTER TABLE Sessions ADD CONSTRAINT FK_Student FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID);	Links the Sessions.Student_ID column to the Students.Student_ID column, enforcing referential integrity.

7. Analytical and Reporting Queries

Concept	Example Code	Explanation
Revenue by Tutor	SELECT t.Tutor_ID, t.First_Name \|\| ‘ ‘ \|\| t.Last_Name AS Tutor_Name, SUM(s.Cost) AS Total_Revenue FROM Tutors t JOIN Sessions s ON t.Tutor_ID = s.Tutor_ID GROUP BY t.Tutor_ID, t.First_Name, t.Last_Name ORDER BY Total_Revenue DESC;	This query calculates the total revenue generated by each tutor by summing up the Cost column for all tutoring sessions conducted by that tutor.
Top Students	SELECT st.First_Name, st.Last_Name, COUNT(s.Session_ID) AS Total_Sessions FROM Students st JOIN Sessions s ON st.Student_ID = s.Student_ID GROUP BY st.First_Name, st.Last_Name ORDER BY Total_Sessions DESC;	Lists students with the most sessions, in descending order.
Session Analysis	SELECT Tutor_ID, AVG(Cost) AS Avg_Cost, MAX(Cost) AS Max_Cost FROM Sessions GROUP BY Tutor_ID;	Provides average and maximum session costs per tutor.

8. Additional Features

Concept	Example Code	Explanation
Create View	CREATE VIEW Student_Payments AS SELECT s.First_Name, s.Last_Name, p.Amount FROM Students s JOIN Payments p ON s.Student_ID = p.Student_ID;	Creates a reusable view to quickly access student payment data.
Index	CREATE INDEX idx_sessions_date ON Sessions(Session_Date);	Creates an index on Session_Date to optimize query performance.

Summary

Right Join vs Left Join

12-18-2024

How do you identify the left table vs the right table in the syntax? In SQL syntax, the left table and right table are determined by their position in the FROM and JOIN clauses. The table listed first in the FROM clause is always the left table, and the table listed after the JOIN keyword is the right table.

Syntax Overview The general syntax of a join looks like this:

SELECT columns

FROM left_table

<JOIN TYPE> right_table

ON left_table.column = right_table.column;

left_table: The table listed in the FROM clause (before the JOIN).
right_table: The table listed in the JOIN clause (after the JOIN).

Example 1: LEFT JOIN

SELECT

s.Student_ID,

s.First_Name,

p.Amount

FROM

Students s — LEFT TABLE

LEFT JOIN

Payments p — RIGHT TABLE

s.Student_ID = p.Student_ID;

Left Table: Students (appears in the FROM clause).
Right Table: Payments (appears after the LEFT JOIN).

Example 2: RIGHT JOIN

SELECT

p.Payment_ID,

p.Amount,

s.First_Name

FROM

Students s — LEFT TABLE

RIGHT JOIN

Payments p — RIGHT TABLE

s.Student_ID = p.Student_ID;

Left Table: Students (appears in the FROM clause).
Right Table: Payments (appears after the RIGHT JOIN).

How to Identify the Left and Right Tables

Position	Left Table	Right Table
FROM Clause	The first table mentioned.	Not applicable.
After the JOIN Keyword	Not applicable.	The second table mentioned.

Example Comparison: LEFT vs. RIGHT JOIN

Using LEFT JOIN:

SELECT

s.Student_ID,

s.First_Name,

p.Amount

FROM

Students s — LEFT TABLE

LEFT JOIN

Payments p — RIGHT TABLE

s.Student_ID = p.Student_ID;

Using RIGHT JOIN:

SELECT

p.Payment_ID,

p.Amount,

s.First_Name

FROM

Students s — LEFT TABLE

RIGHT JOIN

Payments p — RIGHT TABLE

s.Student_ID = p.Student_ID;

In both examples, the left table remains the one in the FROM clause (Students), and the right table remains the one after the JOIN keyword (Payments).

Visualizing LEFT vs. RIGHT

In a LEFT JOIN, you include all rows from the left table (the one in the FROM clause), regardless of matches.
In a RIGHT JOIN, you include all rows from the right table (the one after the JOIN keyword), regardless of matches.

Example Results

LEFT JOIN Results (All Students, Payments if available)

Student_ID	First_Name	Amount
1	Sarah	100
2	Michael	67.5
3	Emily	100
4	Rachel	NULL

RIGHT JOIN Results (All Payments, Students if available)

Payment_ID	Amount	First_Name
1	100	Sarah
2	67.5	Michael
3	100	Emily

Key Takeaways

Left Table: The table mentioned in the FROM clause.
Right Table: The table mentioned after the JOIN keyword.
Direction:
- LEFT JOIN: Includes all rows from the left table, even if no match exists in the right table.
- RIGHT JOIN: Includes all rows from the right table, even if no match exists in the left table.

By reading the SQL syntax, you can always determine which table is the left and which is the right based on their positions relative to the FROM and JOIN clauses!

REVIEW: LEFT JOIN and RIGHT JOIN are used to retrieve data from two related tables, but they differ in which table’s rows are prioritized when there is no match between the tables. Here’s how to distinguish LEFT JOIN from RIGHT JOIN, using examples with sample tables from the My Three Sons Tutoring Group LLC schema.

What is a LEFT JOIN?

A LEFT JOIN retrieves:

All rows from the left table, regardless of whether there is a match in the right table.
If there’s no match in the right table, the columns from the right table will contain NULL.

What is a RIGHT JOIN?

A RIGHT JOIN retrieves:

All rows from the right table, regardless of whether there is a match in the left table.
If there’s no match in the left table, the columns from the left table will contain NULL.

Sample Tables

Students Table (Left Table)

Student_ID	First_Name	Last_Name
1	Sarah	Johnson
2	Michael	Lee
3	Emily	Davis
4	Rachel	Brown

Payments Table (Right Table)

Payment_ID	Student_ID	Amount
1	1	100
2	2	67.5
3	3	100

1. LEFT JOIN Example

Retrieve all students, including those who have not made any payments.

SELECT

s.Student_ID,

s.First_Name || ‘ ‘ || s.Last_Name AS Student_Name,

p.Amount

FROM

Students s

LEFT JOIN

Payments p ON s.Student_ID = p.Student_ID;

Explanation:

The Students table is the left table, so all students are included.
If there’s no matching row in the Payments table, NULL is returned for the Amount.

Result:

Student_ID	Student_Name	Amount
1	Sarah Johnson	100
2	Michael Lee	67.5
3	Emily Davis	100
4	Rachel Brown	NULL

Key Takeaway: The unmatched student (Rachel Brown) appears, but their Amount is NULL because no corresponding payment exists.

2. RIGHT JOIN Example

Retrieve all payments, including those where students are not listed in the Students table (e.g., invalid Student_ID in Payments).

SQL Query:

SELECT

p.Payment_ID,

p.Amount,

s.First_Name || ‘ ‘ || s.Last_Name AS Student_Name

FROM

Students s

RIGHT JOIN

Payments p ON s.Student_ID = p.Student_ID;

Explanation:

The Payments table is the right table, so all payments are included.
If there’s no matching row in the Students table, NULL is returned for the student information.

Result:

Payment_ID	Amount	Student_Name
1	100	Sarah Johnson
2	67.5	Michael Lee
3	100	Emily Davis

Key Takeaway: All payments are included, even if no valid student exists in the Students table.

Key Difference Illustrated

When Data Doesn’t Match

Let’s assume there’s a payment with Student_ID = 5 in the Payments table, but no corresponding student in the Students table:

LEFT JOIN: The unmatched payment won’t appear because we are prioritizing the Students table.

SELECT

s.Student_ID,

s.First_Name || ‘ ‘ || s.Last_Name AS Student_Name,

p.Amount

FROM

Students s

LEFT JOIN

Payments p ON s.Student_ID = p.Student_ID;

Result:

Student_ID	Student_Name	Amount
1	Sarah Johnson	100
2	Michael Lee	67.5
3	Emily Davis	100
4	Rachel Brown	NULL

RIGHT JOIN: The unmatched payment will appear because we are prioritizing the Payments table.

SELECT

p.Payment_ID,

p.Amount,

s.First_Name || ‘ ‘ || s.Last_Name AS Student_Name

FROM

Students s

RIGHT JOIN

Payments p ON s.Student_ID = p.Student_ID;

Result:

Payment_ID	Amount	Student_Name
1	100	Sarah Johnson
2	67.5	Michael Lee
3	100	Emily Davis
4	200	NULL

Summary of Differences:

Aspect	LEFT JOIN	RIGHT JOIN
Row Priority	All rows from the left table are included.	All rows from the right table are included.
Unmatched Rows	Unmatched rows from the left table have NULL values in the right table’s columns.	Unmatched rows from the right table have NULL values in the left table’s columns.
Use Case Example	Retrieve all students, even if they haven’t made payments.	Retrieve all payments, even if they aren’t linked to students.

By understanding these differences, you can choose the appropriate join based on the priority of your business data.

More SQL to decipher and modify

12-18-2024

1. Calculate Total Revenue Earned by Each Tutor

SELECT

t.First_Name || ‘ ‘ || t.Last_Name AS Tutor_Name,

SUM(s.Cost) AS Total_Revenue

FROM

Tutors t

JOIN

Sessions s ON t.Tutor_ID = s.Tutor_ID

GROUP BY

t.First_Name, t.Last_Name;

Output:

Tutor_Name Total_Revenue

John Smith 200

Amanda Brown 67.5

2. Find Students with Outstanding Feedback (Rating < 5)

SELECT

s.First_Name || ‘ ‘ || s.Last_Name AS Student_Name,

f.Rating,

f.Comments

FROM

Feedback f

JOIN

Sessions sess ON f.Session_ID = sess.Session_ID

JOIN

Students s ON sess.Student_ID = s.Student_ID

WHERE

f.Rating < 5;

Output:

Student_Name Rating Comments

Michael Lee 4 Good session, needs examples

3. List Payments by Payment Method

SELECT

Payment_Method,

SUM(Amount) AS Total_Amount

FROM

Payments

GROUP BY

Payment_Method;

Output:

Payment_Method Total_Amount

Credit Card 200

Cash 67.5

INTRODUCTION TO ORACLE SQL DEV WEB: DATA QUERIES AND DATABASE MANAGEMENT

Fall 2024 / September 2024

Prerequisites: proficient with a web browser and basic Microsoft Excel experience or equivalent

LAB COMPUTER SIGN IN

Use your NCC ID. It starts with N00…. The password is your date of birth in this six-digit format: MMDDYY.

Username with capital N

N00______

The password your birthday as six digits:

MMDDYY

Workshop Description: Embark on a transformative hands-on workshop into data with an Oracle SQL Developer specialist. In just three hours, master Oracle SQL Developer Web’s interface, run potent queries, and export data effortlessly. This session lays the groundwork for success when refining database management. Join us and harness the power of data-driven decision-making.

What are the differences between Oracle SQL Developer and MS Access?

Feature	Oracle SQL Developer	MS Access
Purpose	Oracle SQL Developer is a tool designed for managing and querying Oracle databases. It provides features to handle complex queries, PL/SQL development, and large-scale database management.	MS Access is a desktop-based relational database system ideal for small to medium-sized applications and users who need to build simple databases with basic queries and reports.
Scalability	Oracle SQL Developer handles large enterprise-level databases with millions of records and complex queries. It is ideal for robust, distributed environments.	MS Access is more suitable for small databases and personal use, with limits on the number of records and concurrent users.
Multi-user Support	Supports thousands of users simultaneously, with strong concurrency control and user management features.	Limited to a small number of concurrent users (around 10-20 depending on usage), with scalability issues in multi-user environments.
Query Language	Uses PL/SQL (Procedural Language for SQL) for more advanced database logic, which is more powerful than standard SQL alone.	Uses standard SQL with basic querying capabilities, and offers VBA for scripting tasks but lacks complex functionality.
Interface	Oracle SQL Developer has a web-based interface (SQL Developer Web) and a desktop version, providing extensive database management tools, including RESTful services, data modeling, and performance analysis.	MS Access is desktop-based with a user-friendly interface that focuses on building simple queries, forms, and reports, targeting non-technical users.
Export/Import	Extensive export options (CSV, Excel, XML, JSON), plus direct database links for data integration.	Export options are more limited, typically supporting CSV, Excel, and text files, with some support for ODBC connections.
Strengths	Highly scalable, supports advanced database logic, enterprise-grade security, and handles complex database systems efficiently.	Easy to use for beginners, especially for small-scale applications, with built-in GUI tools for designing tables and queries.
Weaknesses	More complex to learn for beginners, requires some knowledge of PL/SQL and Oracle database administration.	Not suitable for large-scale databases or multi-user environments; limited functionality for advanced users.

The hands-on workshop empowers participants to run SELECT queries.

Key points found in Oracle but not in MS Access

— supported browsers as of Sept 2024

Supported Browsers
Oracle Cloud Infrastructure supports the following browsers and versions:

Google Chrome 80 or later
Safari 12.1 or later
Firefox 62 or later (Private Browsing mode isn’t supported)*
Edge 104 or later

Creation of Database:

–show users:

SELECT * FROM dba_users;

–create user using SQL:

SAMPLE

/– USER SQL
CREATE USER STUDENT11 IDENTIFIED BY EasyExcelLearning123;

— ADD ROLES
GRANT CONNECT TO STUDENT11;GRANT RESOURCE TO STUDENT11;

— REST ENABLE
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => ‘STUDENT11’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘student11’,
p_auto_rest_auth=> TRUE
);
— ENABLE DATA SHARING
C##ADP$SERVICE.DBMS_SHARE.ENABLE_SCHEMA(
SCHEMA_NAME => ‘STUDENT11’,
ENABLED => TRUE
);
commit;
END;
/

STUDENT VIEW

ADMIN uploads file

UPLOADED BBQLIST from Excel File

RESULTS of SELECT QUERY

SHOW all columns using “*”

drag and drop of table to generate SELECT code

SELECT
TRAFFIC_LIGHT,
ACTION
FROM

how to rename a table
RENAME TABLE table-Name TO new-Table-Name

Table:
ADMIN.ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE;

RENAME TABLE ADMIN.ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE TO ADMIN.SmallTable

RENAME ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE TO SMALLTABLE;

How do I check all userss and their roles on Oracle SQL Developer WEB?
How to show all privileges from a user in oracle?

Create users which only have SELECT or READ privileges on the desired tables

Grant select on schema.table to userX;

GRANT select to STUDENT account:

To check level of access, as of Oracle 11g.

Replace USER with the desired username

Granted Roles:

SELECT *
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = ‘USER’;

Privileges Granted Directly To User:

SELECT *
FROM DBA_TAB_PRIVS
WHERE GRANTEE = ‘USER’;

Privileges Granted to Role Granted to User:

SELECT *
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT granted_role
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = ‘USER’);

Granted System Privileges:

SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE = ‘USER’;

For Hands ON activity Sign in

Instructor will provide a link and password for account:

Student01

Student02

Student03

Student04

Student05

Checklist

Lesson Plan: Introduction to Oracle SQL Developer Web: Data Queries and Database Management

Prerequisites:

Proficient with web browsers
Basic experience with Microsoft Excel or equivalent

Lesson Plan Overview

Objective:

By the end of this session, participants will be able to navigate Oracle SQL Developer Web, execute SQL queries, and export data in multiple formats.

Session Breakdown

Introduction & Objectives

Introduction to Oracle SQL Developer Web
Overview of workshop goals
- Interface navigation
- Running SQL queries and scripts
- Exporting data in various formats
Practical application of data-driven decision-making

Module 1: Getting Started with Oracle SQL Developer Web

Topics:

Logging in to Oracle SQL Developer Web
Exploring the Dashboard and interface components
- Navigation Menu (SQL, RESTful services, Data Modeler)
- SQL worksheet
- Schema browser

Hands-on Activity:

Walkthrough of navigating the interface
Opening the worksheet for query execution

Module 2: Running SQL Queries and Scripts

Topics:

Writing basic SQL SELECT statements
Executing scripts to manage and manipulate data

Hands-on Activity:

Write and execute a simple SELECT query to retrieve data from an example table (e.g., EMPLOYEES):

sql

Copy code

SELECT employee_id, first_name, last_name, department_id

FROM employees

WHERE department_id = 10;

Module 3: Advanced Query Techniques

Topics:

Aggregation functions: SUM, COUNT, AVG, MAX, MIN
Grouping and filtering data using GROUP BY and HAVING

Hands-on Activity:

Example: Joining employees and departments tables to retrieve department names:

SELECT department_id, COUNT(employee_id) AS total_employees

FROM employees

GROUP BY department_id

HAVING COUNT(employee_id) > 5;

Module 4: Exporting Data

Topics:

Exporting query results to CSV, Excel, and JSON formats
Configuring export options and managing file formats

Hands-on Activity:

Example of exporting employee data to CSV:
- Run query:

sql

Copy code

SELECT employee_id, first_name, last_name, department_id

FROM employees;

Export the result to a CSV file:
- Navigate to “Export” in the worksheet
- Select format (CSV, XLS, etc.)
- Save the file

Conclusion & Q&A

Recap of key concepts

Examples

1. Navigating Oracle SQL Developer Web

Login: Open Oracle SQL Developer Web via a web browser, log in with credentials.
Navigate: Use the left navigation menu to access different features (SQL, Data Models).
Worksheet: Open the worksheet from the toolbar to begin writing queries.

2. Running SQL Statements and Scripts

Basic Query: Retrieve employee names from the EMPLOYEES table:

sql

Copy code

SELECT first_name, last_name FROM employees;

3. Export Data

After running a query, export the result set by clicking the “Export” button.
Choose the export format: CSV, XLS, or JSON.
Save the exported data to your system for further use.

Work with the BBQ list Table

Select queries enable you to select all or specific columns

Example, show all columns of table bbq

SELECT

bbq.*

FROM

ADMIN.ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE_1 bbq;

Shortcut: CTL with ENTER enables you to run a query.

Example: Show specific columns

SELECT

bbq.DESCRIPTION,

bbq.CATEGORY,

bbq.QUANTITY,

bbq.UNIT_PRICE

FROM

ADMIN.ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE_1 bbq;

Example: Multiply two columns to calculate a CALC_subtotal

SELECT

bbq.DESCRIPTION,

bbq.CATEGORY,

bbq.QUANTITY,

bbq.UNIT_PRICE

bbq.QUANTITY * bbq.UNIT_PRICE CalculatedSubtotal

FROM

ADMIN.ADVANCED_EXCEL_BELLMORE_MERRICK_GLEN_COVE_1 bbq;

https://gbbc386fafe6ff5-oraclesandbox.adb.us-ashburn-1.oraclecloudapps.com/ords/student01/_sdw

https://gbbc386fafe6ff5-oraclesandbox.adb.us-ashburn-1.oraclecloudapps.com/ords/student02/_sdw

https://gbbc386fafe6ff5-oraclesandbox.adb.us-ashburn-1.oraclecloudapps.com/ords/student03/_sdw

https://gbbc386fafe6ff5-oraclesandbox.adb.us-ashburn-1.oraclecloudapps.com/ords/student04/_sdw

https://gbbc386fafe6ff5-oraclesandbox.adb.us-ashburn-1.oraclecloudapps.com/ords/student05/_sdw

https://gbbc386fafe6ff5-oraclesandbox.adb.us-ashburn-1.oraclecloudapps.com/ords/student06/_sdw

Juan Hinojosa’s website

Oracle SQL Developer Web Programming

Small Business Accounting

Intermediate Oracle SQL Workshop

Introduction to Oracle SQL

Intro to Oracle Security features of privileges, grant and revoke

Practice Activity: Select Query

The Versatility of VIEWS

Practice Activity: Insert Data using INERST INTO

Add two courses

Add one instructor

Practice Activity: Update a record using UPDATE query

Practice Activity: Run this Aggregate query to get Enrollment per course

Practice Activity: Insert new sections for the next semester by modifying the following code

Practice Activity: Deleting Records

Introduction to Oracle SQL

INTRODUCTION TO ORACLE SQL DEV WEB

Introduction to Oracle SQL Developer for the Web

Work with the BBQ list Table

Essential Oracle SQL Developer Web code examples

Right Join vs Left Join

More SQL to decipher and modify

INTRODUCTION TO ORACLE SQL DEV WEB: DATA QUERIES AND DATABASE MANAGEMENT

LAB COMPUTER SIGN IN

Key points found in Oracle but not in MS Access

For Hands ON activity Sign in

Checklist

Work with the BBQ list Table