Database Objects: Tables, Views, Sequences, Triggers

A view is called a "virtual table." What does it actually store, and what's the point of it?

A parameterized view's parameters all have default values. Can you call it as SELECT * FROM v;?

You mark a column PRIMARY KEY, then also add NOT NULL and UNIQUE to the same column. What is redundant?

A table has UNIQUE(a, b). You insert (NULL, 5) and then (NULL, 5) again. What happens?

You run a bare CREATE TABLE (no ROW or COLUMN keyword) on HANA 2.0 SPS 02, then the same statement on SPS 03. Do you get the same storage type?

You must process millions of rows but read only 3 of the table's 40 columns. ROW store or COLUMN store?

Why can a join between a ROW-store table and a COLUMN-store table cost more than expected?

GLOBAL TEMPORARY vs LOCAL TEMPORARY table: which one shares its definition across sessions, and can another session ever see your rows?

Two sessions call myseq.nextval in parallel. Can they get the same number? And does currval advance the sequence?

In SQLScript, the assignment lv_tmp = myseq.nextval fails. Why, and what's the correct idiom?

In an AFTER DELETE trigger you write REFERENCING NEW ROW ls_new. What breaks?

You change a trigger from FOR EACH ROW to FOR EACH STATEMENT. What must change in the REFERENCING clause?

Can application code sneak a write past a trigger, and why does the book still say use triggers sparingly?

Column a is GENERATED BY DEFAULT AS IDENTITY. Some rows are inserted without a, and one row inserts a = 3 explicitly. Does the explicit 3 stick?

CREATE TABLE t2 LIKE t1 — with WITH DATA vs without it, what's the difference?

In CREATE TABLE ... AS (SELECT ...) WITH DATA, where do the new table's columns come from?

In ALTER TABLE, what's the difference between ADD (col ...) and ALTER (col ...), and how do you switch a table to column store?

What does DROP TABLE prompt before it permanently deletes a table and all its data?

DROP TABLE ... RESTRICT vs CASCADE — how do they treat objects that depend on the table?

What are user-defined table types (CREATE TYPE ... AS TABLE) actually for — can you store rows in one?

A sequence has a negative increment (counts down). Does its first NEXTVAL return MINVALUE or MAXVALUE?

What values does this sequence hand out, in order? ``` CREATE SEQUENCE c INCREMENT BY -1 MAXVALUE 10 MINVALUE 0 CYCLE; -- c.nextval called repeatedly ```