PostgreSQL Flashcards
What is PostgreSQL?
A free, open source Relational Database Management System. That provides robust features, meets standards compliance, and reliability.
What are some advantages of learning a relational database?
- To understand and solve problems based around storing and pulling up data.
- To be able to reject bad data and not worry about half written data
What is one way to see if PostgreSQL is running?
- Check to see if postgresql processes are running in top command
- Type in sudo service postgresql status
What are some alternative relational databases?
MySQL, SQL Server by Microsoft, and Oracle by Oracle.
What is a database schema?
A collection of tables.
- Defines how data in a relational database is organized.
What is a table?
A relational database that stores data in relations.
- It is a list of rows each having the same set of attributes.
What is a row?
A list from a table that has the same attributes.
What is SQL?
Stands for Structured Query Language
- Primary way of interacting with relational databases
- Retrieves, creates, and manipulates data in a relational database
How do you retrieve specific columns from a database table?
select keyword
comma-separated list of column names surrounded by “ “
from clause which specifies which table to get data
ends with a semicolon
How do you filter rows based on some specific criteria?
Using the where clause
- Comes after the from clause
- Column headers will have double quotes while text values have single quotes
What are the benefits of formatting your SQL?
Creates a consistent style and therefore makes SQL easier to read
How is SQL different from languages like JavaScript?
SQL is a declarative programming language.
- Programmer describes the results they want and the program comes up with a plan to get those results
What are four comparison operators that can be used in a where clause?
equals sign =
greater than >
less than <
not equal !=
How do you limit the number of rows returned in a result set?
Using the limit clause
- This will come last in a select statement
- Is a literal integer
How do you retrieve all columns from a database table?
Using an asterisk *
How do you control the sort order of a result set?
Include an order by clause
- Comes after the from clause
- Followed by a column name within “ “
- Default order is ascending
How do you add a row to a SQL table?
insert into “tableName” (“columnName”, “columnName”…etc)
values (‘valueName’, ‘valueName’… etc);
No need for id values because tables will automate that
What is a tuple?
A list of values within SQL
How do you add multiple rows to a SQL table at once?
Have another line that contains a tuple
Use a comma after every subsequent tuple
How do you get back the row being inserted into a table without a separate select statement?
Use a returning clause
How do you update rows in a database table?
update “tableName”
set “columnName” = value
where “rowId” = value;
Why is it important to include a where clause in your update statements?
If a where statement isn’t included then it will update the whole table as opposed to the targeted row
How do you delete rows from a database table?
delete from “tableName”
where “id” = value
returning *;
returning clause is optional
How do you accidentally delete all rows from a table?
delete from “products”;
Not including a where clause…
How do you update multiple columns in a row?
update “tableName”
set “columnName” = value,
“columnName” = value
where “id” = value;
How do you delete multiple rows in SQL?
delete from “tableName”
where “columnName” = value
and “columnName” = value
What safeguard do you use to protect data when making changes?
Do a transaction:
Use begin command on the sql file you want to do changes
To continue:
Use rollback command to go back
Commit command to commit
What is a foreign key?
An id that links one table to another table
How do you join two SQL tables?
select *
from “tableName”
join “tableName2” using (“tableName2Id”);
How do you temporarily rename columns or tables in a SQL statement?
Alias the column names by doing:
select “tableName”.”columnName” as “name”,
“tableName2”.”columnName2” as “name2”
from “tableName”
join “tableName2” using (“tableName2Id”);
What are some examples of aggregate functions?
max( ) avg( ) count( ) min( ) sum( ) every( )
What is the purpose of a group by clause?
To subdivide rows in a subset into groups and performs the aggregate in each group
To get aggregates from groups of data as opposed to the data in its entirety
When would you use an aggregate function?
After the select clause
Example:
select avg(“columnName”) as “columnAlias”
from “tableName”;
