This project provides a comprehensive SQL script to create and manage a Company Database System. It covers essential business entities such as Employees, Departments, Training Courses, Instructors, and Leave Requests.
The database is designed with data integrity in mind, using primary keys, foreign keys, and various constraints (CHECK, UNIQUE, NOT NULL).
- Database Schema: Complete structure for 5 core business tables.
- Relationships: Fully established Foreign Key relationships.
- Data Integrity: Implementation of business rules through SQL constraints.
- Sample Data: Pre-loaded with initial data for testing and demonstration.
- Analytical Queries: A collection of 15+ complex queries including Joins, Aggregations, Subqueries, and Grouping.
The system consists of the following tables:
- Employees: Stores personal and salary information.
- Departments: Manages department names and manager assignments.
- Instructors: Details of trainers providing courses.
- TrainingCourses: Logs of available courses and their schedules.
- LeaveRequests: Tracks employee leave history and status (Pending/Approved/Rejected).
The script includes advanced SQL scenarios such as:
- Average salary per department.
- Mapping instructors to courses.
- Finding the highest-paid employee in each department.
- Filtering employees based on department size using
HAVING. - Tracking leave request statistics.
- Open SQL Server Management Studio (SSMS) or Azure Data Studio.
- Create a new query window.
- Copy and paste the content of
Company_Database.sql. - Execute (F5) to build the
HRdatabase and populate it with data.
Sabah Hijazi