Create a Practice Schema and Run Your First Queries

Oracle DBA Certification 14 min min read Updated: Nov 16, 2026 Beginner
Create a Practice Schema and Run Your First Queries
Beginner Topic 1 of 5

Why SQL First?

A DBA reads and writes SQL all day: checking sessions, finding big tables, fixing data. In this section you learn the SQL a DBA needs, using a small practice schema called SHOP.

Step 1: Create the User (as DBA)

Connect as SYSTEM to the pluggable database, not the root:

sql sqlplus system/YourStrongPwd1@localhost:1521/FREEPDB1
sql CREATE USER shop IDENTIFIED BY "ShopPwd1" DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO shop;
What did we do? A user owns a schema (a set of tables and other objects). We gave SHOP the right to log in and create objects, plus space in the USERS tablespace.

Step 2: Create the Tables (as SHOP)

sql CONNECT shop/ShopPwd1@localhost:1521/FREEPDB1 CREATE TABLE customers ( customer_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, full_name VARCHAR2(60) NOT NULL, city VARCHAR2(40), email VARCHAR2(80) UNIQUE, created_on DATE DEFAULT SYSDATE ); CREATE TABLE products ( product_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, product_name VARCHAR2(60) NOT NULL, price NUMBER(10,2) NOT NULL ); CREATE TABLE orders ( order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id NUMBER NOT NULL REFERENCES customers(customer_id), product_id NUMBER NOT NULL REFERENCES products(product_id), quantity NUMBER(4) NOT NULL, order_date DATE DEFAULT SYSDATE, status VARCHAR2(10) DEFAULT 'NEW' );

Step 3: Add Sample Data

sql INSERT INTO customers (full_name, city, email) VALUES ('Asha Rao', 'Mumbai', 'asha@example.com'); INSERT INTO customers (full_name, city, email) VALUES ('Ben Carter', 'London', 'ben@example.com'); INSERT INTO customers (full_name, city, email) VALUES ('Chen Wei', 'Delhi', 'chen@example.com'); INSERT INTO customers (full_name, city, email) VALUES ('Dina Haddad', 'Mumbai', 'dina@example.com'); INSERT INTO customers (full_name, city, email) VALUES ('Evan Brooks', NULL, 'evan@example.com'); INSERT INTO products (product_name, price) VALUES ('Laptop', 800); INSERT INTO products (product_name, price) VALUES ('Mouse', 15); INSERT INTO products (product_name, price) VALUES ('Keyboard', 40); INSERT INTO products (product_name, price) VALUES ('Monitor', 220); INSERT INTO orders (customer_id, product_id, quantity, status) VALUES (1, 1, 1, 'SHIPPED'); INSERT INTO orders (customer_id, product_id, quantity, status) VALUES (1, 2, 2, 'SHIPPED'); INSERT INTO orders (customer_id, product_id, quantity, status) VALUES (2, 3, 1, 'NEW'); INSERT INTO orders (customer_id, product_id, quantity, status) VALUES (3, 4, 2, 'SHIPPED'); INSERT INTO orders (customer_id, product_id, quantity, status) VALUES (3, 2, 5, 'CANCELLED'); INSERT INTO orders (customer_id, product_id, quantity, status) VALUES (4, 1, 1, 'NEW'); COMMIT;

SELECT Basics

sql SELECT * FROM customers; SELECT full_name, city FROM customers; SELECT product_name, price, price * 1.18 AS price_with_tax FROM products;

Filtering with WHERE

ConditionExample
EqualWHERE city = 'Mumbai'
Not equalWHERE status <> 'CANCELLED'
RangeWHERE price BETWEEN 10 AND 100
ListWHERE city IN ('Mumbai','Delhi')
PatternWHERE full_name LIKE 'A%'
Missing valueWHERE city IS NULL
NULL warning: NULL means unknown. city = NULL never matches anything. Always write city IS NULL.

Sorting and Limiting

sql SELECT product_name, price FROM products ORDER BY price DESC FETCH FIRST 2 ROWS ONLY;
Result

You get Laptop (800) and Monitor (220). FETCH FIRST works in 12c and later, so it is available in 19c and 26ai.

Conclusion

You created a practice schema and learned SELECT, WHERE, ORDER BY and FETCH FIRST. We reuse the SHOP tables in the next lessons.

Quick Summary: Use IS NULL for missing values, ORDER BY to sort and FETCH FIRST n ROWS ONLY to limit rows.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators