PostgresQL/SQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
It is a relational database. Alternative relational databases include MySQL, SQL Server by Microsoft and Oracle.
What are some advantages of learning a relational database?
Most widely used type of database. You can store related data easily
What is one way to see if PostgreSQL is running?
Run ‘sudo service postgresql status’ in the terminal. Can also check in your terminal by running the command ‘top’
What is a database schema?
A collection of tables. A description of how the database should be structured
What is a table?
A list of rows with the same set of attributes
What is a row?
A set of related data describing one item
What is SQL and how is it different from languages like JavaScript?
The primary way of interacting with relational databases. It is a declarative programming language where programmers describe the results they want rather than tell it exactly what to do and how to do it.
How do you retrieve specific columns from a database table?
Using the select statement and a comma-separated list of column names, each wrapped in a pair of quotes, followed by the from clause specifying which table to grab the data from and ending with a semicolon.
How do you filter rows based on some specific criteria?
Using the where clause. It needs to evaluate as true or false
What are the benefits of formatting your SQL?
It gives it a consistent style and it’s 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?
Using the limit clause followed by the number you want to limit it by
How do you retrieve all columns from a database table?
By using *
How do you control the sort order of a result set?
Using the order by clause. It is defaulted to ascending, so if you want it descending, you must include ‘desc’ at the end.
How do you add a row to a SQL table?
Using the insert ‘keyword into statement’ followed by the name of the table in quotes and a list of columns individually in quotes being inserted wrapped in parentheses. The values clause and the values being inserted are also wrapped in parentheses in the same order as the columns.
What is a tuple?
A list of values
How do you add multiple rows to a SQL table at once?
Comma separated list of tuples.
How do you get back the row being inserted into a table without a separate select statement?
Using the returning clause.
How do you update rows in a database table?
Using the ‘update’ statement followed by the table in quotes and the set clause reassigning the column to the new value. Include the where clause to target specific rows
Why is it important to include a where clause in your update statements?
Otherwise you update the entire column.
How do you delete rows from a database table?
Using the delete from statement followed by the table to be referenced to and a where clause to select the row/rows.
How do you accidentally delete all rows from a table?
“Delete from ‘products’”
What is a foreign key?
A column that specifically refers to values from another table - a shared attribute
How do you join two SQL tables?
Using the join clause following the ‘from’ clause.Join tables ‘using’ the attribute to join them with.
How do you temporarily rename columns or tables in a SQL statement?
By using “as” after you select the table and column
What are some examples of aggregate functions?
Max, sum, avg, count
What is the purpose of a group by clause?
To separate rows into groups and perform aggregate functions on those groups of rows.
