Quiz 2 - SQL/Create Database

Question 1

SQL: CREATE TABLE syntax

Answer 1

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
...
);

Question 2

SQL: CREATE TABLE syntax - w/constraints

Answer 2

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
….
);

Question 3

SQL: ALTER TABLE syntax - add column

Answer 3

ALTER TABLE table_name
ADD COLUMN column_name datatype
;

Question 4

SQL: ALTER TABLE syntax - drop column

Answer 4

ALTER TABLE table_name
DROP COLUMN column_name
;

Question 5

SQL: ALTER TABLE syntax - change datatype

Answer 5

ALTER TABLE table_name
ALTER COLUMN column_name datatype
;

Question 6

SQL: ALTER TABLE syntax - add constraint

Answer 6

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_keyword (column_name)
;

Question 7

Constraint Keywords

Answer 7

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

Question 8

SQL: ALTER TABLE syntax - add Foreign Key

Answer 8

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
;

Question 9

SQL: ALTER TABLE syntax - add CHECK

Answer 9

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0)
;

Question 10

SQL: ALTER TABLE syntax - add DEFAULT

Answer 10

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT ‘Fairfax’
;