Section 1
Preview this deck
TEXT
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (105)
Section 1
(50 cards)
TEXT
a text string
BETWEEN
operator that allows you to set parameters within [(up to (but sometimes not) including the final value of the parameter) ex: 'A' and 'J' will include all names up to the end of I and no Js compared to A AND J which will include the names beginning with J] the data of a single column (values can be numbers, text, or dates)
column
a set of values of a particular type
clause
perform specific tasks in SQL. By convention, they are written in capital letters. can also be referred to as commands.
SELECT MIN(column name) FROM table_name;
gives the minimum value of a column
types of data
integer, text, date, real
*
command that tells you to show every column of information
SELECT
command that enables only one sort of data to display a command used every time you want to query data from a database
DATE
the date formatted as YYYY-MM-DD for the year, month, and day
GROUP BY
command to tell you how to group information it is the same as ALL it aggregates the every item of that column
ADD COLUMN
command to add a new column
REAL
a decimal value
ALTER TABLE
enables the ability to alter a perameter of the table (add columns)
relations
a collection of information gathered into rows and columns, sometimes referred to as tables
TABLE NAME
refers to the name of the table that the command is applied to
result set
the table created by using the SELECT statement
SELECT * FROM table name
allows you to select data from every column
ORDER BY
list the data in our result set in a particular order
SELECT price, COUNT(*) FROM fake_apps GROUP BY price; What does this do?
Here, our aggregate function is COUNT() and we are passing price as an argument to GROUP BY. SQL will count the total number of apps for each price in the table.
ASC or DESC
an operator to use under the ORDER BY option that tells the actual order that the results will be presented (alphabetical, numerical)
DELETE FROM table name WHERE column name IS NULL;
removes all records with null items from the viewable table = another way to sort!
use commas to
separate column names in a query
NULL
represents missing or unknown values--the blank space before values are added to an empty column
UPDATE table name
enables an edit to the table
LIKE
can be a useful operator when you want to compare similar values
% in front of, in the middle of, behind something
is an operator that tells you to substitute any number of characters (zero or more) based on where the operator is situated, it differs from the underscore because it can account for multiple or no characters
IS NULL
is a command that removes rows with any NULL values
Return the names of the most downloaded apps in each category. table name = fake_apps
SELECT name, category, MAX(downloads) FROM fake_apps GROUP BY category;
operators with the WHERE clause
=, !=, >, <, >=, <=, <> (also means not equal to on ANSI SQL)
WHERE record number/name/title
designates which record will be edited
statement
text that the database recognizes as a valid command. always ends in a semi-colon ;
AND
is an operator that enables the return of rows that satisfy more than one condition
DELETE FROM
clause that lets you delete rows from a table
INTEGER
a positive or negative whole number
table
a collection of information gathered into rows and columns, sometimes referred to as relations
row
a single record in a table
WHERE
The way to filter queries in SQL a clause that indicates you want to filter the result set to include only rows where the following condition is true
SELECT SUM(column name) FROM table_name;
statement that gives a result set of the total number of values for a column
OR
operator can also be used to combine more than one condition in a WHERE clause operator evaluates each condition separately and if ANY of the conditions are true then the row is added to the result set.
SET column name
designates the column to be edited
relational database
a database that organizes information into one or more tables.
SELECT DISTINCT
is used to return unique values in the result set. It filters out all duplicate values can be used as a filter for COUNT ex: COUNT(DISTINCT yr)
INSERT
adds rows to a table, use this command to add new records
_ (underscore)
means you can substitute any individual character here without breaking the pattern.
LIMIT
is a clause that lets you specify the maximum number of rows the result set will have operator to use under ORDER BY command
Return the names of the apps that have been downloaded the least number of times in each category table name = fake_apps
SELECT name, category, MIN(downloads) FROM fake_apps GROUP BY category;
query
-retrieve information stored in a database -use SQL to allow us to communicate with the database by asking questions and having the result set return data relevant to the question.
MAX(column name) FROM table_name;
gives you the greatest value of that column it takes the name of a column as an argument so it will return the largest value of that column
SELECT COUNT(*) FROM table_name
tells you the number of records in the table named
parameter
a list of columns, data types, or values that are passed to a clause as an argument.
Section 2
(50 cards)
JOIN artists ON
specifies the type of join we are going to use as well as the name of the second table
joins
are used to combine rows from two or more tables
SELECT table_name.column name, table_name.column name FROM table_name;
combines data from different tables to make a new result set and the result set is presented in the order by which the requested data was given in the SELECT clause
FROM albums
specifies the first table we are querying
A table (CAN or CANNOT) have a Primary Key for more than one column
CAN
create a table in ANSI SQL
create table "tablename" ("column1" "data type" [constraint], "column2" "data type" [constraint], "column3" "data type" [constraint]);
For each continent show the number of countries:
SELECT continent, COUNT(name) FROM world GROUP BY continent
CASE WHEN column_name='value' THEN 'value' ELSE column_name END
how to use CASE statements to express multiple cases: repeat the WHEN statement not "CASE WHEN"
Select the code which shows the years when a Medicine award was given but no Peace or Literature award was
SELECT DISTINCT yr FROM nobel WHERE subject='Medicine' AND yr NOT IN(SELECT yr FROM nobel WHERE subject='Literature') AND yr NOT IN (SELECT yr FROM nobel WHERE subject='Peace')
List each continent and the name of the country that comes first alphabetically. using the naming tables method (world x world y)
SELECT continent, name FROM world x WHERE name=(SELECT name FROM world y WHERE y.continent=x.continent ORDER BY name ASC LIMIT 1)
cross join
One way to query multiple tables is to write a SELECT statement with multiple table names separated by a comma
Data types in ANSI SQL
char(size) = fixed length character string varchar(size) = variable length character string number(size) = number with the max length of column digits date = date number(size,d) = Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.
UNION
Returns two sets of information as one result
SELECT * FROM albums JOIN artists ON albums.artist_id = artists.id; 1.what is the foreign key? 2.what is the primary key (the item shared by both tables)
1.artist_id (a column found in the albums table) 2. id
Calculate the average number of downloads at each price AND Make the result set more readable by rounding the average number of downloads to two decimal places for each price table name = fake_apps
SELECT price, ROUND(AVG(downloads), 2) FROM fake_apps GROUP BY price;
Show the number of different winners for each subject.
SELECT subject, COUNT(DISTINCT(winner)) FROM nobel GROUP BY subject
SELECT *
specifies the columns our result set will have. Here, we want to include every column in both tables.
to express non-ASCII characters as in Ü
use the lowercase version of the letter with the symbol
use ______ and ________ to connect rows in two different tables.
foreign keys, primary keys
ROUND(column name or aggregate function, number of decimal places)
rounds the results to a number of decimal places ex: SELECT price, ROUND(AVG(downloads), 2) will round the averages of the downloads column to two decimal places to round to the nearest integer use the ROUND command but do not give a decimal value to round to the nearest ten use -1, hundred use -2, thousand use -3, etc) to implement this function on more than one column use a separate command for each column separated by a comma
List the continents with total populations of at least 100 million.
SELECT continent FROM world GROUP BY continent HAVING SUM(population)>=100000000
Primary Key
serves as a unique identifier for each row or record in a given table. It is literally an id value for a record.
You can use the words ALL or ANY where the right side of the operator might have multiple values.
Show each country that has a population greater than the population of ALL countries in Europe. Note that we mean greater than every single country in Europe; not the combined population of Europe. SELECT name FROM world WHERE population > ALL (SELECT population FROM world WHERE continent='Europe')
NOT IN command
can only be used to report information that is contradictory to a complete affirmative command ex: Select the code which would show the year when neither a Physics or Chemistry award was given answer: SELECT yr FROM nobel WHERE yr NOT IN(SELECT yr FROM nobel WHERE subject IN ('Chemistry','Physics'))
to JOIN two tables that do not share the same id key
join the tables on the columns that they do have in common
Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
SELECT name, continent FROM world x WHERE population/3 > ALL (SELECT population FROM world y WHERE x.continent = y.continent AND x.name != y.name AND population > 0)
to give data a 0 or 1 value in order to have that data anchor a result set
use column_name IN ('value1', 'value 2') which gives every value that does is not value1 or value2 a property of 0, and gives every value that matches value1 or value2 a property of 1, which anchors it to the bottom of the list
inner join
will combine rows from different tables if the join condition is true
population density
population/area
HAVING vs WHERE
The HAVING clause is tested after the GROUP BY. You can test the aggregated values with a HAVING clause. Show the total population of those continents with a total population of at least half a billion.
table1_name.table2name_columnname = tablename.columnname
the join condition that describes how the two tables are related to each other
XOR
The logical operation requiring either condition A, or condition B, but not both simultaneously
To create a table with a Primary KEY
CREATE TABLE table_name(column name VALUE TYPE PRIMARY KEY, column name VALUE TYPE)
adding records to tables with ANSI SQL
insert into "tablename" (first_column,...last_column) values (first_value,...last_value);
to express an apostrophe use
\
NOT LIKE
will exclude a given value or category of values from a command
We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.
SELECT continent, name, area FROM world x WHERE area >= ALL (SELECT area FROM world y WHERE y.continent=x.continent AND area>0)
Calculate the average number of downloads for an app in the database titled fake_apps
SELECT AVG(downloads)
Aggregate commands
Always used within the SELECT clause: SUM, COUNT, MAX, MIN, DISTINCT, ORDER BY, AVG, FIRST, LAST
AS
allows the rename a column or table using an alias in a return set aliases only appear in a result set/they do not change the name of the columns in the original table
Pick the code that shows the amount of years where no Medicine awards were given
SELECT COUNT(DISTINCT yr) FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')
requirements of table and column names
table and column names must start with a letter and can be followed by letters, numbers, or underscores
Calculate the average number of downloads at each price table name = fake_apps
SELECT price, AVG(downloads) FROM fake_app GROUP BY price; notice that the value name has to be given with the SELECT clause and the GROUP BY clause
We can use the word ALL to allow >= or > or < or <=to act over a list. For example, you can find the largest country in the world, by population with this query:
SELECT name FROM world WHERE population >= ALL (SELECT population FROM world WHERE population > 0) you have to list that the population must be greater than 0 because some countries do not have reportable populations
foreign key
a column that contains the primary key of another table in the database
how to express percentage
CONCAT(ROUND((number*100)/whole_value),0,'%') CONCAT function pushes two values from two different columns contained within its parenthesis together percentage is always part over whole times 100, in this case the "times 100" part is done before the division by the whole the round function value placed at zero returns a whole number percentage instead of a decimal percentage the percentage sign smooshes with the calculation
COUNT(column_name) LENGTH(column_name) DISTINCT(column_name)
gives the number of times a parameter assigned under the WHERE function occurs, you put this command in the SELECT clause, not the WHERE clause when you wish for it to display the info in the Return Set COUNT = total DISTINCT = separate instances of an event
column_name*x means
numerical value of that column multiplied by 'x'
IN()
allows us to check if an item is in a list
LEFT JOIN
an outer JOIN command which gives the result set that includes data from the "left table/first table"which may be not be included in the data from the "right table/second table" that information that does not overlap in the right table is reported as NULL
Section 3
(5 cards)