/*******************************************************************************************************************************************************/
show databases;
this query shows all the databases
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
drop database <database_name>;
this command is used to delete the entire database
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
drop table <table_name>;
this command deletes the specified table
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
delete from <table_name>;
this command will delete all the tuples(rows) from the
database
note that is command don’t delete the table, it only deletes
the tuples.
In this you can also include a condition using the where
clause.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Delete form <table_name>
where C;
This command is used to delete a specific tuple which
satisfies the condition C.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Alter table <table_name>
drop <attribute_name>;
This command is specifically used to delete a particular
attribute from the specified table.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Alter table <table_name>
add A D;
This command is used to add a new attribute into an existing
table
A = the new attribute to be added
D = domain type of new attribute
Note that after adding a new attribute, for each tuple, a
NULL value is assigned to the corresponding new attribute.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
create database <database_name>;
this query creates a new database
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
use <database_name>;
this command is used to change the current database
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
create table <table_name> (attribute type_of_value,
attribute type_of_value...);
this command is used to create a new table in the current
database
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
describe <table_name>;
describes all the features of specified table
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
insert into <table_name>
values (value1,value2....);
this command is used to insert records into the specified
table
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Create table <table_name> (attribute1 type,
attribute2 type, attribute3 type, attribute4 type…., Primary key
(any_one_attribute));
This is how we declare any attribute a primary key.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
select <field_name1,field_name2...>
from <table_name>
where C;
or
select all <field_name1,field_name2...>
from <table_name>
where C;
both of the above queries perform the same function.
prints the table with specified fields of specified table
which satisfy the condition C.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
The select clause may also contain arithmetic expressions
involving the operators +,-,*, and /.
For example :-
Select <attribute_1>, <attribute_2>,<attribute_3
* 100>,<attribute_4>
From <table name>
Where <condition>;
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Logical connectives can also be used in the where clause as
described below:-
select <field_name1,field_name2...>
from
<table_name>
where C1 or C2;
here either of the conditions can be true
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
select <field_name1,field_name2...>
from
<table_name>
where C1 and C2;
here all the conditions should be true.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Select * from
<table_name>;
prints the whole table
here “*” is used to denote all the attributes.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Select distinct <field_names>
From <table_name>
Where <condition>;
This query allows us to list all the distinct records. No two
records listed by this query will be exactly same, at least one of the
attribute of any listed record will be different.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
SQL provides us a way to rename the attributes of the resultant relation
Select attribute1, attribute2, attribute3 as
new_attibute3, attribute4
from <table_name>
where C;
This command renames the attribute3 to new_attribute3 only
in the resultant relation (table).
Note here that this command doesn’t rename the attribute in
the original table.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Tuple variables
Select attribute_1, T1.attribute_2, T2.attribute_3
From table_1 as T1, table_2 as T2
Where condition;
The above query creates tuple variables T1 and T2, for
table_1 and table_2 respectively.
These tuple variables are intern used to specify that
attribute_2 belongs to table_1 and attribute_3 belongs to table_2.
Always remember that first the condition in the where clause
is checked, then the from clause is looked upon and then finally, select clause
is gone through.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
If we wish to sort the records according to a particular
tuple, either in ascending or descending sense, SQL provides us an easy way to
do that using the order by clause.
Select <attribute_names>
From <table_name>
Where <condition>
Order by <attribute_name>;
The sorting will be performed according to the attribute
specified in the order by clause.
by default, the order by clause list the items in the
ascending order.
For listing the items in descending order we can use :-
Select <attribute_names>
From <table_name>
Where <condition>
Order by <attribute_name> desc;
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Union operation
( Select <attribute_names>
From <table_1> )
Union
( Select <attribute_names>
From <table_1> );
The union automatically eliminates all the duplicates,
unlike the select clause.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
If we want to retain all the duplicates, then we must use
union all clause as follows:-
( Select <attribute_names>
From <table_1> )
Union all
( Select <attribute_names>
From <table_1> );
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Aggregate functions
Aggregate functions are the functions that take a collection
of set of values and return a single value.
v
Average : avg
v
Minimum : min
v
Maximum : max
v
Total : sum
v
Count : count
The input to the sum and avg must be a collection of
numbers, but other operators can operate on non-numeric data type.
Select avg(<attribute_names>)
From <table_name>
Where <condition>;
This gives the average of all the entries in the specified
record.
Similarly we can use min, max, sum and count.
Select min(<attribute_names>)
From <table_name>
Where <condition>;
Select max(<attribute_names>)
From <table_name>
Where <condition>;
Select sum(<attribute_names>)
From <table_name>
Where <condition>;
Select count(<attribute_names>)
From <table_name>
Where <condition>;
The count function gives us the count of number of entries
in specified record.
/*******************************************************************************************************************************************************/
/*******************************************************************************************************************************************************/
Update <table_name>
Set <expression>
Where <condition>;
This command is used to update the record of specified table
which satisfies the condition.
The update is performed according to the expression.
Eg. Expression can be :-
Account_balance = Account_balance * 100
/*******************************************************************************************************************************************************/
Compiled by
Lavish Kothari
No comments:
Post a Comment