sql playground
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

184 lines
4.6 KiB

-- in class examples
-- dataset is employee(name)
-- first, always see the data you are working with
SELECT * FROM employee;
-- then, see the size of the data
SELECT COUNT(*) FROM employee;
-- dataset is nobel(yr, subject, winner) (where winner is name)
-- q1 show winners of the nobel prize in 1950
SELECT yr, subject, winner FROM nobel
WHERE yr = 1950;
-- q2 show 1962 Literature prize winners
SELECT winner
FROM nobel
WHERE yr = 1950winner, subject, year
AND subject = 'Literature';
-- q3 find nobel prizes that Albert Einstein won
SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein';
-- q4 Peace winners since 2000 inclusively
SELECT winner
FROM nobel
WHERE yr >= 2000
AND subject = 'Peace';
-- Good idea to show the criteria you are filtering on
SELECT winner, yr
FROM nobel
WHERE yr >= 2000
AND subject = 'Peace';
-- q5 Show all details (year, subject winner) of winners from 1980 to 1989 of he Literature prize
SELECT *
FROM nobel
WHERE yr >= 1980
AND yr <= 1989
AND subject = 'Literature';
-- use something other than two ands - LIKE works
SELECT *
FROM nobel
WHERE yr LIKE '198%'
AND subject = 'Literature';
-- and we can also try this:
SELECT *
FROM nobel
WHERE yr BETWEEN 1980 AND 1989 -- this is inclusively specified
AND subject = 'Literature';
-- q6 show the details of these winners
SELECT *
FROM nobel
WHERE winner = 'Theodore Roosevelt'
OR winner = 'Woodrow Wilson'
OR winner = 'Jimmy Carter'
OR winner = 'Barack Obama';
-- better way, where winner IN (list of items)
SELECT *
FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama');
-- q7 Show the winners with the first name 'John'
SELECT winner
FROM nobel
WHERE winner LIKE 'John %';
-- q8 show the year, subject, and name of Physics winners for 1980 and the wnners of Chemistry for 1984
SELECT *
FROM nobel
WHERE (subject = 'Physics' AND yr = 1980)
OR (subject = 'Chemistry' AND yr = 1984)
-- another one I came up with
SELECT *
FROM nobel
WHERE yr = 1980
AND subject = 'Physics';
UNION
SELECT *
FROM nobel
WHERE yr = 1984
AND subject = 'Chemistry';
-- q9 Show all information about 1980 excluding Chemistry and Medicine
SELECT yr, subject, winner
FROM nobel
WHERE subject <> 'Chemistry' -- not equal is <>
AND subject <> 'Medicine'
AND yr = 1980;
-- another one I came up with
SELECT *
FROM nobel
WHERE yr = 1980 AND subject NOT IN ('Chemistry', 'Medicine');
-- another one he did
SELECT *
FROM nobel
WHERE subject NOT IN ('Chemistry', 'Medicine')
AND yr = 1980;
-- q10 Show the info for the 'Medicine' prize before 1910, and Literature after 2004 inclusively
SELECT *
FROM nobel
WHERE (subject = 'Literature' AND yr >= 2004)
OR (subject = 'Medicine' AND yr < 1910);
-- q11 Find all of the prizes by Peter Grünberg
SELECT *
FROM nobel
WHERE name = 'Peter Grünberg';
-- he didn't expect this to work, lol. I think this newer database engine uses UTF-8 encoding for the data, and thus there is no problem there
-- sad excuse for LIKE demonstrated here. He does mention it's bad to do this
SELECT *
FROM nobel
WHERE name LIKE 'Peter Gr%nberg';
-- can also use CHAR(252) to show ü. Not sure why you would need to do this since you can type this particular character, but I guess if your name has newlines in it that this would matter.
SELECT *
FROM nobel
WHERE winner = 'Peter Gr' || CHAR(252) || 'nberg';
-- q12 find this other guy
SELECT *
FROM nobel
WHERE winner = 'Eugene O''Niell';
-- q13 find winners that have names that start with 'Sir', and in the order name, year, subject
SELECT winner, year, subject
FROM nobel
WHERE winner LIKE 'Sir %'
ORDER BY yr DESC, winner;
-- dataset is now world(name, continent, area, population, gdp)
-- q1 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'
);
-- note - you can only expect one result to be returned from the nested query, otherwise it will fail. For example, returning all countries numbers that have an R to start the name of the country will fail.
-- could have also done the population straight.
SELECT name
FROM world
WHERE population > 1460000000;
-- "cheating"
-- q2 show the countries with a per capita GDP greater than the UK
SELECT name
FROM world
WHERE continent = 'Europe'
AND gdp/population > (
SELECT gdp/population AS percapitagdp
FROM world
WHERE name = 'United Kingdom'
);
-- q3 List the names and continent of countries in the continents containng either Argentena or Australia, and order by country
SELECT name, continent
FROM world
WHERE continent IN (
SELECT continent
FROM world
WHERE name IN ('Argentena', 'Australia')
)
ORDER BY name;