Scribd
Upload
23 views11 pages

MIS SQL Assignment 29-044 Gourab Mondal

gjkflflf

Uploaded by

Gourab Mondal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
23 views11 pages

MIS SQL Assignment 29-044 Gourab Mondal

gjkflflf

Uploaded by

Gourab Mondal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 11

Assignment on

WRITING SQL
QUERIES

W202
Assignment on
Writing SQL Queries

Course: Management Information Systems (W202)

Submitted to
Md. Mahmudur Rahman
Assistant Professor,
Department of Computer Science & Engineering, University of Dhaka

Submitted by
Gourab Mondal,

Roll 44, Section: B


Batch: BBA 29th

IBA, University of Dhaka

Date of Submission

20th April, 2024


1. Find the name(s) of the students who have completed at most 20 credits.
SELECT name

FROM student

WHERE tot_cred <= 20

2. Find the name(s) of the departments where some student named 'Jack' has
been registered.
SELECT DISTINCT dept_name
FROM student
WHERE name='Jack';

1|Page
3. Find all the departments situated in 'Packard' building or 'Watson'
building. SELECT dept_name
FROM department
WHERE building IN ('Packard', 'Watson');

4. Find the name(s) of the instructors who get more than 50000 salary.
SELECT name
FROM instructor
WHERE salary > 50000;

2|Page
5. Find the classroom(s) (building with room number) with a capacity of at
least 20 but no more than 50.
SELECT building, room_number
FROM classroom
WHERE capacity >= 20 AND capacity <= 50;

6. Find all the room numbers used in Spring 2017 as a classroom.


SELECT room_number
FROM section
WHERE semester = 'Spring' AND year = 2017;

7. Show the courses (title) of Computer Science Department with 3 credits.


SELECT title AS courses
FROM course
WHERE dept_name='Comp. Sci.' AND credits=3;

3|Page
8. Show the department names with budgets where the budget is less than
20000.
SELECT dept_name
FROM department
WHERE budget < 20000;

9. Show the start time of all the time slots.


SELECT start_hr, start_min
FROM time_slot;

4|Page
10. Find the names of the student(s) who are advised by some instructor(s)
with more than 50000 salary. SELECT student.name
FROM student
INNER JOIN advisor ON student.ID = advisor.s_ID
INNER JOIN instructor ON advisor.i_ID = instructor.ID
WHERE instructor.salary > 50000;

11. Show a list of courses taught by some instructors from Physics department
in Spring 2008.
SELECT course.title
FROM course
JOIN section ON course.course_id = section.course_id
JOIN teaches ON teaches.course_id = section.course_id
AND teaches.sec_id = section.sec_id
AND teaches.semester = 'Spring'
AND teaches.year = 2008
JOIN instructor ON teaches.ID=instructor.ID
WHERE instructor.dept_name = 'Physics';

5|Page
12. List a pair of courses where the courses of a pair have equal credits. [Pair
should contain different courses]
SELECT c1.title AS course1, c2.title AS course2
FROM course c1
INNER JOIN course c2 ON c1.course_id <> c2.course_id AND c1.credits = c2.credits;

6|Page
13. Show a list of student and instructor pairs where the student of a pair has taken a
course of the instructor and achived a grade 'A'.
SELECT DISTINCT s.name AS student_name, i.name AS instructor_name
FROM student s
JOIN takes t ON s.ID = t.ID
JOIN teaches tch ON t.course_id = tch.course_id AND t.sec_id = tch.sec_id AND t.semester =
tch.semester AND t.year = tch.year
JOIN instructor i ON tch.ID = i.ID
WHERE t.grade = 'A';

14. Find the instructors who have never taken any courses.
SELECT instructor.name
FROM instructor
LEFT JOIN teaches
ON instructor.ID=teaches.ID
WHERE teaches.ID is null;

7|Page
15. Suppose there is a scheme that each department will get some
compensation on budget. Condition is, department will get 20% compensation
if the number of instructor is less than 5. Otherwise, it will get 15% as
compensation. Show a list of department with the compensation amount.
output: (Name of the department, Amount of compensation)
SELECT dept_name,
CASE WHEN (SELECT COUNT(*) FROM instructor WHERE dept_name = d.dept_name)
<5
THEN budget * 0.2
ELSE budget * 0.15
END AS compensation
FROM department d;

8|Page
16. Show the classrooms that were never utilized for any classes.

SELECT classroom.building || ' ' || classroom.room_number AS classroom


FROM classroom
LEFT JOIN section
ON classroom.room_number=section.room_number
WHERE section.room_number is null;

9|Page

You might also like