PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
PostgreSQL is a powerful, free, open source Relational Database Management System (RDBMS)
Relational Database: database based on relational model of data
Other relational databases: SQLite, NexusDB, Oracle, SQL Azure
What are some advantages of learning a relational database?
Relational databases can store and modify data in a way that makes data corruption as unlikely as possible.
Also good for storing related data
What is one way to see if PostgreSQL is running?
sudo service postgresql status -command
check if service is running in top command
What is a database schema?
A collection of tables and basically defines how the data in a relational database should be organized
What is a table?
A table is a list of rows each having the same set of attributes
What is a row?
One entry for attributes
How do you add a row to a SQL table?
insert into statement
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);
What is a tuple?
A list of values is referred to as a tuple
How do you add multiple rows to a SQL table at once?
values comma separated list of tuples
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’),
(‘Tater Mitts’, ‘Scrub some taters!’, 6, ‘cooking’)
returning *;
How do you get back the row being inserted into a table without a separate select statement?
returning clause
How do you update rows in a database table?
update statement followed by set statement
update “products”
set “price” = 100;
Why is it important to include a where clause in your update statements?
Without a where clause, everything will be updated
How do you delete rows from a database table?
Delete statement
How do you accidentally delete all rows from a table?
Not including a where clause
What is a foreign key?
Values in a column that links the table to another table
