Find Students with Missing Email
The "College Central" database stores student information for various colleges across India. You are tasked with identifying students who haven't registered their email addresses, as this is crucial for sending out placement notifications.
Tables:
students:student_id(PK),first_name,last_name,college_id,emailcolleges:college_id(PK),college_name
Return the student_id, first_name, last_name, and college_name for all students belonging to the college named 'IIT Madras' whose email is NULL. Order the results by student_id.
If there are no students matching the criteria, return an empty result set. If multiple students have the same student_id (which shouldn't happen with a PK but is handled), the order does not matter. NULL values for email are the primary focus.
Tables
CREATE TABLE colleges (
college_id integer PRIMARY KEY,
college_name varchar(100) NOT NULL
);
CREATE TABLE students (
student_id integer PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
college_id integer REFERENCES colleges(college_id),
email varchar(100)
);Example data
INSERT INTO colleges (college_id, college_name) VALUES (1, 'IIT Bombay'), (2, 'IIT Madras'), (3, 'NIT Trichy'); INSERT INTO students (student_id, first_name, last_name, college_id, email) VALUES (101, 'Arjun', 'Sharma', 2, '[email protected]'), (102, 'Priya', 'Singh', 2, NULL), (103, 'Vikram', 'Patel', 1, '[email protected]'), (104, 'Anjali', 'Verma', 2, NULL), (105, 'Rohan', 'Gupta', 3, '[email protected]'), (106, 'Meera', 'Nair', 2, '[email protected]'), (107, 'Suresh', 'Kumar', 2, NULL);
Your query is also checked on hidden datasets with different data.
Hints
-- Select students without email addresses
SELECT
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.