Assignment 3: SQL
Bring to use in lab on Thursday, May 30th or
Friday, May 31st
A paper copy is due in your lab May 30th or May
31st�
Introduction:
Structured Query Language, or SQL, is the industry standard language for communicating with relational databases.� SQL follows a certain pattern and if you know that pattern, you can easily �speak� beginning SQL.� The main use of the language in this class will be to view data from various tables and to add or update data.�
There are a limited number of
clauses, or commands, used by SQL to access and manipulate data stored in a
database.� The three main clauses are:
SELECT�������� Identifies which columns of data in a table are to be displayed
FROM���������� Identifies which tables hold the data
that is needed and any key
������������������ constraints
WHERE�������� Non-key Criteria that will limit the number of rows to be
returned
The outcome of this series of
statements will be a table, even if there are no rows of data in it.�
The way the tables are associated can be noted in the FROM clause using
the INNER JOIN command.� Inner
Join is what we mean by the Join term used in the book.� It usually indicates a Natural Join.� There are other types of joins which is why
the term Inner is used to differentiate.
The WHERE clause is used
to indicate other non-key constraints and further limits the rows to be
returned.�
For example, if an advisor has many students assigned to them, but each student
can only have only one advisor, then the association of these two things is one
advisor to potentially many students.� If
Student and Advisor data is to be stored properly in a relational database and
the association of a particular advisor with a particular student is also
stored, then the table structures will be:
��������� �����
AdvisorID
is the primary key in tblAdvisor and StudentID is the primary key in tblStudent.�
To show that an Advisor is associated with many students, another field is
added to tblStudent.�
This field will hold the key values for the primary key in tblAdvisor and will be considered a foreign key.�
RULE: To store a one-to-many
relationship between tables, the primary key of the one migrates to become a
foreign key in the many.� In other words,
add another attribute in the table on the many side of the relationship.�
Make sure that attribute will allow the same type of data (number, text, etc.)
as the primary key attribute of the table on the one side of the
relationship.� Store the primary key
value of the related row in the foreign key attribute.
Then, to display all Student last
names and their Advisor last Names, join the tables on the common key attribute
and display all rows where the value of AdvisorID in tblAdvisor matches the value of AdvisorID
in tblStudent.�
��������� SELECT�������� tblStudent.LName, tblAdvisor.LName
��������� FROM ��������� tblStudent INNER JOIN tblAdvisor ON
���������������������������� tblStudent.AdvisorID =� tblAdvisor.AdvisorID;
To further limit the rows returned to only those students with the advisor last name of �Smith�, use the Where clause:
��������� SELECT�������� tblStudent.LName, tblAdvisor.LName
��������� FROM ��������� tblStudent INNER JOIN tblAdvisor ON
���������������������������� tblStudent.AdvisorID =� tblAdvisor.AdvisorID;
��������� WHERE�������� tblAdvisor.LName = �Smith�;
The important points to remember
when creating a query are:
Writing Out SQL
Using what you have learned about Structured Query Language, write out the
proper SQL syntax in the boxes below to return the requested data. You will
need to reference commands that were discussed in class.� Use these tables and their attributes as your
data sources.
������������������
Example:� Show all advisor information from the Advisor
table.
��������� SELECT���������� AdvisorID, LName, FName, Department
����������� FROM� ����������� tblAdvisor;
OR, use the * as a wild card to show all columns:
��������� SELECT
��������� *
����������� FROM
������������ tblAdvisor;
A) Show all students from the Student table.
B) Show all students in alphabetical order by last name.
C) Show only the Advisor Names from the Informatics Department
D) Show all Advisors and the Students they advise.� (Requires a Join)
E) Show the last name and first name of CSE majors.� (Requires a Join)
F) Show all advisors working with Art Majors.� (Requires a Join)
This one is bonus:� You don�t need to write it out. However, it isn�t extra credit!
G) Show each student and their advisor, the advisor�s department and their major. (Requires a Join)
H) Choosing one query from
B, C, D, E, F or G do the following:�
Explain in English what each of the SQL commands is doing to retrieve the data
you requested.� Use the SQL terminology
in your answer.
Example for query A:�
Select the columns StudentID, LName, FName from the Student table and display them in a virtual table.� Bring back all rows, there are no restrictions.