JOINING CONCEPT , ALIASES CONCEPT , DISTINCT CLAUSE , SQL SERVER VARIBLE , IF...ELSE Statement varible , looping

-

 SQL Server: Joins

This SQL Server tutorial explains how to use JOINS, both INNER and OUTER JOINS, in SQL Server (Transact-SQL) with syntax, visual illustrations, and examples.

 

There are 4 different types of SQL Server joins:

  • SQL Server INNER JOIN (or sometimes called simple join)
  • SQL Server LEFT OUTER JOIN (or sometimes called LEFT JOIN)
  • SQL Server RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
  • SQL Server FULL OUTER JOIN (or sometimes called FULL JOIN)

So let's discuss SQL Server JOIN syntax, look at visual illustrations of SQL Server JOINS, and explore SQL Server JOIN examples.

INNER JOIN (simple join)

Chances are, you've already written a statement that uses an SQL Server INNER JOIN. It is the most common type of join. SQL Server INNER JOINS return all rows from multiple tables where the join condition is met.


create table inventa(pid int , pname varchar(200),rate  int );

create table productsu(pid int , brand  varchar(200));


SELECT inventa.pid, inventa.pname, productsu.pname FROM inventa INNER JOIN productsu ON inventa.pid = productsu.pid;


LEFT OUTER JOIN

Another type of join is called a SQL Server LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the LEFT OUTER JOIN in SQL Server (Transact-SQL) is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

 

SELECT inventa.pid, inventa.pname, productsu.brand

FROM inventa

LEFT OUTER JOIN productsu

ON inventa.pid = productsu.pid;


RIGHT OUTER JOIN

Another type of join is called a SQL Server RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

 

Syntax

The syntax for the RIGHT OUTER JOIN in SQL Server (Transact-SQL) is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

  

SELECT inventa.pid, inventa.pname, productsu.pname

FROM inventa

RIGHT  OUTER JOIN productsu

ON inventa.pid = productsu.pid;


FULL OUTER JOIN

Another type of join is called a SQL Server FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

SELECT inventa.pid, inventa.pname, productsu.pname

FROM inventa

FULL OUTER JOIN  productsu

ON inventa.pid = productsu.pid;


EQUI  JOINING 

What is Equi Join in SQL?

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality

SELECT agents.agent_name,customer.cust_name,customer.cust_city

FROM agents,customer WHERE agents.working_area=customer.cust_city;


What is a cross join?

A cross join is a type of join that returns the Cartesian product of rows from the tables in the join. In other words, it combines each row from the first table with each row from the second table. This article demonstrates, with a practical example, how to do a cross join in Power Query.1

Write a SQL query to combine each row of salesman table with each row of customer table.  Cross  joining

 

SELECT *

FROM salesman a

CROSS JOIN customer b;


================================0000=================0000=======

SQL Server: ALIASES

This SQL Server tutorial explains how to use ALIASES in SQL Server (Transact-SQL) with syntax and examples.


Description

SQL Server (Transact-SQL) ALIASES can be used to create a temporary name for columns or tables.

  • COLUMN ALIASES are used to make column headings in your result set easier to read.
  • TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are performing a self join (ie: listing the same table more than once in the FROM clause).
Note
  • If the alias_name contains spaces, you must enclose the alias_name in quotes.
  • It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name.
  • The alias_name is only valid within the scope of the SQL statement.


select pname as "Product name" from inventa invent;

select pname as product_name”  from inventa invent;

SELECT employee_id, first_name + last_name AS NAME
FROM employees
WHERE first_name = 'Sarah';



SQL Server: DISTINCT Clause

This SQL Server tutorial explains how to use the DISTINCT clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) DISTINCT clause is used to remove duplicates from the result set. The DISTINCT clause can only be used with SELECT statements.


SELECT DISTINCT name FROM student  ( duplicate value display one time )

SELECT DISTINCT name FROM student where  city='NGP';

SELECT DISTINCT name , city FROM student where  city='NGP' ORDER BY name;


Variables

SQL Server: Declare Variables

Learn how to declare variables in SQL Server (Transact-SQL) with syntax and examples.


What is a variable in SQL Server?

In SQL Server (Transact-SQL), a variable allows a programmer to store data temporarily during the execution of code.


DECLARE @techonthenet VARCHAR(50);

 

SET @techonthenet = 'Example showing how to declare variable';

 

DECLARE @site_value INT;

To assign a value to the @site_value variable, you can use the SET statement, as follows:

SET @site_value = 10;


Addition of two number 

DECLARE @NUM1 INT, @NUM2 INT ,@NUM3 INT;

SET @NUM1=10;

SET  @NUM2=20;

SET @NUM3=@NUM1+@NUM2;

print @NUM3;


SQL Server: IF...ELSE Statement

This SQL Server tutorial explains how to use the IF...ELSE statement in SQL Server (Transact-SQL) with syntax and examples. 

Description

In SQL Server, the IF...ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE.

DECLARE @site_value INT;

SET @site_value = 15;

 

IF @site_value < 6

   PRINT 'message1';

ELSE

   PRINT 'message2';

GO


Example - No ELSE condition

Since the ELSE condition is optional, you are not required to include the ELSE condition in the IF...ELSE statement in SQL Server.

Let's look at an example of how to exclude the ELSE condition from the IF...ELSE statement in SQL Server (Transact-SQL).

For example:

 

DECLARE @site_value INT;

SET @site_value = 15;

 

IF @site_value < 25

   PRINT 'message';

 

GO


======================program2====================

DECLARE @per INT;

SET @per = 23;

IF @per > 60

   PRINT 'fd';

else if ( @per > 50 and    @per < 60 )

   PRINT 'sd';

else if  (@per > 40  and   @per < 50 )

   PRINT 'td';

else

   PRINT 'fail';

 GO


===============================end ========

SQL Server: WHILE LOOP

This SQL Server tutorial explains how to use the WHILE LOOP in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server, you use a WHILE LOOP when you are not sure how many times you will execute the loop body and the loop body may not execute even once.


DECLARE  @a int;

set @a=1;

while @a<=10

  begin

         print @a;

         set @a=@a+1;

 

  end


===============================end=======================


display  varible  value  with message

 

declare @age INT;

declare @name  varchar(500);

set @age=10;

set @name='Mohan';

 

PRINT 'YOUR AGE IS=' + CAST(@age AS VARCHAR(10));

 

print 'name is=' + @name;





SQL Server CROSS JOIN

 

CROSS JOIN produces the result by combining all possibilities of the two or more tables that contain every row from all contributing tables. In other words, it returns the result by generating a paired combination of each row of all tables participating in the join operation. This join is sometimes also termed CARTESIAN JOIN, which provides the Cartesian product of all associated tables.


1.      SELECT column_lists    

2.      FROM table1    

3.      CROSS JOIN table2;    





SELECT *   

FROM inventa    

CROSS JOIN products;  



Comments

Post a Comment

Popular posts from this blog

Joining Assignment , Insert into select , database backup

-
  create   table salesman ( salesman_id   int , name varchar ( 500 ), city varchar ( 500 ) , commission int   );     create   table customer ( customer_id   int , cust_name varchar ( 500 ) , city varchar ( 500 ), grade int , salesman_id   int );     create   table orders ( ord_no int , purch_amt   int , customer_id int , salesman_id int ) create   table orders ( ord_no int , purch_amt   int , customer_id int , salesman_id int , ord_date varchar ( 20 ))     insert into salesman values ( 101 , 'Ramesh' , 'Nagpur' , '12.23' ) insert into salesman values ( 102 , 'suresh' , 'pune' , '15.23' ) insert into salesman values ( 103 , 'mahesh' , 'delhi' , '16.23' ) insert into salesman values ( 104 , 'raj' , 'pune' , '16.23' ) insert into salesman values ( 105 , 'rahul' , 'mumbai' , '17.23' )     ...
Read more

Komal Student Notes

-
  .net framework framework is a collection of tools and library which is used to applications development .net environment 1) programming environment 2) web environment 3) window environment 1) programming environment programming environment is represent console programming (c#) 2)web environment is represent using asp.net with c# 3) window environment :local application only software c# programming language :programming is a seris of valid step types of programming there are two types of programming language  1) code based programming language 2) form base    1) code based:-depend on code(c, c++, java etc)    2) form base :- based on tools(.net framework, android studio) programming environment user--->compiler(1.group 2.gives syntex error)---->interpriter(1.step by step 2.gives logical error) c# pure object oriented programming language 2) object oriented programming is based on class and object 3) structure of c#    header--->using ...
Read more

SQL SERVER SETTING

-
Image
  Sql server   notes another thing you should consider is maybe you installed sql server express edition. in this case you must enter ".\sqlexpress" as server name. or   .   dot only CREATE NEW TABLE  
Read more