IronMan 70.3 Waco Texas – Recap of Half IronMan triathlon Waco 2021

Primary types of Glaucoma - Overview and Symptoms of Glaucoma

A lot of people have heard of glaucoma but really don’t know what it is, how you get it and what some of the symptoms are.  Most people think it is an “Old Person” disease that has something to do with the eyes.  Though it is true that Glaucoma strikes older Americans, it can also strike children and carries symptoms that are far reaching from your eyes.

To simplify it, Glaucoma is an eye disease that damages your eyes optic nerve.  An optic nerve is located in the back of the eye.  It is also known as the cranial nerve.  The optical nerve transmits visual information from the retina to the brain.  

There are different symptoms that people get when they get glaucoma.  Symptoms can vary depending on what type of Glaucoma you have.  There are generally two primary types of Glaucoma:   Open Angle and Angle Closure.   Open Angle sneaks up without a lot of symptoms.   For Open Angle glaucoma it is the loss of side or peripheral vision.   Since the symptoms for this one are harder to recognize it is important to get checked out every year.  Just visit Orange county glaucoma specialist today to get a checkup. 

Open Angle Glaucoma is when the eye draining canals become clogged over a long period of time.   As a result of this clog, a lot of pressure builds up to the optic nerve.  It is very similar to when you have a clogged pipe in your house.   Ultimately there is a lot of pressure that is pushing against the clog, but the drainage doesn’t happen, causing a lot of pressure.   

On the other hand, the symptoms of Angle Closure are very apparent.   Some of the symptoms include the following, eye and headaches/pain, sight loss, nausea or vomiting, and hazy or blurred vision.   Similar to Open Angle, Angle Closure is also caused by blocked drainage canals in the eye.  If you are experiencing symptoms, you really need to visit a doctor immediately.

Though keeping an eye out for symptoms is key for Glaucoma, there are certain ethnic groups that have a higher propensity to get this disease. Glaucoma occurs five more times more often in African Americans than other races and strikes earlier. The doctors suggest getting checked for Glaucoma every year after the age of 35. By getting checked up more regularly you will be able to identify symptoms earlier.

Insurance Coverage – Liability, Comprehensive and Collision Coverage

Insurance Coverage – Liability, Comprehensive and Collision Coverage

Car Insurance coverage is comprised of many different types like liability, comprehensive and collision. Some of these like liability is typically mandatory while others like collision is optional. What kind of full coverage do you need depends on your particular situation. If you have an older car, it may not make sense to have comprehensive and collision coverages. We have put together information on different types on
insurance coverage so you can make an informed decision for your personal insurance needs.

Liability coverage:

Liability Insurance coverage is typically required by all states in the United States. This will typically cover any bodily damage (physical harm to another person) resulting from a car accident if it is your fault. This will also cover property damages (like automobiles, road signs, houses etc.) if you are found to be the guilty party in an insurance claim.

The liability insurance is typically written as three numbers like 25/50/25. If you carry such a policy, this means that you have maximum coverage in the amount of $25,000 for bodily injury damage per person, maximum coverage of $50,000 for bodily injury damage per accident and amount of $25,000 for property damage per accident. Different states in America set their own limits of liability insurance coverage. You can get that information from this website:

Minimum Liability insurance

What do all these insurance coverage mean? That first number 25 means that the Insurance company will pay for things like medical bills, hospital fees and lost wages in the amount of $25,000 insurance coverage. If there are multiple people injured in an accident, this will limit coverage in the amount of the second number 50 ($50,000) that was mentioned above. The last number 25 is for property damage insurance coverage. The insurance company will pay upto $25,000 insurance coverage for things like other autos involved in the accident, any road signs or property items like fences and household items that were destroyed in the accident. This is again assuming that you were at fault in the accident. If we reverse the scenario and another person is at fault, then their insurance company will be responsible for not only damage to their vehicle but also to your auto, your medical bills and property damages.

Let's say that you live in Nevada and have 15/30/10 liability coverage and you are involved in an accident where you were at fault. In addition, several people get hurt in that accident unfortunately. If an ambulance is called which takes one or more people to the hospital, you will be held liable for payment of any medical expenses. If your insurance limit is only $30,000 per accident, and the total bills happen to be around $50k, you will be responsible for this amount.

$50k - $30k= $20,000!!

As such it is recommended that typically you carry 100/300/100 coverage or at the very least 50/100/50.

Comprehensive and Collision Coverage

Comprehensive and collision on the other hand are necessary to cover damages to your car. When you are involved in a car accident, the insurance company will pay for damages to your vehicle minus any deductible that are specified in your insurance policy.

Collision insurance will help you pay for any damages to your car in the event of an accident, hence a collision! This insurance policy typically carries a deductible, an amount between $250 to an amount of $1000. This deductible portion is typically paid by you (if you are at fault in an accident) and the rest of the damages are covered by the insurance company. Let's say you are involved in an accident and the damage to your SUV is around $4000. If your deductible is $500, this means is that your insurance company will pay you the following for damages.

$4000 - $500 = $3500

Most often they will just cut you a check in that amount and you can take the car to any body shop that you like for repairs.

Comprehensive is for damages that are not due to a traffic accident. It can include things like fire, damage, theft, vandalism, hit and run and natural disasters like tornadoes, hail damage or a big FAT deer in the road! A deductible still applies in this scenario, just like the collision policy. If you have an older car, lets say more than 10 years, it is recommended that you do not carry Comprehensive and Collision.

Auto Medical payments and No-fault option

Auto medical payments will pay for medical expenses incurred by you or the passengers in an accident. It does not matter if you are at fault or not, this option will cover any medical expenses like ER visits, doctors' fees and hospital charges. Some states have No-fault or PIP (Personal Injury Protection) also. Just like the Auto Medical payments, this will pay for medical expenses in addition to lost wages (Earnings you had to forgo due to injury). PIP coverage will also pay for lost wages if the passengers are injured and unable to perform their regular work duties.

Uninsured motorist and Underinsured motorist

Sometimes you may be involved in a hit and run accident or where the person responsible for the accident does not have any coverage. If you are not at fault, having Uninsured motorist will pick up the tab. In a similar fashion, Underinsured motorist kicks in when someone else is at fault, however they do not have enough insurance to cover all the expenses. In this scenario your Underinsured motorist policy will take care of the expenses.

Related Links for Insurance Coverage

-Insurance Coverage definitions from CarInsurance.com

-Auto Insurance Advice from Edmunds.com

-Cheap Motorcycle Insurance Buying Guide

Attention: Bloggers for IT Training topics FREE advertising

Attention: Bloggers for IT Training topics

We are actively looking for bloggers who would like to write content for our site

Learnincomputer.com. The subject matter has to do with topics around IT training, distance learning and computer training topics.

In return you will get FREE advertising on a site that gets 1,000,000 hits a month! There is NO compensation for these articles. Please contact me at blog@Learnincomputer.com today to get started and get your name out there.

How can you save energy with Ambit Energy?

How can you save energy with Ambit Energy?

Ambit Energy is a retail energy provider that is making a difference for energy consumers in Texas, New York, Illinois, Pennsylvania and Maryland. In the above mentioned states, Ambit Energy provides energy savings to residential, small business, and commercial customers. In 2009, Ambit recorded more than 400, 000 customers who saved on energy cost. In fact Ambit Energy was named the fastest-growing privately held company in north Texas by SMU's Caruth Institute for Entrepreneurship in their annual Dallas 100 rankings.

Ambit Energy customers enjoy significant energy savings and an attractive travel rewards program. Additionally, through the Free Energy Program, customers can save energy or even get free electricity each month by referring friends to Ambit Energy and save money on electricity.

Ambit Advantages:

-3 day/2 night stay just for enrolling

-Refer five friends and family who want to save energy and get a FREE travel package at the same time

-Up to 30% off energy savings on your electric bill

-No fee to switch to start your energy savings

-Refer 15 who can save energy cost and you get FREE ELECTRIC month after month!

1 Reward Point for every kWh of electricity you use

-Partner with #1 private company in America (Inc magazine)

http://www.inc.com/magazine/20100901/meet-americas-fastest-growing-company.html

-Fixed and variable rate plans are available to help you save on energy cost

-FREE personal website for referring customers who can save on electric bill

Did you know that a typical U.S. family spends more than $1,600 a year on home utility bills? The good news is, there is a lot you can do to save energy and money at home. With Ambit Energy, there is no complicated paperwork to start saving money and energy cost. Simply complete a short online order form and Ambit Energy will take care of the rest. Nothing will change in the way your service is delivered; you will just save energy cost!

Call Kash @ 214-518-5972 and start saving energy and money today.

http://kash.joinambit.com/

"Get FREE ELECTRIC today!"

Call or email Kash today and he can show you What is Ambit all about

Related Links for Saving Energy

-20 things you can do to save energy cost

-How to Save Energy in Your Home

-Top 10 Tips for Saving Energy Right Now – About.com

-How-to-conserve-energy-at-home. – TLC.com

Ceiling Fan Remote Control - Lightingindoors.com

Ceiling Fan Remote Control

Are you looking for a ceiling fan remote control replacement?


You've come to the right page for your ceiling fan remote control replacement needs. Lightingindoors.com has now grown to be one of the leading suppliers of ceiling fan remote control and wall control systems. Over the past decade, we have ventured into ceiling fan remote control business with products such as universal ceiling fan remote control conversion kit, thermostatic fan remote control system and wall control switch for ceiling fans. We provide ceiling fan remote control replacement solutions and surely you will find a replacement remote control for your fan right at this store.

Listed below are our most popular ceiling fan remote control products for replacement needs.

-Ceiling Fan Remote Control: Part No: JY708R/E01BT
-Ceiling Fan Remote Control: Part No: JY326B-1/JY622CF
-Ceiling Fan Remote Control: Part No: JY200/E01BT

Many ceiling fans are now equipped with the wireless remote control system. This feature offers full operation of the fan (and light attachment) from any location within a reasonable distance. This can be especially beneficial in adjusting the ceiling fan and lights without having to get up and operate a wall switch or pull-chain.

Some ceiling fan remote control systems are not built into the fans. However there is a factory default option added and can be used for future installation at an additional cost. Usually these ceiling fans consist of a remote receiver wired to the fan inside the ceiling canopy and the fan may still have a pull-chain that is not used. If this is the case, then the receiver can be removed and replaced with our universal remote control /receiver conversion kit to turn your regular ceiling fan into a remote controllable one.

If your ceiling fan remote is lost or broken regardless of your fan model, our company could offer you a universal remote control designed to work with the majority of different brands of ceiling fans. In addition, if you need detailed help in replacing/installing a remote control system for your ceiling fan, please refer to the installation manual that come with the kit. Here you will have find a step by step guide that you can follow for ceiling fan remote control replacement.


Lightingindoors.com link specializes in ceiling fan capacitors, ceiling fan remote control, lighting fixtures and ceiling fan parts and LED lighting.

Select the right Plano printing partner Minuteman Press Plano TX

 Select the right Plano printing partner Minuteman Press Plano TX

Do you know how to specify printing and get the most bang for your bucks?  You will most likely at some point need printed marketing communication pieces. With many options available, consider maximizing your print projects with a superior Plano printing resource.  At Minuteman Press Plano, we will deliver top quality print job.

Many factors determine the right type of printing for your job. Basically, Plano printing offers offset printing or digital printing in Plano and Frisco Texas. Offset printing is best for high quality reproduction in a quantity of 500 or more. Offset printing uses ink, which typically yields a much higher quality than digital printing, which uses toner. However, Plano printing digital technology makes digital printing a good choice for great short print runs and quick, economical projects. Select Minuteman Press conveniently located by Frisco and Plano areas as the right type of print partner for your business.

Paper will either enhance or destroy the printed piece. Besides the weight of paper, we will help you choose a coated (gloss) or uncoated (flat) finish. Texture also plays a big part in the overall feel of the piece and guidance from Plano printing makes all the difference.

The number of colors will help determine what kind of printing. In offset, you may choose to print using spot colors such as PMS (Pantone Matching Systems®) or 4-color process (CMYK) depending on the number of colors and/or effects you want to achieve. Spot colors such as PMS will give you a solid ink coverage rather than dots combined to create a color using cyan, magenta, yellow and black. You can see these small dots in 4-color process if you look at a magazine under magnification. The advantage of 4-color process is that you can print as many colors as you like and still only pay for a 4-color print job. Plano printing press comes in even number of colors.  The more colors do not necessarily make a piece look better.  The message and design of the entire piece is what makes it work.

At Minuteman Press Plano, we provide proofs at the beginning of the print job. With so much efforts spent on creating the piece, we want your print projects in North Texas to turn out right. Before the job prints, Minuteman Press Plano will provide a proof for your review.  Our printing expertise at the Plano Office will review your printing project that includes folding, scoring, finishing specification to assist you with the best possible outcome every time.

Site optimized by SEO Dallas company.

Blog is moving!!!!

We will be posting all the new content related to SQL Server topics at this blog
http://sqlazuretutorials.com/wordpress/

Thank you and have a good day!

SQL Server 2005 Managment Studio Express

How to find and remove duplicate rows in SQL Server:

Relational databases by design are supposed to eliminate redundancy. The whole idea of Redundancy is that
you should store information in one place in order to maintain data integrity. Reality however is far from this truth as there are many Production databases out there with redundant data.  As such the duplicate records are alive and well unfortunately!

In today’s discussion we are going to look at some sample code that will let you find duplicate data across one or many fields.  Next we will look at some different methods on how to remove and delete that duplicate data. For today’s exercises, we are going to be working with sample Microsoft Northwind database which has been around for a few years to say the least.  All this SQL code has been tested on SQL server 2008 Enterprise Edition.

-How do we create duplicate data for testing purposes?

First we are going to make a copy of the CUSTOMERS table using the following sql query.
Next we are going to reinsert the data for Country=UK twice into the same table.  We can do this by using the following SQL query to populate duplicate data.

Before we get started with finding and deleting duplicate records, we are going to do a simple record count. This will let us know how many rows are present in the CUSTOMERS_BAK table. Now that we have our baseline data we need to start looking at different methods of finding duplicates in CUSTOMERS_BAK table.

We are going to go through a set of examples to show you how to accomplish this task.

-How do you find duplicate records in a SQL table?

There are many ways of finding duplicate data in a SQL Server table, however the simplest and the most efficient method is to use the GROUP BY clause. Further information on this SQL expression can be found on this website.  In our case we are going to be using GROUP BY with a HAVING clause to find the repeating (duplicate) rows. The first query will let us find any rows with repeating Company Names in our table. We are going to use the following TSQL query in SQL Server for this.

SELECT COMPANYNAME
FROM CUSTOMERS_BAK
GROUP BY COMPANYNAME
HAVING COUNT(*)>1



This query is looking for those rows in the CUSTOMERS_BAK table where the record count is greater than 1 when you group them by COMPANYNAME field. Notice you will get the 7 rows from UK customers that we had added in an earlier step. Here is a screen capture of this duplicate query for you.

Duplicate Company Name

What if we wanted to find out, how often these records are repeated within the table.  We can easily do that by running sql command with COUNT function in it.

You will have noticed that so far we have only looked at one field, COMPANYNAME the above case.  In a similar fashion we can add multiple fields to the SQL query. Let us see if he wanted to find any duplicate rows with fields CITY and COUNTRY that are being repeated in our table. How would we find the duplicates for two fields instead of one?  Let us try this sql server command first.

SELECT CITY, COUNTRY, COUNT(*) AS [# OF TIMES]
FROM CUSTOMERS_BAK
GROUP BY CITY
HAVING COUNT(*)>1



When we ran the above SQL statement ro find duplicate records, we got the following error:

Msg 8120, Level 16, State 1, Line 1
Column 'CUSTOMERS_BAK.Country' is invalid in the select list because it is not contained
in either an aggregate function or the GROUP BY clause.

The reason for this error is that although we are selecting CITY, COUNTRY fields, in our GROUP BY clause, we only have the CITY field in the SELECT portion. The number of fields in the SELECT clause has to match the GROUP BY clause. It is ok to have the COUNT(*) as it is an aggregate function. This is how GROUP BY clause works because you are trying to find the CITY and the COUNTRY fields that are also grouped by the same fields. 

You can simply run the following query that will give you the end result. You will notice that there are 11 rows in our table that match the duplicate criteria. 

SELECT CITY, COUNTRY, COUNT(*) AS [# OF TIMES]
FROM CUSTOMERS_BAK
GROUP BY CITY, COUNTRY
HAVING COUNT(*)>1

Let us say that we actually have duplicate rows across all the fields in a MS SQL table. This is probably the most common type of situation that you will run into working with redundant data. In this one you simply need to add all the fields in the table and also make sure that all those fields are also added in the GROUP BY clause.  Using our CUSTOMERS_BAK table we can use the following tsql script to do just that.

You will notice that there are seven rows in the customer table that are repeated.

How do you remove duplicate rows that?

So far we have covered how to find duplicate data, the next question obviously is: How to delete duplicate rows? There are many options and ways on how you can eliminate duplicate rows, we are going to look at two of these options. Both these options involve inserting non-duplicate data into another table first and then renaming the new table to the original table.

Remove duplicate rows - Option 1

This is the one I prefer as it is more efficient and uses similar code to what we have already seen.  We are going to use a GROUP BY without the having clause. Here’s the query to remove duplicates. We have also included a screen capture from part of the sql statement that removes the duplicates.

Remove duplicate data

Remove duplicate rows - Option 2

Another method is to use the UNION expression to filter out the duplicate rows and leave distinct values behind. Here’s the sql command in order to do that.

Related articles to Finding and Deleting Duplicate rows:

-Remove Duplicate Rows in SQL Server
-Delete duplicate rows in a table
-Using common table expression to remove duplicate data
-Microsoft SQL Server 2008 Management and Administration
-Microsoft SQL Server 2008 Bible
-SQL Server Tutorials

How to import data in SQL Azure from SQL 2008 R2 using SSIS?

How to import data in SQL Azure from SQL 2008 R2 using SSIS

When working with SQL databases one of the most common tasks is to import or export data from a database.  In experimenting with SQL Azure, I realized that it is not possible to migrate data using the familiar Import and Export wizard that is available in SQL Server Management Studio (SSMS). What you have to do it is create a SSIS (SQL Server Integration services) package in Business Intelligence Development Studio (BIDS) and then execute it. 

In this article we are going to show you how to copy data from on-site SQL 2008 to off-site SQL Azure. For this example we are going to export data from Products table in a local database to a database in the cloud using SQL Integration Services.

Before we jump into SSIS, we found another thing that is unique to SQL Azure version. We noticed that we need to have a clustered index on the destination table before SSIS will let us import data into it. So we have to do two things on the destination SQL Azure database first

-Create the Products table
-Define a clustered index on the table

We have included the SQL script for the above two steps for your convenience.


--This script creates the Products table on SQL Azure.
--Next we create a clustered index on ProductID


CREATE TABLE [dbo].[Products](
    [ProductID] [int] NOT NULL,
    [ProductName] [nvarchar](40) NOT NULL,
    [SupplierID] [int] NULL,
    [CategoryID] [int] NULL,
    [QuantityPerUnit] [nvarchar](20) NULL,
    [UnitPrice] [money] NULL,
    [UnitsInStock] [smallint] NULL,
    [UnitsOnOrder] [smallint] NULL,
    [ReorderLevel] [smallint] NULL,
    [Discontinued] [bit] NOT NULL

)
---ON [PRIMARY]

CREATE CLUSTERED INDEX IX_Product ON dbo.Products
    (
    ProductID
    )

GO





Before getting started please realize that we are working with Enterprise edition of SQL Server 2008R2 and cloud version on SQL Server.
First off launch the Business Intelligence Development Studio or BIDS from the start menu by doing the following:
 
Start – All Programs – Microsoft Visual Studio 2008 - Microsoft Visual Studio 2008

This will launch the Business Intelligence Development Studio. Next from the File menu and do the following:

File - New - Project

Next go ahead and select Integration Services Project.  This is shown in the dialog box below:

Integration Services Project

You can add a name for the project and then click OK.  This action will go ahead and create the necessary components under Solution Explorer.  In addition it will also launch the Package Design window in the middle.  The next thing you need to do is to add a data source that will be used to export data. Within the Solution Explorer, right click on Data Sources and then select New Data Source.  This will open up the Data Source wizard screen, go ahead and click on Next.  If you do not have an existing data connection, you can create one by clicking New.

In this example we are going to use sample Northwind database on a local instance of SQL server 2008 R2 to export data. This SSIS data source is for server KASHMONEY-PC\SQL08R2 as shown in the following computer screenshot.

On-site SQL 2008 R2

The next thing we need to do is to define the data destination where data will be imported into. In this case this we will use a SQL Azure database in the cloud. In order to create this connection, we need to define another data source so we are going to follow the earlier step.


We will invoke the Data Source Wizard one more time as shown below.

 Here we had some issues as we tried different data providers in order to connect successfully to a SQL Azure server and the database.  Bottom line is you need to use the .Net Providers\SqlClient data provider as shown below in the Connection Manager screen.

In addition you will need to know the server address, SQL username and the SQL password.

SQL Azure Connection

Next click OK which will finish the wizard.  At this point, we have our data source and are data destination all squared away. We have included a screen capture that shows the end of the data wizard with the connection string information.

Destination Database

Click Finish to exit out of this.

Now we are going to configure the SSIS package details, remember an Integration package is the building block for data movement in SQL Server. Make sure that you are on the Control Flow tab in design pane. From the Toolbox on the left, drag the Data Flow task under the Control Flow Items onto the design surface.

This step has been displayed for you in the following figure.

SSIS Package

Next switch to the Data Flow tab on the SSIS design pane. Next we will be adding a data source from our local on premise database. We drag the OLE DB Source from the Data Flow Sources.

Here is what it looks like on our puter monitor.

OLE DB Source

We need to configure the SSIS data flow item. Right click on OLE DB Source and select Edit.  On the next dialog OLE DB Source Editor, go ahead and select New.  This will open up a new screen as shown below.

On Premise SQL Server

 From the data connections pane, select LocalHost.Northwind and select Ok.

So far we have just made the connection to the source database, we still need to get the table information. Here you will select Table or view under Data access mode. Next choose Products under Name of the table or the view. After you click Ok, you have successfully created SSIS data flow from the source. This step is shown below:

The next step is to create the ADO Net Destination, this Is where the data will be imported into. From the Toolbox on the left, go ahead and select the Ado Net destination SSIS Data Flow item  and then move it over to the design pane in Data Flow tab.  This is shown in the screen capture right below, notice that the connection has not been configured yet and is displayed in red.

Data Flow controls

Before you can configure the ADO NET destination, you have to drag the output (green arrow) from the OLE DB Source to the ADO NET Destination connection. Next you can right click on ADO NET destination and select Edit. This will launch a dialog box titled ADO NET Destination Editor.

Go ahead and click on New. You will get the Connection Manager screen shown below.

Go ahead and select the SQL Azure connection that was defined in earlier step. Also you will need to point to the table we created using SQL Script. For Use a table or view select Products. Here is what is looks like on our Microsoft Visual Studio 2008 application.

One final thing we need to do before we can import data using Integration Services is to do map the columns between the data source and the data destination. If you are using the same table schema structure on the source and the destination tables, this should be pretty straight forward.


Click on Mappings in the left pane and make sure the Input and Output columns match. We have included a screen shot for your review.
 

Column Mappings

Let us take a look at our Data Flow designer before we kick things off. We have done this so far

1- Defined a local on premise server connection to Northwind database
2- Defined a cloud SQL Azure server connection to an off site database
3- Defined a OLE DB source that maps to local on-site database connection
4- Defined a ADO NET destination that maps to an off-site cloud SQL Azure database connection

Here is what we have so far, we are almost done.

Integration Services Package

Now we are going to kick off the SSIS package, all you need to do is click on Execute (green arrow) or Debug – Start Debugging. In our case, all the items are configured properly and our SSIS package executed with success. we have the following end result and were able to move 77 records from SQL 2008 to SQL Azure.

Execute SSIS package

We can verify the data on the target SQL Azure location with this SQL query.

Check data at Target

 




We were successful in migrating data from an on premise local database to a SQL Azure cloud database using SQL Server Integration Services.


More tips on SQL Azure can be found at our site:

http://sqlazuretutorials.com/

Tags: Import data, SQL Azure, SQL 2008 R2, SSIS

SQL Aggregate functions like SQL MAX, SQL MIN, SQL COUNT And SQL AVERAGE function

SQL Aggregate functions are necessary to summarize numerical data in a SQL Server database. We have already looked at other transact SQL statements like SELECT, INSERT, DELETE and UPDATE SQL statements. Those typically are used to return rows of data that matches the criteria in the WHERE clause. SQL statements using aggregate functions like MIN or MAX however return just one value after SQL server has already applied the aggregation and summarized the data.  In this manner SQL Server aggregate functions perform their operation across multiple rows of data. 

For today’s discussion we are going to look at AdventureWorks2008 sample database in SQL Server 2008. You can download the associated data from this location:

Products:


http://www.sqlserver2008tutorial.com/blog/sql-aggregate-functions/production_products.xls

Orders:

http://www.sqlserver2008tutorial.com/blog/sql-aggregate-functions/sales_order_header.xls



There are a number of SQL aggregate functions; however we are only going to look at the important ones so here we go:

SQL MIN Aggregate function:

The SQL function is used to return the minimum value in the select list, ignoring any Null value.  This would typically be used let’s say in a PRODUCTS table to find your cheapest product.

SELECT
MIN(LISTPRICE) AS [MINIMUM PRICED ITEM]
FROM PRODUCTION.PRODUCT
WHERE SIZE IS NOT NULL

--8.99

SQL MAX Aggregate function:

This Aggregate function is used to return the maximum value in the select list, ignoring th any Nulls.  Similar to the SQL MIN function, the SQL Max function can be used to find the highest priced item in the above products table. Here is the SQL syntax for MIN function:

SELECT
MAX(LISTPRICE) AS [MAXIMUM PRICED ITEM]
FROM PRODUCTION.PRODUCT
WHERE SIZE IS NOT NULL

--3578.27

SQL SUM Aggregate function:

This SQL function returns the total sum of all items in the select list, ignoring any Nulls.  The sum function will add up all the values for a column in a certain table.  For example we are going to use the SALESORDERHEADER table and summarize the TOTALDUE field

SELECT SUM(TOTALDUE) AS SUMMATION
FROM SALES.SALESORDERHEADER
WHERE CUSTOMERID='29898'

--197634.248

SQL AVG Aggregate function:

SQL provides the AVG function to return the average of values in the select list, ignoring any Nulls.  This is an important function which will help you find average value of let’s say product prices in your product table.  We are going to use the same SALESORDERHEADER table from the prior example to show you SQL average function in action


SELECT AVG(TOTALDUE) AS AVERAGE
FROM SALES.SALESORDERHEADER
WHERE CUSTOMERID='29898'

--49408.562

SQL COUNT Aggregate function:

A lot of times all you’re trying to figure out his how many rows or records apply to a certain condition.  This is where the sql COUNT function comes in handy as it will simply count the number of rows that meet your criteria. The COUNT aggregate function returns the number of items in the select list. This ignores the NULL values unless you use COUNT(*).  This is by far the most important aggregate function that you will be using. Here is an example from the SALESORDERHEADER table.

SELECT COUNT(TOTALDUE) AS [ITEM COUNT]
FROM SALES.SALESORDERHEADER
WHERE CUSTOMERID='29898'

--4 RECORDS

Sample video on Aggregate Functions here:

http://www.learningsqlserver2008.com/products.htm

TAGS: SQL Server AGGREGATE FUNCTIONS, SQL COUNT, SQL AVG, SQL MAX, SQL MIN, SQL SUM

SQL STATEMENTS INCLUDING SQL INSERT, SQL UPDATE And SQL DELETE:

SQL INSERT STATEMENT:

The SQL INSERT statement adds one or more new rows to a table. In a simplified version, INSERT has this sql syntax:


INSERT [INTO] table_or_view
[(column_list)]
data_values

Using this syntax you can typically insert data in a single row shown as follows. Here we are using Northwind sample database from Microsoft.


INSERT INTO REGION VALUES ('5','MIDWEST')

If you want to insert data in a table with multiple records, INSERT statement can use a sub-select (sub query) to do just that.
In the next sql insert command example we are trying to insert contact information into CUSTOMER_ CONTACT table from CUSTOMER table. Here’s the transact SQL syntax for insert statement with a sub query:


INSERT CUSTOMER_CONTACT (CUSTOMERID, CONTACTNAME, PHONE)
SELECT  CUSTOMERID, CONTACTNAME, PHONE
FROM CUSTOMERS

This will insert only the specific fields, however all the records from the customers table.
Sql insert comman. Here is a screen shot of what this sql command looks like in SQL Server Management Studio (SSMS)

What if you wanted to make a copy of a table and insert data at the same time? No problem. You can do that by using SELECT * INTO. Here is an example of a copy of CUSTOMERS table with a new table is CUSTOMER_CONTACT_BAK. When you use WHERE 1=2, it just creates the table and  does not insert any data in the table.

SELECT * INTO CUSTOMER_CONTACT_BAK
FROM CUSTOMERS WHERE 1=2

SQL UPDATE STATEMENT:

The Update SQL command is used to update data in tables. Here is the basic syntax of this important transact sql (tsql) command.


UPDATE TABLE
SET COLUMN=NEW VALUE 
WHERE CONDITION

If you use the update statement without the WHERE clause, you will update all the rows in the table with the same value Ooops! Be careful with this sql command for sure.

For the next scenario let us say there is a shortage of seafood in the market, so we need to raise the price by 5% for these items. Go ahead and try this update command on Northwind database.


UPDATE PRODUCTS
SET UNITPRICE=UNITPRICE * 1.05
WHERE CATEGORYID=8

Here is a screen capture of the above tsql command from SSMS,

Just like the SQL INSERT command, you can also use a sub-select command with an SQL UPDATE statement also. The concept is say you are trying to update one table with data from another table.  This is commonly used when you are trying to fix the transactional table with values from some lookup table. Let us take a look at this next update sql example now.

UPDATE DBO.CUSTOMER_CONTACT
SET REGION=REGIONDESCRIPTION
FROM DBO.REGION_STATE
WHERE CUSTOMER_CONTACT.STATE=REGION_STATE.STATE

In the above sql query we are updating CUSTOMER_CONTACT table, field REGION with values from REGION_STATE table, field REGIONDESCRIPTION. The catch is that it will only update those rows where there is a match between two tables on the state field.

Here are the copies of the CUSTOMER_CONTACT table before and after update

Before the sql update command
After the update sql command

SQL DELETE STATEMENT:

In five words SQL Delete statement “Removes rows from a table!” Be very careful with this command. Here is the basic syntax of a sql delete statement


DELETE FROM TABLE
WHERE CONDITION

Notice that absence of where clause deletes all rows. Here is an example of a simple delete query command.

DELETE FROM NORTHWIND..REGION
WHERE REGIONDESCRIPTION='MIDWEST'

This delete statement will affect the row that we had added earlier in the INSERT statement. Lets us say that we stopped carrying ay TOFU products, you could get rid of those rows by using the following sql command.


DELETE FROM Products
WHERE ProductName LIKE '%TOFU%'

When we ran this we got the following error:


The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'.
The statement has been terminated.

We have capture the above sql command and the error in the following image

This SQL error has to do with the Primary key and Foreign key relationships. Basically what it is saying is that you have existing customer orders with this product. You would first need to delete the specific rows in ORDER DETAILS table and then delete the “TOFU” rows in PRODUCTS table.

Just like the INSERT And UPDATE statements, DELETE sql statement can also use a sub-select (sub query). Also please note DELETE * FROM TABLE will not work and you have to use DELETE FROM TABLE format.

TAGS: SQL INSERT, SQL DELETE, SQL UPDATE, NORTHWIND