The assignment is framed to use MySQL to test your queries. However, if you do n
ID: 3919648 • Letter: T
Question
The assignment is framed to use MySQL to test your queries. However, if you do not have MySQL, you can write the query without testing it. The purpose of the assignment is to provide you with early feedback on writing single table queries. This will assist the following week when you will have to write single table queries through Java to access a database in MySQL on the Internet.
Examine the Metadata/Data Dictionary in the attached PDF file. We will review in class what the term metadata means. If you want to get ahead, read the documents named “Using Database Management Systems” in the Database Module. Write queries to respond to the five information requests in the attached PDF file. We will review and practice in class, how to write and test single table queries in a Relational Database Management System (RDBMS). Use the metadata in the file to assist you understanding the defined structure of the student table. Use the metadata to correctly write the queries for the 5 requests for information in the attached PDF document. Write your queries in a MS Word document and upload the completed document into the drop box in this assignment link. Your answers can be submitted as a Test File or type directly into the submission text area
Remember that the Single Table Query Assignment is a written assignment. You are not required to actually execute the queries on an actual Relational Database Management System (RDBMS). However, a RDBMS will be provided. For this assignment you should participate in the in class activities to get the answers to the five information requests. You will have the opportunity to actually run and test the queries that you write in a relational database management system. In class, I will review how to start MySQL, a relational database management system (RDBMS). Additionally, I will review how to setup a test environment in MySQL that you can attempt to run your single table queries. With the test environment setup, MySQL will provide a result set if the query worked or feedback notifying you that there is a problem with the syntax of your query. The context of the assignment is for you to write in a text editor what you think the correct syntax is for SELECT queries to perform the specified information request. To assist you in writing the queries, I have provided two supplement documents. One of the documents is named “Using Database Management Systems”. It is a PDF file located in the Database Module in Canvas. In provides an overview of database management systems. The second document is a PowerPoint presentation that specifically focuses on writing single table queries. Additionally, I will review this material in class. The combination of these two documents should provide enough information on writing single table queries. I am also providing real time relational databases and tables with data to run and test your queries. Finally, I added links to a Website that provides an excellent tutorial on writing single table queries. The Website is named W3School.The Website also has interactive practice for you to actually write queries in a simulated database environment.
In Conclusion, examine the Metadata/Data Dictionary in the attached PDF file. Write queries to respond to the five information requests in the attached PDF file. Use the metadata in the file to assist you understanding the defined structure of the student table. Use the metadata to correctly write the queries for the 5 requests for information in the attached PDF document. Write your queries in a Text Editor and upload the completed document into the drop box in this assignment link. Your answers can be submitted as an attached MS Word Document, a PDF file, or type directly into the text area of the assignment submission drop box.
CSCI 112 Introduction to Database Management Assignment
Create SQL queries to extract the following from the Student table, whose metadata is shown
Table: Student
Column Name Data Type Size Description
snum Text 6 Student Number (Primary Key)
firstname Text 15 Student First Name
lastname Text 25 Student Last Name
street Text 25 Address of the Student
city Text 15 City of the Student
st Text 2 State of the Student
Zip Text 6 Zip Code of the Student
Phone Text 10 Contact Phone Number
Major Text 15 Students Current Major
Gpa Numeric Student Grade Point Average
gender Text 1 Gender of Student ( F or M)
scholarship Boolean true = Yes ; false = No
Student ID, Student Name, Phone Number for students that live in Newark DE
Student ID, Student Name, Phone Number for students with grade point averages greater than 3.0
Student ID, Student Name, Phone Number for students that live in NY and PA
Student Name, Address, City, State and Zip Code for students that are Female and major is CIS
Student ID, Student Name, Phone Number for students in zip code 19130 that are on Scholarship
DROP DATABASE IF EXISTS StudentTableTest;
CREATE DATABASE StudentTableTest;
USE StudentTableTest;
CREATE TABLE STUDENT (
snum CHAR (6),
firstname VARCHAR (15),
lastname VARCHAR (25),
street VARCHAR (25),
city VARCHAR (15),
st CHAR (2),
zip CHAR (5),
phone CHAR (10),
major VARCHAR (20),
gpa DOUBLE,
gender ENUM ('F','M'),
scholarship BOOLEAN,
CONSTRAINT STUDENT_PK PRIMARY KEY (snum)
);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('100000', 'Joe', 'Cooper', '01 Main', 'Phila', 'PA',
'19122', '2154445555', 'Comp Science', '3.6', 'M', False);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('J11233', 'Mauris', 'Cholaro', '63 Woodland', 'Phila', 'PA',
'19142', '2671295460', 'Accounting', '2.9', 'M', TRUE);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('J85147', 'Michael', 'Bentt', '22 Le Bronx', 'New York', 'NY',
'19801', '6105290071', 'Business Admin', '4.0', 'M', TRUE);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('A78126', 'Jasmine', 'Davenport', '50 Olney', 'Phila', 'PA',
'19101', '2670297300', 'Art', '3.0', 'F', False);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('Z61403', 'Kwame', 'Traylor', '37 Have Hill', 'Conshohoken', 'PA',
'19341', '2157308912', 'Biology', '3.3', 'F', TRUE);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('Z45503', 'Stephanie', 'Cowdery', '45 Bule Lane', 'Newark', 'DE',
'18677', '3025678944', 'Law', '3.3', 'F', TRUE);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('Z64551', 'Peter', 'Congo', '501 Washington Lane', 'Newark', 'DE',
'18677', '3025677844', 'Construction', '3.7', 'M', False);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('Z64589', 'Celestein', 'Dawson', '456 Flatbush Ave', 'New York', 'NY',
'56789', '2125677711', 'Computer Science', '3.4', 'F', False);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('Z64584', 'Bruce', 'Wiley', '4334 N. 16 Steet', 'Phila', 'PA',
'19020', '2156572134', 'Computer Science', '2.4', 'M', False);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('Z64595', 'Keith', 'Johnson', '5712 Commerce Street', 'Camden', 'NJ',
'34567', '6092344567', 'Liberal Arts', '1.4', 'M', False);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('Z64585', 'Jennifer', 'Thompson', '202 Park Avenue', 'New Castle', 'DE',
'87933', '6092344567', 'CIS', '3.5', 'F', True);
INSERT INTO Student
(snum, firstname, lastname, street, city, st, zip,
phone, major, gpa, gender, scholarship)
VALUES ('Z64590', 'James', 'Smith', '1721 JFK Bulivard', 'Phila', 'PA',
'19120', '2157553478', 'CIS', '3.0', 'M', False);
Explanation / Answer
Q1) Student ID, Student Name, Phone Number for students that live in Newark DE
SELECT snum, firstname || lastname AS 'Student_Name', phone from Student where city = 'Newark' AND st = 'DE';
Q2) Student ID, Student Name, Phone Number for students with grade point averages greater than 3.0
SELECT snum, firstname || lastname AS 'Student_Name', phone FROM STUDENT WHERE gpa > 3.0;
Q3) Student ID, Student Name, Phone Number for students that live in NY and PA
SELECT snum, firstname || lastname AS 'Student_Name', phone FROM STUDENT WHERE st IN ('NY', 'PA');
Q4) Student Name, Address, City, State and Zip Code for students that are Female and major is CIS
SELECT firstname || lastname AS 'Student_Name', street, city, st, zip from STUDENT WHERE gender = 'F' AND major = 'CIS';
Q5) Student ID, Student Name, Phone Number for students in zip code 19130 that are on Scholarship
SELECT snum, firstname || lastname AS 'Student_Name', phone FROM STUDENT WHERE zip = '19130' AND scholarship = FALSE;
Please let me know in case of any clarifications required. Thanks!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.