| Task Pane | |
|---|---|
| F1 | - display the Help task pane |
| CTRL + F1 | - open or close the current task pane |
| Workbook Window | |
| CTRL + F4 | - close the selected workbook window |
| CTRL + F5 | - restore the window size of the selected workbook window |
| CTRL + F6 | - switch to next workbook window when more than one workbook window is open - When the task pane is visible, the next workbook will include that pane when switching between panes |
| CTRL + F7 | - move the workbook window when it is not maximized - use ARROW keys to move the window - press ESC key when finished |
| CTRL + F8 | - resize the workbook window by ARROW keys when it is not maximized |
| CTRL + F9 | - minimize a workbook window to an icon |
| CTRL + F10 | - maximize or restore the selected workbook window |
| Worksheets | |
| ALT + SHIFT + F1 SHIFT + F11 | - insert a new worksheet |
| Cells | |
| F2 | - edit the active cell - point the cursor at the end of the cell contents |
| SHIFT + F2 | - create or edit a cell comment |
| F8 | - select multiple cells by ARROW keys |
| Dialog Box | |
| SHIFT + F3 | - display the Insert Function dialog box |
| F5 | - display the Go To dialog box |
| F7 | - display the Spelling dialog box to check spelling in the active worksheet or selected cells |
| ALT + F8 | - display the Macro dialog box to run, edit, or delete a macro |
| ALT + F11 | - open the Visual Basic Editor which you can create a macro by using Visual Basic for Applications (VBA) |
| ALT + SHIFT + F11 | - open the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any script code |
| F12 | - display the Save As dialog box |
| Menu | |
| F10 | - select the toolbar - close an open menu and submenu at the same time |
| SHIFT + F10 | - display the shortcut menu for a selected cell |
| Chart | |
| ALT + F1 F11 | - create a chart for the selected cells |
| Split Window | |
| F6 | - switch to next pane in a worksheet that has been split (Window > Split command) |
| SHIFT + F6 | - switch to previous pane in a worksheet that has been split |
May 19, 2009
Excel Shortcut Key
May 13, 2009
SQL Basic Syntax
Select Statement
It is used to retrieve a set of data from a database.
Select Distinct Statement
It is used to retrieve a set of data from a database without contains duplicate values.
Where Clause
It is used to extract only those records that fulfill a specified criterion. It could not be used with aggregate functions.
Order By Keyword
It is used to sort the result-set by a specified column in ascending(ASC) order or descending(DESC) order.
Group By Clause
It is used in conjunction with the aggregate functions to group the result-set by one or more columns.
Having Clause
It is used with aggregate functions.
It is used to retrieve a set of data from a database.
SELECT column_name
FROM table_name
FROM table_name
Select Distinct Statement
It is used to retrieve a set of data from a database without contains duplicate values.
SELECT DISTINCT column_name
FROM table_name
FROM table_name
Where Clause
It is used to extract only those records that fulfill a specified criterion. It could not be used with aggregate functions.
SELECT column_name
FROM table_name
WHERE expression
FROM table_name
WHERE expression
Order By Keyword
It is used to sort the result-set by a specified column in ascending(ASC) order or descending(DESC) order.
SELECT column_name
FROM table_name
WHERE expression
ORDER BY column_name ASC
FROM table_name
WHERE expression
ORDER BY column_name ASC
Group By Clause
It is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE expression
GROUP BY column_name
FROM table_name
WHERE expression
GROUP BY column_name
Having Clause
It is used with aggregate functions.
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Example:
SELECT column_name1,SUM(column_name2)
FROM table_name
WHERE expression
GROUP BY column_name1
HAVING SUM(column_name2)< 2000
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Example:
SELECT column_name1,SUM(column_name2)
FROM table_name
WHERE expression
GROUP BY column_name1
HAVING SUM(column_name2)< 2000
May 06, 2009
SQL Functions
COUNT() - returns the number of rows of the specified column.
AVG() - returns the average value of a numeric column.
SUM() - returns the sum of a specific column.
MAX() - returns the largest value of a specific column.
MIN() - returns the smallest value of a specific column.
FIRST() - returns the first value of a specific column.
LAST() - returns the last value of a specific column.
MID()
- Extract characters from a text field.
- start_position: the default starting position is 1.
- length: the number of characters to return (optional)
UCASE() - converts a value to upper case.
LCASE() - converts a value to lower case.
LEN() - returns the length of a text field.
ROUND()
- Rounds a numeric field to the number of decimals specified.
- decimal: specifies the number of decimals to be returned.
FORMAT()
- formats how a field is to be displayed.
- format: specifies the format.
SELECT COUNT(column_name)
FROM table_name
WHERE expression
SELECT COUNT(DISTINCT column_name)
FROM table_name
WHERE expression
FROM table_name
WHERE expression
SELECT COUNT(DISTINCT column_name)
FROM table_name
WHERE expression
AVG() - returns the average value of a numeric column.
SUM() - returns the sum of a specific column.
MAX() - returns the largest value of a specific column.
MIN() - returns the smallest value of a specific column.
FIRST() - returns the first value of a specific column.
LAST() - returns the last value of a specific column.
Example:
SELECT AVG(column_name)
FROM table_name
WHERE expression
SELECT AVG(column_name)
FROM table_name
WHERE expression
MID()
- Extract characters from a text field.
- start_position: the default starting position is 1.
- length: the number of characters to return (optional)
SELECT MID(column_name, start_position, length)
FROM table_name
FROM table_name
UCASE() - converts a value to upper case.
LCASE() - converts a value to lower case.
LEN() - returns the length of a text field.
Example:
SELECT UCASE(column_name)
FROM table_name
SELECT UCASE(column_name)
FROM table_name
ROUND()
- Rounds a numeric field to the number of decimals specified.
- decimal: specifies the number of decimals to be returned.
SELECT ROUND(column_name, decimal)
FROM table_name
FROM table_name
FORMAT()
- formats how a field is to be displayed.
- format: specifies the format.
SELECT FORMAT(column_name, format)
FROM table_name
Example:
SELECT FORMAT(column_name, 'YYYY-MM-DD') AS New_Date
FROM table_name
FROM table_name
Example:
SELECT FORMAT(column_name, 'YYYY-MM-DD') AS New_Date
FROM table_name
SQL Basic Syntax
Create Statement
It is used to create a new database.
It is used to create a new table.
It is used to create a new column in a table.
Drop Statement
It is used to delete an existing database.
It is used to delete an existing table.
It is used to delete an existing column in a table.
Rename Statement
It is used to rename an existing table.
It is used to rename an existing column.
Insert Statement
It is used to insert a new row of data to a table.
It allows to insert multiple rows into a table. You can retrieve the data from another table and insert it into a new table.
Update Statement
It is used to update an existing record in a table.
Delete Statement
It is used to delete rows in a table.
It is used to create a new database.
CREATE DATABASE database_name
CREATE TABLE table_name (column_name1 data_type1 NULL|NOT NULL, column_name2 data_type2 NULL|NOT NULL)
ALTER TABLE table_name
ADD column_name data_type NULL|NOT NULL
ADD column_name data_type NULL|NOT NULL
Drop Statement
It is used to delete an existing database.
DROP DATABASE database_name
DROP TABLE table_name
ALTER TABLE table_name
DROP COLUMN column_name
DROP COLUMN column_name
Rename Statement
It is used to rename an existing table.
sp_rename existing_table_name, new_table_name
sp_rename existing_column_name, new_column_name, 'COLUMN'
Insert Statement
It is used to insert a new row of data to a table.
INSERT INTO table_name (column_name1, column_name2)
VALUES (value1, value2)
VALUES (value1, value2)
INSERT INTO table_name1 (column_name1, column_name2)
SELECT column_name3, column_name4 FROM table_name2
SELECT column_name3, column_name4 FROM table_name2
Update Statement
It is used to update an existing record in a table.
UPDATE table_name
SET column_name1 = value1, column_name2 = value2
WHERE expression
SET column_name1 = value1, column_name2 = value2
WHERE expression
Delete Statement
It is used to delete rows in a table.
DELETE FROM table_name
WHERE expression
WHERE expression
May 05, 2009
Usage of Common Data Types
| Data Type | Size | Description |
|---|---|---|
| Bigint | 8 bytes | - Range of value: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 - Identity Specification: Yes |
| Uniqueidentifier | 16 bytes | - Format: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx - Default Value or Binding: NEWID() |
| Bit | 1 byte (1-8 bit) 2 bytes (9-16 bit) | - TRUE will converted to 1 - FALSE will converted to 0 |
| Smallint | 2 bytes | - Range of value: -32,768 to 32,767 |
| Int | 4 bytes | - Range of value: -2,147,483,648 to 2,147,483,647 |
| Tinyint | 1 byte | - Range of value: 0 to 255 |
| Float | 4 bytes (1-24) 8 bytes (25-53) | - Avoid using in WHERE clause, especially = and <> - It only used in > or < - Default value: float(53) |
| Real | 4 bytes | - Avoid using in WHERE clause, especially = and <> - It only can use in > or < - It equals to float(24) |
| Decimal(p, s) | 5 bytes (1-9p) 9 bytes (10-19p) 13 bytes (20-28p) 17 bytes (29-38p) | - It will not round up the value - precision(p): Number of digit - scale(s): Number of decimal point |
| Money | 8 bytes | - Range of value: -922,337,203,685,477.5807 to 922,337,203,685,477.5807 - It will round up the value |
| Smallmoney | 4 bytes | - Range of value: -214,748.3647 to 214,748.3647 - It will round up the value |
| Smalldatetime | 4 bytes | - Range of value: Jan 1, 1900 to Jun 6, 2079 - accuracy of 1 minute |
| DateTime | 8 bytes | - Range of value: Jan 1, 1753 to Dec 31, 9999 - accuracy of 3.33 milliseconds - Default Value or Binding: getdate() |
| DateTime2 | 6-8 bytes | - Range of value: Jan 1, 0001 to Dec 31, 9999 - accuracy of 100 nanoseconds |
| DateTimeOffset | 8-10 bytes | - It same as datetime2 with the addition of a time zone offset |
| Date | 3 bytes | - Range of value: Jan 1, 0001 to Dec 31, 9999 |
| Time | 3-5 bytes | - accuracy of 100 nanoseconds |
| Timestamp | 8 bytes | - Any update made will changes the timestamp value - It cannot be used as primary key |
| Char(n) | - Maximum length: 8,000 characters - Non-Unicode - Fixed-length: use all the space that defines | |
| NChar(n) | - Maximum length: 4,000 characters - Accept unicode (e.g. Chinese) - Fixed-length: use all the space that defines | |
| VarChar(n) | - Maximum length: 8,000 characters - Non-Unicode - Variable-length | |
| NVarChar(n) | - Maximum length: 4,000 characters - Accept Unicode (e.g. Chinese) - Variable-length | |
| Text | - Maximum length: 2,147,483,647 characters - Accept ASCII code - Variable-length | |
| NText | - Maximum length: 1,073,741,823 characters - Unicode (e.g. Chinese) - Variable-length - It do not allow for sorting, group by, and distinct | |
| Binary(n) | - Maximum length: 8,000 bytes - Fixed-length: use all the space that defines | |
| Varbinary(n) | - Maximum length: 8,000 bytes - Variable-length | |
| Image | - Maximum length: 2,147,483,647 bytes - Variable-length - BMP, TIFF, GIF, or JPEG format | |
