postgres/SQL Flashcards
what is PostgreSQL and what are some alternative relational databases?
powerful, free open source Relational Database Management system (RDBMS)
- relational database - a digital database based on the relational model of data
- relational model - approach to managing data using a structure and language consistent with first-order predicate logic, where all data is represented in forms of tuples, grouped into relations
- alternative relational databases
- -SQL server
- -oracle
what are some advantages of learning a relational database?
support good guarantees about data integrity
store and modify data in a way that makes data corruption as unlikely as possible
works with SQL language
what is one way to see if PostgreSQL is running?
sudo service postgresql status, in the terminal
what is a database schema?
a collection of tables
- a schema defines how the data in a relational database should be organized
what is a table?
a table is a list of rows each having the same set of attributes
what is a row?
also called a tuple, represents a single, implicitly structured data item in a table
-a single instance of a record
what is SQL and how is it different from langauges like JavaScript?
domain-specific language design for managing data held in relational database management systems
it’s a declarative programming languages
- in declarative languages, programmers describe the results they want and the programming environment comes up with its own plan for getting those results
- JavaScript is an imperative language
how do you retrieve specific columns from a database table?
Select “name”
From “table”
how do you filter rows based on some specific criteria?
where “category” = ‘cleaning’
what are the benefits of formatting your SQL
easier to read code
what are four comparison operators that can be used in a where 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
- default ascending
- desc for descending order
how do you add a row to a SQL table?
insert into “table” (“name”, “description”, “price”)
values (‘Ostrich Pillow’, ‘Feel Comfy and Cozy’, 99)
what is a tuple?
a list of values