Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

Archives: August 2014

SQLTea - New SQL tutorial app in Google play store

SQLTea - Our new SQL Tutorial App on Google Play Store
 
We are happy to announce our new SQL tutorial app SQLTea  ( Native android app) for SQL learning which includes articles from this blog.


This application includes 

1) Articles for basic concepts like Normalization, Constrains, Joins, SQL Functions,…

Read more

6 comments, 745 reads

Posted in Vivek's SQL Notes on 18 August 2014

How to restore database backup in SQL SERVER

In this article, I am going to explain the process of  Database restoration using the SQL Server Management Studio. In this article, I will try to explain this process in a simple way so that a database developer having little knowledge of DBA can restore that database using the database…

Read more

0 comments, 153 reads

Posted in Vivek's SQL Notes on 17 August 2014

How to take Database backup in SQL SERVER

This article explain the simple way to take up the backup of a database which can be easily understandable by the database programmer having little knowledge of DBA. For taking the database backup, we need to follow the given step.

Step 1:- Open the SQL Sever Management Studio. For this…

Read more

0 comments, 107 reads

Posted in Vivek's SQL Notes on 17 August 2014

Database Creation using SQL Sever Management Studio (SSMS)

In the previous article (Database Creation using Create Database command), I explained the database creation using the Create database command. In this article, I am going to explain the steps of creation a database using SQL Sever Management Studio (SSMS) using SQL Server 2008 R2. These below database creation…

Read more

0 comments, 208 reads

Posted in Vivek's SQL Notes on 17 August 2014

Database Creation using Create Database command

In SQL Server, database can be created by 2 ways:-
  1.  Using Create Database command
  2.  Using SQL Sever Management Studio (SSMS).

In this article, I am going to explain the database creation using the command CREATE DATABASE.

Syntax for creating a database through Create database command is given below:-

CREATE DATABASE…

Read more

0 comments, 6,639 reads

Posted in Vivek's SQL Notes on 17 August 2014

IN clause in SQL Server

With IN clause, we can specify multiple values in a WHERE clause.

For example, in case of "IN", one can use the subquery to get the multiple values in the IN clause or we can have predefined values.


In this article, we are going to use the following table for…

Read more

0 comments, 247 reads

Posted in Vivek's SQL Notes on 14 August 2014

UPPER() function in SQL Server

UPPER():- This function is used to convert the value of a column to upper case.

 Syntax:

   SELECT UPPER(string)

OR

Select UPPER(Column name) from tablename

For example,

  SELECT UPPER('Vivek')

Result:


   VIVEK



Read more

0 comments, 369 reads

Posted in Vivek's SQL Notes on 14 August 2014

AVG() function in SQL Server

Avg():- This function is used to get the average value of a numeric column

Syntax:-


      SELECT AVG(column_name) FROM table_name


In this article, we are going to use the following table for some examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT…

Read more

0 comments, 223 reads

Posted in Vivek's SQL Notes on 14 August 2014

LOWER() function in SQL Server

LOWER():- This function is used to convert the value of a column to lower case

 Syntax:-

 Select LOWER (string)

 
 or
 

 SELECT LOWER(column_name) FROM table_name

For example,

SELECT LOWER('Vivek Johari')

Result:-

vivek johari


Read more

0 comments, 113 reads

Posted in Vivek's SQL Notes on 14 August 2014

LEN() function in SQL Server

LEN():- This function is used to get the length of the given string value.

  Syntax:-


    Select len (stringvalue)

  For example,

    Select len('Vivek Johari')

   Result:-

   12

Read more

0 comments, 121 reads

Posted in Vivek's SQL Notes on 14 August 2014

Substring() function in SQL Server

Substring():- This function is used to return the specified number (third argument "len") of characters from a given string(first argument) from the start position as specified as the second argument.

Syntax:-

   Select  SUBSTRING ( expression ,start , length )

For example,

       Select  SUBSTRING ( 'kingmaker' ,1 , 4…

Read more

0 comments, 103 reads

Posted in Vivek's SQL Notes on 14 August 2014

LTRIM() function in SQL Server

LTRIM ():- This function is used to get the string after the removal of leading blank spaces.

Syntax:-

LTRIM ( character_expression )


For example:-

Select LTRIM  ('  Vivek Johari')

Result:-

Vivek Johari (without blank spaces on left side)


Read more

0 comments, 99 reads

Posted in Vivek's SQL Notes on 14 August 2014

Max() function in SQL Server

Max():- This function gives the maximum value of the selected column

Syntax:-

Select Max (col1) from table_name


In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150)…

Read more

0 comments, 112 reads

Posted in Vivek's SQL Notes on 13 August 2014

RTRIM() function in SQL Server

RTRIM():-This function is used to get the string after the removal of all trailing blanks.

Syntax:-

RTRIM ( character_expression )

For example:-

Select RTRIM('Vivek Johari  ')

Result:-


Vivek Johari (without blank spaces on Right side)


Read more

0 comments, 126 reads

Posted in Vivek's SQL Notes on 13 August 2014

AND and OR Operator in Sql Server

And & Or:- These Operators are used to further filter the recordset return by SQL queries when more than one conditions are specified. In case of "AND" all the conditions must be true and in case of "OR" any of the condition must be true.
In this article, we are…

Read more

0 comments, 107 reads

Posted in Vivek's SQL Notes on 13 August 2014

SUM() function in SQL Server

Sum():- This function is used to get the sum of values of the specified numeric column.

 Syntax:-

 Select Sum (column_name) from tablename


Example:-

In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary…

Read more

0 comments, 139 reads

Posted in Vivek's SQL Notes on 12 August 2014

Round() function in SQL Server

Round():-This function is used to round a numeric field to the number of decimals specified.

Syntax:-

    ROUND ( numeric_expression , length [ ,function ]

numeric_expression :-Expression of the exact numeric or approximate numeric data type category, except for the bit data type.

length:- Optional.Must be an expression…

Read more

0 comments, 122 reads

Posted in Vivek's SQL Notes on 12 August 2014

Except command in SQL Server

This function returns the distinct values from the left query that are not also found on the right query. For Except command, below basic rules must be followed.

  1. The number and the order of the columns must be the same in all queries.
  2. The data types must be compatible.

Syntax

Read more

0 comments, 117 reads

Posted in Vivek's SQL Notes on 12 August 2014

Like() function in SQL Server

Like:- This operator is used in a WHERE clause to search for a specific pattern in the values of the column.

In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50)…

Read more

0 comments, 112 reads

Posted in Vivek's SQL Notes on 11 August 2014

Best practices for Database Programming

1. All the tables in the database have properly defined relationship using primary keys and foreign keys . 

2. Indexes should be created on the tables as it increases the performance of the SQL Queries.

3. Find the indexes which need to be reorganized or rebuild.

4. Use "set not…

Read more

2 comments, 1,364 reads

Posted in Vivek's SQL Notes on 11 August 2014

Older posts