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 Server : Instead of Trigger
A trigger is a special kind of a store procedure that executes in response to specific actions on the table like insertion, updation or deletion. It is a database object which is bound to a table. Triggers cannot be explicitly invoked. The only ...
Schemas in SQL
Schema or Structure can be defined in SQL
A schema is a collection of logical structures of data objects. A schema is associated with a database user and has the same name as that user. Each user have a single schema. Schema is mainly collecti...
How to use INSERT INTO SELECT in SQL
By using INSERT INTO SELECT Statement one can copy data from one table to other existing table.
Syntax:
To copy all the columns from one table to another table:
INSERT INTO table_name1 (SELECT * from table_name);
To copy only the selec...
SQL Server Management Data Warehouse (MDW)
Management Data warehouse is a database containing a warehouse of data useful for managing SQL Server. Ensuring the excellent and high performance for all the users and customers is a high priority for Database Administrators. Troubleshooting SQL...
Compression and Decompression in SQL Server 2016
In the upcoming version of SQL 2016 a new component of COMPRESS and DECOMPRESS T-SQL functions is added. SQL Server 2016 provides built in functions for compression and decompression.The COMPRESS and DECOMPRESS functions can store and retrieve da...
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);
...
Normalization
Database Normalization
Normalization is a technique of managing records in a database. It is an approach to eliminate data redundancy and anamolies . It also removes data duplicacy in a record. It helps in data update,delete and insert anamol...
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...
SQL CHECK Constraint
It limits the value range which is mentioned in column.
Example of check constraint
CREATE TABLE Persons
(
PId int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT ...
SQL SELECT COUNT
To return the number of rows in a query,we use COUNT. It counts the numbers of records.
the syntax of SQL COUNT statement.
SELECT COUNT (expression)
FROM tables
WHERE conditions;
SQL SELECT COUNT(column_name)
SELECT COUNT(...
ALTERNATE KEY IN SQL
Keys are used to uniquely identify records in a table. it is used to establish relation within a table.
Primary keys,Foreign keys, candidate keys and alternate keys are used in tables which identify rows.
All keys other than primary key are ...
Update query with inner join
Hello readers, today we will discuss about the "SQL query with INNER JOIN".
UPDATE
(SELECT table1.value as OLD, table2.CODE as NEW
FROM table1
INNER JOIN table2
ON table1.value = table2.DESC
WHERE table1.UPDATETYPE='blah'
) t
SET t...
How to search a specific column in all the tables of database?
Hello All,
Working with the SQL Database, I wanted to search a specific column in all the tables of database and to do that I use the following code packet.
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LI...
SQL Views
A view is a virtual table which is stored in database with an associated name .Using views query we can select specific data from a large table . Suppose there is large database of any college , so i want to view the records of any one branch fro...
SQL Between Operator
This operator is used to get the data from a combination of " greater than equal " and " less than equal " condition .
Syntax :
select column_name1.......column_nameN from table_name where column_name between value1 and value2 ;
Example ...
SQL Wildcards
This wildcards is used when we want to match a string pattern . And this can be do with the help of wildcards only .
In SQL there are two types of wildcards :
% : Percentage sign which represent 0 , 1 or more characters .
_ : Underscore whi...
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 Like Operator
This operator is used when we want specific pattern from a column in a table .
% : This is called wildcards , it is used before and after the pattern .
Syntax :
select column_name1 .......column_nameN from table_name where column_name like p...
SQL And & Or Operator
These operators are used where we want to get records based on more than one condition .
And : From this operator we can get data if both the condition ( i.e first and second condition ) are true .
Or : From this operator we can get data if eit...
SQL Min ( ) Function
This function is used to get the minimum value from the selected field .
Syntax :
select min ( column_name ) from table_name ;
Example :
Table name : Employee_Info
Id Employee_name Employee_Age Employe...
SQL Max ( ) Function
This function is used to get the maximum value from the selected field .
Syntax :
select max ( column_name ) from table_name ;
Example :
Table name : Employee_Info
Id Employee_name Employee_Age Employe...
SQL LCASE ( ) Function
This function is used to convert , value of column in lowercase . If value of column already in a lowercase then it doesn't covert . It convert only if value of filed is in uppercase .
Syntax :
select lcase ( column_name ) from table_name ;
...
SQL UCASE ( ) Function
This function is used to convert value of column in uppercase . If value of column already in a uppercase then it doesn't covert . It convert only if value of filed is in lowercase .
Syntax :
select ucase ( column_name ) from table_name ;
...
SQL Mid ( ) Function
This function is used to get the character from a text field .
Syntax In MySQL :
select mid ( column_name , start [ starting_length , ending_length ] ) from table_name ;
Starting length : It is mandatory .
Ending length : It is optional...
SQL Len ( ) Function
SQL Length ( ) function is used to get the length of the string .
In MySQL : Length ( ) ;
In Oracle : Length ( ) ;
In SQL : Len ( ) ;
Syntax :
select len ( column_name ) from table_name ;
Example :
Table name : Employee_Info
...
Update one table with data from another table in sql
Update one table with data from another table :-
Some time we need to update more than one column in a SQL table with values from column in another table.So we can achieve this using join. Please refer the below example:-
Table Structure &...
SQL Avg ( ) Function
Avg ( ) function is used to return the average of numeric column . If in a table there is null value then it ignored that value .
Syntax :
select avg ( column_name ) from table_name where clause ; // where clause is optional
Example :
...
SQL Composite key
Sometime in a table we need two or more column to uniquely identify each row . Composite key is the combination of two or more Primary key . Primary key or candidate key come from the composite key .
Syntax in SQL :
create table table_name co...
SQL Foreign Key
Foreign Key is used to establish a link between two tables . It always define in reference of Primary key ( another table ) . It act as a Primary key in its own table and foreign key for another table .
Difference between Primary key and Foreign...
SQL Primary Key
Basically Primary key is used to identify each row in a table . In a table Only one Primary key can be present . When in a table , there is more than one primary key then it would be called composite Primary key . Composite key is used when we ne...
SQL Cross Join
SQL Cross means cartesian product of the rows of two or more tables . It return all the rows of the table when each row of the first table combined with the each row of the second table . It is also known as cartesian or cross join .
Cross Join ...
SQL Full Join
In full join all the records of both the table will come in resultant table , it doesn't depend on matching the fields . It is the combination of both left and right join . If matches not found then it will return null .Sometime it is also called...
SQL Outer-Right Join
2) Right Outer Join -> It is also known as Right join . In right join all the content of right table will come in resultant and from the left table only matching rows will come . If no rows is matched from left table then null value will be re...
SQL Outer-Left Join
In this Join all the content of both table will come in resultant table either they will be matched or not .
Outer Join are of two types :
1) Left Outer Join -> It is also known as left join . In left join all the content of left table will ...
SQL Join Query
SQL Join query is used to join the two tables i.e add or combine two or more tables .
This clause take data from two or more table in database and then combine them to produce new table .
Joining is based upon the common fields from the tables ...
SQL Delete Statement
Using Delete statement we can delete the record from a table in database . We can delete single or multiple row from a table in database .
Syntax :
delete from table_name where clause ; // where clause is optional
Example :
Table nam...
SQL Update Statement
SQL Update statement is used to modify the records in table in database . We can modify either by change data or deleting data or to update the existing data .
We can modify the rows based on condition and condition come with where clause .
Syn...
SQL Insert Statement
SQL insert statement is used to insert the data in a table in database . Using this statement we can insert single or multiple data in a table .
We can insert the data by two ways :
1) By SQL insert into statement : we can insert data either by...
SQL Order By Clause
With the help of SQL Order By statement we can sort our data in a table either in ascending order or descending order based upon the user, what he wants.
Note -> Some Databases have already sort the data order by default .
Syntax :
select...
SQL Null Meaning
What is Null ? Null means no data or missing data or we don't have a data in a table . If you think Null means 0 ( zero ) than you are wrong .
There can be two condition for Null
1) when SQL is Null
2) when SQL is not Null
If in a table yo...
SQL sum function
Sum ( ) , it is a function which return the aggregate sum of any expression , like salary of multiple employee , sum of population in any particular area etc .
Syntax :
select sum ( Expression ) from student where clause ; // expression can b...
SQL Date Statement
Basically Date is used to extract the data from a table in database . Suppose you want to find and extract the data from database for particular date then you can do this with the Date statement .
Example ->
Table name -> student
Stu...
SQL Join Statement
Basically this tag is used with select statement to find and retrieve the data from multiple tables . And this can be done with the help of join query .
suppose i have two tables , name is student and teacher then i can add the columns with the...
SQL In statement
In SQL Database " In " is used as a operator. It help user to reduce the " OR " statements, means In operator avoid multiple statements .
It is used withe select , insert , update or delete statements only .
Advantage ->
It reduce the " or ...
