SQL:
SELECT *
FROM family_members;
Run SQL
Resultado:
id name gender species num_books_read
1 Dave male human 200
2 Mary female human 180
3 Pickles male dog 0
Lección 2: SELECT specific columns
SELECT * recoge todos los campos (llamados columnas) de una tabla. Si
sólo quisiéramos ver las columnas name y num_book_read, escribiríamos
SELECT name, num_books_read FROM family_members;.
¿Puedes devolver sólo las columnas name y species?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT name, species
FROM family_members;
Run SQL
Resultado:
name species
Dave human
Mary human
Pickles dog
Lección 3: WHERE ... Equals
Para poder seleccionar una fila en particular de esta tabla, utiliza WHERE. Por
ejemplo, si necesitas devolver todas las filas correspondientes a humans,
escribiríamos
SELECT * FROM family_members WHERE species = 'human';
Recuerda que las comillas simples tienen que ir en la palabra human, ya
que es un valor explicíto, no como el comando WHERE.
¿Puedes devolver todas las filas que hacen referencia a dogs?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT *
FROM family_members
WHERE species = 'dog';
Run SQL
Resultado:
id name gender species num_books_read
3 Pickles male dog 0
Lección 4: WHERE ... Greater than
If we want to only select family members based on a numerical field, we
can also use the WHERE keyword. For example, if we wanted to select family
members with a num_books_read, we would type
SELECT * FROM family_members WHERE num_books_read > 0;
Can you run return all rows of family members whose num_books_read is
greater than 190?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT * FROM family_members
WHERE num_books_read > 190;
Run SQL
Resultado:
id name gender species num_books_read
1 Dave male human 200
Lección 5: WHERE ... Greater than or equal
SQL accepts various inequality symbols, including:
= "equal to"
> "greater than"
< "less than"
>= "greater than or equal to"
<= "less than or equal to"
Can you return all rows in family_members where num_books_read is a
value greater or equal to 180?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT*
FROM family_members
WHERE num_books_read >=180
Run SQL
Resultado:
id name gender species num_books_read
1 Dave male human 200
2 Mary female human 180
Lección 6: AND
In the WHERE part of a query, you can search for multiple attributes by using
the AND keyword. For example, if you wanted to find the friends of Pickles
that are over 25cm in height and are cats, you would run:
SELECT * FROM friends_of_pickles WHERE height_cm > 25 AND species = 'cat';
Can you find all of Pickles' friends that are dogs and under the height of
45cm?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT *
FROM friends_of_pickles
WHERE height_cm < 45 AND species = 'do
Run SQL
Resultado:
id name gender species height_cm
5 Odie male dog 40
6 Jumpy male dog 35
Lección 7: OR
In the WHERE part of a query, you can search for rows that match any of
multiple attributes by using the OR keyword. For example, if you wanted to
find the friends of Pickles that are over 25cm in height or are cats, you
would run:
SELECT * FROM friends_of_pickles WHERE height_cm > 25 OR species = 'cat';
Can you find all of Pickles' friends that are dogs or under the height of
45cm?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT *
FROM friends_of_pickles
WHERE height_cm < 45 OR species = 'dog';
Run SQL
Resultado:
id name gender species height_cm
3 Fry male cat 30
4 Leela female cat 25
5 Odie male dog 40
6 Jumpy male dog 35
7 Sneakers male dog 55
Lección 8: IN
Using the WHERE clause, we can find rows where a value is in a list of several
possible values.
SELECT * FROM friends_of_pickles WHERE species IN ('cat', 'human'); would
return the friends_of_pickles that are either a cat or a human.
To find rows that are not in a list, you use NOT IN instead of IN.
Can you run a query that would return the rows that are not cats or dogs?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT *
FROM friends_of_pickles
WHERE species IN ( 'human');
Run SQL
Resultado:
id name gender species height_cm
1 Dave male human 180
2 Mary female human 160
Lección 9: DISTINCT
By putting DISTINCT after SELECT, you do not return duplicates.
For example, if you run
SELECT DISTINCT gender, species FROM friends_of_pickles WHERE height_cm < 100; ,
you will get the gender/species combinations of the animals less than
100cm in height.
Note that even though there are multiple male dogs under that height,
we only see one row that returns "male" and "dog".
Can you return a list of the distinct species of animals greater than 50cm
in height?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT DISTINCT species
FROM friends_of_pickles
WHERE height_cm > 50;
Run SQL
Resultado:
species
human
dog
Lección 10: ORDER BY
If you want to sort the rows by some kind of attribute, you can use
the ORDER BY keyword. For example, if you want to sort
the friends_of_pickles by name, you would run: SELECT * FROM
friends_of_pickles ORDER BY name;. That returns the names in ascending
alphabetical order.
In order to put the names in descending order, you would add a DESC at
the end of the query.
Can you run a query that sorts the friends_of_pickles by height_cm in
descending order?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT *
FROM friends_of_pickles
ORDER BY height_cm DESC ;
Run SQL
Resultado:
id name gender species height_cm
1 Dave male human 180
2 Mary female human 160
7 Sneakers male dog 55
5 Odie male dog 40
6 Jumpy male dog 35
3 Fry male cat 30
4 Leela female cat 25
Lección 11: LIMIT # of returned rows
Often, tables contain millions of rows, and it can take a while to grab
everything. If we just want to see a few examples of the data in a table, we
can select the first few rows with the LIMIT keyword. If you use ORDER BY,
you would get the first rows for that order.
If you wanted to see the two shortest friends_of_pickles, you would
run: SELECT * FROM friends_of_pickles ORDER BY height_cm LIMIT 2;
Can you return the single row (and all columns) of the
tallest friends_of_pickles?
Note:
- Some variants of SQL do not use the LIMIT keyword.
- The LIMIT keyword comes after the DESC keyword.
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT *
FROM friends_of_pickles LIMIT 1;
Run SQL
Resultado:
id name gender species height_cm
1 Dave male human 180
Lección 12: COUNT(*)
Another way to explore a table is to check the number of rows in it. For
example, if we are querying a table states_of_us, we'd expect 50 rows, or
500 rows in a table called fortune_500_companies.
returns the total number of rows in
SELECT COUNT(*) FROM friends_of_pickles;
the table friends_of_pickles. Try this for yourself.
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT COUNT(*)
FROM friends_of_pickles;
Run SQL
Resultado:
COUNT(*)
7
Lección 13: COUNT(*) ... WHERE
We can combine COUNT(*) with WHERE to return the number of rows that
matches the WHERE clause.
For example, SELECT COUNT(*) FROM friends_of_pickles WHERE species =
'human'; returns 2.
Can you return the number of rows in friends_of_pickles where the
species is a dog?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT COUNT(*)
FROM friends_of_pickles
WHERE species = 'dog';
Run SQL
Resultado:
COUNT(*)
3
Lección 14: SUM
We can use the SUM keyword in order to find the sum of a given value.
For example, running SELECT SUM(num_legs) FROM family_members; returns the
total number of legs in the family.
Can you find the total num_books_read made by this family?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT SUM( num_books_read )
FROM family_members;
Run SQL
Resultado:
SUM( num_books_read )
380
Lección 15: AVG
We can use the AVG keyword in order to find the average of a given value.
For example, running SELECT AVG(num_legs) FROM family_members; returns the
average number of legs of each family member.
Can you find the average num_books_read made by each family member?
Note:
- Because of the way computers handle numbers, averages will not always
be completely exact.
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT AVG(num_books_read )
FROM family_members;
Run SQL
Resultado:
AVG(num_books_read )
126.66666666666667
Lección 16: MAX and MIN
We can use the MAX and MIN to find the maximum or minimum value of a
table.
To find the least number of legs in a family member (2), you can run
SELECT MIN(num_legs) FROM family_members;
Can you find the highest num_books_read that a family member makes?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT MAX(num_books_read)
FROM family_members;
Run SQL
Resultado:
MAX(num_books_read)
200
Lección 17: GROUP BY
You can use aggregate functions such as COUNT, SUM, AVG, MAX, and MIN with
the GROUP BY clause.
When you GROUP BY something, you split the table into different piles based
on the value of each row.
For example,
SELECT COUNT(*), species FROM friends_of_pickles GROUP BY species; would
return the number of rows for each species.
Can you return the tallest height for each species? Remember to return
the species name next to the height too, like in the example query.
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT MAX(height_cm), species
FROM friends_of_pickles
GROUP BY species;
Run SQL
Resultado:
MAX(height_cm) species
30 cat
55 dog
180 human
Lección 18: Nested queries
In SQL, you can put a SQL query inside another SQL query.
For example, to find the family members with the least number of legs,
you can run:
SELECT * FROM family_members WHERE num_legs = (SELECT MIN(num_legs) FROM
family_members);
The SELECT query inside the parentheses is executed first, and returns the
minimum number of legs. Then, that value (2) is used in the outside query,
to find all family members that have 2 legs.
Can you return the family members that have the highest
num_books_read?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT *
FROM family_members
WHERE num_books_read = (SELECT MAX
FROM family_members);
Run SQL
Resultado:
id name species num_books_read num_legs
1 Dave human 200 2
Lección 19: NULL
Sometimes, in a given row, there is no value at all for a given column. For
example, a dog does not have a favorite book, so in that case there is no
point in putting a value in the favorite_book column, and the value is NULL.
In order to find the rows where the value for a column is or is not NULL, you
would use IS NULL or IS NOT NULL.
Can you return all of the rows of family_members where favorite_book is
not null?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT*
FROM family_members
WHERE favorite_book IS NOT NULL
Run SQL
Resultado:
id name gender species favorite_book
1 Dave male human To Kill a Mockingbird
2 Mary female human Gone with the Wind
Lección 20: Date
Sometimes, a column can contain a date value. The first 4 digits
represents the year, the next 2 digits represents the month, and the next 2
digits represents the day of the month. For example, 1985-07-20 would
mean July 20, 1985.
You can compare dates by using < and >. For example, SELECT * FROM
celebs_born WHERE birthdate < '1985-08-17'; returns a list of celebrities that
were born before August 17th, 1985.
Can you return a list of celebrities that were born after September 1st,
1980?
Congrats! That is correct!
Haz clic en el botón superior para ir a la siguiente lección
SQL:
SELECT *
FROM celebs_born
WHERE birthdate > '1980-09-01';
Run SQL
Resultado:
id name birthdate
2 Justin Timberlake 1981-01-31
3 Taylor Swift 1989-12-13
