SQL Commands

Question 1

What is SQL?

Answer 1

It is a declarative language used for creating, querying and updating tables in a relational database.

Question 2

What is the SELECT keyword used for?

Answer 2

Used to extract a collection of fields from a given table.

Question 3

Show the syntax of the SELECT command:

Answer 3

SELECT ,
FROM ,
WHERE
ORDER BY

The default for ORDER BY is ascending.

Example:

SELECT CDTitle, RecordCompany
FROM CD
WHERE DatePublished = 01/01/2015
ORDER BY CDTitle

Question 4

What are the most common ‘standard conditions’ that can be used with SQL?

Answer 4

=, <=, >=, !=, AND, OR etc.

Question 5

What does the IN condition mean?

Answer 5

Equal to a value within a set of values.

Question 6

What does the LIKE condition mean?

Answer 6

Similar to.

Question 7

What does the BETWEEN…AND condition mean?

Answer 7

Within a range, including the two defining values.

Question 8

What does the IS NULL condition mean?

Answer 8

Field doesn’t contain a value. eg CD IS NULL

Question 9

What does NOT do?

Answer 9

Invert truth.

Question 10

What is the ORDER BY keyword used for?

Answer 10

Gives control over how the results are displayed.

Question 11

How would you further sort data using the ORDER BY keyword?

Answer 11

Give the command another parameter to sort by. eg SORT BY Name, Date.

Question 12

How would you use the SELECT command to extract and combine data from multiple tables?

Answer 12

Use the SELECT command as usual but prefix the field name with the table name: tablename.fieldname

eg SELECT Song.SongTitle, Artist.ArtistName

Question 13

What does the JOIN command do?

Answer 13

JOIN combines rows from tables based on common fields between them.

Question 14

What is the CREATE TABLE keyword used for?

Answer 14

Used to create a new SQL table.

Question 15

Show the syntax of the CREATE TABLE command:

Answer 15

CREATE TABLE
(

)

Question 16

Define the CHAR(n) Data Type.

Answer 16

Character string with fixed length, n.

Question 17

Define the VARCHAR(n) Data Type.

Answer 17

Variable length character string, max length n.

Question 18

Define the BOOLEAN Data Type.

Answer 18

TRUE or FALSE.

Question 19

INTEGER, INT

Answer 19

Integer

Question 20

FLOAT(a,b)

Answer 20

Float with max digits, a, and max digits after decimal, b.

Question 21

DATE

Answer 21

Stored Day, Month, Year values.

Question 22

CURRENCEY

Answer 22

Formats numbers based on a currency.

Question 23

What is the ALTER TABLE keyword used for?

Answer 23

Used to add, delete or modify fields in an existing table.

Question 24

Show how to ADD a field using the ALTER TABLE command:

Answer 24

ALTER TABLE

ADD

Question 25

Show how to DELETE a field using the ALTER TABLE command:

Answer 25

ALTER TABLE | DROP COLUMN

Question 26

Show how to Change a data type of a field using the ALTER TABLE command:

Answer 26

ALTER TABLE | MODIFY COLUMN

Question 27

What is the INSERT keyword used for?

Answer 27

Inserting a new record.

Question 28

Show the syntax of the INSERT command:

Answer 28

``` INSERT INTO (column #1, n) VALUES (value 1, n) ``` Where column is the field for the data to be inserted and value is the new value.

Question 29

What is the UPDATE keyword used for?

Answer 29

Updates a record in a database.

Question 30

Show the syntax of the UPDATE command:

Answer 30

UPDATE SET = , = WHERE columnX = value

Question 31

What is the DELETE keyword used for?

Answer 31

Deleting a record.

Question 32

Show the syntax of the DELETE command:

Answer 32

DELETE FROM | WHERE columnX = value

Question 33

What is the PRIMARY KEY keyword used for?

Answer 33

Assigning a primary key, or a composite key.

Question 34

Show the syntax of the PRIMARY KEY command:

Answer 34

PRIMARY KEY (,) if n is populated a composite key is created.

Question 35

What is the FOREIGN KEY keyword used for?

Answer 35

Used to set up a Foreign Key.

Question 36

Show the syntax of the FOREIGN KEY command:

Answer 36

FOREIGN KEY REFERENCES ()