Translate a relational algebra expression into SQL, exactly.
What to build
A query that computes π name ( σ dept = 'CSE' ∧ marks > 80 ( Student ) ).
Requirements
- Keep the CSE students with more than 80 marks.
- Return only their names, with no name repeated, as projection requires.
- Sort the names alphabetically.
Tables
Each test fills these tables with its own rows, then runs your SQL:
CREATE TABLE Student (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL,
marks INTEGER
);
Output
One column, name, in alphabetical order. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).
Constraints
Two students may share a name. Marks may be NULL.
Example
When a test starts with:
INSERT INTO Student VALUES (1, 'Rahim', 'CSE', 85), (2, 'Nadia', 'CSE', 91), (3, 'Karim', 'EEE', 90), (4, 'Rahim', 'CSE', 88);
your SQL should print:
name
-----
Nadia
Rahim
(2 rows)
Why it works
Relations are sets, so projection never repeats a row. Plain SELECT keeps repeats, and DISTINCT is what makes the SQL match the algebra.
Examples
CREATE TABLE Student ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL, marks INTEGER ); INSERT INTO Student VALUES (1, 'Rahim', 'CSE', 85), (2, 'Nadia', 'CSE', 91), (3, 'Karim', 'EEE', 90), (4, 'Rahim', 'CSE', 88);
name ----- Nadia Rahim (2 rows)
CREATE TABLE Student ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL, marks INTEGER ); INSERT INTO Student VALUES (1, 'A', 'CSE', 80);
name ---- (0 rows)
Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.
Hints
Hint 1
σ becomes WHERE, and ∧ becomes AND.
Hint 2
π removes repeated rows, so it becomes SELECT DISTINCT.
Solution
Show one correct solution (try it yourself first)
SELECT DISTINCT name FROM Student WHERE dept = 'CSE' AND marks > 80 ORDER BY name;