SQL Projects
SQL Query for Faculty Information in Access
This project involved creating an SQL query in Microsoft Access to retrieve and display detailed information about faculty members based on their office location. The goal was to construct a query that combines data from multiple related tables and dynamically filters results based on user input.
Key Features and Implementation
Combining Data from Multiple Tables:
The query uses INNER JOIN statements to combine data from three different tables: Faculty, Office, and CourseOffering. By linking these tables based on common fields (FacultyID and OfficeNo), the query consolidates faculty information along with their office details and course offerings.
User Input for Dynamic Filtering:
The query includes a WHERE clause that prompts the user for input: [Desired Floor?]. This allows the user to filter the results by specifying a particular floor (represented by the RoomNo field), making the query more dynamic and interactive.
Custom Formatting of Results:
A calculated field, FullName, is created by concatenating the FacultyLastName and FacultyFirstName fields, providing a clear and readable format for displaying the faculty members' names. This calculated field enhances the usability and presentation of the query results.
Organized Output:
The ORDER BY clause is used to sort the results alphabetically by the faculty's last name and first name, ensuring that the output is well-organized and easy to navigate.
Lessons Learned
This SQL project enhanced my understanding of several important database concepts and techniques:
Using Joins to Combine Data: I gained practical experience in using INNER JOIN statements to retrieve related data from multiple tables. This is crucial for creating complex queries that require data from different parts of a database.
Dynamic User Input in SQL Queries: I learned how to prompt users for input within a query, making the SQL statement more dynamic and allowing for real-time data filtering based on user preferences.
Creating Calculated Fields: By using expressions like concatenation (&), I was able to create calculated fields that improve the readability and presentation of query results.
Sorting Data for Better Usability: The use of the ORDER BY clause allowed me to present data in an organized manner, which is especially important for enhancing user experience when dealing with large datasets.
Overall, this project allowed me to strengthen my SQL skills in Access, particularly in creating queries that efficiently retrieve and present meaningful information based on user-defined criteria.
