SQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
It is a relation database management system . other relational databases include MySQL, SQL Server by Microsoft, and Oracle
What are some advantages of learning a relational database?
portable skill, and used everywhere
What is one way to see if PostgreSQL is running?
top command,
What is a database schema?
A collection of tables
What is a table?
in terms of relational databases, a table is known as a relation which stores data in rows and attributes(columns)
What is a row?
a row is an instance of a record in that table, that has a set of attributes
What is SQL and how is it different from languages like JavaScript?
Structured Query Language: it is declarative vs imperative like JavaScript.
How do you retrieve specific columns from a database table?
select “columnName”
How do you filter rows based on some specific criteria?
where “column” (=,, !=) ‘criteria’
What are the benefits of formatting your SQL?
readabililty
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 clause
How do you retrieve all columns from a database table?
select *
How do you control the sort order of a result set?
order by “criteria” (desc)
- ascending by default
How do you add a row to a SQL table?
insert into “table” (‘column’(s))
values (‘value’(s))
What is a tuple?
A list of values
How do you add multiple rows to a SQL table at once?
groups in parenthesis separated by commas
How to 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?
update “table”
set “column” = ‘value’
where “value” = ‘value’
Why is it important to include a where clause in your update statements?
without where, it would update all values in that column
How do you delete rows from a database table?
delete from “tableName”
where “column” = ‘value’
How do you accidentally delete all rows from a table?
delete from “tableName”
without specifying where
What is a foreign key?
a column that links the values from one table to another
How do you join two SQL tables?
join keyword
select *
from “table”
join “table2” using (“foreignKey”);
How do you temporarily rename columns or tables in a SQL statement?
(for columns) : select “table”.”column” as “name”
(for tables) : from “table” as “name”
What are some examples of aggregate functions?
count(), sum(), avg()
What is the purpose of a group by clause?
used to separate different rows into groups
