SQL statements Access Database Tables Create a database in Microsoft Access cont
ID: 671641 • Letter: S
Question
SQL statements
Access Database Tables
Create a database in Microsoft Access containing two tables. Use SQL statements to manipulate the data.
Specification
The first table (Contact) will contain information for at least 10 contacts (real or made-up.). Include the following fields:
Name
Address
Home/cell/business phone numbers
Email address
any other fields you might need
The second table (Schedule) will contain your class schedule. Include the following fields:
Course ID
Course name
Start and end times
Room number
Professor
any other fields you might need
Create and save queries using SQL in Microsoft Access that will do the following:
Query 1 - Select all fields in the Contact table, ordered by last name.
Query 2 - Select first name, last name, and e-mail address in the Contact table, ordered by e-mail address.
Query 3 - Select first name, last name, and city in the Contacts table for any contacts who don't live in Utah, ordered by Zip Code.
Query 4 - Select course ID and time fields in the Schedule table, ordered by course ID.
Query 5 - Select all the classes in the Schedule table by your favorite professor (be very careful on this one), ordered by beginning time.
HINT: If you want credit for the queries, make sure you write the SQL statements out yourself rather than using the built-in Query Wizard.
Explanation / Answer
Please find required solutions:
Query 1 - Select all fields in the Contact table, ordered by last name.
select *from Contact order by last_name
Query 2 - Select first name, last name, and e-mail address in the Contact table, ordered by e-mail address.
select first_name,last_name,email_address order by email
Query 3 - Select first name, last name, and city in the Contacts table for any contacts who don't
live in Utah, ordered by Zip Code.
select first_name,last_name,city from Contact where city!='Utah' order by zip_code
Query 4 - Select course ID and time fields in the Schedule table, ordered by course ID.
select course_id,time from schedule order by course_id
Query 5 - Select all the classes in the Schedule table by your favorite professor (be very careful on this one), ordered by beginning time.
select *from Schedule where professor='professor_name' order by start_time
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.