Core Oracle SQL Statements for Data Retrieval and Modification
Data Retrieval Fundamentals
Retrieve specific columns from a dataset:
SELECT first_name, last_name, email
FROM employees;
Eliminate duplicate values from results:
SELECT DISTINCT department_id
FROM employees;
Filter records based on conditions:
SELECT product_name, unit_price
FROM inventory
WHERE unit_price > 100
AND stock_quantity > 0;
Sort output by multiple columns:
SELECT last_name, hire_date, salary
FROM employees
ORDER BY hire_date DESC, last_name ASC;
Aggregatnig Data
Group records for summary sattistics:
SELECT department_id, AVG(salary) as avg_salary, COUNT(*) as headcount
FROM employees
GROUP BY department_id;
Filter grouped results:
SELECT region, SUM(annual_sales) as total_revenue
FROM sales_data
GROUP BY region
HAVING SUM(annual_sales) > 500000;
Combining Datasets
Merge related tables:
SELECT e.emp_id, e.full_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;
Combine results from multiple queries:
SELECT customer_id, company_name
FROM corporate_clients
UNION
SELECT customer_id, company_name
FROM individual_clients;
Modifying Table Contents
Add new records:
INSERT INTO project_assignments (proj_id, emp_ref, start_date, role)
VALUES (101, 205, DATE '2024-01-15', 'Senior Developer');
Udpate existing data:
UPDATE inventory
SET reorder_level = 50,
last_updated = SYSDATE
WHERE supplier_code = 'ACME-42';
Remove specific rows:
DELETE FROM audit_logs
WHERE created_timestamp < ADD_MONTHS(SYSDATE, -12);
