What is database query? Definintion

The database is a repository of all types of data. After storing it, there should be a mechanism to access, manipulate and update it. For this, we have a tool in the form of a query to perform all these operations. There are many query language like SQL, Search Engine Query Language (Google, Bing), DMX (Data Mining Language), etc. All these languages have different function and their function structure used for the different data model.

In this discussion, we dealt with SQL (Structure Query Language). SQL language is Relational database query language used to access database. SQL is declarative and procedural it is we have two types of Query language one is Database Query Language and the second one is Information Retrieval Query language. SQL language is strong and rich language. It is having data types, operators like a set of operators, comparison operator, arithmetic operator, Logical operator, Number functions, Date functions and more functions.

Structure Query Language (SQL): SQL is a complete set of commands used for creating a Relational database, selecting data, modify data in the database and provide permission to users. SQL language is divide into different part according to use.

Describe Command: Describe is used to show the structure of the table. A dialog box appears to show the column name, data type, primary key, table name, Null constraint and more attributes.

DDL (Data Definition Language): Language used to create and manipulate data structure. Create, Alter, Drop, Truncate are commands of DDL.

Create Command: Create command is used to create a table in the database.

Syntax: Create table Table_Name(Column1 datatype(size), 
Column2 datatype(size),…………….., Column N datatype(size));
SQL>Create table Employee(Eid number(10),Emp_Name Varchar2(10),City varchar2(10));
During typing a command, we will use keyword create,table, data types. After typing a command use semicolon to end the command. If we make any syntax error than table will not create .
Alter Command: Alter command is used to modify the structure. When we created a table,
alter command is used to modify the structure of table like adding more columns, changing the size of column, data types and adding a primary key to attribute. In alter command two types of keyword Add, and Modify used. Add is used to adding one or more attributes, constraints.
Syntax: Alter table Table_Name add Consrtaints(Columns);
Or
Alter table Table_Name Modify(Column_Name Column_datatype);
Or
Alter table Table_Name Modify(Column_Name1 Column_datatype, 
Column_Name 2 Column_datatype,..., Column_Name N Column_datatype);
Modify: Modify keyword is used to redefine the structure by altering data type, column size.
SQL> Alter table Employee modify (Emp_Name varchar2 (20));
Similarly, Modify is used to redefine the structure.
Drop Command: Drop command is used to delete the structure of the table from database completely. We can also drop single or multiple columns from relation using alter command. After dropping a table, we have to recreate it.
Syntax: Drop table Table_Name;
‘Column’ is a keyword used in Drop command. If we don’t use Column keyword then ‘Keyword error’ will display. 
Multiple columns: Similarly If you want to delete multiple columns add column name in parenthesis.
Primary Key: To remove primary key don’t need to mention columns name.
To drop table just type :
SQL>Drop table Employee;
SQL>Alter table Employee drop (Emp_Name);
 Emp_Name is deleted from the structure of the table.
Truncate: Truncate is used to remove entire data entry at once, but the structure will remain the same. Therefore no need to create a table again. Drop command delete structure, permission, grant, and constraints, primary key, relation to other tables as well as. After a drop command, we need to recreate the table.
Syntax: Truncate table Table_Name;
SQL> Truncate table Employee;
It will empty the relation but not delete the structure.
 Relation after Truncate command.
DML(Data Manipulation Language): Used to access or manipulate the data in relation. Insert,Select ,Update,Delete all these are DML commands.
Insert: Insert command is used to enter the value in the table. We can enter values in the table using more than one method.
Ist method: In this method column name is not required.
Note: When adding a row to a table, only the characters or date values should be enclosed with single quotes, but not numeric data values.
2) In this method, while adding values, if you change the sequence of attributes values like in below example first we are going to enter (1,’pooja’,’Abohar’). Values will be stored properly, But If I change the sequence like(1,’Abohar’,’Pooja’) than Abohar will get stored in ‘Emp_Name ‘ attribute and name value gets stored in ‘City’).So in this method, we should do it properly.
Syntax: Insert into Table_Name values(value1,value2,value3,…………valueN);
SQL->Insert into Employee values(1,’Pooja’,’Abohar’)
 
2) IInd Method: We can mention column name with column values.
For example:
SYNTAX: Insert into Table_Name (Column1,Column2,…………,Column N) values(value1,value2,value3,…………value N);
SQL>Insert into Employee(Eid,Emp_name,City) values(2,'Pooja','Abohar');
In this method we can change sequence with proper change in attribute sequence.
Insert into Employee(Eid,City,Emp_name) values(4,'Rohit','Assam');
Note: With both method,we can insert only one row at time.
 Multiple values: For inserting multiple values through a single command, we used the select command .
For instance:
SQL>Insert into Employee(Eid,Emp_name,City)(select * from Student);
Suppose If employee table has four entry .then all these entries get copied into Employee table. But if both tables have not a same number of column and same naming than it will create an error.
Update->Update command is used to change predefined data in relation. Command allows us to modify single or multiple records in a relation. In update command, we used some keyword like the set, column, where to update the values.
Syntax->Update table_name Set column=value,column2=value where Column=value;
Or
Update table_name Set column=(Select expression from table);
Parameters: 
Where: Where is used to find the records in the table so that record gets updated?If Condition not matched in this case single column value and all column values replaced with Null value. 
Set: Set keyword reset the column values that you want to set after condition execution.
For instance:
                                                                                           Employee
Eid
Emp_Name
City
1
Pooja
Abohar
2
Pooja
Abohar
3
Goa
Sonal
4
Assam
Rohit
Structure of table:
SQL>Update Employee set Emp_Name='Sumit' where Eid=5;
After execution of the command, Emp_Name is set to be submitted to the table.
 Or 
 Multiple Records Updation:
SQL>Update Employee set Emp_name='Sonal',City='Goa' where Eid=2;
Command will set Emp_Name = Sonal And City = Goa where Employee id =2;
Points to remember:
 1) With Select command, we can only fetch single value, not multiple values. Otherwise, the system will generate ‘Two Many Values’ error.
 2) Unique Constraint Violation error->  Update Employee set Eid=2 where city=’Rohit’;
 This command will generate Unique Constraint Violation error because Eid column has a primary key.Eid column is already haveing value 2 in Eid column, and we can’t enter it again.
 Delete Command: Delete command is used to delete rows in a table. We can use delete command to delete a single row as well as multiple rows. If we don’t use where keyword than entire rows will be deleted from the table. We can use AND or OR logical operators for more than two expressions.
 For instance:
SQL> Delete from Employee where Eid=2;
Command will delete single row whose Eid=2 from employee table.
 Or
SQL> Delete from Employee;
Delete command not having where clause.So it will delete entire rows from the table.
 For multiples rows:
SQL> Delete from Employee where Eid=1 or Eid=2;
This command will delete both rows from table whose Employee id=1, 2;
 Select Command: Select command is used to select data from table. Select command fetches attributes through table according to table using where clause. If where the cause is not used in the command, it will return entire entry.
Syntax-Select  Column1,Column2,…,Column N from Table_Name where [conditions];
Or 
Select * (‘*’ represent Entire_Columns in a table) from Table_Name;
For instance
SQL> Select * from Employee;
This command return all rows from table.
SQL> Select Emp_name, City from Employee where Eid=1;
It will return Employee name and city whose id number is 1.
SQL> Select Emp_name, City from Employee where Eid=1 OR Eid=2;
Command will return two rows whose Employee id=1 or Employee id=2.
 ‘OR’ is logical operator it will fetch both records according to id.
 TCL(Transaction Control Language): This language is used to manage the changes made by DML.We can rollback data after identifying a savepoint or any DML command. Commands are Commit, Savepoint, Rollback and Set Transaction.
 Commit: Commit command is used to make the change permanently in the database.When we use insert, delete and update command these changes are not permanent, until we closed the section or use commit command or use auto-commit option. If we don’t use commit command, entire changing can be undone with the help of rollback command.
Syntax->Commit;
Rollback: Rollback work like undo button used for that transaction which not saved in the database. It reverses the changing made by DML command. When we used auto-commit and commit command. This time changes permanently applied to the database.  Now if we used rollback command after some accidental lost of data by DML command, committed data will be retrieved again.
Syntax->Rollback;
For instance->
SQL> Delete from Employee where Eid=1;
1 row(s) deleted.
SQL> Rollback; (Run command)
Statement processed. The deleted row will retrieve again.
Savepoint: Savepoint is a mark to the transaction so that we can rollback the transaction when we required. We can create many savepoints during transaction. After that, we can use rollback.
 For Example:
SQL> Insert into Employee values(5,’Juhi’,’Abohar’);
1 Row(s) Created.
SQL > Savepoint p1;
Savepoint Created.
SQL> Insert into Employee values(6,’Jitender’,’Abohar’);  
1 Row(s)Created.
SQL> Savepoint p2;
Savepoint Created.
SQL> Delete from Employee;
6 row(s) deleted.
SQL> Rollback to p2;
Rollback Complete.
Now we will retrieve all data up to savepoint p1.Now use Commit command to save data permanently.
DCL(Data Control Language): DCL used to control the access to the database. This command is used to provide and revoke the access permission from a user. Grant and revoke command is used to give and deny the permission on the database.
 Grant: Grant command is used to provide some specific permission to a user like an insert, update, create, delete, etc.
 Deny: This command disallow user to perform some specific task.
 Revoke: Revoke command cancel granted permission to a user.
You’ll also like:
SQL UPDATE Query 
MySql SELECT BETWEEN Query using Java servlet 
MySql SELECT LIKE Query using Java servlet 
Java Servlet MySql LIMIT Query 
Java Servlet UCASE Query 
   
Next →
← Prev