UptoSkills Logo
Login
Find Students from a Specific CollegeEasy

Find Students from a Specific College

EasySQLPractice library · +10 XPselect and filtersqlfiltering

A university maintains a list of its students and their affiliated colleges.

Tables:

  • Students (student_id integer, student_name varchar(100), college_id integer)
  • Colleges (college_id integer, college_name varchar(100))

Return the student_name for all students who belong to the college named 'IIT Madras'. The results should be ordered alphabetically by student name. Ties in student names are handled by SQL's default behavior. NULL values in student_name or college_name are not expected in this dataset. An empty result is possible if no students are from 'IIT Madras'.

Tables

CREATE TABLE Colleges (
    college_id integer PRIMARY KEY,
    college_name varchar(100) NOT NULL
);

CREATE TABLE Students (
    student_id integer PRIMARY KEY,
    student_name varchar(100) NOT NULL,
    college_id integer REFERENCES Colleges(college_id)
);

Example data

INSERT INTO Colleges (college_id, college_name) VALUES
(1, 'IIT Bombay'),
(2, 'IIT Madras'),
(3, 'IIT Delhi');

INSERT INTO Students (student_id, student_name, college_id) VALUES
(101, 'Arjun Sharma', 2),
(102, 'Priya Singh', 1),
(103, 'Rahul Verma', 2),
(104, 'Anjali Gupta', 3),
(105, 'Vikram Patel', 2),
(106, 'Neha Kumari', 1),
(107, 'Amit Kumar', 2);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems