postgres/sql Flashcards
Why do we use databases in Web development?
to store data and information
What is PostgreSQL and what are some alternative relational databases?
PostgreSQL is a data management system and other alternatives include Oracle, MySQL
What are some advantages of learning a relational database?
Ease of use, open source, flexibility
What is one way to see if PostgreSQL is running?
sudo service postgresql status
What is a database schema?
it shows how a data is organized within the database
What is a table?
rows and columns used to store data titles and the data itself
What is a row?
the horizontal part of the table
What is an attribute and what other names are used to describe them?
an attribute in postgresql is the column in the postgresql table it shows the information of the data type
What is SQL and how is it different from languages like JavaScript?
Structured query language (SQL) is a way to interact with databases and it is different from JavaScript you tell it how to do it while in SQL you ask what you want
How do you retrieve specific columns from a database table?
by using the select clause
(ex. select “firstName”, “lastName”
from “table_name”)
How do you filter rows based on some specific criteria?
by using the where clause
(ex. where “lastName” = ‘Lee’)
What are the benefits of formatting your SQL?
for readability purposes
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?
by using the limit clause
(ex. limit 10;)
How do you retrieve all columns from a database table?
by using an *
(ex. select *
from “table_name”;)
How do you control the sort order of a result set?
by using the order by clause
(ex. order of “price”;)
How do you add a row to a SQL table?
by using the insert into keyword
(ex. insert into “students” (“name”, “age”))
What is a tuple?
the list of values
How do you add multiple rows to a SQL table at once?
by adding multiple tuples
How do you get back the row being inserted into a table without a separate select statement?
by using the returning keyword
(ex. returning *;)
How do you update rows in a database table?
by using the update keyword
(ex. update “student”
set “name” = “Timothy Lee”
where “studentId” = 2;)
Why is it important to include a where clause in your update statements?
So it knows which row to update
How do you delete rows from a database table?
by using the delete keyword
(ex. delete from “student”
where “studentId” = 2;)
How do you accidentally delete all rows from a table?
by not putting where to delete
(ex. delete from “student”;)
What is a foreign key?
it is a primary key from a different table
How do you join two SQL tables?
by using the join keyword
(ex. select “p”.”amount”, “c”.”firstName”,
from “payment” as “p”
join “customers” as “c” using (“customerId”)
)
How do you temporarily rename columns or tables in a SQL statement?
by using the as keyword
(ex. select g.name as genre
from genres as g)
What are some examples of aggregate functions?
sum(), avg(), count(), min(), max(), total()
What is the purpose of a group by clause?
so that the aggregate function is applied to only that group
What are JavaScript classes?
classes are used to create objects that have properties and methods, they also provide a more structured way to define and create objects
When would you want to use a class?
How do you declare a class?
by using the class keyword
(ex.
class Shape {
constructor (area, circumference)
}
)
How do you inherit from another class?
by using the extends keyword
(ex.
class Square extends Shape {
}
)
Why would you want to inherit from another class?
so that you can reuse the code instead of writing it all over again
How do you add methods and properties to a class?
by adding it within the class object using functions