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:


Contribute your code and comments through Disqus.

Previous: Unique values.
Next: Salesperson that makes maximum number of sales amount.



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