Question

Consider the database for a college. Write the query for the following.

Insert at least 5 tuples into each table.

a. List the details of students in the ascending order of date of birth

b. Display the details of students from computer department

c. List the faculties in the descending order of salary

d. Display the total number of students in each department

e. Display the total number of faculties in each department with salary greater than 25000

Answer 1

(I'm answering this with the view that the column names include Name, DOB, Department and Salary, by going through the questions and that the table name is College.)

Inserting at least 5 tuples into the table.

• Insert into College values('Vanessa', '2000-09-14', 'English', 2000);
• Insert into College values('April', '2001-06-16', 'Computer', 1500);
• Insert into College values('Jeanette', '2000-12-31', 'Computer', 36000);
• Insert into College values('Ace', '2000-10-06', 'Sports', 25400);
• Insert into College values('Nathan', '2002-03-14', 'Sports', 15000);

a. List the details of students in the ascending order of date of birth.

• Select * from College order by DOB;

b. Display the details of students from the computer department.

• Select * from College where Department = 'Computer';

c. List the faculties in the descending order of salary;

• Select Department from College order by Salary desc;

d. Display the total number of students in each department.

• Select Count(Name) from College;

e. Display the total number of faculties in each department with a salary greater than 25000.

• Select Count(Department) from College where Salary > 25000;