Chapter 19 - Defining and updating tables by using SQL

Question 1

What are the commands to create a new table in the SQL?

Answer 1

» CREATE TABLE (tablename)
{
Column1 Data type,etc
Column2 Data type,etc
Column3 Data type, etc
}

Question 2

What is the purpose of the NOT NULL statement?

Answer 2

» If you put it in when you create a new table, it is a compulsory field

Question 3

What is the purpose of the PRIMARY KEY statement?

Answer 3

» States the column is the primary key

Question 4

What is the purpose the CHAR(n) data type?

Answer 4

» Makes sure the data entered character’s are a fixed length of, n

Question 5

What is the purpose of the VARCHAR(n) data type?

Answer 5

» Makes sure the data entered has a character string variable of max length, n

Question 6

What is the purpose the BOOLEAN data type?

Answer 6

» Make sure the data entered is TRUE or FALSE

Question 7

What is the purpose of the INTEGER data type?

Answer 7

» Makes sure the data entered is an integer

Question 8

What is another way to write the INTEGER data type?

Answer 8

» INT

Question 9

What is the purpose of the FLOAT data type?

Answer 9

» Make sure it is a number with a floating decimal point

Question 10

What does it mean by the FLOAT(X,Y) command?

Answer 10

» X - Maximum number of digits is X
» Y - Maximum number after decimal point is Y

Question 11

What does the DATE data type do?

Answer 11

» Stores the data entered as Day,Month,Year values

Question 12

What is the purpose of the TIME data type?

Answer 12

» Stores the data entered as Hour,Minute,Second values

Question 13

What does the CURRENCY data type do?

Answer 13

» Formats numbers in the currency used in your region

Question 14

What does the CURRENCY data type do?

Answer 14

» Formats numbers in the currency used in your region

Question 15

What is the purpose of the ALTER TABLE statement?

Answer 15

» To add, delete or modify columns in an exisiting table

Question 16

What is the SQL commands to add a column?

Answer 16

ALTER TABLE (tablename)
ADD COLUMN DATATYPE

Question 17

What is the SQL command do delete a column?

Answer 17

ALTER TABLE (tablename)
DROP COLUMN (columname)

Question 18

What is the SQL command to change the data type of a column?

Answer 18

ALTER TABLE (tablename)
MODIFY COLUMN (columname) …. DATATYPE you want to modify

Question 19

What are the steps to create a linked table

Answer 19

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)

Question 20

What is the SQL statement to insert a new record in a database table?

Answer 20

INSERT INTO tablename(column1,column2)
VALUES (value1,value2…)
……….

Question 21

What is some tips to remember about the stuff in brackets in VALUES (value1…)?

Answer 21

» 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

Question 22

What would be a more efficient way of inserting data, when all the fields are being added in the correct order?

Answer 22

» You would not need fields names in the INSERT statement
» INSERT INTO tablename - would be enough

Question 23

What is the SQL statement to update a record in a database table?

Answer 23

UPDATE tablename
SET column1 = value1, column 2 =value2…..
WHERE ColumnX = value
» Example:
UPDATE EMPLOYEE
SET SALARY = SALARY*1.1
WHERE DEPARTMENT = “TECHNICAL”

Question 24

What is the SQL statement to delete a record from a database table?

Answer 24

DELETE FROM tablename
WHERE columnx = value
» Example:
DELETE FROM EMPLOYEE
WHERE EmpID = “1122”

Question 25

What does the DROP TABLE tablename command do?

Answer 25

» Can be used to drop an existing table in a database