New SQL Server development,MSBI-SSRS with Power BI batch details

Thanks

Srinivas

SQL Server new development Batch 27-Jan-16

Thanks

Srinivas

New SQL Server Dev Batch from 3rdOCT 6-7AM ISD

Team,

I am pleased to inform that I am going to start new SQL Server Dev Batch on 3rd Oct.

Let us know if any one interested.
REF:

Thanks
ADMIN

SCHEMAs in TSQL SQL SERVER

SCHEMA:

It is nothing but a logical container Under a database or we can group a set of objects under database by using a SCHEMA.

We can able to Create objects(Tables,views,procedures,functions,triggers etc) under a SCHEMA.

Creating a SCHEMA:

We can Create a schema by using the below syntax

syntax:

CREATE SCHEMA <SCHEMA_name>

Example:

CREATE SCHEMA SALES
CREATE SCHEMA EMPLOYEE

Creating an Object Under SCHEMA:

If we cant to Create an object under SCHEMA,we need to prefex the SCHEMA name before the object name as follows.

CREATE table SALES.TEST
 (
id int,
 name varchar(20)
 )

So we created the table called TEST under the SALES SCHEMA.

SELECT  * FROM TEST
--If  we used the above statement we will get an error that the object is not available,As the SQL server will look for a table under default SCHEMA dbo.So we need to write a Query as below.

SELECT  * FROM SALES.TEST

Like this we can Create any object under this SCHEMA and we can use it with SCHEMA.object Name where ever it requires.

Altering a SCHEMA:
we can Move an object from one SCHEMA to another by following the below syntax.

Syntax:

 ALTER SCHEMA <target_SCHEMA_name>
 transfer <source_SCHEMA_name.objectName>

example:

 ALTER SCHEMA SALES transfer dbo.dept

In the above example we are transfering the table dept from dbo SCHEMA to SALES SCHEMA,so that complete object willmove to SALES SCHEMA.

SELECT  * FROM  DEPT
 --in valid

SELECT  * FROM  SALES.DEPT
 --valid

Example 2:
Creating a view called VIEW1 in dbo SCHEMA as follows

 CREATE view VIEW1
 as
 select top 2 * from books

 SELECT  * FROM VIEW1

Transfering the view from dbo SCHEMA to SALES SCHEMA as folows

ALTER SCHEMA SALES transfer dbo.VIEW1

 SELECT  * FROM VIEW1
--in valid(throws an error)

 SELECT  * FROM SALES.VIEW1

Example 3:
Transfering an object from SALES SCHEMA to dbo SCHEMA(default)

ALTER SCHEMA dbo transfer SALES.TEST

 SELECT  * FROM TEST

 Droping a SCHEMA:

We can Drop the SCHEMA by using the below syntax

syntax:

 DROP SCHEMA <SCHEMA_name>

Example:

 DROP SCHEMA SALES
 --Cannot Drop SCHEMA 'SALES' because it is being referenced by object 'dept'.

NOTE:
 If we want to Drop a scehma we need to Drop all the objects unedr that SCHEMA or transfer all the obects to other scehmas and make the SCHEMA as empty then only we  can Drop a Schema.

I have two objects under the SALES Schema so i am unable to Drop the Schema.

 DROP view SALES.VIEW1
ALTER SCHEMA dbo transfer SALES.dept

I dropped one object under that Schema and trnsfered one object to another Schema and made the Schema empty now we are good to drop a Schema.

DROP SCHEMA SALES

Let me know if you need any more information on this.

Thanks
SQL SRINIVAS

Working with Variables in TSQL Programming

Variable:

A variable is nothing but a memory location whch can hold a value inside it.At any point of time we can have one value in the varibale.

We can use that varibale's value as part of our program logic implementation and we can override the value with new value if required.

We have two types of variables,those are

1)Local variables: These are declared by developers the scope of the varibale is withing that block of code.It will be preceding with single @

2)Global Variables:These are already declared by SQL SERVER,developers can use these variables,the scope of the variables are Global. These varibales can identifyable with @@
  
Ex: @@Fetch_status

Working with Local variables:

Declaring a variable in SQL Server:

Syntax:

DECLARE @<variable_name> datatype[size]

Example:

DECLARE @a int
DECLARE @name varchar(20)
DECLARE @tdate Date

We can delcare multiple variables in a single statement as follows

DECLARE @a int
                    ,@name varchar(20)
                    ,@tdate Date

2)How to Assign a value to a variable

To assign a static value:

   SET @<ariable_name>=value/expression

   SET @a=100
   SET @tdate=GETDATE( )
   SET  @name='SRINIVAS'

To assign a values from table:

We can bring the values from table and assign it a variables by using the below approach

   SELECT @variable_name1=col1(col1_value),@variable_name2=col2(col2_value)
   from <remaining SELECT statement>

 Example:

 DECLARE @tename varchar(20)
 DECLARE @tesal int
 DECLARE @teno int
 SET @teno=1003
 SELECT @tename=ename,@tesal=esal from employee where eno=@teno

3)Printing a value:

From program we can print anything with the help of print statment.

PRINT <statement>

PRINT 'srinivas'

Example 1:

DECLARE @a int
DECLARE @b int
SET @a=100
SET @b=200
PRINT @a+@b

We can optimise the above statement as bellow ways

Method 1:

DECLARE @a int ,@b int
SET @a=100
SET @b=200
PRINT @a+@b

Method 2:
DECLARE @a int=100
DECLARE @b int=200
PRINT @a+@b

Method 3:

DECLARE @a int=100,
                   @b int=200
PRINT @a+@b

All the above four statements are correct and represents the same.

Example 2

Write a Query which will print the empname and salary of a given number.

DECLARE @tename varchar(20)
DECLARE @tesal int
DECLARE @teno int
SET @teno=1003
SELECT @tename=ename,@tesal=esal from employee where eno=@teno
PRINT @tename
PRINT @tesal

Way 2:

DECLARE @tename varchar(20)
                   ,@tesal int
                  ,@teno int=1003
SELECT @tename=ename,@tesal=esal from employee where eno=@teno
PRINT @tename
PRINT @tesal

Let me know if you need any more information on this.

Thanks
SQL SRINIVAS

Priting a pyramid by using TSQL

Hi Team,

I have been observed that many of the interviews people are asking printing the pyramid like below by using SQL server.

 

So I am giving a script to that one

DECLARE @lclMaxLevel INT=5
DECLARE @lclPrintCount INT =0

WHILE @lclMaxLevel > 0
  BEGIN
      PRINT Space(@lclMaxLevel)
            + Replicate('*', @lclPrintCount)
            + Replicate('*', @lclPrintCount+1)

      SET @lclMaxLevel=@lclMaxLevel - 1
      SET @lclPrintCount=@lclPrintCount + 1
  END

Output as follows:

Let me know if you have any doubts on this.

Thanks
SQL Srinivas

***New MS SQL SERVER Development Batch from 16th June***

Team,

We are going to start a new batch for MS SQL SERVER development(TSQL &TSQLProgramming) on 16th June at 10-11 PM ISD.

Please find the below Course content

MS SQL SERVER DEVELOPMENT COURSE CONTENT

let your friends/relatives/colleagues, near and  dear know if any one are in need the same.

Thanks
ADMIN

***New MSBI SSAS Batch from 6th June***

Hi Team,

I am glad to inform you that we are going to start a new MSBI-SSAS(including MDX) batch on 6th June 2015.Please find the below details.

Batch Type-weekend(only on Sat and sunday)

Number of hours per day--3(3+3)

Prerequisites--Nothing(basic SQL is fine)

timings--6 to 9PM ISD

Number of hours- SSAS+MDX(15+7)--in max of 25 hours You will become experts in SSAS and MDX queries.

Let me know if you need any more information on this.

Thanks
Admin Team

SQL Server Download Links for 2008 R2 and 2014

Find the Sql Server download links for windows7 and windows8 below.

Download links for Windows 7:

for windows 7-32 bit:

http://www.microsoft.com/en-in/download/details.aspx?id=26729

Open the above link as shown below and click on download

and select the download option as SQLEXPRADV_x86_ENU.exe  and click on 'Next' as shown below.

your download will start automatically.


for windows 7-64 bit:

http://www.microsoft.com/en-in/download/details.aspx?id=26729

Open the above link as shown below and click on download

and select the download option as SQLEXPRADV_x64_ENU.exe  and click on 'Next' as shown below.

your download will start automatically.

Download links for Windows 8/8.X+ versions:

for windows 8-32 bit:

http://www.microsoft.com/en-in/download/details.aspx?id=42299

Open the above link as shown below and click on download

In that click on download and select one item ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe,and click on 'Next' as shown below.

your download will start automatically.

for windows 8-64 bit:

http://www.microsoft.com/en-in/download/details.aspx?id=42299

Open the above link as shown below and click on download

In that click on download and select one item Express 64BIT\SQLEXPR_x64_ENU.exe ,and click on 'Next' as shown below.

your download will start automatically.

Drop me a comment if you are facing any issue with download links.

Thanks
SQL Srinivas