GA - Fundamentals of Databases & SQL Flashcards
(22 cards)
What does a Structured Query Language (SQL) do?
It is used to communicate with databases to return data
What are some of the capabilities of SQL?
‣Store and work with with very large amounts of data (millions or billions of rows).
‣Impose structure on data
‣Ability to access a dataset to rule them
all, which lots of people can access simultaneously
When is SQL and Databases commonly used?
‣ When there’s lots of data.
‣ When many people need to access the data.
‣ When the data is being updated regularly.
‣ When data needs to be clean and well structured.
What does a database do?
It houses large amounts of info in the form of data tables
What is the purpose of a data table?
It provides relational information. Those creating a table use columns and rows to arrange the various data.
Why do databases typically live off your computer on a server (basically a
computer-connected to the internet)?
Due to their size and to allow for easier
access.
How do you download an SQL Query into a file (typically
a .csv)?
N/A
What are the most common forms of SQL?
There are a number of implementations of databases, including: ‣ PostgreSQL (what we will be using) ‣ MySQL ‣ MS SQL ‣ Oracle
What are relational databases?
It is when the overall data is broken up into logical sets of
information, with KEYS or IDs enabling linking between tables.
What is transactional data?
It is when large volumes of data are updated frequently. e.g. daily_dog_sales
What is reference data?
It is data that rarely changes and is relatively small in volume. e.g. types_of_dog
What are the three main clauses used to comm specific request/questions to databases?
Select
From
Where
What does the SELECT clause comm?
“I WANT THESE COLUMNS”
‣ Allows you to select certain columns from a table.
What does the FROM clause comm?
“FROM THIS DATA TABLE”
‣ Specifies the tables from which the query extracts data.
What does the WHERE clause comm?
“ONLY WHEN THESE THINGS ARE TRUE“
‣ Determines which rows are selected from the tables.
What does “*” mean in SQL?
It stands for “every column”
What does SELECT DISTINCT mean in SQL?
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
What does ORDER BY do in SQL?
It sorts your results in an ascending (ASC) or descending (DESC) order, depending on which you choose.
After the ORDER BY is a number or column name that indicates which column you are sorting by; you either leave it blank or place a desc to determine if you will sort ascending (blank) or descending (desc).
What do we need to take into acct when you want to implement multiple conditions in SQL?
You should consider using the AND, OR, AND CAST clauses
What does the AND clause do?
It returns TRUE if both conditions are true
What does the OR clause do?
It returns FALSE if neither condition is true (True if either is true)
What does the CAST (field AS type) clause do?
It converts the contents of “field” to the specified data type
