Add Your SQL 638216

Add Your Sql

MSAccess - Assignment 4 – Chapter 8 (30 pts) Instructions : Add your SQL command and results (using copy/paste and screen capture) to each question. Do NOT remove any existing content or images from this file. You MUST include both your SQL Command and Results from running that command in your response to each question.

1. (10 pts) List the guide number, first name, and last name for all guides. The first name should appear in lowercase letters and the last name should appear in uppercase letters.

NOTE: MSAccess uses the functions LCASE and UCASE whereas Oracle uses LOWER and UPPER.

Put your SQL command and results here:

2. (10 pts) Every guide receives their first review 6 months after their hire date. List the guide’s last name, first name, and hire date and his/her first review date. Use REVIEW_DATE as the column title.

NOTE: MSAccess uses the function DATEADD whereas Oracle uses the function ADD_MONTHS.

Put your SQL command and results here:

Paper For Above instruction

The task involves retrieving specific information about guides and their reviews from a database using SQL queries in MSAccess. The first query aims to list all guides with their guide number, first name in lowercase, and last name in uppercase. The second query calculates the date of each guide’s first review, which occurs six months after their hire date. Both queries require the use of specific functions—LCASE and UCASE for string case conversion, and DATEADD for date calculations in MSAccess. Accurate implementation of these queries will involve understanding of SQL string functions, date functions, and proper aliasing for clarity in results.

In terms of SQL syntax, for the first query, the SELECT statement will retrieve guide number, with applying LCASE to first name and UCASE to last name, respectively. For example:

SELECT guide_number, LCASE(first_name) AS first_name, UCASE(last_name) AS last_name FROM guides;

Results will display all guides aligned with the specified format.

For the second query, the focus is on computing the first review date by adding six months to the hire date, which is achieved via the DATEADD function in MSAccess:

SELECT last_name, first_name, hire_date, DATEADD('m', 6, hire_date) AS REVIEW_DATE FROM guides;

This will result in the list of guides with their corresponding first review date, giving valuable insights into review scheduling.

Conclusion

These SQL queries facilitate efficient data retrieval and analysis about guides, their personal information, and review timelines. Proper use of string and date functions ensures data is transformed into informative and human-readable formats, essential for management oversight and operational planning within the database system.

References

  • Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
  • Ambler, S. W. (2003). The Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema. Wiley.
  • Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, and Management. Cengage Learning.
  • Microsoft Support. (2022). Function Reference (Access).https://support.microsoft.com/en-us/office/
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  • Melton, J., & Simon, A. R. (1993). Understanding SQL. Morgan Kaufmann Publishing.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage.
  • Kumar, V. (2017). SQL Programming for Beginners. Packt Publishing.
  • Chen, P. P. (1976). The Entity-Relationship Model—Toward a Unified View of Data. ACM Transactions on Database Systems, 1(1), 9-36.

Related Assignments