Question

You may use Microsoft access or Derby DB

Please I need an answer for this problem, I have posted this problem 5 times.

Put aggregate without condition for the following 6 queries:

• Queries:

  -- Query 1
  -- Find the details of students who was born after 31st december 2004.
  select * from Student_Information where DOB>"2004-12-31";

  -- Query 2
  -- Find all student details whose first name begins with J.
  select * from Student_Information where FirstName like 'J%';

  -- Query 3
  -- Find the total fees for each FeeID.
  select FeeID,(OrientationFee+CampusFee+LabFee+CommencementsFee) as "Total Fees" from Fees;

  -- Query 4
  -- Find all subject IDs whose Subject1 is botany.
  select subjectID from Subjects where Subject1="Botany";

  -- Query 5
  -- Find all gradeIDs with 10 points for both quiz and exam.
  select GradeID from Grades where Quizzes=10 and exams=10;

  -- Query 6
  -- Find the feeid and orientation fees where orientation fees is beween 500 and 1300
  select FeeId,OrientationFee from Fees where OrientationFee BETWEEN 500 AND 1300;

Schema:

Student's Information

Fees

Subjects

Grades

Student’s Information (ID, Firstname, Lastname, DOB);

Fees (ID, Orientationfees, Campusfees, Labfees, Commencementfees);

Subjects (ID, Subject1, Subject2, Subject3, Subject4);

Grades (ID, Quizzes, Classwork, Homework, Exams, Finalgrade);

Student's Information

ID	Firstname	Lastname	DOB

Fees

ID	Orientationfees	Campusfees	Labfees	Commencementfees

Subjects

ID	Subjectname

Grades

ID	Quizzes	Classwork	Homework	Exams	Finalgrade

Answer 1

Answer.

Step 1

An aggregate functions are used to calculate the summary values from the data in a particular column. An aggregate function return a single value. They are: SUM, AVG, MAX, MIN and COUNT.

1. In query 1, we can use aggregate function to count the number of students, who was born after 31st december 2004.

select COUNT(ID) from Student_Information where DOB>"2004-12-31";

2. In query 2, we can use aggregate function count, to count the number of students whose first name begins with J.

select COUNT(ID) from Student_Information where FirstName like 'J%';

3. In query 3, we can use aggregate sum() function to find the total fees for each FeeID.
select FeeID, SUM(OrientationFee+CampusFee+LabFee+CommencementsFee) as "Total Fees" from Fees;

Step 2

4. In query 4, we can use aggregate count() function to count the subject IDs whose Subject1 is botany.
select COUNT(subjectID) from Subjects where Subject1="Botany";

5. In query 5, we can use aggregate count() function to count the gradeIDs with 10 points for both quiz and exam.
select COUNT(GradeID) from Grades where Quizzes=10 and exams=10;

6. In query 6, we can use aggregate avg() function to find the average of all orientation fees where orientation fees is between 500 and 1300.
select AVG(OrientationFee) from Fees where OrientationFee BETWEEN 500 AND 1300;

Thank you.