Intern — Senior
SQL query to retrieve rows with unique field values
livecode
Task condition
In the database, there are two related tables — works and materials. You need to write a query that returns only those records from the materials table where the value of the name column occurs only once.
CREATE TABLE works
(
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE materials
(
id SERIAL PRIMARY KEY,
name VARCHAR(255),
quantity INTEGER,
work_id BIGINT,
FOREIGN KEY (work_id) REFERENCES works(id) ON DELETE CASCADE
);
INSERT INTO works(id, name)
VALUES (1, 'work 1'),
(2, 'work 2'),
(3, 'work 3');
INSERT INTO materials(id, name, quantity, work_id)
VALUES (1, 'mat 1', 10, 1),
(2, 'mat 2', 20, 2),
(3, 'mat 2', 15, 2),
(4, 'mat 4', 30, 2);