Exporting query results for use in a spreadsheet

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which file format is commonly used to export query results so a spreadsheet can open them?

A A SQL CREATE TABLE statement

B CSV file

2. What should the first line of an exported CSV file contain?

A The SQL query that was run

B A header row of field names

3. After exporting query results, what is the main reason to import them into a spreadsheet?

A To analyse the data further with formulas, sorting and charts

B To change the original database tables automatically

4. A query returns studentId, firstName and points. How many columns should the imported spreadsheet have?

A 3 columns

B As many as the database has tables

5. You import a CSV file and all the points values are left-aligned and will not add up. What does this suggest?

A The numbers were imported as text and need checking

B The SUM function is not available in spreadsheets

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1SELECT firstName, points FROM students;		AThe header row of a CSV file, holding the field names that become the spreadsheet column headings
2firstName,points		BA query whose returned rows and columns can be exported as a CSV file for a spreadsheet
3A CSV file stores each exported record on its own line, with the values separated by commas		CValues are separated by spaces, not commas, so a spreadsheet would put each whole line into a single column. Use Ava,120 and Liam,95.
4After importing, check that the imported columns match the query fields and hold the right data type		DThe header row is missing. The file should start with firstName,points so the columns have headings in the spreadsheet.
5Ava 120 Liam 95		E=ISNUMBER(B2)
6Ava,120 Liam,95 Mia,130		FAva,120

Definitions

Export query results as CSV

Fill in the Blanks

Complete each line of code by filling in the missing parts.

.headers on .mode ___ .output results.csv

._______ on .mode csv .output marks.csv

.mode csv .______ house_points.csv SELECT house, points FROM students;

firstName,______ Ava,120 Liam,95

=_____(B2:B11)

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A .headers on

B .mode html

C .output top.csv

D SELECT name, price

E FROM products;

Question 2

A .headers on

B .mode csv

C .read stock.csv

D SELECT name, stock

E FROM products

F WHERE stock = 0;

Question 3

A .headers off

B .mode csv

C .output houses.csv

D SELECT firstName, house

E FROM students;

Question 4

A firstName,points

B Ava,120

C Liam;95

D Mia,110

Question 5

A name,price,stock

B Pencil,1.5

C Notebook,4,80

Answer Key

Exporting query results for use in a spreadsheet

Section A: Quick Questions

B — CSV file

B — A header row of field names

A — To analyse the data further with formulas, sorting and charts

A — 3 columns

A — The numbers were imported as text and need checking

Section B: Match the Pairs

SELECT firstName, points FROM students; → A query whose returned rows and columns can be exported as a CSV file for a spreadsheet

firstName,points → The header row of a CSV file, holding the field names that become the spreadsheet column headings

A CSV file stores each exported record on its own line, with the values separated by commas → Ava,120

After importing, check that the imported columns match the query fields and hold the right data type → =ISNUMBER(B2)

Ava 120 Liam 95 → Values are separated by spaces, not commas, so a spreadsheet would put each whole line into a single column. Use Ava,120 and Liam,95.

Ava,120 Liam,95 Mia,130 → The header row is missing. The file should start with firstName,points so the columns have headings in the spreadsheet.

Section C: Definitions

Export query results as CSV — Save the rows a query returns as a CSV file with a header row of field names. Import it into a spreadsheet, then check the columns came across correctly before further analysis.

Section D: Fill in the Blanks

.headers on
.mode csv
.output results.csv

.headers on
.mode csv
.output marks.csv

.mode csv
.output house_points.csv
SELECT house, points FROM students;

firstName,points
Ava,120
Liam,95

=COUNT(B2:B11)

Section E: Spot the Error

B — The export must be in CSV format so a spreadsheet can import it. The line should be .mode csv. .mode html would write HTML tags instead of comma-separated values.

C — .output sends the query results into a file. .read tries to run a file as commands, so no CSV would be created. It should be .output stock.csv.

A — The CSV needs a header row of field names so the spreadsheet columns are labelled. It should be .headers on.

C — A CSV file separates values with commas. A semicolon would leave 'Liam;95' in one column when imported. It should be Liam,95.

B — Every data row needs one value for each header field. This row is missing the stock value, so the imported columns would not line up. It should be Pencil,1.5,200.