Database Lab Assignment 2 Solution



Use the following tables of “DBLAB” that you have created in the last assignment.

  • Paper_details(paper_id (varchar), paper_title (varchar), paper_type* (varchar), publication(date))//*paper-type should be conference/journal

  • Paper_author(paper_id(varchar), author_id(varchar))

  • Author_details(author_id(varchar), author_type* (varchar))//*author_type should be student/faculty

  • Student_details(student_id(varchar), student_name(varchar), student_institute(varchar), department(varchar), DOB(date), research_area(varchar))

  • Faculty_details(faculty_id(varchar), faculty_name(varchar), faculty_institute(varchar), department(varchar), DOB(date), research_area(varchar))

  • Supervisor(faculty_id(varchar), student_id(varchar))

Now write MySQL query to perform each of the followings-

  1. Display the faculty details for those faculties who have not supervised any student.

  1. List the details of students who are from IIT Patna and also have published paper.

  2. List of all the papers published by a student with id = “1501CS60”

  3. List of all the students who have published conference paper under the guidance of faculty with id =102

  1. List the titles of the papers which are authored by a single author.

  1. List the faculty_id and the corresponding number of papers published by that faculty.

  1. Display the author_id with maximum number of papers.

  2. Display the details of all the students who have no publication.

  3. List the students who are guided by two faculties.

  4. Count the number of papers published by the faculties of research area “Big Data”

  5. Display the research areas of the faculties in descending order based on the total number of papers published.

  1. Display the faculty details who has guided maximum number of student.

error: Content is protected !!