From Chat to Code: Generate Complex SQL Queries for Oracle Apex

In this tutorial, I demonstrate how to interact with AI to generate complex SQL queries quickly. I showcase the process step by step using Oracle Apex workspace, downloading examples, creating SQL scripts, and utilizing AI services to produce detailed SQL statements. Viewers can learn how to effortlessly obtain SQL commands by speaking to the AI, saving time compared to manual query writing.

Step 1: Preparing the Prompts

First, we prepare the prompts that we will ask the AI to generate the desired SQL queries. Here are five prepared prompts:

Prompt 1

 

Plain Text

xxxxxxxxxx

 

1

Generate an SQL query that counts the number of employees in each department,

2

showing the department name and the number of employees.

Result 1

 

SQL

xxxxxxxxxx

 

1

-- SQL query that counts the number of employees in each department

2

select d.department_name,

3

       count(e.employee_id) as employee_count

4

  from departments d

5

  left join employees e

6

    on d.department_id = e.department_id

7

 group by d.department_name;

Explanation: This query counts the number of employees in each department. It uses a LEFT JOIN operation to ensure that all departments, including those without employees, are included in the result.

Step 2: Counting Managers and Employees

Prompt 2

 

Plain Text

xxxxxxxxxx

 

1

Generate an SQL query that shows the number of employees each manager supervises,

2

including the manager's first name, last name, and the employee count.

Result 2

 

SQL

xxxxxxxxxx

 

1

-- SQL query that shows the number of employees each manager supervises

2

select m.first_name,

3

       m.last_name,

4

       count(e.employee_id) as employee_count

5

  from employees m

6

  left join employees e

7

    on m.employee_id = e.manager_id

8

 group by m.first_name,

9

          m.last_name;

Explanation: This query shows the number of employees supervised by each manager. The LEFT JOIN operation ensures that managers without any subordinates are also included in the result.

Step 3: Detailed Employee Information

Prompt 3

 

Plain Text

xxxxxxxxxx

 

1

Generate an SQL query that retrieves the employee's first name, last name, salary,

2

department name, country, region, and their manager's first and last name.

3

The query should include all employees, even those without managers,

4

and it should also join the countries and regions tables to show 

5

the employee's location details.

Result 3

 

SQL

xxxxxxxxxx

20

 

1

-- SQL query that retrieves detailed information about employees

2

select e.first_name,

3

       e.last_name,

4

       e.salary,

5

       d.department_name,

6

       c.country_name,

7

       r.region_name,

8

       m.first_name    as manager_first_name,

9

       m.last_name     as manager_last_name

10

  from employees e

11

  join departments d

12

    on e.department_id = d.department_id

13

  join locations l

14

    on d.location_id = l.location_id

15

  join countries c

16

    on l.country_id = c.country_id

17

  join regions r

18

    on c.region_id = r.region_id

19

  left join employees m

20

    on e.manager_id = m.employee_id;

Explanation: This query retrieves detailed information about each employee, including their name, salary, department name, country, region, and their manager's name. The LEFT JOIN operation ensures that employees without managers are also included in the result.

Step 4: Salary Statistics per Department

Prompt 4

 

Plain Text

xxxxxxxxxx

 

1

Generate an SQL query that retrieves the total salary sum, average salary,

2

and number of employees per department, including departments with no employees.

3

The query should also include the department name.

Result 4

 

SQL

xxxxxxxxxx

 

1

-- SQL query that retrieves the total salary sum, average salary, and number of employees per department

2

select d.department_name,

3

       sum(e.salary)       as total_salary,

4

       avg(e.salary)       as average_salary,

5

       count(e.employee_id) as employee_count

6

  from departments d

7

  left join employees e

8

    on d.department_id = e.department_id

9

 group by d.department_name;

Explanation: This query retrieves the total salary sum, average salary, and number of employees per department. The LEFT JOIN operation ensures that departments without employees are also included in the result.

Step 5: Detailed Employee Analytics

Prompt 5

 

Plain Text

xxxxxxxxxx

 

1

Generate an SQL query that retrieves detailed employee analytics.

2

The query should include each employee's first and last name,

3

the department they currently work in, the number of jobs they 

4

have held, the duration of their employment in years,

5

their current salary, their starting salary, and the 

6

percentage increase in salary since they joined the company.

7

The query should also calculate the average salary 

8

increase per year of employment.

Result 5

 

SQL

xxxxxxxxxx

47

 

1

-- SQL query that retrieves detailed employee analytics

2

with employee_durations as (

3

    select e.employee_id,

4

           e.first_name,

5

           e.last_name,

6

           e.department_id,

7

           e.salary,

8

           e.hire_date,

9

           months_between(sysdate, e.hire_date) / 12 as employment_duration_years

10

      from employees e

11

),

12

employee_first_salaries as (

13

    select e.employee_id,

14

           e.first_name,

15

           e.last_name,

16

           min(salary) as starting_salary

17

      from employees e

18

     join job_history jh

19

        on e.employee_id = jh.employee_id

20

     group by e.employee_id,

21

              e.first_name,

22

              e.last_name

23

),

24

job_counts as (

25

    select e.employee_id,

26

           count(jh.job_id) as job_count

27

      from employees e

28

      left join job_history jh

29

        on e.employee_id = jh.employee_id

30

     group by e.employee_id

31

)

32

select ed.first_name,

33

       ed.last_name,

34

       d.department_name,

35

       jc.job_count,

36

       ed.employment_duration_years,

37

       efs.starting_salary,

38

       ed.salary as current_salary,

39

       ((ed.salary - efs.starting_salary) / efs.starting_salary  * 100) as salary_increase_percent,

40

       ((ed.salary - efs.starting_salary) / ed.employment_duration_years) as avg_salary_increase_per_year

41

  from employee_durations ed

42

  join departments d

43

    on ed.department_id = d.department_id

44

  join employee_first_salaries efs

45

    on ed.employee_id = efs.employee_id

46

  join job_counts jc

47

    on ed.employee_id = jc.employee_id;

Explanation: This query retrieves detailed analytics for each employee, including their name, the department they work in, the number of jobs they have held, the duration of their employment in years, their current salary, their starting salary, the percentage increase in salary since they joined the company, and the average salary increase per year of employment.

Summary

By using these prompts and the generated SQL queries, we can create complex data analyses and reports without manually writing extensive SQL queries. This saves time and reduces the likelihood of errors.

Call to Action

• Explore Further: Try these SQL queries in your own Oracle Apex workspace to gain a better understanding.
• Customize Queries: Adapt the generated SQL queries to your specific requirements to obtain even more detailed analyses.
• Leverage AI: Utilize AI services to optimize your SQL queries and perform complex data analyses faster and more efficiently.

 {fullWidth}