Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
24 Cards in this Set
- Front
- Back
|
How to add a table? |
CREATE TABLE celebs ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER ); |
|
|
How to add a new row? |
INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21); |
|
|
How to read all data from a table? |
SELECT * from celebs; |
|
|
How to modify data in a row? |
UPDATE celebs SET age = 22, name = 'Justin Biba' WHERE id = 1; |
|
|
How to add a new column to a table? |
ALTER TABLE celebs ADD COLUMN twitter_handle TEXT; |
|
|
How to delete a column from a table? |
ALTER TABLE celebs DROP COLUMN age; |
|
|
How to delete a row from a table? |
DELETE FROM celebs WHERE twitter_handle IS NULL; |
|
|
How to select unique values from a table? |
SELECT DISTINCT genre FROM movies; |
|
|
How to select information from all movies with imdb_rating greater than 8? |
SELECT * FROM movies WHERE imdb_rating > 8; |
|
|
How to select all movies that have the pattern Se*en in their names? |
SELECT * FROM movies WHERE name LIKE 'Se_en'; |
|
|
How to select all movies that start with the letter a? |
SELECT * FROM movies WHERE name LIKE 'a%'; |
|
|
How to select all movies that contain the letters man anywhere in their names? |
SELECT * FROM movies WHERE name LIKE '%man%'; |
|
|
How to select all movies that start with letters ranging from a to j?
|
SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';
|
|
|
How to select all movies released between the years 1990 and 2000? |
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000; |
|
|
How to select all movies and sort the results in descending order according to their imdb_rating? |
SELECT * FROM movies ORDER BY imdb_rating DESC; |
|
|
How to select the first 3 movies sorted in ascending order according to their imdb_rating? |
SELECT * FROM movies ORDER BY imdb_rating ASC LIMIT 3; |
|
|
How to count how many rows exist in the fake_apps table? |
SELECT COUNT(*) FROM fake_apps; |
|
|
How to count the number of free apps in the fake_apps table? |
SELECT COUNT(*) FROM fake_apps WHERE price = 0; |
|
|
How to count the number of apps with the same price? |
SELECT price, COUNT(*) FROM fake_apps GROUP BY price; |
|
|
How to count the number of apps with the same price and more than 20000 downloads? |
SELECT price, COUNT(*) FROM fake_apps WHERE downloads > 20000 GROUP BY price; |
|
|
Give examples of aggregate functions |
COUNT(), AVG(), MAX(), MIN(), SUM() |
|
|
Calculate the total number of downloads in the fake_apps table. |
SELECT SUM(downloads) FROM fake_apps;
|
|
|
Calculate the total number of downloaded apps for each category.
|
SELECT category, SUM(downloads) FROM fake_apps GROUP BY category; |
|
|
How many times was downloaded the mos popular app? |
SELECT MAX(downloads) FROM fake_apps; |
