PostgreSQL Flashcards
What is PostgreSQl and what are some alternative relational databases?
Open source object-relational database system
alternatives: mySQL, SQL server, Oracle
What are some advantages of learning a relational database?
You work with the SQL language, and a majority of databases use SQL
What is one way to see if PostgreSQL is running?
sudo service postgresql status
What is a database schema?
collection of tables in sql
What is a table?
list of rows each having the same set of attributes.
ex. student table can all have attributes: “firstName” , “lastName” , and “dateOfBirth
What is a row?
a record of the table with data
What is SQL and how is it different from languages like JavaScript?
Structured Query Language - it is declarative meaning programmers describe the results they want and the programming environments gets those results
How do you retrieve specific columns from a database table?
Select “columnName” from “tableName”;
How do you filter rows based on some specific criteria?
Where “” = ‘’
What are the benefits of formatting your SQL?
easier to read
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?
*
How do you control the sort order of a result set?
order by
How do you add a row to a SQL table?
Insert into “tableName” (“columnName”) values (‘valueName’) returning *;
What is a “tuple”?
A tuple is simply a row contained in a table in the tablespace.
How do you add multiple rows to a SQL table at once?
Insert into “tableName” (“columnNames”, etc etc.) values (‘value’, etc etc) returning *;
How do you get back the row being inserted into a table without a separate select statement?
returning *
How do you update rows in a database table?
returning *
How do you update rows in a database table?
Update “table” set “columnName” = ‘value’ where “columnName = ‘newValue’;
Why is it important to include a where clause in your update statements?
So you dont update everything inside of the table
How do you delete rows from a database table?
Delete from “tableName” where “columnName” = value returning *;
How do you accidentally delete all rows from a table?
Delete from “tableName” DONT DO THIS!!
What is a foreign key?
Value in column used to link two tables
How do you join two SQL tables?
join
How do you temporarily rename columns or tables in a SQL statement?
Aliasing use as
What are some examples of aggregate functions?
min(), sum(), and every()
What is the purpose of a group by clause?
Instead, you want to separate rows into groups and perform aggregate functions on those groups of rows. This is done with a group by clause.
