Lessons

Resources

There is no database file for this class
PDF Handbook
Full Video ZIP File (54 MB)
Full Course Outline
Skills Test Quiz - Under Construction
Certificate of Completion
Post-Class Survey

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for SQL 3 Seminar Lessons

Age	Subject	From
2 years	Cannot DROP COLUMN with SQL	Sami Shamma
3 years	Pass Passwd to OpenDatabase	Sami Shamma
4 years	Syntax Error 3290 in Procedure	Despina Karayanni

Subscribe to SQL 3 Seminar Lessons
Get notifications when this page is updated

Intro

In this lesson, you will learn advanced Microsoft Access SQL techniques, including how to create and modify tables, use different SQL data types, add indexes and constraints, set up relationships and enforce referential integrity, and use ANSI92 SQL. We will also cover stored views, procedures, and user-level security, with plenty of examples and practical tips along the way. This is part 3.

Transcript

Welcome to the Access SQL Seminar Part 3, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Part 1 in the series was to teach you basic SQL, such as Select Queries, to select and view data. Part 2 focused mostly on data manipulation, including Update Queries, Append Queries, and deleting records from tables.

In Part 3, which is this class, we will focus mostly on database design, developing tables, views, and procedures, and modifying the structure of the database from our SQL. We will learn how to create and modify tables using nothing more than SQL. We will learn about the different SQL data types and how they are similar to Access data types. We will learn how to index fields, such as setting up a primary and foreign key, and how to use certain constraints to control the data that is entered. We will learn how to set up relationships between tables and enforce referential integrity.

We will learn about ANSI92 SQL, which is an upgraded version of SQL that Access supports but does not normally use. We will learn how to create stored views and procedures, which, while using Access, for all intents and purposes are pretty much the same as queries. We will also learn about database security.

Again, this is Part 3 of a 3-part series. So, if you have not watched Parts 1 and 2 of the SQL Seminar, I strongly recommend you watch those first before beginning this lesson.

I will be using Access 2010 for the videos in class today. I will be showing you some of the screens from Access 2003 because a few of the menus are different. Most of the material that I am going to show you today works for all versions of Access, with the exception that lessons 7 through 10 are not valid for Access 2000 and earlier. As long as you have Access 2002, XP, 2003, 2007, or 2010, everything is fine.

If you are using Access 2000 or earlier, I am sorry, but the section on ANSI92 SQL is not compatible with Access 2000 or earlier. If you do have that older version of Access, I strongly recommend you upgrade to at least Access 2003. However, you still will be able to enjoy most of the material in this class.

Now, let us take a moment to go over exactly what is covered in each lesson of today's class.

In lesson 1, we are going to learn how to create a table using SQL. In lesson 2, we are going to learn about all the different Access SQL data types. In lesson 3, we are going to take a look at indexing our fields with SQL. In lesson 4, we are going to learn about altering table structure from SQL. In lesson 5, we are going to learn about constraints, which give us more control over our tables.

In lesson 6, we are going to learn about relationships and how to set up foreign keys in our SQL. In lesson 7, we are going to learn about ANSI92 standard SQL and how it is different from Classic Access SQL. In lesson 8, we are going to learn how to use SQL to set a check constraint, which is basically a validation rule, and a default value. In lesson 9, we are going to talk about referential integrity with cascade updates and deletes. In lesson 10, we are going to talk about views and procedures, which are basically stored queries. In lesson 11, we are going to talk about user-level security and how it works with SQL.

If you have any questions while you are taking this class, please feel free to post your questions in the Access Forum on the website. You will find we have a very active community of users, and either myself or one of them will be happy to answer your questions. If you would like to contact me directly and you do not want to post in the forum, feel free to use the contact page at accesslearningzone.com/contact.

Now sit back, relax, and get ready to enjoy the Access SQL Seminar Part 3.

Quiz

Q1. What is the main focus of Part 3 of the Access SQL Seminar?
A. Data manipulation such as updating and appending records
B. Creating reports and forms using SQL
C. Database design, table development, views, and procedures using SQL
D. Advanced VBA automation using SQL

Q2. Which topic is specifically covered in lesson 3 of this class?
A. Setting up relationships between tables
B. Indexing fields with SQL
C. Database security using SQL
D. Creating tables using SQL

Q3. Why should users watch Parts 1 and 2 before starting Part 3?
A. Part 3 repeats information from the first two parts
B. Parts 1 and 2 are more advanced than Part 3
C. Parts 1 and 2 provide foundational knowledge needed for Part 3
D. Part 3 does not reference the previous lessons at all

Q4. Which Access versions are fully compatible with all material covered in this class except for lessons 7 through 10?
A. Access 2000 and earlier
B. Access 97 only
C. Access 2002, XP, 2003, 2007, and 2010
D. Only Access 2010

Q5. What is not supported in Access 2000 or earlier according to the video?
A. Creating tables with SQL
B. ANSI92 SQL standard
C. Setting index fields
D. Referencing queries

Q6. What are views and procedures considered to be in the context of Access, as stated in lesson 10?
A. Table definitions
B. Stored queries
C. Macros
D. Security settings

Q7. Which lesson covers setting up foreign keys and relationships via SQL?
A. Lesson 2
B. Lesson 4
C. Lesson 6
D. Lesson 8

Q8. What should a student do if they have questions during the class?
A. Wait until after finishing the class to ask questions
B. Post questions in the Access Forum or use the contact page
C. Email other students directly
D. Only contact the instructor via phone

Q9. What is covered in lesson 8 of this class?
A. How to create tables using SQL
B. How to set constraints such as validation rules and defaults
C. How to upgrade Access to a newer version
D. How to use VBA with SQL

Q10. What topic is discussed in lesson 11?
A. Creating and modifying table structures
B. User-level security and its application in SQL
C. Designing reports and charts
D. Data import and export techniques

Answers: 1-C; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Part 3 of the Access SQL Seminar series. My name is Richard Rost, and I will be guiding you through this lesson.

In the first part of this series, we looked at the basics of SQL, such as SELECT queries, which allow you to select and view data in your database. The second part concentrated on data manipulation tasks, including how to update records, append new data, and delete information from your tables.

Now, in Part 3, we will turn our attention to database design. Our focus will be on developing tables, creating and modifying views and procedures, and changing the structure of your database using just SQL. I will show you how to both create and modify tables using SQL statements alone. We will cover the different SQL data types available and compare them with the corresponding types in Access. You will learn how to set up indexes for your tables, including defining primary and foreign keys, and how to use constraints to ensure you maintain control over the type of data that gets entered. Setting up relationships between tables and enforcing referential integrity will also be explained.

This class will introduce you to ANSI92 SQL, which is a more advanced version of SQL that Access can use, although it does not default to this version. I will explain how to create stored views and procedures in Access, which function basically as queries. We will also touch on the topic of database security.

Since this is the third part of a three-part series, if you have not already completed Parts 1 and 2, I highly recommend starting with those before moving to this session.

For these lessons, I will mostly be demonstrating using Access 2010. Occasionally, I will show you screens from Access 2003, as some menus look different in that version. The vast majority of topics covered here apply to all recent versions of Access, with the exception of lessons 7 through 10, which are not compatible with Access 2000 or earlier. If you have Access 2002, XP, 2003, 2007, or 2010, you are all set.

If you are using Access 2000 or an earlier version, be aware that the material on ANSI92 SQL will not work. In that case, I would advise upgrading to at least Access 2003. Even so, most of the material in this seminar will still be relevant and useful.

Let me give you a rundown of what is included in each lesson for today's class:

Lesson 1 is all about creating tables with SQL. In lesson 2, we will cover the various Access SQL data types. Lesson 3 will focus on how to index fields using SQL. In lesson 4, we will see how to alter the structure of tables via SQL commands. Lesson 5 deals with adding constraints for greater control over your tables.

In lesson 6, the emphasis is on setting up relationships and configuring foreign keys with SQL. Lesson 7 introduces you to the ANSI92 standard for SQL and shows the differences from classic Access SQL. Lesson 8 will teach you how to add check constraints, which work like validation rules, and how to set default values. Lesson 9 covers referential integrity, including cascading updates and deletes. In lesson 10, we will discuss views and procedures, which are essentially saved queries. Finally, lesson 11 goes over user-level security and its integration with SQL.

If you have any questions while working through this class, you are always welcome to post them on the Access Forum on our website. We have a very active community, and either I or another community member will be glad to help you with any issues. If you prefer to contact me directly instead of using the forum, you can use the contact page found at accesslearningzone.com/contact.

So get comfortable and prepare to expand your skills with Access SQL Seminar Part 3. A complete video tutorial with step-by-step instructions covering everything discussed here is available on my website at the link below.

Live long and prosper, my friends.

Topic List

Creating tables using SQL

Access SQL data types overview

Indexing fields with SQL

Altering table structure via SQL

Using constraints for table control

Setting up relationships with SQL

Defining foreign keys in SQL

Introduction to ANSI92 SQL standard

Setting check constraints and default values in SQL

Enforcing referential integrity with cascade options

Creating views and procedures in Access SQL

User-level security with SQL