Scribd
Upload
46 views6 pages

Sab Query

The lab assignment asks the student to: 1. Ensure they have tables for Courses, Students, and course Registrations loaded with sample data. 2. Write SQL queries to return: - Details of all students - Students not registered in any course - Students registered in courses by joining or subquery - Students details and course registered by outer join 3. Return aggregate information like: - Third highest course fee - Number of students from each city - Details of students in most expensive course - Highest, second highest, and total sum of course fees - Department total fees and fees over Rs. 80,000 or Rs. 30,000

Uploaded by

Compiler Error
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
46 views6 pages

Sab Query

The lab assignment asks the student to: 1. Ensure they have tables for Courses, Students, and course Registrations loaded with sample data. 2. Write SQL queries to return: - Details of all students - Students not registered in any course - Students registered in courses by joining or subquery - Students details and course registered by outer join 3. Return aggregate information like: - Third highest course fee - Number of students from each city - Details of students in most expensive course - Highest, second highest, and total sum of course fees - Department total fees and fees over Rs. 80,000 or Rs. 30,000

Uploaded by

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

Lab assignment

(SUBQUERY, GROUPING, JOINS) SUBJECT: DBMS

1. Ensure that your login has following tables with proper constraints and data:-
COURSE TABLE
CourseCode CourseName CourseFees Department
101 MCA 80000 MCA
102 MBA 60000 MBA
103 Btech 75000 Engg
104 BCA 30000 MCA
105 BSc(IT) 25000 MCA
106 MSc(IT) 35000 MCA
107 BBA 22000 MBA

STUDENT TABLE
RollNo Name City
1 Amit Delhi
2 Sumit Goa
3 Shweta Gwaliar
4 Kumar Goa
5 Puneet Meerut
6 Bharat Gwaliar

REGISTER TABLE
RollNo CourseCode
2 101
4 103
5 101
3 105
1 103

SQL> con hr
Enter password:
Connected.
2. See the contents of above table and commit data
3. Write queries for the following:-
a) Show details of all students

b) Show details of all students who aren’t registered in any course

c) Show details of students who are registered in any course:


• Write Query Using Join

• Write Query Using Sub query


d) Details of all students along with course details in which they are registered, if any.
Include those students also which are not registered (Use Outer Join)

e) Display third highest fees

f) Number of students from each city

g) Details of students who are registered in most expensive course


h) Display highest course fees

i) Display second highest course fees

j) Display total sum of all course fees

k) Display department wise total fees of all courses. Show both department name and
total fees

l) Display department wise total fees of all those departments whose total department
fees is greater than Rs. 80000/-
m) Display department wise total fees of only those courses having fees greater than Rs.
30000/-

You might also like