Word processing has been around for decades now. There are many people who do not know how to use the most basic functions yet.
Create/Save Documents
Use document template
Format text; bold, italic, underline, font face, font size, font colour
Paragraph alignment; left, centre, right, justify
Search and replace with different text
Spell check; correct errors, understand names are not errors.
Adjust page layout.
Open and view different toolbars
Open and switch between different documents
Insert headers and footers different examples for each
Save file in a different format eg pdf
Tables; insert table; delete and insert rows and columns.
Insert images; modify to position and resize
Print documents; preview mode, print.
A database is a compilation of information that is organized so that it can be easily accessed
A table is a structured collection of related data within a database
A single table database is a database that consists of only one table
An example of a single table database is a simple counter. The counter writes its count to a database that contains one table, Table. As the counter increments, the field Counter_Column is updated with the new value, and resets to 0 when reset. The simple database consists of a field value, in the column Counter_Column, in the table Table.
A table stores mass amounts of information in tabular form
A field (or column) show vertically in a table
Each column has a column name
This column name also presents the field name in a table
The column value makes up the field's value
A record (or row) holds the particular grouped information for an item in a table
A key field is a compulsory unique identifying field given to each record
It often consists of a unique numeral, such as a Student ID number
Source: W3Resource.com
This article will show you some examples of simple queries using SQL
Each query will provide the database, table and column names necessary to perform the task
Try coming up with some of your own simple queries to test!
This query will simply return all details about all students in our "Students" table.
Database name: myDatabase
Table name: Students
Query:
SELECT * FROM `Students`.`myDatabase`
Note: appending the database name to the table name isn't necessary and is up to the developer's preferences
This query will return the first name (FirstName) and last name (LastName) of all students in a database with a "male" gender.
Database name: myDatabase
Table name: Students
Query:
SELECT `FirstName`, `LastName` FROM `Students`.`myDatabase` WHERE `Gender` = `Male`
This query will insert a new student in to the Students table of our database.
Database name: myDatabase
Table name: Students
Query:
INSERT INTO `Students` VALUES(`John`, `Doe`, `Male`)
This query will update a student's gender and change it to "Female".
Database name: myDatabase
Table name: Students
Query:
UPDATE `Students` SET `Gender` = `Female` WHERE `FirstName` = `Mary`
This query will delete all records of students who have graduated according to our database.
Database name: myDatabase
Table name: Students
Query:
DELETE FROM `Students` WHERE `Schooling_Status` = `Graduated`
Forms are commonly used for entering information into a database
They're graphical user interfaces with command buttons and text boxes capable of entering, manipulating and even deleting data
Forms are the preferred method of data entry due to their graphical nature and ease of use
(Source)
Each set of information brought up belongs to a database record (row)
Forms refer to database columns in the form of textboxes
The textboxes present the data for existing fields, and act as entry points for new additions
The buttons down the bottom allow for navigating through records
Information written in the textboxes next to each label are saved as the column (or field) value
Searching content is something we take for granted
In the olden days, there were no websites to even Ctrl-F on!
Mastering searching is a key skill that can make browsing the web much more efficient
These techniques are for search engines such as Google. Try think of some other techniques that you could incorporate in to your own search engine!
Use quotation marks
Using quotation marks will search for exact phrases.
Example: Dr. Seuss quote "I do not like green eggs and "
Cut-down on unncessary words
Sometimes we write words that are unnecessary for a search query.
Example: "Please help me find a recipe for Lava Cakes" -> "lava cake recipe"
Capitalization
Most search engines can't distinguish between capital letters and lowercase letters. Don't waste time on capitalizing words when trying to find something quickly.
Search a specific site
Search engines can show you results for a specific website.
Example: Simple search techniques site:thecomputingteacher.com
Wildcard searches
Some databases allow for wildcard searches. This means that you can use a character to represent "ALL". Most databases utilise the asterisk ("*") as a wildcard.
Example: three blind *
Find a word in the title, content or URL
Some search engines allow for searching within a website's title, content and URL.
Example: intitle:The Computing Teacher
Example: intext:databases utilise the
Example: inurl:computingteacher.com
How to Find Anything Online with Advanced Search Techniques on TutsPlus here
TechRepublic's 10 tips for smarter, more efficient Internet searching here
Spreadsheet software is very good at crunching numbers
It is capable of many simple and complex calculations
Some software provide preset templates such as financial sheets for businesses and budget documents for individuals
Read The Computing Teacher's article on Simple Functions here
To add cell values together, use the + character
Example: Cell A3 must equal the sum of B1 and D9
Formulae: = B1 + D9
To subtract a cell's value from another call, use the - character
Example: Cell F5 must equal the sum of D3 minus A6
Formulae: = D3 - A6
To multiply the values of two or more cells, use the * character
Example: Cell C4 must equal the product of H8 and J4
Formulae: = H8 * J4
To divide a value by another cell's value, use the / character
Example: Cell B9 must equal the value of E5 divided by E3
Formulae: = E5 / E3
Excel formulae cheatsheet by PCWorld here
Excel functions and formulas list by Tech On The Net here
Watch Excel Spreadsheet Basics on YouTube here
lookup tables help us find data in tables
in the old days, to find the price of a car part from a car part shop, the dude had to look up a book to find the price.
open the book
find your car model
find the car part
look across on the page for the column that had the cost
when the price changed, they had to bring out a new book. hmm not the best system.
nowadays, the same information can be found in a spreadsheet or database
yes it can be updated easier as well.
Download the attached spreadsheet which takes you through vLookups.
Try to make our own hLookup of their example for the vegetable table.
Try to do the vegetable example with the drop down list too.
see our AIT site
See https://it.usu.edu/plugins/work/sitemaps/107/files/VLOOKUP.pdf
See http://www.techonthenet.com/excel/formulas/lookup.php
See https://www.timeatlas.com/vlookup-tutorial/
A function is a piece of code that, when run, performs a specific pre-defined taks
Spreadsheet software can also be used to create simple and even complex functions
Below are some basic functions that can be implemented in spreadsheet software such as Microsoft Excel
Read The Computing Teacher's article on Simple Formulae here
This function returns the sum of two (or more) cells
Example : =sum(B4:D4)
This function returns the average of two (or more) cells
Example: =average(G3 + F9)
For use with a range of cells: =average(B4:D10)
This function returns the minimum value out of the range of cells provided
Example: =min(B4:D10)
This function returns the maximum value out of the range of cells provided
Example: =max(B4:D10)
This function returns the tally of cells that have a value
In this example it is used to find out how many students are in the class
Example: =count(E4:E10)
This function returns the number of cells that meet a criteria
Example: =countif(range, criteria)
In this example in the cells from E4 to E10, count how many cells have a value greater than 10
Working with basic functions by GCF LearnFree.org here
Excel formulae cheatsheet by PCWorld here
Excel functions and formulas list by Tech On The Net here
Watch Excel Spreadsheet Basics on YouTube here
Quiz below.
vLookup - excellent help here
vLookup help 2 here
Term
Meaning
Example
Cell
A cell is a box created by the crossing of a column and a row. The cell value is referenced with coordinates made up from the row and columns.
A1 is composed of a vertical column, A, and a horizontal row, 1.
Formula
A formula is an expression that is used to calculate the value of a cell.
=(A1 + Z10)
A function is a pre-defined formula that are readily available in a spreadsheet software.
- Sum
Returns the sum of the given range of cells.
=SUM(A1:Z10)
- Average
Returns the average of the given range of cells.
=AVERAGE(A1:Z10)
- Max
Returns the value of the cell with the largest value.
=MAX(A1:Z10)
- Min
Returns the value of the cell with the smallest value.
=MIN(A1:Z10)
- Count
Returns the counted number of cells with a value in the given range.
=COUNT(A1:A10)
- Countif
Returns the counted number of cells with specified criterion.
=COUNTIF(A1:Z10, "Hello") [Shows the count of cells with the word'Hello']
=COUNTIF(A1:Z10, 3) [Shows the count of cells with a value of '3']
Label
Commonly referred to as a heading used to identify a column in a spreadsheet.
"Date" - e.g. a column consisting of date values.
Worksheet
A worksheet is a single spreadsheet made up of rows and columns that meet to create cells.
"Worksheet 1"
Lookup tables
These functions are used to reference values in cells, either in the same worksheet (locally) or external worksheets.
LOOKUP / VLOOKUP / HLOOKUP
- VLookup
VLOOKUP is a function used when you need to find values from a table, or a specific range by row. For example, a spreadsheet could utilize VLOOKUP to find an employee's email address by looking up their last name. Note: the key to VLOOKUP is to organize data in such a way that the value we know (e.g. their last name) is to the left of the value we want to find (e.g. their email address).
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP("Bloggs",B2:D4,2)
In this example, we're looking for the 2nd column's value from the ranges B2:D4, but only where "Bloggs" is found.
- HLookup
HLOOKUP works in the same way that VLOOKUP works, except with one difference: this looks up information specified by a row as opposed to a column. This means that it returns a value from the same column as a row is in.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
=HLOOKUP("Chocolates",A1:C4,2)
In this example, we're looking up "Chocolate" in row 1 and returns the value from row 2 in the same column of the given range (A1:C4).
A cell is the smallest part of a spreadsheet
A row is the line along a spreadsheet from left to right.
A column is the line down a spreadsheet from top to bottom.
A spreadsheet can have multiple worksheets - these are the tabs at bottom of the spreadsheet.
Answer these.
What is the difference between a label and a text or number in a cell?
List the 6 most common functions.