Featured
-
How Regression Testing Detects Integral Errors In Business Processes
Humans are forever changing and evolving and so to
by kristina.rigina -
Get Display Banner Advertising Opportunity on FindNerd Platform
“Do you have a product or service that nee
by manoj.rawat -
Android O Released with Top 7 New Features for App Developers
Android was founded by Andy Rubin, Rich Miner, Nic
by sudhanshu.tripathi -
Top 5 Features That Make Laravel the Best PHP Framework for Development
Laravel is a free open source Web Framework of PHP
by abhishek.tiwari.458 -
Objective C or Swift - Which Technology to Learn for iOS Development?
Swift programming language is completely based on
by siddharth.sindhi
Tags
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 (
...
