Question

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.

PART 2

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.

Answer 1

PART 1

The SQL code to create the given tables and query to extract data is given below.

Step 1: Three tables are created namely Students, GPA and Scholarships. Each table has primary key and associated foreign key. All necessary conditions are meeting.

CREATE TABLE Students
(
   ID       INT,
   FirstName   VARCHAR(50),
   LastName   VARCHAR(50),
   Address       VARCHAR(255),
   City       VARCHAR(50),
   State       VARCHAR(25),
   ZipCode       VARCHAR(5),
   Phone       VARCHAR(15),
   PRIMARY KEY (ID)
);

CREATE TABLE GPA
(
   ID       INT,
GPA       DECIMAL(3,2),
   Class       INT,
   PRIMARY KEY ID,
   FOREIGN KEY (ID) REFERENCES Students(ID)
);

CREATE TABLE Scholarship
(
   ID       INT,
   MajorCourse   VARCHAR(50),
   AcademicScholarship ENUM('Yes','No'),
   AthleticScholarship ENUM('Yes', 'No'),
   PRIMARY KEY (ID),
   FOREIGN KEY (ID) REFERENCES Students(ID)
);

Step 2: Table Students and GPA are joined to extract the records. Order by clause is used to sort the result as specified.

SELECT FirstName, LastName, Address, City, State, ZipCode, Phone
FROM Students INNER JOIN GPA
ON Students.ID = GPA.ID
WHERE GPA.Class = 4
ORDER BY LastName, FirstName;

Step 3: Table Students and GPA are joined on common attribute ID.

SELECT s.FirstName, s.LastName, g.GPA
FROM Students AS s INNER JOIN GPA AS g
ON s.ID = g.ID
WHERE g.GPA >= 3.25