Chapter 19 - Defining and updating tables by using SQL
No description yet. This curated deck is ready for daily study.
Card preview (24)
What are the commands to create a new table in the SQL?
» CREATE TABLE (tablename) { Column1 Data type,etc Column2 Data type,etc Column3 Data type, etc }
What is the purpose of the NOT NULL statement?
» If you put it in when you create a new table, it is a compulsory field
What is the purpose of the PRIMARY KEY statement?
» States the column is the primary key
What is the purpose the CHAR(n) data type?
» Makes sure the data entered character’s are a fixed length of, n
What is the purpose of the VARCHAR(n) data type?
» Makes sure the data entered has a character string variable of max length, n
What is the purpose the BOOLEAN data type?
» Make sure the data entered is TRUE or FALSE
What is the purpose of the INTEGER data type?
» Makes sure the data entered is an integer
What is another way to write the INTEGER data type?
» INT
What is the purpose of the FLOAT data type?
» Make sure it is a number with a floating decimal point
What does it mean by the FLOAT(X,Y) command?
» X - Maximum number of digits is X » Y - Maximum number after decimal point is Y
What does the DATE data type do?
» Stores the data entered as Day,Month,Year values
What is the purpose of the TIME data type?
» Stores the data entered as Hour,Minute,Second values
What does the CURRENCY data type do?
» Formats numbers in the currency used in your region
What does the CURRENCY data type do?
» Formats numbers in the currency used in your region
What is the purpose of the ALTER TABLE statement?
» To add, delete or modify columns in an exisiting table
What is the SQL commands to add a column?
ALTER TABLE (tablename) ADD COLUMN DATATYPE
What is the SQL command do delete a column?
ALTER TABLE (tablename) DROP COLUMN (columname)
What is the SQL command to change the data type of a column?
ALTER TABLE (tablename) MODIFY COLUMN (columname) …. DATATYPE you want to modify
What are the steps to create a linked table
CREATE TABLE (tablename) { Column1 Column2 … DATATYPE FOREIGN KEY COLUMN REFERENCE X FOREIGN KEY COLUMN REFERENCE Y PRIMARY KEY (FOREIGN KEY, FOREIGN KEY) } » Note X and Y are where the foreign key originally came from, e.g for a foreign key called COURSEID which came from Course, X would look like Course(COURESEID)
What is the SQL statement to insert a new record in a database table?
INSERT INTO tablename(column1,column2) VALUES (value1,value2…) ……….
What is some tips to remember about the stuff in brackets in VALUES (value1…)?
» Everything should be seperated by a comma » Speech marks for all the strings inserted » Apart from CURRENCY and Date » Data has the syntax #day/month/year# » Currency does not need anything else
What would be a more efficient way of inserting data, when all the fields are being added in the correct order?
» You would not need fields names in the INSERT statement » INSERT INTO tablename - would be enough
What is the SQL statement to update a record in a database table?
UPDATE tablename SET column1 = value1, column 2 =value2….. WHERE ColumnX = value » Example: UPDATE EMPLOYEE SET SALARY = SALARY*1.1 WHERE DEPARTMENT = “TECHNICAL”
What is the SQL statement to delete a record from a database table?
DELETE FROM tablename WHERE columnx = value » Example: DELETE FROM EMPLOYEE WHERE EmpID = “1122”