Hello Friends,
Today I will discuss
some very important SQL Commands on Basic Level. These commands are very common
and useful in everyday office work if you are using SQL. So, I am sharing
different types of commands with syntax.
Basically SQL is a
Structured Query Language or you can say Standard Language for accessing and
manipulating database.
What we can do with SQL
Commands?
·
We can execute queries against a
database
·
We can retrieve, insert, update and
delete data or records from a database
·
We can create new databases
·
We can create new table in a database
·
We can create stored procedure in a
database
·
We can create views in a database
·
We can set permissions on tables,
procedures and views
Select Query
We use select query
to retrieve selected columns or data from tables stored in a database.
Select column_name from
table_name
Distinct Query
This query is used to
select unique records on selected column from a table. We can also select all
unique data from a table.
Select distinct
column_name from table_name
Select distinct * from table_name
Where Query
This query is used to
select data, column on condition.
Select column_name from
table_name where condition
AND/OR Condition
We use AND/OR
when we want two conditions in Where Query. AND is used
when we want both conditions true. OR is used when we want any condition is
true.
Select column_name from
table_name where condition_1 AND/OR condition_2
Create Table Query
We use this query to
create a new table in database. We give table name and column names based on
our requirement.
Create table table_name (
column_name_1
column_datatype (constraint),
column_name_2
column_datatype (constraint)
)
Insert Query 1
This query is used to
insert records manually in an existing table.
Insert into table_name (column_name_1, column_name_2, … )
values ('value1', 'value2', … )
Insert Query 2
This query is used to
insert records from one table to another table in a database.
Insert into table_name_1 (column_name_1, column_name_2)
Select
column_name_3, column_name_4 from table_name_2
Select Date Query
This query is used to
select current date and time from system in which SQL in installed.
Select Getdate () --- Select Current system
date with time
Select Curdate () --- Select Current system date
Select Curtime () --- Select Current system time
Update Query
This query is used to
update existing records in a database tables. You can also update using
criteria.
Update table_name set
column_name = 'new_value'
where condition
Delete Query
This query is used to
delete records from existing table in a database. You can delete all records
and selected records based on conditions.
Delete from table_name where condition
Drop Query
This query is used to
delete a table from database. This will delete table permanently.
Drop table table_name
Truncate Query
This query is used to
delete all records in a table. Means this query will make the table empty.
Truncate table table_name
Alter Table Query
Alter queries
basically used to change the structure of existing tables like changing
variable, renaming column, remove column, addition of column etc.
Add column in a table
Alter table table_name add column_name datatype
Alter of modify column
Alter table table_name alter column
column_name new_datatype
Rename column
Alter table table_name rename column column_name_old to
column_name_new
Drop column
Alter table table_name drop column
column_name
Index
Index can be created
on Tables for faster execution of query or to fetch records faster.
There are two types
of Index in SQL:
1. 1. Unique
Index
2. 2. Non-Clustered
Index
Create Index
This query is used to
create Index on columns in a table. This syntax will create Non-Clustered index
by default.
Create index index_name on table_name (column_name1,
column_name2)
Create Unique Index
This query is used to
create Index on columns in a table where records are unique.
Create unique index index_name on
table_name (column_name1, column_name2)
Add Index
This query is used to
add index on a table.
Alter table table_name add index index_name
Drop Index
This query is used to
delete or drop existing index from a table.
Alter table table_name drop index index_name
Add Constraint
This query is used to
add criteria or condition in records on a table
Alter table table_name add constraint_name constraint_type
constraint_condition
Drop Constraints
This query is used to
delete or drop existing constraints from a table.
Alter table table_name drop constraint_name
Copy table structure into
new one
This query is used to
copy structure of an existing table into other table. This will copy all column
structure. So there are different syntax to do this task.
1. Select top 0 * into new_table_name
from old_table_name
2. Select * into new_table_name from
old_table_name where 1=2
3. Create table
new_table_name as select
* from
old_table_name where 0=1
Copy table structure into
new one for selected columns
If you want to copy
few column from an existing table then use this query.
Select top 0 column_name1, column_name2,
column_name3 into new_table_name from old_table_name
Copy structure with data
into new one
This query will copy
structure and all data from an existing table into new table. You can write
this query in different ways.
1. Select * into new_table_name from
old_table_name
2. Select * into new_table_name from
old_table_name where 1=1
3. Create table
new_table_name as select
* from
old_table_name
Copy data into an
existing table
This query will copy
all data in an existing table from another existing table but the structure of
both table should be same.
Insert into
new_table_name select *
from old_table_name
Copy table from other
database
If you want to copy a
table from another existing database then run this query.
Select * from into
new_table_name from database_name.old_table_name
Functions in SQL
There are many different
functions in SQL. Like to find max, min, first, count etc. These functions are
very useful for criteria based data and for QA.
Max Function
This function is used
in the query to get maximum value in a particular column. Like maximum salary
etc.
Select max(column_name) from table_name where
condition
Min Function
This function is used
in the query to get minimum value in a particular column. Like minimum salary
etc.
Select min(column_name) from table_name where
condition
Average Function
This function is used
in the query to get average value of a particular column. Like average salary
etc.
Select avg(column_name) from table_name where
condition
Sum Function
This function is used
in the query to get sum value of a particular column. Like sum of salary amount
etc.
Select sum(column_name) from table_name where
condition
Count Function
This function is used
in the query to get count of rows or data of a particular column. Like you want
to get the count of total employees or you want to get employees count under
salary 50000 etc.
Select count(column_name) from table_name where
condition
Average Function
This function is used
in the query to get average number of a particular column based on criteria.
Like you want to get the average salary in a specific department then you can
use this function.
Select avg(column_name) from table_name where
condition
Sum Function
This function is used
in the query to get sum or total of column based on criteria. For example, you
want to get total salary give to a particular department’s employees.
Select sum(column_name) from table_name where
condition
Like Function
This function is used
in the query to get similar data to a string or name from a particular column.
Like you want to fetch all records where employee name is “kumar”.
So it will search all
records whose name contains kumar. And there are some wildcards (criteria) to refine
your search result.
Select column_name from
table_name where column_name like '%kumar%'
Wildcards in Like
Function
a% - Select
all the records where column value start with a.
%a – Select
all the records where column value end with a.
%kumar% - Select
all the records whose name contains kumar.
a_%_% - Select
all the records where column value start with a and total length is 3
character.
a%w – Select
all the records where column value start with a and end with w.
In Function
This function is used
in the query to enter multiple criteria values or clause within a column.
Select column_name from
table_name where column_name in ('value1', 'value2')
Select column_name from
table_name where column_name in (Select column_name from table_name) -- combined two table result
Between Function
This function is used
in the query to get data between a range. Like from particular date to another
date, selecting salary data in salary range etc.
Select column_name from
table_name where column_name between 'value1' and 'value2'
Order By Function
This function is used
in the query to show results in ascending or descending order. By default it
takes ascending order but if you want to show result in descending order then
use desc in the query.
Select column_name from
table_name where condition order by column_name
[asc/desc]
Group By Function
This function is used
in the query when we use aggregate function in select query. And aggregate
functions are Max, Min, Count, Average, Sum etc.
Select column_name1, sum(column_name2) from table_name group by column_name1
Union All Function
This function is used
in the query to get the result from two or more tables. But the column names in
both tables should be same.
Select column_name1,
column_name2, column_name3 from table_name1
Union all
Select column_name1,
column_name2, column_name3 from table_name2
No comments:
Post a Comment