postgres Flashcards
What is PostgreSQL and what are some alternative relational databases?
relational database management system (rdms); mysql
What are some advantages of learning a relational database?
store and modify data in a way that makes data corruption as unlikely as possible
What is one way to see if PostgreSQL is running?
sudo service postgresql status
What is a database schema?
defines how the data in the relational database should be organized
What is a table?
- a list of rows each having the same set of attributes ( attributes are commonly referred to as columns)
- a relation object, which can be one or more, which store the data for the relational database system
- data organized in a row-and-column format similar to a spreadsheet
What is a row?
represents a unique record for the respective column attribute
what is a column?
contain the column name, data type, and any other attributes for the column
represents a field in the record
column is a set of data values of a particular type
What is SQL and how is it different from languages like JavaScript?
way of interacting with relational databases; way of retrieving, creating, and manipulating data in a relational database
js: imperative (tell what to do and how to do it)
sql: declarative (describe the results you want to get)
How do you retrieve specific columns from a database table?
using the ‘select’ keyword, i.e.,
select “column”
from “database”
How do you filter rows based on some specific criteria?
using the ‘where’ keyword, i.e., where a ‘column’ is equal to a certain ‘value’:
select “column”
from “database”
where “column” = ‘value’;
What are the benefits of formatting your SQL?
easier to comprehend
What are four comparison operators that can be used in a where clause?
=, !=,
equal, not equal to, less than, greater than
How do you limit the number of rows returned in a result set?
use the ‘limit’ keyword followed by an integer; should be at the end of the query request, i.e.,
select “column”
from “database”
limit 3;
How do you retrieve all columns from a database table?
using the universal selector (*)
select *
from “database”
How do you control the sort order of a result set?
using the ‘order by’ keywords, i.e.,
select *
from “database”
order by “category”
How do you add a row to a SQL table?
using the insert keyword/clause, i.e.,
insert from “table”
What is a tuple?
a list of values, i.e.,
when you are inserting a new row onto a table, we want to use tuples to define the data in respect to the columns - tuples are usually within ()
values (‘data1’, ‘data2’, ‘data3’)
values are wrapped in single quotes
How do you add multiple rows to a SQL table at once?
specifying more than one tuple of values, ie.,
insert from “table” (“column”
values (‘data’)
‘some other data’)
(‘data again’)
How do you get back the row being inserted into a table without a separate select statement?
using the returning clause at the end of the commands
How do you update rows in a database table?
using the ‘update’ keyword/clause, i.e.,
update from “table”
Why is it important to include a where clause in your update statements?
update a specific row rather than updating every row
specificity
How do you delete rows from a database table?
using the ‘delete’ keyword/clause, i.e.,
delete from “table”
How do you accidentally delete all rows from a table?
not specifying a “where” clause
What is a foreign key?
a shared value btwn tables, i.e.,
in database A, I have a column which references another column in database B
links two tables together by a common relation
