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 Tutorial -> Sql Delete
<-- Chapter 19: SQL UPDATE
Chapter 20
SQL DELETE
DELETE statement is used to delete a particular data Rows from Database Table with the help of WHERE clause. Without WHERE clause all rows will be deleted.
Lets see an example from th...
SQL Tutorial -> Sql Update
<-- Chapter 18: SQL INSERT
Chapter 19
SQL UPDATE
UPDATE statement is used to update existing table data in database table with the help of WHERE clause.WHERE clause tells which record is to be updated.
Lets see an example from the b...
SQL Tutorial -> Sql Insert
<-- Chapter 17: SQL ORDER BY
Chapter 18
SQL INSERT
INSERT INTO is the SQL Statement used to insert data in database table in the form of rows and columns.
Lets see an example from the below table "employees" :-
employee_id
...
What is "Back-End Testing" and "DB Testing"?
"Back-End Testing" and "DB Testing" is the same term. In this we use to verify the DB with regard to the data which we submit though UI / getting delivered on the UI. Major entity around which the whole testing activity revolves is "Data". Some o...
SQL Tutorial -> Sql ORDER BY
<-- Chapter 16: SQL And & Or
Chapter 17
SQL ORDER BY
ORDER BY is the keyword used to sort the database table data by one or more columns. ORDER BY sort the Records in Ascending order by default. To sort the Record in Descending Ord...
SQL Tutorial > Sql And & Or
<-- Chapter 15: SQL <= Operator
Chapter 16
SQL And & Or
AND/OR Operators are used in SQL statements to filter the Records based on more than one condition. AND Operator display the records in which all the specified conditions a...
SQL Tutorial > Sql <= Operator
<-- Chapter 14: SQL >= Operator
Chapter 15
SQL <= Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-1...
SQL Tutorial > Sql >= Operator
<-- Chapter 13: SQL < Operator
Chapter 14
SQL >= Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-10...
SQL Tutorial -> Sql < Operator
<-- Chapter 12: SQL > Operator
Chapter 13
SQL < Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-102...
SQL Tutorial -> Sql > Operator
<-- Chapter 11: SQL BETWEEN Operator
Chapter 12
SQL > Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-...
SQL Tutorial -> SQL BETWEEN Operator
<-- Chapter 10: SQL LIKE Operator
Chapter 11
SQL BETWEEN Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-...
SQL Tutorial -> Sql LIKE Operator
<-- Chapter 9: SQL != Operator
Chapter 10
SQL LIKE Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-102
D...
SQL Tutorial -> Sql != Operator
<-- Chapter 8: SQL = Operator
Chapter 9
SQL != Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-102
Docto...
SQL Tutorial -> Sql = Operator
<-- Chapter 7: SQL NOT IN Operator
Chapter 8
SQL = Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-102
D...
SQL Tutorial -> Sql NOT IN Operator
<-- Chapter 6: SQL IN Operator
Chapter 7
SQL NOT IN Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-102
...
SQL Tutorial -> Sql IN Operator
<-- Chapter 5: SQL Where
Chapter 6
SQL IN Operator
Lets see an example from the below table "employees" :-
employee_id
name
code
designation
salary
101
ABC
E-101
Engineer
12000
102
DEF
E-102
Doctor
80...
SQL Tutorial -> Sql Where
<-- Chapter 4: SQL Distinct
Chapter 5
SQL Where
SQL Where : SQL Where clause extract or filters those records which matches the specified condition. Syntax for Where clause is used below
SELECT *
FROM tablename
WHERE condition
...
To Run Stored Procedure everyday
For doing that we need to create procedure first and also define the repeating interval to call it again
We will create procedure named getnotification from the particular database that gets fired after 3:30 hours on a daily basis.
Wh...
SQL Tutorial -> Sql Distinct
<-- Chapter 3: SQL Select
Chapter 4
SQL District
SQL District : District is a keyword which is used to fetch unique column values from database table OR we can simply say that ignores the column duplicate values. Syntax for district ke...
Different types of SQL Commands
SQL(Structured Query Language) is a computer language used for storing, retrieving and management of data in Relational Database Management System(RDBMS).
The commands of SQL can be categorize into following categories :
1. DDL : DDL stands for...
SQL Tutorial -> Sql Select
<-- Chapter 2: SQL Syntax
Chapter 3
SQL Select
SQL Select :
SELECT command describes that through which we can fetch data from MYSQL database tables OR we can say, It is used to retrieve/select data from MYSQL database.
SELECT comma...
SQL : How to get last N records based on ordering of a specific column in SQL Server ?
Sometimes we may be required to get the get last N records of a table in SQL server based on ordering on a specific column . In this post we will create a query to get the result. First let us create a table which we will use in our query.
CR...
SQL : How to reset identity seed after deletion of records in SQL server ?
By using the seed value for every new record Microsoft SQL Server's identity column generates sequential values. In this post we will learn how to reseed an identity column.
Following is the command to reset the identity property :
DBCC C...
SQL : How to get column names of a table in SQL Server ?
Often we may be required to get the names of all columns in a table in SQL server. In this post we will see the different options available in SQL server to fetch the column names.
1) We can use the below query to get the column names.
SELE...
SQL : How to Delete using INNER JOIN in SQL Server ?
In this post we will see how to use JOIN for deleting data from SQl server table. Let us first create tables which we will use for understanding the deletion process using JOINS.
-- Create table1
CREATE TABLE #Table1 (Col1 INT, Col2 VARCHAR(5...
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...
Encryption in SQL
For providing security and privacy we need to store data in encrypted form.
In SQL this can be done with series of steps:
CREATE TABLE dbo.Employees(
EmployeeID int primary key,
EmployeeName varchar(50) NULL,
n...
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 ...
import assemblies in SQL
In SQL Server there are situations when you need to import assemblies to perform a specific task
For doing that you need to write the path where assembly is present and provide the permissions to the assembly and associating with the particu...
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...
To find duplicate records in SQL
In SQL if we dont have used primary keys then our id gets repeated accidentally or unintentionally.
In this case we need to find out how many records are same. We need to find out the same tuples or rows from a table.
For doing that...
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....
APEX SQL
APEX SQL
APEX in SQL is a product from Microsoft that is used for synching of data stored on different servers.
We have our main data stored in a main server and replica of those data are stored on different servers.
Consider...
Temporary Tavles in SQL
Temporary tables in SQL are used for manipulation of data for a short period of time.
It can be used as a accumulator like we have in microprocessor which acts as mediator to store and perform operations into it.
Temporary table in ...
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 ...
Endpoints in SQL Server
Endpoints in SQL
For providing TCP/IP communication between your SQL and other applications you need to create end point for that .
Creating end points is a series of steps
Message
Contract
Queue
Service
Route...
CTE in SQL
CTE Common Type Expression
For providing pivoting in tables say you want to view your data in such a form such that rows comes in form of columns and columns in form of rows. For doing that SQL provide facility to use CTE in SQL
Ex :
Suppo...
SQl Server : How to insert results of a stored procedure to a table?
In the following article we will learn how to insert results of a stored procedure to a table. There are two approaches to do this depending on whether we know the table schema beforehand or not.
We will be using the following stored procedure t...
How to delete duplicate data in Oracle SQL?
Let's create a table with duplicate rows in SQL.
create table CustomerDuplicate(customer_id int,customer_name varchar(20))
insert into CustomerDuplicate values(1,'Adam')
insert into CustomerDuplicate values (1,'Adam')
insert into Customer...
SQL Server : How to insert values in identity column programmatically?
Identity field is typically used as a primary key in database. When a new record is inserted into a table, this field automatically assigns an incremented value to this column.However, inserting our own value into this column is not straightforwa...
SQL Server : How to truncate a table being referenced by a FOREIGN KEY constraint ?
Many times we need to truncate a table which has an FK constraint on it. Typically we get the following error:
Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.
The solution to the above problem i...
SQL Server : How to shrink or clear the transaction log file ?
Many times the large size of transaction log file (.ldf) in Microsoft SQL is too big which leads to performance issues and loss of valuable disk space.Therefore it's imperative to periodically do database maintenance.
In order to clear or ...
SQL Server : Comparison of stored procedure and UDF
Stored Procedures are pre-compile database objects which are compiled for first time and execution plan is saved for further use. However UDF is compiled and executed every time when it is invoked.This article provides a detailed comparison of st...
SQL Server : Use of sysobjects table
SQL Server sysobjects Table contains one row for each object created. In other words, it has a row for every constraint, default, log, stored procedure etc in the database.The two columns most useful in this table are sysobjects.name and sysobje...
