Featured
-
How Regression Testing Detects Integral Errors In Business Processes
Humans are forever changing and evolving and so to
by kristina.rigina -
Get Display Banner Advertising Opportunity on FindNerd Platform
“Do you have a product or service that nee
by manoj.rawat -
Android O Released with Top 7 New Features for App Developers
Android was founded by Andy Rubin, Rich Miner, Nic
by sudhanshu.tripathi -
Top 5 Features That Make Laravel the Best PHP Framework for Development
Laravel is a free open source Web Framework of PHP
by abhishek.tiwari.458 -
Objective C or Swift - Which Technology to Learn for iOS Development?
Swift programming language is completely based on
by siddharth.sindhi
Tags
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...
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.
...
BETWEEN and NOT BETWEEN Operator
BETWEEN Operator:
It is used to fetch values which lies within a range given in BETWEEN operator.
Syntax:
SELECT columnname(s)
FROM tablename
WHERE columnname BETWEEN value1 AND value2;
Example:
SELECT * FROM Employee
WHERE ...
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 ...
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...
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 ...
Dual Table
Dual table:
DUAL table is a dummy table which
contains one row and one column and
by default it is present in
database.It contains a single
VARCHAR2(1) column known as DUMMY
with a value "X".
It can be accessed by all users but
the ...
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...
Aggregate Functions
Aggregate Functions:
Oracle has many built-in functions which are used to process numbers or strings. They are very useful as we can directly use them , we just have to write the aggregate function name and in braces the column name on which w...
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...
How to use Left Join
Left Join:
Left Join is used to join two tables and it return all rows from table 1 and matching rows from table 2. The result is NULL for unmatched rows for table 2. Left Join keyword is used to apply Left Join on two tables.
Syntax for Le...
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...
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 ...
Truncating a table
TRUNCATE TABLE Statement:
TRUNCATE Statement is used to remove all records from a table. It is a Data Definition Language (DDL) Statement as it deletes all records and only structure remains. It is similar to delete statement but without where...
Null Values
Null Values:
Null values denotes missing data in a table or a database. If a column does not have any NOT NULL constraint then for that column there can be NULL values. By default a column can have NULL values.
We cannot use comparison oper...
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...
Sub Queries
Sub Queries:
Sub Queries are those query which comes within another query and are written after the WHERE clause.They are also known as Inner query or nested query.
It is used to return records that are used in the main query as a conditio...
DELETE Query
DELETE Statement:
It is used to delete existing records from a table. It can delete all records from a table if where condition is not given and if where condition is given than it will delete selected records which will meet the condition.
...
Having Clause
Having Clause:
The Having clause is used to filter the group result. As we cannot use Where to filter the Group By clause results so Having provide the same functionality as Where. It filter the result fetched by Group By clause. It comes a...
SQL commands in database
SQL stands for structured query language. It is used to manage and access data from database.
A database may contain more than one table having unique name for each table.
SQL language have many different versions but it supports most of the co...
Group By Statement
Group By :
It is used to group the result-set by one or more column. It is used in conjunction with aggregate functions provided by Structured Query Language . If order by is used then Group By must come before Order By clause.
Syntax1:
...
Crreating Virtual Table using CREATE VIEW statement
CREATING VIEWS:
Views are virtual tables which are created from other tables .It is beneficial for those situation when we want to show limited columns to users. It can be created from one or more tables.
Syntax:
CREATE VIEW viewname AS ...
Pattern Matching Using Like Operator
LIKE Operator:
It is used to search for pattern in a table or a database . It is used with where condition. It is similar to REGEXP of MySQL.
Syntax:
SELECT columnname(s) FROM tablename WHERE columnname LIKE pattern-condition;
Examp...
Updating a column value
Updating a column value using Update Statement:
UPDATE statement is used to change the value for a column or to change multiple records using a condition. It is a data manipulation language command which are used for managing data.
Syntax:
...
To add a primary key using ALTER command
Adding a primary key using ALTER Command:
Alter command is generally used to modify table . It can be used to add columns or to remove columns. Using Alter command we can also add or remove a key from a table.
Syntax:
ALTER TABLE tablena...
To check the structure of a table
DESCRIBE Statement:
It is used to view the structure of a table means it shows name and data type of columns.
Syntax 1:
DESC tablename;
Syntax 2:
DESCRIBE tablename;
Example:
DESC Employee;
Column
Datatype
...
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...
Inserting data using replace statement
REPLACE INTO Statement:
It is used to insert a row in a table.It is similar to INSERT INTO statement but it does not allow duplicate row as it replaces the old one with the new one.
Syntax:
REPLACE INTO tablename (column1, column2, col...
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 ...
Eliminating Duplicates using Distinct Keyword
DISTINCT Keyword:
It is used to fetch different values from a table. It removes duplicate records from the result set and used when there can be multiple duplicate tuples in a table.
Syntax:
SELECT DISTINCT columnname FROM tablename;
...
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...
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 ...
