A2 SQL Commands

Which of the following SQL commands create a table?

• CREATE TABLE TableName

• CREATE TableName

• BUILD TableName

• INSERT TableName

Answer explanation

CREATE TABLE Students (

StudentID VARCHAR(4) NOT NULL,

StudentName VARCHAR(20) NOT NULL,

DOB DATE

PRIMARY KEY (StudentID)

)

Creates a table with 3 fields which specific validation techniques applied. The primary key is the StudentID. NOT NULL indicates that this field must be filled in when inserting a record.

Which of the following SQL commands delete a table?

• DELETE TABLE TableName

• DELETE TableName

• DROP TableName

• REMOVE TableName

Answer explanation

DROP Students

Deletes the table and all the data

Which of the following SQL commands return data from a database table?

• SELECT

• INSERT

• DELETE

• DROP

Which of the following SQL commands return all the data from a database table?

• SELECT * FROM Students

• SELECT * FROM Pupils WHERE PupilID = “EHP01”

• SELECT PupilID FROM Pupils

• SELECT PupilName, DOB FROM Pupils WHERE Gender = “F”

Which of the following SQL commands return a specific column from a database table?

• SELECT * FROM Students

• SELECT * FROM Pupils WHERE PupilID = “EHP01”

• SELECT PupilID FROM Pupils

• SELECT PupilName, DOB FROM Pupils WHERE Gender = “F”

Which of the following SQL commands return all the data for a specific record?

• SELECT * FROM Students

• SELECT * FROM Pupils WHERE PupilID = “EHP01”

• SELECT PupilID FROM Pupils

• SELECT PupilName, DOB FROM Pupils WHERE Gender = “F”

Which of the following SQL commands return all the data specific data that meets a given criteria?

• SELECT * FROM Students

• SELECT * FROM Pupils WHERE PupilID = “EHP01”

• SELECT PupilID FROM Pupils

• SELECT PupilName, DOB FROM Pupils WHERE Gender = “F”

Which of the following SQL commands return all the data specific data that meets a given criteria in descending order?

• SELECT PupilName, DOB FROM Pupils WHERE Gender = “F” ORDER BY PupilName DESC

• SELECT * FROM Pupils WHERE PupilID = “EHP01”

• SELECT PupilName, DOB FROM Pupils WHERE Gender = “F” ORDER BY PupilName ASC

• SELECT PupilName, DOB FROM Pupils WHERE Gender = “F”

Which of the following SQL commands lets you insert a new record?

• INSERT INTO Pupils (PupilID, PupilName, DOB, Gender) VALUES (”EHP07”, “Dave”, “25/12/2010”, “M”)

• ADD INTO Pupils

  (PupilID, PupilName, DOB, Gender)

  VALUES (”EHP07”, “Dave”, “25/12/2010”, “M”)

• INSERT INTO Pupils

  (PupilID, PupilName, DOB, Gender)

  DATA (”EHP07”, “Dave”, “25/12/2010”, “M”)

• ADD INTO Pupils

  (PupilID, PupilName, DOB, Gender)

  DATA (”EHP07”, “Dave”, “25/12/2010”, “M”)

Which of the following SQL commands lets you update a record? [Select 2 methods]

• UPDATE Pupils SET PupilName = “Pete”

• UPDATE Pupils

  SET PupilName = “Pete”

  AT PupilID = “EHP01”

• UPDATE Pupils SET PupilName = “Pete” WHERE PupilID = “EHP01”

• UPDATE Pupils

  GIVE PupilName = “Pete”

  WHERE PupilID = “EHP01”