Oracle database knowledge has been organized into 12 articles so far. This is not the end, but rather a beginning. I hope my articles can help beginners get started with databases more quickly. If you find these articles helpful, congratulations on your entry into the world of databases! There's so much knowledge in databases that it can sometimes feel overwhelming. This article will help you quickly learn various querying techniques in Oracle:

Oracle Course Outline:

Chapter 1: Oracle Database Login

Chapter 2: Basic Oracle Database Operations

Chapter 3: Oracle Database Data Types and Constraints

Chapter 4: Creating Tables in Oracle Database

Chapter 5: Inserting, Modifying, and Deleting Data in Oracle Database

Chapter 6: Single Table Queries in Oracle Database

Chapter 7: Date Function Handling in Oracle Database

Chapter 8: Column Operations in Oracle Database

Chapter 9: Multi-table Queries Part 1

Chapter 10: Multi-table Queries Part 2

Chapter 11: Grouping Queries in Oracle Database

Chapter 12: Subqueries in Oracle Database

If you want to practice with some database exercises, the following examples might be a good choice!

-- Exercise 1 -- New employee Wang Xiaoming, employee ID is 11, gender is male, age 30, position ID is 5, position is Test Engineer, department ID is 3, -- department name is Testing Department, salary 6000 (base salary 2800, bonus 3200);

select * from compensation; -- Compensation table
select * from employees; -- Employees table
select * from departments; -- Departments table
select * from positions; -- Positions table

Exercise 1 Solution

-- Compensation table
insert into compensation(comp_id,emp_id,base_salary,bonus) values(11,11,2800,3200);
commit;

-- Employees table
insert into employees(emp_name,emp_id,gender,age,pos_id,dept_id) values('Wang Xiaoming',11,'Male',30,5,3);
commit;

-- Departments table
insert into departments(dept_id,dept_name) values(3,'Testing Department');
commit;

-- Positions table
insert into positions values(5,'Test Engineer');
commit;

-- Exercise 2 -- Wang Xiaoming has passed his probation period and performed exceptionally well. The company decides to give him a 10% raise in base salary and 15% in bonus;

select * from compensation; -- Compensation table
select * from employees; -- Employees table
select * from departments; -- Departments table
select * from positions; -- Positions table


update compensation
set base_salary = base_salary + base_salary * 0.1,
bonus = bonus + bonus * 0.15
where comp_id = 11;
commit;

-- Exercise 3 -- Query the highest, lowest, and average salary in the Testing Department, displaying the highest, lowest, and average salary;

select * from compensation; -- Compensation table
select * from employees; -- Employees table
select * from departments; -- Departments table
select * from positions; -- Positions table


select t2.dept_name Department,
max(t3.base_salary + t3.bonus) Highest_Salary,
min(t3.base_salary + t3.bonus) Lowest_Salary,
avg(t3.base_salary + t3.bonus) Average_Salary
from employees t1, departments t2, compensation t3
where t1.emp_id = t3.emp_id
and t2.dept_id = t1.dept_id
and t2.dept_id = 3
group by t2.dept_name;

-- Exercise 4 -- Query the highest, lowest, and average salary for all departments, displaying department, highest salary, lowest salary, and average salary, and sort by department name in ascending order;

select * from compensation; -- Compensation table
select * from employees; -- Employees table
select * from departments; -- Departments table
select * from positions; -- Positions table<br></br>
select t3.dept_id Department_ID, t3.dept_name Department_Name,
max(t2.base_salary+t2.bonus) Highest_Salary,
min(t2.base_salary+t2.bonus) Lowest_Salary,
avg(t2.base_salary+t2.bonus) Average_Salary
from employees t1, compensation t2, departments t3 where t1.emp_id=t2.emp_id and t1.dept_id=t3.dept_id 
group by t3.dept_id,t3.dept_name order by t3.dept_name asc;

-- Exercise 5 -- Count how many employees are in the Testing Department, displaying the employee count;

select * from compensation; -- Compensation table
select * from employees; -- Employees table
select * from departments; -- Departments table
select * from positions; -- Positions table

select t2.dept_name Department_Name, count(t1.emp_id) Employee_Count
from employees t1, departments t2
where t1.dept_id = t2.dept_id
and t2.dept_id = 3
group by t2.dept_name;

-- Exercise 6 -- Count employees in all departments and sort by deparmtent in ascending order, displaying department and employee count;

select * from compensation; -- Compensation table
select * from employees; -- Employees table
select * from departments; -- Departments table
select * from positions; -- Positions table<br></br>
select t2.dept_name Department_Name, count(t1.emp_id) Total_Employees
from employees t1, departments t2
where t1.dept_id = t2.dept_id
group by t2.dept_name
order by t2.dept_name asc;

-- Exercise 7 -- Query all employee information for those with the last name Wang;

select * from compensation; -- Compensation table
select * from employees; -- Employees table 
select * from departments; -- Departments table
select * from positions; -- Positions table


select *
from employees t1, compensation t2, departments t3, positions t4
where t1.emp_id = t2.emp_id
and t1.dept_id = t3.dept_id
and t1.pos_id = t4.pos_id
and t1.emp_name like 'Wang%'
order by t1.emp_id;

-- Exercise 8 -- Calculate average salary by department and gender;

select t2.dept_name Department_Name,
t1.gender Gender,
round(avg(t3.base_salary + t3.bonus)) Average_Salary
from employees t1, departments t2, compensation t3
where t1.dept_id = t2.dept_id
and t1.emp_id = t3.emp_id
group by t2.dept_name, t1.gender;

-- Exercise 9 -- Query the average salary of employees aged 30 to 40;

select t1.emp_name Employee_Name, avg(t2.base_salary + t2.bonus) Average_Salary
from employees t1, compensation t2
where t1.emp_id = t2.emp_id
and t1.age between 30 and 40
group by t1.emp_name;

-- Exercise 10 -- Query the highest-paid employee in the Testing Department, displaying the employee name;

select * from compensation; -- Compensation table
select * from employees; -- Employees table 
select * from departments; -- Departments table
select * from positions; -- Positions table


select t1.emp_name Employee_Name,
t2.dept_name Department_Name,
max(t3.base_salary + t3.bonus) Salary
from employees t1, departments t2, compensation t3
where t1.dept_id = t2.dept_id
and t1.emp_id = t3.emp_id
and t2.dept_id = 3
group by t2.dept_name, t1.emp_name;

-- Exercise 11 -- Delete all information about Wang Xiaoming

select * from compensation; -- Compensation table
select * from employees; -- Employees table 
select * from departments; -- Departments table
select * from positions; -- Positions table


delete from compensation where comp_id=11;
delete from employees where emp_name='Wang Xiaoming';
delete from departments where dept_id=3;
delete from positions where pos_id=5;