Difference between revisions of "SELECT names"

From SQLZOO
Jump to: navigation, search
Line 180: Line 180:
 
SELECT name FROM world
 
SELECT name FROM world
 
  WHERE name LIKE '____'
 
  WHERE name LIKE '____'
 +
</source>
 +
</div>
 +
 +
==Harder Questions==
 +
Well done for getting this far - these questions are optional and only for students who are finding the basic questions too easy.
 +
 +
<div class='qu'>
 +
Show the countries where the capital is the same as the name of the country
 +
<p class='imper'>Find the countries that have exactly four characters.</p>
 +
 +
<source lang='sql' class='def'>
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT name FROM world
 +
WHERE name = capital
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 18:13, 30 April 2015

namecontinent
AfghanistanAsia
AlbaniaEurope
AlgeriaAfrica
AndorraEurope
AngolaAfrica
....

Pattern Matching Strings

This tutorial uses the LIKE operator to check names. We will be using the SELECT command on the table world:

Summary

You can use WHERE name LIKE 'B%' to find the countries that start with "B".

  • The % is a wild-card it can match any characters

Find the country that start with Y

SELECT name FROM world
  WHERE name LIKE 'F%'
SELECT name FROM world
  WHERE name LIKE 'Y%'


Find the countries that end with y

SELECT name FROM world
  WHERE name LIKE 'T%'
SELECT name FROM world
  WHERE name LIKE '%y'

Luxembourg has an x - so does one other country. List them both.

Find the countries that contain the letter x

SELECT name FROM world
  WHERE name LIKE 'T%'
SELECT name FROM world
  WHERE name LIKE '%x%'

Iceland, Switzerland end with land - but are there others?

Find the countries that end with land

SELECT name FROM world
  WHERE name LIKE 'T%'
SELECT name FROM world
  WHERE name LIKE '%land'

Columbia starts with a C and ends with ia - there are two more like this.

Find the countries that start with C and end with ia

SELECT name FROM world
  WHERE name LIKE 'T%'
SELECT name FROM world
  WHERE name LIKE 'C%ia'

Greece has a double e - who has a double o?

Find the country that has oo in the name

SELECT name FROM world
  WHERE name LIKE '%ee%'
SELECT name FROM world
  WHERE name LIKE '%oo%'

Bahamas has three a - who else?

Find the countries that have three or more a in the name

SELECT name FROM world
  WHERE name LIKE 'T%'
SELECT name FROM world
  WHERE name LIKE '%a%a%a%'

India and Angola have an n as the second character. You can use the underscore as a single character wildcard.

SELECT name FROM world
 WHERE name LIKE '_n%'
ORDER BY name

Find the countries that have "t" as the second character.

SELECT name FROM world
 WHERE name LIKE '_n%'
ORDER BY name
SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name

Lesotho and Moldova both have two o characters separated by two other characters.

Find the countries that have two "o" characters separated by two others.

SELECT name FROM world
 WHERE name LIKE '_n%'
SELECT name FROM world
 WHERE name LIKE '%o__o%'

Cuba and Togo have four characters names.

Find the countries that have exactly four characters.

SELECT name FROM world
 WHERE name LIKE 'Cu%'
SELECT name FROM world
 WHERE name LIKE '____'

Harder Questions

Well done for getting this far - these questions are optional and only for students who are finding the basic questions too easy.

Show the countries where the capital is the same as the name of the country

Find the countries that have exactly four characters.


SELECT name FROM world
 WHERE name = capital