Featured
-
Find out a particular column throughout the database in Postgresql
Previously got a situation where I needed to get t
by Nitika.Verma
Tags
SQL NULL Values
missing or unknown data is considered as null values.It indicates that value is not applicable for a set of attributes. NULL Value is different from an empty or zero value
To test for NULL values we use comparison operators, such as =, <,...
SQL As statement
If we want to give temporarily name to a table then we can do easily with " as " statement .
Sometime permanent name of table is very confusing so we need temporary name of table to make easy understanding .
Syntax
select old_col_name as...
SQL Random Function
Using this function we can retrieve random row from a table in database . Mostly it is used in online exam , for random passwords etc .
Every Database has their own syntax for random function .
Syntax for MySQL Database
select col_name from...
SQL Last ( ) Function
It work similar to Last ( ) function but it return the last column from a table in database .
Syntax
select last ( col_name ) from table_name ;
It support in Ms Access Database . But for other database there is different statement for L...
SQL First ( ) function
If you want to select first coulmn from a table in database then you can do with first ( ) function . It return the first column from a table .
Syntax ->
select first ( col_name ) from table_name ; // This is valid for Ms Access Database o...
SQL Top Statement
Using this SQL Top statement we can select the topmost( first element from top ) element from the table in database . We can also select one or more element from the top in a table . This clause tell how many rows is returned .
Syntax ->
...
SQL Select Coun statement
Count ( ) In SQL Database this is a function which return no of rows from a table in Database . Generally this is used in SQL select statement and count the no. of records in a table .
Syntax ->
select count ( expression_name ) from table...
SQL Unique/DIstinct Statement
Basically Unique and Distinct both are same means both do the same work , Unique statement was used earlier by the oracle database but Now a days Distinct statement is using by the oracle ( Now it is the official keyword ) , Though Unique stateme...
SQL Select Statement
SQL query is used to retrieve selected row or column from the database based on the given condition . It is also used to retrieve data from a table in database .
Syntax ->
Select expression from table_Name where clause ; // Expression in...
How to Copy entire date into other table in SQL Database
SQL Copy Table
If you want to copy a SQL table into another table in the same SQL database then you can do with the copy statement .
Syntax ->
select * into destinationTable from sourceTable ;
Example ->
**Student name** ...
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
...
SQL SELECT DATE
SQL SELECT DATE
if you want to retrieve a date from database,use this staement. For eg. you want to find a particular date from a database, this statement is the answer.
1)For example: let's see the query to get all the records after '201...
How to Rename Table Name in SQL Database
SQL rename statement is used to change the name of the table in database . Sometime we want to change our table name i.e we want to give some useful name of our table . SO by using this statement we can do the same .
Syntax
Rename oldTableNa...
SQL Table
SQL Table is used to store the data in terms of rows and column . It is the very simple form for data storage .
Example ->
**Student name** **Student Age** **Student Id**
Mukesh 23 1
...
SQL Database
SQL Create statement is used to create a database .
Syntax
create database databaseName ;
Now if you wanna to add tables in that database then first you have to use that databse using USE DATABASE_NAME and then use create table statemen...
SQL Database
SQL follow the unique set of rules that is called syntax ,follow the rules given below
SQL database is not a case sensitive . Generally in SQL database keywords are written in uppercase
SQL statements are dependent on text lines .
You can d...
Recovery Models in SQL server
There are three types of Recovery model in SQL server
Simple recovery model -- gives you the backup to replace the entire database in the event of a failure . In simple recovery model data is recoverable only to the most recent backup ....
SQL Joins
The process of combining rows of two or more then two tables based on a common field between them are known as SQL joins.
Types of SQL joins :
INNER JOIN: It is returns rows only when at least one row matches from both tables.
LEFT...
RAID
RAID or Redundant Array of Independent Disks is a method by which we store the same data in several places on different hard disks. By Implementing RAID we can Make Input/Output operation to overlap in good proportions and it also Improve the Per...
Recovering mysql from backups
Here we will learn how to restore the database from a backup using mysql commands
we need to follow the following steps for the process
We must have a database dump that we want to restore on the machine with the correct
database ...
Example of Stored Procedure using INOUT mode parameter
Example of Stored Procedure using INOUT mode parameter:
INOUT mode is used when you pass and want to get the result using same parameter. Using INOUT mode parameter we can pass the argument to the Stored Procedure and also can get the result wi...
How to create Stored Procedure with parameter
Stored Procedures : Stored Procedures are the functions which sits on the database side and provide the services to the user by calling it. Stored Procedures are same as we like functions in our code. Stored Procedures can be created with having ...
Innodb data base and Row Level Locking
MySQL provides a storage engine called InnoDB. It is a default storage engine for MySQL 5.5 and later. InnoDB supports ACID-compliant transaction features, along with foreign key support. InnoDB is a general-purpose storage engine that is used fo...
Hot And Cold Backups in MySql
While taking backup our goal is to keep the application, database system, and web site, in running condition while we are taking the backup.
Database backup technique can be classified mainly into two categories:
- Hot Backup
- Cold ...
Indexes in mysql: Types and How to create indexes
Database index help in speeding up the retrieval of data from tables. When we query data from a table the following process is followed:
-First MySQL checks if the indexes exist
-Indexes are used by MySql to select relevant rows of the ta...
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...
Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT
In most of our applications, We need last inserted identity value in SQL Server table. For getting the last inserted identity value we have multiple options:
@@IDENTITY
SCOPE_IDENTITY
IDENT_CURRENT
All these three return the l...
ORDER BY keyword in SQL
We use ORDER BY to make our record list in a ordered manner in the sense of descending /ascending sort.
Syntax:-
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC
Note:- User can ORDER BY more then o...
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...
Encryption/Decryption function in Sql Server
To encrypt and decrypt a string we have a predefined functions in Sql Server 2008 that are easy to use. Suppose we want to save some data that doesn't need to be exposed as a plain text then in such case we can use these functions, in real scenar...
How to display SQL row data as a column wise with user specific column name
Hello all,
The below tutorial will help you in displaying SQL row data as a column wise with user specific column name.
here, I've a table with #Temp1 name that contains the following columns Id, ReferenceId and Total.
SQL Code:--
CREA...
Find a particular text in all stored procedures
Below mentioned command is used to find a particular text in all stored procedures
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%EmailOnTRX%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(...
Restoring SQLServer Databases from backup files
There are many ways to restore MSSQL Server database from backup files(.bak) like using tools available in SQLServer Management tools. But we face problems when the backup was not created on the same machine or it was shared by someone else or i...
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...
Advantages of using stored procedures :
Stored Procedures
stored procedures is the collection of Sql statements which is stored in data base .
below are the advantage of stored procedures :
1.stored procedures reducing network traffic and boost application performance. .
...
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...
How to fetch data from XML file and insert into SQL Database
Fetch data from XML file and insert into SQL Database
Step1. Create a Stored procedure in Sql database which takes one input parameter and one output parameter which returns Success /Failure
Create PROCEDURE [dbo].[SP_Insert_...
ACID property of database
Full form of ACID is Atomicity Consistency Isolation Durability . ACID is the concept that database professional follow while evaluating relational database and application architecture. For a reliable database all of the following attributes sho...
Finding Distance on basis of Longitude and Latitude from Database
Below is an example of finding all the user who are stored in db and lies in the given range of Distance on basis of Longitude and Latitude.
CREATE proc [dbo].[SP_UserByDistance]
(
@UserID uniqueidentifier,--UserId
@Date datetime, --Date
@...
Migration in SQL Server
Migeration in SQl Server :
There are several options if one wishes to migrate a database from a SQL Server 2005 to SQL 2008 Server. First of all transferring a database from any version of SQL Server 2000 and above to 2008 version. This Wizar...
Mirroring in SQL Server
Data mirroring refers to the real-time operation of copying data, as an exact copy, from one location to a local or remote storage medium. In computing, a mirror is an exact copy of a dataset. Most commonly, data mirroring is used when multiple e...
How to Insert Data From One Table to Another Table in SQL
How to Insert Data From One Table to Another Table in SQL
step1: Create two tables ContentTable1 Table and ContentTable2 Table
CREATE TABLE ContentTable1
(
ContentId1 int identity(1, 1) not null,
ContentHeading1 nvarchar(100) NULL,
...
SQL query to add column's values, containing null values
Write a sql query to add column's value , containing null values also in records. But the sum of the added columns must be numeric only. Output cannot be null. It can be achieve by two ways.
SELECT (COALESCE(Column1, 0) + COALESCE(Column2, ...
How To Connect With SQL Server DB .
Below is the C sharp code to connect with the sql data base in .net
usingSystem.Data.Sql;
usingSystem.Data.SqlClient;
// FUNCTION TO CONNECT TO THE SQL SERVER DATABASE
privatevoidconnectToSQLServer()
{
// SQL CONNECTION...
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...
