postgres

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

relational database management system (rdms); mysql

Question 2

What are some advantages of learning a relational database?

Answer 2

store and modify data in a way that makes data corruption as unlikely as possible

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

sudo service postgresql status

Question 4

What is a database schema?

Answer 4

defines how the data in the relational database should be organized

Question 5

What is a table?

Answer 5

• a list of rows each having the same set of attributes ( attributes are commonly referred to as columns)
• a relation object, which can be one or more, which store the data for the relational database system
• data organized in a row-and-column format similar to a spreadsheet

Question 6

What is a row?

Answer 6

represents a unique record for the respective column attribute

Question 7

what is a column?

Answer 7

contain the column name, data type, and any other attributes for the column

represents a field in the record

column is a set of data values of a particular type

Question 8

What is SQL and how is it different from languages like JavaScript?

Answer 8

way of interacting with relational databases; way of retrieving, creating, and manipulating data in a relational database

js: imperative (tell what to do and how to do it)
sql: declarative (describe the results you want to get)

Question 9

How do you retrieve specific columns from a database table?

Answer 9

using the ‘select’ keyword, i.e.,

select “column”
from “database”

Question 10

How do you filter rows based on some specific criteria?

Answer 10

using the ‘where’ keyword, i.e., where a ‘column’ is equal to a certain ‘value’:

select “column”
from “database”
where “column” = ‘value’;

Question 11

What are the benefits of formatting your SQL?

Answer 11

easier to comprehend

Question 12

What are four comparison operators that can be used in a where clause?

Answer 12

=, !=,

equal, not equal to, less than, greater than

Question 13

How do you limit the number of rows returned in a result set?

Answer 13

use the ‘limit’ keyword followed by an integer; should be at the end of the query request, i.e.,

select “column”
from “database”
limit 3;

Question 14

How do you retrieve all columns from a database table?

Answer 14

using the universal selector (*)

select *
from “database”

Question 15

How do you control the sort order of a result set?

Answer 15

using the ‘order by’ keywords, i.e.,

select *
from “database”
order by “category”

Question 16

How do you add a row to a SQL table?

Answer 16

using the insert keyword/clause, i.e.,

insert from “table”

Question 17

What is a tuple?

Answer 17

a list of values, i.e.,

when you are inserting a new row onto a table, we want to use tuples to define the data in respect to the columns - tuples are usually within ()

values (‘data1’, ‘data2’, ‘data3’)

values are wrapped in single quotes

Question 18

How do you add multiple rows to a SQL table at once?

Answer 18

specifying more than one tuple of values, ie.,

insert from “table” (“column”
values (‘data’)
‘some other data’)
(‘data again’)

Question 19

How do you get back the row being inserted into a table without a separate select statement?

Answer 19

using the returning clause at the end of the commands

Question 20

How do you update rows in a database table?

Answer 20

using the ‘update’ keyword/clause, i.e.,

update from “table”

Question 21

Why is it important to include a where clause in your update statements?

Answer 21

update a specific row rather than updating every row

specificity

Question 22

How do you delete rows from a database table?

Answer 22

using the ‘delete’ keyword/clause, i.e.,

delete from “table”

Question 23

How do you accidentally delete all rows from a table?

Answer 23

not specifying a “where” clause

Question 24

What is a foreign key?

Answer 24

a shared value btwn tables, i.e.,

in database A, I have a column which references another column in database B

links two tables together by a common relation

Question 25

How do you join two SQL tables?

Answer 25

using the ‘join’ clause

combines data from two separate databases into one result set

calls to link two tables by a their respective common column

i.e.,
select *
from “tableA”
join “tableB” using (“column”)

Question 26

How do you temporarily rename columns or tables in a SQL statement?

Answer 26

alias method using dot notation and the keyword ‘as’ , i.e.,

select “table”.”column” as “column1”
“table”.”column” as “column2”

table names are usually aliased in the ‘from’ and ‘join’ clauses

Question 27

What are some examples of aggregate functions?

Answer 27

MIN() returns the smallest value in a given column

MAX() returns the maximum value in a given column.

SUM() returns the sum of the numeric values in a given column

AVG() returns the average value of a given column

COUNT() returns the total number of values in a given column

COUNT(*) returns the number of rows in a table

Question 28

What is the purpose of a group by clause?

Answer 28

put all those with the same value n the one group

Question 29

how to kill a process running at a certain port

Answer 29

run the two commands:

sudo lsof -i tcp:3000

or

sudo lsof -i:8080

note: 3000 represents the port, change as needed; the above command will output the process running at a certain PID (make note of it)

sudo kill -9 PID

note: use the PID from the result of the first command; PID is process ID you want to kill.

Question 30

sql order

Answer 30

SELECT (is used to select data from a database)

FROM (clause is used to list the tables)

WHERE (clause is used to filter records)

GROUP BY (clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns)

HAVING (clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE)

ORDER BY (keyword is used to sort the result-set)