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