Student SQL Queries

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

• At least 4 of them (but not all!) have the last name "Jones" At least
• 3 students must have the first and last name 'Jessica Jones'
• At least 2 rows has a student with the GPA of 3.7 and 3.84, respectively
• At least 2 rows have a GPA of 2.7 and 3.69, respectively
• At least 2 rows have a GPA of 1.7 and 2.69, respectively
• At least 2 rows have a GPA of 1.0 and 1.69, respectively
• At least 1 have GPAs less than 1.0
• At least 1 have GPAs greater than 3.85
• At least 3 students must live at '485 W 46th Street, New York City, NY'
• At least 6 students have to live on a street other than '485 W 46th Street'
• At least 7 of them are from state NJ
• More than 7 are from NY
• More than 2 are from DE (Delaware)
• At least 6 students must come from states other than NJ, NY, or DE
• At least 3 have more than 96 credits
• There must be at least one student with credits of 32, 33, 64, 65, and 96, respectively (i.e. 5 different entries)
• At least 5 of them has less than32 credits
• At least 5 have between 65-96 credits
• At least 2 have between 33-64 credits

To accomplish this, you can use the "insert" command (multiple times) via the command-line interface. Recall how you inserted Sally Smith into your table in the lab:

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

2. Try creating another student with the id 1.

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

into student values ('1', 'Jane', 'Doe', '14 King Blvd.', 'NJ', '3.17', '124');

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

3. After populating the table, 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='FL';

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

If you did not enter a row with Florida as the state there won't be any rows so the value will be:

Empty set (0.01 sec)

Note, the time value will vary.

5. Next, write and run SQL queries that answer the following queries. For each query, provide a screenshot of the SQL query and the results within your homework document so I can grade it.

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

b. Retrieve all honors students who are NOT from NJ. (see tables for definitions in first question)

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

d. Retrieve all freshman (credits are less than 32) from NOT Texas (TX)

e. Retrieve last names, state and credits of all sophomores that are from NJ that are in honors,

f. Retrieve the firstnames and credits of all students with the lastname "Jones" that are from NY or NJ but don't live on '485 W 46th Street'.

6. Use the information I gave you to populate your table to explain what is boundary testing.

EXTRA CREDIT

1. Retrieve a sorted Student ID list of for all students that do not have the lastname "Jones" and are above a C- GPA.

2. How many students are from NY?

3. Sort your list by state and then by high school grade level (freshman, sophomore, junior, senior). Display everything.