﻿ SQL Challenges-1: Highest Single marks - w3resource

# SQL Challenges-1: Highest Single marks

## SQL Challenges-1: Exercise-23 with Solution

Table students contain marks of mathematics for several students in a class. It may same marks for more than one student.
From the following table write a SQL table to find the highest unique marks a student achieved. Return the marks.

Input:

Table: students

Structure:

FieldTypeNullKeyDefaultExtra
student_idint(11)YES
student_namevarchar(255)YES
marks_achievedint(11)YES

Data:

student_idstudent_namemarks_achieved
1Alex87
2Jhon92
3Pain83
4Danny87
5Paul92
6Rex89
7Philip87
8Josh83
9Evan92
10Larry87

Sample Solution:

SQL Code(MySQL):

``````CREATE TABLE students(student_id int, student_name varchar(255), marks_achieved int);

INSERT INTO students VALUES(1, 'Alex',87);
INSERT INTO students VALUES(2, 'Jhon',92);
INSERT INTO students VALUES(3, 'Pain',83);
INSERT INTO students VALUES(4, 'Danny',87);
INSERT INTO students VALUES(5, 'Paul',92);
INSERT INTO students VALUES(6, 'Rex',89);
INSERT INTO students VALUES(7, 'Philip',87);
INSERT INTO students VALUES(8, 'Josh',83);
INSERT INTO students VALUES(9, 'Evan',92);
INSERT INTO students VALUES(10, 'Larry',87);

SELECT * FROM students;

SELECT MAX(marks_achieved) as marks
FROM (
SELECT marks_achieved
FROM students
GROUP BY marks_achieved
HAVING COUNT(*) = 1
) z;
```
```

Sample Output:

```marks|
-----|
89|
```

SQL Code Editor:

Previous: Sales Person.
Next: Internal changes of beds.

﻿

## SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

```SELECT...
LIMIT y OFFSET x
```

Oracle, DB2 and MSSQL supports the standard windowing functions:

```SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
```

Database: SQL

Ref: https://bit.ly/3zPxcD8