SQL Flashcards
(31 cards)
relational database
represents a collection of related (two-dimensional) tables. Each of the tables are similar to an Excel spreadsheet, with a fixed number of named columns (the attributes or properties of the table) and any number of rows of data.
SQL
structured query language
in SQL columns represent x?
properties
in SQL rows represent x?
instances
how to select all columns of data in a table?
“dumping all the data at once”
SELECT *
FROM table_name;
how to select several columns in a database?
by separating them with a comma:
SELECT title, director FROM movies;
conditional word for operations with constraints
WHERE
e.g.:
SELECT column, another_column, …
FROM mytable
WHERE condition
AND/OR another_condition
AND/OR …;operator for: Number is within range of two values (inclusive)
BETWEEN … AND …
e.g.:
col_name BETWEEN 1.5 AND 10.5
operator for: Number is not within range of two values (inclusive)
NOT BETWEEN … AND …
e.g.:
col_name NOT BETWEEN 1 AND 10
operator for: number exists in a list
IN (…)
e.g.:
col_name IN (2, 4, 6)
operator for: number does not exist in a list
NOT IN (…)
e.g.:
col_name NOT IN (1, 3, 5)
query capitalization mandatory?
NO. Just a convention to help distinguish from column and table names
operator: Case sensitive exact string comparison
=
operator: Case sensitive exact string inequality comparison
!= or <>
operator: Case insensitive exact string comparison
LIKE
operator: Case insensitive exact string inequality comparison
NOT LIKE
operator: Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)
%
e.g. col_name LIKE “%AT%”
(matches “AT”, “ATTIC”, “CAT” or even “BATS”)
operator: Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)
_ (underscore)
e.g.: col_name LIKE “AN_”
(matches “AND”, but not “AN”)
string quoting mandatory?
yes. Allows to distinguish strings from SQL keywords
how do we add an additional condition?
By introducing AND/OR after WHERE.
e.g.:
SELECT column, another_column, …
FROM mytable
WHERE condition
AND/OR another_condition
AND/OR …;keyword: a convenient way to discard rows that have a duplicate column value
DISTINCT
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);
clause: SQL provides a way to sort your results by a given column in ascending or descending order (alphanumerically)
ORDER BY e.g.: SELECT column, another_column, … FROM mytable WHERE condition(s) ORDER BY column ASC/DESC;
clause: will specify where to begin counting the number rows from
OFFSET
clause: will reduce the number of rows to return
LIMIT e.g.: SELECT column, another_column, … FROM mytable WHERE condition(s) ORDER BY column ASC/DESC LIMIT num_limit OFFSET num_offset;
