Tables

CREATE TABLE Classification (
      Classif_ID         integer  NOT NULL PRIMARY KEY,
      Classification    varchar(25))


 INSERT into Classification VALUES( 1,'Pop')
 INSERT into Classification VALUES( 2,'Country')
 INSERT into Classification VALUES( 3,'Alternative')
 INSERT into Classification VALUES( 4,'Metal')




 CREATE TABLE CD (
      CD_ID              integer  NOT NULL,
     CD_Title           varchar(40),
      Composer_ID        integer  NOT NULL,
      Classif_ID         integer  NOT NULL,
      SalesPrice        money,
      AverageCost       money)

 INSERT into CD VALUES(2000,'John',100,1,16.99,6.99)
 INSERT into CD VALUES(2001,'Chica 16',107,1,14.99,5.99)
 INSERT into CD VALUES(2002,'Chica 17',107,1,14.99,5.99)
 INSERT into CD VALUES(2003,'Chica 18',107,1,14.99,5.99)
 INSERT into CD VALUES(2004,'Greatest Hits',107,1,16.99,7.99)
 INSERT into CD VALUES(2005,'Midnight',101,3,14.99,5.99)
 INSERT into CD VALUES(2006,'Mode',115,3,14.99,5.99)
 INSERT into CD VALUES(2007,'Ultra',115,3,15.99,5.99)
 INSERT into CD VALUES(2008,'Mindcrime',102,4,14.99,5.99)
 INSERT into CD VALUES(2009,'Empire',102,4,14.99,5.99)
 INSERT into CD VALUES(2010,'Land',102,4,12.99,4.99)
 INSERT into CD VALUES(2011,'Night',103,4,11.99,3.99)
 INSERT into CD VALUES(2012,'Pyromania',103,4,14.99,5.99)
INSERT into CD VALUES(2013,'Hysteria',103,4,14.99,5.99)
 INSERT into CD VALUES(2014,'Hits',103,4,13.99,4.99)
 INSERT into CD VALUES(2015,'Hits 2',104,2,15.99,6.99)
 INSERT into CD VALUES(2016,'Greatest',105,2,14.99,5.99)
 INSERT into CD VALUES(2017,'Hits 3',106,1,13.99,5.99)
 INSERT into CD VALUES(2018,'Deep',108,1,12.99,2.99)
INSERT into CD VALUES(2019,'Turning',109,1,14.99,5.99)
 INSERT into CD VALUES(2020,'TheHits',109,1,16.99,7.99)
 INSERT into CD VALUES(2021,'Cars',110,1,9.99,3.99)
 INSERT into CD VALUES(2022,'Anthology',110,1,25.99,11.99)
 INSERT into CD VALUES(2023,'City',110,1,14.99,5.99)
 INSERT into CD VALUES(2024,'Rick',111,1,11.99,2.99)
 INSERT into CD VALUES(2025,'Live',112,1,19.99,8.99)
 INSERT into CD VALUES(2026,'Pat',113,1,16.99,6.99)
 INSERT into CD VALUES(2027,'Big',114,1,14.99,5.99)
 INSERT into CD VALUES(2028,'Hurting',114,1,11.99,3.99)
 INSERT into CD VALUES(2029,'Vol 1',116,1,9.99,2.99)
 INSERT into CD VALUES(2030,'Vol 2',116,1,9.99,2.99)

 select * from Classification

 select * from CD

 select * into classificationdummy from Classification

   INSERT into classification VALUES( 7,'cad')
 INSERT into classification VALUES( 8,'auto') 

 --intersect

 select * from Classification 
  intersect
 select * from  classificationdummy 

 --union
select * from  classificationdummy 
union
 select * from Classification 
  
 --Except
  select * from Classification 
    EXCEPT 
  select * from  classificationdummy
  
 SELECT CONVERT(varchar(30),SalesPrice) from CD
 UNION
 Select Classification from Classification
--
create table Billings (
     BankerID           INTEGER,
     BillingNumber      INTEGER,
     BillingDate        datetime,
    BillingTotal       INTEGER,
     TermsID            INTEGER,
     BillingDueDate     datetime ,
     PaymentTotal       INTEGER,
     CreditTotal        INTEGER

 );


 INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);



 INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321);



 INSERT INTO Billings VALUES (3, 3, '2003-05-02', 165, 1,'2005-04-12',123,321);



 INSERT INTO Billings VALUES (4, 4, '1999-03-12', 165, 1,'2005-04-18',123,321);



 INSERT INTO Billings VALUES (5, 5, '2000-04-23', 165, 1,'2005-04-17',123,321);



 INSERT INTO Billings VALUES (6, 6, '2001-06-14', 165, 1,'2005-04-18',123,321);



 INSERT INTO Billings VALUES (7, 7, '2002-07-15', 165, 1,'2005-04-19',123,321);



 INSERT INTO Billings VALUES (8, 8, '2003-08-16', 165, 1,'2005-04-20',123,321);



 INSERT INTO Billings VALUES (9, 9, '2004-09-17', 165, 1,'2005-04-21',123,321);



 INSERT INTO Billings VALUES (0, 0, '2005-10-18', 165, 1,'2005-04-22',123,321);





 create table Bankers(
    BankerID             Integer,
    BankerName           VARCHAR(20),
    BankerContactLName   VARCHAR(20),
    BankerContactFName   VARCHAR(20),
    BankerCity           VARCHAR(20),
    BankerState          VARCHAR(20),
    BankerZipCode        VARCHAR(20),
    BankerPhone          VARCHAR(20)
 )


 insert into Bankers values (1, 'ABC Inc.','Joe','Smith','Vancouver','BC','11111','111-111-1111');



 insert into Bankers values (2, 'DEF Inc.','Red','Rice', 'New York', 'DE','22222','222-222-2222');



 insert into Bankers values (3, 'HJI Inc.','Kit','Cat',  'Paris',    'CA','33333','333-333-3333');



 insert into Bankers values (4, 'QWE Inc.','Git','Black','Regina',   'ER','44444','444-444-4444');



 insert into Bankers values (5, 'RTY Inc.','Wil','Lee',  'Toronto',  'YU','55555','555-555-5555');



 insert into Bankers values (6, 'YUI Inc.','Ted','Larry','Calgary',  'TY','66666','666-666-6666');



 insert into Bankers values (7, 'OIP Inc.','Yam','Act',  'San Franc','FG','77777','777-777-7777');



 insert into Bankers values (8, 'SAD Inc.','Hit','Eat',  'Orland',   'PO','88888','888-888-8888');



 insert into Bankers values (9, 'DFG Inc.','Sad','Lee',  'Wisler',   'PL','99999','999-999-9999');



 insert into Bankers values (0, 'GHJ Inc.','Bit','Lee',  'Ticker',   'MN','00000','000-000-0000');


select * from Billings

 SELECT 'Active' AS Source, BillingNumber, BillingDate, BillingTotal
     FROM Billings
     WHERE BillingTotal - PaymentTotal - CreditTotal < 0
 UNION
     SELECT 'Paid' AS Source, BillingNumber, BillingDate, BillingTotal
     FROM Billings
     WHERE BillingTotal - PaymentTotal - CreditTotal <= 0
 ORDER BY BillingTotal DESC

 select * from Billings where BillingDueDate>'04/20/2005'
Except
 select * from Billings where BillingDueDate<'04/20/2005'

 select * from Billings
 case
 when (select Billings.BillingDueDate from Billings where BillingDueDate<'02/02/2003') then 'it is below 2003'
 end

Case statement in sql server?

SQL SERVER – CASE Statement/Expression Examples and Explanation

April 14, 2007 by pinaldave

CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

Two basic formulations for CASE expression 
1) Simple CASE expressions 
A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

Syntax:
CASE expressionWHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]END
Example:
DECLARE @TestVal INT
SET @TestVal = 3SELECTCASE @TestValWHEN 1 THEN 'First'WHEN 2 THEN 'Second'WHEN 3 THEN 'Third'ELSE 'Other'END

2) Searched CASE expressions
A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:

Syntax:
CASEWHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]END
Example:
DECLARE @TestVal INT
SET @TestVal = 5SELECTCASEWHEN @TestVal <=3 THEN 'Top 3'ELSE 'Other'END

Reference : Pinal Dave (http://blog.SQLAuthority.com)


USEFUL LINK FOR CASE
http://www.mssqltips.com/sqlservertip/1455/using-the-case-expression-instead-of-dynamic-sql-in-sql-server/

Set operators in sql

Set operators
Union
Intersect
Except

Tables:        
    EMP1
  

Classif_ID Classification

1 Pop

2 Country

3 Alternative

4 Metal

7 cad

8 auto

EMP2

Classif_ID Classification

5 cad

6 auto

1 Pop

2 Country

3 Alternative

4 Metal

UNION :
   

select * from  Emp1

union
 select * from Emp2

it will give the results from both the tables with matched and unmatched records
Result

Classif_ID Classification

1 Pop

2 Country

3 Alternative

4 Metal

5 cad

6 auto

7 cad

8 auto

INTERSECT:

 select * from  Emp1
    EXCEPT 
  select * from Emp2
Result:

Classif_ID Classification
1                Pop
2              Country
3              Alternative
4               Metal


EXCEPT

 select * from Emp1

    EXCEPT 

  select * from  Emp2

Result

Classif_ID Classification
7                 cad
8                 auto

Difference Between Where and Having clause in sql server 2008?

DIFFERENCE:

in 'where' clause every records filtered based on where
in 'having' is with aggregate records (group by functions )

http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

How to compare two tables in sql server?

As a database developer sometime or the other you might have come across this requirement and it can be achieved using many ways including built-in features like Checksum and TableDiff utility. However, I would like to share an alternate way to achieve the objective, which might be useful in some situations.

Usually the intention of comparison is to find out the missing rows from either of the tables or both. This where the operator "EXCEPT" comes very handy. Let us see it with an example.

 --Create two Tables--

CREATE TABLE TableA(ID Int, Name Varchar(256))

GO

CREATE TABLE TableB(ID Int, Name Varchar(256))

GO

INSERT INTO TableA VALUES(1,'A'),(2,'B'),(3,'D')

INSERT INTO TableB VALUES(1,'A'),(2,NULL),(3,'C')

--Get rows from TableA that are not found in TableB--

SELECT * FROM TableA

EXCEPT

SELECT * FROM TableB

--Get rows from TableB that are not found in TableA--

SELECT * FROM TableB

EXCEPT

SELECT * FROM TableA

I have used the above concept for comparing data between heavy tables and the performance is satisfactory. I could get the result in 3 Min 55 sec when I compared a table having 1.18 million rows. The comparison can't be performed for some of the data types of the columns like XML, Text and Image etc. So, I have written a generic stored procedure that does the comparison between two tables by excluding the columns that can't be compared. It also allows to compare tables across databases and schemas. Validations and error handling are not added as the intention is to just present the idea.

Limitation of view in sql server?

SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5

September 21, 2010 by pinaldave

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

One of the most prominent limitations of the View it is that it does not support COUNT(*); however, it can support COUNT_BIG(*) operator. In the following case, you see that if View has COUNT (*) in it already, it cannot have a clustered index on it. On the other hand, a similar index would be created if we change the COUNT (*) to COUNT_BIG (*).For an easier understanding of this topic, let us see the example here.

USE tempdb
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))DROP VIEW [dbo].[SampleView]
GOIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))DROP TABLE [dbo].[mySampleTable]
GO-- Create SampleTableCREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))INSERT INTO mySampleTable (ID1,ID2,SomeData)SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),ROW_NUMBER() OVER (ORDER BY o2.name),o2.nameFROM sys.all_objects o1CROSS JOIN sys.all_objects o2
GO-- Create ViewCREATE VIEW SampleViewWITH SCHEMABINDINGAS
SELECT COUNT(*) TableCount, ID2FROM dbo.mySampleTableGROUP BY ID2
GO-- Create Index on ViewCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO/* Above statement will thrown an error
Msg 10136, Level 16, State 1, Line 1
Cannot create index on view "tempdb.dbo.SampleView" because it uses the aggregate COUNT. Use COUNT_BIG instead.
*/
-- Aleter View to replace COUNT with BIG_COUNTALTER VIEW SampleViewWITH SCHEMABINDINGAS
SELECT COUNT_BIG(*) TableCount, ID2FROM dbo.mySampleTableGROUP BY ID2
GO-- Now let us create Index again - this time successfullyCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO

Here is a screenshot of an error that occurred when Views used COUNT(*) and there was an attempt to create an Index on it.

If you are wondering about the reason behind allowing COUNT_BIG and COUNT, here is a quick explanation for this. Itzik Ben-Gan explained me the reason for this situation. He has also clarified the reasons in his book series, ‘Inside T-SQL‘. Here is a concise summary of the explanation. This response from Itzik is produced unaltered and with his permissions:

If the query is a grouped query, SQL Server needs to keep track of the count in each group in order to known whether a group needs to be modified or removed altogether upon DELETE/UPDATE of rows against the underlying tables. As for why the COUNT_BIG and not just COUNT, since SQL Server materializes the counts along with the rest of the view’s data, I guess this has to do with support for groups with more rows than the maximum four-byte integer.

BTW, unrelated to views but along similar lines, see what happens if you add to a clustered table more than the maximum four-byte integer number of rows with the same non-unique clustered index key. The uniqueifiers SQL Server uses internally to distinguish between rows with the same clustered index key is a four-byte integer. Once it overflows, you get error 666 and are not allowed to add more rows with the same clustered index key.

Now, with uniqueifiers for clustering keys I understand the choice to go for four bytes since there are great space savings and therefore read performance benefits as a result, and we are talking about an extreme case for this to happen . But with grouped queries, usually the number of groups is not too large, but groups themselves can be large. Imagine a situation where you try to add more rows to a table that has an indexed view and SQL Server rejects the insert because of a four-byte int overflow in the target group count.

I hope it is clear now. If you want to learn more about this, you can continue reading his book Inside T-SQL.

Let me know what you think of these limitations, as well as your opinions about the example.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Aggregate functions in sql server?

AGGREGATE FUNCTIONS IN SERVER

Aggregations will perform calculations on a set of values and returns a

 single value.

These are aggregate fns in sql server

AVG, 

MIN, 

CHECKSUM_AGG,

 SUM,

 COUNT,

 STDEV, 

COUNT_BIG,

 STDEVP,

 GROUPING, 

VAR,

 MAX,

 VARP.

Examples

AVG:  select AVG(Sales) from Sales as averagesalary

Max: select MAX(Sales) from Sales as maximumsalary

Min:select Min(Sales) from Sales

COUNT:select COUNT(*) from Sales

stdev

CHECKSUM_AGG

select CHECKSUM_AGG(CAST(Sales as int)) from Sales

Explanation:

CHECKSUM_AGG function is used to get checksum of values in a group. It ignores null values in computation.

CHECKSUM_AGG can be used along with BINARY_CHECKSUM to detect changes in a table.

The order of the rows in the table does not affect the result of CHECKSUM_AGG. In addition, CHECKSUM_AGG functions may be used with the DISTINCT keyword and the GROUP BY clause.

Syntax of CHECKSUM function :

CHECKSUM ( * | expression [ ,...n ] )

* means that the computation is over all the columns of the table.

Expression is any valid sql expression of any type.

 Example of CHECKSUM function :

Example 1 : Use of CHECKSUM_AGG function in SELECT clause to get checksum in group

SELECT   CHECKSUM_AGG(CONVERT(INT,UnitsInStock))

FROM   Products

Output

 50

Now update some of the rows and again run the query.

UPDATE Products

SET         UnitsInStock = 15

WHERE  UnitsInStock < 10

  

SELECT  CHECKSUM_AGG(CONVERT(INT,UnitsInStock))

FROM     Products

Output

61

Above example displays checksum of unit price field in the products table.

 Example 2 : Using CHECKSUM_AGG function with BINARY_CHECKSUM to detect changes in a table

SELECT  CHECKSUM_AGG(BINARY_CHECKSUM(UnitsInStock))

FROM     Products

Output

7913472

Above example displays checksum to detect changes in the unit in stock field of product table.

COUNTBIG;

COUNT() aggregate function is used in SQL to calculate the total count of items in a group.
COUNT_BIG() does the same calculation as COUNT() but difference is what they return.
The difference between the two is COUNT_BIG() returns a BigInt data type value And COUNT() return an Int data type.

Count calculates all null values as well in the group count calculation. All the other aggregate functions may exclude the null values in the calculations.

Examples,

Code:

select COUNT(Distinct city) from tablepeople

If you have millions of rows in a table, COUNT() may throw an error message so you can use COUNT_BIG function instead to avoid this data type error.

Code:

select COUNT_BIG(id) from tablepeople

VAR: