d1 - data and database entities Flashcards
what will this postgres command do: \l
lists your databases
what will this postgres command do: \du
list of users
what will this postgres command do: \c
connect to a database
ex. \c
what will this postgres command do: \d
show details of table
what will this postgres command do: \dt
show list of tables
when using a database and pair programming do we both need to setup the DB?
yes! because the DB is only local
what should you do before each time you use homebrew to install somehting?
update homebrew using: brew update
what are some ways to do unique identities in postgres?
NOT NULL
UNIQUE
what will happen when user inputs something null? How does NOT NULL in postgres effect this?
if someone puts some null entity input, an error will occur
what will happen when a user inputs something with the same name and identifier? How does UNIQUE in postgres effect this?
postgres will check rows to see if something already exists, if so, it’ll throw an error
what is postbird?
it’s a gui for postgres
What does RDBMS stand for?
relational database management systems (basically a database)
What are some populare RDBMS?
oracle
SQL server
MySQL
postgreSQL
In postgress when typing what should be in uppercase and what should be in lowercase?
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
how do you see who your logged in as in postgres?
select CURRENT_USER
if there’s an ‘=’ in the name in postgres that’s good, but what if there’s a ‘-‘?
’-‘ 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
What does SQL stand for?
structured query language (a query is a question. in this case we ask the database a question)
what does -U stand for in postgres?
it stands for user
what command do you use to access the PostgreSQL server?
psql postgres
what does the postgreSQL data type mean: VARCHAR(n)
a variable-length character string that lets you store up to n charactars.
what does the postgreSQL data type mean: DECIMAL(p,s)
a floating point number with p digits and s number of places after the decimal point
what does the postgreSQL data type mean: INT
INT is a 4-byte integer
what does the postgreSQL data type mean: BOOLEAN
a boolean value. SQL accepts standard boolean vals true, false, or null
what are the postgreSQL boolean values that are accepted?
True
- true
- t
- ‘true’
- ‘yes’
- ‘y’
- ‘1’
False
- false
- ‘f’
- ‘no’
- ‘n’
- ‘0’
What does SELECT * mean?
it gives you back all of the rows in postgres table
What’s the format to select by columns?
SELECT [column] FROM [name]
ex. SELECT name FROM puppies
how do you select multiple columns?
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
what does the WHERE query do?
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
How to use a WHERE clause to get a list of values?
WHERE [column] IN (‘value1’, ‘value2’, ‘value3’).
How to set the order you want to receive info back from postgres?
SELECT name, breed FROM puppies
ORDER BY name;
When would you use the LIMIT clause? What about OFFSET?
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;
What’s the logical operator: ALL
TRUE if all of the subquery values meet the condition.
What’s the logical operator: AND
TRUE if all the conditions separated by AND are TRUE.
What’s the logical operator: ANY
TRUE if any of the subquery values meet the condition.
What’s the logical operator: BETWEEN
TRUE if the operand is within the range of comparisons.
What’s the logical operator: EXISTS
TRUE if the subquery returns one or more records.
What’s the logical operator: IN
TRUE if the operand is equal to one of a list of expressions.
What’s the logical operator: LIKE
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)
What’s the logical operator: NOT
Displays a record if the condition(s) is NOT TRUE.
What’s the logical operator: OR
TRUE if any of the conditions separated by OR is TRUE.
What’s the logical operator: SOME
TRUE if any of the subquery values meet the condition.
what is the following comparison operator: <>
not equal to
What is the following comparison operator: !=
not equal to
What is the following comparison operator: !< and !>
not less than
not greater than
What does id SERIAL and PRIMARY KEY do?
SERIAL auto assigns an identifier. it’s sequential.
PRIMARY KEY allows the id to be used as a foreign key
What’s the syntax to create a new database?
CREATE DATABASE «your database’s name» WITH OWNER «your user name»;
What does the signifier NUMERIC do?
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
