Intermediate SQL learning

how do you use a * with COUNT()?

-COUNT (field_name) counts values in a field -COUNT(*) counts records in a table ex: SELECT COUNT(*) AS total_records FROM people;

How do we summarize data?

-using aggragate functions to return a single value -COUNT is a aggragate function -AVG( ), SUM( ), MIN( ), MAX( ), COUNT( ) are aggragate functions -All aggragate functions come after SELECT

Querying a database

A query is a request for data from a database given certain selection parameters. this study set is for postgreSQL ex: count and view specified records, understand query execution and style, filtering, and aggregate functions, and sorting and grouping

SELECT title, release_year FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget > 100000000 Amend the query to include Spanish or French-language films

AND (language = 'Spanish' OR language = 'French');

what aggragate functions can only use Numerical fields?

AVG ( ) and SUM ( )

what aggragate functions can we use with non numerical fields?

COUNT ( ), MIN ( ), and MAX ( )

what does DISTINCT do in PostgreSQL / SQL?

DISTINCT removes duplicates to only show unique values a good example of using DISTINCT would be to find which languages are in the film table. ex: SELECT DISTINCT language FROM films;

what does a keyword error look like?

SELCT title, country, duration FROM films; syntax error at or near "SELCT" Line 1: SELCT title, country, duration ^

Select all records for German-language films released after 2000 and before 2010

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

how do we use WHERE with aggragate function AVG ( ) ?

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

Calculate the average duration of all films

SELECT AVG (duration) AS average_duration FROM films ;

aggragate function using AVG ( ) ?

SELECT AVG( budget ) FROM films ;

Calculate the average gross of films that start with A

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

count the records with atleast 100k votes

SELECT COUNT (*) AS films_over_100k_ votes FROM reviews WHERE num_votes >= 100000;

count the Distinct countries from the film table

SELECT COUNT (DISTINCT country) AS count_distinct_countries FROM films;

count the records for languages and countries in the films table; alias AS count_languages and count_countries

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

what does the count function look like?

SELECT COUNT (birthdate) AS count_birthdates FROM people;

How do we use aggragate function COUNT with WHERE?

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

find the number of records containing film_id from reviews.

SELECT COUNT (film_id) AS Count_film_id FROM reviews;

count the number of films that have a language associated with them

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

how do i combine COUNT() and DISTINCT in PostgreSQL / SQL?

SELECT COUNT() with DISTINCT to count unique values ex: SELECT COUNT(birthdate) AS Count_distinct_birthdates FROM people;

count the number of records in the peoples table

SELECT COUNT(*) AS count_records FROM People; ex result: 8397

count the number of birthdates in the people table

SELECT COUNT(birthdate) AS Count_birthdate FROM people; ex result: 6152

Calculate the percentage of people who are no longer alive

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

Calculate the highest gross film released between 2000-2012

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

Find the latest release_year

SELECT MAX (release_year) latest_year FROM films ;

How do we use WHERE with aggragate function MAX?

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

how do we use non-numerical field data with the aggragate function MAX ( ) ?

SELECT MAX(country) AS max_country FROM films;

Find the duration of the shortest film

SELECT MIN (duration) AS shortest_film FROM films ;

how do we use non-numerical field data with aggragate function MIN ( ) ?

SELECT MIN(country) AS min_country FROM films;

Calculate the lowest gross film in 1994

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

how do we use a negative number to ROUND in SQL?

SELECT ROUND (AVG(budget) -5) AS avg_budget FROM films WHERE release_year >= 2010; the result is usually 41072235 but with the -5 reounding we move the decimal left so we will get this rounded number result: 41100000

Calculate the average budget rounded to the thousands

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

how do we use the ROUND function to round a number to 2 decimal spaces?

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

Round the average number of facebook_likes to one decimal place

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

what are the two ways to use ROUND to find a whole number?

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

Query the sum of film durations

SELECT SUM (duration) AS total_duration FROM films ;

Calculate the sum of gross from the year 2000 or later

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

how do you COUNT() multiple fields?

SELECT count (name) AS count_names, COUNT (birthrate) AS count_birthdates FROM people;

Select film_ids and facebook_likes for ten records with less than 1000 likes

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

how do we find all names that end with r using the LIKE clause?

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

what does the LIKE opera look like in SQL using % ?

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

what does the LIKE opera look like in SQL using the _ ?

SELECT name FROM people WHERE name LIKE 'Ev_' ;

how do we find name records where the 3rd character is" t "?

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

Select the names that start with B

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

filter films released in 1994 or 1995, and certified PG or R

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

Using BETWEEN, AND, OR

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

how do i see films released before 1960?

SELECT title FROM films WHERE release_year < 1960

how do i see all films released in the years except 1960?

SELECT title FROM films WHERE release_year <> 1960;

how do i see films released during a specific year?

SELECT title FROM films WHERE release_year = 1960;

what is the correct way to use the OR operator in SQL?

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

how do i see films released after 1960 with SQL?

SELECT title FROM films WHERE release_year > 1960;

Use AND if we need to safisfy all criteria correct way to ise AND:

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

BETWEEN, AND in mutilple criteria

SELECT title FROM films WHERE release_year >= 1994 AND release_year <= 2000; another way to use BETWEEN, AND SELECT title FROM films WHERE release_year BETWEEN 1994 AND 2000;

Calculate the title and duration_hours from films

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

what does a comm error look like in SQL?

SELECT title, country duration FROM films; syntax error at or near "duration" Line 1: SELECT title, country duration ^

Find the title and year of films from the 1990 or 1999 Add a filter to see only English or Spanish-language SELECT title, release_year Filter films with more than $2,000,000 gross

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

Find the title and year of films from the 1990 or 1999 Add a filter to see only English or Spanish-language SELECT title, release_year

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

-- Select the title and release_year for all German-language films released before 2000

SELECT title, release_year FROM films WHERE release_year < 2000 AND language = 'German'; make sure to keep the G in german capital, or the code wont work

Find the title and year of films from the 1990 or 1999

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

Select the title and release_year for films released between 1990 and 2000 using BETWEEN Narrow down your query to films with budgets > $100 SELECT title, release_year Restrict the query to only Spanish-language films

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

Select the title and release_year for films released between 1990 and 2000 using BETWEEN Narrow down your query to films with budgets > $100 SELECT title, release_year

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

Select the title and release_year for films released between 1990 and 2000 using BETWEEN

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

Find the title and release_year for all films over two hours in length released in 1990 and 2000

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

what does the ROUND ( ) function do in SQL? and why is it useful?

The ROUND ( ) will round decimal results to a specified decimal, and it's useful in rounding aggragate functions that usually give us decimal answers

what are the 2 wildcards with the LIKE opera in SQL?

The percent ( % ) and the underscore ( _ )

why do we use single quotation marks around words like 'English' and 'Spanish'?

This is because the database will recognize the SQL code English as a column instead of a string code like 'English'.

when can I use OR in SQL?

Use OR when you need to satisfy at least one condition ex: perhaps we want to have green OR purple coat options

what does the SUM aggragate function do?

add up all the values together

what's the difference between aggragate functions and arithmetic functions in SQL?

aggragate functions calculate vertically like a column, where as arithmetic functions calculate horizontally like the rows

why is filtering data with just names that start with A like 'A%' isn't super good filtering?

because 'A%' still gives us results with names that start with Á

why does SQL give us the result of 1 when doing SELECT (4.0 / 3.0) ?

because SQL assumes you want to get an integer back if you divide an integer by an integer

why do we need to use a alias with arithmetic and aggragate functions in SQL?

because if we don't we won't get a defined field name in the results

what does the COUNT() function do in PostgreSQL / SQL?

counts the number of records with a value in the field

how do we filter null records in SQL?

ex: SELECT name FROM people WHERE birthdate IS NULL;

when do we use "null" in SQL?

for missing values due to: -human error -information not available -unknown

SQL is not processed in written order

it is processed at FROM first then SELECT good to know processing order for debugging and aliasing aliases are declared in the SELECT statement

what does LIMIT do?

limits how many results we return Ex: SELECT name FROM people LIMIT 10;

Is formatting required in SQL?

no, but when you work with other data analysts you should format your SQL to make it easier to read as the code can get long

what does the MIN ( ) aggragate function do?

return us the lowest value in the field

what does the MAX aggragate function do?

return us the maximum value in the field

What does the % wildcard do in the LIKE opera?

the % will match zero, one, or many characters in the text

what does the _ wildcard do in the LIKE opera field?

the _ wildcard will match a single character in the text

What do we use to filter text and why?

to filter a pattern rather then specific text: -LIKE -NOT LIKE -IN

How do we use the NOT LIKE opera in SQL?

to find records that dont match the specified pattern SELECT name FROM people WHERE name NOT LIKE 'A.%' ; in this case we are seaching for names that do not have a " . " in their first name

why do we add AS (aliasing) to our code?

to make others reading our code really clear to read

what is summarizing data?

understanding data as a "whole"

how do i use the where clause with strings?

use single quotation marks around the string you want to filter ex: SELECT title FROM films WHERE country = ' japan ';

how do i see films released during or before 1960?

using <= with WHERE clause ex: SELECT title FROM films WHERE release_year <= 1960;

what allows us to specify multiple values in a WHERE clause?

using IN in SQL like: SELECT title FROM films WHERE release_year IN (1920, 1930, 1940) ;

how do you filter numbers?

using WHERE the filtering clause ex: WHERE color = ' green '

do we still alias even with two field names

yes because if we don't the field names in the result can be called the same exact thing making the results very confusing and unclear

What does the LIKE opera do in SQL?

LIKE: -Used to search for a pattern in a field

what can be used with the WHERE clause to add multiple criteria?

OR, AND, BETWEEN ex: SELECT * FROM coats WHERE color = 'yellow' OR length = 'short'; ex: SELECT * FROM coats WHERE color = 'yellow' AND length = 'short'; ex: SELECT* FROM coats WHERE buttons BETWEEN 1 AND 5;

what is the order of execution so far in SQL?

FROM WHERE SELECT LIMIT

order of execution with WHERE and LIMIT on SQL?

FROM , WHERE , SELECT, LIMIT

SQL order of execution?

FROM, SELECT, LIMIT

what's another word for columns?

Fields

what does the following query return? SELECT title FROM films WHERE release_year > 2000;

Films released after the year 2000

what is debugging in SQL

Fixing punctuation, capatilization, grammar. ex: SELECT nme FROM people; field "nme" does not exist line 1: SELECT nme ^ HINT: perhaps you meant to reference the field "people.name".