Consider the following tables with partial saved information

Consider the following tables (with partial saved information) from the database \"University\". Describe in plain English what the following statements would do {keep in mind that the data given above is only partial) and list the solution given the partial data in the tables above. For the \'insert\' and \'update\' statements, list the entry in each field of the corresponding table. insert into table Student(FirstName, lastName, Major, YearEnrolled) values(\"Mike\", \"Johnson\", \"CIS\", 2015); select * from Takes where StudentlD=123 and Grade=\"A\"; delete from Takes where StudentlD=222; update Course set CourseTitle=\"Business Terminology\" where \"CourselD=\"BUS101\"; select major, count(*) from Student group by major;

Solution

a. This insert statement insert a new row in the Student table with following values

FirstName ----- Mike

LastName  ----- Johnson

Major   ------- CIS

YearEnrolled -----2015

If there is primary key is defined its value should be included in the insert columns eg ID here.

b.

This select query will display all records from the table Takes where StudentId = 123 and Grade =A

Now in the table for the first condition ,there are two rows with the StudentId =123 but for second condition Grade = A , and both conditions should satisfy so there is only one row which will be the output as follows

StudentId =123

UniqueCourseIdentified = CIS101_2014Q11

Grade =A

c.

This delete query will delete all rows from the table where StudentID =222

There is no rows matching this condition. So no row will be deleted.

d.

This update query will update the table Course . It will set the value of CourseTitle =\"Business Terminology\" where CourseId =\"BUS101\".

now there is one row with this CourseID =\"BUS101\" where CourseTitle =\"Introduction to Business\" . With this update query, The CourseTitle will be Changed to \"Business Terminology\".

e.

This select query will select all majors and count the corresponding rows matching those majors from Student table as it is grouping the rows based on major.

The output will will as under:

CIS          2

Business   1

Drama       1