Create a Practice Schema and Run Your First Queries
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
| Condition | Example |
|---|---|
| Equal | WHERE city = 'Mumbai' |
| Not equal | WHERE status <> 'CANCELLED' |
| Range | WHERE price BETWEEN 10 AND 100 |
| List | WHERE city IN ('Mumbai','Delhi') |
| Pattern | WHERE full_name LIKE 'A%' |
| Missing value | WHERE 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.

