PostgreSQL + SQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
a Relational Database Management System
uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads
MySQL, SQL, and Oracle SQL
What are some advantages of learning a relational database?
1) let you store relational data– data that connect to one another in some way
2) relational databases support good guarantees about data integrity - developers can set up their database to reject “bad” data and not worried about data being “half written”
3) most widely used kind of database
What is one way to see if PostgreSQL is running?
1) by doing
sudo service postgresql status
to check its status
2) running top command
3) pgweb (the GUI will not work if postgreSQL is not running)
What is a database schema?
its a blueprint of how your data will look
a collection of tables
it defines how the data in a relational database should be organized
What is a table?
a table is where relational databases are stored
a table is a list of rows with the same set of attributes
ex: “customers” table could have “customerId” “firstName” “lastName” “email” “orderNumber”
ex: all students in a “students” table could have “firstName”, “lastName” and “dataOfBirthday” attributes
(attributes are referred to as columns)
What is a row?
a row share the same set of attributes
How do you retrieve specific columns from a database table?
select keyword
list of the attribute name
from the name of the table
How do you filter rows based on some specific criteria?
where clause, attribute name, operator, condition
this expression evaluates to true of false
What are the benefits of formatting your SQL?
make your code more readable
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 follow by maximum number you want
How do you retrieve all columns from a database table?
by using asterisk *
How do you control the sort order of a result set?
order by clause
What is SQL and how is it different from languages like JavaScript?
SQL (similar to CSS and HTML) is a declarative programming language where programmers have to DESCRIBE the results they want and the programming environment (Web browser) comes up with its own plan for getting those results
relational databases interpret SQL and then DYNAMICALLY GENERATE A PLAN OF ACTION to perform the programmer’s commands as efficiently as possible
Whereas JavaScript is an imperative programming language where you tell the JavaScript runtime what to do and HOW TO DO IT
How do you add a row to a SQL table?
insert statement
What is a tuple?
a list of values
How do you add multiple rows to a SQL table at once?
you can specify more than one tuple of values by separating them with a comma
How do you get back the row being inserted into a table without a separate select statement?
using the returning clause
returning *;
instead of * you can use a coma separated list of column names
How do you update rows in a database table?
update statement
Why is it important to include a where clause in your update statements?
including a where clause will target specific row, without it, it will update EVERY row in the table
How do you delete rows from a database table?
delete statement with a where clause
How do you accidentally delete all rows from a table?
by not specifying where / including a where clause
What is a foreign key?
a column (a table identifier column) that is used to establish a link between the data in two tables
the column (aka foreign key) is used in another table (aka the foreign key table)
the VALUE from a foreign table that is used to link to another table??
How do you join two SQL tables?
after SELECTing the table, use the JOIN statement to join another table
