## SQL Mathematical Functions : ABS, Round, Trunc, Power

Hi friends, If you are on this page and learning mathematical functions of SQL I believe you have visited my previous post Basics of SQL and Date TIme Functions in SQL. If not, do it right now to enhance your knowledge.

There are various mathematical functions available in SQL. Some of these are given below:

1. ABS

This mathematical function always returns a positive value.

e.g. Select ABS(-50) returns 50

### 2. Ceiling

This function gives the upper value of the input integer.

e.g. Select Ceiling(14.25) -> returns 15

Select (-14.25) -> returns -14

### 3. Floor

Floor is mathematical function which gives the nearest lowest value of the supplied input integer.

e.g. Select Floor(14.25) -> returns 14

Select Floor(-14.25) -> returns -15

### 4. Power

Power is mathematical function which takes integer input and returns the power of base number.

Syntax : Power(base number, power number)

e.g. Select power(3, 2) -> returns 9

### 5. SQRT

SQRT function returns the square root value of the input integer.

e.g. Select SQRT(81) -> returns 9

### 6. SQUARE()

SQUARE function returns the square value as expected from its name :p

e.g. Select SQUARE(3) -> returns 9

### 7. Rand()

Rand is a mathematical function which returns a random value but if you will use it in the following way: Select rand(2), then it will always return the same value.

#### How to generate a random value between 1 to 100?

Use floor function with rand(), this way you will be able to generate a different value each time:

Select Floor(rand()*200)

This function will return some random value between 1 and 200.

Or simply use rand()*some_integer_value

### 8. Round()

The round function is very useful and it rounds the supplied input value to the specified integer.

Syntax: Select Round(number_to_roundoff, int_value_to_specify_round_limit, specify 1 if value needs to be truncated)

e.g. Select round(23.456, 2, 0) – > Rounds the value up to two decimal places: 23.460

Select round(23.456, 2,1) -> Truncate the value up to two decimal places. The output will be 23.450

The round() function also takes negative value to specify the round off/trunc direction to apply. A negative value indicates the truncation/round to apply to the left.

e.g. Select round(230.456, -2) -> rounds the two integer values to the left of decimal value. Thus this function returns 200.00 as an output

#### Want More on Round function?

Select round(250.456, -2) -> is rounded to 300.00

Select round(250.456, -2, 1) -> value is truncated to the 2 places left to the decimal value and the output will be 200.00

Select round(256.456, -1) -> returns 260.00

Select round(256.456, -1, 1) – > returns 250.00

Hope this clarifies all the confusion about mathematical functions. Want to learn about stored procedures in SQL? Check out my post What are stored procedures and benefits of using Stored procedures.

Till that time c yaa friends 🙂 Comment on this post if you like it because this will keep me going to share good stuff with you. happy learning 🙂

## What is a Malicious Program/Malwares? Check the difference between malwares – Virus, Worm, Root Kit, Trojan Horse and Types of Virus

Hi friends, today I am writing about malicious programs – Virus, Root kit, Trojan horse etc and the types of viruses. So, firstly it is very important to know What is malware? It is an important topic for exams like IBPS IT officer, Assistant Manager (IT), SBI IT officer, GIC Officer Scale – I, Computer Teacher, Computer Engineer or any other computer engineer related post.

Looking over the importance of this topic, I am explaining this in detail and will also share a questionnaire on it soon. So stay updated with this website and let me know if you want more on this topic. Let’s first understand what Malicious Program is.

## What is Malicious Program/Malwares?

Malicious software, which are commonly known as Malwares are a type of software that can harm your computer system. These programs come in different forms for example: viruses, trojans, worms, spyware, adware, root kit, zombie, key loggers etc.

These programs can delete documents, modify or steal the protected data and also add software to your computer programs which are not approved by user.

Hope you get a basic idea of the dangerous nature of these programs. Now, let’s move to the core concepts of this Malicious Programs section so that IT officer post or any computer science post people can answer the questions related to malicious programs.

## Types and Forms of Malicious Programs

I have tried to provide the maximum information that covers most of the questions from Malicious Programs section for government exams. Also, if you want to learn basics of SQL, then follow.

## Difference between Computer Virus, Computer Worm, Trojan Horses and Bots

### Computer Virus vs Computer Worm :

Computer worms are similar to viruses in the manner that they replicate the functional copies of themselves and can cause the same type of damage. But, still there are some differences in the way these malicious programs cause damage.

In contrast to Viruses, which require the spreading of an infected host file, worms do not require a host file to propagate. Instead, worms are standalone software that do not require any host program or human help to propagate from one computer to another. Now let’s see some basic difference between these malicious programs/malware programs:

### Virus :

Computer viruses attach a copy of itself to files or programs, infecting each computer it comes across as it spreads. It usually spreads through Emails or executable files. Some viruses are very annoying because they slow down your computer, while others can wreak havoc on your system. Viruses usually modifies files or it may sometimes change the location of a file.

### Worms:

Computer Worms also replicate themselves but unlike viruses, these malicious programs do not attach themselves to a program. Instead, these spreads from computer network to network and are extremely dangerous.

These can also damage the servers. Computer worms usually do not infect files. Instead these monetize your computer memory or CPU.

### Trojan Horses:

These malicious programs are sneaky ones. Trojan horses hides all sorts of malicious goodies and can harm your computer. These malicious programs may look legitimate software at first glance, but when you install these programs they can delete or modify your files.

Unlike viruses and worms, Trojan horses do not replicate themselves.

### Bots:

Bots are a type of malicious programs that may have good intent sometimes. These self-replicating malware is designed to retrieve some sorts of information from the infected system. Bots rarely announce their presence. These malicious programs infect networks in a way that escapes immediate notice.

## Life Cycle of Virus/ Phases of Virus:

During its lifetime, a typical virus goes through the following four phases:

1. Dormant Phase: In this phase, the malicious program Virus is idle. The virus will eventually be activated by some event, such as a date or the presence of another program of file. Not all virus have this stage.
2. Propagation Phase: In this phase, the virus places an identical copies of itself into other programs or into certain areas on the disk. Each infected area/program in the computer system will now contain a clone of the malicious program Virus.
3. Triggering Phase: In this phase, the virus is activated to perform the intended function. The triggering phase can be caused by a variety of system events like a specific date or exceeding the disk space to a limit.
4. Execution Phase: In this phase, the virus perform the intended function. The performed function may be harmless or can damage files or programs of the system.

## Nature of Viruses:

Viruses are designed in a way to take advantage of the details and weakness of a particular system. These malicious programs are usually specific to a particular hardware platform or a particular operating system in some cases.

Hope the nature of these malicious programs is clear up to this point.

## Types of Virus:

There are numerous types of viruses but I am sharing the most significant types of Virus here. With the enormous growth of technology and available data, the count of virus types is growing. New viruses are being developed and used. So, here is the list of the most common types of viruses:

1. Memory-Resident Virus: It lodges in main memory as part of the resident system program. From that point on, the virus infects every program that executes.
2. Stealth Virus: This malicious program/malware is explicitly designed to hide itself from any antivirus software. The purpose of the stealth virus is to defeat the virus detection/scan process. To achieve this, the signature of the virus with vary with each replication. The virus may insert superfluous instructions or can also change the order of the independent instruction inside a program.
3. Parasitic Virus: This is the most common form of virus. A parasitic virus attaches itself to the executable files and replicates. Thus this virus infects other executable files when the infected executable file is executed.
4. Boot Sector Virus: This virus infects the boot record or the master boot record and thus can prove to be very harmful for the system. Infecting the master boot record, this malicious program spreads when the system is booted with the disk containing the concerned virus.
5. Polymorphic Virus: This virus mutates itself with every infection, thereby making the detection of the virus impossible.
6. Metamorphic Virus: This is similar to polymorphic virus, with one difference that it rewrites itself completely with every infection. Hence, these virus change the detection very difficult. The metamorphic virus may change their behavior as well as appearance.

Phew… finally I was able to frame all the useful information related to malicious program – Network Security section in this post. Please comment below for any feedback or suggestions. For theon this section please read my next post here.

Post Views: 8,417

## Why is Python the Best Language for Machine Learning and Data Science?

Let us begin by reading the Quote of Guido van Rossum on Python. It goes like this, The joy of coding Python should be in seeing short, concise, readable classes that express a lot of action in a small amount of clear code — not in reams of trivial code that bores the reader to death.” So let’s see why Python is the best language for Machine Learning and Data Science.

First, let’s see why Python is best for Machine Learning and then let’s see why it is best for Data Science.

### Why is Python Best for Machine Learning?

Python is the most popular programming language for Machine Learning (ML). Python for ML has the following advantages. First, Python has a great collection of in-built libraries. Some of the in-built libraries are,

• NumPy: This is used for scientific calculation.
• Scikit-learn: It has tools for data mining and analysis that optimizes Python’s brilliant ML usability.
• Pandas: It is a package that provides developers with high-performance structures and data analysis tools. Moreover, it helps developers to reduce project implementation time.
• SciPy: This is used for advanced computation.
• Pybrain: It is exclusively used in Machine Learning
• Seaborn and Matplotlib: Seaborn is an excellent visualization library aimed at statistical plotting. On the other hand, Matplotlib is the most commonly used 2D Python visualization library.

Secondly, Python enables a moderate learning curve. Python is very accessible and easy to learn and use. Moreover, it focuses on code readability and is a versatile and well-structured language.

Thirdly, Python is a general-purpose programming language which is a good choice for project requirements if they are more than just information.

Fourthly, Python is easy to integrate. Python incorporates better for business environments. Moreover, it is easy to integrate it with lower-level languages such as C, C++, and Java. Likewise, Python-based stack is easy to incorporate with the work of a data scientist.

Fifthly, less amount of code. ML has a huge amount of algorithms, and Python makes it simpler for developers in testing. It comes with the potential of implementing the same logic with as less as one-fifth of code required in other OOP (object-oriented programming) languages.

Sixthly, it is easy to create prototypes with Python. As Python requires less coding, you can create prototypes and test your concepts quickly and easily.

Seventhly, Python supports both object-oriented and procedural programming models. Significantly, classes and objects in object-oriented programming help to model the real world while functions in procedural programming enable to reuse the code.

Lastly, the advantage of portability. Code written in Python can be run on another platform. This is called Write Once Run Anywhere (WORA).

All the above supportive cases for Python makes it a part of the vital teaching curriculum in many Python training institutes. Now let’s see Python’s benefits for Data Science.

## Why is Python Best for Data Science?

Let’s get straight to the tech part. Python libraries for Data Science are similar to that of ML. They are Numpy, Matplotlib, Scikit-learn, Seaborn, and Pandas.

Basically, Python is good for Data Science for the following reasons,

• Python is flexible and an open source language.
• With it’s simple and easy to read syntax Python cuts development time in half.
• Python powerfully enables data manipulation, analyzes, and visualization.
• It provides good libraries for scientific computations.

Let’s get into pointing out solid factors that makes Python a valuable choice for Data Science projects.

## Less is More

Python employs fewer codes. It automatically identifies and associates data types and follows an indentation based nesting structure. With Python, there is no limit to data processing. For a good hands-on, please check Learn basics of SQL.

Moreover, Python is faster with the Anaconda platform. Hence it is fast in both development and execution.

## Python is Compatible with Hadoop

Hadoop is a popular open-source big data platform and the inherent compatibility of Python is another reason to prefer it over other languages. Importantly, the PyDoop package offers access to the HDFS API for Hadoop and hence allows to write Hadoop MapReduce programs and applications.

Moreover, PyDoop also offers MapReduce API for complex problem solving with minimal programming efforts. Eventually, this API can be used seamlessly to apply advanced data science concepts like ‘Counters’ and ‘Record Readers’.

## Python is Good for Data Visualization

APIs like Plotly and libraries like Matplotlib, ggplot, Pygal, NetworkX can bring about breathtaking data visualizations. Moreover, you can use Tabpy to integrate Tableau and use win32com and Pythoncom to integrate Qlikview.

## Python has a lot of Deep Learning Frameworks

There are several deep learning frameworks like Caffe, TensorFlow, PyTorch, Keras, and mxnet. You can pick from any of these tools that will fit your project and allow you to build deep learning architectures with few lines of Python code.

## Python is Good for Writing Scraping Software

Python has a variety of tools for scraping data and the largest community support for doing so. Moreover, you can choose many different scraping ecosystems such as Scrapy, BeautifulSoup, or requests.

Scrapy for example can handle a lot of dirty work for you, by providing a structure for your spiders. By using Scrapy you can write web spiders in minutes.

## Python is Versatile

Being a general-purpose programming language, Python is a quick and powerful tool with a lot of capabilities. From building web services, data mining to data mining, Python is a programming language that helps you to solve data problems end-to-end.

## Python is Good for Building Analytics Tools

When it comes to creating a web service to allow others to find outliers in their datasets, Python is a good way forward. This is even more important when self-service analytics is becoming more important.

## Python is Best for Deep Learning

Plenty of packages such as Theano, Keras, and TensorFlow make it really easy to create neural networks in Python. While some of these packages are being ported to R, the support available in Python is far advanced.

For all the above reasons, Python class conducted by private professional institutes emphasize the leading role of Python over other languages for use in Data Science.

Conclusion

Let us put in a nutshell the advantages of Python for Machine Learning and Data Science. They are,

• A great library ecosystem
• It has a low entry barrier
• Flexibility
• Platform independence
• Great visualization options
• Community support and
• Growing popularity

Let us end this article with another attribute to Python. Google’s Peter Norvig has this to say about Python, “Python has been an important part of Google since the beginning, and remains so as the system grows and evolves. Today dozens of Google engineers use Python, and we’re looking for more people with skills in this language.”

If you are new to programming, then start with Learn Basics of SQL

# What is a Database?

Database is an organized collection of related information. In daily world we deal with lots of data. In this internet technology more and more data is getting produced these days. We have multiple database management systems available with us to manage, store and update this enormous data in convenient way. e.g. Oracle, Sybase, Microsoft SQL server management studio etc.

## DBMS and SQL

DBMS (Database management server) is a collection of software tools used to manage, update, retrieve the data from the database. SQL (Structured query language is used to connect the DBMS with the database.

All queries have been executed on the Microsoft SQL Server management Studio version 17.0. SSMS is a client tool and not the server. It is rather used as a tool to connect to the database server.

Settings: Local Host

Connect: Database Engine

### SQL Databases:

In Microsoft SQL Server Management Studio you will find two types of databases:

1. System Database
2. User created Database

-System database can’t be deleted

##### SQL Command Types:
1. DDL (Data Definition language) – Used to define/create database object
2. DML (Data manipulation language) – Used to insert values, update and delete values from the database object created by DDL commands.
3. TCL (Transaction Control language) – Used to control transactions through Commit and Rollback commands

### SQL DDL Commands – data definition language (Create, Alter and Drop commands)

#### 1. Creating a database:

Database can be created either using GUI or through SQL query in SSMS.

Create statement is used for this purpose: Create [Database Object] [Database Object name]

Ex. Create Database db1 (this statement will create a database with name db1)

Whenever we create a database, two types of files are created with it: 1. .MDF file (contains actual data) 2. .LDF file (contains log file)

#### 2. Modify a Database:

Alter statement is used to alter a sql database object.

Alter Command:  Alter [Database Object] [Database Object name] Modify Col1 = Value

E.g. Alter Database db1 Modify Name = db2 (this will change the name of the database)

Renaming through stored procedure: sp_renameDB [Old database name] [New database name]

e.g. sp_renameDB db1 db2

#### 3. Dropping a Database:

Drop statement is used to delete a database completely from the system(.mdf and .ldf files are also deleted with it)

Drop command: Drop [Database Object] [Database Object name]

e.g. Drop Database db1 (this will delete database db1)

Note- If a database is getting used by any other user, make sure that database is not getting used by any other database. Else an error will be generated

Resolve this single user thing, use this command:

Alter Database db1 set Single_USER with Rollback immediate

(Rollback immediate, rollback any commands and delete the database immediately)

#### 1. Create a Table:

Command: Create Table [table name] ([column name] [data type of column] [constraint])

e.g. Create table t1(ID int NOT NULL Primary Key, Gender nvarchar(20) NOT NULL)

This command will create a table with name t1 and 2 columns ID and Gender of INT and nvarchar datatypes respectively. nvarchar is a UNICODE data type and store 2 bytes per characters, while varchar stores 1 byte per character.

In order to store the table in a particular database use the following command:

Use [database name]

Go

Create table command….

Primary Key – Can’t be null and must be unique. It uniquely identify each row in the table

Foreign key– It can contain null values and it references primary key present in some other values (basically the column in which it looks for a value). Foreign key is used to establish relationship between two tables. It is used to enforce database integrity.

Create a Foreign key relation –

Alter table [table name] add constraint [constrain name] foreign key(foreign key column name) references [PrimaryColumn Table Name] (primary key column)

e.g. Alter table tb1 add constraint tb1_genderid foreign key(tb1) references tb(id)

Note- Constraint name should make sense like tablename_columnName

#### 2. Select all values of a table:

Command: Select * from [table name]

To select all tables of a database choose:

Select * from dual (dual refers data dictionary)

#### 3. Insert values in a table :

Insert command is used to insert values in a table: Insert into [table name] (col 1, col 2, …) Values(col 1 value, col 2 value,…)

e.g. Insert into a1(id, name, gender) values(11, “ss”, “male”)

#### 4. Adding a Default value in a column:

We can assign default values to a column rather than assigning Null values:

Alter table [table_name] add constraint constraint_name Default [default value] For [column name]

e.g. Alter table tb1 add constraint tb1_gender default 2 for gender

This command will assign default value 2 to column gender if value not explicitly defined.

#### 5. Adding a New column into table:

Command: Alter table [table name] add [column name] [column data type] [NULL|NOT NULL] add constraint [constraint name] Constraint

This command will add one column Address to the table tb1 that don’t accept null value. Also, default value of ‘xyz’ will be assigned to it.

#### 6. Dropping a Constraint:

Command: Alter table [table name] Drop Constraint [constraint name]

e.g. Alter table tb1 drop constraint tb1_gender

This will drop the constraint.

#### 7. Delete a Table record:

To delete a table record, we use delete command:

Delete from [table name] where column1=”column value”

Note: Where clause is used to put some condition on search selection

However, you can’t delete a table record if the table is getting used by some other user. There are some cascading referential integrity constraint imposed on foreign key constraints.

#### 8. Cascade Referential Integrity Constraint:

We can choose options if a foreign key constraint reference is getting deleted. Four options are there:

1. No Action : This will simply generate an error if a record from primary key table is deleted that has some value in foreign key table.
2. Cascade: This option will delete all the foreign key records that are related to primary key will be deleted
3. Set NULL: This option will set the foreign key dependent value to Null.
4. Set Default: This option will set the foreign key dependent value to default values provided to the column.

#### 9. Adding a Check Constraint:

This constraint is used to enforce value checks on column. For e.g. The value in the age column>4

Command: Alter table [table name] add constraint [constraint name] check (boolean expression)

e.g. Alter table tb1 add constraint tb1_age_check check(AGE>0 AND AGE<30)

This command will only let you add age between 0 and 30 in the Age column.

Note: The check constraint returns a Boolean value based on which the value is entered in the table. It also let you insert Null values because for Null values, check constraint returns “Unknown”.

#### 10. Identity Column:

It is a column property in SSMS.

Identity column is a column to which values are automatically assigned. There are different properties linked to this column. Please set up following values while creating identity column:

Identity Seed: A value with which the identity column value starts

Identity Increment: The value with which identity column value is incremented.

CommandCreate table stu(id int identity(1,1) Primary key)

This command will create a stu table having id as a identity column. The id column here will start from 1 and incremented by 1.

#### 10. Setting up External Values/Explicit Value to Identity Column:

To set up external value in Identity column, add the following command before inserting values in table:

Command: Set IDENTITY_INSERT [table name] ON

Insert into table name(column list) values(1,”23″..etc)

#### 11. Setting Off External Values/Explicit Value to Identity Column:

Command: Set IDENTITY_INSERT [table name] OFF

Insert into table name(column list) values(1,”23″..etc)

Note: To reset the identity column value, use DBCC command.

#### 12. Unique Key Constraint:

Unique key constraint is used to enforce unique values in database. There is a slight difference between primary key and unique key.

Primary key values = Unique+Not Null

Unique constraint value = Unique + values can be null

CommandAlter table table_name add constraint constraint_name unique(column name)

or

Create table Stu(Name varchar(20) Unique)

#### 13. Applying a Trigger:

Firstly let’s try to understand what a trigger is. A trigger is an sql instruction/set of instructions that will will cause an action once a specific condition occurs. For example: Inserting another row table 2 when a row is entered in the table 1.

Command:

Create Trigger [trigger_name] on [table_name] for Insert/Update/Delete/Condition

as

begin

[instructions]

end

#### 14. Selecting values from table:

Select is a command used to retrieve records from a table.

1. To fetch all records from a table:

Command: Select * from [table_name]

e.g. Select * from emp

2. Select specific columns from a table:

Command: Select [col_name_1], [col_name_2]… from [table_name]

e.g. Select name, age, id from Employee

3. Fetch all distinct records from a table:

Command: Select distinct [column_name] from [table_name]

e.g. Select distinct name from Employee

This command will help in fetching the distinct records from table Employee by Name column.

4. Fetch record matching a specific condition:

Where is used to apply a specific condition in the SQL command.

Command: Select * from table_name where column_name = condition value

e.g. Select name, id from employee where name=”John”

This command will fetch all the records from the table with name column value as john.

5. Fetch record not matching a specific condition (column value):

Command: Select * from table_name where col_name <> Column value

“<>” signifies as not equal to here. We can also use “!=” to compare values.

6. OR operator in SQL:

OR operator is used to specify two or more conditions together.

Command: Select * from table_name where col1=value OR col2=value

e.g. Select name, age, salary from Employee where name=”John” OR name=”Nick”

This sql command will fetch all the table records where name is either John or Nick

7. AND Operator in SQL:

AND operator is used to specify two and more conditions together.

Command: Select * from table_name where col1=value AND col2=value

e.g. Select name, age, salary from Employee where name=”John” AND age=”30″

This sql command will fetch all the table records where name is John and age is Nick

8. IN Operator in SQL:

IN operator is used to retrieve records where condition matches more than 1 value. (And you don’t want to use OR multiple times in a sql command)

Command: Select * from table_name where col_name IN(value1, value2, value3…)

e.g. Select * from Employee where age(21, 25, 30)

This command will fetch all the table record where age is either 21 or 25 or 30.

SQL supports various kind of wild card characters to facilitate data retrieval in multiple ways. Please refer the image for all sql wild card characters.

The DataBird

Post Views: 2,025

## Cons of working in Third Party Payroll System

So, here we are discussing all the cons of working in Third Party Payroll system. As you may have already visited my previous post of What is Third Party Payroll System? What are the Pros and Cons of working in Third Party Payroll, now lets check the cons of working in Third Party payroll system in detail:

1. Low Salary

You’ll get low salary as compare to the market standards for the same job posts if you are working as a third party payroll employee. The reason being, some part of your salary will be taken by the your third party payroll company. So, don’t cry for not getting the actual price value of your work.

2. Not getting Salary on time

Since you will be working on client site, you’ll never know what’s the delay in getting salary when you are already out of budget for the month. You will have to talk to you third party consultant. Most of time, you won’t get your monthly income on time. So, hell no to the groceries..

3. Rewards are delayed

So if you are someone who likes to work hard and a good performer, forget about your incentives. And, when you will go to your HR, they will say something…there is some internal issue in the accounts section. Incentives requires approval from a long chain of people and at the end you won’t get it…because somebody has already disapproved it. Bear the cons of working in third party payroll system.

4. HR is the God

Your only point of contact at client site will be your HR and that would be of no use many times. HR considers himself/herself the God in such scenario and you won’t know who should be your next point of contact if HR misbehaves with you.

5. Slow Growth and Zero Job Security

The job growth is very slow for the ones who are working in third party payroll system. You will work hard and still won’t get much. At the end, when you are discussing the appraisal the so called God (HR) will explain you about the budget specified by the client for your process. And yes, there is no job security. Just one error and you will get escalation emails from client, then manager and then HR. The quality demand is always very high as compared to your pay. Your soul will be saying I want more at appraisal time.

So, don’t bear the cons of working in third party payroll system. Just prepare for job interviews given on my website and switch the job 🙂

TheDataBird

Post Views: 1,481