postgreSQL Flashcards
(27 cards)
What is PostgreSQL and what are some alternative relational databases?
an open source Relational Database Management System (RDBMS)
MySQL, SQL Server, Oracle
What are some advantages of learning a relational database?
they support good guarantees about data integrity, store and modify data in a way that makes data corruption as unlikely as possible. Learning databases are a transferable skill
What is one way to see if PostgreSQL is running?
sudo service postgresql status, or top
What is a database schema?
A collection of tables, how the data in a relational database should be organized.
What is a table?
relations - where relational databases are store data in
a set of rows.
a collection of related data held in a table format within a database. It consists of columns and rows.
What is a row?
a data record within a table. Each row, which represents a complete record of specific item data, holds different data within the same structure.
What is SQL and how is it different from languages like JavaScript?
Structured Query Language (SQL) - primary way of interacting with relational databases
- a declarative programming language
How do you retrieve specific columns from a database table?
select “column1”,
“column2”
from “table
How do you filter rows based on some specific criteria?
where “column1” = ‘data’,
limit
What are the benefits of formatting your SQL?
for consistent style and 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?
limit #
How do you retrieve all columns from a database table?
select *
How do you control the sort order of a result set?
order by “columnName” asc/desc
How do you add a row to a SQL table?
insert into “tableName” (“column1”, “column2”)
values (‘value1’, ‘value2’)
What is a tuple?
list of values
How do you add multiple rows to a SQL table at once?
values (‘value1’, ‘value2’),
(‘value1’, ‘value2’),
(‘value1’, ‘value2’)
comma separated list of tuples.
How do you get back the row being inserted into a table without a separate select statement?
returning * (or by column name)
at the end of the statement
How do you update rows in a database table?
update “tableName”
set “columnName” = ‘value’
where “columnName” = ‘value’
Why is it important to include a where clause in your update statements?
you will update everything otherwise
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?
not specifying where
What is a foreign key?
one column linking all the attributes to a different table. Shared data value
How do you join two SQL tables?
join “tableName” using (“foreign key”)
