Introduction to Databases and SQL in MS Access 2400-SP-SQL-MSA
The goal of this course is to introduce the concept of relational databases and to familiarize students with MS Access.
Course topics:
✓ The Structure of MS Access
The Ribbon, overview of the program’s features and capabilities, similarities and differences between Access and Excel.
✓ Tables
Introduction to the table environment, rules for creating and modifying tables, object properties, data types, basic data operations, keys, importing data into Access, linking an Excel worksheet to Access.
✓ Data Transformation
Removing duplicates from databases, filling in missing data, working with text fields (text functions, concatenating text, changing case, finding and replacing text), simple calculations, date processing functions.
✓ Relational Databases
Definition, overview of the structure of a relational database, advantages of storing information in multiple tables, comparison of a relational database and a flat file, types of relationships in databases.
✓ Queries
Introduction to queries, aggregate queries along with aggregation functions, functional queries (create, delete, join, update), cross-tab queries.
✓ Calculation Fields
Using functions in MS Access; comparing Excel and Access spreadsheet functions.
✓ Conditional Analysis
Queries with parameters; conditional functions (IIF, Switch).
✓ SQL
Introduction to SQL, the SELECT statement, the WHERE clause, grouping using the GROUP BY and HAVING clauses, sorting using ORDER BY, creating aliases, functional queries, special queries (UNION, CREATE TABLE, ALTER TABLE).
✓ Reporting
Report creation, conditional formatting, data visualization, pivot tables and charts in Microsoft Access.
Course coordinators
Type of course
Mode
Learning outcomes
Participants will gain the ability to use MS Access independently and learn how to take advantage of its versatile features. They will be able to manage databases at a basic level, analyze data by creating queries in SQL, and present the results of their analyses in attractively formatted reports.
Assessment criteria
An independent assessment task to be completed by the participant upon course completion. A minimum score of 50% is required to pass.
Bibliography
Materials prepared by the lecturer and made available to participants on e-learning platform