SQL Flashcards
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;
SQL is used by …?
- database administrators
- developers writing data integration scripts
- data analysts looking to set up and run analytical queries
SQL is used for…?
- modifying database table and index structures;
- adding, updating and deleting rows of data;
- retrieving subsets of information from within relational database management systems (RDBMSes)
database object: VIEW
logical representations of data assembled from one or more database tables
database object: INDEX
lookup tables that help speed up database lookup functions
database object: REPORT
consist of data retrieved from one or more tables, usually a subset of that data that is selected based on search criteria
SQL status?
de facto standard programming language for relational databases
database normalization
splitting data into separate tables
helps minimize duplicate data in any single table, and allows for data in the database to grow independently of each other. BUT, queries get slightly more complex since they have to be able to find data from different parts of the database, and performance issues can arise when working with many large tables
