Lecture 3 - SQL CREATE

Question 1

What type of language is SQL?

Answer 1

a declarative language -> we say what rather than how

Question 2

Statement for creating a schema in SQL?

Answer 2

CREATE SCHEMA name;

Question 3

Each statement ends with a what in SQL?

Answer 3

;

Question 4

SQL statement for creating a table?

Answer 4

CREATE TABLE name (attrs)

Question 5

What do we need to specify when creating a table in SQL?

Answer 5

the name of the relation
attributes and their domain
any constraints

Question 6

What domains can we have in SQL?

Answer 6

integer
real / decimal(digits , sig figs after decimal)
char(n)
varchar(n)
bit
bit varying
bool
date
timestamp
date intervals

Question 7

Difference between char and varchar

Answer 7

Char allows exactly n characters while varchar allows from 0 to n characters

Question 8

How do we put a PK constraint into CREATE TABLE?

Answer 8

At the end PRIMARY KEY (attr)

Question 9

How do we put a FK constraint into CREATE TABLE?

Answer 9

At the end FOREIGN KEY (attr) REFERENCES TABLE(attr(s))

Question 10

What value constrains are possible in SQL?

Answer 10

• default value
• NOT NULL
• CHECK CLAUSE (checking if given value meets requirements and boundries)

Question 11

What is the key constraint in SQL?

Answer 11

that the primary key is unique

Question 12

What is an entity integrity constraint in SQL?

Answer 12

PK can’t be null

Question 13

What is the UNIQUE clause?

Answer 13

specific candidate keys as they can uniquely identify a field

Question 14

What are the triggered actions that are most common? (what happens on delete/update on relations with a referential constraint)

Answer 14

Action: ON DELETE SET NULL/ DEFAULT/ CASCADE
Action: ON UPDATE SET NULL/ DEFAULT /CASCADE

Question 15

What is CASCADE?

Answer 15

propagate the change (DELETE/UPDATE) to referential relations