Showing posts with label SQL 2008. Show all posts
Showing posts with label SQL 2008. Show all posts

Tuesday, November 24, 2009

update one table with data from another table

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in two RDBMS that I use.

Table Structures and values:
TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)
The foreign key relationship between the two tables is based on A.a = B.a1
The data in these 2 tables is as follows:
I. TableA
a b c d
1 x y z
2 a b c
3 t x z

II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:
UPDATE TABLEASET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/

Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z


SQL Server:
UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.
Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z



Happy writing SQL

Thursday, July 9, 2009

Divide by zero error encountered

You may have a request to extract percentages and you encounter this dreaded error, when developing in SQL 2000, SQL 2005 or even SQL 2008.

The workaround for the Divide by zero error encountered. error is by using the NULLIF Function as the the example below demonstrates. The NULLIF function returns the first expression if the two expressions are not equivalent otherwise a NULL is returned.

Usage: SELECT NULLIF(col1, 0)

When col1 = 0 a NULL will be returned.

Example


CREATE TABLE #Products
(
Product VARCHAR(10),
Price DECIMAL(7, 2),
CostPrice DECIMAL(7, 2)
)


INSERT #Products SELECT 'A4 Books', 12.00, 6.00
INSERT #Products SELECT 'Pens', 0.10, 0.05
INSERT #Products SELECT 'Pencils', 0, 0.05


SELECT Product, (CostPrice / Price) * 100 AS PercentProfit FROM #Products


Will Produce the following Error:

(3 row(s) affected)

Server: Msg 8134, Level 16, State 1, Line 14
Divide by zero error encountered.

---------------------------------------------------------------------

SELECT Product, ((CostPrice/NULLIF(Price, 0))*100) AS PercentProfit
FROM #Products

Returns:

Product PercentProfit
---------- -----------------------
A4 Books 50.0000000000
Pens 50.0000000000
Pencils NULL


(3 row(s) affected)

Friday, June 26, 2009

SQL Query to get customers with one product

SQL Query using min and max aggregate function

You may want to find Customer that only has only bought one particular product in the Sales table
You will know the Product_ID is the in the Products table, in this example we use ProductID=10

In this example for each client you will find the lowest value Product_ID and the highest value Product_ID, then filters rows to those where Product_ID is the same as the variable.

--Written by Rishka Booran-Johnson
-- Get Customers with only one product

CREATE TABLE #Customers (
customer_ID int,
firstname varchar(30),
lastname varchar(30)
)

CREATE TABLE #Sales (
Transaction_ID int,
Customer_ID int,
Product_ID int,
Sale_date datetime
)


CREATE TABLE #Products (
Product_ID int,
Product_name varchar(30),
Product_description varchar(50)
)


INSERT #customers SELECT 1, 'john', 'smith'
INSERT #customers SELECT 2, 'sally', 'johnson'
INSERT #customers SELECT 3, 'joe', 'bloggs'



INSERT #Products SELECT 1, 'bicycles', ''
INSERT #Products SELECT 2, 'trains', ''
INSERT #Products SELECT 3, 'dolls', ''



INSERT #Sales SELECT 1, 1, 2, '2009-06-01'
INSERT #Sales SELECT 2, 1, 1, '2009-06-08'
INSERT #Sales SELECT 2, 1, 1, '2009-06-07'
INSERT #Sales SELECT 3, 2, 2, '2009-06-12'
INSERT #Sales SELECT 4, 2, 2, '2009-06-05'
INSERT #Sales SELECT 5, 3, 3, '2009-06-06'
INSERT #Sales SELECT 6, 3, 2, '2009-06-01'
INSERT #Sales SELECT 7, 3, 1, '2009-06-03'


declare
@vproduct_ID int

set @vproduct_ID = 2; -- this would be your input variable for trains


SELECT s.customer_ID, c.firstname, c.lastname, MIN(s.product_ID), MAX(s.product_ID)
FROM #sales s, #customers c
WHERE s.customer_ID = s.customer_ID
AND s.customer_ID = c.customer_ID
GROUP BY s.customer_ID, c.firstname, c.lastname
HAVING MIN(s.product_ID) = @vproduct_ID and MAX(s.product_ID) = @vproduct_ID


DROP TABLE #Customers
DROP TABLE #Sales
DROP TABLE #Products

Wednesday, June 24, 2009

How to check database growth

Sometimes you’ll want to know how much and how fast your database has been growing. Usually you will have a document that tells you the size information on all the databases, but this is not always the case in the real world. Considering that you perform regular backups, the following script can give you a estimated idea of growth over time of your database.

select
BackupDate = convert(varchar(10),backup_start_date, 111),
SizeInGigs=floor( backup_size/1024000000)
from msdb..backupset
where
database_name = ‘DatabaseName’
and type = ‘d’
order by
backup_start_date desc

Furthermore the Backup type can be used for the following types as well , making it easier to track other types of objects:

  • D = Database
  • I = Differential database
  • L = Log
  • F = File or filegroup
  • G =Differential file
  • P = Partial
  • Q = Differential partial

Tuesday, April 14, 2009

SQL.. the beginning.

The first time I ever saw a database was in Paradox (database), in 1995. It was a huge learning curve, but I quickly learned what tables, columns and rows were. Then I learned how to build relationships and write SQL queries. (SQL is short for Structured Query Language)

This is how I then figured it was put together.

Every database has collection of one or more tables.
Tables are made up of rows and columns.
The rows contain the data and columns describe the format of the data, namely data type, for that column.

Imagine a telephone directory, information of a person’s first name, surname , address and ph number, this is how the basic table would be constructed:

Table: Phone_Directory

























FirstNameLastNameAddressPhone
JakeBlack123 Round Road, CA123 321-1234
SoniaCoke19 Sailfish Road, WA232 555-5555
MelBrown32 71st Ave, NY614 555-5432