SQL Query

Question 1

Give the SQL instruction to: Create a table called students
(StudentNumber as primary key, forename, surname, and date of birth)

Answer 1

CREATE TABLE students
(
StudentNumber INT PRIMARY KEY,
forename TEXT,
surname TEXT,
DateOfBirth DATE
)

Question 2

SQL: What is the keyword for deleting a database/table?

Answer 2

DROP …

Question 3

What are the SQL data types

Answer 3

CHAR(size) // → fixed length string
VARCHAR(size) // → string (with a maximum length)
‘TEXT’ // → string
ENUM // → string object that has to be chosen from a list of possible values
BOOL / BOOLEAN
INT(size) // → size is the number of digits
FLOAT(size,d) // → size is the number of digits , with d digits after the decimal point
DATE
TIME
DATETIME

Question 4

Give the SQL WHERE command for: all strings beginning with ‘bor’

Answer 4

LIKE ‘bor%’

Question 5

Give the SQL WHERE command for: any strings at least five characters long

Answer 5

LIKE ‘_____%’

Question 6

Give the SQL WHERE command for: a studentID between 10 an d100 numbers

Answer 6

… WHERE StudentID BETWEEN 10 AND 100

Question 7

Give the SQL command for: ordering results by surname

Answer 7

ORDER BY surname

ORDER BY surname ASC

ORDER BY surname DESC

Question 8

Give the SQL command for: Inserting data into the student table
Assume it’s of the form: Students(StudentID, Forename, Surname, House)

Answer 8

INSERT INTO students VALUES(‘’,’’,’’,’’), (‘’,’’,’’,’‘)…

INSERT INTO students (surname , forename) VALUES (__…
//if you can’t remember what order you’re supposed to do it in)

Question 9

Give the SQL command for: update a forename to ‘Harry’ where…:

Answer 9

UPDATE students SET forename = ‘Harry’ WHERE…

Question 10

Give the SQL command for: Delete a record from the students table

Answer 10

DELETE FROM students WHERE …

Question 11

Give the SQL command for: Selecting from two tables

Answer 11

SELECT table1.attributeX , table2.attributeY
FROM table1 , table2
WHERE table1.primarykey = table2.foreignkey
AND …