SQL query tutorial

Question

given that there are 2 such entities

city(name, country, population)
country(code, name, capital, population)

and the question is that there exist cities in different countries that have the same name. For instance, paris in texas, usa, and paris in france. we assume, however, that every city in one country has a unique name in that country. find the names of cities that have a unique name.

would this work then

SELECT DISTINCT c1.name
FROM city c1, city c2
WHERE c1.name<>c2.name;

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style should no longer be used and instead it is recommended to use the proper ANSI JOIN syntax introduced with the ANSI-92 SQL Standard (more than 20 years ago) — marc_s
– marc_s, Commented Feb 27, 2015 at 6:55
To satisfy your questions requirements you will need to get counts of how many city names are used more than once. This is where an aggregate function comes in handy. Take a look at this tutorial w3schools.com/sql/sql_having.asp — WorkSmarter
– WorkSmarter, Commented Feb 27, 2015 at 6:57
Create the tables, insert some data and try different SQL constructions - that's how you learn!!! — jarlh
– jarlh, Commented Feb 27, 2015 at 8:18

WorkSmarter · Accepted Answer · 2015-02-27 07:00:33Z

1

This will find all the cities that are unique in the database.

SELECT name
FROM city
Group by city
Having count(city) = 1

edited Feb 27, 2015 at 7:00

answered Feb 27, 2015 at 6:46

WorkSmarter

3,8383 gold badges32 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WorkSmarter Over a year ago

Hi @user3345791 if this or any answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this.

Collectives™ on Stack Overflow

SQL query tutorial

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related