Unit 3 - SQL Queries

Question 1

ALTER TABLE

Answer 1

A statement that makes structural changes to a table.

Question 2

AVG

Answer 2

The function that calculates the average of non-null values.

Question 3

AVG Function

Answer 3

A function that calculates the average value of the entries in a numeric column. It is commonly used in statistical calculations and data analysis.

Question 4

Aggregate

Answer 4

To combine individual items or objects and treat the combined set as a single unit.

Question 5

Aggregate Function

Answer 5

A function that operates on multiple records and returns a single value that summarizes them, such as producing a count, sum, or average.

Question 6

Boolean Expression

Answer 6

An expression that can be evaluated as either true or false.

Question 7

CHECK

Answer 7

A constraint that uses a Boolean expression to evaluate whether the value is valid when inserted into or updated in the column.

Question 8

COALESCE

Answer 8

This function in PostgreSQL returns the first non-null value from a list of expressions. It evaluates multiple arguments in order, returning the first non-null value encountered.

Question 9

COUNT

Answer 9

The function that returns the number of rows in a result set.

Question 10

Cascading Order

Answer 10

A multilevel sequence used for complex sorting operations.

Question 11

Casting

Answer 11

To change the data type for all entries in a column when that column’s data type changes.

Question 12

Clause

Answer 12

An individual command used in a statement.

Question 13

Constraint

Answer 13

A rule that imposes limitations on what can be done. In the context of a database table, a constraint limits what data can be entered.

Question 14

DISTINCT

Answer 14

A keyword in PostgreSQL that enables you to limit query results to unique values from a specified column in a result set. It can be used on its own or with an aggregate function such as SUM.

Question 15

DROP TABLE

Answer 15

A SQL statement that removes a table from a database.

Question 16

FROM Clause

Answer 16

The part of a SQL SELECT statement that identifies which tables should be used as the data source.

Question 17

Foreign Key

Answer 17

The term foreign key refers to when a primary key from one table appears in another.

Question 18

GROUP BY

Answer 18

A clause that calculates aggregates within a group of rows, with groups created based on values in one or more specified columns.

Question 19

HAVING Clause

Answer 19

A clause that enables you to filter the results of a GROUP BY clause according to specified conditions.

Question 20

INSERT INTO

Answer 20

A statement that creates a new row or updates an existing row in a table.

Question 21

LIMIT Clause

Answer 21

A clause that limits the number of rows returned by a query.

Question 22

MAX

Answer 22

The function that returns the largest of the non-null values.

Question 23

MAX Function

Answer 23

A function that reports the largest value for a certain column in a dataset or result set.

Question 24

MIN

Answer 24

The function that returns the smallest of the non-null values.

Question 25

MIN Function

Answer 25

A function that reports the smallest value for a certain column in a dataset or result set.

Question 26

OFFSET Clause

Answer 26

This clause specifies a maximum number of rows to return from a query to restrict the number of rows returned. like when you skip pages on a search, it skips the previous data to show the next page (pagination)

Question 27

Pseudo-Type

Answer 27

A data type that is used on data that is not directly stored in a table, such as data in a result set.

Question 28

RANK

Answer 28

This function returns the rank of the row based on the value. A row with the same value as the prior row will return the same rank.

Question 29

RELEASE

Answer 29

A statement that enables you to release a savepoint that is no longer needed.

Question 30

RETURNING Clause

Answer 30

A clause that can be added to an UPDATE/ INSERT statement to return the updated rows so the results can be inspected.
use RETURNING * at the end (or column names

Question 31

ROLLBACK

Answer 31

A statement that enables you to undo a transaction.

Question 32

ROUND Function

Answer 32

A function that rounds numeric values to a specified number of decimal places or to the nearest whole number.

Question 33

Referential Integrity

Answer 33

A set of constraints that ensure data and relationship integrity by enforcing rules about primary key constraints, foreign keys, and cascading operations.

Question 34

Result Set

Answer 34

The results returned from a SELECT statement.

Question 35

Return True

Answer 35

To match the criteria required by a WHERE clause.

Question 36

SAVEPOINT

Answer 36

A statement that creates a saved point in a transaction that you can roll back to with the ROLLBACK TO statement.

Question 37

SELECT Clause

Answer 37

The part of a SELECT statement that specifies which columns should be included.

Question 38

SELECT Statement

Answer 38

A SQL statement that retrieves rows of data from one or more tables.

Question 39

STDDEV

Answer 39

This function returns the standard deviation based on the non-null values.

Question 40

SUM

Answer 40

The SUM function returns the sum of all non-null values.

Question 41

Schema Browser

Answer 41

A list of table names, column names, and data types contained within a database.

Question 42

Serial

Answer 42

In Postgres, the unique identifier assigned to each record as it is entered into a table. (Can use IDENTITY instead as a SQL standard)

Question 43

Statement

Answer 43

A standalone instruction that the DBMS can interpret and execute.

Question 44

TRUNC() Function

Answer 44

A function in PostgreSQL that truncates a numeric value to a specified number of decimal places or removes the decimal part altogether.

TRUNC(numeric_expression, decimal places)

Question 45

Transaction

Answer 45

A set of steps that add, modify, or delete data in a database.

Question 46

UNIQUE

Answer 46

UNIQUE constraints ensure that all values in a column are different.

Question 47

UPDATE

Answer 47

A statement that updates the content of one or more columns for one or more rows.

Question 48

VARIANCE

Answer 48

This function returns the variance of non-null values.

Question 49

WHERE Clause

Answer 49

A SQL clause that applies conditions to filter the result set.

Question 50

CREATE INDEX

Answer 50

Indexes the specified column so that searches based on it run quickly.

Question 51

CHECK

Answer 51

Ensures that the values in a column satisfy a specific condition

Question 52

PRIMARY KEY

Answer 52

Designates the primary key field in the table, which applies the *UNIQUE and NOT NULL constraints and indexes the field.

Question 53

CREATE TABLE pet (
pet_id int PRIMARY KEY,
pet_name VARCHAR(40) NOT NULL,
species VARCHAR(40) NOT NULL,
breed VARCHAR(40) NULL,
Birthdate DATE,
owner_id int,
CONSTRAINT fk_owner
FOREIGN KEY (owner_id)
REFERENCES owner(owner_id)
);

Answer 53

CONSTRAINT fk_owner defines which table is being connected to; fk stands for foreign key

FOREIGN KEY (owner_id) defines which field in the pet table should be on the “many” side of the relationship. You know it’s the main side because it’s the foreign key;
recall that the primary key side is the “one” side.

REFERENCES owner(owner_id) defines which field in the owner table should be on the “one” side of the relationship.

Question 54

PostgreSQL - difference between Sequence and Serial

Answer 54

Serial auto increments by 1, whereas sequence lets you customize incrementation, start value, and min/max values

Question 55

Generic way of listing all foreign keys in a database for every table

Answer 55

SELECT conrelid::regclass AS table_name,
conname AS foreign_key,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = ‘f’
AND connamespace = ‘public’::regnamespace
ORDER BY conrelid::regclass::text, contype DESC;

Question 56

Delete table without needing to check for related tables

Answer 56

CASCADE and IF EXISTS

Question 57

What does this do?
COALESCE(SUM(total), 0)

Answer 57

Ensures that if the result of SUM(total) is NULL (which happens if there are no rows matching the condition), it defaults to 0.

Question 58

IDENTITY

Answer 58

SQL Standard to define an auto-incrementing column.

EX: grade_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

GENERATED ALWAYS always assigns an incrementing value for each record

GENERATED BY DEFAULT assigns an incrementing value only if a specific value is not entered for it