{"id":550,"date":"2021-11-16T13:35:45","date_gmt":"2021-11-16T13:35:45","guid":{"rendered":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/?page_id=550"},"modified":"2022-11-09T12:16:05","modified_gmt":"2022-11-09T12:16:05","slug":"create-statement","status":"publish","type":"page","link":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/advanced-higher\/create-statement\/","title":{"rendered":"CREATE Statement"},"content":{"rendered":"

Part of the SQL Data Definition Language (DDL), the CREATE statement is used to create databases and tables.<\/p>\n

It is common to use the DROP command to remove any existing database\/tables, otherwise the CREATE command could generate an error is created and halt the SQL script.<\/p>\n

CREATE Database<\/h1>\n

A database server can have many databases, for different applications or users.<\/p>\n

CREATE DATABASE databaseName<\/pre>\n
Note: Access and SQLite store databases as separate files, so the CREATE database command does not get used.<\/p>\n
CREATE Table<\/h1>\n
This is used to specify filed names, datatypes, keys and constraints.<\/p>\n
CREATE TABLE tableName (\r\n    fieldName1 dataType,\r\n    fieldName2 dataType,\r\n    ......\r\n);<\/pre>\n
Data Types<\/h2>\n
In National 5 and Higher you were expected to use datatypes: text, number, date, time, Boolean.
\nIn Advanced Higher you must use standard SQL types as described on the Data Types<\/a> page (varchar, int, float, date, time)<\/p>\n
Primary and Keys<\/h2>\n
fieldName dataType PRIMARY KEY,\r\n-- or\r\nPRIMARY KEY (fieldName1)\r\n-- or\r\nPRIMARY KEY (fieldName1, fieldName2, ...)\r\n<\/pre>\n
fieldName dataType FOREIGN KEY REFERENCES tableName (fieldName)\r\n-- or\r\nFOREIGN KEY(fieldName) REFERENCES tableName (fieldName)<\/pre>\n
Some dialects of SQL allow the PRIMARY or FOREIGN KEY constraint to be applied in the clause used to identify the data type for the field; other dialects require the PRIMARY or FOREIGN KEY constraint to be applied in a separate clause.<\/p>\n
If the primary or foreign key consists of multiple columns, users must specify them in a separate clause at the end of the CREATE TABLE statement.<\/p>\n
Auto Increment<\/h2>\n
AUTO_INCREMENT automatically generates a unique number when a new record is inserted. This is often used for surrogate keys<\/em>.<\/p>\n
fieldName dataType AUTO_INCREMENT\r\n\r\n-- example\r\nuserID int AUTO_INCREMENT<\/pre>\n
Required Fields<\/h2>\n
Required fields use NOT NULL to ensure a field always contains a value and is not left empty (NULL)<\/p>\n
fieldName dataType NOT NULL\r\n\r\n-- example\r\nmembershipType varchar(10) NOT NULL<\/pre>\n
Check Constraints<\/h2>\n
Check constraints ensure that a field value meets specific conditions.<\/p>\n
fieldName dataType CHECK(fieldName condition)\r\n\r\n-- examples\r\nlevel VARCHAR(2) CHECK(status IN ('N4', 'N5', 'H', 'AH' ) )\r\nmonth INT CHECK(month BETWEEN 1 AND 12)<\/pre>\n
Standard SQL provides the CHECK constraint, as described and exemplified here. However, the CHECK constraint is not provided in all dialects of SQL (for example, MS Access and MySQL do not support the use of CHECK).<\/p>\n
In the case of MySQL, the CHECK constraint is ignored and the intended data validation is not carried out. To implement the CHECK constraint in MySQL, triggers or views must be used.<\/p>\n
Note: candidates should implement triggers or views within their project solution, as required; however, these constraints are not assessed in the Advanced Higher Computing Science course.<\/p>\n
Multiple Constraints<\/h2>\n
It is possible to apply several constraints to one field, for example:<\/p>\n
 fieldName dataType NOT NULL PRIMARY KEY<\/pre>\n
Example: Travel Agency<\/h1>\n\n\n\n\n
Customer<\/strong><\/td>\nBooking<\/strong><\/td>\nHotel<\/strong><\/td>\nResort<\/strong><\/td>\n<\/tr>\n
customerNo PK
\nfirstname
\nsurname
\naddress
\ntown
\npostcode<\/td>\n		hotelRef PK FK
\ncustomerNo PK FK
\nstartDate PK
\nnumberOfNights
\nnumberInParty<\/td>\n		hotelRef PK
\nhotelName
\nresortID FK
\nstarRating
\nseasonStartDate
\nmealPlan
\ncheckInTime
\npricePersonNight<\/td>\n		resortID PK
\nresortName
\nresortType<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
DROP DATABASE IF EXISTS TravelAgency;\r\nCREATE DATABASE TravelAgency;\r\n\r\nCREATE TABLE Resort (\r\nresortID int NOT NULL PRIMARY KEY,\r\nresortName varchar(20) NOT NULL,\r\nresortType varchar(20) NOT NULL CHECK (resortType IN('coastal', 'city', 'island', 'country'))\r\n);\r\n\r\nCREATE TABLE Hotel (\r\nhotelRef varchar(4) NOT NULL PRIMARY KEY,\r\nhotelName varchar(20) NOT NULL,\r\nresortID int NOT NULL,\r\nstarRating int NOT NULL CHECK(starRating >=1 AND starRating <= 5),\r\nseasonStartDate date,\r\nmealPlan varchar(17) NOT NULL CHECK(mealPlan IN('Room Only', 'Bed and Breakfast', 'Half Board', 'Full Board')),\r\ncheckInTime time NOT NULL,\r\npricePersonNight float(6,2) NOT NULL CHECK(pricePersonNight >=50 AND pricePersonNight <= 250),\r\nFOREIGN KEY (resortID) REFERENCES Resort(resortID)\r\n);\r\n\r\nCREATE TABLE Customer (\r\ncustomerNo int AUTO_INCREMENT PRIMARY KEY,\r\nfirstname varchar(20) NOT NULL,\r\nsurname varchar(20) NOT NULL,\r\naddress varchar(40) NOT NULL,\r\ntown varchar(20) NOT NULL,\r\npostcode varchar(8) NOT NULL\r\n);\r\n\r\nCREATE TABLE Booking (\r\nhotelRef varchar(4) NOT NULL,\r\ncustomerNo int NOT NULL,\r\nstartDate date NOT NULL,\r\nnumberNights int NOT NULL CHECK(numberNights >=1),\r\nnumberInParty int NOT NULL CHECK(numberInParty >=1),\r\nPRIMARY KEY (customerNo, hotelRef, startDate),\r\nFOREIGN KEY (customerNo) REFERENCES Customer(customerNo),\r\nFOREIGN KEY (hotelRef) REFERENCES Hotel(hotelRef)\r\n);\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Part of the SQL Data Definition Language (DDL), the CREATE statement is used to create databases and tables. It is common to use the DROP command to remove any existing database\/tables, otherwise the CREATE command could generate an error is created and halt the SQL script. CREATE Database A database server can have many databases, for different applications or users. CREATE DATABASE databaseName Note: Access and SQLite store databases as<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"parent":533,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-550","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/550","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/comments?post=550"}],"version-history":[{"count":22,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/550\/revisions"}],"predecessor-version":[{"id":636,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/550\/revisions\/636"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/533"}],"wp:attachment":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/media?parent=550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}