PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

pronounced “Post Gress Queue El”, PostgreSQL is a powerful, free, open source Relational Database Management System (RDBMS).
Alternatives - MySQL, SQL Server by MIcrosoft, and Oracle by Oracle Corporation.

Question 2

What are some advantages of learning a relational database?

Answer 2

They can store and modify data in a way that makes data corruption as unlikely as possible.
You can store related data.
Relational Databases are the most widely used kind of database.

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

use the command ‘ sudo service postgresql status ‘.

Question 4

psql

Answer 4

the command to acces PostgreSQL in the terminal.

Question 5

pgweb

Answer 5

is just a graphical user interface for the PostgreSQL database.

Question 6

What is a database schema?

Answer 6

A collection of tables , and defines how the data in a relational database should be organized.
defines the structure of a database.

Question 7

What is a table?

Answer 7

A table is a list of rows each having the same set of attributes.
Ex - all students in a “students” table could have “firstName”, “lastName”, and “dateOfBirth” attributes.
Attributes are commonly referred to as tables.

Question 8

What is a row?

Answer 8

each row in a table is a record of data
a row is a data record within a table.
holds data that correlates to the attribute or column .
the stored or collected data.

Question 9

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

Answer 9

SQL is a declarative programming language which means you describe the results you want and then its up to the programming environment to come up with a plan for getting the results, whereas JavaScript is imperative which means you tell the program / runtime what to do and how to do it.

Question 10

How do you retrieve specific columns from a database table?

Answer 10

use the select keyword followed by a comma-separated list of column names, each surrounded by “ double quotes.
select column
from table name;

Question 11

How do you filter rows based on some specific criteria?

Answer 11

use the where clause, which uses operators.

Question 12

What are the benefits of formatting your SQL?

Answer 12

SQL does not have to be indented, but you should do it anyway for consistent style and therefore readability.

Question 13

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

Answer 13

=, >, <, !=

Question 14

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

Answer 14

The limit clause includes a literal integer number with no quotes to specify the maximum number of rows that should be returned.

Question 15

How do you retrieve all columns from a database table?

Answer 15

use the * (asterick) after the select keyword.

Question 16

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

Answer 16

Order by clause
The default sort order of the results is ascending order, but you can switch it by adding descending keyword in the order by clause.

Question 17

How do you add a row to a SQL table?

Answer 17

insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);
use insert keyword, then the table to insert into is specified in “ double quotes.

Question 18

What is a tuple?

Answer 18

In SQL, a list of values is referred to as a tuple.

values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);

Question 19

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

Answer 19

insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’),
(‘Tater Mitts’, ‘Scrub some taters!’, 6, ‘cooking’)
returning *;
insert however many tuples of values you specify and seperate them by commas.

Question 20

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

Answer 20

use the returning clause, followed by an asterick and semicolon.
returning *;

Question 21

How do you update rows in a database table?

Answer 21

use the Update keyword followed by the name of table in double quotes
underneath that use the Set keyword followed by what column you want to update, an equal signs then
whatever value you want to update it to.
update “products”
set “price” = 100;

Question 22

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

Answer 22

if you dont specify a where clause whatever you update the value to will change for every row for that column.

Question 23

How do you delete rows from a database table?

Answer 23

delete from “products”
where “productId” = 24
use the Delete From keyword followed by table name in double quotes, then the Where keyword followed by column name in double quotes, then an operator and a value.

Question 24

How do you accidentally delete all rows from a table?

Answer 24

delete from “tableName”;

Use the Delete From keyword then the table name in double quotes.

Question 25

What is a foreign key?

Answer 25

is a column with values that appears in different tables that can be linked to different tables.

Question 26

How do you join two SQL tables?

Answer 26

select “products”.”name” as “product”,
“suppliers”.”name” as “supplier”
from “products”
join “suppliers” using (“supplierId”);

select tableName followed by a period then the column name
from tableName
join tableName that has the foreign key using the foreign key.

Question 27

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

Answer 27

use the ‘ as ‘ keyword.

select “tableName”.”columnName” as “column1”.

from “tableName” as “table1”.

Question 28

What are some examples of aggregate functions?

Answer 28

MAX( ), AVG( ), COUNT( ), SUM( )

Question 29

What is the purpose of a group by clause?

Answer 29

The GROUP BY clause is used to group together those rows in a table that have the same values in all the columns listed.

A SQL command that is used to group rows that have the same values.