Objectives: To learn to use MySQL queries to create databases and tables, and to add, update, delete, and retrieve records.
Please see the attachment for the detail.
Microsoft Word - JL_BCS350_201_Module5_programming assignment (hw4).docx BCS 350: Web Database Development CRN 24518 JL Spring 2020 ------------------------------------------------------------------------------------------------------------------------------ Programming Assignment 4 ------------------------------------------------------------------------------------------------------------------------------ Objectives: To learn to use MySQL queries to create databases and tables, and to add, update, delete, and retrieve records. Tasks: A. Write SQL statements to create a database called bcs350hw4 and create a table called students to store the student records including id, name, email, phone, start_year, and gpa. The file hw4data.txt has some sample data. The start_year data range from 2016 to 2019. You should make your own data to fill out the table that initially stores 20 students’ records. (Log in as root to create the bcs350hw4 database and grant all privileges on this database to jim. Then log in as jim to use the bcs350hw4 database.) B. Write SQL queries for the following questions. 1. Find the names and emails of all students and display them in the ascending order of name. 2. Find the id and name of all students whose start_year is the same as Alice Brown (Assume you only know the student name “Alice Brown” and don’t know her start_year). 3. Find the id, name, and gpa of all students whose gpa is between 2.5 and 3.5. 4. Find the id, name, and gpa of all students in the descending order of gpa. 5. Find the average gpa of all students as AverageGPA. 6. Find the average gpa of students for each start_year (write one query, not multiple queries). 7. Find the number of students for each start_year (write one query, not multiple queries). 8. Find the id, name, gpa, phone, start_year of the student who has the highest gpa. 9. Update John Jones’s phone number to 101-202-3030. 10. Delete Goof Goofy’s record from students table. 11. Add a record that contains your name (make it a little special). 12. Write a query of your own that is different from the above queries. Your query should output your name with information from any three other fields but your name cannot appear in the command, i.e., don’t simply use the where name = “your name” clause. Submission Submit One word or pdf document that includes the following: Part A) Screenshots of the SQL commands that you used to create database, grant user, create table, and populate the table, plus all the data in the final students table. Part B) Screenshots of the SQL commands for task B.1 through B.12 and the corresponding output. Note: Please insert image files into the word document with short descriptions. Do not just ZIP all image files. IDNameEmailPhone Start_YearGPA 1Alice
[email protected] 934-123-45672016 3.40 2Bob
[email protected] 2.55 3Carl
[email protected] 3.86 4David
[email protected] 4.00 5John Capra 6George Smith 7Elaine Sanders 8Jack Cunningham 9Susie Brown 1Marvella Garcia 1Tony Peterson 12John Jones 13Mary Evans 14Nancy Drew 15Lola Zapeta 16Duckey Donald 17Goof Goofy 18Brian Balto 19Snow Smith 20William Whitman