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 Injection
<-- Chapter 20: SQL DELETE
Chapter 21
SQL INJECTION
SQL Injection is an injection attack wherein attacker is able to submit a database SQL command which is executed by a web application exposing back-end database. Attacker can add SQL ...
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
...
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 <= 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 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 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 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
...
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...
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...
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 ...
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 ...
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);
...
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...
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 (
...
