An SQL Statement is a statement written in SQL. Every SQL statement could be classified as either a Data Access Control Statement, Data Definition Statement, Data Manipulation and Retrieval Statement or a Data Transaction Control Statement. You can download a complete set of SQL statements (SQL Satement & Basics.pdf) here. This handbook describes most of the frequently used SQL statements and contains numerous examples of how to use those statements.
What are the various types of SQL statements?
SQL Data Definition Statements
These elements allow the user to define the overall structure of the data and how it is organized. Data definition statement include the CREATE, ALTER and DROP SQL statements. These statements are used primarily in the creation and management of database tables. The CREATE SQL statement can be used to create a new table, database or a user. The ALTER statement is typically used modify the structure of a table by way of adding and deleting columns from the table. The DROP SQL statement is used when one needs to delete a table, a database or a role from database management system.
SQL Data Manipulation and Retrieval Queries
These elements allow the user to work with the data once it has been defined. Using these elements a user can view and modify data stored in a table(s). These are some of the most commonly used SQL statements and are often used by data analysts and people whose role is to retrieve information from the database. These include the SELECT, INSERT, UPDATE and DELETE SQL statements. The SELECT is probably the most frequently used of all SQL statements. It purpose is to simply fetch a set of records from a single or multiple tables as per the criteria specified in the statement. The INSERT statement is used to insert new records in a table. The UPDATE SQL statement is used while updating the information that is already present in the database. Finally, the DELETE statement is used to delete records from a table.
SQL Data Access Control Statements
These elements allow a system administrator to give and take away various permissions to and from the users of the database. These include the GRANT and REVOKE SQL statements. The GRANT statement is used to grant specific privileges to users and roles to enable them to run specific statements on the database. The REVOKE SQL statement is the opposite of the GRANT and is used to…well…revoke permissions granted previously.
SQL Data Transaction Control Statements
These elements provide a way of controlling the state of the database and a particular transaction. These are similar to the save command in windows and allow you to save the “work” done so far. They allow you to resume from the last saved point onwards rather than having to start again at the beginning. They include the COMMIT, ROLLBACK and SAVEPOINT SQL statements. Although you would not use these statements for a simple database, they serve a very critical role when dealing with multi-user and multi-point-of-origination transactional systems; say for example a ticket reservation system. In such cases, since the same transaction can be originated from the same time for the same thing, the SQL statements have to be written to avoid delicacy and preserve the integrity of the database as well as the transaction. The SAVEPOINT SQL statement tells the database that it can safely save all the previous transactions. Any future ROLLBACK will only be able to rollback to the most recent point of saving. The ROLLBACK, as you might have already guessed, simply rolls back a set of SQL statements to the most recent SAVEPOINT point. For example, assume that you are trying to run an SQL statement that has 3 steps, say A, B and C with step C being dependent on the successful execution of step B and B in turn, being dependent on execution of step A. Now let’s say you run this multi-step SQL statement and are able to successfully carry out steps A and B. However, at during the execution of step C, something interrupts the process and it fails. At this point, you would want to ROLLBACK all the steps so that system is regains the same state that it had before the execution of this statement. The COMMIT SQL statement is used for committing a set of changes permanently to the database and making them visible to other users of the database.