Assignment
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 GPAof1.0
• At least 6 of them are from state DE
• More than 7are 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 least3have 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.
|
GPA
|
Recognition Letters
|
|
3.85 or above
|
A+ or High Honors
|
|
3.7 - 3.84
|
Honors
|
|
2.7 - 3.69
|
B
|
|
1.7 - 2.69
|
C
|
|
1.0 - 1.69
|
D
|
|
Below 1.0
|
F
|
|
Credits
|
School level
|
|
More than 96
|
Senior
|
|
65-96
|
Junior
|
|
33-64
|
Sophomore
|
|
Less than 33
|
Freshman
|
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 RIfreshman (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 lastnames and credits of all students that are NOT from NJ, RI or DE. Order by the state.