Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

SQL : How to check if a stored procedure exists before creation?

In this article we will learn how to check if a stored procedure exists before creating it. We can use the below script which will drop the proc if it exists and then recreate it. IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name...

SQl : How to replace a string in SQL server column ?

We know that the data can be changed inside a field by using update command. In this article I will guide you to replace part of the data without changing the entire data of the field. For this we will use replace command to replace a part of t...

SQL Tutorial ->Introduction to SQL

Chapter 1 Introduction to SQL Introduction to SQL : SQL stands for Structured Query Language or we can call it as "sequel" or "S-Q-L" . SQL is a query Language used to accessing and modifying information in a database.There are some common ...

Copying Data from one table into another

INSERT INTO SELECT statement: INSERT INTO SELECT statement is used to copy data from existing table into another table. Syntax-1: INSERT INTO tablename2 SELECT * FROM tablename1; Syntax-2 INSERT INTO tablename2 (columnname(s)) ...

SQL Tutorial -> Sql Syntax

<-- Chapter 1 Introduction to SQL Chapter 2 SQL Syntax SQL Syntax : SQL syntax is basically followed by sql commands which modifies the database tables such as "users" , "students" etc. All the SQL statements always starts with the key...

SQL : How to pass an array of parameters to stored procedure using XML?

In this article we will see how to pass an array of parameters to a stored procedure using xml. For illustration purpose we will be passing the below list of id's : <ids> <id>1</id> <id>2</id> <...

SQL : How to get duplicate rows based on specific fields in table ?

In the following article we will go through a solution to a very common requirement of getting duplicate rows from SQL Server table based on specific columns. Let us first create a table and add sample data to this table. Col1 in the table is an...

SQL : How to remove duplicates in SQL table?

In the following article we will go through a solution to a very common requirement of removing delete duplicate rows from SQL server table. Let us first create a table and add sample data to this table. Col1 in the table is an identity column. ...

SQL : How to get count of duplicate records?

In the following article we will go through a solution to a very common requirement of getting count of duplicate rows from SQL server table. Let us first create a table and add sample data to this table. Col1 in the table is an identity column....

SQL : How to get Nth record in SQL Table ?

In the following article we will go through a solution to a very common requirement of getting Nth record in a SQL server table. Let us first create a table and add sample data to this table. Col1 in the table is an identity column. CREATE TA...

SQL : How to find all tables containing column with specific name ?

Many times we come across a requirement of finding names of all tables which contain specific columns. We can get the answer by using the below query: SELECT COL.name AS ColumnName, TAB.name AS TableName FROM sys.columns COL JOIN sys.tab...

SQL : Difference between UNION and UNION ALL

Both UNION and UNION ALL operators are used to combine the results of two or more SELECT statements. However the two differ as below: 1) UNION performs a DISTINCT on the result set, removing any duplicate rows.There is a performance hit when ...

Handling Duplicates using INSERT IGNORE

INSERT IGNORE: INSERT IGNORE is used for handling duplicacy in a table as it ignores the query if the data is already present in the table and if the data does not exist then new row will be inserted. Syntax: INSERT IGNORE INTO tablename...

Inner Join

Using Inner Join: It selects rows from both tables as long as there is a match between the column on which join is applied. Inner join is used to fetch data from more than one table. Syntax: SELECT columnname(s) FROM tablename1 INNER JO...

Pattern Matching using REGEXP

REGEXP: It is similar to LIKE which is used to fetch data based on regular expression from a table. Syntax: SELECT columnname(s) FROM tablename WHERE columnname REGEXP pattern; Example 1: SELECT name FROM Employee WHERE name R...

DROP Statement

DROP Statement: With the help of drop statement we can easily delete a table or a database and it is a DML(Data Manipulation Language) command. Syntax to drop a table: DROP table tablename; Example: DROP table Employee; It ...

SQl:Create,Drop and Select Database Commands

1. CREATE DATABASE Create database is a type of command used to create new SQL database. Here is the syntax of CREATE DATABASE statement:- CREATE DATABASE DatabaseName; 2.DROP DATABASE Drop database is a type of command used to...

IN operator

IN Operator use: It allows us to put multiple values in a where clause . With the help of this we can compare multiple values wtih where. Syntax: Select columnname(s) from Tablename where columnname IN(value1,value 2, .... value n); ...

Fetching Second Highest Salary

If we want to find highest salary we can use max() function simply. Example: SELECT max(salary) FROM Employee; It will return the highest salary from Employee table. But if we need to find the second highest salary then we have...

SQL Joins Introduction

SQL JOIN is used to combine data from two or more different table based on a common field between them in a relational database. There are 5 types of JOIN: INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN CROSS JOIN INNER JOIN:...

Different types of SQL Joins

The SQL Joins are mostly used to combine records from two or more tables in a database for getting data. A JOIN defined as combining fields from 2 tables. There are several operators that can be used to join tables. The opertaors that are used...

Prevent SQL Injection Attack

SQL injection is a technique that exploits a security vulnerability within the database layer of an application.Using this technique the attacker tries to run his own malicious query against the database.The key component of these malicious queri...

Function in SQL

Function in SQL There are two type of function in sql SQL aggregate function 1) AVG() - Returns the average value SELECT AVG(column_name) FROM table_name 2) COUNT() - Returns the number of rows SELECT COUNT(column_name...

Comparison of CTE, temp table and table variable

This post describes the major differences between CTE, temp table and table variables. 1) CTE CTE stands for Common Table expressions. These are simple select queries and they do not create physical space in tempDB. Unlike temporary tabl...

Difference between truncate and delete

The following article captures the difference between truncate and delete. 1) Rollback Possibility: It is possible rollback a DELETE operation but not a TRUNCATE operation. 2) Impact on Identity TRUNCATE resets identity of a t...

Call a procedure with in procedure in SQL server

Call a procedure with in procedure in SQL server :- While working in sql Some times you need to Calling one stored procedure within another stored procedure. So you can implement this using below sample code:--- //Store Procedure 1 CREATE...

Joins In SQL

Joins There are different type of joins in SQL 1.Inner Join-Return all the values if there is a match in both the table. 2.Outer join -left outer join-Return all the values from left table and the values matched from the right table. ...

SQL Joins

Joins Joins in SQL are nothing but a technique to join the two table based on a common field. OrderID CustomerID OrderDate 10308 2 1996-09-18 10309 37 1996-09-19 10310 77 1996-09-20 Custom...

Primary Key

Primary Key A primary key in a table is a unique key that is the element in this column are unique and can not be null. It is used to mantain the integrity. It uniquely identify the row in the table. It is a constraint on the table. ...

Indexing

The index is used to search the data in a table. The index is basically a data structure that search our data. It is applied usually on a column of a table. It is usually a B-tree but can also be hash table and other depending upon the ...

Referential Integrity

Referential Integrity Referential Integrity is a concept in which the tables shares a relationship between them and that should be consistent. Ex: If we have a table called Employee and the other Employee Salary and columns in them of na...

SQL Alter Table

SQL Alter Table Using this query you can add , modify or delete columns in a existing table . It is also used to change the table name . Syntax -> Alter table table_Name Add column_Name column_Definition ; If you want to add multip...

SQL Truncate Table

SQL Truncate Table Using this statement you can remove all data of rows or column ( complete data ) not table. In this there is no where clause but where clause is available in Delete statement. Truncate table is faster than Delete statement ...

Introduction to SQL

Overview of SQL : SQL is a scripting language for the database handling. SQL is called as Structured Query Language. SQL is used to handle relational database, we can create, update, delete, and fetch records from the database. SQL is a powerful...

Warning of Saving Changes in not permitted in SQL

Warning: "Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that cant be re-created or enabled the option Prevent saving changes that r...

Get all running/sleeping processes in database

Hi All, Some times we need to know the all running/sleeping process in database. We can use the below code:-- SELECT 'These processes are using database' AS Note ,[Database]=DB_NAME(dbid), spid, last_batch, status, hostname, loginame FRO...

Get the List of all tables using foreign key

Hello All, Many time, We want to know that, What are all the tables who have referenced a column as a "foreign key" which is a "Primary Key" of other table. Example:-- In a database I have one Company table which stores company information...

How to improve SQL database design and performance

In this era of software development technology, the main concern in development should be the best performance. As we already aware that database is the backbone of any software. So whilst designing the database we need to keep a few things in mi...

How to get next Date excluding weekends with start date and the number of working days using SQL ?

Hi All, While working with SQL, I came across the requirement to get the business days only. I had the number of working days and the starting date. Now the requirement was to get the next date by excluding all the Saturday Sunday and only to co...

SQL Joins and it's Uses

In SQL **joins** are used when we want to select data and information from two or more tables on the basis of some relationships between one or more columns in tables. SQL Joins are used to relate information in different tables. Thus,we ca...

Creating xml from SQL query

This blog will show you how to create an xml from sql query having sql data. Write down the followng query to generate xml:- WITH XMLNAMESPACES ('http://www.dossia.org/v2.0/xml/phr' as phr) Select OrderResultID,OrderID ,PatientID ,SpecimenNum...

Restricting data in Oracle

This blog will help you to restrict data from a database. You can restrict the rows returned from the query by using the WHERE clause. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condi...

SQL - Generating XML 2

Every XML has two elements i.e. 1) Attributes and 2) Value SELECT magazineId AS '@id' , magazineName AS '@name' , CoverPage AS '@coverPage' , isActive AS '@status' , publishDate AS '@publishDate' , expiryDate A...

SQL SERVER - Generating XML - 1

Following is the XML which we want to create: SELECT magazineId , magazineName , coverPage , publishDate , expiryDate , isActive ,( SELECT ( SELECT ( ...
1 3 next
Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: