LinkedIn

Practice Files

Introduction-to-Excel-Bellmore-Merrick-Glen-CoveDownload
Intermediate-Excel-Bellmore-Merrick-Glen-CoveDownload
Advanced-Excel-Bellmore-Merrick-Glen-CoveDownload
Introduction-to-Excel-10-26-2022Download
Prepare-Holiday-ListDownload
Prepare-Holiday-List-Pivot-Table-ReportDownload
Intermediate-Excel-Bellmore-Merrick-Glen-Cove-November-2023Download

INTERMEDIATE ORACLE

— START HERE code to view tickets in a user friendly layout

SELECT

— -Review the open tickets, filtered by Department_Name alias PROJECT

    T.TICKET_ID,

    T.TITLE,

    T.DESCRIPTION,

    T.CREATED_DATE,

    S.STATUS_NAME,

    P.PRIORITY_NAME,

    D.DEPARTMENT_NAME,

    U.USERNAME AS CREATED_BY

FROM

    TICKETS T

JOIN TICKET_STATUSES S ON T.STATUS_ID = S.STATUS_ID

JOIN TICKET_PRIORITIES P ON T.PRIORITY_ID = P.PRIORITY_ID

JOIN USERS U ON T.USER_ID = U.USER_ID

LEFT JOIN DEPARTMENTS D ON T.ASSIGNED_DEPARTMENT_ID = D.DEPARTMENT_ID

WHERE

    S.STATUS_NAME = ‘Open’

    — AND D.DEPARTMENT_NAME = ‘Bathroom’

ORDER BY

TICKET_ID DESC

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

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?

FeatureOracle SQL DeveloperMS Access
PurposeOracle 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.
ScalabilityOracle 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 SupportSupports 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 LanguageUses 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.
InterfaceOracle 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/ImportExtensive 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.
StrengthsHighly 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.
WeaknessesMore 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;