EXAM 2 (Mods 3-5)

Question 1

What is the computer’s order of execution for the select statement clauses?

Answer 1

1: FROM
2: WHERE
3: GROUP BY
4: HAVING
5: SELECT
6: ORDER BY

Question 2

What does CRUD stand for?

Answer 2

Create, Read, Update, Delete

Question 3

Do the Select statement commands/clauses fall within DML, QL, or DDL

Answer 3

QL

Question 4

What is the meaning of the following Reg expression operator;
<>

Answer 4

not equal

Question 5

Which “optional” SELECT clause is mandatory for data aggregation?

Answer 5

GROUP BY

Question 6

Which select statement clause(s) support aliases?

Answer 6

all but WHERE

Question 7

Which clause(s) don’t support aggregate functions?

Answer 7

WHERE & FROM

Question 8

What are the three purposes of the select statement and the associated clause responsible for each?

Answer 8

1) Projection -> SELECT
2) Selection -> WHERE
3) Join -> FROM (implicit)

Question 9

What are the two main join syntax styles in SQL?

Answer 9

Implicit- FROM/WHERE
Explicit- <”some join type”> JOIN ON

Question 9

How are tables most commonly joined

Answer 9

Equi joins on Pk-Fk

Question 10

Can outer joins be only implicit or only explicit?

Answer 10

explicit

Question 11

Which type of join will produce the fewest # of rows back?

Answer 11

Inner Join

Question 12

What type of join does the FROM/WHERE syntax perform?

Answer 12

inner

Question 13

If you want to preserve the rows of the second table in your join, which type should you use? Which table’s null fields will be included in the output?

Answer 13

RIGHT OUTER
The first/left table

Question 14

What type of join will the following statement produce?
Select *
From Table_1, Table_2

Answer 14

Cartesian Product

Question 15

If table 1 had 200 rows and table 2 has 150 rows, how many rows will be returned from a FULL OUTER Join between the two tables?

Answer 15

200

Question 16

which table (left, middle, or right) of a nested join is the table that resolves the many-to-many relationship aka is the “bridging table”

Answer 16

The right table

Question 17

What is a sub-query? and what clauses are supported within a sub-query?

Answer 17

A SELECT statement within a SELECT-Statement
Supports WHERE, GROUP By, HAVING and sometimes SELECT

Question 18

When will the condition be true with the ANY operator?

Answer 18

condition is true if the operator is true for any of the values in the range- think ‘or’ in programming

Question 19

When will the condition be true with the All operator?

Answer 19

When the operator is true for all the values in the range- think programming AND

Question 20

Is the BETWEEN operator inclusive or exclusive, and which clause can it be used with?

Answer 20

Inclusive, WHERE

Question 21

What does the IN operator do?

Answer 21

Allows you to specify multiple values in a WHERE Clause

Question 22

Wildcards for the Like operator?

Answer 22

% which represents zero, one, or multiple characters
_ which represents a single character
[charlist] any single character within charlist

Question 23

Wildcards for the RLIKE (~*) operator

Answer 23

.* which represents zero, one, or multiple characters
. which represents a single character
[charlist] any single character within charlist

Question 24

What does DML stand for?

Answer 24

Data Manipulation Language

Question 25

What are the three key commands of DML?

Answer 25

INSERT
UPDATE’
DELETE

Question 26

Does DML deal with rows/columns or objects/tables

Answer 26

Rows/Columns

Question 27

What Does DDL Stand for?

Answer 27

Data Definition Language

Question 28

What are the three main commands of DDL?

Answer 28

CREATE
ALTER
DROP

Question 29

What does DCL stand for and what are it’s two main commands?

Answer 29

Data Control Language
REVOKE, GRANT

Question 30

what does ACID stand for?

Answer 30

Atomicity, Consistency, Isolation, and Durability

Question 31

Name the following SIRDs 4326, 269__, 3857, 3005, 326__, 4267, 3978

Answer 31

WGS 84 (lat/long), NAD 83 UTM zone __, WGS 84 Web Mercator, MELP-Albers, WGS 84 UTM zone __, NAD1927 (lat,long), Canada atlas lambert

Question 32

what are the SRIDs for the following
Canada Atlas Lambert
WGS 84 (lat/long)
NAD 83 UTM zone __
Melp-Albers
WGS 84 Web Mercator
WGS 84 utm zone __
NAD 1927 (lat,long)

Answer 32

3978
4326
269__
3005
3857
326__
4267

Question 33

what operators are supported by the Btree indexing method?

Answer 33

range and equality

Question 34

which indexing method supports spatial E-Tree and Quad-Tree?

Answer 34

GiST and SP-GiST

Question 35

what operators does GIN support?

Answer 35

array, ranges, JSONB

Question 36

Which indexing method only supports equality

Answer 36

HASH/BITMap

Question 37

what are the two most powerful DML and DDL statements for GIS?

Answer 37

1) CREATE TABLE {table_name} AS {select-statement}
2) INSERT INTO {table_name} (field_1, field_n)
SELECT field_1, field_n FROM… WHERE…

Question 38

What is the difference between an INSERT, UPDATE, and ALTER?

Answer 38

Insert command is used to insert a new row to an existing table, Update is a SQL command that is used to update existing records in a database, while alter is a SQL command that is used to modify, delete or add a column to an existing table in a database. Insert and Update are DML statements, whereas alter is a DDL statement. Alter command modifies the database schema, while insert and update statements only modify records in a database or insert records into a table without modifying its structure.

Question 39

which two commands would you use to remove a column from a table?

Answer 39

ALTER (table), DROP (column)