SQL:Group By

All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function


http://stackoverflow.com/questions/5986127/do-all-columns-in-a-select-list-have-to-appear-in-a-group-by-clause


Include non-aggregate column in group by clause (with a slight wrinkle)

http://stackoverflow.com/questions/8849552/include-non-aggregate-column-in-group-by-clause-with-a-slight-wrinkle

Getting summarizing values

http://www.sql-ex.ru/help/select4.php

----------
Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
http://stackoverflow.com/questions/6456727/column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-ag


;WITH cte AS
(
 SELECT *
 FROM GREScores g
 WHERE g.applicationID = 1
)
SELECT 
     g.applicationId,
     -- (another 100 or so columns just like above)
    AScore =(select  max(g2.AScore) FROM cte g2) ,
    APercentile =(select  max(g2.APercentile) FROM cte g2)
FROM cte g

----------
T-SQL GROUP BY: Best way to include other grouped columns

http://stackoverflow.com/questions/626788/t-sql-group-by-best-way-to-include-other-grouped-columns

You can get it to work with something around these lines:

select e.empID, fname, lname, title, dept, projectIDCount
from
(
   select empID, count(projectID) as projectIDCount
   from employees E left join projects P on E.empID = P.projLeader
   group by empID
) idList
inner join employees e on idList.empID = e.empID

This way you avoid the extra group by operations, and you can get any data you want. Also you have a better chance to make good use of indexes on some scenarios (if you are not returning the full info), and can be better combined with paging.
~~~

 select e.empID, e.fname, e.lname, e.title, e.dept, p.projectIDCount
    from employees e 
   inner join ( select projLeader, count(*) as projectIDCount
                  from projects
                 group by projLeader
              ) p on p.projLeader = e.empID

SQL - IF statements

Refer Source Video: SQL - IF statements

************************
The Basic Syntax of IF:

************************
DECLARE @NumFilmsCategory1 INT
DECLARE @NumFilmsCategory2 INT

SET @NumFilmsCategory1 = (SELECT Count(*) FROM tblFilm WHERE FilmGenreID = 1)
SET @NumFilmsCategory2 = (SELECT Count(*) FROM tblFilm WHERE FilmGenreID = 2)

IF @NumFilmsCategory1 >0

BEGIN

PRINT 'message here'

PRINT 'message here'

IF @NumFilmsCategory2 >0

BEGIN

PRINT 'message here'

END

ELSE

BEGIN

PRINT 'message here'

END

END

ELSE

BEGIN

PRINT 'message here'

PRINT 'message here'

END

************************
Stored Procedure:
************************
USE Movies
Go

CREATE PROC spVariableData( @InfoType VARCHAR(9) )
AS
BEGIN

IF @InfoType='ALL'

BEGIN
(SELECT * FROM tblFilm)
RETURN
END

IF @InfoType='AWARD'

BEGIN
(SELECT FilmName FROM tblFilm)
RETURN
END

--if other than above ones

SELECT 'You must choose ALL or AWARD'
END

--
EXEC spVariableData @InfoType='ALL'
--
EXEC spVariableData @InfoType='AWARD'

************************************************************************
How to Use Variables, IF and CASE in Database Interactions with TransactSQL

SET NOCOUNT (Transact-SQL)

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

SQL - WHILE Loops

Refer Source : SQL - WHILE Loops

******************************
The Basic Syntax of WHILE:
******************************
exam.1  
DECLARE @Counter INT
SET @Counter = 1

WHILE @Counter<=10
BEGIN
PRINT @Count
SET @Count = @Count + 1
END

exam.2:Using SELECT Statement in a Loop.
DECLARE @Counter INT
DECLARE @MaxOscars INT
DECLARE @NumFilms INT
SET @Counter = 0
SET @MaxOscars = (SELECT MAX(FilmOscarWins) FROM tblFilm)

WHILE @Counter<=@MaxOscars
BEGIN
SET @NumFilms = SELECT COUNT(*) FROM tblFilm) FilmOscarWins = @Counter)

--IF @NumFilms = 0 BREAK

PRINT CAST(@NumFilms AS VARCHAR(3)) +  + 'fILM HAVE WON' + CAST(@Counter AS VARCHAR(2)+ + 'Oscars' 

SET @Count = @Count + 1
--(if the above is commented then @count is always 0, it will be end less loop)
--make sure the condition to be false , to break it.

END

(Can do this in group by clause, but the records showing no films having 9 oscars and 9 oscars,Refer Video.)

******************************
Using BREAK to Exit a Loop:In the above:IF @NumFilms = 0 BREAK
******************************

This will not print unlike the 11 Oscars.Refer Video.

******************************
How to Stop an Endless Loop:--make sure the condition to be false
******************************
******************************
Using Loops with Cursors:covered in Cursor post.
******************************

SQL: SQL Server join :- Inner join,Left join,Right join and full outer join

Refer the Source Video: SQL Server join :- Inner join,Left join,Right join and full outer join

Full outer join=Result of (Inner join (common records) + Left Outer Join + Right Outer Join)

SQL - User Defined Functions

Refer the Source Video: SQL Server Programming Part 7 - User Defined Functions
(Sql Server 2012)

Sql Server has builtin Functions under Database node.

Programmability-Functions-System Functions.

SELECT FilmName,FilmReleaseDate,DATENAME(DW,FilmReleaseDate) + ' '+ DATENAME(D,FilmReleaseDate) + ' '+ DATENAME(M,FilmReleaseDate)+ ' '+ DATENAME(YY,FilmReleaseDate) FROM tblFilm

2.Instead of writing every time this , 

2 ways to create custom functions, but the template is not that friendly.

Programmability-Functions-Scalar-valued-Function

note:scalar:return one value(string,number or date)

******************

Simple Function:Defining function

******************

USE Movies

GO

CREATE FUNCTION fnLongDate( @FullDate AS DATETIME)

RETURNS VARCHAR(MAX)

AS

BEGIN

--RETURN DATENAME(DW,FilmReleaseDate) + ' '+ DATENAME(D,FilmReleaseDate) + ' '+ DATENAME(M,FilmReleaseDate)+ ' '+ DATENAME(YY,FilmReleaseDate) 

--find and replace 

RETURN DATENAME(DW, @FullDate) + ' '+ DATENAME(D, @FullDate) + ' '+ DATENAME(M, @FullDate)+ ' '+ DATENAME(YY, @FullDate) 

END

--

Execute so it creates.So fresh the windows to show.(Edit menu-InteliSense-Refresh Local Cache)

--

Test it.

SELECT FilmName,FilmReleaseDate,[dbo].[fnLongDate](FilmReleaseDate) from tblFilm

-----------

Modifying a Function:

-----------

adding a suffix:

USE Movies

GO

ALTER FUNCTION fnLongDate( @FullDate AS DATETIME)

RETURNS VARCHAR(MAX)

AS

BEGIN

--RETURN DATENAME(DW,FilmReleaseDate) + ' '+ DATENAME(D,FilmReleaseDate) + ' '+ DATENAME(M,FilmReleaseDate)+ ' '+ DATENAME(YY,FilmReleaseDate) 

--find and replace 

RETURN DATENAME(DW, @FullDate) + ' '+ DATENAME(D, @FullDate) + 

CASE

WHEN DAY(@FullDate IN(1,21) THEN 'st'

WHEN DAY(@FullDate IN(1,22) THEN 'nd'

WHEN DAY(@FullDate IN(3,23) THEN 'rd'

else 'th'

END+' '+ 

DATENAME(M, @FullDate)+ ' '+ DATENAME(YY, @FullDate) 

END

--

execute

--

Test it.

SELECT FilmName,FilmReleaseDate,[dbo].[fnLongDate](FilmReleaseDate) from tblFilm

--

The same function can be used in many places.

---

SELECT ActorName,ActorDOB,dbo.fnLongDate(ActorDOB) FROM tblActor

***************

Complex Expressions:

***************

--refer the video

SELECT DirectorName,LEFT(DirectorName,CHARINDEX(' ',DirectorName)-1) FROM tblDirector

--does not work if there only firstname no lastname

--if CHARINDEX returns 0

***************

Using Variables & IF Statements:

***************

USE Movies

GO

CREATE FUNCTION fnFirstName( @FullName AS VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

  --where the space

DECLARE @SpacePosition AS INT

DECLARE @Answer AS VARCHAR(MAX)

SET @SpacePosition = CHARINDEX(' ',@FullName)

IF @SpacePosition = 0

SET @Answer = @FullName

ELSE

SET @Answer = LEFT(@FullName,@SpacePosition - 1)

RETURN Answer

END

--

Execute and then test it.

--

--In function if more code it takes more time.

SELECT ActorName, dbo.fnFirstName(ActorName) FROM tblActor

SQL:Dynamic SQL

Refer the Source Video:Dynamic SQL

*****************************************************************************************************************************************************************

Dynamic SQL Basics

*****************************************************************************************************************************************************************

***********************
What is Dynamic SQL?
***********************

1.Sql statement when one convert into a text by using ' ' and then executing it,thats Dynamic SQL.

--Using Dynamic SQL

SELECT * FROM tblFilm

EXECUTE ('SELECT * FROM tblFilm')

--can use  EXEC ('SELECT * FROM tblFilm')

******************************
Using System Stored Procedure:
******************************

--Using Dynamic SQL

EXEC sp_executesql N'SELECT * FROM tblFilm'

Note:
1.Uniquecode string , data in nVarchar N'SELECT * FROM tblFilm.
2.sp_executesql - interpret uniquecode strings.


******************************
Which Method Should You Use?
******************************

note:queryplan:store in cache:EXEC ,it executes the query every time.

using stored procedure "sp_executesql" keeps the plan,so its faster.


******************************
Concatenating a Dynamic SQL String:
******************************
--Concatenating a Dynamic SQL String

DECLARE @Tablename NVARCHAR(128)
DECLARE @SQLString NVARCHAR(MAX)

SET @Tablename = N'tblFilm'
SET @SQLString = N'SELECT * FROM'+ @Tablename

EXEC sp_executesql @SQLString

******************************
Concatenating with Numbers:
******************************
--Concatenating numbers

DECLARE @Number INT
DECLARE @NumberString NVARCHAR(4)
DECLARE @SQLString NVARCHAR(MAX)

SET @Number = 10
SET @NumberString = CAST(@Number AS NVARCHAR(4))

--N'SELECT TOP 10 * FROM tblFilm ORDER BY FilmReleaseDate'

--SET N'SELECT TOP + ' + @Number + ' * FROM tblFilm ORDER BY FilmReleaseDate'

--(with the above,it would give error,Conversion failed when converting the nvarchar value SELECT TOP 10' to data type int
--what it does is try to find 'SELECT TOP ' as a number and to add to @Number
--what we need is to convert @Number to a string, using cast,convert function

SET N'SELECT TOP + ' + @NumberString + ' * FROM tblFilm ORDER BY FilmReleaseDate'


EXEC sp_executesql @SQLString


*************************************************************************************************************************************************************

Creating Stored Procedures

*****************************************************************************************************************************************************************

--Creating stored procedures


CREATE PROC spVariableTable ( @TableName NVARCHAR(128) )

AS
BEGIN

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString = N'SELECT * FROM' + @TableName

EXEC sp_executesql @SQLString 
END


--
EXEC spVariableTable 'tbleFilms'

EXEC spVariableTable 'tbleAccts'

--

***********************
Adding Multiple Parameters:
***********************

ALTER PROC spVariableTable ( @TableName NVARCHAR(128),@Number INT )
...Refer Video
DECLARE @NumberString NVARCHAR(4)
SET @NumberString =CAST(@Number AS NVARCHAR(4))
SET @SQLString = N'SELECT * TOP' + @NumberString  + '* FROM' + @TableName
...Refer Video


EXEC spVariableTable 'tbleAccts',15


***********************
Using the IN Operator:
***********************

--Dynamic SQL and the IN Operator

cCREATE PROC spFilmYears( @YearList NVARCHAR(MAX)
AS
BEGIN

DECLARE @SQLString NVARCHAR(MAX)

  SET @SQLString =

N'SELECT * FROM tblFilm WHERE YEAR(FilmReleaseDate) IN(' + @YearList + N') ORDER BY FilmReleaseDate'

 EXEC sp_executesql @SQLString
END


---
EXEC spFilmYears '2000,2001,2002'
---

***********************
Parameters of sp_executesql:
***********************
--Parameters of sp_executesql

EXEC sp_executesql N'SELECT FileName,FilmreleaseDate From tblFilm WHERE  FilmRunTimeMinute > @Length'
AND FilmReleaseDate > @StartDate,
N'Length INT',
@StartDate,
@Length = 180,
@StartDate ='2001-01-01'

***********************
The Danger of SQL Injection:
***********************

--SQL Injection

EXEC spVariableTable 'tblFilm'


EXEC spVariableTable 'tblFilm; DROP TABLE tblTest'

( it will drop table)

The Curse and Blessings of SQl Injection attack.

Search:

SQL:Cursors

Refer the Source Video: SQL - Cursors

Cursor works on single record at a time.Performance hit:It works slowly than the SET based operation.
*********************************************************************************
                                  Simple Cursor statements
*********************************************************************************
********
Declaring a Cursor
********
--Declaring a cursor

DECLARE FilmCursor CURSOR

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
********
Opening and Closing Cursors
********
OPEN FilmCursor

--Do something useful here

FETCH NEXT FROM FilmCursor

WHILE @@FETCH_STATUS = 0

FETCH NEXT FROM FilmCursor

CLOSE FilmCursor

DEALLOCATE FilmCursor

*********************************************************************************
                                                Calling a Stored Procedure
*********************************************************************************
******** Executing a Stored Procedure with a Cursor: ********

CREATE PROC spListCharacters ( @FilmID INT ,@FilmName VARCHAR(MAX) ,@FilmDate DATETIME) AS BEGIN PRINT @Name + 'released on' + CONVERT(CHAR(10),@Date,103) PRINT '=================================================' PRINT 'List of Characters' SELECT CastCharacterName FROM CastFilmID=@FilmID END ------- Execute it, so it will create it.
---Below Cursor definition
******** Reading Values into Variables: ******** DECLARE @ID INT DECLARE @Name VARCHAR(MAX) DECLARE @Date DATETIME

--Declaring a cursor
DECLARE FilmCursor CURSOR

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

OPEN FilmCursor

--Do something useful here

FETCH NEXT FROM FilmCursor  INTO @ID,@Name,@Date

WHILE @@FETCH_STATUS = 0

    BEGIN
EXEC spListCharacters @ID,@Name,@Date

FETCH NEXT FROM FilmCursor  INTO @ID,@Name,@Date


END

CLOSE FilmCursor

DEALLOCATE FilmCursor

*********************************************************************************

                                                  Types of Cursors

                                 SQL Server Different Types of Cursors

                   Understanding SQL Server Fast_Forward Server Cursors


*********************************************************************************
********
OTHER FETCH OPTIONS for Cursors: DECLARE FilmCursor CURSOR SCROLL/FORWARD_ONLY
*******

--Do something useful here


FETCH FIRST/LAST FROM FilmCursor
~~~~~~~~
FETCH ABSOLUTE 10 FROM FilmCursor
--ABSOLUTE - 10 (for cursor to move backward).

WHILE @@FETCH_STATUS = 0

FETCH RELATIVE 10 FROM FilmCursor
--RELATIVE - 10
********
Setting the Scope of a Cursor: DECLARE FilmCursor CURSOR LOCAL/GLOBAL

********
Note:Right Click->Database->Properties->Default Cursor(LOCAL or GLOBAL)
********
Record Set Types for Cursors:
DECLARE FilmCursor CURSOR STATIC/DYNAMIC/CURSOR_FAST_FORWARD

********

Note: CURSOR_FAST_FORWARD( readonly),STATIC( copy of data in tempdb),KetSET(Key values in tempdb),DYNAMIC( change key or nonkey values)
********
Record Locking Options for Cursors:
DECLARE FilmCursor CURSOR READ_ONLY/SCROLL_LOCKS/OPTIMISTIC

Note:OPTIMISTIC(updates when you change ,if inbetween someone changes it keeps its update)

********

********
Combining Cursor Options:
DECLARE FilmCursor CURSOR GLOBAL FORWARD_ONLY STATIC READ_ONLY

********
********
Declaring Cursors for Updating Records:
--Declaring a cursor

DECLARE FilmCursor CURSOR 
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm


FOR UPDATE OF FilmCumulativeOscars

Note:By Default the general declaration of cursor allows update.One can specify Update and specific fields.

********
Running Total:
********
DECLARE @filmOscars  INT
DECLARE @TotalOscars INT

SET @TotalOscars = 0
********
Declaring a Cursor
********

--Declaring a cursor
...Refer Video
FETCH NEXT FROM FilmCursor INTO @FilmOscars
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalOscars += @FilmOscars
PRINT @TotalOscars --comment it out

UPDATE tblFilms  SET FilmCumulativeOscars = @TotalOscars  WHERE CURRENT OF  FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
END
.....Refer Video
*********************************************************************************