| UPPER | ||||
| Lower Case Text | Upper Case | |||
| alan jones | ALAN JONES | =UPPER(C4) | ||
| bob smith | BOB SMITH | =UPPER(C5) | ||
| carol williams | CAROL WILLIAMS | =UPPER(C6) | ||
| cardiff | CARDIFF | =UPPER(C7) | ||
| abc123 | ABC123 | =UPPER(C8) | ||
| What Does It Do ? | ||||
| This function converts all characters in a piece of text to upper case. | ||||
| Syntax | ||||
| =UPPER(TextToConvert) | ||||
| Formatting | ||||
| No special formatting is needed. | ||||
| Example | ||||
| See the example for FREQUENCY. | ||||
Monday, August 3, 2015
Excel Tutorial - lesson 5 - UPPER
Excel Tutorial - lesson 4 - LOWER
| LOWER | ||||
| Upper Case Text | Lower Case | |||
| ALAN JONES | alan jones | =LOWER(C4) | ||
| BOB SMITH | bob smith | =LOWER(C5) | ||
| CAROL WILLIAMS | carol williams | =LOWER(C6) | ||
| CARDIFF | cardiff | =LOWER(C7) | ||
| ABC123 | abc123 | =LOWER(C8) | ||
| What Does It Do ? | ||||
| This function converts all characters in a piece of text to lower case. | ||||
| Syntax | ||||
| =LOWER(TextToConvert) | ||||
| Formatting | ||||
| No special formatting is needed. | ||||
Excel Tutorial - lesson 3 - RIGHT
| RIGHT | ||||||
| Original Text |
Number Of Characters Required |
Right String |
||||
| Alan Jones | 1 | s | =RIGHT(C4,D4) | |||
| Alan Jones | 2 | es | =RIGHT(C5,D5) | |||
| Alan Jones | 3 | nes | =RIGHT(C6,D6) | |||
| Cardiff | 6 | ardiff | =RIGHT(C7,D7) | |||
| ABC123 | 4 | C123 | =RIGHT(C8,D8) | |||
| What Does It Do ? | ||||||
| This function displays a specified number of characters from the right hand side of a | ||||||
| piece of text. | ||||||
| Syntax | ||||||
| =RIGHT(OriginalText,NumberOfCharactersRequired) | ||||||
| Formatting | ||||||
| No special formatting is needed. | ||||||
| Example | ||||||
| The following table was used to extract the second name of a person from their full name. | ||||||
| The =FIND() function locates the position of the space between the first and second name. | ||||||
| The length of the second name is calculated by subtracting the position of the space from | ||||||
| the overall length of the full name. | ||||||
| The =RIGHT() function can then extract the second name. | ||||||
| Full Name | Second Name | |||||
| Alan Jones | Jones | =RIGHT(C28,LEN(C28)-FIND(" ",C28)) | ||||
| Bob Smith | Smith | =RIGHT(C29,LEN(C29)-FIND(" ",C29)) | ||||
| Carol Williams | Williams | =RIGHT(C30,LEN(C30)-FIND(" ",C30)) | ||||
Excel tutorial - lesson 2 - LEFT
| LEFT | ||||||
| Text | Number Of Characters Required |
Left String | ||||
| Alan Jones | 1 | A | ||||
| Alan Jones | 2 | Al | ||||
| Alan Jones | 3 | Ala | ||||
| Cardiff | 6 | Cardif | ||||
| ABC123 | 4 | ABC1 | ||||
| What Does It Do ? | ||||||
| This function displays a specified number of characters from the left hand side of a | ||||||
| piece of text. | ||||||
| Syntax | ||||||
| =LEFT(OriginalText,NumberOfCharactersRequired) | ||||||
| Formatting | ||||||
| No special formatting is needed. | ||||||
| Example | ||||||
| The following table was used to extract the first name of a person from their full name. | ||||||
| The =FIND() function was used to locate position of the space between the first and second name. | ||||||
| The length of the first name is therefore the position of the space minus one character. | ||||||
| The =LEFT() function can now extract the first name based on the position of the space. | ||||||
| Full Name | First Name | |||||
| Alan Jones | Alan | =LEFT(C27,FIND(" ",C27)-1) | ||||
| Bob Smith | Bob | =LEFT(C28,FIND(" ",C28)-1) | ||||
| Carol Williams | Carol | =LEFT(C29,FIND(" ",C29)-1) | ||||
Excel tutorial - lesson 1- CONCATENATE
| CONCATENATE | |||||
| NAME1 | NAME2 | Concatenated Text | |||
| Alan | Jones | Alan Jones | =CONCATENATE(C4,D4) | ||
| Bob | Williams | Bob Williams | =CONCATENATE(C5,D5) | ||
| Carol | Davies | Carol Davies | =CONCATENATE(C6,D6) | ||
| Alan | Jones | Alan Jones | =CONCATENATE(C7," ",D7) | ||
| Bob | Williams | Bob Williams | =CONCATENATE(D8,", ",C8) | ||
| Carol | Davies | Carol Davies | =CONCATENATE(D9,", ",C9) | ||
| What Does It Do? | |||||
| This function joins separate pieces of text into one item. | |||||
| Syntax | |||||
| =CONCATENATE(Text1,Text2,Text3...Text30) | |||||
| Formatting | |||||
| No special formatting is needed, the result will be shown as normal text. | |||||
| Note | |||||
| You can achieve the same result by using the & operator. | |||||
| Name 1 | Name 2 | Concatenated Text | |||
| Alan | Jones | Alan Jones | =LEFT(C4,D4) | ||
| Bob | Williams | Bob Williams | =LEFT(C5,D5) | ||
| Carol | Davies | Carol Davies | =LEFT(C6,D6) | ||
| Alan | Jones | Alan Jones | =LEFT(C7,D7) | ||
| Bob | Williams | Bob Williams | =LEFT(C8,D8) | ||
| Carol | Davies | Carol Davies | |||
SQL tutorial - lesson 26 - Truncate
If you want to remove all the records in the table then you can use Truncate as Truncate will delete all the records in the table but it will not delete the structure
syntax:
truncate table emp;
when you run the above query it will remove all the records from the table.
syntax:
truncate table emp;
when you run the above query it will remove all the records from the table.
SQL tutorial - lesson 25 - View
A view is a virtual table.
A view can be created based on two or more tables.
What ever data is present in the view cannot be modified. but you run queries on it .
syntax:
create view "view name" as SELECT column1, column2, column3..... FROM table_name WHERE "condition";
A view can be created based on two or more tables.
What ever data is present in the view cannot be modified. but you run queries on it .
syntax:
create view "view name" as SELECT column1, column2, column3..... FROM table_name WHERE "condition";
SQL tutorial - lesson 24 - insert into
If you want to insert a record into table or if you want to copy a record from one table to an another we use insert into
syntax:
INSERT INTO emp (empName, Country) SELECT empno, Country FROM empdata;
syntax:
INSERT INTO emp (empName, Country) SELECT empno, Country FROM empdata;
Sunday, August 2, 2015
SQL tutorial - lesson 23 - Delete
If you want to delete records in a table then you use delete statement
syntax:
Delete from emp where empno=2001;
in the above sql query . SQL will delete the record where empno=2001
syntax:
Delete from emp where empno=2001;
in the above sql query . SQL will delete the record where empno=2001
SQL Tutorial - Lesson 22 - Drop table,Drop Index and Drop Database
If you want to drop the table then you use drop table.
Syntax:
Drop table table name;
If you want to drop index in a table then you use drop index
Syntax:
Drop index tablename.indexname;
and lastly if you want to drop a database then you use drop database
Syntax:
Drop database databasename;
Syntax:
Drop table table name;
If you want to drop index in a table then you use drop index
Syntax:
Drop index tablename.indexname;
and lastly if you want to drop a database then you use drop database
Syntax:
Drop database databasename;
SQL Tutorial - lesson 21 - Alter
If you want to modify the existing table to add, modify or delete columns then you use Alter table.
using Alter you can modify the existing table, add a column, delete a colum
Syntax:
Alter table emp add dateofbirth date;
using Alter you can modify the existing table, add a column, delete a colum
Syntax:
Alter table emp add dateofbirth date;
Saturday, August 1, 2015
SQL tutorail - lesson 20 - Mid function
SQL mid function is used to extract the text from the text field that you specify
syntax:
select Mid(empname,1,5) as empshortname from emp;
when you run the above the SQL query, SQL will extract the test form the emp table
there empname is the column.
1 indicates from where to start
and 5 indicates that how many letters it should extract
syntax:
select Mid(empname,1,5) as empshortname from emp;
when you run the above the SQL query, SQL will extract the test form the emp table
there empname is the column.
1 indicates from where to start
and 5 indicates that how many letters it should extract
SQL tutoail - lesson 19 - len function
If you want to check the length of any particular text field then you use len( ) function
syntax:
select empname, len(empaddress) as length of employee adress from emp;
When you run the above SQL query, SQL will return the length of the employee address.
syntax:
select empname, len(empaddress) as length of employee adress from emp;
When you run the above SQL query, SQL will return the length of the employee address.
SQL Tutorial - lesson 18 - SUM function
If you wanted to calculate the sum of any numeric field in the table then you use sum function
syntax:
select sum(quantity) as total quantity from products;
When you run the above SQL query, SQL will add all the value under the quantity and display the sum value.
syntax:
select sum(quantity) as total quantity from products;
When you run the above SQL query, SQL will add all the value under the quantity and display the sum value.
SQL tutorial - Lesson 17 - max function
If you want to return the maximum value of the numeric field then you use max function
syntax:
select max(price) as maximum price from product;
when you run the above SQL query, SQL will return the maximum value of the products table.
syntax:
select max(price) as maximum price from product;
when you run the above SQL query, SQL will return the maximum value of the products table.
SQL tutorial - lesson 16 - Min function
If you want to return the min value of the selected numeric field then you use min function
syntax:
select min(price) as minimum price from products;
when you run the above SQL query, SQL will return the minimun price of the product in the products table.
syntax:
select min(price) as minimum price from products;
when you run the above SQL query, SQL will return the minimun price of the product in the products table.
SQL tutorial - lesson 15 - Last function
If you want to return the last value of any particular column then you use last funcion
syntax:
select last(empname) as lastname from emp;
when you run the above SQL command SQL will return the last value of the empname column from the emp table.
syntax:
select last(empname) as lastname from emp;
when you run the above SQL command SQL will return the last value of the empname column from the emp table.
SQL tutorial - lesson 14 - First function
If you wanted to get the first value of the selected column then you use first () function.
syntax:
select first(emp name) as emp first name from empl;
when you run the above statement sql will return the first name of the emp table.
syntax:
select first(emp name) as emp first name from empl;
when you run the above statement sql will return the first name of the emp table.
SQL tutorial - lesson 13 - count function
If you want to count the number of rows matching the given condition or if you want to see the number of rows that matches a specified criteria then you can use count function
syntax:
select count(employee id) as total employee from emp;
when you run the above statement it will count the number of employee ids and it will return the total count from the emp table.
syntax:
select count(employee id) as total employee from emp;
when you run the above statement it will count the number of employee ids and it will return the total count from the emp table.
SQL tutorial - lesson 12 - AVG function
If you want to calculate average value of any column in the table then you use average function
syntax:
select avg( price) as avg price from products;
when you run the above command sql will return the average price of the price in the products table
syntax:
select avg( price) as avg price from products;
when you run the above command sql will return the average price of the price in the products table
SQL tutorial - lesson 11 - having clause
Having is used when you are using aggregate functions in the the statement. for example you cannot use where clause when you are using aggregate functions but having clause can be used
both where and having clause can be used for conditions but where cannot be used if you are using aggregate functions.
syntax:
select * from employees where emp region = "USA" Having avg(sal) > 25000;
In the above statement you cannot put where in the place of having as it is aggregate.
when you run the above statement sql will return the employee details whose region is USA with the average salary more than 25000.
both where and having clause can be used for conditions but where cannot be used if you are using aggregate functions.
syntax:
select * from employees where emp region = "USA" Having avg(sal) > 25000;
In the above statement you cannot put where in the place of having as it is aggregate.
when you run the above statement sql will return the employee details whose region is USA with the average salary more than 25000.
Subscribe to:
Posts (Atom)