d1 - data and database entities

Question 1

what will this postgres command do: \l

Answer 1

lists your databases

Question 2

what will this postgres command do: \du

Answer 2

list of users

Question 3

what will this postgres command do: \c

Answer 3

connect to a database

ex. \c

Question 4

what will this postgres command do: \d

Answer 4

show details of table

Question 5

what will this postgres command do: \dt

Answer 5

show list of tables

Question 6

when using a database and pair programming do we both need to setup the DB?

Answer 6

yes! because the DB is only local

Question 7

what should you do before each time you use homebrew to install somehting?

Answer 7

update homebrew using: brew update

Question 8

what are some ways to do unique identities in postgres?

Answer 8

NOT NULL

UNIQUE

Question 9

what will happen when user inputs something null? How does NOT NULL in postgres effect this?

Answer 9

if someone puts some null entity input, an error will occur

Question 10

what will happen when a user inputs something with the same name and identifier? How does UNIQUE in postgres effect this?

Answer 10

postgres will check rows to see if something already exists, if so, it’ll throw an error

Question 11

what is postbird?

Answer 11

it’s a gui for postgres

Question 12

What does RDBMS stand for?

Answer 12

relational database management systems (basically a database)

Question 13

What are some populare RDBMS?

Answer 13

oracle
SQL server
MySQL
postgreSQL

Question 14

In postgress when typing what should be in uppercase and what should be in lowercase?

Answer 14

usernames should be in lowercase

*if you wrap the password in “ “ you can make it uppercase but sequilize will do it for us so don’t worry too much

Question 15

how do you see who your logged in as in postgres?

Answer 15

select CURRENT_USER

Question 16

if there’s an ‘=’ in the name in postgres that’s good, but what if there’s a ‘-‘?

Answer 16

’-‘ means that your continuing on the same line. So, make sure you end each line with a ‘;’

to get out of that hit ‘;’ and enter

Question 17

What does SQL stand for?

Answer 17

structured query language (a query is a question. in this case we ask the database a question)

Question 18

what does -U stand for in postgres?

Answer 18

it stands for user

Question 19

what command do you use to access the PostgreSQL server?

Answer 19

psql postgres

Question 20

what does the postgreSQL data type mean: VARCHAR(n)

Answer 20

a variable-length character string that lets you store up to n charactars.

Question 21

what does the postgreSQL data type mean: DECIMAL(p,s)

Answer 21

a floating point number with p digits and s number of places after the decimal point

Question 22

what does the postgreSQL data type mean: INT

Answer 22

INT is a 4-byte integer

Question 23

what does the postgreSQL data type mean: BOOLEAN

Answer 23

a boolean value. SQL accepts standard boolean vals true, false, or null

Question 24

what are the postgreSQL boolean values that are accepted?

Answer 24

True

• true
• t
• ‘true’
• ‘yes’
• ‘y’
• ‘1’

False

• false
• ‘f’
• ‘no’
• ‘n’
• ‘0’

Question 25

What does SELECT * mean?

Answer 25

it gives you back all of the rows in postgres table

Question 26

What’s the format to select by columns?

Answer 26

SELECT [column] FROM [name]

ex. SELECT name FROM puppies

Question 27

how do you select multiple columns?

Answer 27

SELECT name
, age_yrs
, weight_lbs
FROM puppies;

we can also do this on a one liner, but it’s better to do this above when doing multiple especially hundreds of columns

Question 28

what does the WHERE query do?

Answer 28

It allows us to filter parts from our SELECT query. When using this be sure to use ‘ ‘ not “ “ otherwise it’ll think you’re looking for uppercase letters

Question 29

How to use a WHERE clause to get a list of values?

Answer 29

WHERE [column] IN (‘value1’, ‘value2’, ‘value3’).

Question 30

How to set the order you want to receive info back from postgres?

Answer 30

SELECT name, breed FROM puppies

ORDER BY name;

Question 31

When would you use the LIMIT clause? What about OFFSET?

Answer 31

when you have millions or a lot of data and you want to limit to a certain number of rows of data returned.

To see next rows after the limit you could use OFFSET

EX. SELECT name, breed
FROM puppies
ORDER BY age_yrs
LIMIT 100 OFFSET 100;

Question 32

What’s the logical operator: ALL

Answer 32

TRUE if all of the subquery values meet the condition.

Question 33

What’s the logical operator: AND

Answer 33

TRUE if all the conditions separated by AND are TRUE.

Question 34

What’s the logical operator: ANY

Answer 34

TRUE if any of the subquery values meet the condition.

Question 35

What’s the logical operator: BETWEEN

Answer 35

TRUE if the operand is within the range of comparisons.

Question 36

What’s the logical operator: EXISTS

Answer 36

TRUE if the subquery returns one or more records.

Question 37

What’s the logical operator: IN

Answer 37

TRUE if the operand is equal to one of a list of expressions.

Question 38

What’s the logical operator: LIKE

Answer 38

TRUE if the operand matches a pattern (accepts “wildcards”).

It uses the % sign
ex. NOT LIKE ‘%Shepherd’
% can be put before word, after word, or before and after (it’s like when you search the library database)

Question 39

What’s the logical operator: NOT

Answer 39

Displays a record if the condition(s) is NOT TRUE.

Question 40

What’s the logical operator: OR

Answer 40

TRUE if any of the conditions separated by OR is TRUE.

Question 41

What’s the logical operator: SOME

Answer 41

TRUE if any of the subquery values meet the condition.

Question 42

what is the following comparison operator: <>

Answer 42

not equal to

Question 43

What is the following comparison operator: !=

Answer 43

not equal to

Question 44

What is the following comparison operator: !< and !>

Answer 44

not less than

not greater than

Question 45

What does id SERIAL and PRIMARY KEY do?

Answer 45

SERIAL auto assigns an identifier. it’s sequential.

PRIMARY KEY allows the id to be used as a foreign key

Question 46

What’s the syntax to create a new database?

Answer 46

CREATE DATABASE «your database’s name» WITH OWNER «your user name»;

Question 47

What does the signifier NUMERIC do?

Answer 47

it’s a signifier for the number of things. syntax: NUMERIC(num1, num2)

• first num is the number of total digits including the decimal
• second num is the number of decimal places needed

ex. NUMERIC(3, 2) - accepts 2.35