postgresql Flashcards
What is PostgreSQL and what are some alternative relational databases?
relational database management system, alternative –> Mysql, oracle, sql microsoft
What are some advantages of learning a relational database?
data integrity, storing related data, client server model
learn sql –> what everyone uses (sql)
What is one way to see if PostgreSQL is running?
top
sudo service postresql status
What is a database schema?
schema is a collection of tables, in this case a database with a collection of tables
What is a table?
rows and attributes as columns with data
What is SQL and how is it different from languages like JavaScript?
structured query language, it is a declarative language
How do you retrieve specific columns from a database table?
How do you filter rows based on some specific criteria?
What are the benefits of formatting your SQL?
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?
How do you retrieve all columns from a database table?
How do you control the sort order of a result set?
- Select “columns” …
- Where “column” some operator and what you want it to look like
- data can be relational
- = < > !=
- Limit
- Select *
- order by “column” desc
How do you add a row to a SQL table?
What is a tuple?
How do you add multiple rows to a SQL table at once?
How do you get back the row being inserted into a table without a separate select statement?
insert into “table name” (“columnName”…)
values (‘value’…)
returning *; –> returns row inserted
tuple is a list of values
multiple lines of value rows
How do you update rows in a database table?
Why is it important to include a where clause in your update statements?
UPDATE “table”
SET “column name” = ‘value’
WHERE “column name” = ‘value’
How do you delete rows from a database table?
How do you accidentally delete all rows from a table?
DELETE from “tableName”
WHERE “columnName” = ‘value’
What is a foreign key?
How do you join two SQL tables?
How do you temporarily rename columns or tables in a SQL statement?
foreign key is a column that is out of reach of one of the tables and needs to be accessed by using a junction table
join two sql tables using using
SELECT “column Names”
FROM “tableName”
What are some examples of aggregate functions?
What is the purpose of a group by clause?
sum()
max()
count()
to group rows – colapses it down
