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
How do you join two SQL tables?
using the ‘join’ clause
combines data from two separate databases into one result set
calls to link two tables by a their respective common column
i.e.,
select *
from “tableA”
join “tableB” using (“column”)
How do you temporarily rename columns or tables in a SQL statement?
alias method using dot notation and the keyword ‘as’ , i.e.,
select “table”.”column” as “column1”
“table”.”column” as “column2”
table names are usually aliased in the ‘from’ and ‘join’ clauses
What are some examples of aggregate functions?
MIN() returns the smallest value in a given column
MAX() returns the maximum value in a given column.
SUM() returns the sum of the numeric values in a given column
AVG() returns the average value of a given column
COUNT() returns the total number of values in a given column
COUNT(*) returns the number of rows in a table
What is the purpose of a group by clause?
put all those with the same value n the one group
how to kill a process running at a certain port
run the two commands:
sudo lsof -i tcp:3000
or
sudo lsof -i:8080
note: 3000 represents the port, change as needed; the above command will output the process running at a certain PID (make note of it)
sudo kill -9 PID
note: use the PID from the result of the first command; PID is process ID you want to kill.
sql order
SELECT (is used to select data from a database)
FROM (clause is used to list the tables)
WHERE (clause is used to filter records)
GROUP BY (clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns)
HAVING (clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE)
ORDER BY (keyword is used to sort the result-set)
