﻿ SQL Challenges-1: Find Student Supporter - w3resource

# SQL Challenges-1: Find Student Supporter

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

From the following table, write a SQL query to find those students who have referred by the teacher whose id not equal to 602. Return the student names.

Input:

Table: students

Structure:

FieldTypeNullKeyDefaultExtra
student_idint(11)YES
student_namevarchar(25)YES
teacher_idint(11)YES

Data:

student_idstudent_nameteacher_id
1001Alex601
1002Jhon
1003Peter
1004Minto604
1005Crage
1006Chang601
1007Philip602

Sample Solution:

SQL Code(MySQL):

``````CREATE TABLE IF NOT EXISTS students (student_id INT,student_name VARCHAR(25),teacher_id INT);
TRUNCATE TABLE students;

CREATE TABLE IF NOT EXISTS students (student_id INT,student_name VARCHAR(25),teacher_id INT);
INSERT INTO students (student_id, student_name, teacher_id) values ('1001', 'Alex', '601');
INSERT INTO students (student_id, student_name, teacher_id) values ('1002', 'Jhon', NULL);
INSERT INTO students (student_id, student_name, teacher_id) values ('1003', 'Peter', NULL);
INSERT INTO students (student_id, student_name, teacher_id) values ('1004', 'Minto', '604');
INSERT INTO students (student_id, student_name, teacher_id) values ('1005', 'Crage', NULL);
INSERT INTO students (student_id, student_name, teacher_id) values ('1006', 'Chang', '601');
INSERT INTO students (student_id, student_name, teacher_id) values ('1007', 'Philip', '602');
SELECT student_name
FROM students WHERE teacher_id <> 602
OR teacher_id IS NULL;
```
```

Sample Output:

```student_name|
------------|
Alex        |
Jhon        |
Peter       |
Minto       |
Crage       |
Chang       |
```

SQL Code Editor:

﻿

## 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