CREATE TABLE STUDENT(
	ID domain
)

domain

• Character: single char
  • CHARACTER(6) : string of 6
  • CHAR(6) : char
  • VARCHAR(6) : a string of at most 6 char
• Numerical Domains
  • INT : Integer
  • SMALL INT
  • FLOAT
  • REAL
• TIME/ DATE/ TIMESTAMP (TIME + DATE)
• BIT: a single bit / BIT(6): a string of 6 Bit / BIT VARYING(6): a string at most 6 bit

Example: Create an empty table

• STUDENT (ID, NAME, SURNAME, ADDRESS, CITIZENSHIP)

CREATE TABLE STUDENT(
	ID CHAR(8) PRIMARY KEY,
	NAME VARCHAR(20) NOT NULL, # the length at most of 20 char
	SURNAME VARCHAR(20) NOT NULL,
	ADDRESS VARCHAR(30),
	CITIZENSHIP VARCHAR(20) DEFAULT 'ITALIAN'
)

• COURSE (COURSE ID, NAME, CREDITS)

CREATE TABLE COURSE(
	COURSE ID CHAR(6) PRIMARY KEY,
	NAME VARCHAR(10) NOT NULL, # the length at most of 20 char
	CREDITS SMALL INT DEFAULT 6,
	CHECK (CREDITS > 0)
)

• EXAM (STUDENT, COURSE, DATE, MARK)

CREATE TABLE EXAM(
	STUDENT CHAR(8), # = STUDENT.ID
	COURSE CHAR(6), # = COURSE.COURSE ID
	DATE DATE,
	MARK SMALLINT,
	PRIMARY KEY (STUDENT,COURSE),
	FOREIGN KEY (COURSE) REFERENCES COURSE (COURSE ID) ON UPDATE CASCADE
)

UPDATE

DELETE

SET NULL

SET DEFAULT

CASCADE

NO ACTION → You Forbid the update operations IF there are tuples in the slave table referencing the update value

INSERT INTO STUDENT
VALUES ('VR0000001', 

ALTER TABLE STUDENT ADD PHONE CHAR(10)
ALTER TABLE STUDENT DROP ADDRESS

CREATE VIEW TOP STUDENT (ID, AVERGAE) AS
	SELECT STUDENT, AVG(MARK)
	FROM EXAM
	HAVING AVG(MARK) > 25