Kurzy SQL, MySQL, PostgreSQL, Microsoft SQL Server

Kurzy SQL sú koncipované tak, aby ťa doviedli od teórie databáz k praxi. Osvojíš si základné koncepcie a princípy jazyka SQL. Naučíme ťa, ako definovať databázové objekty a ako pracovať so základnými príkazmi a ďalšími funkciami. Už ako začiatočník zvládneš tvorbu jednoduchších dotazov. Pre pokročilých je pripravený kurz, v rámci ktorého ťa zasvätíme do jazyka DML, DCL a osvojíš si tvorbu zložitejších dotazov. Bez problémov zvládneš správu dát, zabezpečenie a implementáciu.

Vyriešené príklady SQL Zoo | SQLZOO | Cheat mode

Sekcie:

SELECT basics
SELECT name
SELECT from WORLD
SELECT from NOBEL
SELECT in SELECT
SUM and COUNT
JOIN
More JOIN
8a. Using NULL
8b. Numeric Examples
9a. Self JOIN
9b. Window function

SELECT basics

Niekoľko jednoduchých dopytov, ktoré ti pomôžu začať

  SELECT population FROM world
    WHERE name = 'Germany'

  SELECT name, gdp/population FROM world
    WHERE area > 5000000

  SELECT name, population FROM world
    WHERE name IN ('Ireland','Iceland','Denmark');

  SELECT name, area FROM world
    WHERE area BETWEEN 200000 AND 250000

SELECT name

Niekoľko dopytov k vyhľadávanie podľa vzorov/pattern matching

SELECT from WORLD

Dopytovanie v tabuľke svet/world

SELECT name, continent, population FROM world

SELECT name FROM world
WHERE population>200000000

SELECT name, gdp/population FROM world
  WHERE population > 200000000

SELECT name, population/1000000 FROM world
  WHERE continent = 'South America'

SELECT name,population FROM world
  WHERE name IN ('France','Germany','Italy')

SELECT name FROM world
  WHERE name LIKE '%United%'

select name, population, area from world
  where population > 250000000 or area > 3000000

select name, population, area from world
  where population > 250000000 xor area > 3000000

select name, ROUND(population/1000000,2), ROUND(gdp/1000000000,2) from world
  where continent = 'South America'

select name, ROUND(gdp/population,-3) from world
  where gdp > 1000000000000

Ťažšie otázky:

SELECT name,
       CASE WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
  FROM world
 WHERE name LIKE 'N%'

SELECT name,
       CASE WHEN continent='Europe' or continent='Asia' THEN 'Eurasia'
            WHEN continent in ('North America','South America','Caribbean') THEN 'America'   
            ELSE continent END
  FROM world
 WHERE name LIKE 'A%' or name LIKE 'B%'

SELECT name, continent, CASE
                     WHEN continent = 'Oceania' THEN 'Australasia'
                     WHEN continent = 'Eurasia' THEN 'Europe/Asia'
                     WHEN name = 'Turkey' THEN 'Europe/Asia'
    WHEN continent = 'Caribbean' AND name LIKE 'B%' then 'North America'
    WHEN continent = 'Caribbean' THEN 'South America'    
                 ELSE continent END
FROM world ORDER BY name

SELECT from NOBEL

Dodatočné precvičenie základných príkazov a funkcií pomocou tabuľky nositeľov Nobelovej ceny 1.

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'

SELECT yr, subject
  FROM nobel
WHERE winner = 'Albert Einstein'

SELECT winner
FROM nobel
WHERE subject = 'Peace' AND yr >= 2000

SELECT yr, subject, winner
  FROM nobel
WHERE subject = 'Literature' AND yr BETWEEN 1980 AND 1989
(in MySQL BETWEEN is inclusive.. this is not always the case)

SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt',
                  'Woodrow Wilson',
                  'Jimmy Carter')

SELECT winner FROM nobel
  WHERE winner LIKE 'JOHN %'

SELECT * FROM nobel
  WHERE yr = 1980 AND subject = 'Physics'
     OR yr = 1984 AND subject = 'Chemistry'

SELECT * FROM nobel
  WHERE yr = 1980
    AND subject NOT IN ('Chemistry','Medicine')

SELECT * FROM nobel
  WHERE yr < 1910 AND subject = 'Medicine'
     OR yr >= 2004 AND subject = 'Literature'

Ťažšie otázky

SELECT * FROM nobel
  WHERE winner = 'Peter Grünberg'

SELECT * FROM nobel
  WHERE winner = 'Eugene O''Neill'

SELECT winner, yr, subject FROM nobel
  WHERE winner LIKE 'Sir %'
  ORDER BY yr DESC, winner

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject in ('Chemistry','Physics'), subject, winner

SELECT in SELECT

Príklady, v ktorých tvoríme dopyty pomocou iných dopytov 1.

SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Russia')

SELECT name FROM world
  WHERE continent = 'Europe'
    AND gdp/population > (SELECT gdp/population
                            FROM world
                           WHERE name = 'United Kingdom')

SELECT name, continent FROM world
  WHERE continent IN (SELECT continent FROM world
                        WHERE name IN ('Argentina','Australia'))
  ORDER BY name

SELECT name, population FROM world
  WHERE population > (SELECT population FROM world
                        WHERE name = 'Canada')
    AND population < (SELECT population FROM world
                        WHERE name = 'Poland')

SELECT name, CONCAT(ROUND(population/(SELECT population FROM world
                          WHERE name = 'Germany')*100,0),'%')
             FROM world WHERE continent = 'Europe'

SELECT name FROM world
  WHERE gdp > ALL(SELECT gdp FROM world
                   WHERE gdp > 0 AND continent = 'Europe')

SELECT continent, name, area FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0)

SELECT continent, name FROM world x
  WHERE name <= ALL
    (SELECT name FROM world y
        WHERE y.continent=x.continent)

Ťažšie otázky

SELECT name, continent, population FROM world x
  WHERE 25000000 >= ALL(SELECT population
	                FROM world y
		        WHERE x.continent = y.continent
                        AND y.population>0);

SELECT name, continent FROM world x
  WHERE population >= ALL(SELECT population*3
                         FROM world y
                         WHERE x.continent = y.continent
                         and y.name != x.name)

SUM and COUNT

Príklady na agregované funkcie, distinct, order by, group by, having

SELECT SUM(population)
FROM world

SELECT DISTINCT continent FROM world

SELECT SUM(gdp) FROM world
  WHERE continent = 'Africa'

SELECT COUNT(name) FROM world
  WHERE area >= 1000000

SELECT SUM(population) FROM world
  WHERE name IN ('France','Germany','Spain')

SELECT continent, COUNT(name) FROM world
GROUP BY continent

SELECT continent, COUNT(name) FROM world
  WHERE population > 10000000
  GROUP BY continent

SELECT continent FROM world
  GROUP BY continent
  HAVING SUM(population) > 100000000

JOIN

Príklady na spájanie viacerých tabuliek hra, góly

SELECT matchid, player FROM goal
  WHERE teamid = 'GER'

SELECT id,stadium,team1,team2
  FROM game
  WHERE id = 1012

SELECT player, teamid, stadium, mdate
  FROM game JOIN goal ON (id=matchid)
  WHERE teamid = 'GER'

SELECT team1, team2, player FROM game
  JOIN goal ON (id=matchid)
  WHERE player LIKE 'Mario%'

SELECT player, teamid, coach, gtime
  FROM goal
  JOIN eteam on (teamid=id)
 WHERE gtime<=10

SELECT mdate,teamname FROM game
  JOIN eteam ON (team1 = eteam.id)
  WHERE coach = 'Fernando Santos'

SELECT player FROM goal
  JOIN game ON (matchid = id)
  WHERE stadium = 'National Stadium, Warsaw'

Ťažšie otázky

SELECT DISTINCT player
  FROM game JOIN goal ON matchid = id
    WHERE (team1= 'GER' OR team2='GER')
    AND teamid != 'GER'

SELECT teamname, COUNT(*)
  FROM eteam JOIN goal ON id=teamid
 GROUP BY teamname

SELECT stadium, COUNT(*) FROM goal
  JOIN game ON (matchid = id)
  GROUP BY stadium

SELECT matchid, mdate, COUNT(*)
  FROM game JOIN goal ON matchid = id
  WHERE (team1 = 'POL' OR team2 = 'POL')
  GROUP BY mdate,matchid

SELECT matchid, mdate, COUNT(*) FROM goal
  JOIN game ON (matchid=id)
  WHERE teamid = 'GER'
  GROUP BY matchid, mdate

SELECT DISTINCT mdate, team1,
	SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
    team2,
    SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
FROM game
LEFT JOIN goal ON game.id = goal.matchid
GROUP BY id, mdate, team1, team2
ORDER BY mdate, matchid, team1, team2

More JOIN

Príklady na spájanie viacerých tabuliek herci, filmy, obsadenie

SELECT id, title
 FROM movie
 WHERE yr=1962

SELECT yr
  FROM movie
  WHERE title = 'Citizen Kane'

SELECT id, title, yr FROM movie
  WHERE title LIKE '%Star Trek%'
  ORDER BY yr

SELECT title FROM movie
  WHERE id IN (11768, 11955, 21191)

SELECT id FROM actor
  WHERE name = 'Glenn Close'

SELECT id FROM movie
  WHERE title = 'Casablanca'

SELECT name FROM casting JOIN actor ON (id=actorid)
  WHERE movieid=11768

SELECT name FROM casting
  JOIN actor ON (actor.id=actorid)
  JOIN movie ON (movie.id=movieid)
  WHERE title = 'Alien'

SELECT title FROM casting
  JOIN movie ON (movie.id = movieid)
  JOIN actor ON (actor.id = actorid)
  WHERE name = 'Harrison Ford'

SELECT title FROM casting
  JOIN movie ON (movie.id = movieid)
  JOIN actor ON (actor.id = actorid)
  WHERE name = 'Harrison Ford'  AND ord > 1

SELECT title, name FROM casting
  JOIN movie ON (movie.id = movieid)
  JOIN actor ON (actor.id = actorid)
  WHERE yr = 1962 and ord = 1

Ťažšie otázky

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 WHERE name='John Travolta'
 GROUP BY yr) AS t
)

SELECT title, name FROM casting
  JOIN movie ON movie.id = movieid
  JOIN actor ON actor.id = actorid
WHERE ord = 1
	AND movie.id IN
	(SELECT movie.id FROM movie
	   JOIN casting ON movie.id = movieid
	   JOIN actor ON actor.id = actorid
           WHERE actor.name = 'Julie Andrews')

SELECT DISTINCT name FROM casting
  JOIN movie ON movie.id = movieid
  JOIN actor ON actor.id = actorid
  WHERE actorid IN (
	SELECT actorid FROM casting
	  WHERE ord = 1
	  GROUP BY actorid
	  HAVING COUNT(actorid) >= 30)
ORDER BY name

SELECT title, COUNT(actorid) FROM casting
  JOIN movie ON movieid = movie.id
  WHERE yr = 1978
  GROUP BY movieid, title
  ORDER BY COUNT(actorid) DESC

SELECT DISTINCT name FROM casting
  JOIN actor ON actorid = actor.id
  WHERE name != 'Art Garfunkel'
	AND movieid IN (
		SELECT movieid
		FROM movie
		JOIN casting ON movieid = movie.id
		JOIN actor ON actorid = actor.id
		WHERE actor.name = 'Art Garfunkel'
)

Using NULL

Používanie stavu NULL, tabuľky učitelia, oddelenia

SELECT name FROM teacher
  WHERE dept IS NULL

SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id)

SELECT teacher.name, dept.name
 FROM teacher LEFT JOIN dept
           ON (teacher.dept=dept.id)

SELECT teacher.name, dept.name
 FROM teacher RIGHT JOIN dept
           ON (teacher.dept=dept.id)

SELECT teacher.name, COALESCE(teacher.mobile,'07986 444 2266')  FROM teacher

SELECT teacher.name, COALESCE(dept.name,'None') FROM teacher
  LEFT JOIN dept ON teacher.dept = dept.id

SELECT COUNT(name), COUNT(mobile) FROM teacher

SELECT dept.name, COUNT(teacher.dept) FROM teacher
  RIGHT JOIN dept ON dept.id = teacher.dept
  GROUP BY dept.name

SELECT name, CASE WHEN dept IN (1,2) THEN 'Sci'
                  ELSE 'Art'
                  END
                  FROM teacher

SELECT name, CASE WHEN dept IN (1,2) THEN 'Sci'
                  WHEN dept = 3 THEN 'Art'
                  ELSE 'None'
                  END
                  FROM teacher

Self JOIN

Príklady na spájanie tabuliek autobusové zastávky, cesty

SELECT DISTINCT COUNT(*) FROM stops

SELECT id FROM stops
  WHERE name = 'Craiglockhart'

SELECT id, name FROM stops JOIN route ON (stops.id = route.stop)
  WHERE num = 4

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*) = 2

SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 AND b.stop = (SELECT id FROM stops WHERE name = 'London Road')

SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' and stopb.name = 'London Road'

SELECT a.company, a.num  
FROM route a, route b
WHERE a.num = b.num AND (a.stop = 115 AND b.stop = 137)
GROUP BY num;

SELECT a.company, a.num
FROM route a
JOIN route b ON (a.company = b.company AND a.num = b.num)
JOIN stops stopa ON a.stop = stopa.id
JOIN stops stopb ON b.stop = stopb.id
WHERE stopa.name = 'Craiglockhart'
AND stopb.name = 'Tollcross';

SELECT DISTINCT name, a.company, a.num
FROM route a
JOIN route b ON (a.company = b.company AND a.num = b.num)
JOIN stops ON a.stop = stops.id
WHERE b.stop = 53;

SELECT a.num, a.company, stopb.name, c.num, c.company
FROM route a
JOIN route b ON (a.company = b.company AND a.num = b.num)
JOIN (route c JOIN route d ON (c.company = d.company AND c.num = d.num))
JOIN stops stopa ON a.stop = stopa.id
JOIN stops stopb ON b.stop = stopb.id
JOIN stops stopc ON c.stop = stopc.id
JOIN stops stopd ON d.stop = stopd.id
WHERE stopa.name = 'Craiglockhart'
	AND stopd.name = 'Sighthill'
	AND stopb.name = stopc.name
ORDER BY LENGTH(a.num), b.num, stopb.name, LENGTH(c.num), d.num;

Window function

Skúmame výsledky všeobecných volieb vo Veľkej Británii

Name		Name	Last commit message	Last commit date
Latest commit History 18 Commits
MySQL SQL Injection Cheat Sheet.sql		MySQL SQL Injection Cheat Sheet.sql
README.md		README.md

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Kurzy SQL, MySQL, PostgreSQL, Microsoft SQL Server

Vyriešené príklady SQL Zoo | SQLZOO | Cheat mode

Sekcie:

SELECT basics

SELECT name

SELECT from WORLD

Ťažšie otázky:

SELECT from NOBEL

Ťažšie otázky

SELECT in SELECT

Ťažšie otázky

SUM and COUNT

JOIN

Ťažšie otázky

More JOIN

Ťažšie otázky

Using NULL

Self JOIN

Window function

About

Releases

Packages

miroslav-reiter/SQL_Zoo

Folders and files

Latest commit

History

Repository files navigation

Kurzy SQL, MySQL, PostgreSQL, Microsoft SQL Server

Vyriešené príklady SQL Zoo | SQLZOO | Cheat mode

Sekcie:

SELECT basics

SELECT name

SELECT from WORLD

Ťažšie otázky:

SELECT from NOBEL

Ťažšie otázky

SELECT in SELECT

Ťažšie otázky

SUM and COUNT

JOIN

Ťažšie otázky

More JOIN

Ťažšie otázky

Using NULL

Self JOIN

Window function

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages 0

Packages