Skip to content

miroslav-reiter/SQL_Zoo

BranchesTags

Folders and files

NameName
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

 
 

Repository files navigation

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:

  1. SELECT basics
  2. SELECT name
  3. SELECT from WORLD
  4. SELECT from NOBEL
  5. SELECT in SELECT
  6. SUM and COUNT
  7. JOIN
  8. 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

About

Kurzy SQL, MySQL, PostgreSQL, Microsoft SQL Server

www.it-academy.sk/kategoria/databazy/kurzy-sql/

Topics

mysql postgres data sql sql-server database analytics analysis postgresql sql-query mysql-database data-analysis sql-injection db2 postgresql-database sqlserver ibm-db2

Resources

Readme
Activity

Stars

2 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published