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.