Tracy785的博客

Pattern Matching StringsThis tutorial uses the LIKE operator to check names. We will be using the SELECT command on the table world:You can use WHERE name LIKE ‘B%’ to find the countries that star...

1. List the films where the yr is 1962 [Show id, title]SELECT id, title FROM movie WHERE yr = 1962;2. Give year of 'Citizen Kane’SELECT yr FROM movie WHERE title = 'Citizen Kane';3. List al...

1. Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget)SELECT name FROM actor INNER JOIN movie ON actor.id = director WHER...

1. List the teachers who have NULL for their departmentselect name from teacherwhere dept IS NULL;2. Note the INNER JOIN misses the teachers with no department and the departments with no teache...

1. Select the code which uses an outer join correctly SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept = dept.id)2. Select the correct statement that shows the nam...

1. One MSP was kicked out of the Labour party and has no party. Find himselect name from mspwhere party is null;2. Obtain a list of all parties and leadersselect name,leader from party;3. Give...

The example uses a WHERE clause to show the population of ‘France’. Note that strings (pieces of text that are data) should be in ‘single quotes’;Modify it to show the population of GermanySELECT...

Some questions concerning basic SQL statementsSelect the code which produces this tableSELECT name, population FROM world WHERE population BETWEEN 1000000 AND 1250000Pick the result you wo...

1. The example shows the number who responded for:question 1at ‘Edinburgh Napier University’studying ‘(8) Computer Science’Show the the percentage who STRONGLY AGREESELECT A_STRONGLY_AGREE FRO...

1. How many stops are in the databaseselect count(id) from stops;2. Find the id value for the stop 'Craiglockhart’select id from stopswhere name = 'Craiglockhart';3. Give the id and the name f...

1.Show the athelete (who) and the country name for medal winners in 2000SELECT who,nameFROM ttms JOIN country ON country = idWHERE games = 2000;2.Show the who and the color of the medal for th...

1. You want to find the stadium where player ‘Dimitris Salpingidis’ scored. Select the JOIN condition to use: game JOIN goal ON (id=matchid)2. You JOIN the tables goal and eteam in an SQL stateme...

1. Modify it to show the matchid and player name for all goals scored by GermanySELECT matchid,player FROM goal WHERE teamid = 'GER';2. Show id, stadium, team1, team2 for just game 1012SELECT ...

In this tutorial you will use the SELECT command on the table world:Read the notes about this table. Observe the result of running this SQL command to show the name, continent and population of all...

Select the code which gives the name of countries beginning with USELECT name FROM world WHERE name LIKE 'U%'Select the code which shows just the population of United Kingdom?SELECT popula...

1. Change the query shown so that it displays Nobel prizes for 1950SELECT * FROM nobelWHERE yr = 1950;2. Show who won the 1962 prize for LiteratureSELECT winner FROM nobelWHERE yr = 1962AND su...

1. Pick the code which shows the name of winner’s names beginning with C and ending in nSELECT winner FROM nobel WHERE winner LIKE 'C%' AND winner LIKE '%n'2. Select the code that shows how many ...

1. List each country name where the population is larger than that of 'Russia’SELECT name FROM world WHERE population > (SELECT population FROM world WHERE name='Russia');2. Show t...

1. Select the code that shows the name, region and population of the smallest country in each region SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc...

1. Show the total population of the world.SELECT SUM(population) FROM world;2. List all the continents - just once eachselect distinct continent from world;3. Give the total GDP of Africaselec...

1. Select the statement that shows the sum of population of all countries in 'Europe’SELECT SUM(population) FROM bbc WHERE region = 'Europe'2. Select the statement that shows the number of countri...

1. Show the total number of prizes awardedSELECT COUNT(winner) FROM nobel;2. List each subject - just onceselect distinct subject from nobel;3. Show the total number of prizes awarded for Physic...

1. Select the code that would show it is possible to get from Craiglockhart to HaymarketSELECT DISTINCT a.name, b.name FROM stops a JOIN route z ON a.id=z.stop JOIN route y ON y.num = z.num JO...