w3resource

SQL Challenges-1: Consecutive Numbers

SQL Challenges-1: Exercise-4 with Solution

From the following table, write a SQL query to find the marks, which appear at least thrice one after another without interruption. Return the number.

Input:

Table: logs

Structure:

FieldTypeNullKeyDefaultExtra
student_idint(11)YES
marksint(11)YES

Data:

student_idmarks
10183
10279
10383
10483
10583
10679
10779
10883

Sample Solution:

SQL Code(MySQL):

CREATE TABLE IF NOT EXISTS logs (student_id int, marks int);
TRUNCATE TABLE logs;
INSERT INTO logs (student_id, marks) VALUES ('101', '83');
INSERT INTO logs (student_id, marks) VALUES ('102', '79');
INSERT INTO logs (student_id, marks) VALUES ('103', '83');
INSERT INTO logs (student_id, marks) VALUES ('104', '83');
INSERT INTO logs (student_id, marks) VALUES ('105', '83');
INSERT INTO logs (student_id, marks) VALUES ('106', '79');
INSERT INTO logs (student_id, marks) VALUES ('107', '79');
INSERT INTO logs (student_id, marks) VALUES ('108', '83');
select * from logs;
SELECT DISTINCT L1.marks AS  ConsecutiveNums
FROM (logs L1 JOIN logs L2 ON L1.marks = L2.marks AND L1.student_id = L2.student_id-1)
JOIN logs L3 ON L1.marks = L3.marks AND L2.student_id = L3.student_id-1;

Sample Output:

ConsecutiveNums|
---------------|
             83|

Relational Algebra Expression:

Relational Algebra Expression: Consecutive Numbers.

Relational Algebra Tree:

Relational Algebra Tree: Consecutive Numbers.

Relational Algebra Expression:

Relational Algebra Expression: Consecutive Numbers.

Relational Algebra Tree:

Relational Algebra Tree: Consecutive Numbers.

Solution-1:

SELECT DISTINCT L1.marks AS  ConsecutiveNums
FROM (logs L1 JOIN logs L2 ON L1.marks = L2.marks AND L1.student_id = L2.student_id-1)
JOIN logs L3 ON L1.marks = L3.marks AND L2.student_id = L3.student_id-1;

Solution-2:

SELECT DISTINCT L1.marks AS ConsecutiveMarks
FROM logs AS L1, logs AS L2, logs AS L3
WHERE L1.student_id = L2.student_id +1 
AND L1.student_id = L3.student_id +2 
AND L1.marks = L2.marks AND L1.marks = L3.marks;

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Nth Highest Sale amount.
Next: Duplicate Emails.



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