Populate your quot;studentquot; table with at 20 or more students, such that: At least 6 of them (but not all!) have the last name...

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.

6.    Submit your homework to Moodle. The Word document must have your lastname as part of the filename.

EXTRA CREDIT:                                                                                

For each problem, show the SQL query and a screenshot of the results:

1.    How many students are from RI?

2.    Retrieve just a list of Student IDs along with their credit information for all students that do NOT have the lastname "Jonsey" and are honors students. Sort by gpa.

REMARKS:

Feel free to use either the command-line interface or the Web-based interface for this lab assignment.

·      As an alternative to using the command-line interface, feel free to use MySQL's Web-based interface for this exercise ( insert two or three more students via the command-line and try the insert command for Bob Campbell above). This interface can be accessed via the following URL:

http://web.njit.edu/mysql/phpMyAdmin

After logging in, select your table student, shown on the left-hand side, and then choose the "insert" tab. From there, it should be straightforward.

·      The command-line interface allows for the direct execution of SQL queries stored in a file on your AFS account.  For example, assume you have written and saved a query in a file called query.txt.  To run the query, type the following command (when logged in via the command-line interface):

source  query.txt;

Note that query.txt can contain multiple queries, one per line. They will be run in order.