| 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.
Thursday, July 30, 2015
SQL tutorial - Lesson 10 - Full outer join
Full outer join returns all the rows from left table and also returns all the rows from the right table
Syntax:
Select "column name" from "table1" full outer join "table2" on table1.coulmn =table2.column
Note: Please do not use quotes when trying to type the query I just mentioned so that you can understand better
Syntax:
Select "column name" from "table1" full outer join "table2" on table1.coulmn =table2.column
Note: Please do not use quotes when trying to type the query I just mentioned so that you can understand better
Wednesday, July 29, 2015
SQL tutorial - lesson 9 - right join
Right join displays all the rows from the right side table along with the matching rows from the left side table.If there are no matching rows on the left side table it will display null.
Note: please do not use ( " ) when you are trying to practice as I used them so that you can understand
Syntax:
Select
"column name" from "table 1" Right join "table 2"
on table1.coulmn =table2.column
Note: please do not use ( " ) when you are trying to practice as I used them so that you can understand
SQL tutorial - Lesson 8 - Left Join
Left join returns all the rows from the left side table along with the matching rows from the right side table. and if there is no match on the right side table it display null
syntax: select "column name" from "table1" left join "table2" on table1.coulmn =table2.column
Please look at the picture below for better understanding
When you are writing the query. do not use quotes ( " ) I used it so that you can understand the fields that I am trying to explain
syntax: select "column name" from "table1" left join "table2" on table1.coulmn =table2.column
Please look at the picture below for better understanding
When you are writing the query. do not use quotes ( " ) I used it so that you can understand the fields that I am trying to explain
Tuesday, July 28, 2015
SQL Tutorial - Lesson 7 - Inner Join
Inner joins selects the rows from both the tables and displays the result provided there should be a match in the columns of both the tables
For example I have 2 tables
Out put
For example I have 2 tables
Table1: tblcountry
|
Country id
|
Country Name
|
|
1
|
USA
|
|
2
|
UK
|
|
3
|
SA
|
|
4
|
AUS
|
|
Null
|
Null
|
Table2: tblstate
|
Country id
|
State ID
|
Name
|
|
1
|
01
|
LA
|
|
2
|
02
|
London
|
|
4
|
03
|
Sydney
|
When you write the query
Select * from tblcountry inner join tblstate on
tblcountry.country id=tblstate.country id
Out put
|
Country id
|
Country Name
|
State ID
|
Name
|
|
1
|
USA
|
01
|
LA
|
|
2
|
UK
|
02
|
London
|
|
4
|
AUS
|
03
|
Sydney
|
In the above query SQL joined both the tables which has a
match
SQL tutorial - lesson 6 - Joins
There are 4 different joins available.
1. Inner Join
2. Left Join
3.Right Join
4. Full outer Join
1. Inner Join
2. Left Join
3.Right Join
4. Full outer Join
Monday, July 27, 2015
SQL Tutorial - Lesson 5 - Where
Where clause in SQL is use to filter records based on a condition.
For example you wanted to filter employee data based on the manager you would write the command like this.
select * from emp where manager ="john";
In the above statement sql will search for the employee records who work under john and display the output.
For example you wanted to filter employee data based on the manager you would write the command like this.
select * from emp where manager ="john";
In the above statement sql will search for the employee records who work under john and display the output.
SQL Tutorial - Lesson 3 - Data Types
There are a lot of data types which are available in SQL. I would give you few of them
|
Data Type
|
Description
|
|
INTEGER
|
Integer number
|
|
CHARACTER
|
Character string
|
|
SMALLINT
|
Small integer
|
|
DATE
|
iT stores year, month and days values.
|
|
TIME
|
it stores hour, minute and second values
|
|
FLOAT
|
Float
|
|
TIMESTAMP
|
Stores year, month, day, hour, minute, and second values
|
|
DECIMAL
|
Decimal (P,S)
|
SQL Tutorial - Lesson one - select statement
The select Statement is used to select the data from the database based on the condition and display the result
Syntax: Select * from EMP table;
I used the term * because I want select to select all the fields in the table and display the results from the EMP table
Syntax: Select * from EMP table;
I used the term * because I want select to select all the fields in the table and display the results from the EMP table
Subscribe to:
Posts (Atom)
