-- DATABASE MANAGEMENT SYSTEMS 2019/20
-- LECTURE 4 QUERIES
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/*
	Consider the Branch table for DreamHome:
		Branch(branchNo, streetAddress, city, postcode, phoneNo)

	Here are the data definition requirements:
		branchNo: Character String, Size: 5, Required, PK
		streetAddress: Character String, Size: 30, Required
		city: Character String, Size: 20, Required
		postcode: Character String, Size 8, Required, Limit values to min length 6
		phoneNo: Character String, Size 12, Required

	QUESTION: Which of the following SQL statement(s) is/are correct to add a new record into the branch table?
*/

INSERT INTO Branch (BranchNo, StreetAddress, City, PhoneNo)				-- INCORRECT, missing required Postcode column
VALUES ('B0009', '17 High St', 'Swansea', '01792480000')

INSERT INTO Branch (BranchNo, StreetAddress, City, Postcode, PhoneNo) 	-- CORRECT
VALUES ('B0009', '17 High St', 'Swansea', 'SA1 6ED', '01792480000')

INSERT INTO Branch (BranchNo, StreetAddress, City, Postcode, PhoneNo)	-- INCORRECT, PhoneNo is a Character String column and must be quoted i.e. '01792480000'
VALUES ('B0009', '17 High St', 'Swansea', 'SA1 6ED', 01792480000)

INSERT INTO Branch (BranchNo, StreetAddress, City, Postcode, PhoneNo)	-- TECHNICALLY CORRECT, but see comments below
VALUES ('B0009', '17 High St', 'Swansea', 'SA1 6ED', 'NULL')

INSERT INTO Branch (BranchNo, StreetAddress, City, Postcode, PhoneNo)	-- INCORRECT, Values specified in the list are incomplete i.e. VALUES ('B0009', '17 High St', ..., 'SA1 6ED', '01792480000')
VALUES ('B0009', '17 High St', 'SA1 6ED', '01792480000')

INSERT INTO Branch (BranchNo, StreetAddress, City, Postcode, PhoneNo)	-- INCORRECT, PhoneNo column is required so you cannot insert a NULL value for it
VALUES ('B0009', '17 High St', 'Swansea', 'SA1 6ED', NULL)


/* 
	SOLUTION
	The correct answers are Queries 2 and 4, but:
	While Query 4 is technically correct, it is an example of Bad Data. The quoted 'NULL' value used for the phone number column is bad data showing a bad way of getting round the NOT NULL constraint. Note that a quoted 'NULL' is not the same thing as the NULL (unknown) value.

*/