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
Alias
Alias is basically renaming a table temporarily for a sql statement.
The rename is done only for a particular statement is temporory.
ex
The syntax of table alias is as follows
SELECT column1, column2....
FROM table_name AS alias_n...
Subquery
A subquery is a query within a query embedded within where clause
It is used to return the data that will be used in the main query as a condition to the data that is retrieved.
SELECT, INSERT, UPDATE, and DELETE statements along with the o...
SQL datatype
SQL data type is an attribute that tell us the type of the object.
SQL server has 6 type of datatype
Exact numeric data type
1. bigint- Range start from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
2. int- Range start from ...
Differece between ROW_NUMBER(), RANK() and DENSE_RANK() in SQL Server
ROW_NUMBER()
RANK()
DENSE_RANK()
All of these three functions are used to calculate the Id of row but in different way.
I am using the below script for examples
CREATE TABLE Marks
(
SubjectId INT,
Marks INT
)...
SQL: How to display all the records related to common id
I was stuck in the following issue. Issue was related with retrieving common records in group. I have to display records which are common with each id.
Following records are inserted into the table "mytable":
id | ti...
SQL wildcards
SQL wildcards are used within a table to search for data. Usage of wildcard characters in SQL call for SQL LIKE operator, which enables to hold a comparison between a value and similar values.
Following are the two types of wildcard oper...
Difference between DELETE and TRUNCATE commands?
Delete command in SQL removes the ROW based on the condition provided with the WHERE clause, if no WHERE clause is provided it deletes all the row from the table.
Syntax for delete command:
DELETE FROM table_name WHERE column_name='value'...
How to select second highest salary from a table?
This question is asked a lot in interviews, so here some of the methods by which one can get the second highest salary of an employee from a table.
Suppose the Employee is like this
ID
Name
Salary
Table: Employe...
EXISTS Operator
EXISTS Operator:
Exists operator is a special type of operator which is used in a subquery. It is used to check whether a subquery returns something or not. If a subquery returns some row then outer query get executed otherwise the whole query...
What is process to Copy entire date into other table in SQL Database ?
Hi Reader's,
Welcome to FindNerd, today we are going to discuss what is process to Copy entire date into other table in SQL Database ?
If you want to copy a SQL table into another table in the same SQL database then you should use copy st...
SELECT INTO Statement
SELECT INTO Statement:
SELECT INTO statement is used to copy data from one table into another table. We can provide a condition while using SELECT INTO statement.
Syntax:
SELECT columnname(s)
INTO new_tablename
FROM t...
NOT operator in SQL
NOT Operator:
The SQL NOT operator is used to reverse a condition in a SQL query. It is used with SELECT, DELETE, UPDATE, or INSERT statement. It can be used with other operators like BETWEEN, IN , EXISTS but it will reverse the condition.
...
Case Expression in SQL
Case Statement:
Case Statement is used to provide IF-ELSE functionality in a SQL query to fetch result. We provide multiple conditions and according to condition matches the value will change for that that column value.
Syntax:
SELECT ...
What is view in Sql server
A View is a virtual table which is created form another table and its content are defined by a query. In real scenarios we use views for security purpose by allowing users to access data through the view, without granting the users permissions to...
Temp Table and Temp variable in sql
Temp Table -Temporary Temp Tables
1)They can be created at run time in Tempdb database.
2)DDL,DML statements like select,update which can be executed on regular tables can be done on temporary tables. They can have constraints and can be in...
Binary Relationship
A Binary relationship is when there is relation between two entities.This relationship is further divided into three types.
One to One
One to Many
Many to one
Many to Many
1.One to One: In this relationship, one entity can be assoc...
SQL COMMANDS
SQL is Structure Query Language which is developed by E.F.Codd.Today almost all Relational Database Management Systems (RDBMS) uses SQL as the standard database language. We can use it to do different types of operations in RDBMS.
SQL COMMAND...
Differences Between IsNull() and Coalesce() Functions?
Differences Between IsNull() and Coalesce() Functions?
ISNULL() Function:
1)ISNULL function is regraded as Transact-SQL function.
2)This function is used to replace NULL with the replacement value specified in second argument or from from ...
Difference between datatype char(n),varchar(n),nchar(n),nvarchar(n) in sql server?
Difference between datatype char(n),varchar(n),nchar(n),nvarchar(n) in sql server?
char(n)
A char datatype takes 1 byte per character. It is a fixed length character datatype i.e even though character entered are less than n,fixed memory si...
Use of AVG() Function in MySql
The MySql provides us with various for wide use for handling the database. The AVG() function is also one of the important function used in MySql. The AVG() is used to calculate the average value of the columns.
Syntax:
SELECT AVG(column-n...
TOP Clause in SQL
SELECT TOP clause:
TOP clause is used to fetch specific
number of records from a table.
This clause is very handy when we
have huge table of thousands of
record in it . And fetching data
from these tables are quite
time-consuming , in ...
FULL OUTER JOIN IN SQL
FULL OUTER JOIN
If you want to combine the results of both LEFT and RIGHT join than we use FULL JOIN keyword. The joined table which created after FULL JOIN contains all records from both the two tables, and fills null value for those places...
How to use Right Join on two tables
Right Join:
Right Join is used to join two tables and it return all rows from right table(table 2) and matching rows from table 1(left table). The result is NULL for unmatched rows for table 1(left table). Right Join keyword is used to apply R...
SQL CONSTRAINTS
SQL CONSTRAINTS
A constraint is a rule or limitation that we place on the data in a database that we can put into a column(s). A constraint is part of the table. we can apply constraints when we create the table or afterwards. Constraints coul...
FOREIGN KEY
FOREIGN KEY
Foreign key is used to setup relation between two tables in a database. A foreign key is a values that appear in both the tables in database system.
The table which contain foreign key is called the child table while the table to...
UNION CLAUSE IN SQL
UNION CLAUSE
The union operator in sql is used to combined two different queries into a singel table.
For eg:-we have one table which have information about employee, also have another table that have information about their department, and t...
SQL Server : Read only databases
Databases whose data is not required to be changed should be considered to be set as READ ONLY.Databases can be set to READ ONLY mode and back using T-SQL and SSMS.
Following are the scripts that can be used to set database read only and bac...
SQL Server : Useful datetime functions to find specific dates
Following are some useful SQL Server Date functions to find specific dates.
1) Today
SELECT GETDATE() 'Today'
2) Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
3) First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk...
SQL Server : Global Variables
SQL Server provides a number of global variables, which are very useful.The values of these variables is maintained by the server. All the global variables represent information specific to the server or a current user session.The names of global...
Common Table Expressions
CTE:Common Table Expressions
1)CTE's replace subqueries i.e they are result of complex queries so can be defined as temporary resultset.
2)CTE's are mainly used for recursive programming.
3)The lifetime of CTE's is the individual query i...
SQL Server : How to Enable or Disable All the Triggers on a Table and Database?
Sometimes we need to disable trigger on a table especially when performing admin tasks on a table. Following commands provide a quick way to disable all the triggers for a table. Please note
that when we are diabling triggers on a table we w...
SQL Server : How to do case sensitive search?
Sometimes during application development we need to do case sensitive search. Let us see how we can do the same in SQL Server.
For illustration purpose we will use a table called Technology which has a column named Platform.Now let us say colu...
NORMALIZATION IN DBMS
NORMALIZATION
Database is a collection of meaningful data by which we can easily upload,update,delete and manage data.
while creating a database the main problem which encounter is data redundancy. Data redundancy is condition in which same ...
SQL : How to update top N records in a table?
It is a common requirement in SQL server development to update top N records in SQL server.In this blog we will see two approaches to accomplish the same.
1) Using UPDATE with TOP
UPDATE TOP (100) Table1 SET field1 = 1
However witho...
SQL Server : Error handling using TRY-CATCH
Before SQL Server 2005, the only practical way to trap errors in SQL was using the old-fashioned @@error system variable. Although this is still supported, in this blog we will learn how to use TRY-CATCH for handling errors.TRY-CATCH block is si...
SQL Server Transactions
A Transaction groups a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully finish.If all of these tasks execute successfully, then a transaction is committe...
Change the datatype of a column in SQL Server
Hello Reader!If you wish to add, delete or modify columns in an existing table you can do this with the help of ALTER command.
The syntax to change or we can say to modify a column in an existing table in SQL server is:
ALTER TABLE table-na...
SQL LIKE Clause
The SQL like clause is used to look for the values which are similar to the values in the table with the help of wildcard operators. We have two types of wildcard operators:
1. "% - percent sign
2. _- underscore
Syntax:
SELEC...
Combining Results of two or more SELECT Statements
Combining Results of two or SELECT Statements using UNION OPERATOR:
UNION Operator is used to combine the results of two or more SELECT statements. But UNION operator does not return duplicate rows so to return duplicate rows UNION ALL operato...
SQL: Difference between inner join and outer join
Joins are used to combine the data from two tables and return specific rows of data from the tables.
A join can be either an inner join or an outer join, depending on what is expected in the result.
INNER JOIN:
Gets all matching rows i...
what is the use of FORMAT() function in SQL?
In SQL, FORMAT() function is used to format a column value means how a field will to be displayed.
FORMAT() Syntax
SELECT FORMAT(column_name,format) FROM table_name;
In the above syntax parameters are as below:
Parameter Descri...
what is the use of NOW() function in SQL?
In SQL, NOW() function is used to return the current system date and time.
NOW() Syntax
SELECT NOW() FROM table_name;
We have a table "employee" as below:
employee
id first_name last_name salary
........
SQL Tutorial -> Sql Inner Join
<-- Chapter 25: SQL Join
Chapter 26
SQL Inner Join
SQL Inner Join clause is used to fetch all the rows from more than two tables in which there is a match between the columns. Inner Join is similar like Join clause.
Lets see an ...
SQL Tutorial -> Sql Joins
<-- Chapter 24: SQL Aliases
Chapter 25
SQL Joins
SQL Joins clause are used to combine data rows from two or more than two tables according to common field values between them.
Lets see an examples from the below tables "customer...
SQL: WHERE ,AND and OR Clause
1. SQL WHERE Clause:-
SQL WHERE Clause is one of the most useful feature of the SQL query as it is used to specify a condition while fetching the data from single table or joining with multiple tables,it also allows you to select specific ...
SQL Tutorial -> SQL Aliases
<-- Chapter 23: SQL Wildcards
Chapter 24
SQL Aliases
SQL Aliases are used to rename a particular table name or column name in a temporary manner. In a SQL statement , any table or column name can be temporary change.
SQL Syntax fo...
SQL Tutorial -> Sql Wildcards
<-- Chapter 22: SQL Select Top
Chapter 23
SQL Wildcards
SQL Wildcard characters are used to substitute any characters in a string. As we have discussed LIKE operator in chapter 10. SQL supports two wildcard operators with LIKE opera...
SQL Tutorial -> Sql Select Top
<-- Chapter 21: SQL Injection
Chapter 22
SQL Select Top
SQL Select Top clause is used to retrieve TOP N number of Records or X percent of Records from database table.
Note: All database systems does not support the SELECT TOP claus...
ALIAS Keyword
ALIAS Keyword:
It is used to give temporary names to table and columns. It is very useful for situations where column or table names are too long.
Synatx:
SELECT columnname AS alias_name FROM tablename;
This syntax is used ...
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 ...
