ISYS 2263 – Principles of Information Systems
APPLYING SQL IN MS ACCESS 2013
This is a tutorial for applying SQL. In this tutorial you will be using MS Access 2013 for the execution of
SQL statements. In Appendix A, you will find the background information for the data (ERD and data
dictionary) that will be used in this tutorial. You can also find the SQL statements that will be used in
Appendix B & C of this tutorial. You will use this knowledge throughout this tutorial to demonstrate your
understanding of applying SQL.
Opening MS Access & Saving Your Database File
1. Open MS Access 2013.
2. In the new template options, click on the “Blank Desktop Database” option.
3. You will be prompted to enter a name and location for your new Access database. I recommend
naming your file something appropriate and in a location that you can easily access. If you’re on
VMWare, it should be in your documents section for future access to the file.
1
2
Fall 2015
1|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
4. In the initial screen that opens up, right click on the “Table 1” tab and choose Close. We do not
want to create our table from the datasheet view as given. So, we do not need this.
Executing SQL in MS Access
1. We are now going to execute one of our SQL CREATE statements. These can all be found in
Appendix B. You are not having to recreate these. Within MS Access, go to the Create tab and
click on the Query Design button on the ribbon within the Queries group.
2. Click Close on the Show Table Selection Dialog Box that appears. It will be blank (since we have
not created any tables).
3. In the top left corner of the MS Access window, click on the button that says SQL View.
4. This is where we will insert our SQL CREATE statement for execution. You can only run one SQL
statement at a time within MS Access. Other full DBMS systems will allow the execution of
multiple commands, as long as the semi-colons have been included in the notation. MS Access
does not have this capability. Copy the CREATE statement for the Student table from Appendix B
and paste it into this query window.
Click on Run in the top left corner
(next to where you clicked on SQL
View). Once it has executed without
2
error, you will see the table appear
4
on the left. Now, save your query.
1
Right click on the tab that says
3
“Query 1” and name your query to
save it within your MS Access file.
Fall 2015
2|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
5. Complete the same task as above for the remaining 5 tables. (Hint: You will be completing the
same steps for task #’s 1-4. You will have to add an additional query
for each one.)
a. Semester
b. Class
c. Instructor
d. Class_Taught
e. Student_Enrollment
Your final list on the left should contain all 6 tables listed, as Student
is in task #4.
Viewing the Table Relationships in MS Access
Now that you have executed your SQL CREATE statements within MS Access, you should now have a
basic database structure created. To confirm that all of your relationships were created, you are going
to view the internal ERD that MS Access creates as you executed your SQL.
1. Click on the Database Tools tab at the top and then click on the
Relationships button on the ribbon within the
Relationships group.
2. You will see a screen with all of your
tables and their relationships on it. Below
you will find an example of the contents
2
that you should see. These boxes can be moved
around for better organization (which has been done in the screen provided).
Fall 2015
1
3|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
Inserting Data into your Tables
In Appendix C, you will find the SQL Insert statements for inserting data into your tables using SQL
INSERT statements. Execute these SQL statements within Access (as you did with the creation
statements in the prior section). You do not have to save these statements as you did with the CREATE
statements. Once the SQL INSERT statements have been executed, we will now execute basic select
statements to see the data within you tables. Reminder: MS Access can only execute one SQL
statement at a time. If you view the data in your tables (double click on any table name on the left), you
should have 2 rows inserted for each table upon completion of this step and 3 for the Class_Taught
table.
Selecting Data from Tables to Answer Queries
When working with SQL, you have a lot of functionality that allows you to answer analytical questions
about your data and display data for a report appropriately. In these examples, you are going to be
given single table queries, at least one join, and an aggregate function example. Some of the notation is
a little different than the slides due to MS Access SQL requirements. You will be provided the SQL to
answer these questions. The purpose is to understand what the SQL can do for you, not that you fully
be able to write complex SQL queries. You will save each of these queries in your MS Access document
that you have created. Think about the structure of your database and try to figure out where the data
will come from (which tables? Which attributes?). These SELECT statements can also be found in the
text file provided for copy/paste.
1. Question 1:
What course(s) is taught by instructor Bright? Think about being able to display/search
instructor name, course name, etc.
a. SQL Answer:
SELECT Class_Taught.section AS Section,
Instructor.InstructorLName AS Instructor,
Class.ClassName AS Course,
Semester.Term,
Semester.Year
FROM (((Class_Taught
INNER JOIN Instructor
ON Class_Taught.InstructorId = Instructor.InstructorID)
INNER JOIN Class
ON Class_Taught.ClassID = Class.ClassID)
INNER JOIN Semester
ON Semester.SemesterID = Class_Taught.SemesterID)
WHERE Instructor.InstructorLName = "Bright";
Fall 2015
4|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
2. Question 2:
What student(s) are enrolled in Freshman Business Connections? Who is the instructor?
What semester were they enrolled?
a. SQL Answer:
SELECT Student.StudentLName AS "Last Name",
Student.StudentFName As "First Name",
Semester.Term,
Semester.Year,
Instructor.InstructorLName As "Course Instructor"
FROM (((((Student_Enrollment
INNER JOIN Class_Taught
ON Class_Taught.ClassTaughtiD = Student_Enrollment.ClassTaughtID)
INNER JOIN Class
ON Class_Taught.ClassId = Class.ClassID)
INNER JOIN Instructor
ON Class_Taught.InstructorID = Instructor.InstructorID)
INNER JOIN Student
ON Student_Enrollment.StudentID = Student.StudentID)
INNER JOIN Semester
ON Class_Taught.SemesterID = Semester.SemesterID)
WHERE ClassName = "Freshman Business Connections";
3. Question 3:
List all students and information about them.
a. SQL Answer:
SELECT StudentID AS "ID",
StudentLName As "Last Name",
StudentFName As "First Name"
FROM Student;
Fall 2015
5|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
4. Question 4:
List all instructors and information about them.
a. SQL Answer:
SELECT InstructorID AS "ID",
InstructorLName As "Last Name",
InstructorFName As "First Name"
FROM Instructor;
5. Question 5:
List all instructors and the number of courses each has taught.
a. SQL Answer:
SELECT Instructor.InstructorLName As "Last Name",
Instructor.InstructorFName As "First Name",
COUNT(Class_Taught.ClassTaughtID) AS "# Courses Taught"
FROM (Instructor
INNER JOIN Class_Taught
ON Class_Taught.InstructorID = Instructor.InstructorID)
GROUP BY Instructor.InstructorLName,Instructor.InstructorFName;
b. If I wanted to narrow this down to display only a specific instructor, then I could add a
WHERE Instructor.InstructorLName = “Bright” to the end and it would only display
Brittany Bright’s information. If I wanted to only display instructors who have taught at
least 2 courses, then I could add a HAVING COUNT(ClassTaughtID) >= 2.
Fall 2015
6|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
Appendix A: Background Information
For creating/running SQL queries, we will begin with a little background information on the data we will
be utilizing.
For this tutorial, we will use the information from your Intro to SQL slides with the Student/Course
information. Below you will find the data dictionary and ERD for this example.
Entity Relationship Diagram (ERD)
This ERD contains 6 entities with their associated attributes for an example database creation. Below
you will find the data dictionary further describing each of these entities and attributes.
Fall 2015
7|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
Data Dictionary
Table
STUDENT
Field Name
StudentID
P/F
Key
PK
Description
Data
Type
INTEGER
Student First Name
Student Last Name
StudentFName
StudentLName
Student Identification
Number
VARCHAR
VARCHAR
Field
Size
Constraints
NOT NULL
20
20
NOT NULL
NOT NULL
PK,F
K
Student Identification
Number
INTEGER
NOT NULL
PK
Class for Enrollment
INTEGER
NOT NULL
SemesterID
PK
Semester Identification
Number
Semester Term
INTEGER
NOT NULL
VARCHAR
20
Year of Term
Identification Number
of Class Being Taught
VARCHAR
INTEGER
4
Section of Class
VARCHAR
4
Identification Number
of Instructor
Identification of Class
being Taught
Identification of
Semester
Identification Number
of Class
Department of Class
INTEGER
NOT NULL
INTEGER
NOT NULL
ClassDepartment
VARCHAR
4
NOT NULL
ClassNum
SEMESTER
StudentID
ClassTaughtID
STUDENT_ENROLLMENT
Class Number
VARCHAR
4
NOT NULL
Identification Number
for Instructor
INTEGER
Last Name of Instructor
First Name of Instructor
VARCHAR
VARCHAR
Term
CLASS_TAUGHT
Year
ClassTaughtID
PK
Section
InstructorID
FK
SemesterID
FK
ClassID
INSTRUCTOR
FK
ClassID
CLASS
Possible
Value
PK
InstructorID
InstructorLName
InstructorFName
PK
Fall, Spring,
Summer
NOT NULL
INTEGER
INTEGER
NOT NULL
WCOB, ISYS,
MRKT
1234, 3456,
4820
NOT NULL
20
20
This data dictionary outlines each of the entities in this example database, the associated attributes,
whether they are Primary Key (PK) or a Foreign Key (FK), a description, a data type and length (where
appropriate), any constraints on the attribute, and example values for some of the attributes.
Fall 2015
8|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
Appendix B: SQL Create Statements for ERD/Data Dictionary
In this appendix, you will find the CREATE statements for this database that were provided to you
previously in the lecture slides. It is important to realize that not all of the data types originally
discussed can be used in the MS Access environment. All database systems (DBMS’s) will have their
own allowable data types. Your CREATE statements will execute as given below; however, you will
notice it applies a data type of “Short Text” and “Number.” Remember: The order that your tables are
executed does matter – Foreign Keys must already exist to be able to create that relationship.
RUN 1st:
RUN 4th:
CREATE TABLE Student (
StudentID
INTEGER
NOT NULL,
StudentFName VARCHAR(20) NOT NULL,
StudentLName VARCHAR(20) NOT NULL,
CONSTRAINT Student_PK PRIMARY KEY (StudentID)
);
RUN 2nd:
CREATE TABLE Semester (
SemesterID
INTEGER
NOT NULL,
Term
VARCHAR(20),
Year
VARCHAR(4),
CONSTRAINT Semester_PK PRIMARY KEY
(SemesterID)
);
RUN 3rd:
CREATE TABLE Class (
ClassID
INTEGER
NOT NULL,
ClassDepartment
VARCHAR(4) NOT NULL,
ClassNum
VARCHAR(4) NOT NULL,
ClassName
VARCHAR(40),
CONSTRAINT Class_PK PRIMARY KEY (ClassID)
);
Fall 2015
CREATE TABLE Instructor (
InstructorID
INTEGER
NOT NULL,
InstructorLName
VARCHAR(20),
InstructorFName
VARCHAR(20),
CONSTRAINT Instructor_PK PRIMARY KEY
(InstructorID)
);
RUN 5th:
CREATE TABLE Class_Taught (
ClassTaughtID INTEGER
NOT NULL,
Section
VARCHAR(4),
InstructorID
INTEGER
NOT NULL,
ClassID
INTEGER
NOT NULL,
SemesterID
INTEGER
NOT NULL,
CONSTRAINT ClassT_PK PRIMARY KEY (ClassTaughtID),
CONSTRAINT ClassT_FK1 FOREIGN KEY (InstructorID)
REFERENCES Instructor (InstructorID),
CONSTRAINT ClassT_FK2 FOREIGN KEY (ClassID)
REFERENCES Class (ClassID),
CONSTRAINT ClassT_FK3 FOREIGN KEY (SemesterID)
REFERENCES Semester(SemesterID)
);
RUN 6th:
CREATE TABLE Student_Enrollment (
StudentID
INTEGER
NOT NULL,
ClassTaughtID INTEGER
NOT NULL,
SemesterID
INTEGER
NOT NULL,
CONSTRAINT StudentE_PK PRIMARY KEY (StudentID,
SemesterID, ClassTaughtID),
CONSTRAINT StudentE_FK1 FOREIGN KEY (StudentID)
REFERENCES Student (StudentID),
CONSTRAINT StudentE_FK2 FOREIGN KEY
(ClassTaughtID) REFERENCES Class_Taught
(ClassTaughtID)
);
9|P a g e
ISYS 2263 – Principles of Information Systems
Applying SQL in MS Access 2013
Appendix C: Insert Statements for Data
Note: In MS Access, you’re unable to execute multiple queries at the same time, while most DMBS’s
would allow you to execute the full set of statements below (as long as they were ended with the
semicolon). For MS Access, you will have to execute one at a time.
Note: You will notice that the text has double quotes around it, rather than the single quote
demonstrated in the lectures. MS Access does require that there be double quotes. If you are prompted
for a parameter when trying to copy/paste, you will most likely have to type the syntax. MS Word adds
formatting that causes problems with copy/paste. I have provided a text file with these statements as
well, this might work better for copy/paste.
Student Table Inserts
INSERT INTO Student (StudentID, StudentFName, StudentLName) VALUES (1,"Deidra","Smith");
INSERT INTO Student (StudentID, StudentFName, StudentLName) VALUES (2,"John”,”Doe");
Semester Table Inserts
INSERT INTO Semester (SemesterID, Term, Year) VALUES (1,"Fall","2014");
INSERT INTO Semester (SemesterID, Term, Year) VALUES (2,"Spring","2015");
Class Table Inserts
INSERT INTO Class (ClassID, ClassDepartment, ClassNum, ClassName) VALUES
(1,"WCOB","1111","Freshman Business Connections");
INSERT INTO Class (ClassID, ClassDepartment, ClassNum, ClassName) VALUES
(2,"ISYS","2263","Principles of IS");
Instructor Table Inserts
INSERT INTO Instructor (InstructorID, InstructorLName, InstructorFName) VALUES
(1,"Bright","Brittany");
INSERT INTO Instructor (InstructorID, InstructorLName, InstructorFName) VALUES (2,"Bristow",
"Susan");
Class_Taught Table Inserts
INSERT INTO Class_Taught (ClassTaughtID, Section, InstructorID, ClassID, SemesterID) VALUES
(1,"001",2,1,1);
INSERT INTO Class_Taught (ClassTaughtID, Section, InstructorID, ClassID, SemesterID) VALUES
(2,"901",1,2,2);
INSERT INTO Class_Taught (ClassTaughtID, Section, InstructorID, ClassID, SemesterID) VALUES
(3, "002", 1, 1, 1);
Student_Enrollment Table Inserts
INSERT INTO Student_Enrollment (StudentID, ClassTaughtID,SemesterID) VALUES (1,1,1);
INSERT INTO Student_Enrollment (StudentID, ClassTaughtID,SemesterID) VALUES (2,2,2);
Fall 2015
10 | P a g e
