Write a SQL statement for each of the following questions based on the above tables (50 Points).

   

1) Create “Enrollment” table.

   

2) Change the credits of “Database” course from 2 to 3.

   

3) List the course titles (not Course_No) and grades John Doe had taken.

   

4) Calculate average of Café Balance, and name new field as Average Balance.

   

5) Create a roster for “Database” course (list student ID, last name, first name, major, and status for each student enrolled the course).

   

6) Find number of students for each major, and name new field as No of Student.

   

7) Insert a new student data. You choose the data.

   

8) Delete the student date you inserted in Question 7).

   

9) List student ID, first name, last name, major, course no, course title, who got the grade A.

   

10) List student ID, first name, and last name whose café balance is $54, $434, OR $23. Use IN operator.

   

Transcribed Image Text:

The following tables contain the data for courses, enrollment, and students. ENROLLMENT Student ID COURSE Course Number Title AC202 |FL101 CIS326 |FL105 FL503 MA102 MA130 NM204 PH121 |PH150 PY123 Instructor Credits Accounting Guffsaton Basic English McCoy Database Choi Speech Frank Composition Mauffler Basic Math Murray Statistics Counter Journalism Basic circuit Shockly Tennis Lizzer Resisitor T Charlse Hernandon 000348282 Tirilee 234556677 Koons 777349993 John 800005555 Patrick 999001111 James 999009800 Mary 999110000 Sally 666110001 Bill Lytler Smith Doe Saint Kirkland Smith Jones Mack Johnson Schwartz Donald 555119800 Greg 444339837 Jacob 333448887 Jenny 222551288 Nancy Ogur Business Business Und. Math 3 English English Business English English Art 3 2 2 STUDENT Student ID First Name Last Name Major Status Gender Spanish Business 3 3 3 2 3 1 1 1 1 2 1 1 1 2 777349993 777349993 777349993 777349993 2 1 3 777349993 777349993 777349993 800005555 999001111 234556677 999110000 999119800 000348282 666110001 555119800 444339837 333448887 222551288 222551288 1 Course No AC202 CS303 FL105 MA130 2 NM204 PH150 PY123 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 NM204 Grade IC A D JA B A B C JA D A B C IC D JA B Date of Birth Café Balance 2 12/01/1974 1 03/03/1976 2 02/03/1974 2 07/08/1974 1 01/05/1976 2 08/28/1947 2 06/19/1978 1 09/06/1978 1 04/05/1955 12/25/1974 08/07/1974 05/05/1948 B B $23.00 $0.00 $34.00 $45.00 $55.00 $323.00 $43.00 $434.00 $434.00 $54.00 $54.00 $54.00 The following tables contain the data for courses, enrollment, and students. ENROLLMENT Student ID COURSE Course Number Title AC202 |FL101 CIS326 |FL105 FL503 MA102 MA130 NM204 PH121 |PH150 PY123 Instructor Credits Accounting Guffsaton Basic English McCoy Database Choi Speech Frank Composition Mauffler Basic Math Murray Statistics Counter Journalism Basic circuit Shockly Tennis Lizzer Resisitor T Charlse Hernandon 000348282 Tirilee 234556677 Koons 777349993 John 800005555 Patrick 999001111 James 999009800 Mary 999110000 Sally 666110001 Bill Lytler Smith Doe Saint Kirkland Smith Jones Mack Johnson Schwartz Donald 555119800 Greg 444339837 Jacob 333448887 Jenny 222551288 Nancy Ogur Business Business Und. Math 3 English English Business English English Art 3 2 2 STUDENT Student ID First Name Last Name Major Status Gender Spanish Business 3 3 3 2 3 1 1 1 1 2 1 1 1 2 777349993 777349993 777349993 777349993 2 1 3 777349993 777349993 777349993 800005555 999001111 234556677 999110000 999119800 000348282 666110001 555119800 444339837 333448887 222551288 222551288 1 Course No AC202 CS303 FL105 MA130 2 NM204 PH150 PY123 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 CIS326 NM204 Grade IC A D JA B A B C JA D A B C IC D JA B Date of Birth Café Balance 2 12/01/1974 1 03/03/1976 2 02/03/1974 2 07/08/1974 1 01/05/1976 2 08/28/1947 2 06/19/1978 1 09/06/1978 1 04/05/1955 12/25/1974 08/07/1974 05/05/1948 B B $23.00 $0.00 $34.00 $45.00 $55.00 $323.00 $43.00 $434.00 $434.00 $54.00 $54.00 $54.00

Answer rating: 100% (QA)

Answers Q1 Syntax for creating table query CREATE TABLE tablename columnName1 DataTypeSizecolumnName2 DataTypeSizecolumnNameN DataTypeSize CREATE TABL... View the full answer