Monday, August 3, 2015

Excel Tutorial - lesson 5 - UPPER

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.

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.

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";

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;

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

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;



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;

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

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.

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.

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.

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.

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.

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.

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.


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



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.

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

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.

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

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


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


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.



SQL Tutorial - Lesson 4 - Drop Table

Drop Table is a statement which can be used to drop a table.

You should be careful when you are trying to use this command as one the table is dropped data in the table is also lost.

syntax:  Drop table EMP;

When you run the above command sql will drop the table with the name EMP.



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 2 - create table

Create table statement is used to create a table.

syntax:
Create table Employee
(
Empid int;
Name Varchar(20);
Address Varchar(50);
);

In the above Example  Employee is the table name and Empid, Name and Address are the different fields.


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