SQL Tutorial - SELECT Statement

SQL Tutorial

SELECT Statement -- Extended Query Capabilities

This subsection details the remaining features of SELECT statements. The basics are at SELECT Statement Basics.

The extended features are grouped as follows:

• Sorting Query Results -- using the ORDER BY clause
• Expressions -- in the SELECT clause and WHERE clause
  • Literal -- self-defining values
  • Function Call -- expression functions
  • System Value -- builtin system values
  • Special Construct -- special expression construct
  • Numeric or String Operator -- expression operators
• Joining Tables -- in the FROM clause
  • Outer Join -- extended join
  • Self Join -- joining a table to itself
• Subqueries -- embedding a query in another
  • Predicate Subqueries -- subqueries in logical expressions
  • Scalar Subqueries -- subqueries in scalar expressions
  • Table Subqueries -- subqueries in the FROM clause
• Grouping Queries -- using the GROUP BY clause, Set Function and HAVING clause
  • GROUP BY Clause -- specifying grouping columns
  • Set Functions -- summary functions
  • HAVING Clause -- filtering grouped rows
• Aggregate Queries -- using Set Functions and the HAVING clause
• Union Queries -- using the query operator, UNION
  • Union-Compatible Queries -- query requirements for Union

ORDER BY Clause

The ORDER BY clause is optional. If used, it must be the last clause in the SELECT statement. The ORDER BY clause requests sorting for the results of a query.

When the ORDER BY clause is missing, the result rows from a query have no defined order (they are unordered). The ORDER BY clause defines the ordering of rows based on columns from the SELECT clause. The ORDER BY clause has the following general format:

ORDER BY column-1 [ASC|DESC] [ column-2 [ASC|DESC] ] ...

column-1, column-2, ... are column names specified (or implied) in the select list. If a select column is renamed (given a new name in the select entry), the new name is used in the ORDER BY list. ASC and DESC request ascending or descending sort for a column. ASC is the default.

ORDER BY sorts rows using the ordering columns in left-to-right, major-to-minor order. The rows are sorted first on the first column name in the list. If there are any duplicate values for the first column, the duplicates are sorted on the second column (within the first column sort) in the Order By list, and so on. There is no defined inner ordering for rows that have duplicate values for all Order By columns.

Database nulls require special processing in ORDER BY. A null column sorts higher than all regular values; this is reversed for DESC.

In sorting, nulls are considered duplicates of each other for ORDER BY. Sorting on hidden information makes no sense in utilizing the results of a query. This is also why SQL only allows select list columns in ORDER BY.

For convenience when using expressions in the select list, select items can be specified by number (starting with 1). Names and numbers can be intermixed.

Example queries:

SELECT * FROM sp ORDER BY 3 DESC

sno	pno	qty
S1	P1	NULL
S3	P1	1000
S3	P2	200
S2	P1	200

SELECT name, city FROM s ORDER BY name

name	city
John	London
Mario	Rome
Pierre	Paris

SELECT * FROM sp ORDER BY qty DESC, sno

sno	pno	qty
S1	P1	NULL
S3	P1	1000
S2	P1	200
S3	P2	200

Expressions

In the previous subsection on basic Select statements, column values are used in the select list and where predicate. SQL allows a scalar value expression to be used instead. A SQL value expression can be a:

• Literal -- quoted string, numeric value, datetime value
  
• Function Call -- reference to builtin SQL function
  
• System Value -- current date, current user, ...
  
• Special Construct -- CAST, COALESCE, CASE
  
• Numeric or String Operator -- combining sub-expressions

Literals

A literal is a typed value that is self-defining. SQL supports 3 types of literals:

• String -- ASCII text framed by single quotes ('). Within a literal, a single quote is represented by 2 single quotes ('').
  
  
  
• Numeric -- numeric digits (at least 1) with an optional decimal point and exponent. The format is
  

  [ddd][[.]ddd][E[+|-]ddd]

  Numeric literals with no exponent or decimal point are typed as Integer. Those with a decimal point but no exponent are typed as Decimal. Those with an exponent are typed as Float.
  
  
  
• Datetime -- datetime literals begin with a keyword identifying the type, followed by a string literal:
  
  
  
  • Date -- DATE 'yyyy-mm-dd'
    
  • Time -- TIME 'hh:mm:ss[.fff]'
    
  • Timestamp -- TIMESTAMP 'yyyy-mm-dd hh:mm:ss[.fff]'
    
  • Interval -- INTERVAL [+|-] string interval-qualifier
  The format of the string in the Interval literal depends on the interval qualifier. For year-month intervals, the format is: 'dd[-dd]'. For day-time intervals, the format is '[dd ]dd[:dd[:dd]][.fff]'.

SQL Functions

SQL has the following builtin functions:

• SUBSTRING(exp-1 FROM exp-2 [FOR exp-3])
  

  Extracts a substring from a string - exp-1, beginning at the integer value - exp-2, for the length of the integer value - exp-3. exp-2 is 1 relative. If FOR exp-3 is omitted, the length of the remaining string is used. Returns the substring.
  

  
  
• UPPER(exp-1)
  

  Converts any lowercase characters in a string - exp-1 to uppercase. Returns the converted string.
  

  
  
• LOWER(exp-1)
  

  Converts any uppercase characters in a string - exp-1 to lowercase. Returns the converted string.
  

  
  
• TRIM([LEADING|TRAILING|BOTH] [FROM] exp-1)
  TRIM([LEADING|TRAILING|BOTH] exp-2 FROM exp-1)
  

  Trims leading, trailing or both characters from a string - exp-1. The trim character is a space, or if exp-2 is specified, it supplies the trim character. If LEADING, TRAILING, BOTH are missing, the default is BOTH. Returns the trimmed string.
  

  
  
• POSITION(exp-1 IN exp-2)
  

  Searches a string - exp-2, for a match on a substring - exp-2. Returns an integer, the 1 relative position of the match or 0 for no match.
  

  
  
• CHAR_LENGTH(exp-1)
  CHARACTER_LENGTH(exp-1)
  

  Returns the integer number of characters in the string - exp-1.
  

  
  
• OCTET_LENGTH(exp-1)
  

  Returns the integer number of octets (8-bit bytes) needed to represent the string - exp-1.
  

  
  
• EXTRACT(sub-field FROM exp-1)
  

  Returns the numeric sub-field extracted from a datetime value - exp-1. sub-field is YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR or TIMEZONE_MINUTE. TIMEZONE_HOUR and TIMEZONE_MINUTE extract sub-fields from the Timezone portion of exp-1. QUARTER is (MONTH-1)/4+1.

System Values

SQL System Values are reserved names used to access builtin values:

• USER -- returns a string with the current SQL authorization identifier.
  
• CURRENT_USER -- same as USER.
  
• SESSION_USER -- returns a string with the current SQL session authorization identifier.
  
• SYSTEM_USER -- returns a string with the current operating system user.
  
• CURRENT_DATE -- returns a Date value for the current system date.
  
• CURRENT_TIME -- returns a Time value for the current system time.
  
• CURRENT_TIMESTAMP -- returns a Timestamp value for the current system timestamp.

SQL Special Constructs

SQL supports a set of special expression constructs:

• CAST(exp-1 AS data-type)
  

  Converts the value - exp-1, into the specified date-type. Returns the converted value.
  

  
  
• COALESCE(exp-1, exp-2 [, exp-3] ...)
  

  Returns exp-1 if it is not null, otherwise returns exp-2 if it is not null, otherwise returns exp-3, and so on. Returns null if all values are null.
  

  
  
• CASE exp-1 { WHEN exp-2 THEN exp-3 } ... [ELSE exp-4] END
  CASE { WHEN predicate-1 THEN exp-3 } ... [ELSE exp-4] END
  

  The first form of the CASE construct compares exp-1 to exp-2 in each WHEN clause. If a match is found, CASE returns exp-3 from the corresponding THEN clause. If no matches are found, it returns exp-4 from the ELSE clause or null if the ELSE clause is omitted.
  

  The second form of the CASE construct evaluates predicate-1 in each WHEN clause. If the predicate is true, CASE returns exp-3 from the corresponding THEN clause. If no predicates evaluate to true, it returns exp-4 from the ELSE clause or null if the ELSE clause is omitted.

Expression Operators

Expression operators combine 2 subexpressions to calculate a value. There are 2 basic types -- numeric and string.

• String Operators
  

  There is just one string operator - ||, for string concatenation. Both operands of || must be strings. The operator concatenates the second string to the end of the first. For example,
  

  'ab' || 'cd'  ==> 'abcd'

  
  
• Numeric operators
  

  The numeric operators are common to most languages:
  

  
  
  • + -- addition
    
  • - -- subtraction
    
  • * -- multiplication
    
  • / -- division
  All numeric operators can be used on the standard numeric data types:
  
  
  
  • Integer -- TINYINT, SMALLINT, INT, BIGINT
    
  • Exact -- NUMERIC, DECIMAL
    
  • Approximate -- FLOAT, DOUBLE, REAL
  Automatic conversion is provided for numeric operators. If an integer type is combined with an exact type, the integer is converted to exact before the operation. If an exact (or integer) type is combined with an approximate type, it is converted to approximate before the operation.
  

  The + and - operators can also be used as unary operators.
  

  The numeric operators can be applied to datetime values, with some restrictions. The basic rules for datetime expressions are:
  

  
  
  • A date, time, timestamp value can be added to an interval; result is a date, time, timestamp value.
    
  • An interval value can be subtracted from a date, time, timestamp value; result is a date, time, timestamp value.
    
  • An interval value can be added to or subtracted from another interval; result is an interval value.
    
  • An interval can be multiplied by or divided by a standard numeric value; result is an interval value.
  
  

  A special form can be used to subtract a date, time, timestamp value from another date, time, timestamp value to yield an interval value:
  

  (datetime-1 - datetime-2) interval-qualifier

  The interval-qualifier specifies the specific interval type for the result.
  

  A second special form allows a ? parameter to be typed as an interval:
  

  ? interval-qualifier

In expressions, parentheses are used for grouping.

Joining Tables

The FROM clause allows more than 1 table in its list, however simply listing more than one table will very rarely produce the expected results. The rows from one table must be correlated with the rows of the others. This correlation is known as joining.

An example can best illustrate the rationale behind joins. The following query:

SELECT * FROM sp, p

Produces:

sno	pno	qty	pno	descr	color
S1	P1	NULL	P1	Widget	Blue
S1	P1	NULL	P2	Widget	Red
S1	P1	NULL	P3	Dongle	Green
S2	P1	200	P1	Widget	Blue
S2	P1	200	P2	Widget	Red
S2	P1	200	P3	Dongle	Green
S3	P1	1000	P1	Widget	Blue
S3	P1	1000	P2	Widget	Red
S3	P1	1000	P3	Dongle	Green
S3	P2	200	P1	Widget	Blue
S3	P2	200	P2	Widget	Red
S3	P2	200	P3	Dongle	Green

Each row in sp is arbitrarily combined with each row in p, giving 12 result rows (4 rows in sp X 3 rows in p.) This is known as a cartesian product.

A more usable query would correlate the rows from sp with rows from p, for instance matching on the common column -- pno:

SELECT *
FROM sp, p
WHERE sp.pno = p.pno

This produces:

sno	pno	qty	pno	descr	color
S1	P1	NULL	P1	Widget	Blue
S2	P1	200	P1	Widget	Blue
S3	P1	1000	P1	Widget	Blue
S3	P2	200	P2	Widget	Red

Rows for each part in p are combined with rows in sp for the same part by matching on part number (pno). In this query, the WHERE Clause provides the join predicate, matching pno from p with pno from sp.

The join in this example is known as an inner equi-join. equi meaning that the join predicate uses = (equals) to match the join columns. Other types of joins use different comparison operators. For example, a query might use a greater-than join.

The term inner means only rows that match are included. Rows in the first table that have no matching rows in the second table are excluded and vice versa (in the above join, the row in p with pno P3 is not included in the result.) An outer join includes unmatched rows in the result. See Outer Join below.

More than 2 tables can participate in a join. This is basically just an extension of a 2 table join. 3 tables -- a, b, c, might be joined in various ways:

• a joins b which joins c
  
• a joins b and the join of a and b joins c
  
• a joins b and a joins c

Plus several other variations. With inner joins, this structure is not explicit. It is implicit in the nature of the join predicates. With outer joins, it is explicit; see below.

This query performs a 3 table join:

SELECT name, qty, descr, color
FROM s, sp, p
WHERE s.sno = sp.sno
AND sp.pno = p.pno

It joins s to sp and sp to p, producing:

name	qty	descr	color
Pierre	NULL	Widget	Blue
John	200	Widget	Blue
Mario	1000	Widget	Blue
Mario	200	Widget	Red

Note that the order of tables listed in the FROM clause should have no significance, nor does the order of join predicates in the WHERE clause.

Outer Joins

An inner join excludes rows from either table that don't have a matching row in the other table. An outer join provides the ability to include unmatched rows in the query results. The outer join combines the unmatched row in one of the tables with an artificial row for the other table. This artificial row has all columns set to null.

The outer join is specified in the FROM clause and has the following general format:

table-1 { LEFT | RIGHT | FULL } OUTER JOIN table-2 ON predicate-1

predicate-1 is a join predicate for the outer join. It can only reference columns from the joined tables. The LEFT, RIGHT or FULL specifiers give the type of join:

• LEFT -- only unmatched rows from the left side table (table-1) are retained
  
• RIGHT -- only unmatched rows from the right side table (table-2) are retained
  
• FULL -- unmatched rows from both tables (table-1 and table-2) are retained

Outer join example:

SELECT pno, descr, color, sno, qty
FROM p LEFT OUTER JOIN sp ON p.pno = sp.pno

pno	descr	color	sno	qty
P1	Widget	Blue	S1	NULL
P1	Widget	Blue	S2	200
P1	Widget	Blue	S3	1000
P2	Widget	Red	S3	200
P3	Dongle	Green	NULL	NULL

Self Joins

A query can join a table to itself. Self joins have a number of real world uses. For example, a self join can determine which parts have more than one supplier:

SELECT DISTINCT a.pno
FROM sp a, sp b
WHERE a.pno = b.pno
AND a.sno <> b.sno

pno
P1

As illustrated in the above example, self joins use correlation names to distinguish columns in the select list and where predicate. In this case, the references to the same table are renamed - a and b.

Self joins are often used in subqueries. See Subqueries below.

Subqueries

Subqueries are an identifying feature of SQL. It is called Structured Query Language because a query can nest inside another query.

There are 3 basic types of subqueries in SQL:

• Predicate Subqueries -- extended logical constructs in the WHERE (and HAVING) clause.
  
• Scalar Subqueries -- standalone queries that return a single value; they can be used anywhere a scalar value is used.
  
• Table Subqueries -- queries nested in the FROM clause.

All subqueries must be enclosed in parentheses.

Predicate Subqueries

Predicate subqueries are used in the WHERE (and HAVING) clause. Each is a special logical construct. Except for EXISTS, predicate subqueries must retrieve one column (in their select list.)

• IN Subquery
  

  The IN Subquery tests whether a scalar value matches the single query column value in any subquery result row. It has the following general format:
  

  value-1 [NOT] IN (query-1)

  Using NOT is equivalent to:
  

  NOT value-1 IN (query-1)

  For example, to list parts that have suppliers:
  

  SELECT *
  FROM p
  WHERE pno IN (SELECT pno FROM sp)

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  pno	descr	color
  P1	Widget	Blue
  P2	Widget	Red

  
  

  The Self Join example in the previous subsection can be expressed with an IN Subquery:
  

  SELECT DISTINCT pno
  FROM sp a
  WHERE pno IN (SELECT pno FROM sp b WHERE a.sno <> b.sno)

  
  
  
  
  
  
  
  
  

  pno
  P1

  
  

  Note that the subquery where clause references a column in the outer query (a.sno). This is known as an outer reference. Subqueries with outer references are sometimes known as correlated subqueries.
  

  
  
• Quantified Subqueries
  

  A quantified subquery allows several types of tests and can use the full set of comparison operators. It has the following general format:
  

  value-1 {=|>|<|>=|<=|<>} {ANY|ALL|SOME} (query-1)

  The comparison operator specifies how to compare value-1 to the single query column value from each subquery result row. The ANY, ALL, SOME specifiers give the type of match expected. ANY and SOME must match at least one row in the subquery. ALL must match all rows in the subquery, or the subquery must be empty (produce no rows).
  

  For example, to list all parts that have suppliers:
  

  SELECT *
  FROM p
  WHERE pno =ANY (SELECT pno FROM sp)

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  pno	descr	color
  P1	Widget	Blue
  P2	Widget	Red

  
  

  A self join is used to list the supplier with the highest quantity of each part (ignoring null quantities):
  

  SELECT *
  FROM sp a
  WHERE qty >ALL (SELECT qty FROM sp b
                  WHERE a.pno = b.pno 
                  AND a.sno <> b.sno
                  AND qty IS NOT NULL)

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  sno	pno	qty
  S3	P1	1000
  S3	P2	200

  
  
  
  
• EXISTS Subqueries
  

  The EXISTS Subquery tests whether a subquery retrieves at least one row, that is, whether a qualifying row exists. It has the following general format
  

  EXISTS(query-1)

  Any valid EXISTS subquery must contain an outer reference. It must be a correlated subquery.
  

  Note: the select list in the EXISTS subquery is not actually used in evaluating the EXISTS, so it can contain any valid select list (though * is normally used).
  

  To list parts that have suppliers:
  

  SELECT *
  FROM p
  WHERE EXISTS(SELECT * FROM sp WHERE p.pno = sp.pno)

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  pno	descr	color
  P1	Widget	Blue
  P2	Widget	Red

Scalar Subqueries

The Scalar Subquery can be used anywhere a value can be used. The subquery must reference just one column in the select list. It must also retrieve no more than one row.

When the subquery returns a single row, the value of the single select list column becomes the value of the Scalar Subquery. When the subquery returns no rows, a database null is used as the result of the subquery. Should the subquery retreive more than one row, it is a run-time error and aborts query execution.

A Scalar Subquery can appear as a scalar value in the select list and where predicate of an another query. The following query on the sp table uses a Scalar Subquery in the select list to retrieve the supplier city associated with the supplier number (sno column in sp):

SELECT pno, qty, (SELECT city FROM s WHERE s.sno = sp.sno)
FROM sp

pno	qty	city
P1	NULL	Paris
P1	200	London
P1	1000	Rome
P2	200	Rome

The next query on the sp table uses a Scalar Subquery in the where clause to match parts on the color associated with the part number (pno column in sp):

SELECT *
FROM sp
WHERE 'Blue' = (SELECT color FROM p WHERE p.pno = sp.pno)

sno	pno	qty
S1	P1	NULL
S2	P1	200
S3	P1	1000

Note that both example queries use outer references. This is normal in Scalar Subqueries. Often, Scalar Subqueries are Aggregate Queries.

Table Subqueries

Table Subqueries are queries used in the FROM clause, replacing a table name. Basically, the result set of the Table Subquery acts like a base table in the from list. Table Subqueries can have a correlation name in the from list. They can also be in outer joins.

The following two queries produce the same result:

SELECT p.*, qty
FROM p, sp
WHERE p.pno = sp.pno
AND sno = 'S3'

pno	descr	color	qty
P1	Widget	Blue	1000
P2	Widget	Red	200

SELECT p.*, qty
FROM p, (SELECT pno, qty FROM sp WHERE sno = 'S3')
WHERE p.pno = sp.pno

pno	descr	color	qty
P1	Widget	Blue	1000
P2	Widget	Red	200

Grouping Queries

A Grouping Query is a special type of query that groups and summarizes rows. It uses the GROUP BY Clause.

A Grouping Query groups rows based on common values in a set of grouping columns. Rows with the same values for the grouping columns are placed in distinct groups. Each group is treated as a single row in the query result.

Even though a group is treated as a single row, the underlying rows can be subject to summary operations known as Set Functions whose results can be included in the query. The optional HAVING Clause supports filtering for group rows in the same manner as the WHERE clause filters FROM rows.

For example, grouping the sp table on the pno column produces 2 groups:

sno	pno	qty
S1	P1	NULL	'P1' Group
S2	P1	200
S3	P1	1000
S3	P2	200	'P2' Group

• The P1 group contains 3 sp rows with pno='P1'
  
• The P2 group contains a single sp row with pno='P2'

Nulls get special treatment by GROUP BY. GROUP BY considers a null as distinct from every other null. Each row that has a null in one of its grouping columns forms a separate group.

Grouping the sp table on the qty column produces 3 groups:

sno	pno	qty
S1	P1	NULL	NULL Group
S2	P1	200	200 Group
S3	P2	200
S3	P1	1000	1000 Group

The row where qty is null forms a separate group.

GROUP BY Clause

GROUP BY is an optional clause in a query. It follows the WHERE clause or the FROM clause if the WHERE clause is missing. A query containing a GROUP BY clause is a Grouping Query. The GROUP BY clause has the following general format:

GROUP BY column-1 [, column-2] ...

column-1 and column-2 are the grouping columns. They must be names of columns from tables in the FROM clause; they can't be expressions.

GROUP BY operates on the rows from the FROM clause as filtered by the WHERE clause. It collects the rows into groups based on common values in the grouping columns. Except nulls, rows with the same set of values for the grouping columns are placed in the same group. If any grouping column for a row contains a null, the row is given its own group.

For example,

SELECT pno
FROM sp
GROUP BY pno

pno
P1
P2

In Grouping Queries, the select list can only contain grouping columns, plus literals, outer references and expression involving these elements. Non-grouping columns from the underlying FROM tables cannot be referenced directly. However, non-grouping columns can be used in the select list as arguments to Set Functions. Set Functions summarize columns from the underlying rows of a group.

Set Functions

Set Functions are special summarizing functions used with Grouping Queries and Aggregate Queries. They summarize columns from the underlying rows of a group or aggregate.

Using the Group By example from above, grouping the sp table on the pno column:

sno	pno	qty
S1	P1	NULL	'P1' Group
S2	P1	200
S3	P1	1000
S3	P2	200	'P2' Group

A Set Function can compute the total quantities for each group:

sno	pno	qty		qty total
S1	P1	NULL	'P1' Group	1200
S2	P1	200
S3	P1	1000
S3	P2	200	'P2' Group	200

Null columns are ignored in computing the summary. The Set Function -- SUM, computes the arithmetic sum of a numeric column in a set of grouped/aggregate rows. For example,

SELECT pno, SUM(qty)
FROM sp
GROUP BY pno

pno
P1	1200
P2	200

Set Functions have the following general format:

set-function ( [DISTINCT|ALL] column-1 )

set-function is:

• COUNT -- count of rows
  
• SUM -- arithmetic sum of numeric column
  
• AVG -- arithmetic average of numeric column; should be SUM()/COUNT().
  
• MIN -- minimum value found in column
  
• MAX -- maximum value found in column

The result of the COUNT function is always integer. The result of all other Set Functions is the same data type as the argument.

The Set Functions skip columns with nulls, summarizing non-null values. COUNT counts rows with non-null values, AVG averages non-null values, and so on. COUNT returns 0 when no non-null column values are found; the other functions return null when there are no values to summarize.

A Set Function argument can be a column or an scalar expression.

The DISTINCT and ALL specifiers are optional. ALL specifies that all non-null values are summarized; it is the default. DISTINCT specifies that distinct column values are summarized; duplicate values are skipped. Note: DISTINCT has no effect on MIN and MAX results.

COUNT also has an alternate format:

COUNT(*)

... which counts the underlying rows regardless of column contents.

Set Function examples:

SELECT pno, MIN(sno), MAX(qty), AVG(qty), COUNT(DISTINCT sno)
FROM sp
GROUP BY pno

pno
P1	S1	1000	600	3
P2	S3	200	200	1

SELECT sno, COUNT(*) parts
FROM sp
GROUP BY sno

sno	parts
S1	1
S2	1
S3	2

HAVING Clause

The HAVING Clause is associated with Grouping Queries and Aggregate Queries. It is optional in both cases. In Grouping Queries, it follows the GROUP BY clause. In Aggregate Queries, HAVING follows the WHERE clause or the FROM clause if the WHERE clause is missing.

The HAVING Clause has the following general format:

HAVING predicate

Like the WHERE Clause, HAVING filters the query result rows. WHERE filters the rows from the FROM clause. HAVING filters the grouped rows (from the GROUP BY clause) or the aggregate row (for Aggregate Queries).

predicate is a logical expression referencing grouped columns and set functions. It has the same restrictions as the select list for Grouping Queries and Aggregate Queries.

If the Having predicate evaluates to true for a grouped or aggregate row, the row is included in the query result, otherwise, the row is skipped (not included in the query result).

For example,

SELECT sno, COUNT(*) parts
FROM sp
GROUP BY sno
HAVING COUNT(*) > 1

sno	parts
S3	2

Aggregate Queries

An Aggregate Query can use Set Functions and a HAVING Clause. It is similar to a Grouping Query except there are no grouping columns. The underlying rows from the FROM and WHERE clauses are grouped into a single aggregate row. An Aggregate Query always returns a single row, except when the Having clause is used.

An Aggregate Query is a query containing Set Functions in the select list but no GROUP BY clause. The Set Functions operate on the columns of the underlying rows of the single aggregate row. Except for outer references, any columns used in the select list must be arguments to Set Functions. See Set Functions above.

An aggregate query may also have a Having clause. The Having clause filters the single aggregate row. If the Having predicate evaluates to true, the query result contains the aggregate row. Otherwise, the query result contains no rows. See HAVING Clause above.

For example,

SELECT COUNT(DISTINCT pno) number_parts, SUM(qty) total_parts
FROM sp

number_parts	total_parts
2	1400

Subqueries are often Aggregate Queries. For example, parts with suppliers:

SELECT *
FROM p
WHERE (SELECT COUNT(*) FROM sp WHERE sp.pno=p.pno) > 0

pno	descr	color
P1	Widget	Blue
P2	Widget	Red

Parts with multiple suppliers:

SELECT *
FROM p
WHERE (SELECT COUNT(DISTINCT sno) FROM sp WHERE sp.pno=p.pno) > 1

pno	descr	color
P1	Widget	Blue

Union Queries

The SQL UNION operator combines the results of two queries into a composite result. The component queries can be SELECT/FROM queries with optional WHERE/GROUP BY/HAVING clauses. The UNION operator has the following general format:

query-1 UNION [ALL] query-2

query-1 and query-2 are full query specifications. The UNION operator creates a new query result that includes rows from each component query.

By default, UNION eliminates duplicate rows in its composite results. The optional ALL specifier requests that duplicates be retained in the UNION result.

The component queries of a Union Query can also be Union Queries themselves. Parentheses are used for grouping queries.

The select lists from the component queries must be union-compatible. They must match in degree (number of columns). For Entry Level SQL92, the column descriptor (data type and precision, scale) for each corresponding column must match. The rules for Intermediate Level SQL92 are less restrictive. See Union-Compatible Queries.

Union-Compatible Queries

For Entry Level SQL92, each corresponding column of both queries must have the same column descriptor in order for two queries to be union-compatible. The rules are less restrictive for Intermediate Level SQL92. It supports automatic conversion within type categories. In general, the resulting data type will be the broader type. The corresponding columns need only be in the same data type category:

• Character (String) -- fixed/variable length
  
• Bit String -- fixed/variable length
  
• Exact Numeric (fixed point) -- integer/decimal
  
• Approximate Numeric (floating point) -- float/double
  
• Datetime -- sub-category must be the same,
  
  
  
  • Date
    
  • Time
    
  • Timestamp
  
  
• Interval -- sub-category must be the same,
  
  
  
  • Year-month
    
  • Day-time

UNION Examples

SELECT * FROM sp
UNION
SELECT CAST(' ' AS VARCHAR(5)), pno, CAST(0 AS INT)
FROM p
WHERE pno NOT IN (SELECT pno FROM sp)

sno	pno	qty
S1	P1	NULL
S2	P1	200
S3	P1	1000
S3	P2	200
	P3	0

SQL Modification Statements

The remaining SQL-Data Statements (SQL DML) are the SQL Modification Statements, described in the next sub-section:

• INSERT Statement -- add rows to tables
  
• UPDATE Statement -- modify columns in table rows
  
• DELETE Statement -- remove rows from tables

SQL-Data Statements

SQL Tutorial Main Page

---

Copyright © 2002-2005 FFE Software, Inc. All Rights Reserved WorldWide

Free Training on Wise Packaging

http://www.appdeploy.com/video/index.asp

 

AppDeploy Training Videos provide point and click instructions to describe and illustrate Windows Installer, application deployment and other desktop administration topics.

New AppDeploy Webinar Now Available!

The latest in a series of AppDeploy webinars is now available on-demand. The topic is "Windows Vista SP1 for Administrators" and it covers Windows Vista adoption, mitigation of key concerns, admin-related changes introduced, deployment options and more. Watch it now!

Windows Installer

Repackaging Best Practices
running time 21:45
This video presentation explains several best practices pertaining to repackaging, very helpful regardless of the software you use. More...

---

Dealing with InstallScript for Administrators
running time 18:30
Covers the issues surrounding InstallShield's InstallScript and how it can affect deployment scenarios along with three demos showing ways to deal with the problem. More....

---

Determining Transform (MST) File Contents
running time 10:29
Discusses Windows Installer Transforms (MST) and how you can determine the contents of a MST using popular tools like AdminStudio, Package Studio and ORCA. More....

---

Windows Installer AppSearch
running time 21:03
Covers AppSearch - including how to show the value of the AppSearch property in a Windows Installer Dialog, how to use it as a condition for a custom action and using it as a launch condition. More...

---

Creating Custom Actions With InstallShield AdminStudio
running time 16:01
This video presentation and demonstration provides a technical overview of Windows Installer custom actions and how they may be employed using InstallShield AdminStudio. More...

---

Creating Custom Actions With Wise Package Studio
running time 13:50
This video presentation and demonstration provides a technical overview of Windows Installer custom actions and how they may be employed using Wise Package Studio. More...

---

Repackaging With Wise Package Studio
running time 19:20
This video describes repackaging and walks you through the process of repackaging a sample application (WinZip v9) using Wise SetupCapture. More...

---

Troubleshooting the Windows Installer
running time 34:34
This video presentation discusses Windows Installer error codes, controlling logging and generating logs as well as reading and interpreting these logs. Includes two demonstrations of freeware SDK tool usage. More...

---

Creating A Clean Packaging Environment
running time 8:09
This video provides a step-by-step guide to creating a clean environment in which you may repackage application setups. More...

---

To Repackage or Not to Repackage
running time 37:12
This video presentation is a higher quality redelivery of the Webinar offered online in October of 2007. It covers the pros and cons of repackaging versus making use of any automation support offered by vendor provided legacy setups. More...

---

Packaging Adobe Reader
running time 9:54
This free video shows how to extract the Adobe Reader source files, create an installation point, create a customized MST with InstallShield Tuner and how you may build a command line installation string to take advantage of it all. More....

---

Desktop Deployment

Leveraging Preinstallation Environments
running time 44:52
This video presentation offers information about Windows PE and BartPE as Preinstallation environments that may be used to support the deployment of Windows, and also as a recovery and troubleshooting tool. Background as well as the pros and cons of each solution are discussed along with demonstrations of each.  More....

---

Intro to Windows PE v2.0
running time 16:10
This video discusses Windows PE, a new DOS replacement for Windows based on the Windows Vista kernel. The video discusses the benefits and limitations of this new tool and demonstrates how to customize your own Windows PE image using command line tools such as ImageX and PEImg. How to install a network card driver (VMware is used in the demo) into the mounted file-based image is covered as well as how to install packages such as MDAC, HTA, Scripting and XML. In addition to showing you how to build a Windows PE image, you are also shown how to create a bootable ISO image and how to prepare a USB memory drive as a source for your customized, bootable Windows PE image.  More....

---

Working with ImageX and Windows PE
running time 18:50
This walks the viewer through how to use ImageX and Windows PE to create an image of a Windows Longhorn Server, sysprep it, and apply it to a fresh system. The same steps also apply to imaging Windows Vista! The file-based images generated ImageX (WIM Files) are hardware independent and may be applied to differing hardware without concern for HAL incompatibilities associated with binary images. Finally, ImageX is a tool that may be used to create images of pre-Vista systems such as Windows XP as well which makes this both a very valuable and free tool worth getting familiar with.  More....

---

Remote Installation Services (RIS)
running time 25:54
This video shows how you may use RIS to deploy Windows XP SP2 from a Windows 2000 server in fully unattended mode. From RIS installation and configuration, to slipstreaming Windows XP SP2, customizing the SIF (unattended answer file) and even including additional scripts and source files to automate the installation of additional software following the installation of Windows XP. More....

---

Community Research in Deployment Process
running time 16:07
This video covers some different integration points for formalizing the inclusion of "community research" as a valuable step in your package development process. More....

1E Free Tools - SMS / ConfigMgr 2007

Program Clone Wizard

The Program Clone Wizard is an MMC snap-in for the Administrator Console. It extends the right mouse click menu for a single SMS/ConfigMgr Program allowing you to run a simple wizard which duplicates the Program and sets it to run using Nomad Enterprise.

Date: 16 Aug 2006   Size: 234 Kb

Program Clone Script

This download contains a vb script which allows you to make a copy of an existing SMS/ConfigMgr Program which is configured to use Nomad Enterprise. This automates the task of converting your existing SMS/ConfigMgr Programs to use Nomad Enterprise.

Date: 08 Aug 2006   Size: 8.62 Kb

LogfileViewer

Easily monitor and investigate multiple log files in a single consolidated view. This free tool allows you to compare log file entries from different files side-by-side. It supports the following features: multiple file sets; file and entry highlighting; date and time management and sorting and filtering.

Date: 24 Oct 2005   Size: 276 Kb

Program Pre-pend Script

This download contains a sample vb script which allows you to pre-pend your existing SMS/ConfigMgr Program command lines with the text "SMSNomad.exe". This automates the task of converting your existing SMS/ConfigMgr Programs to using Nomad Enterprise.

Date: 03 Sep 2005   Size: 7.26 Kb

SMSOrganiser

SMSOrganiser is a free tool from 1E for Microsoft SMS 2003 SP1. This handy tool enables you to easily drag and drop your SMS Packages into folders using a simple user interface. This product requires SMS 2003 SP1.

Date: 04 May 2005   Size: 46.7 Kb

ProgEditor

ProgEditor is a utility that can be used to edit SMS Programs command lines and attributes. The utility gives you a better view when the command line is very long.

Date: 06 Apr 2005   Size: 75.7 Kb

Client Master

A simple SMS 2.0 client installation method run from the SMS Central site using existing Network Discovery information. For Windows NT/2000.

Date: 02 Feb 2005   Size: 823 Kb

SMS Advert Success Rate Reporting

Generates ‘Snap-shot’ reports in Excel of the success rate of SMS Advertisement.

Date: 12 Jan 2005   Size: 484 Kb

User Coll

A command line SMS tool for creating a new user collection from a text file.

Date: 20 Aug 2004   Size: 78.7 Kb

SMSDependency

Tracing a chain of dependencies for programs in SMS can be a time consuming business. SMSDependency solves this usability issue by providing visibility of the entire tree of dependencies for a given program in one place.

Date: 21 Jul 2004   Size: 300 Kb

SMS Commander

SMSCommander is an extension for the SMS Administrator Console. It allows you to dynamically run command lines against SMS Resources. Setup these command lines in an ini file and use System Properties as input parameters.

Date: 10 Jun 2004   Size: 444 Kb

WMIPing

This is a simple tool for administrators to diagnose if connection to WMI of the target system can be established.

Date: 12 Sep 2003   Size: 65.9 Kb

EasyMSI

An extension to the SMS Administrator Console which enables easy deployment and maintenance of software to single machines using Windows Installer (MSI)

Date: 18 Aug 2003   Size: 508 Kb

User Info

A tool which provides a way of returning custom data to SMS. In this sample, the user's postcode and location are requested. This can be easily altered to return any custom information.

Date: 23 Jun 2003   Size: 6.14 Kb

Client Builder

A simple SMS 2.0 client Installation method run from the client. It is a command line tool which removes and re-installs SMS client components. It can be used to repair faulty client Installation or can be integrated easily into any unattended build or ghosting process. For Windows NT/2000.

Date: 17 Mar 2003   Size: 122 Kb

MSIMaster

MSIMaster enhances the SMS product by making Windows Installer product information available within the SMS database. This information adds to the standard Systems Management Server software inventory.

Date: 17 Mar 2003   Size: 208 Kb

SMS Program Wrapper

When deploying software when users may be logged in, SMS Program Wrapper displays a fully customisable dialog box prior to deployment, checks for open applications and provides customisable status MIF handling.

1E Free Tools for ConfigMgr 2007

ConfigMgr tools

Advanced Task Sequence Environment Tool

The TSEnv2.exe is a command line tool that lets administrators manipulate the Microsoft 2007 task sequence environment. It allows them to get, set, list and dump all variables within the OSD Task Sequence environment. This tool also allows changes to ‘read only’ variables that cannot be changed with the original TSEnv.exe which is included in the Configuration Manager 2007 OSD environment.

Date: 11 May 2009   Size: 108 Kb

1E Migration Tool

This tool is designed to allow an SMS Administrator to Migrate all or selected packages and programs from his SMS environment into a System Center Configuration Management (ConfigMgr) environment. The tool has the following features:-

• The Migration of Package and Program details along with source files
• Renaming of Package and Program names during migration
• Direct transfer of packages from your SMS site to ConfigMgr
• Export to flat file structure from SMS site and Import into ConfigMgr from flat file
• Exporting of SMSNomad command line settings to new ConfigMgr Nomad tab settings.

Date: 23 Feb 2009   Size: 107 Kb

Service Window

Provides a graphical interface that allows an administrative user to view, add, edit and delete maintenance windows (service windows) on a selected target computer. Maintenance windows define times during which Microsoft System Center Configuration Manager 2007(ConfigMgr) can apply advertisements and software update deployments to the specified computer.
If no windows are specified then the computer can receive advertisements at any time. Administrators may assign advertisements outside any maintenance windows irrespective of what has been set.

Date: 23 Dec 2008   Size: 110 Kb

WMI Permissions Tool

The WMIConfigPerms.exe is a command line to that lets you view and edit the security of a specified WMI Namespace. This tool will allow administrators to silently configure namespaces in preparation for software installs that require modified permissions to install. Examples are configuring permissions for the SMS Admins group on Root\SMS; 1E Shopping service account on Root\SMS; non-administrators on SQL Reporting Services; 1E NightWatchman Console service account on the N1E\WakeUp and SMSWAK namespaces.

Date: 12 Apr 2010   Size: 60.1 Kb

Access to SQL Query Build automatically

MMS 2011 SPONSORS & EXHIBITORS

MMS 2011 SPONSORS & EXHIBITORS

We would like to thank the following sponsors and exhibitors for their part in making MMS 2011 a success.

Table of Contents

Platinum Sponsors

Dell develops and delivers a comprehensive portfolio of IT services and solutions that reduce IT complexity and lower costs. We take a holistic view of each customer’s environment and business objectives to design solutions that help enable growth, streamline operations, and show a measurable return on investment. Our tested and proven solutions help meet specific business goals while mitigating risk. We offer interrelated consulting, business process, applications, infrastructure, and managed services that consistently deliver deep levels of value for our customers in commercial enterprises, healthcare, government, and education.
www.dell.com

---

As a world-leading information technology company, HP applies new thinking and ideas to create more simple, valuable and trusted experiences with technology. Our focus is to continuously improve the way our customers live and work through technology products and services, form the individual consumer to the largest enterprise. More information can be found at www.hp.com.
www.hp.com

Gold Sponsors

1E believes that all organizations should expect more from their IT. Founded in 1997, 1E pioneered advanced PC power management with the release of NightWatchman^® and WakeUp^™ as part of a unique range of industry-leading solutions designed to reduce IT costs. Headquartered in London and New York with nearly 14m licenses deployed worldwide, over 1100 public and private sector organizations in 42 countries enlist 1E to help them work more efficiently, productively and sustainably.  1E solutions have cumulatively saved  $530m+ in energy costs alone, cutting CO2 emissions by 4.3 million US tons. For more information, please visit: www.1e.com.
www.1e.com

---

Adaptiva^™, a Microsoft Gold Partner, and System Center Alliance member, makes sophisticated add-ons for Microsoft’s Configuration Manager product. Based on cutting edge technology, Adaptiva's OneSite, Client Health, SCCM Companion, and Green Planet products are deployed at leading Fortune 500 companies, federal and state government agencies, and Department of Defense customers. These products reduce infrastructure costs, increase desktop manageability, and empower administrators with robust Systems Management capabilities. Adaptiva has earned a legendary reputation for lightning-fast support, extreme scalability, and rich enterprise feature sets which have led to successful deployments on large 200,000+ node networks.
www.adaptiva.com

---

AppSense has been providing technology solutions to optimize the user experience and simplify desktop management for over ten years, and has developed the technology, experience and operational capacity to offer a solution to what has been to date the most challenging yet important aspect of the Microsoft desktop to manage - the user.  Whether using physical PC, virtual desktops, App-V delivered applications, client hypervisors, cloud-based infrastructures or combinations thereof, AppSense User Virtualization technology provides a seamless working experience to all users at all times. AppSense is a Microsoft Gold Partner and is the only provider of user personalization technology in global VDI solutions from IBM, Dell, HP and CSC.
www.appsense.com

---

Citrix Systems, Inc. (NASDAQ:CTXS) is a leading provider of virtual computing solutions that help companies deliver IT as an on-demand service. Founded in 1989, Citrix combines virtualization, networking, and cloud computing technologies into a full portfolio of products that enable virtual workstyles for users and virtual datacenters for IT.  More than 230,000 organizations worldwide rely on Citrix to help them build simpler and more cost-effective IT environments. Citrix partners with over 10,000 companies in more than 100 countries. Annual revenue in 2010 was $1.87 billion. Visit us at booth #315 and follow us at http://twitter.com/citrixmicrosoft.
www.citrix.com

---

EMC Corporation (NYSE: EMC) is the world’s leading developer and provider of information infrastructure technologies. We provide global customers—of all sizes and across all industries—the products, services, and solutions they need to manage data growth and get the most value from their information. EMC leads customers on the journey to the private cloud—a dramatically more efficient and flexible way to manage, deliver, and consume IT services for reduced costs and increased business agility. EMC is a Microsoft Gold Certified Partner with 15 certifications and recognized 20-time Partner of the Year winner.
www.emc.com

---

For over 40 years Intel has been bringing new technology to the marketplace. Now we are building on our historical strength in silicon innovation and global manufacturing capacity to create new products and technologies that help people live happier, more productive lives. It's not just about making technology faster, smarter and cheaper - it's about using that technology to make life better, richer, and more convenient for everyone. Intel provides resources, technologies, products and services that both IT professionals and developers need to create innovative products and industry-leading software solutions with enhanced business value. Additional information available at www.intelalliance.com/microsoft.
www.intelalliance.com/microsoft

---

NetApp creates innovative storage and data management solutions that deliver outstanding cost efficiency and accelerate performance breakthroughs. Our dedication to principles of simplicity, innovation, and customer success has made us one of the fastest-growing storage and data management providers today.  Customers around the world choose us for our “go beyond” approach and broad portfolio of solutions for server-to-storage virtualization, business applications, data protection, and more. Our solutions provide nonstop availability of critical business data and simplify business processes so you can deploy new capabilities with confidence and get to revenue faster than ever before. Discover our passion for helping companies around the world go further, faster at www.netapp.com.
www.netapp.com

---

RES Software, the proven leader in dynamic desktop solutions, is driving a transformation in the way organizations manage, maintain and reduce the cost of their desktop infrastructure. The RES Software award-winning, patented products enable IT professionals to manage and deliver secure, personalized and compliant desktops independent of the underlying computing infrastructure – thin clients, virtual desktops, physical desktops, or server-based computing environments. The company empowers customers, from small to medium-sized businesses to global enterprises, to reduce desktop complexity and meet the essential needs of a dynamic workforce that requires on-demand access to their personalized workspaces. For more information, follow updates on Twitter @RESSoftware and visit www.ressoftware.com.
www.ressoftware.com

---

SCCM Expert is a Microsoft Gold Partner and founding member of the Microsoft System Center Alliance. SCCM Expert’s Self Service deeply integrates with System Center Configuration Manager 2007^® enabling an automated software request and license management business process.

appSurvey, in Self Service, automates application migration work flow for large scale Windows 7 migrations. Administrators approve and trigger remote migration based on end-user surveys. We help global corporations, education institutions and government agencies:

• Boost productivity through on-demand application delivery
• Simplify Windows7 application migration
• Save IT time by automating software request management
• Reduce license spend through leasing and reclamation

www.sccmexpert.com

---

Veeam Software, 2010 Microsoft Partner of the Year finalist, develops innovative software for managing virtual datacenter environments. Veeam is an active member of the System Center Alliance with solutions complementing Microsoft Core Infrastructure Optimization offerings. Veeam’s PRO-enabled nworks Management Pack provides well-informed monitoring of VMware from within Operations Manager, and integrates with SCVMM to automate administrative and remedial actions. The Management Pack is verified as “VMware Ready” on vSphere4 and VI3, and it is part of Veeam ONE, which provides a single solution to optimize the performance, configuration and utilization of growing, mission-critical virtual infrastructures.
www.veeam.com

Bronze Sponsors

www.app-dna.com

---

Avanade business technology services connect insight, innovation and expertise in Microsoft^® technologies to help customers realize results. Avanade’s services and solutions help improve performance, productivity and sales for organizations in all industries. The company applies Microsoft expertise from its global network of consultants, who help deliver results faster, at lower cost and with less risk. Avanade, founded in 2000 by Accenture and Microsoft Corporation, has more than 11,000 dedicated resources in more than 24 countries.
www.avanade.com

---

Bay Dynamics, a leading analytics technology provider in the systems management and security space, brings business intelligence and cube-based analytics to System Center products. Bay Dynamics has created proven and highly adopted solutions that provide detailed insight into IT operations through trend discovery and powerful forensic analysis capabilities. This allows Bay Dynamics’ clients greater visibility into their environment to help them plan for the future. Through IT Analytics^™ for System Center, companies can now turn their vast amounts of data coming from System Center products into useful information that will help them identify problems, view trends, and make more informed and timely business decisions.
www.baydynamics.com

---

CommVault^® Simpana's^® Windows Centric Data Management software enables an organization to granularly and content search heterogeneous data. Meet with, see live demos and talk with CommVault technical experts about how one solution enables backup, recovery, archiving, compliance, deduplication, search, snapshot management, replication, SRM and reporting. For a preview, visit www.commvault.com/microsoft and learn how the Microsoft Office Products team benefitted from CommVault.
www.commvault.com

---

It can be complicated to manage PCs and their users with Microsoft System Center Configuration Manager (SCCM), and therefore daily operational tasks often need involvement from the SCCM Manager. With easy administration suite, which is a superstructure to SCCM, you will be able to let any IT employee carry out up to 90% of the daily SCCM tasks.
www.easyadm.com

---

Iron Mountain provides information management services that help businesses lower the costs, risks and inefficiencies of managing their physical and digital data. The company’s solutions enable customers to protect and better use their information — regardless of its format, location or lifecycle stage — so they can optimize their business and ensure proper recovery, compliance and discovery. Founded in 1951, Iron Mountain manages billions of information assets for businesses around the world. For more information, visit www.ironmountain.com
www.ironmountain.com

---

NetWrix Corporation provides systems management solutions for change auditing, regulatory compliance, identity management, security, endpoint management, health monitoring, virtualization, SharePoint, and others. The company offers simple and affordable products for a fraction of the price of similar competitive offerings, and matches its quality products with reliable customer service. Please visit the NetWrix booth on MMS to see how the award-winning NetWrix products can extend Microsoft System Center family and help you manage your IT infrastructure efficiently and sustain compliance.
www.netwrix.com

---

Odyssey Software is a leading provider of enterprise-class remote device management software products that make it efficient and cost effective for IT organizations to manage and support the complexity of remote (mobile and embedded) deployments utilizing today’s most popular devices, including Windows Phone 7. Our flagship product, Athena, is the only mobile device management software that seamlessly integrates into Configuration Manager 2007, providing vital functions for management and support of remote devices. 
www.odysseysoftware.com

---

The Provance^™ IT Asset Management Pack for Microsoft^® System Center Service Manager provides powerful IT Asset Life Cycle Management and Software Asset Management that drives down IT costs, increases service management efficiency, and reduces risk. Supporting ITIL^® and the Microsoft Operations Framework, Provance strengthens IT effectiveness of companies at every level of the Microsoft Core Infrastructure Optimization model. Provance is a Microsoft Gold Certified Partner with the Systems Management Competency, and System Center Alliance Member.
www.provance.com

---

Quest Software simplifies and reduces the cost of managing IT for more than 100,000 customers worldwide. Our innovative solutions make solving the toughest IT management problems easier, enabling customers to save time and money across physical, virtual and cloud environments.  For more information about Quest go to www.quest.com.
www.quest.com

---

Simplify the deployment and management of Microsoft System Center management packs, configuration packs and configuration compliance reporting with Silect’s MP Studio (for Operations Manager), CP Studio (for Configuration Manager/DCM) and ConfigWise. With Silect, you gain the expertise to quickly and easily customize and optimize key System Center components for enhanced reliability and faster ROI.  Learn how you can overcome complexity, speed implementation, minimize risk and increase productivity across your System Center environment by visiting www.silect.com.
www.silect.com

---

WinWorkers is an international IT corporation headquartered in Switzerland. As system integrator in the MS System Center environment WinWorkers focuses on corporate automation and deployment projects in Germany, Austria, Switzerland, the US and the UK. WinWorkers also develops software solutions in-house that are used and implemented world-wide. Since March 2010 WinWorkers is a member of the Microsoft System Center Alliance Group of. It is the first enterprise in the D-A-CH area to gain this award.
www.winworkers.com

---

Redmond magazine provides cutting-edge product, business and how-to information for network professionals, IT managers and IT directors working on the Microsoft Windows platform. Visit our web site for news, analysis, trends, product reviews, roadmaps and best practices. Our readers trust our independent stance on Microsoft, the Windows computing platform and third-party vendors. They come for product information, business strategies and behind-the-scenes insight so they can make better informed decisions for their IT infrastructures.
redmondmag.com

---

The Windows IT Pro community is the heartbeat of the Windows IT world – a gathering of people, content, and resources focused on Microsoft Windows technologies and applications. It's a community bringing an independent, uncensored voice to IT managers, network and systems administrators, developers, systems analysts, CIOs, CTOs, and other technologists at companies worldwide. The Windows IT Pro community is marked by its depth of editorial content that allows IT professionals to interact with colleagues, technical experts, analysts, and other thought-leaders.
www.windowsitpro.com

Patching Related End to end – Collections and reports :-

Below is for a compliance report based on SQL

declare @CollectID AS  varchar(8)
SET @CollectID= 'SMS0001'

declare @CollectionListID AS  varchar(90)
SET @CollectionListID='ScopeId_5432f432-F885-4A98-B666-5432134122/AuthList_F15C63EA-B655-4940-A250-654323fd432'

declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@CollectionListID

declare @CollCount int, @NumClients int; select @CollCount = count(*), @NumClients=isnull(sum(cast(IsClient as int)), 0)

from v_ClientCollectionMembers ccm where ccm.CollectionID=@CollectID

select
    CollectionName=vc.Name,
    'Update List'=al.Title,
    Status=sn.StateName,
    NumberOfComputers=count(*),
    PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif(@CollCount, 0), 1))),
    CollectionID=@CollectID,
    AuthListID=@CollectionListID
from v_Collection vc right join v_ClientCollectionMembers cm on vc.CollectionID=cm.CollectionID
join v_UpdateListStatus_Live cs on cs.CI_ID=@CI_ID and cs.ResourceID=cm.ResourceID
left join v_StateNames sn on sn.TopicType=300 and sn.StateID=isnull(cs.Status, 0)
left join v_AuthListInfo al on cs.CI_ID=al.CI_ID
where cm.CollectionID=@CollectID
group by vc.Name, sn.StateName, al.Title
order by sn.StateName

________________________________________________________________________________________________________________________
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
________________________________________________________________________________________________________________________
<<<<<<<---WQL based query----->>>>> systems will be automatically part of the collection with the name of "2011 Jan Updates" Deployment

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System  WHERE        SMS_R_System.ResourceID IN (     SELECT        SMS_UpdateComplianceStatus.MachineID        FROM         SMS_UpdateComplianceStatus        JOIN SMS_UpdateDeploymentSummary           ON    SMS_UpdateComplianceStatus.CI_ID = SMS_UpdateDeploymentSummary.CI_ID     WHERE           SMS_UpdateComplianceStatus.Status = "2"        AND SMS_UpdateDeploymentSummary.AssignmentName    = "2011 Jan Updates")

______________________________________________________________________________________________________________________________
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
______________________________________________________________________________________________________________________________

Patching Related End to end – Collections and reports :-

Public SCCM 2007 Right Click Tools : TWO different versions available

One from Dan Ireland

http://myitforum.com/cs2/blogs/direland/pages/sccm-console-extensions.aspx

Second One from Rick Houchins

 

http://myitforum.com/cs2/blogs/rhouchins/0401ConfigMgrTools.zip

AutoCAD Mof File for all versions

:::::::::::::::SMS_def.mof file:::::::::::::::

#pragma namespace ("\\\\.\\root\\cimv2\\sms")

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 14"),
SMS_Class_ID ("CUSTOM|AUTOCAD_14|1.0") ]
class Win32Reg_AutoCAD_14 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2000"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2000|1.0") ]
class Win32Reg_AutoCAD_2000 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2000i"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2000i|1.0") ]
class Win32Reg_AutoCAD_2000i : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2002"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2002|1.0") ]
class Win32Reg_AutoCAD_2002 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2004"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2004|1.0") ]
class Win32Reg_AutoCAD_2004 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2005"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2005|1.0") ]
class Win32Reg_AutoCAD_2005 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2006"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2006|1.0") ]
class Win32Reg_AutoCAD_2006 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2007"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2007|1.0") ]
class Win32Reg_AutoCAD_2007 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2008"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2008|1.0") ]
class Win32Reg_AutoCAD_2008 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2009"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2009|1.0") ]
class Win32Reg_AutoCAD_2009 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2010"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2010|1.0") ]
class Win32Reg_AutoCAD_2010 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD 2011"),
SMS_Class_ID ("CUSTOM|AUTOCAD_2011|1.0") ]
class Win32Reg_AutoCAD_2011 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[SMS_Report(TRUE),SMS_Group_Name("AutoCAD 2012"),SMS_Class_ID("Custom|Autocad_2012|1.0"),
SMS_Context_1("__ProviderArchitecture=32|uint32"),
SMS_Context_2("__RequiredArchitecture=true|boolean")]
Class Win32Reg_Autocad_2012: SMS_Class_Template
{
[SMS_Report(TRUE),key] string KeyName;
[SMS_Report(TRUE)] String ProductName;
[SMS_Report(TRUE)] String Release;
[SMS_Report(TRUE)] String SerialNumber;
[SMS_Report(TRUE)] String StandaloneNetworkType;
};

[SMS_Report(TRUE),SMS_Group_Name("AutoCAD 2012"),SMS_Class_ID("Custom|Autocad_2012|1.0"),
SMS_Context_1("__ProviderArchitecture=64|uint32"),
SMS_Context_2("__RequiredArchitecture=true|boolean")]
Class Win32Reg_Autocad_2012_64 : SMS_Class_Template
{
[SMS_Report(TRUE),key] string KeyName;
[SMS_Report(TRUE)] String ProductName;
[SMS_Report(TRUE)] String Release;
[SMS_Report(TRUE)] String SerialNumber;
[SMS_Report(TRUE)] String StandaloneNetworkType;
};

//***************************************
//* AutoCAD LT Starts Here
//***************************************

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 97"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_97|1.0") ]
class Win32Reg_AutoCAD_LT_97 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2000"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2000|1.0") ]
class Win32Reg_AutoCAD_LT_2000 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2000i"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2000i|1.0") ]
class Win32Reg_AutoCAD_LT_2000i : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2002"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2002|1.0") ]
class Win32Reg_AutoCAD_LT_2002 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2004"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2004|1.0") ]
class Win32Reg_AutoCAD_LT_2004 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2005"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2005|1.0") ]
class Win32Reg_AutoCAD_LT_2005 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2006"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2006|1.0") ]
class Win32Reg_AutoCAD_LT_2006 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2007"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2007|1.0") ]
class Win32Reg_AutoCAD_LT_2007 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2008"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2008|1.0") ]
class Win32Reg_AutoCAD_LT_2008 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2009"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2009|1.0") ]
class Win32Reg_AutoCAD_LT_2009 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2010"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2010|1.0") ]
class Win32Reg_AutoCAD_LT_2010 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("AutoCAD LT 2011"),
SMS_Class_ID ("CUSTOM|AUTOCAD_LT_2011|1.0") ]
class Win32Reg_AutoCAD_LT_2011 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

//***************************************
//* Inventor Starts Here
//***************************************

[ SMS_Report (TRUE),
SMS_Group_Name ("Inventor"),
SMS_Class_ID ("CUSTOM|AUTODESK_INVENTOR|1.0") ]
class Win32Reg_Autodesk_Inventor : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

//***************************************
//* 3dsmax Starts Here
//***************************************

[ SMS_Report (TRUE),
SMS_Group_Name ("3dsmax 9"),
SMS_Class_ID ("CUSTOM|3dsmax_9|1.0") ]
class Win32Reg_3dsmax_9 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("3dsmax 13"),
SMS_Class_ID ("CUSTOM|3dsmax_13|1.0") ]
class Win32Reg_3dsmax_13 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

[ SMS_Report (TRUE),
SMS_Group_Name ("3dsmax 14"),
SMS_Class_ID ("CUSTOM|3dsmax_14|1.0") ]
class Win32Reg_3dsmax_14 : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Release;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string StandaloneNetworkType;
};

//********************
//Algor Starts here
//**********************

[ SMS_Report (TRUE),
SMS_Group_Name ("Algor"),
SMS_Class_ID ("CUSTOM|Algor_All|1.0") ]
class Win32Reg_Algor : SMS_Class_Template
{
    [SMS_Report (TRUE), Key ] string KeyName;
    [SMS_Report (TRUE)] string ProductName;
    [SMS_Report (TRUE)] string Version;
    [SMS_Report (TRUE)] string SerialNumber;
    [SMS_Report (TRUE)] string InstallationType;
};

:::::Configuration.mof file:::::

 

#pragma namespace("\\\\.\\root\\cimv2")

//***************************************
//* AutoCAD Starts Here
//***************************************

// AutoCAD 14
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R14.0")
]

class Win32Reg_AutoCAD_14
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

// AutoCAD 2000
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R15.0")
]

class Win32Reg_AutoCAD_2000
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

// AutoCAD 2000i
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R15.1")
]

class Win32Reg_AutoCAD_2000i
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

// AutoCAD (including Mechanical) 2002
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R15.2")
]

class Win32Reg_AutoCAD_2002
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Mechanical) 2004
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R16.0")
]

class Win32Reg_AutoCAD_2004
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Electrical & Mechanical) 2005
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R16.1")
]

class Win32Reg_AutoCAD_2005
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Electrical & Mechanical) 2006
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R16.2")
]

class Win32Reg_AutoCAD_2006
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Electrical & Mechanical) 2007
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R17.0")
]

class Win32Reg_AutoCAD_2007
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Electrical & Mechanical) 2008
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R17.1")
]

class Win32Reg_AutoCAD_2008
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Electrical & Mechanical) 2009
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R17.2")
]

class Win32Reg_AutoCAD_2009
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Electrical & Mechanical) 2010
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R18.0")
]

class Win32Reg_AutoCAD_2010
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Electrical & Mechanical) 2011
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R18.1")
]

class Win32Reg_AutoCAD_2011
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD (including Electrical & Mechanical) 2012

[dynamic, provider("RegProv"), ClassContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R18.2")]
Class Win32Reg_Autocad_2012
{
[key] string KeyName;
[PropertyContext("ProductName")] String ProductName;
[PropertyContext("Release")] String Release;
[PropertyContext("SerialNumber")] String SerialNumber;
[PropertyContext("StandaloneNetworkType")] String StandaloneNetworkType;
};

#pragma namespace ("\\\\.\\root\\cimv2")
//#pragma deleteclass("Win32Reg_Autocad_2012_64", NOFAIL)
[dynamic, provider("RegProv"), ClassContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD\\R18.2")]
Class Win32Reg_Autocad_2012_64
{
[key] string KeyName;
[PropertyContext("ProductName")] String ProductName;
[PropertyContext("Release")] String Release;
[PropertyContext("SerialNumber")] String SerialNumber;
[PropertyContext("StandaloneNetworkType")] String StandaloneNetworkType;
};

//***************************************
//* AutoCAD LT Starts Here
//***************************************

//AutoCAD LT 97
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R4")
]

class Win32Reg_AutoCAD_LT_97
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2000
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R6")
]

class Win32Reg_AutoCAD_LT_2000
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2000i
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R7")
]

class Win32Reg_AutoCAD_LT_2000i
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2002
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R8")
]

class Win32Reg_AutoCAD_LT_2002
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2004
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R9")
]

class Win32Reg_AutoCAD_LT_2004
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2005
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R10")
]

class Win32Reg_AutoCAD_LT_2005
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2006
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R11")
]

class Win32Reg_AutoCAD_LT_2006
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2007
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R12")
]

class Win32Reg_AutoCAD_LT_2007
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2008
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R13")
]

class Win32Reg_AutoCAD_LT_2008
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2009
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R14")
]

class Win32Reg_AutoCAD_LT_2009
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2010
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R15")
]

class Win32Reg_AutoCAD_LT_2010
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//AutoCAD LT 2011
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\AutoCAD LT\\R16")
]

class Win32Reg_AutoCAD_LT_2011
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//***************************************
//* Inventor Starts Here
//***************************************

//All Inventor
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\Inventor")
]

class Win32Reg_Autodesk_Inventor
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//***************************************
//* 3dsmax Starts Here
//***************************************

//3dsmax 9
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\3dsmax\\9.0")
]

class Win32Reg_3dsmax_9
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//3dsmax 13
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\3dsmax\\13.0")
]

class Win32Reg_3dsmax_13
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//3dsmax 14
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Autodesk\\3dsmax\\14.0")
]

class Win32Reg_3dsmax_14
{
    [Key] string KeyName;
    [PropertyContext("ProductName")] string ProductName;
    [PropertyContext("Release")] string Release;
    [PropertyContext("SerialNumber")] string SerialNumber;
    [PropertyContext("StandaloneNetworkType")] string StandaloneNetworkType;
};

//***************************************
//* Algor Starts Here
//***************************************
[ dynamic,
provider("RegProv"),
ClassContext("local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Algor, Inc.\\Algor Software\\Algor")
]

class Win32Reg_Algor
{
    [Key] string KeyName;
    [PropertyContext("Product Name")] string ProductName;
    [PropertyContext("Version")] string Version;
    [PropertyContext("Serial Number")] string SerialNumber;
    [PropertyContext("Installation Type")] string InstallationType;
};

//Total Auto Desk END******************************************************************************