Question

Use MS Access software to build tables, as well as write, and execute queries. Submit the database and screenshots of all tables and queries. Step by step snapshots included. Don't want queries. Just process of creating this data step by step in Access.

Create your own data. Ensure the data will test all criteria and prove all sorts worked as required. Be sure that all tables and queries have descriptive names. “Query 1” and “Step 1” are examples of poor names.

Step 1

Build a Database named DBMS Course Project. The database should include the following tables:

First Table – Students

ID

First Name

Last Name

Address

City

State

Zip Code

Phone Number with area code

Second Table – GPA

ID

Current GPA (two decimal positions)

Class (numeric field, 1 = Freshman, 2 = Sophomore, etc.)

Freshman

Sophomore

Junior

Senior

Graduate Student

Third Table – Scholarships

ID

Major course of study

Academic Scholarship (Use Yes/No data type)

Athletic Scholarship (Use Yes/No data type)

Step 2

Extract the First and Last Name, Address, City, State, Zip Code and Phone Number of each Senior on the database. Sort by Last Name, then First Name (1 sort).

Step 3

Extract the First and Last Name, and GPA of each student who qualifies for the Dean’s List. Sort by GPA, then Last Name, then First Name (1 sort). A GPA of 3.25 is required to make the Dean’s List.

Final Project (Part 2)

Final Project (Part 2)

Use OpenOffice software (or MS Access if you have it) to write and execute queries with tables you created in Project Part 1.

Build a query of all students on athletic scholarship. Additionally, produce a report with descriptive report and column headings. Be sure there is enough data to prove the selection and sort worked as required. Submit the database in unzipped format as well as submitting screenshots of the query and the report.

Step 1

Extract the First and Last Name, Student ID, Course Major and GPA of all students on athletic scholarship. Sort by Course Major, Last Name, then First Name in a single sort.

Step 2

Produce a report with the data from Step 1 and use good headings.

Use the Add submission button to submit your assignment.

Answer 1

Answer of Part 1 - Step 1,2,3)

BEGIN TRANSACTION;

CREATE TABLE Students(ID integer PRIMARY KEY,First_Name varchar(20),
Last_Name varchar(20), Address varchar(50), City varchar(20),
State varchar(20), Zip_Code integer(20), Phone_number integer(15));

INSERT INTO Students VALUES(1,'Tom','Lee','Address2', 'City 2', 'State 2',
700098, 0334567894765);
INSERT INTO Students VALUES(2,'Hank','Fae','Address3', 'City 3', 'State 3',
700099, 0334567838765);
INSERT INTO Students VALUES(3,'Jean','Red','Address4', 'City 4', 'State 4',
700091, 0334562898765);
INSERT INTO Students VALUES(4,'Scott','Wer','Address5', 'City 5', 'State 5',
700092, 0337567898765);
INSERT INTO Students VALUES(5,'Leo','MBH','Address1', 'City 1', 'State 1',
700095, 0334567898705);
COMMIT;

SELECT * FROM Students;

CREATE TABLE GPA(ID integer PRIMARY KEY,Current_GPA decimal(8,2),
Class integer(1));

INSERT INTO GPA VALUES(1,55.45,1);
INSERT INTO GPA VALUES(2,56.45,2);
INSERT INTO GPA VALUES(3,57.45,3);
INSERT INTO GPA VALUES(4,58.45,4);

SELECT * FROM GPA;

CREATE TABLE Scholarships(ID integer PRIMARY KEY,Major varchar(10),
Academic_Scholarship varchar(3), Athletic_Scholarship varchar(3));

INSERT INTO Scholarships VALUES(1,'Science','Yes','No');
INSERT INTO Scholarships VALUES(2,'Music','Yes','No');
INSERT INTO Scholarships VALUES(3,'Arts','Yes','No');
INSERT INTO Scholarships VALUES(4,'Dance','Yes','No');

select * from Scholarships;

select First_Name, Last_Name, Address, City, State, Zip_Code, Phone_Number
from Students where ID in
(select ID from GPA where Class = 4);

Outputs:

1|Tom|Lee|Address2|City 2|State 2|700098|334567894765
2|Hank|Fae|Address3|City 3|State 3|700099|334567838765
3|Jean|Red|Address4|City 4|State 4|700091|334562898765
4|Scott|Wer|Address5|City 5|State 5|700092|337567898765
5|Leo|MBH|Address1|City 1|State 1|700095|334567898705
1|55.45|1
2|56.45|2
3|57.45|3
4|58.45|4
1|Science|Yes|No
2|Music|Yes|No
3|Arts|Yes|No
4|Dance|Yes|No
Scott|Wer|Address5|City 5|State 5|700092|337567898765

Step 3)

select Students.First_Name, Students.Last_Name, GPA.Current_GPA from
Students inner join GPA on
Students.ID = GPA.ID;

Output:

Tom|Lee|55.45
Hank|Fae|56.45
Jean|Red|57.45
Scott|Wer|58.45