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
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.
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
ON
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
ON
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
ON
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
ON
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;