Term Glossary (Topic 1.3)

Question 1

Explain ‘Sequence Programming’ when referring to programming

Answer 1

The computer runs code in the order it is written.

Question 2

Explain ‘Selection’ when referring to programming

Answer 2

The computer runs different pieces of code according to a condition being met (normally achieved using IF, CASE or SWITCH statements (depending on the programming language you are using))

Question 3

Explain what the ‘CASE’ function achieves in SQL

Answer 3

Used to conditionally fill columns (used with WHEN and THEN).

Question 4

Explain what the ‘DATETIME’ function achieves in SQL

Answer 4

A datatype used to store date and time data in standard format.

Question 5

Explain what the ‘CAST’ function achieves in SQL

Answer 5

Similar to CONVERT – Changes the data type of a value. This is an example of EXPLICIT data type conversion.

Question 6

Name the six types of JOINS in SQL

Answer 6

1) Inner
2) Outer
3) Left
4) Right
5) Anti
6) Cross

Question 7

Describe an Inner Join

Answer 7

Tables A and B joined with matching rows (according to their key fields). Only rows with key values present in both tables are shown.

Question 8

Describe an Outer Join

Answer 8

Tables A and B joined with matching rows (according to their key fields). Rows with key values present in either one of or both tables are shown.

Question 9

Describe a Left Join

Answer 9

Tables A and B joined with matching rows (according to their key fields). Rows with key values present in the Left table are shown with any matching rows from the right table.

Question 10

Describe a Right Join

Answer 10

Tables A and B joined with matching rows (according to their key fields). Rows with key values present in the Right table are shown with any matching rows from the left table.

Question 11

Describe an Anti Join

Answer 11

Rows are shown that are present (according to a key value) in one table but not in another. In SQL, there is no ‘ANTI’ statement for this join. Instead you must perform a standard join and filter the resulting table using ISNULL.

Question 12

Describe a Cross Join

Answer 12

All combinations of key values from both tables are shown, regardless of whether those combinations occurred in the database.

Question 13

What will occur if a Join is performed on tables with duplicate rows

Answer 13

If there are duplicate values, a join will replicate them in the resulting table – providing these values match with the other table.

Question 14

How would a Join need to be adjusted if the tables have multiple fields?

Answer 14

You can join tables using more than one key field with the ON statement.

Question 15

State the four types of ‘Unions’ in SQL

Answer 15

1) Union
2) Union All
3) Except
4) Intercept

Question 16

Describe ‘TABA UNION TABB’ in SQL

Answer 16

Merge tables and remove duplicate rows.

Question 17

Describe ‘TABA UNION ALL TABB’ in SQL

Answer 17

Merge tables and include duplicates.

Question 18

Describe ‘TABA INTERCEPT TABB’ in SQL

Answer 18

Merge tables and only include rows that match from both tables.

Question 19

Describe ‘TABA EXCEPT TABB’ in SQL

Answer 19

Merge tables include rows present in TableA but not present in TableB.

Question 20

Describe ‘SELECT INTO’ in SQL

Answer 20

Take columns from a query and add them to a new table. The INTO statement would be used: (SELECT cols INTO Tab2 FROM Tab1 WHERE conditions).

Question 21

Describe ‘Implicit Data Conversion’

Answer 21

Where the computer changes the data type without you telling it to do so. Often, this is done to allow a calculation to be completed correctly.

Question 22

Describe ‘Explicit Data Conversion’

Answer 22

This is where you tell the computer explicitly to change data format (using functions such as CAST and CONVERT).

Question 23

Define ‘Iteration’ when referring to programming

Answer 23

The computer runs the code repeatedly while a condition is met (this is done using FOR or WHILE loops).

Question 24

What does “ += “ achieve in relation to compound expressions?

Answer 24

Add to original value

Question 25

What does “ -= “ achieve in relation to compound expressions?

Answer 25

Subtract from original value

Question 26

What does “ *= “ achieve in relation to compound expressions?

Answer 26

Multiple original value by some amount

Question 27

What does “ /= “ achieve in relation to compound expressions?

Answer 27

Divide original value by some amount

Question 28

What does “ %= “ achieve in relation to compound expressions?

Answer 28

Replace original value with division remainder