Database Analysis of Employees and Departments

To design, implement, test and demonstrate elements of relational database applications – as well as provide evidence for database analysis

Objective

• Implement and Test 'SELECT' SQL queries using the Oracle APEX SQL tool to retrieve information from a database to meet business analysis requirements.
• Using logical relational database design with appropriate software tools and apply SQL to create database tables with integrity constraints.
• Apply the APEX Application Builder to develop and test a web-based application, including report pages and form pages as well as a suitable home page providing the navigation.
• As well as to learn, reflect and evaluate through demonstrating engagement.

Installation

• Must have Oracle APEX Application

• Download the SQL_Schema.sql

  (Will eventually publish URL Soon)

Outline of Content

This repository covers the essential concepts and practices of Database Management and Structured Query Language (SQL), divided into three key blocks.

• Block 1 introduces foundational topics including the database environment, RDBMS, Entity Relationship Diagrams (ERDs), and SQL basics, progressing to advanced retrieval operations and practical SQL programming.
• Block 2 focuses on designing and implementing databases with integrity, including data modeling, logical design, normalization, and database administration, with hands-on exercises in table creation, data manipulation, and constraint management.
• Block 3 explores the development and deployment of database applications, delving into big data technologies, data analytics, and practical experience using the Oracle APEX Application Builder. This structured approach equips learners with the skills needed for effective database management and application development.

Code

A list of annual salary of each employee who does not earn commission, in surname alphabetical order:

SELECT LAST_NAME, SALARY 
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL
ORDER BY LAST_NAME;

Simply put this query retrieves the last names and salaries of employees who don't have a commission percentage specified and it arranges them alphabetically by last name. Shown in Figure 1

Figure 1

A list of departments, corresponding city, country and region names - in city name order

SELECT DEPARTMENT_NAME,CITY,COUNTRY_NAME,REGION_NAME
FROM
  DEPARTMENTS D
JOIN
  LOCATIONS L ON D.LOCATION_ID = C.COUNTRY_ID
JOIN
  REGIONS R ON C.REGION_ID = R.REGION_ID
ORDER BY
L.CITY;

This query retrieves department-related information along with the corresponding city, country, region details and sorts the results based on city names. Shown in Figure 2

Figure 2

The Total number of employees in each department and their average salary - the department name should be included

SELECT
  D.DEPARTMENT_NAME,
  COUNT(E.EMPLOYEE_ID) AS TOTAL_EMPLOYEES.
  AVG(E.SALARY) AS AVERAGE_SALARY
FROM
  DEPARTMENTS.D
LEFT JOIN
  EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY
  D.DEPARTMENT_NAME
ORDER BY
  D.DEPARTMENT_NAME;

In summary, this query retrieves department-related information along with the total number of employees and the average salary for each department, ensuring that all departments are included within the result set as well as it order the result set by department name. Shown in Figure 3.

Figure 3

ERD design effectively captures the structure needed to manage the training courses, the departments that offer them, the scheduling of course sessions and the employees attending these sessions. It ensures that the relationships between entities are clearly defined, facilitating efficient data management and retrieval. Shown in Figure. Shown in Figure 4.

Figure 4

TRAINING_COURSE_TABLE 
CREATE TABLE Training_Course (
  COURSE_ID NUMBER PRIMARY KEY,
  COURSE_NAME VARCHAR2(255),
  DURATION VARCHAR2(50),
  DEPARTMENT_ID NUMBER,
  CONSTRAINT DEPARTMENT_ID FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);

Creates a table for storing information about training course which includes to add name, description, durations and the departments associated with them. It ensures data integrity by establishing a foreign key constraint between the "Training_Course" table and the "DEPARTMENTS" table. *Figure 5 shows the table creation of Training Course.

Figure 5

COURSE__SESSION TABLE

CREATE TABLE CourseSession (
  SESSION_ID INT PRIMARY KEY,
  START_DATE DATE,
  END_DATE DATE,
  EMPLOYEE_ID INT,
  COURSE_ID INT,
  FOREIGN KEY (EMPLOYEE_ID) REFERENCES Employees(Employee_ID),
  FOREIGN KEY (COURSE_ID) REFERENCES TRAINING_COURSE(COURSE_ID)
);
ALTER TABLE COURSESESSION RENAME TO COURSE_SESSION;

This SQL script creates a table to manage course sessions, linking them to employees, training courses and then renames the table to follow a naming a convention. Figure 6 shows the table creation of Course Sessions

Figure 6

ALTERED EMPLOYEES TABLE

ALTER TABLE EMPLOYEES
ADD CONSTRAINT SESSION_ID
FOREIGN KEY (SESSION_ID) REFERENCES COURSE_SESSION (SESSION_ID);

This SQL statement modifies the employees table by adding a foreign key constraint named SESSION_ID. It ensures that the SESSION_ID column in the COURSE_SESSION table, maintaining data integrity by enforcing that every SESSION_ID in the employees table must correspond towards an existing SESSION_ID within the COURSE_SESSION Table. Shown in Figure 7

Figure 7

Contributions!

Contributions are welcome! Please fork this repo and submit a pull request with your improvements!