SQL Básico: Consultas e Junções

Prévia do material em texto

<p>SQL Basics Cheat Sheet LearnSOL com SQL FILTERING THE OUTPUT QUERYING MULTIPLE TABLES SQL, or Structured Query Language, is a language to talk to COMPARISON OPERATORS INNER JOIN FULL JOIN databases. It allows you to select specific data and to build Fetch names of cities that have a rating above 3: complex reports. Today, SQL is a universal language of data. It is JOIN (or explicitly INNER JOIN) returns rows that have FULL JOIN (or explicitly FULL OUTER JOIN) returns all rows matching values in both tables. from both tables if there's no matching row in the second used in practically all technologies that process data. SELECT name table, NULLS are returned. FROM city SAMPLE DATA WHERE rating > 3; SELECT city.name, country.name SELECT city.name, country.name FROM city FROM city COUNTRY [INNER] JOIN country FULL [OUTER] JOIN country id name population area Fetch names of cities that are neither Berlin non Madrid: ON city.country_id = country.id; ON city.country_id = country. id; 1 France 66600000 640680 2 Germany 80700000 357000 CITY COUNTRY SELECT name CITY COUNTRY id name id id name FROM city id name id id name 1 Paris 1 1 France CITY WHERE name != 'Berlin' 1 Paris 1 1 France 2 Berlin 2 2 Germany id name country population rating AND name != 'Madrid'; 2 Berlin 2 2 Germany 3 Warsaw 4 NULL NULL 1 Pari 1 2243000 5 3 Warsaw 4 3 Iceland NULL NULL NULL 3 Iceland 2 Berlin 2 3460000 3 TEXT OPERATORS Fetch names of cities that start with a 'P' or end with an 's': QUERYING SINGLE TABLE SELECT name LEFT JOIN CROSS JOIN Fetch all columns from the country table: FROM city LEFT JOIN returns all rows from the left table with CROSS JOIN returns all possible combinations of rows from SELECT * WHERE name LIKE 'P%' corresponding rows from the right table. If there's no both tables. There are two syntaxes available. FROM country; OR name LIKE matching row, NULLS are returned as values from the second SELECT city. .name, country.name table. FROM city Fetch id and name columns from the city table: SELECT city.name, country.nam CROSS JOIN country; SELECT id, name Fetch names of cities that start with any letter followed by FROM city FROM city; 'ublin' (like Dublin in Ireland or Lublin in Poland): SELECT city.name, country.name LEFT JOIN country FROM city, country; Fetch city names sorted by the column SELECT name ON city.country_id = country.id CITY COUNTRY in the default ASCending order: FROM city CITY COUNTRY id name id id name WHERE name LIKE id name country_id id name 1 Paris 1 1 France SELECT name 1 Paris 1 1 France 1 Paris 1 2 Germany FROM city 2 Berlin 2 2 Germany 2 Berlin 2 1 France ORDER BY rating [ASC]; OTHER OPERATORS 3 Warsaw 4 NULL NULL 2 Berlin 2 2 Germany Fetch city names sorted by the rating column Fetch names of cities that have a population between in the SCending order: 500K and 5M: SELECT name SELECT name FROM city FROM city RIGHT JOIN NATURAL JOIN ORDER BY rating DESC; WHERE population BETWEEN 500000 AND 5000000; RIGHT JOIN returns all rows from the right table with NATURAL JOIN will join tables by all columns with the same name. corresponding rows from the left table. If there's no ALIASES matching row, NULLS are returned as values from the left SELECT city.name country.name Fetch names of cities that don't miss a rating value: table. FROM city COLUMNS SELECT name NATURAL JOIN country; SELECT city.name, country.name SELECT name AS city_name FROM city FROM city CITY COUNTRY FROM city; WHERE rating IS NOT NULL; country_ id name name id RIGHT JOIN country 6 6 San Marino San Marino 6 TABLES ON city.country_id = 7 7 Vatican City Vatican City 7 SELECT co.name, ci.name Fetch names of cities that are in countries with IDs 1, 4, 7, or 8: 5 9 Greece Greece 9 CITY COUNTRY 10 11 Monaco Monaco 10 FROM city AS ci id SELECT name name country_id id name NATURAL JOIN used these columns to match rows: 1 Paris 1 1 France JOIN country AS CO FROM city 2 Berlin 2 2 Germany city.id, city.name, country.id country. name ON ci.country_id = co.id; WHERE country_id IN (1, 4, 7, 8) NULL NULL NULL 3 Iceland NATURAL JOIN is very rarely used in practice. LearnSQL.com is owned by Vertabelo SA Try out the interactive SQL Basics course at LearnSQL.com, and check out our other SQL courses. vertabelo.com CC BY-NC-ND Vertabelo SA</p>