3.1.15.1 SQL Query Methods

Question 1

What are the 4 query statements?

Answer 1

• DELETE
• CREATE
• SELECT
• UPDATE

Question 2

SQL command for creating a table:

Answer 2

CREATE TABLE

Question 3

explain what this statement executes:
first_name CHAR (30) NULL,

Answer 3

• CHAR is assigning first_name is a character column (character is basically a string)
• the (30) in brackets means it will store up to 30 characters in this column
• NULL means this column (first_name) can be empty

Question 4

why must the key field in a database always have a NOT NULL statement?

Answer 4

it must always have a value
for example: key INT IDENTITY (1001, 1) NOT NULL,
) ;

Question 5

explain what this statement executes:
key INT IDENTITY (1001, 1) NOT NULL,
) ;

Answer 5

• this is the key
• uses the term INT so that it is an integer
• we want it to start with 1001 and then go up in increments of 1
• the semi-colon at the end tells the database you’ve finished the command

Question 6

what is VARCHAR and why is it used?

Answer 6

stores a set of alphanumeric data (letters and numbers) as a variable string

• it is used because it is a variable so will only store the number of characters used (so if the max amount of characters was (80) but only 20 characters were used, it would only allocate 20 spaces)
• this means is uses less memory

Question 7

why isn’t always the case that VARCHAR uses less memory space?

Answer 7

there could be a minimum length

Question 8

what statement is used to add data?

Answer 8

INSERT INTO

Question 9

explain what this statement does:

INSERT INTO Name, ID, DOB VALUES (Jim, NULL, NULL)

Answer 9

inserts the value Jim as the name and no values for the ID and DOB

Question 10

explain what this means:
WHERE ID (less than sign and the greater than sign)  10

(wouldn’t let me show it)

Answer 10

choose data that is not equal to 10 (less than sign and the greater than sign means not equal to)

Question 11

Key point

Answer 11

you must add data in the same given order as the rows in the table. if you don’t know the data for a row, you would assign it to NULL

Question 12

what is the statement to edit/ update data in table?

Answer 12

UPDATE

Question 13

what happens if the WHERE clause is left out?

Answer 13

all record will be affected (edited)

Question 14

statement for retrieving data:

Answer 14

SELECT

SELECT * FROM table1 means select everything in table1