Thursday, December 12

Miscellaneous Tasks 2

CREATE TABLE Items
(
Id               INT,
Name         VARCHAR(50),
Cost          MONEY
);


INSERT INTO Items VALUES 
(1, 'Pen', 20),(1, 'Pencil', 30),
(2, 'Pencil', 30),(3, 'Pen', 30),
(3, 'Pencil', 40),(4, 'Pen', 30),
(5, 'Paper', 40),(5, 'Pencil', 30),
(6, 'Pencil', 40);


Select * from  Items;

Id     
Name
Cost        
1
Pen
20.00
1
Pencil
30.00
2
Pencil
30.00
3
Pen
30.00
3
Pencil
40.00
4
Pen
30.00
5
Paper
40.00
5
Pencil
30.00
6
Pencil
40.00

The Output should be,

Product
Number_of_Product
Total_Cost        
Pencil
2
70.00
Pen
1
30.00
Pencil/Paper
1
70.00
Pencil/Pen
2
120.00



Solution:

select distinct(Product),COUNT( distinct(Id)) as   
[Number_of_Product],sum(cost) as Total_Cost from   
(                                                  
select                                             
STUFF((Select '/'+ Name                            
from Items t1                                      
where t1.Id=t2.Id                                  
FOR XML PATH('')),1,1,'')                          
as Product,Cost,Id from Items  t2)gsf              
group by Product                                   

INSERT INTO SELECT - SQL

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.

Syntax:

INSERT INTO Table_A         --here it will insert records into Table_A from Table_B, if
SELECT * FROM TABLE_B;      data types are same.

If you want to insert from a specified column of a table to specified column of another table then we can write query as:

INSERT INTO Table_A (column_names) 
SELECT (column_names) FROM TABLE_B;

Example: Consider Table employee_A having same structure as Table employee. 

INSERT INTO employee_A
SELECT * FROM  employee
;

Output:





























Now Consider inserting from a specified column of a table to specified column of another table.

INSERT INTO employee_A (E_ID,E_Name)
SELECT ID,Name FROM employee;

Output:

























Here you could see that only two columns inserted from employee table.


Now consider using where condition along with select statement.

INSERT INTO employee_A (E_ID,E_Name)
SELECT ID,Name FROM employee 
Where Salary is null and Gender is null;

Output:





















You could see that only two such records exists in employee table which is inserted into employee_A table.


Wednesday, December 11

INSERT INTO - SQL

The INSERT INTO statement is used to insert new records in a table. It is a DML statement.

Syntax:


Here only values are specified for all corresponding columns in order.


INSERT INTO table_name                                         
VALUES (value1,value2,value3,...);                                                                         

or,

Here records are inserted in specified columns only.

INSERT INTO table_name (column1,column2,column3,...              
VALUES (value1,value2,value3,...);                                                                                                           
Example: We will insert values into Employee table, that we have created in Mytest_db                           database during previous sessions.

INSERT INTO employee
VALUES (1231,'Smith',15000,'M');
INSERT INTO employee
VALUES (1232,'Jones',15000,'M');

Note: From SQL Server 2008 onwards, the code can be simplified to the following.

INSERT INTO employee                                      VALUES (1233,'Alice',15000,'F'),(1234,'Biona',15000,'F');


Output:

Select * from dbo.employee;






















Now lets see to Insert data in specified columns;

INSERT INTO employee(ID,Name)
VALUES (1235,'Rijo'),(1236,'Ashni');
Output:
Select * from dbo.employee;
























The columns without values will display Null.

Thursday, December 5

Create Temporary Tables- Local/Global

Temporary tables are two types:

  •  Local Temporary Tables
  •  Global Temporary Tables

When you create Temporary Tables, 
They are created in -->System Databases -->tempdb database --> Temporary Tables .


Local Temporary Tables:

They are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. 

A local temporary table is created using #, like #employee.

Create Table #employee
    (
         ID int,
         Name Varchar(255),
         Salary Money,
         Gender char(1)
     );
     

select * from #employee;

















Global Temporary Tables:

They are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Global temporary table is created using ##, like ##employee.

Create Table ##employee
    (
         ID int,
         Name Varchar(255),
         Salary Money,
         Gender char(1)
     );
     

select * from ##employee;



Create Table Statement- SQL

Previously we saw how to create a database using Create Database Statement. Now lets create Tables in this " Mytest_db" database using Create Table Statement.


Create Table Statement (for creating Base Tables)

Syntax:
            Create Table table_name
                     (
                       Column_name1 datatype(size),     
                       Column_name2 datatype(size),
                       Column_name3 datatype(size),
                       Column_name4 datatype(size)
                      );

A Table is a set of rows and columns used to represent an entity. Each row represents an instance of the entity and each column for that row represents an attribute.

Eg:  
                Use Mytest_db;                    -- creates table inside database ' Mytest_db '

                Create Table employee
                     (
                        ID int,
                        Name Varchar(255),
                        Salary Money,
                        Gender char(1)
                     );


     
Result: You could see that ' employee ' table is created in ' Mytest_db '.  i.e. Only the structure of the table is created with columns ID, Name, Salary and Gender, Having no data in it. Base tables are persistent tables in a database.

Wednesday, December 4

Create Database statement -SQL

Create command is a DDL statement that is used to create databases, Tables in a database, Triggers, Stored Procedures, Functions, Views etc.


Create Database statement

Syntax:

                CREATE DATABASE dbname;

Now lets create a database called '' Mytest_db"


                CREATE DATABASE Mytest_db;




You could see that '' Mytest_db" is created.




Wednesday, November 27

Outer Joins- Full Outer Join

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the
right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT
and RIGHT joins.

Syntax:
  SELECT column_name(s)
  FROM table1
  FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Eg: Using AdventureWorksDW2008R2 Database

  select d.AccountKey,f.Financekey,d.AccountDescription, f.amount from DimAccount d
  FULL OUTER JOIN FactFinance f
  on d.AccountKey=f.AccountKey order by AccountKey asc;

Output:















To retain the non-matching information by including non-matching rows in the results of a
join, use a full outer join.

Outer Joins- Right Outer Join / Right Join


The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching
rows in the left table (table1). The result is NULL in the left side when there is no match.

Syntax-RIGHT JOIN 
  SELECT column_name(s)
  FROM table1
  RIGHT OUTER JOIN table2
  ON table1.column_name=table2.column_name;

Eg: Using AdventureWorksDW2008R2 Database

  select d.AccountKey,f.Financekey,d.AccountDescription, f.amount from DimAccount d
  RIGHT OUTER JOIN
  FactFinance f on d.AccountKey=f.AccountKey order by AccountKey asc;

Output:
when no match is found from left table DimAccount  for the corresponding rows of
the right table FactFinance, NULL result set will be displayed. In this case no nulls.

Outer Joins- Left Outer Join / Left Join

Outer Joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. 

Left Outer Join / Left Join

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching
rows in the right table (table2). The result is NULL in the right side when there is no match.

Syntax-LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Eg: Using AdventureWorksDW2008R2 Database

  select d.AccountKey, f.Financekey,
  d.AccountDescription, f.amount from DimAccount d
  LEFT OUTER JOIN FactFinance f
  on d.AccountKey=f.AccountKey order by AccountKey asc;

Output:

You counld see that NULL result set are displayed when no match is found from right
table FactFinance for the corresponding rows of the left table DimAccount .