Basic SQL Select Queries

Qn 1.

This first question in SQL helped me to recollect the basics I learnt. The solution came to me almost instantly requiring not much thought.

• The question wants the rows in column CITY that has ID 1661.

SELECT * FROM city WHERE id=1661;

---

Qn 2.

• This was also pretty intuitive knowing the very basics that were brushed up in class.

select * from city where countrycode ='USA' and population >100000; 

---

Qn 3.

• Another easy query returning one column using where clause.

select * from city where countrycode='JPN';

---

Qn 4.

• This was a little challenging at first. I couldn't remember count so it threw me off a bit. Then i looked at the problem logically and not just trying to remember the syntax, thinking "How will i get the number of rows in city column?" That's when it hit me and I got the word count. With that it was easy to solve.

select count (city) - count(distinct city) from station;

---

Qn 5.

• Another simple where clause query which was successful in the first attempt.

select name from city where countrycode='USA' and population>120000;

---

Qn 6.

• One of the easy qns from the problem statement but it was fun to solve regardless.

select name from city where countrycode='JPN';

---

Qn 7.

• This one had me stumped. I arrived at the logic pretty quick but the syntax was not that easy to get it right. The query below is what i came up with and later i came to know about regexp, with which the query could be made much shorter and more effective in terms of complexities.

select distinct city from station where city not like 'A%'
and city not like 'E%'
and city not like 'I%'
and city not like 'O%'
and city not like 'U%'
;

---

Qn 8.

• Absolutely no explanation needed for this one.

select * from city;

---

Qn 9.

• Finishing off strong with yet another simple query. These easy qns after that one challenging query were happy wins for me.

select city, state from station;

---