Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

C++ Graphics – 01 – Line program by Using Mid-Point Algorithm

December 1, 2009 Leave a comment

Create a Line program by Using Mid-Point Algorithm.

#include <stdio.h>
#include <conio.h>
#include <graphics.h>
#include <math.h>
#include <dos.h>

void lineMidPoint(int x1, int y1, int x2, int y2){
	int dx = x2 - x1;
	int dy = y2 - y1;
	int d = 2 * dy - dx;
	int incrE = 2 * dy;
	int incrNE = 2 * (dy - dx);
	int x = x1;
	int y = y1;
	putpixel(x, y, WHITE);
	while(x < x2){
		if(d<=0){
			d += incrE;
			x++;
			}
		else{
			d += incrNE;
			x++;
			y++;
			}
		putpixel(x, y, WHITE);
		}
	}

int main(){
	int gd=DETECT, gm;
	initgraph(&gd, &gm, "\\tc");
	lineMidPoint(100, 100, 600, 300);
	getch();
	return 0;
	}
Categories: Cpp Graphics Tags: , ,

DB Basics – Difference between Primary Key & Unique Key

November 3, 2009 2 comments

Primary Key & Unique Key are nothing but Constraints that can be set at column level of a table, to maintain Uniqueness in the table and thus not allowing duplicate entries.
 

–> Primary Key (PK) Constraint:

1. A PRIMARY KEY uniquely identifies every row in a database table.

2. The PK constraint on a Table’s COLUMN enforces:
– – UNIQUE values and
– – NOT NULL values

3. Every table can have only one PK defined on a particular column (or more than 1 columns).

4. You can create PK on a Table with:
– – CREATE TABLE statement (in two ways)
– – or later with ALTER TABLE … ADD CONSTRAINT statement.

5. You can create a PK with an IDENTITY column.

6. PK can be Composite Key, containing more than one column.

PRIMARY Key and INDEXes:

7. In SQL Server on creating a Primary Key on a table, a Clustered Index is created with PK column as the Clustering Key.

8. You can also create a Primary Key with a Non-Clustered Index, check this blog post.
 

Check the video on Primary Keys:

PK Constraint
 

–> Unique Key (PK) Constraint:

1. A UNIQUE KEY just like PK uniquely identifies every row in a database table.

2. The UK constraint on a Table’s COLUMN enforces:
– – UNIQUE values (no duplicate values)
– – Allow single NULL value (But PK do not allow NULL values)

3. A table can have one or more than one UK defined on many columns (or more than 1 columns). But only 1 PK.

4. You can create UK on a Table with:
– – CREATE TABLE statement (in two ways)
– – or later with ALTER TABLE … ADD CONSTRAINT statement.

6. UK can be Composite Key, containing more than one column.

7. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

Unique Key and INDEXes:

8. In SQL Server on creating a Unique Key on a table, a Non-Clustered Index is created with the column(s) as the Index Key.
 

Check the video on Unique Keys:

UK Constraint
 

To know about more on Constraints and their types check this blog post.

Categories: DB Concepts, Differences Tags: , , , ,

SQL Basics – Persisted and Non-Persisted Computed columns in SQL Server

October 2, 2009 Leave a comment

In SQL Server you have an option to create Calculated Columns in a table other than normal columns. By saying Normal columns I meant that their values are stored physically with the table. But with Calculated Columns their values are not stored physically, instead they are re-calculated every time whenever they are referenced.

A Computed Column is nothing but an expression, which can be a:

1. Non Computed column

2. Constant

3. Function

4. or any combination of above of these by using operators.

These type of Computed Columns are Non-Persisted in nature.
 

Persisted Computed Columns: You can also make Computed columns values stored physically by using the PERSISTED keyword. This will calculate the column values initially while inserting a row, or later if there is any change made to the column that is part of its expression.
 

Finally a Computed Column:

– is computed from an expression that can use other columns in the same table.

– can be a noncomputed column name, constant, function with one or more operators.

– cannot be a subquery.

– cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.

– used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.

– can be used in SELECT list, WHERE clause, ORDER BY clause, etc.

– cannot be the target of an INSERT or UPDATE statement.
 

Check video on how to create Calculated Columns in a Table:

Computed Columns
 

–> SQL Script used in the above demo:

CREATE TABLE [dbo].[Employee] (
	[EmployeeID]	int,
	[EmployeeName]	nvarchar(100),
	[Gender]		nchar(1),
	[DOB]			datetime,
	[DOJ]			datetime,
	[DeptID]		int
)
GO

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('JHON K', 'M', '1970-03-03', NULL, NULL)

INSERT INTO [dbo].[Employee] (EmployeeName, DOB)
VALUES ('Brock H', '1980-02-02')
GO

SELECT * FROM [dbo].[Employee]
GO

ALTER TABLE [dbo].[Employee]
ADD AGE AS (DATEDIFF(YY, DOB, GETDATE()))
GO

ALTER TABLE [dbo].[Employee]
ADD XYZ AS 'nothing'
GO

SELECT * FROM [dbo].[Employee]
GO


CREATE TABLE dbo.Products (
    ProductID	int IDENTITY (1,1),
	Qty			smallint,
	Price		money,
	TotalValue AS (Qty * Price)
)

INSERT INTO dbo.Products (Qty, Price)
VALUES (5, 200)

INSERT INTO dbo.Products (Qty, Price)
VALUES (3, 150)

select * from dbo.Products
GO


ALTER TABLE dbo.Products
DROP COLUMN TotalValue

ALTER TABLE dbo.Products
ADD TotalValue AS (Qty * Price) PERSISTED
GO

-- Final Cleanup
DROP TABLE [dbo].[Employee]
DROP TABLE dbo.Products
GO
Categories: DB Concepts, SQL Basics Tags: ,

Create Parameterized Excel Refreshable Reports

September 24, 2009 4 comments

In my [previous post] I provided steps to created a simple Excel Pivot Report. This Report queries your SQL Server Table and pulls data from it and renders into the Excel Sheet.

Many of the time we don’t require whole data, and want to add conditions to restrict the data, like what if we just need to pull data for Q2 (2nd Quarter) of year 2009. This would require a Date Range to be applied to the SQL Query.

How about Start Date & End Date that will be filled by the user on Excel Sheet on particular cells? And those values will be applied to the SQL Query WHERE clause and will pull just the data lying between those Date Ranges?

>> Here I’ve defined how we can pass Parameters from the Excel-Sheet to the SQL query to filter data.

1. Please refer to my previous post, stop at step 13.

2. Now, open Connection Properties and go to Definition Tab and add the following line at the end of your query:

where [DATE_FIELD] between ? and ?

Create Parameterized Excel Refreshable Reports

Now, what does “?” means.

By applying ? (question-mark) in our query in WHERE clause Excel identifies them as 2 parameters and asks you from where to pick the data.

Now point both the parameters to the desired cell in the excel-sheet as shown below:

Create Parameterized Excel Refreshable Reports Create Parameterized Excel Refreshable Reports

And finally you are done… when you will close the “Connection Properties” window the pivot report will get refreshed itself and pull the data as per the date you have applied.

Categories: Excel Tags: , ,

Create an Excel Refreshable Report from database & Pivot Report

September 24, 2009 2 comments

 
Steps to create a Pivot Report
 

1. Open Excel Workbook, assuming that we are on sheet1.

2. Click on Data [Tab] — From Other Sources — From Microsoft Query

3. A new window will open “Choose Data Source”, and click OK button.

4. A new window will open “Create New Data Source”.
a. Provide the Data Source name, any name.
b. Select the Database Driver Name: SQL Server
c. Click Connect button.

5. A new window gets open “SQL Server Login”. Provide the DB credentials as shown above. Click OK button.

6. It will connect to the data-source.

7. Now Click on OK button of the “Create New Data Source” window as shown at step-4.
Leave the 4th step for the default table.

8. You will be prompted back to “Choose Data Source” window with a new Data Source entry you just created.

Select it and click on OK button.

9. A new window opens “Query Wizard – Choose Columns”.

Select the number of columns you want to pull out and click on Next Button.

10. Click Next button repeatedly for the next 2 windows. In the final window “Query Wizard – Finish”…

… select “View data or edit query in Microsoft Query” and click on Finish button.

11. You will be redirected to a new tool i.e. “Microsoft Query”.

Here you can update your query, add new criteria and more filters.

When you are done with the query, go to the File menu and select “Return Data to Microsoft Office Excel”.

12. You will be redirected back to Excel with this new small window “Import Data”.

Choose your type of report & worksheet and then click on OK button.

13. You will get the data pulled out from the database for the specific query you applied, shown below:

Click on the Refresh button to get the latest data.

14. To create a pivot report from this report on sheet1.

Click on sheet2 and click on Insert tab and select PivotTable from the drop-down menu as shown in window below.

15. A new window will appear “Create Pivot Table”.

Select the source and destination table range as shown above. Click OK button.

16. PivotTable wizard is on the excel sheet and on the right side are the PivotTable field lists.

You can choose and drop the selected fields on the PivotTable area shown above.

Also you can apply filters and formulas to the PivotTable report.

17. The final PivotTable report is here.

I’ve chosen 4 columns from the table (as shown inside the Row Labels box) and the 5th column is the derived column, count of Customers/Records (as shown inside the Value box).

Categories: Excel Tags: , ,