MYSQL

MIS245-Homework 3 Summer, 2017 Page 2 of 2

1. Populate your "student" table with at 20 or more students, such that:

· At least 6 of them (but not all!) have the last name "Jonesy"

· At least 1 of them has a GPA of 3.85

· At least 2 of them have a GPA of 3.7 and 3.84, respectively

· At least 2 of them have a GPA of 2.7 and 3.69, respectively

· At least 2 of them have a GPA of 1.7 and 2.69, respectively

· At least 2 of them have a GPA of 1.0 and 1.69, respectively

· At least 1 has a GPA of 1.0

· At least 6 of them are from state DE

· More than 7 are from NJ

· More than 4 are from RI (Rhode Island)

· At least 1 is from some other state than listed here

· More than 2 have more than 96 credits

· At least 3 of them have credits of 32, 33, 64, 65, and 96, respectively

· At least 1 of them has less than32 credits

· At least 3 have between 65-96 credits

· At least 3 have between 33-64 credits

To accomplish this, you can use the "insert" command (multiple times) via the command-line interface or web interface. Example of how to insert Sally Smith record into your table:

insert into student values ('1', 'Sally', 'Smith', '10 Main St.', 'NJ', '3.92', '98');

Note that the value of the field "id," while arbitrary must be unique for all students. (That's why it is designated as the "primary key.") I recommend that you simply assign the id's sequentially, that is, the first student is 1, the second is 2, etc.

For the rest of this assignment the letter grade to GPA and the grade level to credits relationships are as follows. Note these may not apply to an actual school

GPA	Recognition Letters		Credits	School level
3.85 or above	A+ or High Honors		More than 96	Senior
3.7 - 3.84	Honors		65-96	Junior
2.7 - 3.69	B		33-64	Sophomore
1.7 - 2.69	C		Less than 33	Freshman
1.0 – 1.69	D
Below 1.0	F

2. Try creating another student with the id 1 (the same as Sally's).

a. Screenshot the results of the following query and insert it into your homework WORD document:

insert into student values ('1', 'Bob', 'Smith', '14 King Blvd.', 'NY', '3.17', '124');

b. How does MySQL respond? Explain what the message displayed means.

3. Use the following SQL query to retrieve all records. Take a screenshot and insert it into your homework WORD document:

select * from student;

4. Screenshot the results of the following query and insert it into your homework WORD document:

select gpa, lastname, state from student where state='RI';

The query shows 3 columns for the gpa, lastname and state fields and it only displays students from RI.

5. Next, write and run (issue) SQL queries that do the following. For each query, provide screenshots for the SQL query and the results within a Word document so I can grade it.

a. Show all students with the last name Jonsey.

b. Retrieve all student names (first and last) with a B GPA (see table for definition in first question)and are NOT from NJ.

c. Retrieve all students that are NOT in honors (see table for definition in first question)

d. Retrieve lastname, gpa, and credits of all DE and RI freshman (see table for definition in first question). Order the results by last name.

e. Display the query and the results to determine if there are any students from NJ with High Honors.

f. Retrieve the last names and credits of all students that are NOT from NJ, RI or DE. Order by the state.