HCL Learning, the education division of HCL Infosystems, has signed a memorandum of understanding (MoU) with a South African educational firm Spatial Data Holdings, to launch their products in five African countries including South Africa. Through this agreement the company has already started selling its educational content , tablets anddongles in Africa. The countries are South Africa, Zimbabwe, Zambia, Botswana and Namibia.

"We are mapping content to African educational boards and also modifying them to adapt to dialects and accents prevailing in these countries. The focus of content is primarily on English, Maths and Science," said Rothin Bhattacharyya, EVP Marketing , Strategy and Corporate Development at HCL Infosystems.

While the first wave of institutions driving this initiative in Africa are primarily non-governmental organisations (NGOs), governments are soon expected to step in. "Right now, NGOs and relatively expensive schools are taking up such solutions in Africa. We expect government buying to become big next year," said Bhattacharyya.

At the same time, government laptop buying remains a big source of growth in India. "We will be finishing this week our shipping requirements for the Tamil Nadu project worth about Rs 270 crore. In UP, we have been shortlisted for laptop project and have also applied for their tablet initiative. Governments in Punjab, Goa and Assam, among others, also have similar initiatives declared in their manifestoes," said Bhattacharyya.

HCL Learning, started three years ago, had so far been selling its products mainly in a business-to-business (B2B) fashion but now the company is going business-to-consumer (B2C). It plans to sell its tablets and dongles through more than 600 outlets nationwide in the coming few weeks.

"Introducing dongles allows us to reach on all the 50-60 million computing devices present in Indian market. We will be selling our content and services through retail channel as well as through our longstanding relationships with schools," said Bhatacharyya.

Keep misplacing your car keys? Just wave your smartphone to unlock the door!
Engineers at South Korean automobile majorHyundai have invented a new system that will allow your smartphone to double as your car keys, and the technology will be made available to buyers within two years.

Rather than using Bluetooth, the system by Hyundai uses wireless Near Field Communication ( NFC), allowing you to lock and unlock the car by waving your phone over a small tag on the car window.

Inside the car, you place the phone on a pad in the centre console that wirelessly charges it while the content is synced and streamed to the car's infotainment system and touchscreen.

The system can also store in-car preferences, including radio stations, seating positions and even mirror adjustment - with multiple profiles able to be saved for different drivers.

The system was demonstrated on a concept version of Hyundai's popular i30 in Germany.

Hyundai said developing the system was part of the carmaker's aim of producing technology for the mainstream consumer, website carsguide.com.au reported.

"The Connectivity Concept showcases Hyundai's philosophy of making tomorrow's technology accessible to a wide range of customers," Hyundai Motor Europe chief operating officer Allan Rushforth said.

"With this technology, Hyundai is able to harness the all-in-one functionality of existing smartphone technology and integrating it into everyday driving in a seamless fashion," Rushforth said.

           An internet virus attacked computers at industrial sites in southern Iran, in an apparent extension of a covert cyber war that initially targeted the country's nuclear facilities, an Iranian official said.

Iran, the world's No. 5 oil exporter, has tightened online security since its uranium enrichment centrifuges were hit in 2010 by the Stuxnet computer worm, which Tehran believes was planted by arch-adversaries Israel or the United States.

The unit tasked with fighting cyber attacks, the Passive Defence Organisation, said a virus had infected several sites in Hormozgan province in recent months but was neutralised.

"Enemies are constantly attacking Iran's industrial units through Internet networks in order to create disruptions," Ali Akbar Akhavan, head of the Hormozgan branch of the organisation, was quoted as saying by the Iranian Students' News Agency on Tuesday.

"This virus has even penetrated some manufacturing industries in Hormozgan province, but with timely measures and the cooperation of skilled hackers in the province, the progress of this virus was halted," Akhavan said.

"As an example, the Bandar Abbas Tavanir Co., a producer of electricity in the province and even adjacent provinces, has been the target of Internet attacks in recent months," he said.

Bandar Abbas is the capital of Hormozgan province on Iran's southern coast and home to an oil refinery and container port.

Israeli officials have threatened military action against Iranian nuclear facilities if Western sanctions on Tehran's banking and oil sectors do not persuade the Islamic Republic to shelve its disputed atomic programme.

Western powers suspect Iran is trying to develop the means to produce nuclear weapons. Tehran says it is enriching uranium only for civilian energy.

Iranian authorities said in April that a computer virus was detected inside the control systems of Kharg Island - which handles the vast majority of Iran's crude oil exports - but the terminal had remained operational.

Cyber attackers also slowed Iran's Internet and attacked its offshore oil and gas platforms this year, Iranian officials have said.

This is not a blog post for the IT professional, unless you have little idea how anti-malware, reputation, etc. work.  It is a small primer for those who don’t quite get all this anti-malware stuff.At its core anti-malware seems pretty straightforward.  You take something that can be run (usually an “image” or “executable”, but for sake of the audience we’ll just say App) and run a mathematical algorithm over it that produces an (almost) unique identifier for that App.  Generally that unique identifier is called a Hash (because of the technique used).  Anytime you run the same algorithm over the same App you get the same Hash.  Two different Apps with same name will have different Hashes. 

 Two Apps that are identical but have different names will get the same Hash.  If someone modifies an app, even slightly, it will have a different Hash.  So the Hash tells you exactly what you have.Anti-Malware software simply computes the Hash for an App and compares it to a “Black List” of Hashes that have been deemed Malware.  Those updates your anti-Malware software does all the time are to update the Black List of Hashes (or Signatures) of known Malware.   While this technique was sufficient in the early (particularly pre-Internet) days, today the Malware can spread rapidly enough to be endemic before your Anti-Malware vendor adds its Hash to their Black List.  More recently Malware authors have taken to constant updating of the App with slight changes so that each copy has a different Hash, defeating the simple Black List mechanism.

With Black Lists of  shrinking effectiveness Anti-Malware has grown to incorporate more sophisticated techniques for detecting Malware.  The problem with these techniques is that they significantly slow down App execution, so another mechanism was needed to minimize their use.  That is the addition of a White List of App Hashes that are known to be safe to run.  Let’s say you go to run Microsoft Excel.  The White List will contain the Hash of Excel’s main executable, excel.exe, as known to have been distributed by Microsoft.  Anti-Malware just checks the Hash and lets Excel run with no further interference.  If the Hash doesn’t match then either excel.exe has been modified or some other App has an executable called excel.exe.  Either way, it won’t be mistaken for the real Microsoft Excel.

Most Apps you run will either be on the White List or Black List and thus Anti-Malware software either lets them run without further interference or blocks them.  But what happens when an App is not on either list?  That’s when the App will be deeply evaluated either before, or while, it is running.The specific techniques for evaluating unknown Apps are beyond this blog posting but basically cover two broad areas.  The first, known as Generic Signatures, is for the Anti-Malware software to scan the image for known indicators that this is a particular kind of Malware.  

This helps defend against the case where Malware authors simply keep issuing very slight modifications of their Apps as a way to avoid the Black List.  When a new example of the Malware App is found its Hash is sent to the Anti-Malware vendor so it can be added to the Black List (and thus blocked with less performance impact).The second set of techniques are more dynamic, monitoring an App for suspicious behavior.  If suspicion that the App is Malware is high then execution is blocked, but if it is only slightly suspicious then it is allowed to proceed.  Information about the App may then be sent to the Anti-Malware vendor for analysis and a final determination on if it should be added to the Black List or White List.

Since traditionally unidentified Apps that have either no or only mildly suspicious behaviors are allowed to run it is possible for Malware to slip through these evaluation techniques.  To combat this more complete “Reputation” systems are being incorporated into Anti-Malware software as well as Operating Systems.  With Reputation, an App is onlyallowed to run if it is on the White List.  Unknown applications are completely blocked.  The problem with this approach in the past has been that many legitimate applications would be blocked, making systems almost unusable.  Recent advances have made Reputation far more practical, although in rare cases blocking of legitimate Apps will still occur.

Modern Reputation-based systems are more pro-active in adding Apps to the White List.  In particular they can look at the (verifiable) identity of the author of the App and, if they have a reputation for producing safe apps, automatically add any new (or legitimately changed) App to the White List.  Apps that engage in suspicious or clearly harmful behavior can be removed from the White List, blocking their execution.  This will also “damage” the reputation of the App’s author, making it more difficult for their Apps to be added to the White List.  Apps obtained from a tightly controlled “Store” like the Windows Store or Apple’s App Store receive the most direct reputation verification and are always on the White List (unless pulled from the Store). 

 By changing the focus of Anti-Malware from the Black List to the White List Reputation-based systems protect against Malware even before it is identified as such.Other techniques contribute to fighting Malware as well.  URL Filtering uses a Black List/White List technique to keep users from navigating to web sites that are known to distribute Malware or have done so in the past.  Email Filtering removes Malware-infested images from mail, and keeps SPAM (many of whose links take you to Malware distributing websites) out of your Inbox.  Firewalls help block Malware from communicating to the outside world.That’s the simple tutorial.  Hopefully one of the things it does is explain why Reputation is so important.  It’s really the only technique that gets ahead of the Malware authors.

Problem
Every once in awhile a table gets created without a primary key and duplicate records get entered.  The problem gets even worse when you have two identical rows in the table and there is no way to distinguish between the two rows.  So how do you delete the duplicate record?

Solution
One option that SQL Server gives you is the ability to set ROWCOUNT which limits the numbers of records affected by a command.  The default value is 0 which means all records, but this value can be set prior to running a command.  So let's create a table and add 4 records with one duplicate record.

Create a table called duplicateTest and add 4 records.

CREATE TABLE dbo.duplicateTest 
( 
[ID] [int] , 
[FirstName] [varchar](25), 
[LastName] [varchar](25)  
) ON [PRIMARY] 

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

If we select all data we get the following:

SELECT * FROM dbo.duplicateTest

ID	FirstName	LastName
1	Bob	Smith
2	Dave	Jones
3	Karen	White
1	Bob	Smith

If we try to select the record for Bob Smith will all of the available values such as the following query:

SELECT * FROM dbo.duplicateTest WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'

We still get 2 rows of data:

ID	FirstName	LastName
1	Bob	Smith
1	Bob	Smith

So to delete the duplicate record with SQL Server 2000 and 2005 we can use the SET ROWCOUNT command to limit the number of rows affected by a query.  By setting it to 1 we can just delete one of these rows in the table.  Note: the select commands are just used to show the data prior and after the delete occurs.

SELECT * FROM dbo.duplicateTest 

SET ROWCOUNT 1 
DELETE FROM dbo.duplicateTest WHERE ID = 1 
SET ROWCOUNT 0 

SELECT * FROM dbo.duplicateTest

With SQL Server 2005 we can also use the TOP command when we issue the delete, such as the following. Note: the select commands are just used to show the data prior and after the delete occurs.

SELECT * FROM dbo.duplicateTest 
DELETE TOP(1) FROM dbo.duplicateTest WHERE ID = 1 
SELECT * FROM dbo.duplicateTest

So as you can see with SQL Server 2005 there are two options to allow you to delete duplicate identical rows of data in your tables.

Here is one note from Microsoft about using SET ROWCOUNT:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

About a year ago, I wrote out some principles for web programming in PHP. I called it the MicroPHP Manifesto. The thing is, what I talked about wasn’t really specific to PHP. So, I’ve decided to explore the concepts again in the context of the other languages I work with.

What follows are some principles I try to keep in mind.

Learn languages, not frameworks

I like PHP, Python, and JavaScript, and I like making things in PHP, Python, and JavaScript. I’m not a Symfony developer, or a Django developer, or a jQuery developer.

I think this is an important distinction. It’s entirely possible to be a jQuery developer, but not a JavaScript developer. It’s possible to be a Django developer, but not a Python developer. Those are all certainly valuable and useful tools, but if I only know how to use one framework, my options for using the right tool for the job get pretty limited, and in my experience, large, full-stack frameworks are often not the right tool, particularly if flexibilty and performance are major concerns.

My experience has been that I become a better, more versatile developer when I focus on learning a language first. Diving head-first into a full-stack, complex framework when I’m starting out has allowed me to build finished products more quickly, but it also becomes a detriment when I need solutions outside of the framework’s scope. I often end up with a “plug and pray” approach to development, where I find a library or plugin that sounds like it will meet my needs, cross my fingers, and shove it in. That might get my app launched faster, but it makes stuff a lot harder down the road.

Additionally, learning a full-stack framework can be as complex as learning a new programming language. They often have complex architectures and nomenclatures — parts that don’t carry over to other frameworks and tools. I’d rather spend my time learning more about the language itself, knowing the skills that I learn will apply to anything I build in the language, no matter which libraries I use.

Build small things

Small units of code are good. The smaller it is, the easier it is to understand. It’s harder to screw it up — and I screw stuff up a lot, so limiting that is really important.

So, I strive to build small modules of code with a single purpose, or at most a few closely-related purposes. They should be self-contained pieces that solve individual problems. These pieces then work together to solve larger, more complex problems.

With simpler, modular code, fixing bugs is easier, because I can look at an individual piece and see clearly what it’s doing. And, if the modules are self-contained, testing my code is much easier.

Less code is better than more

To paraphrase Biggie Smalls, “more code, more problems.”

I want to manage less code. Larger codebases get harder to manage. Searching across the codebase takes longer. Navigating through complex file structures takes longer. Tracing execution through dozens of files is hard. Keeping it all straight in my brain gets challenging.

Bigger libraries and longer code seem to overflow my brain buffer. I have trouble keeping track of code flow when the source gets too long, or when execution is jumping between several source files, and visual noise really affects me, too. This is why I love syntax coloring so much, and consistent whitespace really helps. I use the code folding feature in my editor a lot, just to hide things, so I can focus better on the task at hand.

I also want to support less code. I’m responsible for all of the code that my app uses, not just the stuff I wrote — every single line of it. This means that bugs and security holes are my responsibility. How often do we see a WordPress or Drupal plugin get abandoned after a year or so? Am I sure I want to use a piece of code I don’t really understand, when a year down the road I may have to fix an exploit in it?

This is not to say that I would never use code that I didn’t write — I’d be hard-pressed to get much done that way, and frankly there are better programmers out there than me — but every line of code in my app matters, so I need to justify each one.

Create and use simple, readable code

I want code that is easy to understand. Understanding things quickly means getting stuff done faster. My time to productivity is shortened. My time to fix bugs is shortened.

I also want code that is easy to verify. My code should be testable, whether or not I get around to actually writing those tests, and I’ve consistently found that simpler, more modular code is more testable.

Readability should be a feature. Code should be simple and terse, but clear. Value clarity over cleverness. When I’m writing code, I try to consider how quickly another developer will be able to understand it at first glance. Alternately, will I be able to understand it when I come back to it in two months? The less time I waste trying to figure out how things work, the more there will be for getting things done.

I’d be lying if I said I always follow these principles. Sometimes, I just get lazy. Sometimes, time constraints mean I write hacky, complex code as fast as possible to get something working, or I pull in a library without reviewing it carefully, and hope it works. In the short term, writing simple, clear code is harder — it requires more discipline and frequent reassessment of technique. Particularly with time-sensitive projects, it’s hard to ever feel like I ever am entirely happy with the results.

But, when I make the time and put the effort in, it always pays off down the road — not just for myself, but for the other members of my team and people who use the code I’ve written, and that’s a really good feeling.

SOURCE LINK::http://webadvent.org/2012/more-code-more-problems-by-ed-finkler

1. Functions Introduction

Functions may be used within all expressions except aggregate functions that may only be used within the SELECT .... FROM clause.

2. Mathematical Functions

Returns the absolute value of a number.

Examples:

     SELECT ABS(-0.94)
==>  0.94

     SELECT ABS(9 - 200)
==>  191

SIGN(number)

Returns 1 if the number is positive, -1 if the number is negative and 0 if the number is zero.

Examples:

     SELECT SIGN(40)
==>  1

     SELECT SIGN(-40)
==>  -1

     SELECT SIGN(40 - 40)
==>  0

MOD(number1, number2)

Returns the modulo of number1 and number2 (equivalent to {number1 % number2} in Java).

Examples:

     SELECT MOD(15, 5)
==>  0

     SELECT MOD(33, 10)
==>  3

     SELECT ROUND(552 / 10), MOD(552, 10)
==>  55, 2

ROUND(number, decimal_places)
ROUND(number)

Rounds the number to 'n' decimal places. When no 'decimal_places' argument is provided the number is rounded to the nearest whole number.
This will round up if the fraction to the right is >= .5 otherwise it rounds down. This uses the {BigDecimal.setScale(decimal_places, BigDecimal.ROUND_HALF_UP)} method for rounding.

Examples:

     SELECT ROUND((943 * 13) / 99, 3)
==>  123.828

     SELECT ROUND((943 * 13) / 99, 2)
==>  123.83

     SELECT ROUND((943 * 13) / 99)
==>  124

POW(number1, number2)

Raises number1 to the power of number2.

Examples:

     SELECT POW(9, 6)
==>  531441

     SELECT POW(2, 32)
==>  4294967296

     SELECT POW(2, 64)
==>  18446744073709551616

     SELECT POW(2, -3)
==>  0.125

SQRT(number)

Finds the square root of the number argument.

Examples:

     SELECT SQRT(65536)
==>  256

     SELECT SQRT(-1)
==>  NULL

LEAST(val1, val2, ...)

This function accepts any number of arguments and returns the value that represents the least value of the set.

Examples:

     SELECT LEAST(4)
==>  4

     SELECT LEAST(90, 9.125, 3, 75)
==>  3

     SELECT LEAST('H', 'Z', 'B')
==>  B

     SELECT LEAST(10 / 3, 10 * 3,
                  POW(10, 3), MOD(10, 3))
==>  1

GREATEST(val1, val2, ...)

This function accepts any number of arguments and returns the value that represents the greatest value of the set.

Examples:

     SELECT GREATEST(4)
==>  4

     SELECT GREATEST(90, 9.125, 3, 75)
==>  90

     SELECT GREATEST('H', 'Z', 'B')
==>  Z

     SELECT GREATEST(10 / 3, 10 * 3,
                     POW(10, 3), MOD(10, 3))
==>  1000

3. String Functions

LOWER(str)

Returns a lower case version of the string literal argument.

Examples:

     SELECT LOWER('THis is sOME TEXT')
==>  this is some text

UPPER(str)

Returns an upper case version of the string literal argument.

Examples:

     SELECT UPPER('THis is sOME TEXT')
==>  THIS IS SOME TEXT

CONCAT(str1, str2, ...)

Returns the concatenation of the string arguments. This function can take any number of arguments.

Examples:

     SELECT CONCAT('This i', 's some text', '.')
==>  This is some text.

     SELECT CONCAT('-', 0.95)
==>  -0.95

LENGTH(str)

Returns the number of characters in the string argument.
NOTE: This may additionally be used on BLOB data to return the count of bytes in the BLOB.

Examples:

     SELECT LENGTH('This is some text')
==>  17

     SELECT LENGTH(0.544)
==>  5

     SELECT LENGTH('    Test')
==>  8

TRIM( [ [ LEADING | TRAILING | BOTH ] [ characters ] FROM ] str )
LTRIM(str)
RTRIM(str)

Trims characters from a string argument. The LTRIM and RTRIM form trim whitespace from the left and right of the string respectively.

Examples:

     SELECT TRIM(TRAILING 'a' FROM 'aaabcdaaa')
==>  aaabcd

     SELECT TRIM(LEADING 'a' FROM 'aaabcdaaa')
==>  bcdaaa

     SELECT TRIM('ab' FROM 'ababzzzzab')
==>  zzzz

     SELECT TRIM('  a string message ')
==>  a string message

SUBSTRING(str, start_index)
SUBSTRING(str, start_index, length)

Returns a substring of a string. The SUBSTRING function complies with the SQL specification. The start_index parameter is a value between 1 and the length of the string where 1 includes the first character, 2 includes the second character, etc. The length parameter represents the size of the substring.

Examples:

     SELECT SUBSTRING('Tobias Downer', 8)
==>  Downer

     SELECT SUBSTRING('abcd', 1, 2)
==>  ab

     SELECT SUBSTRING('abcd', 3, 4)
==>  cd

     SELECT SUBSTRING('abcd', 3, 5000)
==>  cd

     SELECT SUBSTRING('abcd', 0, 5000)
==>  abcd

     SELECT SUBSTRING('abcd', 1, 0)
==>  (string of 0 length)

4. Aggregate Functions

Aggregate functions can only operate within a group of a SELECT statement. They are used to compute statistics over a set of records.
COUNT(*)
COUNT(DISTINCT expression_list)
COUNT(column_name)
COUNT(expression)

The * version of this function returns the total number of rows in the group. If a column name is specified it returns the number of non-null values in the group. The 'expression' form of this function evaluates the expression for each row in the group and counts it only if it evaluates to NULL. COUNT(DISTINCT ... ) counts all distinct values of the expression list over the group.

Examples:

     SELECT COUNT(*)
       FROM Orders

     SELECT COUNT(*)
       FROM Orders
   GROUP BY division

     SELECT COUNT(id)
       FROM Orders
   GROUP BY division

     SELECT last_name, COUNT(DISTINCT last_name)
       FROM Customers
   GROUP BY age

SUM(column_name)
SUM(expression)

Calculates the sum of all values in a column/expression over a group. The expression form of this function is evaluated for each row in the group.

Examples:

     SELECT SUM(value) FROM Orders

     SELECT SUM(quantity * value)
       FROM Orders

     SELECT SUM(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

AVG(column_name)
AVG(expression)

Calculates the average of the column/expression over the group. The expression form of this function is evaluated for each row in the group.

Examples:

     SELECT AVG(value) FROM Orders

     SELECT AVG(quantity * value)
       FROM Orders

     SELECT AVG(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

MIN(column_name)
MIN(expression)

Finds the minimum value of a column/expression over a group.

Examples:

     SELECT MIN(value) FROM Orders

     SELECT MIN(quantity * value)
       FROM Orders

     SELECT MIN(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

MAX(column_name)
MAX(expression)

Finds the maximum value of a column/expression over a group.

Examples:

     SELECT MAX(value) FROM Orders

     SELECT MAX(quantity * value)
       FROM Orders

     SELECT MAX(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

5. Security Functions

Functions that provide security information about the session performing the query.
USER()

Returns the current user.

PRIVGROUPS()

Returns a comma deliminated list of priv groups the user belongs to. A user may belong to any number of groups which dictate the tables a user may access.

6. Branch Functions

IF(condition_expr, true_expr, false_expr)

If the first expression (condition_expr) evaluates to true this function returns the result of 'true_expr' otherwise returns the result of 'false_exp'.

Examples:

     SELECT IF(true, 5, 8)
==>  5

     SELECT IF(false, 5, 8)
==>  8

     SELECT IF(NULL, 5, 8)
==>  NULL

     SELECT IF(true, IF(false, 1, 2), 3)
==>  2

     SELECT IF(col1 = 0, 'N/A', col1) FROM MyTable

COALESCE(expr1, expr2, expr3, ....)

Returns the first non null value from the parameters or null if the entire list contains null values.

Examples:

     SELECT COALESCE(NULL, 'a')
==>  a

     SELECT COALESCE(NULL, NULL, NULL)
==>  NULL

     SELECT COALESCE(col1, 'N/A') FROM MyTable

7. Date/Time Functions

DATEOB(date_string)

Parses a string to a Date object that can be used on queries against TIMESTAMP / DATE / TIME columns. DATEOB with no arguments returns the current time of the machine running the database.
Since version 0.92 this function has been deprecated. Use the standard DATE, TIME and TIMESTAMP literals specified in SQL-92 instead.

Examples:

     SELECT DATEOB()
==>  Wed Aug 09 11:49:31 EDT 2000

     SELECT DATEOB('Aug 1, 2000')
==>  Tue Aug 01 00:00:00 EDT 2000

     SELECT number FROM Orders
      WHERE date_made >= DATEOB('Jan 1, 2000')

8. Misc Functions

UNIQUEKEY(table_name)

Returns a unique key for the given table name. This is an atomic operation that is guaranteed to return a unique number each call. It should be used to generate unique identification numbers for records. It is similar to the AUTO_INCREMENT feature of other database systems.

Examples:

     SELECT UNIQUEKEY('Orders')

     INSERT INTO Orders
        ( id, number, division, date_made, quantity,
          value )
       VALUES
        ( UNIQUEKEY('Orders'), CONCAT('Order-', id),
          'Bio Engineering', DATEOB(), 25, 1900.00 )

TONUMBER(expression)

Attempts to cast the expression to a number. If the expression is a boolean then this function will return 1 for true or 0 for false. If the expression is a String then it attempts to parse the string into a number. If the expression is a Date then it returns the date as the number of milliseconds since Jan 1st, 1970.

Examples:

     SELECT TONUMBER(DATEOB('Aug 1, 2000'))
==>  965102400000

Database

A database consists of one or more tables. A table is identified by its name. A table is made up of columns and rows. Columns contain the column name and data type. Rows contain the records or data for the columns.

Basic SQL

Each record has a unique identifier or primary key. SQL, which stands for Structured Query Language, is used to communicate with a database. Through SQL one can create and delete tables. Here are some commands:

• CREATE TABLE - creates a new database table
• ALTER TABLE - alters a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

SQL also has syntax to update, insert, and delete records.

• SELECT - get data from a database table
• UPDATE - change data in a database table
• DELETE - remove data from a database table
• INSERT INTO - insert new data in a database table

SELECT

The SELECT is used to query the database and retrieve selected data that match the specific criteria that you specify:

SELECT column1 [, column2, ...]
FROM tablename
WHERE condition

The conditional clause can include these operators

• = Equal
• > Greater than
• < Less than
• >= Greater than or equal
• <= Less than or equal
• <> Not equal to
• LIKE pattern matching operator

SELECT * FROM tablename

returns all the data from the table.
Use single quotes around text values (most database systems will also accept double quotes). Numerical values should not be enclosed in quotes.
LIKE matches a pattern. The wildcard % is used to denote 0 or more characters.

• 'A%' : matches all strings that start with A
• '%a' : matches all strings that end with a
• '%a%' : matches all strings that contain an a

CREATE TABLE

The CREATE TABLE statement is used to create a new table. The format is:

CREATE TABLE tablename
(column1 data type,
column2 data type,
column3 data type); 

• char(size): Fixed length character string.
• varchar(size): Variable-length character string. Max size is specified in parenthesis.
• number(size): Number value with a max number of columns specified in parenthesis
• date: Date value
• number(size,d): A number with a maximum number of digits of "size" and a maximum number of "d" digits to the right of the decimal

INSERT VALUES

Once a table has been created data can be inserted using INSERT INTO command.

INSERT INTO tablename
(col1, ... , coln)
VALUES (val1, ... , valn) 

UPDATE

To change the data values in a pre existing table, the UPDATE command can be used.

UPDATE tablename
SET colX = valX [, colY = valY, ...]
WHERE condition 

DELETE

The DELETE command can be used to remove a record(s) from a table.

DELETE FROM tablename
WHERE condition

To delete all the records from a table without deleting the table do

DELETE * FROM tablename

DROP

To remove an entire table from the database use the DROP command.

DROP TABLE tablename 

ORDER BY

ORDER BY clause can order column name in either ascending (ASC) or descending (DESC) order.

ORDER BY col_name ASC

AND / OR

AND and OR can join two or more conditions in a WHERE clause. AND will return data when all the conditions are true. OR will return data when any one of the conditions is true.

IN

IN operator is used when you know the exact value you want to return for at least one of the columns

SELECT * FROM table_name WHERE col_name IN (val1, val2, ...)

BETWEEN / AND

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

SELECT * FROM table_name WHERE col_name BETWEEN val1 AND val2

JOIN

There are times when we need to collate data from two or more tables. That is called a join. Tables in a database are related to each other through their keys. We can associate data in various tables without repeating them. For example we could have a table called Customers which could have information about customers like their name, address, phone numbers. We could have another table called Products that has information regarding the products like part number, product name, manufacturer, number in stock, unit price. A third table called Orders could have information regarding what product was ordered, by whom, the date the order was placed, and quantity. Here are the tables:

Customers

Cust_ID	FirstName	LastName	Address	Phone
01	Mickey	Mouse	123 Gouda St.	456-7890
02	Donald	Duck	325 Eider Ln.	786-2365

Products

Part_No	Name	Manufacturer	In_Stock	Price
20-45	Hammer	Stanley	57	3.50
21-68	ScrewDriver	DeVries	84	2.75

Orders

Order_No	Part_No	Cust_ID	Date	Quantity
2005-27	21-68	02	31 Oct 2005	2
2005-34	20-45	01	02 Nov 2005	3

We can obtain information on who has ordered what:

SELECT Customers.FirstName, Customers.LastName, Products.Name
FROM Customers, Products, Orders
WHERE Customers.Cust_ID = Orders.Cust_ID AND Products.Part_No = Orders.Part_No 
We can select data from two tables with INNER JOIN. The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Customers that do not have matches in Orders, those rows will not be listed.
SELECT Customers.FirstName, Customers.LastName, Orders.Date
FROM Customers
INNER JOIN Orders
ON Customers.Cust_ID = Orders.Cust_ID 
The LEFT JOIN returns all the rows from the first table (Customers), even if there are no matches in the second table (Orders). If there are rows in Customers that do not have matches in Orders, those rows also will be listed.
SELECT Customers.FirstName, Customers.LastName, Orders.Date
FROM Customers
LEFT JOIN Orders
ON Customers.Cust_ID = Orders.Cust_ID 
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Customers). If there had been any rows in Orders that did not have matches Customers, those rows also would have been listed.
SELECT Customers.FirstName, Customers.LastName, Orders.Date
FROM Customers
RIGHT JOIN Orders
ON Customers.Cust_ID = Orders.Cust_ID 

ALTER TABLE

With ALTER TABLE you can add or delete columns in an existing table. When you add a column you must specify a data type.
ALTER TABLE table_name
ADD col_name datatype

ALTER TABLE table_name
DROP COLUMN col_name 

UNION

The UNION command is used to select data from two tables very similar to the JOIN command. But the UNION command can be used only with columns having the same datatype. With UNION only distinct values are selected, i.e. if there are common data in the two tables only one instance of that data is returned.

SELECT Name FROM Customers_USA
UNION
SELECT Name FROM Customers_Asia

This will select all the customers from USA and Asia but if there is a name that occurs in both the tables it will return only one such name. To get all the names use UNION ALL instead.

SQL Functions

There are several built-in functins in SQL. The basic function types are:

• Aggregate Functions: These are functions that operate against a collection of values, but return a single value.
• Scalar Functions: These functions operate against a single value, and return a single value.

To use a built-in function the syntax is:

SELECT function (col_name) FROM table_name 

GROUP BY

The GROUP BY was added to SQL so that aggregate functions could return a result grouped by column values.

SELECT col_name, function (col_name) FROM table_name GROUP BY col_name 
HAVING keyword was introduced because the WHERE keyword could not be used. HAVING states a condition.

SELECT clo_name, function (col_name) FROM table_name
GROUP BY col_name
HAVING function (col_name) condition value 

CREATE VIEW

A view is a virtual table that is a result of SQL SELECT statement. A view contains fields from one or more real tables in the database. This virtual table can then be queried as if it were a real table.

CREATE VIEW view_name AS
SELECT col_name(s)
FROM table_name
WHERE condition

A view could be used from inside a query, a stored procedure, or from inside another view. You can add functions and joins to a view and present the data you want to the user.

Find Null/Empty Values in Asp.net Datatable and Replace with Other Values

Introduction:

Here I will explain how to find null or empty values in datatable and replace with other values in asp.netusing C#, VB.NET.

Description:

In previous posts I explained Dynamically create datatable in asp.net, jQuery display average rating with decimal values, jQuery generate thumbnails from you tube videos, jQuery redirect to another page after some time delay, send forgot password as email in asp.net and many articles relating to asp.net, C#,VB.NET code snippets. Now I will explain how to find null values in datatable and replace with other values in asp.net using C#, VB.NET.

I have one datatable which contains data with some null values as shown below

UserId	UserName	Education	Location
1	SureshDasari		Chennai
2	MadhavSai	MBA
3	MaheshDasari	B.Tech	Nuzividu
4	Mahendra	CA

Now I want to replace these null or empty values with some other values for that we need to write the code as shown below

for (int i = 0; i < dt.Rows.Count;i++ )

{

for(int j=0;j<dt.Columns.Count;j++)

{

if(string.IsNullOrEmpty(dt.Rows[i][j].ToString()))

{

// Write your Custom Code

dt.Rows[i][j] = "your required value";

}

}

}

If you want to see it in complete example first write following code in your aspx page

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1">

<title>Find Null Values in datatable and replace with other values in asp.net</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:GridView ID="gvDetails" runat="server">

<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />

</asp:GridView>

</div>

</form>

</body>

</html>

In code behind add the following namespaces

C# Code

using System;

using System.Data;

After that write the following code in code behind

dt.Rows.Add(dtrow);

dtrow = dt.NewRow();              // Create New Row

dtrow["UserId"] = 3;              //Bind Data to Columns

dtrow["UserName"] = "MaheshDasari";

dtrow["Education"] = "B.Tech";

dtrow["Location"] = "Nuzividu";

dt.Rows.Add(dtrow);

dtrow = dt.NewRow();              // Create New Row

dtrow["UserId"] = 4;              //Bind Data to Columns

dtrow["UserName"] = "Mahendra";

dtrow["Education"] = "CA";

dtrow["Location"] = null;

dt.Rows.Add(dtrow);

for (int i = 0; i < dt.Rows.Count;i++ )

{

for(int j=0;j<dt.Columns.Count;j++)

{

if(string.IsNullOrEmpty(dt.Rows[i][j].ToString()))

{

// Write your Custom Code

dt.Rows[i][j] = "N/A";

}

}

}

gvDetails.DataSource = dt;

gvDetails.DataBind();

}

VB.NET Code

Imports System.Data

Partial Class Default2

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

If Not IsPostBack Then

BindGridviewData()

End If

End Sub

Protected Sub BindGridviewData()

Dim dt As New DataTable()

dt.Columns.Add("UserId", GetType(Int32))

dt.Columns.Add("UserName", GetType(String))

dt.Columns.Add("Education", GetType(String))

dt.Columns.Add("Location", GetType(String))

Dim dtrow As DataRow = dt.NewRow()

' Create New Row

dtrow("UserId") = 1

'Bind Data to Columns

dtrow("UserName") = "SureshDasari"

dtrow("Education") = Nothing

dtrow("Location") = "Chennai"

dt.Rows.Add(dtrow)

dtrow = dt.NewRow()

' Create New Row

dtrow("UserId") = 2

'Bind Data to Columns

dtrow("UserName") = "MadhavSai"

dtrow("Education") = "MBA"

dtrow("Location") = String.Empty

dt.Rows.Add(dtrow)

dtrow = dt.NewRow()

' Create New Row

dtrow("UserId") = 3

'Bind Data to Columns

dtrow("UserName") = "MaheshDasari"

dtrow("Education") = "B.Tech"

dtrow("Location") = "Nuzividu"

dt.Rows.Add(dtrow)

dtrow = dt.NewRow()

' Create New Row

dtrow("UserId") = 4

'Bind Data to Columns

dtrow("UserName") = "Mahendra"

dtrow("Education") = "CA"

dtrow("Location") = Nothing

dt.Rows.Add(dtrow)

For i As Integer = 0 To dt.Rows.Count - 1

For j As Integer = 0 To dt.Columns.Count - 1

If String.IsNullOrEmpty(dt.Rows(i)(j).ToString()) Then

' Write your Custom Code

dt.Rows(i)(j) = "N/A"

End If

Next

Next

gvDetails.DataSource = dt

gvDetails.DataBind()

End Sub

End Class

In above code I am replacing null or empty values with "N/A" run your application and check below output

Output

UserId	UserName	Education	Location
1	SureshDasari	N/A	Chennai
2	MadhavSai	MBA	N/A
3	MaheshDasari	B.Tech	Nuzividu
4	Mahendra	CA	N/A