EXAM 2 (Mods 3-5) Flashcards
What is the computer’s order of execution for the select statement clauses?
1: FROM
2: WHERE
3: GROUP BY
4: HAVING
5: SELECT
6: ORDER BY
What does CRUD stand for?
Create, Read, Update, Delete
Do the Select statement commands/clauses fall within DML, QL, or DDL
QL
What is the meaning of the following Reg expression operator;
<>
not equal
Which “optional” SELECT clause is mandatory for data aggregation?
GROUP BY
Which select statement clause(s) support aliases?
all but WHERE
Which clause(s) don’t support aggregate functions?
WHERE & FROM
What are the three purposes of the select statement and the associated clause responsible for each?
1) Projection -> SELECT
2) Selection -> WHERE
3) Join -> FROM (implicit)
What are the two main join syntax styles in SQL?
Implicit- FROM/WHERE
Explicit- <”some join type”> JOIN ON
How are tables most commonly joined
Equi joins on Pk-Fk
Can outer joins be only implicit or only explicit?
explicit
Which type of join will produce the fewest # of rows back?
Inner Join
What type of join does the FROM/WHERE syntax perform?
inner
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?
RIGHT OUTER
The first/left table
What type of join will the following statement produce?
Select *
From Table_1, Table_2
Cartesian Product
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?
200
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”
The right table
What is a sub-query? and what clauses are supported within a sub-query?
A SELECT statement within a SELECT-Statement
Supports WHERE, GROUP By, HAVING and sometimes SELECT
When will the condition be true with the ANY operator?
condition is true if the operator is true for any of the values in the range- think ‘or’ in programming
When will the condition be true with the All operator?
When the operator is true for all the values in the range- think programming AND
Is the BETWEEN operator inclusive or exclusive, and which clause can it be used with?
Inclusive, WHERE
What does the IN operator do?
Allows you to specify multiple values in a WHERE Clause
Wildcards for the Like operator?
% which represents zero, one, or multiple characters
_ which represents a single character
[charlist] any single character within charlist
Wildcards for the RLIKE (~*) operator
.* which represents zero, one, or multiple characters
. which represents a single character
[charlist] any single character within charlist
What does DML stand for?
Data Manipulation Language
What are the three key commands of DML?
INSERT
UPDATE’
DELETE
Does DML deal with rows/columns or objects/tables
Rows/Columns
What Does DDL Stand for?
Data Definition Language
What are the three main commands of DDL?
CREATE
ALTER
DROP
What does DCL stand for and what are it’s two main commands?
Data Control Language
REVOKE, GRANT
what does ACID stand for?
Atomicity, Consistency, Isolation, and Durability
Name the following SIRDs 4326, 269__, 3857, 3005, 326__, 4267, 3978
WGS 84 (lat/long), NAD 83 UTM zone __, WGS 84 Web Mercator, MELP-Albers, WGS 84 UTM zone __, NAD1927 (lat,long), Canada atlas lambert
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)
3978
4326
269__
3005
3857
326__
4267
what operators are supported by the Btree indexing method?
range and equality
which indexing method supports spatial E-Tree and Quad-Tree?
GiST and SP-GiST
what operators does GIN support?
array, ranges, JSONB
Which indexing method only supports equality
HASH/BITMap
what are the two most powerful DML and DDL statements for GIS?
1) CREATE TABLE {table_name} AS {select-statement}
2) INSERT INTO {table_name} (field_1, field_n)
SELECT field_1, field_n FROM… WHERE…
What is the difference between an INSERT, UPDATE, and ALTER?
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.
which two commands would you use to remove a column from a table?
ALTER (table), DROP (column)
