fork download

copy

Q: Find the average balance of all bank accounts.
 
SELECT AVG(balance) AS avg_balance FROM account;
 
A2
 
Q: Find the maximum loan amount per branch.
 
SELECT branch_id, MAX(amount) AS max_loan FROM loan
 
GROUP BY branch_id;
 
A3
 
Q: Find total number of students in each department.
 
SELECT dept_id, COUNT(*) AS total_students FROM student
 
GROUP BY dept_id;
 
A4
 
Q: Find highest grade in course 'DBMS'.
 
SELECT MAX (grade) AS highest_grade FROM enroll e JOIN course c ON e.course_id=c.course_id
 
WHERE c.title= 'DBMS';
 
 
 
A5
 
Q: Find the oldest patient.
 
SELECT MAX (age) AS oldest_patient_age FROM patient;
 
A6
 
Q: Count appointments handled by each doctor.
 
SELECT doctor_id, COUNT(*) AS appointment_count
 
FROM appointment
 
GROUP BY doctor_id;
 
A7
 
Q: Count books by each author.
 
SELECT author, COUNT(*) AS total_books
 
FROM book
 
GROUP BY author:
 
A8
 
Q: Which book is borrowed maximum times?
 
SELECT book id, COUNT(*) AS times_borrowed
 
FROM borrow
 
GROUP BY book id
 
ORDER BY COUNT(*) DESC
 
FETCH FIRST I ROW ONLY;
 
 
B2
 
Q: Print label based on balance:
 
HIGH >100k
 
MEDIUM >50k
 
LOW otherwise
 
 
SELECT account_number,
 
CASE
 
WHEN balance > 100000 THEN 'HIGH'
 
WHEN balance > 50000 THEN 'MEDIUM'
 
ELSE 'LOW'
 
END AS balance_label
 
FROM account;
 
B3
 
Q: Show student status Pass/Fail (>=60).
 
SELECT student id, student _name,
 
CASE
 
WHEN grade >= 60 THEN 'PASSED'
 
ELSE 'FAILED'
 
END status
 
FROM enroll;
 
 
C1
 
Q: List customers who have an account.
 
SELECT DISTINCT c.customer_name
 
FROM customer c
 
JOIN depositor d ON c.customer_id =d.customer_id;
 
C2
 
Q: Customers who have BOTH loan and account.
 
SELECT DISTINCT c.customer_name
 
FROM customer c
 
JOIN depositor d ON c.customer_id= d.customer_id
 
JOIN borrower b ON c.customer_id=b.customer id;
 
C3
 
Q: Students enrolled in a course taught by Dr.Ali.
 
SELECT DISTINCT s.student_name
 
FROM student s
 
JOIN enroll e ON s.student_id = e.student_id
 
JOIN teach t ON t.course ide.course id
 
JOIN instructor i ON i. instructor_id = t.instructor_id
 
WHERE i.instructor_name = 'Dr. Ali';
 
C4
 
Q: Instructors who teach at least one course.
 
SELECT DISTINCT instructor_name
 
FROM instructor i
 
JOIN teach t ON i.instructor_id = t. instructor_id;
 
Q: List patients treated by Dr.Rahman.
 
SELECT DISTINCT p.patient_name
 
FROM patient p
 
JOIN appointment a ON p.patient id = a.patient_id JOIN doctor d ON d.doctor id = a.doctor id WHERE d.doctor name = 'Dr. Rahman';
 
C6
 
Q: List appointment with doctor name + patient name.
 
SELECT app_id, p.patient_name, d.doctor_name FROM appointment a
 
JOIN patient p ON a.patient_id = p.patient_id JOIN doctor d ON a.doctor_id= d.doctor_id;
 
C7
 
Q: Members who borrowed books by "J.K. Rowling".
 
SELECT DISTINCT m.member name
 
FROM member m
 
  JOIN borrow b ON m.member id=b.member_id
 
JOIN book bk ON bk.book id =b.book id
 
WHERE bk.author = 'J.K. Rowling';
 
C8
 
Q: Books borrowed by people from the same city as author (assume author city stored in publisher)
 
SELECT DISTINCT bk.title
 
FROM book bk
 
JOIN borrow br ON bk.book id= br.book id
 
JOIN member m ON m.member_id = br.member_id WHERE m.city bk.publisher;
 
 
Q:
Department-wise average salary (only those > 10,000)
 
SELECT 
    dept_id,
    AVG(salary) AS avg_salary
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > 10000;
 
Q3:
Find the patients who were treated by the same doctor as patient 'P01'
 
Assume table treatment(patient_id, doctor_id, ...)
 
SELECT DISTINCT t.patient_id
FROM treatment t
WHERE t.doctor_id = (
    SELECT doctor_id
    FROM treatment
    WHERE patient_id = 'P01'
)
AND t.patient_id <> 'P01';

Main.java:1: error: class, interface, or enum expected
Q: Find the average balance of all bank accounts.
^
Main.java:5: error: class, interface, or enum expected
A2
^
Main.java:13: error: class, interface, or enum expected
A3
^
Main.java:21: error: class, interface, or enum expected
A4
^
Main.java:23: error: unclosed character literal
Q: Find highest grade in course 'DBMS'.
                                ^
Main.java:23: error: unclosed character literal
Q: Find highest grade in course 'DBMS'.
                                     ^
Main.java:27: error: unclosed character literal
WHERE c.title= 'DBMS';
               ^
Main.java:27: error: unclosed character literal
WHERE c.title= 'DBMS';
                    ^
Main.java:37: error: class, interface, or enum expected
A6
^
Main.java:47: error: class, interface, or enum expected
A7
^
Main.java:72: error: class, interface, or enum expected
B2
^
Main.java:87: error: unclosed character literal
WHEN balance > 100000 THEN 'HIGH'
                           ^
Main.java:87: error: illegal line end in character literal
WHEN balance > 100000 THEN 'HIGH'
                                ^
Main.java:89: error: unclosed character literal
WHEN balance > 50000 THEN 'MEDIUM'
                          ^
Main.java:89: error: illegal line end in character literal
WHEN balance > 50000 THEN 'MEDIUM'
                                 ^
Main.java:91: error: unclosed character literal
ELSE 'LOW'
     ^
Main.java:91: error: illegal line end in character literal
ELSE 'LOW'
         ^
Main.java:97: error: class, interface, or enum expected
B3
^
Main.java:105: error: unclosed character literal
WHEN grade >= 60 THEN 'PASSED'
                      ^
Main.java:105: error: illegal line end in character literal
WHEN grade >= 60 THEN 'PASSED'
                             ^
Main.java:107: error: unclosed character literal
ELSE 'FAILED'
     ^
Main.java:107: error: illegal line end in character literal
ELSE 'FAILED'
            ^
Main.java:114: error: class, interface, or enum expected
C1
^
Main.java:124: error: class, interface, or enum expected
C2
^
Main.java:136: error: class, interface, or enum expected
C3
^
Main.java:150: error: unclosed character literal
WHERE i.instructor_name = 'Dr. Ali';
                          ^
Main.java:150: error: unclosed character literal
WHERE i.instructor_name = 'Dr. Ali';
                                  ^
Main.java:162: error: class, interface, or enum expected
Q: List patients treated by Dr.Rahman.
^
Main.java:168: error: unclosed character literal
JOIN appointment a ON p.patient id = a.patient_id JOIN doctor d ON d.doctor id = a.doctor id WHERE d.doctor name = 'Dr. Rahman';
                                                                                                                   ^
Main.java:168: error: unclosed character literal
JOIN appointment a ON p.patient id = a.patient_id JOIN doctor d ON d.doctor id = a.doctor id WHERE d.doctor name = 'Dr. Rahman';
                                                                                                                              ^
Main.java:178: error: class, interface, or enum expected
C7
^
Main.java:190: error: unclosed character literal
WHERE bk.author = 'J.K. Rowling';
                  ^
Main.java:190: error: unclosed character literal
WHERE bk.author = 'J.K. Rowling';
                               ^
Main.java:205: error: class, interface, or enum expected
Q:
^
Main.java:215: error: class, interface, or enum expected
Q3:
^
Main.java:216: error: unclosed character literal
Find the patients who were treated by the same doctor as patient 'P01'
                                                                 ^
Main.java:216: error: illegal line end in character literal
Find the patients who were treated by the same doctor as patient 'P01'
                                                                     ^
Main.java:225: error: unclosed character literal
    WHERE patient_id = 'P01'
                       ^
Main.java:225: error: illegal line end in character literal
    WHERE patient_id = 'P01'
                           ^
Main.java:227: error: unclosed character literal
AND t.patient_id <> 'P01';
                    ^
Main.java:227: error: unclosed character literal
AND t.patient_id <> 'P01';
                        ^
41 errors

Standard output is empty