sql part 2

Run to view results

SELECT COUNT(birthdate) AS count_birthdates FROM people;

Run to view results

SELECT COUNT(name) AS count_name, COUNT(birthdate) AS count_birthdates FROM people;

Run to view results

SELECT COUNT(*) AS total_records FROM people;

Run to view results

SELECT language FROM films;

Run to view results

SELECT DISTINCT language FROM films;

Run to view results

SELECT COUNT(DISTINCT birthdate) AS count_distinct_birthdates FROM people;

Run to view results

SELECT COUNT(film_id) AS count_film_id FROM reviews;

Run to view results

SELECT COUNT(*) AS count_records FROM people;

Run to view results

SELECT COUNT(birthdate) AS count_birthdate FROM people;

Run to view results

SELECT COUNT(language) AS count_languages, COUNT(country) AS count_countries FROM films;

Run to view results

SELECT DISTINCT(country) FROM films;

Run to view results

SELECT COUNT(DISTINCT(country)) AS count_distinct_countries FROM films;

Run to view results

SELECT name FROM people LIMIT 10;

Run to view results

SELECT nme FROM people;

Run to view results

SELECT title, country. duration FROM films;

Run to view results

SELCT title, country, duration FROM films;

Run to view results

SELECT certification FROM films LIMIT 5;

Run to view results

SELECT film_id, imdb_score, num_votes FROM reviews;

Run to view results

SELECT COUNT(birthdate) AS count_birthdays FROM people;

Run to view results

select title, release_year, country from films limit 3

Run to view results

SELECT title, release_year, country FROM films LIMIT 3;

Run to view results

SELECT title, release_year, country FROM films LIMIT 3;

Run to view results

SELECT title, release_year, country FROM films LIMIT 3;

Run to view results

SELECT title, "release year", country FROM films LIMIT 3;

SELECT person_id, role FROM roles LIMIT 10;

Run to view results

SELECT title FROM films WHERE release_year > 1960;

Run to view results

SELECT title FROM films WHERE release_year < 1960;

Run to view results

SELECT title FROM films WHERE release_year <= 1960;

Run to view results

SELECT title FROM films WHERE release_year = 1960;

Run to view results

SELECT title FROM films WHERE release_year <> 1960;

Run to view results

SELECT title FROM films WHERE country = 'Japan';

Run to view results

SELECT item FROM coats WHERE color = 'green' LIMIT 5;

SELECT film_id, imdb_score FROM reviews WHERE imdb_score > 7.0;

Run to view results

SELECT film_id, facebook_likes FROM reviews WHERE facebook_likes < 1000 LIMIT 10;

Run to view results

SELECT count(*) AS films_over_100K_votes FROM reviews WHERE num_votes >= 100000;

Run to view results

SELECT COUNT(language) AS count_spanish FROM films WHERE language = 'Spanish';

Run to view results

SELECT * FROM coats WHERE color = 'yellow' OR length = 'short';

SELECT * FROM coats WHERE color = 'yellow' AND length = 'short';

SELECT * FROM coats WHERE buttons BETWEEN 1 AND 5;

Run to view results

SELECT title FROM films WHERE release_year = 1994 OR release_year = 2000;

Run to view results

SELECT title FROM films WHERE release_year = 1994 OR 2000;

Run to view results

SELECT title FROM films WHERE release_year > 1994 AND release_year < 2000;

Run to view results

SELECT title FROM films WHERE release_year > 1994 AND < 2000;

Run to view results

SELECT title FROM films WHERE (release_year = 1994 OR release_year = 1995) AND (certification = 'PG' OR certification = 'R');

Run to view results

SELECT title FROM films WHERE release_year >= 1994 AND release_year <= 2000;

Run to view results

SELECT title FROM films WHERE release_year BETWEEN 1994 AND 2000;

Run to view results

SELECT title FROM films WHERE release_year BETWEEN 1994 AND 2000 AND country='UK';

Run to view results

SELECT title, release_year FROM films WHERE language = 'German' AND release_year < 2000;

Run to view results

SELECT title, release_year FROM films WHERE release_year > 2000 AND language = 'German';

Run to view results

SELECT * FROM films WHERE language = 'German' AND release_year > 2000 AND release_year < 2010;

Run to view results

SELECT title, release_year FROM films WHERE release_year = 1990 OR release_year = 1999;

Run to view results

SELECT title, release_year FROM films WHERE (release_year = 1990 OR release_year = 1999) AND (language = 'English' OR language = 'Spanish');

Run to view results

SELECT title, release_year FROM films WHERE (release_year = 1990 OR release_year = 1999) AND (language = 'English' OR language = 'Spanish') AND (gross > 2000000);

Run to view results

SELECT title, release_year FROM films WHERE release_year BETWEEN 1990 AND 2000;

Run to view results

SELECT title, release_year FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget > 100000000;

Run to view results

SELECT title, release_year FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget > 100000000 AND language = 'Spanish';

Run to view results

SELECT title, release_year FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget > 100000000 AND (language = 'Spanish' OR language = 'French');

Run to view results

SELECT title FROM films WHERE country = 'Japan';

Run to view results

SELECT name FROM people WHERE name LIKE 'Ade%';

Run to view results

SELECT name FROM people WHERE name LIKE 'Ev_';

Run to view results

SELECT name FROM people;

Run to view results

SELECT name FROM people WHERE name NOT LIKE 'A.%';

Run to view results

SELECT name FROM people WHERE name LIKE '%r';

Run to view results

SELECT name FROM people WHERE name LIKE '__t%';

Run to view results

SELECT title FROM films WHERE release_year = 1920 OR release_year = 1930 OR release_year = 1940;

Run to view results

SELECT title FROM films WHERE release_year IN (1920, 1930, 1940);

Run to view results

SELECT title FROM films WHERE country IN ('Germany', 'France');

Run to view results

SELECT name FROM people WHERE name LIKE 'B%';

Run to view results

SELECT name FROM people WHERE name LIKE '_r%';

Run to view results

SELECT name FROM people WHERE name NOT LIKE 'A%';

Run to view results

SELECT title, release_year FROM films WHERE release_year IN (1990, 2000) AND duration > 120;

Run to view results

SELECT title, language FROM films WHERE language IN ('English', 'Spanish', 'French');

Run to view results

SELECT title, certification, language FROM films WHERE certification IN ('NC-17', 'R') AND language IN ('English', 'Italian', 'Greek');

Run to view results

SELECT COUNT(DISTINCT(title)) AS nineties_english_films_for_teens FROM films WHERE release_year BETWEEN 1990 AND 1999 AND language = 'English' AND certification IN ('G', 'PG', 'PG-13');

Run to view results

SELECT COUNT(*) AS count_records FROM people;

Run to view results

SELECT * FROM people;

Run to view results

SELECT name FROM people WHERE birthdate IS NULL;

Run to view results

SELECT COUNT(*) AS no_birthdates FROM people WHERE birthdate IS NULL;

Run to view results

SELECT COUNT(name) AS count_birthdates FROM people WHERE birthdate IS NOT NULL;

Run to view results

SELECT COUNT(certification) AS count_certification FROM films;

Run to view results

SELECT COUNT(certification) AS count_certification FROM films WHERE certification IS NOT NULL;

Run to view results

SELECT title AS no_budget_info FROM films WHERE budget IS NULL;

Run to view results

SELECT COUNT(language) AS count_language_known FROM films WHERE language IS NOT NULL;

Run to view results

SELECT AVG(budget) FROM films;

Run to view results

SELECT SUM(budget) FROM films;

Run to view results

SELECT MIN(budget) FROM films;

Run to view results

SELECT MAX(budget) FROM films;

Run to view results

SELECT MIN(country) FROM films;

Run to view results

SELECT MAX(country) FROM films;

Run to view results

SELECT MIN(country) FROM films;

Run to view results

SELECT MIN(country) AS min_country FROM films;

Run to view results

SELECT SUM(duration) AS total_duration FROM films;

Run to view results

SELECT AVG(duration) AS average_duration FROM films;

Run to view results

SELECT MAX(release_year) AS latest_year FROM films;

Run to view results

SELECT MIN(duration) AS shortest_film FROM films;

Run to view results

SELECT AVG(budget) AS avg_budget FROM films WHERE release_year >= 2010;

Run to view results

SELECT SUM(budget) AS sum_budget FROM films WHERE release_year = 2010;

Run to view results

SELECT MIN(budget) AS min_budget FROM films WHERE release_year = 2010;

Run to view results

SELECT MAX(budget) AS max_budget FROM films WHERE release_year = 2010;

Run to view results

SELECT COUNT(budget) AS count_budget FROM films WHERE release_year = 2010;

Run to view results

SELECT AVG(budget) AS avg_budget FROM films WHERE release_year >= 2010;

Run to view results

SELECT ROUND(AVG(budget), 1) AS avg_budget FROM films WHERE release_year >= 2010;

Run to view results

SELECT ROUND(AVG(budget)) AS avg_budget FROM films WHERE release_year >= 2010;

Run to view results

SELECT ROUND(AVG(budget), 0) AS avg_budget FROM films WHERE release_year >= 2010;

Run to view results

SELECT ROUND(AVG(budget), -5) AS avg_budget FROM films WHERE release_year >= 2010;

Run to view results

SELECT SUM(gross) AS total_gross FROM films WHERE release_year >= 2000;

Run to view results

SELECT AVG(gross) AS avg_gross_A FROM films WHERE title LIKE 'A%';

Run to view results

SELECT MIN(gross) AS lowest_gross FROM films WHERE release_year = 1994;

Run to view results

SELECT MAX(gross) AS highest_gross FROM films WHERE release_year between 2000 AND 2012;

Run to view results

SELECT ROUND(AVG(facebook_likes), 1) AS avg_facebook_likes FROM reviews;

Run to view results

SELECT ROUND(AVG(budget),-3) AS avg_budget_thousands FROM films;

Run to view results

SELECT (4 + 3);

Run to view results

SELECT (4 - 3);

Run to view results

SELECT (4 * 3);

Run to view results

SELECT (4 / 3);

Run to view results

SELECT (4.0 / 3.0);

Run to view results

SELECT (gross - budget) FROM films;

Run to view results

SELECT (gross - budget) AS profit FROM films;

Run to view results

SELECT MAX(budget), MAX(duration) FROM films;

Run to view results

SELECT MAX(budget) AS max_budget, MAX(duration) AS max_duration FROM films;

Run to view results

SELECT budget AS max_budget FROM films WHERE max_budget IS NOT NULL;

Run to view results

SELECT (2/10);

Run to view results

SELECT title, duration/60.0 AS duration_hours FROM films;

Run to view results

SELECT COUNT(deathdate) * 100.0 / COUNT(*) AS percentage_dead FROM people;

Run to view results

SELECT (MAX(release_year) / 10.0) - (MIN(release_year) / 10.0) AS number_of_decades FROM films;

Run to view results

SELECT title, ROUND(duration / 60.0, 2) AS duration_hours FROM films;

Run to view results

SELECT title, budget FROM films ORDER BY budget;

Run to view results

SELECT title, budget FROM films ORDER BY title;

Run to view results

SELECT title, budget FROM films ORDER BY budget ASC;

Run to view results

SELECT title, budget FROM films ORDER BY budget DESC;

Run to view results

SELECT title, budget FROM films WHERE budget IS NOT NULL ORDER BY budget DESC;

Run to view results

SELECT title FROM films ORDER BY release_year;

Run to view results

SELECT title, release_year FROM films ORDER BY release_year;

Run to view results

SELECT title, release_year FROM films WHERE release_year IS NOT NULL ORDER BY release_year DESC;

Run to view results

SELECT title, release_year, gross FROM films WHERE release_year IS NOT NULL AND gross IS NOT NULL ORDER BY release_year DESC, gross DESC;

Run to view results

SELECT birthdate, name FROM people ORDER BY birthdate, name DESC;

Run to view results

SELECT item FROM coats WHERE color = `yellow` ORDER BY length LIMIT 3;

SELECT name FROM people ORDER BY name;

Run to view results

SELECT title, duration FROM films ORDER BY duration DESC;

Run to view results

SELECT release_year, duration, title FROM films ORDER BY release_year, duration;

Run to view results

SELECT certification, release_year, title FROM films ORDER BY certification, release_year DESC;

Run to view results

SELECT certification, COUNT(title) AS title_count FROM films GROUP BY certification;

Run to view results

SELECT certification, title FROM films GROUP BY certification;

Run to view results

SELECT certification, COUNT(title) AS count_title FROM films GROUP BY certification;

Run to view results

SELECT certification, language, COUNT(title) AS title_count FROM films GROUP BY certification, language;

Run to view results

SELECT certification, COUNT(title) AS title_count FROM films GROUP BY certification;

Run to view results

SELECT certification, COUNT(title) AS title_count FROM films GROUP BY certification ORDER BY title_count DESC;

Run to view results

SELECT certification, COUNT(title) AS title_count FROM films GROUP BY certification ORDER BY title_count DESC LIMIT 3;

Run to view results

SELECT release_year, COUNT(title) AS film_count FROM films GROUP BY release_year;

Run to view results

SELECT release_year, AVG(duration) AS avg_duration FROM films GROUP BY release_year;

Run to view results

SELECT release_year, country, MAX(budget) AS max_budget FROM films GROUP BY release_year, country ORDER BY release_year, country;

Run to view results

SELECT release_year, COUNT(DISTINCT(language)) AS lang_count FROM films GROUP BY release_year ORDER BY lang_count DESC;

Run to view results

SELECT release_year, COUNT(title) AS title_count FROM films GROUP BY release_year WHERE COUNT(title) > 10;

Run to view results

SELECT release_year, COUNT(title) AS title_count FROM films GROUP BY release_year HAVING COUNT(title) > 10;

Run to view results

SELECT certification, COUNT(title) AS title_count FROM films WHERE certification IN ('G', 'PG', 'PG-13') GROUP BY certification HAVING COUNT(title) > 500 ORDER BY title_count DESC LIMIT 3;

Run to view results

SELECT title FROM films WHERE release_year = 2000;

Run to view results

SELECT release_year FROM films GROUP BY release_year HAVING AVG(duration) > 120;

Run to view results

SELECT country, COUNT(DISTINCT(certification)) AS certification_count FROM films GROUP BY country HAVING COUNT(DISTINCT(certification)) > 10;

Run to view results

SELECT country, ROUND(AVG(budget), 2) AS average_budget FROM films GROUP BY country HAVING ROUND(AVG(budget), 2) > 1000000000 ORDER BY average_budget;

Run to view results

SELECT release_year FROM films WHERE release_year > 1990 GROUP BY release_year;

Run to view results

SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross FROM films WHERE release_year > 1990 GROUP BY release_year;

Run to view results

SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross FROM films WHERE release_year > 1990 GROUP BY release_year HAVING AVG(budget) > 60000000;

Run to view results

SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross FROM films WHERE release_year > 1990 GROUP BY release_year HAVING AVG(budget) > 60000000 ORDER BY avg_gross DESC LIMIT 1;

Run to view results