NPM/PostgreSQL Flashcards
What is NPM?
world's largest software registry. Node Package Manager. consists of 3 distinct components: 1. the website 2. the Command Line Interface 3. the registry
What is a package?
reusable codes.
a Node Modules or contain Node Modules.
is a file or directory and a package.json file.
How can you create a package.json with npm?
npm init –yes
What is a dependency and how to you add one to a package?
dependency - the other modules that this module uses. to specify the packages your project depends on. someone else's code, for the project to run on
You can add dependencies to a package.json file from the command line or by manually editing the package.json file.
from command line: npm install [–save-prod]
manually: add an attribute called ‘dependencies’ that references the name and semantic version of each dependency.
{
"name": "my_package",
"version": "1.0.0",
"dependencies": {
"my_dep": "^1.0.0",
"another_dep": "~2.2.0"
}
}
What happens when you add a dependency to a package with npm?
the package is automatically added as a dependency
What is PostgreSQL and what are some alternative relational databases?
PostgreSQL is an open source object-relational database system (relational database system).
alternatives are Microsoft SQL Server, MySQL, Oracle Database. maybes = MongoDB, Redis, Neo4j
What are some advantages of learning a relational database?
Simple Model. A Relational Database system is the most simple model, as it does not require any complex structuring. Data Accuracy, Easy access to Data. Used very frequently, it’s all shared query language. share the same syntax. compacted very well. good at preventing data duplication. very flexible based on what the data is. Essentially a table
What is one way to see if PostgreSQL is running?
on terminal enter the command / having the top command.
sudo service postgresql status
What is a database schema?
is a collection of tables. defines the structure of databas.
What is a table?
A table is a list of rows each having the same set of attributes. For example, all students in a “students” table could have “firstName”, “lastName”, and “dateOfBirth” attributes. Attributes are commonly referred to as columns
What is a row?
is the actual record of single data.
the table it defines what each row should have, each row is the instance of the data.
What is SQL and how is it different from languages like JavaScript?
is the primary way of interacting with relational databases. way of retrieving, creating, and manipulating data in a relational database.
it differs from JS which is an imperative programming language, whereas SQL is a declarative programming language. => describes the results they want and the programming environment comes up with its own plan for getting the results (like HTML CSS)
How do you retrieve specific columns from a database table?
with SELECT keyword
SELECT “attribute” (if more than one ,)
FROM clause “table”
How do you filter rows based on some specific criteria?
after FROM clause use WHERE clause “column” = > < != ‘field’
What are the benefits of formatting your SQL?
for consistent style and readability
What are four comparison operators that can be used in a where clause?
=, !=, >, <, >= ,<=, < >, BETWEEN
How do you limit the number of rows returned in a result set?
with LIMIT clause (number without ‘’)
How do you retrieve all columns from a database table?
SELECT (keyword with) *
How do you control the sort order of a result set?
ORDER BY (clause ) “column” desc. if desc left blank, ascending by default.
How do you add a row to a SQL table?
INSERT INTO “name of table” (“column”, column”)
VALUES (‘value’, ‘value’)
(‘value’, ‘value’)
RETURNING *;
in the same order as the columns they belong to
What is a tuple?
a list of values. rows. a record stored in a row
How do you add multiple rows to a SQL table at once?
INSERT INTO “name of table” (“column”, column”)
VALUES (‘value’, ‘value’),
(‘value’, ‘value’)
RETURNING *;
in the same order as the columns they belong to
How do you get back the row being inserted into a table without a separate select statement?
RETURNING *;
can also specify columns with the RETURNING keyword
How do you update rows in a database table?
update “products”
set “price” = 200,
“name” = ‘Super ShakeWeight’,
“description” = ‘Makes you ULTRA strong!’
where “productId” = 24;
Why is it important to include a where clause in your update statements?
because it will update every row in the table. essentially the whole column.
How do you delete rows from a database table?
delete from “products”
where “productId” = 24
returning *;
How do you accidentally delete all rows from a table?
when no other keywords were not provided like delete from “products”; will delete the whole table rows
What is a foreign key?
a column that specifically refers to values in a column of another table. is a constraint that one column has to match with another column
How do you join two SQL tables?
FROM “table-name”
JOIN “table-name” USING (“column”)
How do you temporarily rename columns or tables in a SQL statement?
JOIN “table-name” AS “tn” USING (“column”);
What are some examples of aggregate functions?
max(), avg(), count(), sum()
more on
https://www.postgresql.org/docs/current/functions-aggregate.html
aggravate bunch of values into one value, it returns one value. similar to join() in JS and reduce()
What is the purpose of a group by clause?
The GROUP BY Clause is utilized in SQL with the SELECT statement to organize similar data into groups. It combines the multiple records in single or more columns using some functions.
