Please solve each question with - turple query, relational query and time domain
ID: 3723641 • Letter: P
Question
Please solve each question with - turple query, relational query and time domain query
1. Find the names of all the instructors from Biology department.
2)Find the names of courses in Computer Science department which have 3 credits.
3)For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student.
4)For the student with ID 12345 (or any other value), show the total number of credits taken by that student. Use SQL aggregation on courses taken by the student
. 5)Display the total credits for each student, along with the ID of the student; don't worry about the name of the student. (Don't bother about students who have not registered for any course, they can be omitted).
6)Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)
. 7)Display the IDs of all instructors who have never taught a couse. (Note: Oracle uses the keyword minus in place of except).
8)Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment. (Don't worry about those that had no students taking that section.)
. 9)Find all sections that had the maximum enrollment (along with the enrollment), using a subquery.
10)Find all courses whose identifier starts with the string "CS-1".
11)Create a view faculty showing only the ID, name, and department of instructors.
12)Create a view CSinstructors, showing all information about instructors from the Comp. Sci. department
. 13)Find all rooms that have been assigned to more than one section at the same time. Display the rooms along with the assigned sections. (Hint: use WITH or views.)
Explanation / Answer
NOTE: Here I am assuming table name as "education".
Solving 8 queries as it is taking much time to write and test.
Query: 1
SELECT
DISTINCT instructor_names
FROM
education
WHERE
department = "Biology";
Query: 2
SELECT
DISTINCT course_names
FROM
education
WHERE
department = "Science"
AND credits > 3;
Query: 3
SELECT
course_id,
title
FROM
education
where
student_id = 12345;
Query: 4
SELECT
student_id,
SUM(credits) as Total_Credits
FROM
education
WHERE
student_id = 12345;
Query: 5
SELECT
DISTINCT student_id,
SUM(credits) as total_credit
FROM
education;
Query: 6
SELECT
DISTINCT student_names
FROM
education
WHERE
course = "Comp. Sci.";
Query: 7
SELECT
instructor_id
FROM
education
where
course = null;
Query: 8
SELECT
DISTINCT sections,
MAX(enrollment),
MIN(enrollment)
FROM
education;