PostreSQL Flashcards
Why do we use databases in Web development?
It is quicker to query, organize, get access to data from a database. Store a lot of information. Data less likely to be stolen or corrupted, can be accessed by many computers/phones by many users.
What is PostgreSQL and what are some alternative relational databases?
An open source relational database. microsoft SQL server, MySQL, SQLite.
What are some advantages of learning a relational database?
What is one way to see if PostgreSQL is running?
using the top command.
sudo service postgresql status.
sudo = super user do.
pgweb
pgweb queries your postgres database and visualizing it for you.
What is a database schema?
A collection of tables
What is a table?
A table is a list of rows each having the same set of attributes.
every row in a table should have the same columns.
What is a row?
an object, an instance of the data, a data point,
A row is a single set of data with all columns that describe pieces of the data.
What is an attribute and what other names are used to describe them?
Attributes are commonly referred to as columns. Keys, properties.
What is SQL and how is it different from languages like JavaScript?
Structured Query Language, SQL is a declarative programming language which means you tell it the results you want, not what to do, as opposed to JavaScript which is an imperative programming language.
How do you retrieve specific columns from a database table?
by using the select keyword followed by the column names in double quotes separated by commas.
How do you filter rows based on some specific criteria?
by using the where keyword followed by the column name in double quotes, and then a comparison to the value in single quotes. an expression.
What are the benefits of formatting your SQL?
SQL does not have to be indented, but you should do it anyway for consistent style and therefore readability.
What are four comparison operators that can be used in a where clause?
=, <, >, !=
How do you limit the number of rows returned in a result set?
using the limit keyword followed by a number where the number is the max number of rows to be returned.
How do you retrieve all columns from a database table?
the select keyword followed by *. the * is not surrounded by any type of quotes
How do you control the sort order of a result set?
using the order by keyword. This will sort it in ascending order, use the desc keyword to sort by descending order.
How do you add a row to a SQL table?
using the insert keyword followed by into followed by the table you want to insert into in double quotes, followed by the parenthesis with the column inside quotes inside the parenthesis. then on the next line the keyword values followed by parenthesis and the values inside with single quotes if a string.
insert into “actors” (“firstName”, “lastName”)
values (‘Henry’, ‘Ding’)
returning *;
What is a tuple?
In SQL, a list of values is referred to as a tuple.
How do you add multiple rows to a SQL table at once?
Data rows can be batch inserted into a database table by specifying more than one tuple of values, separated by commas.
How do you get back the row being inserted into a table without a separate select statement?
In PostgreSQL it’s possible to get the full inserted row back from the database, including its auto-generated attribute(s), like a productId. This is done with a returning clause. Otherwise, it would be necessary to make a separate query to the database just to get the auto-generated attributes.
How do you update rows in a database table?
update “tablename”
set “columnname” = ‘new value’
where “columnname” = ‘value’
Why is it important to include a where clause in your update statements?
To make sure you only update the specific row you want and not everything.
How do you delete rows from a database table?
delete
from “tablename”
where “columnname” = ‘value’
How do you accidentally delete all rows from a table?
delete
from “tablename”
What is a foreign key?
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. A foreign key references other tables’s primary keys.
How do you join two SQL tables?
using the join keyword and the using keyword
How do you temporarily rename columns or tables in a SQL statement?
select “addresses”.”line1” as “address”
will rename line1 to address.
What are some examples of aggregate functions?
count(), sum(), min(), max(), every()
What is the purpose of a group by clause?
the GROUP BY clause divides a table into sets.
