Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

28 cards

()

Section 1

Preview this deck

NULL

Front

1 / 28

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (28)

Section 1

(28 cards)

NULL

Front

SELECT name FROM movies WHERE imdb_rating IS NOT NULL;

Back

create table

Front

CREATE TABLE celebs (id Integer, name Text, age Integer);

Back

MIN/MAX

Front

SELECT MAX(price) FROM fake_apps;

Back

Union

Front

SELECT * FROM newspaper UNION SELECT * FROM online;

Back

LEFT JOIN

Front

SELECT * FROM newspaper LEFT JOIN online on newspaper.id = online.id WHERE online.id IS NULL;

Back

Comparison operators

Front

=, !=, >, <, >=, <=

Back

delete

Front

DELETE FROM celebs WHERE twitter_handle IS NULL;

Back

JOIN

Front

SELECT * FROM orders JOIN subscriptions ON orders.subscription_id = subscriptions.subscription_id WHERE description = 'Fashion Magazine';

Back

like

Front

SELECT * FROM movies WHERE name LIKE 'Se_en'; (underscore is wildcard)

Back

insert row

Front

INSERT INTO celebs (id, name, age) VALUES (1, 'numb nuts', 21);

Back

HAVING

Front

SELECT price, ROUND(AVG(downloads)) FROM fake_apps GROUP BY price HAVING count(name) > 9;

Back

Case

Front

SELECT name, CASE WHEN genre = 'romance' THEN 'Shit' WHEN genre = 'comedy' THEN 'Probably Shit' ELSE 'Probably Shit' END AS 'Mood' FROM movies; (You get a column named mood, with entries shit, probably shit)

Back

AVG

Front

SELECT AVG(downloads) FROM fake_apps;

Back

ROUND

Front

SELECT ROUND(AVG(price), 2) FROM fake_apps;

Back

like2

Front

SELECT * FROM movies WHERE name LIKE 'The %';

Back

alter table

Front

ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

Back

between

Front

SELECT * FROM movies WHERE name BETWEEN 'D' AND 'G'; (selects all movies that start with letters D , E, F )

Back

where

Front

SELECT * FROM movies WHERE imdb_rating < 5;

Back

distinct

Front

SELECT DISTINCT genre FROM movies;

Back

SUM

Front

SELECT SUM(downloads) FROM fake_apps;

Back

AND

Front

SELECT * FROM movies WHERE year > 1985 AND (genre = 'romance' OR genre = 'comedy');

Back

GROUP BY 2

Front

SELECT category, SUM(downloads) FROM fake_apps GROUP BY category;

Back

order by

Front

SELECT name, year, imdb_rating as 'rating' FROM movies ORDER BY rating DESC;

Back

COUNT

Front

SELECT COUNT(*) FROM fake_apps WHERE price = 0;

Back

GROUP BY

Front

SELECT price, COUNT(*) FROM fake_apps WHERE downloads > 20000 GROUP BY price;

Back

CROSS JOIN

Front

SELECT month, COUNT(*) as subscribers FROM months CROSS JOIN newspaper WHERE month > start_month AND month < end_month GROUP BY month; (shows number of subscribers for each month)

Back

update

Front

UPDATE celebs SET age = 22 WHERE id = 1;

Back

With

Front

WITH previous_results AS (SELECT customer_id, COUNT(subscription_id) as subscriptions FROM orders GROUP BY customer_id) SELECT customers.customer_name,previous_results.subscriptions FROM previous_results JOIN customers ON customers.customer_id = previous_results.customer_id;

Back