PostgreSQL Flashcards
[Postgres Intro]
What is PostgreSQL and what are some alternative relational databases?
PostgreSQL - relational database
MySQL, SQL Server by Microsoft, Oracle
[Postgres Intro]
What are some advantages of learning a relational database?
They are very common, knowing one will translate well to others
[Postgres Intro]
What is one way to see if PostgreSQL is running?
$ sudo service postgresql status
with service, they won’t take up your terminal (they run in the background)
What does SQL mean?
Structured Query Language
[Postgres Database]
What is a database schema?
A collection of tables which define how the data in a database should be organized
[Postgres Database]
What is a table?
Rows with the same set of attributes
[Postgres Database]
What is a row?
One entry with one value of the data
[Postgres Database]
What command is used to create a database?
What command is used to delete a database?
$ createdb databasename
$ dropdb databasename
[SQL Select]
What is SQL and how is it different from languages like JavaScript?
SQL is a declarative language where JavaScript is imperative language.
[SQL Select]
How do you retrieve specific columns from a database table?
select (keyword) “col title”
from (clause) “table name”
[SQL Select]
How do you filter rows based on some specific criteria?
where (clause) “category” comparison operator ‘value’
which returns true or false
[SQL Select]
What are the benefits of formatting your SQL?
Makes it easier to read the information
[SQL Select]
What are four comparison operators that can be used in a where clause?
=, !=, <, >
[SQL Select]
How do you limit the number of rows returned in a result set?
limit num;
[SQL Select]
How do you retrieve all columns from a database table?
select (keyword) *
[SQL Select]
How do you control the sort order of a result set?
order by “category” desc/asc
[SQL Insert]
How do you add a row to a SQL table?
insert into “table name” (“cat1”, “cat2”, “cat3”, “cat4”)
values (“val1”, “val2”, “val3”, “val4”)
[SQL Insert]
What is a tuple?
a list of values
[SQL Insert]
How do you add multiple rows to a SQL table at once?
write multiple tuples separated by commas
[SQL Insert]
How do you get back the row being inserted into a table without a separate select statement?
returning *;
[SQL Update]
How do you update rows in a database table?
update “table”
set “category name” = ‘value’
where “category” = ‘original value’;
[SQL Update]
Why is it important to include a ‘where’ clause in your update statements?
If omitted, will update the value of every row
[SQL Delete]
How do you delete rows from a database table?
delete from “table name”
where “category” = ‘value’
returning *;
[SQL Delete]
How do you accidentally delete all rows from a table?
delete from “table name”;
[not including ‘where’ clause]
