Monday, November 3, 2014

Interview questions for database developer

Technical Round Database Interview Questions

1) What are the different types of Trace flags which we used for detecting the Deadlock?
2) What are the different types of database backups?
3) How do you perform the Error handling in SQL Server?
4) What is the ACID property in SQL Server?
5) What are the best practices in Database programming?
6) What are the different isolation level in SQL Server
7) What is the default isolation level in SQL Server
8) What is dirty read in SQL Server
9) What are the Serializable isolation level in SQL Server
10)What are the Rank functions in SQL server
11) What is Replication in SQL Server
12) What is the stuff function in SQL Server?
13) What is the Difference between Stuff command and Replace command in SQL Server
14) What is the merge command in SQL Server?
15) What is the Pivot/Unpivot table in SQL Server?
16) What is Derived table in SQL Server?
17) What is the Output clause in SQL Server?
18) What is the temporary tables in SQL Server?
19) What are the different types of database in SQL Server?
20) What is CTE in SQL Server?
21) What are the benefits of using CTE SQL Server  
22) What are cursors in SQL Server?
23) What are Views in SQL Server?
24) What are Triggers in SQL Server?
25) What are magic tables in SQL Server?
26) What are the difference between Stored Procedure and SQL triggers in SQL Server?
27)If no rows are affected by the SQL query, will after trigger will execute?
28) How can we improve the performance of an stored procedure?
29) What are the heap table in SQL Server?
30) Where we need to use table variable and where we should use temporary tables?
31) What is log shipping in SQL Server?
32) What is fragmentation in SQL Server?
33) What is the difference between logical and physical fragmentation?
34) In which database temporary tables are created?
35) What is the difference between Except command and Intersect command?
36) What is the BCNF in SQL Server?
37) What is database collation?
38) Suppose there are two tables A and B and we need to write 3 SQL queries which returns the record set as shown in the below figure.
 
39) Suppose we have to design a database in which employees can work on multiple projects.There are many designation defined in the organization to which each employee can belongs.
 

For example, Software engineer, Team lead, Project Manager, Project lead, QC engineer, QC Lead, QC manager etc. An employee can belong to one designation
at a time.  It means at a time an employee can either be Software engineer or team lead or project manager but not software engineer as well team lead at a same time.

Also the projects can work on different technologies like .Net, SQL Server, Oracle, HTML5 etc. Every project has a start data and end date. A project can work can include many technologies like .net, SQL Server , Java script, HTML5 etc. Most of the time every project work include a front end server technology like JAVA. Asp.net, uses Java script, HTML, CSS as client side technologies and SQL Server, oracle as back end database server. So In this ways a project work can include many technologies as in above example,  suppose a project can use Asp.net as server end front end technology, java script, HTML, CSS , AJAX as client side technology and SQL server as back end database server.

What should be the design the database schema (tables, their foreign keys, primary keys etc.?


40) Suppose we have a table named tbl_charactername have only one column say "charactername".
It contains 10 rows. First 5 rows contains "Vivek Johari" and the next 5 rows contains "Abhinav Golwalkar"

tbl_charactername
---------------------
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar

Now we need a SQL Query which can return the result in the form

Desired Result:-

Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Query should work even if the table has 30 rows with first 15 contains "vivek johari" and next 15 contains "Abhinav Golwalkar" or 40 rows with first 20 contains "vivek johari" and next 20 contains "Abhinav Golwalkar" etc. But output should contains "Vivek Johari" and "Abhinav Golwalkar" in alternate rows.

41)  Suppose we have a table says tbl_students whose structure is given below:-

Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))

Suppose it contains the following data:-

Studentid  Studentname
1               Vivek Johari
2               Chandra Singh
4               Avinash Dubey
6               Akhil
7               Sunny Rana
10             Saurabh

As one can see from the data that some rows are deleted from the table as some studentids are missing.

So write a query which gives the studentid which are deleted from this table

Project Manager Round Questions:-

1) Give me your brief introduction.
2) What your current project and your role in it?
3) How is your team size?
4) what is the hierarchy in your organization?
5) Have you any experience of DBA activity?
6) How big your database size?
7) How you optimize database (same technical question but this time PM just want to know just an overview)?
8) What best practices you follow in your company?
9) Any tools your company uses for database designing?
10) Do your company follow Agile methodology ?
11) Any challenges you faces in your current project?
12) Why you want to change your current organization
13) Would you like to work on C#, web services, Java (in case of Oracle) if required?
14) How early you can join(want to know your notice period or possibility of early joining)?
15) Do you want to ask any question from us?



Sunday, October 19, 2014

My Best LinkedIn Articles

Sunday, October 12, 2014

My Best Technical Sites and Blogs

Many times my friends ask me which technical sites they should refers for finding the solutions, so below is the list of tech sites which I used to refer most of the times for learning technologies and finding the solutions

Mssqltips.com:- I like this site because I find here some very good tutorials in SQL which are very simple to understand and gives some good knowledge for a person which is not expert in SQL Server.


Simple-talk.com:- I love this site since it has some brilliant authors who share their knowledge through some very good articles.  

Technet.microsoft.com:- This is Microsoft sites where you can get help on any topic however small it may be. Some in depth information with easy to understand language.

Stackoverflow.com:- This is one of my favorite discussion forum where you can get the solutions of most of your problems. Also from multiple answers , you can also mention which is working or which is best which help others.

Codeproject.com:- This site needs no introduction as it contains lots of articles on each technology. Lots of technical professional share their knowledge on this site. You can ask your question in this site ask question section. You can get many quality articles on this site. Also best articles of the month competition is always going on where you can win some good prizes by submitting your articles and winning the competition. 

Techmixing.com:-This is my blog where I share my technical knowledge though articles. I always reads the articles on this site before going for any interview or whenever I needs to brush up my knowledge. So always trying to maintains the quality of articles on this site.

SQLServerCentral.com:- Like Simple-talk, this is one of the best site for database. Have so many brilliant database experts as its author. Daily publish so many quality articles and also mentions good articles from other sites too. Its Question of the Day sections always comes up with a very good objective questions.

Sqlauthority.com:- Whenever you search for any SQL topic or issue , Google always show the link of this site in first few entries. Run by one of the best Indian database legend Pinal Dave, this site contains lots of SQL articles from small to complex topics. This is the site which helps me a lot in learning the SQL Server.
  
Pluralsight.com:- A video tutorial site contains lots of video tutorials for each
and every technology. All tutorials contains good explanations of the concepts with some very good demo. You can also took pre course online test to judge your knowledge before starting the course and also took post course test to judge your increased knowledge.You only has to subscribe on it which cost you some monthly fees. It has 2 options of subscription


Developer.android.com:-This is the best site for learning Android development. All tutorials on this site has been written in a very simple language which is easy to understand. I must say it is a must read site for any developer who want to learn android or working on it.

There are some other popular sites also like beyondrelational.com, C#corner, DatabaseJournal.com, Tutorialpoint.com which you can refers for finding your solutions. If you refers some other quality technical site or blog, please mention it as a comment so that others also get benefited. 

Monday, August 18, 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, SQL commands etc and also advance concepts like SQL Triggers, Views, Cursors, Stored Procedures and many more.

2) Every article is explained with the help of examples.

Techmixing.com new apptechmixing.com new app


3) Once installed, it will work in offline mode (No Internet needed). You can read articles easily even if you don't have Internet connections


4) Includes many popular articles from this blog.

5) Light weight and fast

6) Will going to frequently update this App with new blog articles.

7) Simple to use and navigates.

So please download this app and gives your valuable comments on it.



Sunday, August 17, 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 backup.

For database restoration process, we need to follow the below steps:-


Step 1:- Open the SQL Sever Management Studio. For this we have to follow the following path:-


START --> All Programs --> Microsoft SQL Server 2008R2 --> Click on SQL Sever Management Studio (SSMS) --> Open the SQL Server Management Studio using Login information (Username & password or Window authentication )

Please note:- Above path can be changed depending on the Window version.

Step 2:- Once SQL Sever Management Studio is opened, opened the Object Explorer. By default it will be already opened. If it is not opened, then follow the below steps to open it.

Click on the View(Top menu) --> Click on Object Explorer.

Step 3:- Under the Databases node, select the database for Restoration and right click on it. Then choose Tasks, click on Restore and then choose the Database... as shown in the below figure. Here we choose the Company_DB as database.


Select Database for restore process

Step 4:- On the click of Database.. link,  we get the Restore Database window. In this window, we can select the database backup file, first by selecting the Device radio button and then click on the Add button, as shown in the below figure:-

Select Backup file location

Step 5:- When we click on the Add button, Locate Backup File Window is opened. On this window, we have to choose the backup file of the database as shown in the below figure.



Select Database backup file

Step 6:- When user selected the database backup file and click on the OK button, it will close the Locate Backup File window. Now on the Restore database window we can see the selected database backup file as shown in the below figure.


Selected backup file
Step 7:- When we click on the Options link, below screen will be shown


Database restoration Options
In this screen, we can see many options like overwrite the existing database and many others. We can leave this screen with default values. we usually choose the option of overwrite the existing database, but user can choose other options as per his/her requirement.

If you want to know this process and its all option in detail, you can refer below link of MSDN


Restore a Database Backup (SQL Server Management Studio)


DMCA.com

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 we have to follow the following path:-

START --> All Programs --> Microsoft SQL Server 2008R2 --> Click on SQL Sever Management Studio (SSMS) --> Open the SQL Server Management Studio using Login information (Username & password or Window authentication )

Please note:- Above path can be changed depending on the Window version.

Step2:- Once SQL Sever Management Studio is opened, opened the Object Explorer. By default it will be already opened. If it is not opened, then follow the below steps to open it.

Click on the View(Top menu) --> Click on Object Explorer.

Step3:- Under the Databases node, select the database for backup and right click on it. Then choose Tasks and then Back Up.. as shown in the below figure. Here we choose the Company_DB as database.


Database backup Fig-1
Choose Database for backup
Step4:- On the click on Back Up... link, it will create a Back Up Database window as shown below:-

Back up database Fig-2
Back Up Database Window
On this window, we have the options to choose the backup type. There are 3 backup types in SQL Server.

1) Full Backup 
2) Differential Back Up
3) Transaction Log

I will explain all 3 database types in another article. Full Backup type is the default Backup type and it is usually chosen for taking the database backup.

Also, if we want to choose another destination for storing the backup file, we can either remove the default backup destination first and then add the new destination for the database files or we can add the other destination file without removing the default destination by clicking on the Add button. On the click on Add button, it will open a new window from which we can choose our new destination. The backup file will have the extension of .bak. In this article, I will keep the default values.

If we click on the Options link from the left hand side menu, it will open the following screen.

  Backup database Fig 3
Backup database Option Screen
In this screen, we have the option of appending the new backup with the existing backup sets or overwriting the existing backup set. There are many other options available like backup like verify backup when finished and Perform checksum before writing to the Media for checking the reliability of the database backup. Let keeps the default setting. I will explains the Checksum in detail in another article.

When we click on the OK button, it will made the database backup either on the default destination which is given below:-

C:\Program Files\Microsoft SQL Server \ MSSQL10_50.MSSQLSERVER\ MSSQL\ Backup

or 

on the destination, which we have added by Clicking on the ADD button(see Back Up Database Window ).

DMCA.com

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 steps will be same for SQLServer 2005 and onward.

Step 1:- Open the SQL Sever Management Studio. For this we have to follow the following path:-

START --> All Programs --> Microsoft SQL Server 2008R2 --> Click on SQL Sever Management Studio (SSMS) --> Open the SQL Server Management Studio using Login information (Username & password or Window authentication )

Please note:- Above path can be changed depending on the Window version.

Step2:- Once SQL Sever Management Studio is opened, opened the Object Explorer. By default, it will be already opened. If it is not opened, then follow the below steps to open it.

Click on the View(Top menu) --> Click on Object Explorer

Once Object Explorer is opened, select the Databases node and right click on it . Then Select the open "New Database...".(as shown in the below figure)


Create database - Select database node
Create database fig-1
Step 3:- On the click of New Database option, New Database window is opened. In this window, we need to provide the database name and let the other values as default.(As shown in the below figure). Since we are creating the database Company_DB, we specify the name Company_DB as the Database name.


Create database fig-2
Specify database name

In this window, we can choose the owner of the database, Path, Initial size, File growth, maximum size for the Data file and the Log file. In this article, let them as default. 



Step 4:- In the "New Database" window, click on the left hand side Options link. It will open the below screen.

Create database Fig-3
Choose collation
In this screen, we can choose the database collation, Recovery Model and Compatibility level. Also there are many other options under the heading Other options, which we can change according to our need. But for creating a simple database, we can let these options has the default values.

Collation is required if we need to stored the data in the languages other than English. Many times we need to store the data of a country in its own language in to the database. For storing the data in different languages, we need to set the collation of the database as shown in the below figure:-

Create database Fig-4
Choose Collation
After that, Click on the OK button below to complete the database creation process. You can see the newly created database Company_DB under the main node Databases.



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  'Database name'
ON  PRIMARY   ( NAME = N'Database name', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database name.mdf' ,
 SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON   ( NAME = N'Database name_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database name_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

Database name:- This defines the name of the database which we are going to create.

On:-It specifies that the disk files which we are going to use for storing the data section of the database are explicitly defined.

Primary:-It specifies the primary data file that contains the startup information for the database and points to the other files in the database. User data and objects like tables, stored procedures, views can be stored in this primary file (or in secondary data files) . Every database has one primary data file. Primary file has an extension of .mdf

Log on:-It specifies that the log files which we are going to use for storing the logs of the database are explicitly defined. If LOG ON is not specified, one log file is automatically created, which has a size that is 25 percent of the sum of the sizes of all the data files for the database, or 512 KB, whichever is larger.This log file information is used for recovering database. Log file has an extension of .ldf.

Size :- It is the initial size of the file. When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified, but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

Filegrowth:- It specify the automatic growth increment of the file. Growth increment is the amount of space added to the file every time whenever there is a requirement of new space.

As per MSDN, Filegrowth can be specified in MB, KB, GB, TB, or percent (%) and if a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.

Also if we do not want to allowed additional space, we have to give 0 as the value to Filegrowth. Also If Filegrowthis not specified, the default value is 1 MB for data files and 10% for log files. The minimum value is 64 KB.


There are many other optional arguments like Maximum size, collation etc. But in this article, I have explained the main arguments which we needs to mention while creating database using Create Database command. 


For example, below SQL query will create the database Company_DB using Create database command.


CREATE DATABASE Company_DB

ON  PRIMARY   ( NAME = N'Company_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Company_DB.mdf' ,
 SIZE = 2048KB , FILEGROWTH = 1024KB )

 LOG ON   ( NAME = N'VCompany_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Company_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)



DMCA.com

Thursday, August 14, 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 some examples:-

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


Suppose, we have another table Empprojects, which contains the employees and their project relationship and it has the below table structure

create table Empprojects (empprojectid int identity(1,1), empnumber nvarchar (50), projectcode nvarchar(50))

Now suppose we want to get the records for the employees which are associated with the project having code "Proj002" or "Proj005", then we can use the following query:-

select * from employee where empnumber in (select empnumber from Empprojects where projectcode='Proj002' or projectcode='Proj005')


In the above query, we use the subquery "select empnumber from Empprojects where projectcode='Proj002' or projectcode='Proj005' " to fetch the multiple values in the IN clause.

Also suppose, we want to get the records of employee who worked in the first project having code 'Proj001'. We can use subquery for it as we see in the above example. But we know that that project is completed years back and only 3 employees having empnumber "A001" , "A002" and "A003" worked on it , then we can pass the empnumber of these 3 employee as predefined values in the IN clause as shown in the below query:-

select * from employee where empnumber In ('A001','A002','A003')




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



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 NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)

We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)
 

For example,

      Select AVG(salary) from employee


will give the given result:-

234000   


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

DMCA.com

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

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 )

Result

    king

DMCA.com

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)

DMCA.com

Wednesday, August 13, 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) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Abhinav','Golwalkar','abhinav@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)


For example, query to get the maximum salary of an employee from the table Employee is given below:-

Select Max (salary) from Employee

It will gives the following result:-

320000

DMCA.com

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)

DMCA.com

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 going to use the table Employee for explaining examples. Structure of this table is given below:-
CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


Following Insert queries will insert data into the table Employee

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Nidhi','Sharma','nidhi@abcmail.com',270000)


For example, in case of "AND", suppose we want to get records from the table Employee whose last name is "Sharma" and salary less than 300000, then we can use the following query:-

Select * from employee where EmpLastName='Sharma' AND salary < 300000

This query will return the record of the employee whose empnumber is "A004".

In case of "OR", suppose we want to get records from the table Employee whose last name is either Sharma or salary is less than 300000, then we can use the following query

Select EmpNumber from employee where EmpLastName='Sharma' OR salary  <  300000

This query will returns all the records:-

A001
A002
A003
A004


Tuesday, August 12, 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 key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Abhinav','Golwalkar','abhinav@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)
 


For example, query for finding the total amount of salary given by the company to its employee is given below:-

 Select sum (salary) from Employee

 Result

 1170000

DMCA.com

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 of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

function:-Function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Examples:-

1) Select Round(234.32,-3)
Result:-
0.00

2) Select Round(234.32,-2)
Result:-
200.00

3) Select Round(234.32,-1)
Result:-
230.00

4) select Round(234.32,0)
Result:-
234.00

5) Select Round(234.32,1)
Result:-
234.30

6) Select Round(234.32, 2)
Result:-
234.32

7) Select Round(234.32,3)
Result
234.32

DMCA.com

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

 { query_specification }
   EXCEPT
 { query_specification }


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) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)

Example

 For example, if we execute the below queries individually, we get the following result:-

Select Empnumber from employee where salary between 100000 and 250000

Result

A001
A002
A005


Select Empnumber from employee where salary>200000

Result

A003
A004
A005


Now, if we use Except command between these two above mention queries, we got the following result:-

Select Empnumber from employee where salary>200000
 EXCEPT
Select Empnumber from employee where salary between 100000 and 250000
 

Results

A003
A004

If we reverse the order of the SQL Queries, we get the different results

Select Empnumber from employee where salary between 100000 and 250000
EXCEPT
Select Empnumber from employee where salary>200000

Result

A001
A002


From the above example's results, we can say that Except command returns the distinct values from the left query that are not also found on the right query

DMCA.com