SQL Queries

Assume a table containing rollno, name & another table containing roll no, subcd, & Marks.

Write a SQL query to:

  1. Display the student having opted maximum number of subjects.
  2. Display the subject wise maximum marks obtained by students.
  3. Display the merit list based on total marks.
  4. Display the number of students having scored 10 marks higher than average marks in subcd =01.
  5. Display the roll no. of the students who has not opted any subject.
  6. Display the total number of subjects offered.
  7. Display the roll no. of the student who has not opted any subject.
  8. Display the students who have opted all the subjects.
  9. Display the students total marks and the percentage obtained assume the maximum marks in every subject are 100.
  10. Display the top 10 worst performers in every subject.

 

Solutions:

Student (rollno, name)

Marks (rollno, subcd, marks)

 

 

1

->Student having opted maximum number of subjects.

Select Name

from student, marks

Where students rollno. = marks.rollno

Group by marks.rollno

Order by count (subcd)

DESC LIMIT 1

—//—

2

->Display the subject wise maximum marks obtained by students.

Select Subcd, max(marks)

From Marks

Group By Subcd

—//—

3

-> Display the merit list based on total marks.

Select roll no, sum (marks)

From Marks

Group By roll no

Order By sum (marks) DESC

—//—

4

-> Display the number of students having scored 10 marks higher than    average marks in subcd =01.

Select count* From Marks

Where subcd =01 && marks –(select avg(marks))

From marks Group by subcd

Having subcd =01 ) =10

 

—//—

5

-> Display the roll no. of the students who has not opted any subject.

Select name

from student

Where name like ‘% Rahul Jain %’ ;

—//—

6

-> Display the total number of subjects offered.

Select count (distinct subcd)

from marks

group by Subcd

—//—

 

7

-> Display the roll no. of the student who has not opted any subject.

Select students roll no

from students

where students, roll no., not in

(select marks,roll no

From marks)

—//—

8

-> Display the students who have opted all the subjects.

Select students, roll no.,

From Students, Marks

Where student, roll no. = marks.rollno.

Group by marks.rollno

Having count (subcd) = (Select count (distinct subject)

From marks)

—//—

9

-> Display the students total marks and the percentage obtained assume the maximum marks in every subject are 100.

Select student.name , sum (marks.marks), sum(marks.marks)/count(suncd) as percent

From student and marks

Group by marks.rollno;

—//—

10.

-> Display the top 10 worst performers in every subject.

 

SELECT TOP 10 Student Name

FROM Student.Name, Marks, Subcd

Group By Marks

ORDER BY Sum (Marks) DESC

—//—

 

 

 

 

 

 

 

 

 

Advertisements

Data Mining & Process

 

Data mining is the extraction of interesting patterns from large data sets.

Slide2.JPG

Subscribe Our Channel For More Technical Videos & Latest Updates.