Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Friday, October 19, 2012

WHAT IS USE OF COALESCE KEYWORD

step 1:

CREATE TABLE COLLEGE(SID NVARCHAR(10),SNAME NVARCHAR(20),PLACE NVARCHAR(20))

 

Step 2:

 

INSERT INTO COLLEGE VALUES(1,'SURESH',NULL),(NULL,'RAMESH','PUDUKKOTTAI'),(3,'RAMU','COIMBATORE')

-- ABOVE QUERY WILL RUN ONLY SQL 2008,2012

 

STEP 3:

 

SELECT COALESCE(SID,SNAME,PLACE) AS 'STUDNAME' FROM COLLEGE

 

STEP 4:

 

Post your Ans  http://jssql.blogspot.in/

 

 
 

Thursday, October 18, 2012

What is output below query?


Question 1:

Employee table has 2 column eid,ename

CREATE TABLE EMP(EID NUMERIC(10),ENAME NVARCHAR(20))

INSERT INTO EMP VALUES(1,'devi')

INSERT INTO EMP VALUES(2,'TIRU')

INSERT INTO EMP VALUES(3,'Ram')

INSERT INTO EMP VALUES(4,'Saran')

What is output below query?
 

SELECT EID ENAME FROM EMP
 
 
OutPut:
 
???   Post your Answer http://jssql.blogspot.in/

Saturday, August 25, 2012

Sql Server Trigger for Beginner


                                                    Trigger

A trigger is a SQL procedure  that initiates an action  .when an event (INSERT,UPDATE,DELETE) occures
Triggers can be viewed as similar to stored procedure in that both consist of procedural logic that is stored at the database level.

STEP 1:
CREATE TABLE  Student(SID int IDENTITY, SNAME varchar(10))
create a trigger that displays the count student table when a row is inserted into the table to which it is attached.
STEP 2:
CREATE TRIGGER tr_student_insert
ON STUDENT
FOR INSERT
AS
SELECT COUNT(*) AS 'NUMBER OF RECORD' FROM  Student

STEP 3:

INSERT INTO Student VALUES('SUTHAHAR')

RESULT:


Use the inserted and deleted Tables

STEP 1:
CREATE TABLE  STUMARK(SID int IDENTITY, MARK NUMERIC(10))
STEP 2:
CREATE TRIGGER tr_STUMARK_insert
ON STUMARK
FOR INSERT
AS
IF((SELECT MARK FROM  inserted)  < 40)
BEGIN

PRINT 'FAIL'

END
ELSE
BEGIN

PRINT 'PASS'
END


STEP 3:

INSERT INTO STUMARK VALUES(78)

OUTPUT:


AFTER UPDATE

CREATE TRIGGER tr_STUDENT_UPDATE
ON STUDENT
AFTER UPDATE
AS

AFTER DELETE
CREATE TRIGGER tr_STUDENT_DELETE
ON DELETE
AFTER UPDATE
AS


INSTEAD  OF TRIGGERS

CREATE TRIGGER tr_STUDENT_INSERT_InsteadOf
ON STUDENT
INSTEAD OF INSERT
AS
PRINT 'Updateable Views are Messy'
go


Friday, August 17, 2012

CUBE,COMPUTE,COMPUTE BY and ROLLUP OPERATOR IN SQL SERVER




The CUBE,COMPUTE,COMPUTE BY and ROLLUP operators are useful in generating reports that contain subtotals and totals. 

There are extensions of the GROUP BY clause.

The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY; however, ROLLUP has these advantages:

  • ROLLUP returns a single result set; COMPUTE BY returns multiple result sets that increase the complexity of application code.

  • ROLLUP can be used in a server cursor; COMPUTE BY cannot.

  • The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

STEP 1:

create table JSSTUD(COL nvarchar(20),PER numeric(10))

STEP 2:

INSERT INTO JSSTUD VALUES('JJ COLLEGE',100) 
INSERT INTO JSSTUD VALUES('JJ COLLEGE',200) 
INSERT INTO JSSTUD VALUES('JJ COLLEGE',300) 
INSERT INTO JSSTUD VALUES('PSG',150) 
INSERT INTO JSSTUD VALUES('PSG',50) 
INSERT INTO JSSTUD VALUES('PSG',1050) 
INSERT INTO JSSTUD VALUES('GRG',2000) 

STEP 3:

ROLLUP

SELECT  COL,SUM(PER) FROM JSSTUD GROUP BY COL
WITH ROLLUP



OR

COMPUTE  bY

SELECT COL,PER FROM JSSTUD ORDER BY COL
COMPUTE SUM(PER) BY COL



OR

SELECT COL,PER FROM JSSTUD ORDER BY COL
COMPUTE SUM(PER) 



CUBE

select COL, sum(per) 
from JSSTUD
group by COL with CUBE

Tuesday, February 28, 2012

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Sql Server Error :


Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager. The specified module could not be found. [0x8007007e]

Inline image 1

Solution :

 
Start ==> Run => Type below command  

 mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Thanks

Friday, February 10, 2012