Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- SCHEMA
- CREATE TABLE cities(
- id serial PRIMARY KEY,
- name VARCHAR(50) NOT NULL
- );
- CREATE TABLE users (
- id serial PRIMARY KEY,
- username VARCHAR(50) NOT NULL,
- city_id INT NOT NULL,
- CONSTRAINT fk_city
- FOREIGN KEY(city_id)
- REFERENCES cities(id)
- );
- INSERT INTO cities(id, name) VALUES
- (1, 'Nur-Sultan'),
- (2, 'Almaty'),
- (3, 'New Delhi');
- INSERT INTO users(id, username, city_id) VALUES
- (1, 'John', 1),
- (2, 'Jane', 1),
- (3, 'Mary', 2),
- (4, 'Jack', 3),
- (5, 'Anna', 3),
- (6, 'Sara', 3);
- -- QUERY
- select count(*), min(c.name) from users u
- join cities c on c.id = u.city_id
- group by u.city_id
- having count(*) > 1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement