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 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
Use MS Access software to build tables, as well as write, and execute queries. Submit the...
Design an Access database including tables and queries for the model below Relational Model (Project 9) Student SSN Name Address GPA Type SchoolName EnrollDate Current Student SSN ExpectedGraduate Alumni SSN GradDate EmployerName EmployerAddress Position Salary School Name Location Size OfLibrary BudgetForMBA AACSBStatus AACSBDate Curriculum Curriculum Code SchoolName SchoolName Course CourseCode Name Type Curriculum Code Course Topics CourseCode Topic Complete SSN CourseCode
Prepare and execute each of the queries listed using the "Adventureworks2012" database in SQL: Server: http://msftdbprodsamples.codeplex.com/releases/view/93587 When all of your queries are complete, cut and paste the SQL Syntax into a word document. In order to see what the column names are, you need to click on the table and then Columns to see the field names. Make sure to include column headings that make sense in the queries (use the as “Field Name” after the field selected). Multi-table Queries...
Create queries in Access to answer the following questions: Consider event Create a query that reports the following: Last Name, First Name, date of birth, team abbreviation, LSC and actual time for each swimmer. Sort the report from fastest to slowest time.
Database Queries Use the file at the bottom of this assignment to complete the following: Create a query that shows all customers with account balances less than $18,000. Sort ascending by the date the account was open. Create a query that shows all customers from Durham with Savings accounts. Sort ascending by the last name of the customer. Create a query that shows all customers from Raleigh who opened their accounts before 1998. Sort descending by account type. Create a...
The assignment requires reading and understanding the data model used by Cassandra and representing a given ER model with Cassandra “tables”. The decision of which tables to define is driven by the queries that need to be answered. A software company is interested in developing a Video Sharing application. They have designed a conceptual model of the data they need to handle in order to offer users these main functions: 1. The creation of channels where they can upload videos....
Please help me to solve Please, No handwriting COURSE; introduction to database Q- write a query SQL by Using the info below A. Normalize the Tables (in 3NF at least) B. Create the Normalized Tables and Populate them with at least 5 Rows C. Write the Wholesale Management System requested Queries & Execute them VERY IMPORTANT Screenshots from MySQL (or any other software you use) of all the tables after queries result. - Database system for a Wholesale Management System...
Lab Assignment – Database Implementation and Security In this lab you will create a Microsoft Access database of employee information and secure the table username and password security. Steps Enter data for five employee records. Each record should have fields: Employee ID (5 digits), First Name, Last Name, Home Address, Hire Date Create a query that displays Employee ID, First Name and Last Name. Create a form that requires entering username and password to access employee table. Error message should...
This is third time im posting this Question COURSE; introduction to database Please, No handwriting thanks for your efforts Design the database by following . Execute the sample requested queries. . ER Diagram. . All schemas before and after normalization. . All SQL statements of: -Creating tables. - Inserting data in tables. -Queries. 5. Screenshots from MySQL (or any other software you use) Question is - Database system for a Wholesale Management System Consider a database system for a Wholesale...
This is third time im posting this Question COURSE; introduction to database Please, No handwriting thanks for your efforts Design the database by following . Execute the sample requested queries. . ER Diagram. . All schemas before and after normalization. . All SQL statements of: -Creating tables. - Inserting data in tables. -Queries. 5. Screenshots from MySQL (or any other software you use) Question is - Database system for a Wholesale Management System Consider a database system for a Wholesale...
Lab Assignment – Database Implementation and Security In this lab you will create a Microsoft Access database of employee information and secure the table username and password security. Steps Enter data for five employee records. Each record should have fields: Employee ID (5 digits), First Name, Last Name, Home Address, Hire Date Create a query that displays Employee ID, First Name and Last Name. Create a form that requires entering username and password to access employee table. Error message should...