postgreSQL, SQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
a powerful, free, open source Relational Database Management System (RDBMS). It is often cited as the most advanced open source database of its kind.
alternative relational db: MySQL, SQL Server by Microsoft, and Oracle by Oracle Corporation.
What are some advantages of learning a relational database?
Many problem domains can be modeled well using a relational database.
they support good guarantees about data integrity. They can store and modify data in a way that makes data corruption as unlikely as possible.
the most widely used kind of database
satisfies ACID properties
doesnt require a complex structure
What is one way to see if PostgreSQL is running?
‘sudo service postgresql status’
top command
pgweb
is just a web interface for interacting w/ postgresql (so its easier)
there are many web interfaces like pgweb, this is not the only one
psql
what you write in terminal to give commands w/ postgresql
What is a database schema?
defines how the data in a relational database should be organized.
collection of tables
the structure of the whole database, the “blueprint”
What is a table?
list of rows each having the same set of attributes.
defines what attribures(column) each row should have
What is a row?
a single record of data in a table
What is SQL and how is it different from languages like JavaScript?
primary way of interacting with relational databases.
It is a powerful way of retrieving, creating, and manipulating data in a relational database.
SQL is a declarative programming language while JS is a imperative programming language
(declarative: programmers describe the results they want and the programming environment comes up with its own plan for getting those results)
(imperative: you basically tell the JavaScript runtime what to do and how to do it)
How do you retrieve specific columns from a database table?
select “column name”,
“column name”,
etc.
from “name of table”
How do you filter rows based on some specific criteria?
where “column name” = or > or < or != ‘criteria’
What are the benefits of formatting your SQL?
cleaner, easier to read, easier to tell what is going on
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
give num of rows you want at most
How do you retrieve all columns from a database table?
- (an asterisk) instead of column names
How do you control the sort order of a result set?
order by “column name”
*will be ascending order by default
can use order by “column name” desc for it to be in descending order
How do you add a row to a SQL table?
insert into “table name” (column1 name, column2 name, etc)
values (value for column1, value for column2, etc)
What is a tuple?
a list of values (in SQL)
a row/a record
How do you add multiple rows to a SQL table at once?
list tuples separated by commas
How do you get back the row being inserted into a table without a separate select statement?
returning keyword *;
or
returning keyword specific column names;
(if you don’t want entire row returned)
How do you update rows in a database table?
update "table name"
set "column name" = 'new value',
"column name" = 'new value',
etc.
where "column name" = < > != 'value/condition';Why is it important to include a where clause in your update statements?
if there is no where clause, it will update every row of table
How do you delete rows from a database table?
delete from “table name”
where “column name” = > < != ‘value/condition’
How do you accidentally delete all rows from a table?
by not adding a where clause!!
