SQL Commands

• SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
• SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.

Types of SQL Commands

There are four types of SQL commands: DDL, DML, DCL, TCL.

1. Data Definition Language (DDL)

• DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
• All the command of DDL are auto-committed that means it permanently save all the changes in the database.

Following are the some commands that come under DDL:

a. CREATE It is used to create a new table in the database.

Syntax:

In above statement, TABLE_NAME is the name of the table, COLUMN_NAMES is the name of the columns and DATATYPES is used to define the type of data.

Example:

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax: To DROP a table permanently from memory

The cascade constraint is an optional parameter which is used for tables which have foreign keys that reference the table being dropped. If cascade constraint is not specified and used attempt to drop a table that has records in a child table, then an error will occur. So by using cascade constraints, all child table foreign keys are dropped.

Example

c. ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

Following are the list of modifications that can be done using ALTER command.

• With the use of ALTER commands we can add or drop one or more columns form existing tables.
• Increase or decrease the existing column width by changing the data type
• Make an existing mandatory column to optional.
• Enable or disable the integrity constraints in a table. We can also add, modify or delete the integrity constraints from a table.
• We can also specify a default value for existing column in a table.

Adding new columns in Table:

With the use of ALTER table command we can add new columns existing table.

Syntax: To add a new column in the table

In the above syntax, where table_name corresponds to the name of the table, column-definition corresponds to the valid specifications for a column name and data type.

EXAMPLE:

Syntax: To ADD a multiple column from a table.

ALTER TABLE table_name ADD column_name1, column_name2;

Example:

Adding constraints in a Table:

You can also add constraints to an existing table. For example: If you forget to add a primary key constraint to a table during creation, you can add it using the ALTER TABLE statement.

Syntax: To ADD a constraint from a table.

Example:

Following points should be kept in mind while adding new columns/relationships to existing tables.

• No need for parentheses if you add only one column or constraints.
• You can add a column at any time if NULL is not specified. You can add a new column with NOT NULL if the table is empty.

Modifying Column using ALTER:

With the use of ALTER table we can modify column and constraint in the existing table. These statements can increase or decrease the column widths and changing a column from mandatory to optional.

Syntax:

Example:

SQL does not allow column widths to be reduced even if all column values are of valid length. So the values should be set to NULL to reduce the width of the columns. It is also not possible to reduce the width of the ADHAR_NUM column from 18 to 12 even if all values in the ADHAR_NUM column are less than 12 characters, unless all al values in the name column are null. You can modify the column form NULL to NOTNULL constraints if there is no record in that column in the table.

Example:

Drop column and constraints using ALTER

You cannot only modify columns but you can also drop them entirely if it is no longer required in a table. Using drop statement in alter command we can also remove the constraints form the table.

Syntax: To drop a column from a table.

Example:

Syntax: To drop a multiple column from a table.

Example:

Syntax: To drop a constraint from a table.

Example:

Following points should be kept in mind while deleting columns/associations:

• You cannot drop columns in a table. If you want to drop a column from a table, the deletion is permanent so you cannot undo the column if you accidentally drop the wrong column.
• You cannot drop a column whose username is SYS.
• If you want to drop a primary key column unless you drop the foreign keys that belong to it then use cascade keyword for this.

Example:

• You can also enable or disable the key constraint in a table. It can be done in various situations such as: when loading large amount of data into table, performing batch operations, migrating the organizations legacy data.

Example: To disable constraint

Example: To Enable constraint

• Instead of dropping a column in a table, we can also make the column unused and drop it later on. It makes the response time faster. After a column has been marked as unused, the column and all its contents are no longer available and cannot be recovered in the future. The unused columns will not be retrieved using Select statement

Example:

RENAMING TABLE

SQL provides the facility to change the name of the table by using a ALTER TABLE statement.

Syntax:

Example:

d. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

Syntax:

Example:

e. Rename: It is used to rename the table.

Syntax:

In the above syntax, Rename is a command, <OLD_TABLENAME> is the name of the table and <NEW_TABLENAME> is the name that you have changed.

Example:

2. Data Manipulation Language

• DML commands are used to modify the database. It is responsible for all form of changes in the database.
• The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback.

Following are the some commands that come under DML:

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table. To insert a new row into a table you must be your on schema or INSERT privilege on the table.

Following are the list of points should be considered while inserting the data into tables.

• SQL uses all the columns by default if you do not specify the column name while inserting a row.
• The number of columns in the list of column name must match the number of values that appear in parenthesis after the word "values".
• The data type for a column and its corresponding value must match.

Syntax: To add row in a table

Or

In the above syntax, TABLE_NAME is the name of the table in which the data will be inserted. The (col1, col2, col3, col N) are optional and name of the columns in which values will be inserted. The value1 corresponds to the value of be inserted in col1 and similarly value2 corresponds to the value of be inserted in col2 and so on.

For example:

Syntax: To add multiple rows in a table

For example:

b. UPDATE: This command is used to update or modify the value of a column in the table.

Syntax: To update record in a table

In the above syntax, table_name is the name of the table, the column_name is the name of column in the table to be modified, and value1 corresponds to the valid SQL values. The "WHERE" is a condition that restricts the rows updated for which the specified condition is true. If condition is not specified is not defined then SQL updates all the rows in the table. It contains comparison and logical operators etc.

The following the list of points should be remembered while executing the UPDATE statement.

• It references only one table.
• In the SET clause atleast one column must be assigned an expression for the update statement,
• In the where clause you could also give multiple conditions for update statement.

For example:

c. DELETE: It is used to remove one or more row from a table. To delete rows from the table, it must be in your schema or you must have delete privilege.

Syntax: To Delete a record from table

In the above syntax, condition is used in the where clause to filter the records that are actually being removed. You can remove zero or more rows from a table. If you do not use where condition then DELETE statement will remove all the rows from the table. You can also use one or multiple conditions in WHERE clause.

For example:

d. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.

Syntax: It is used for retrieve the records from table

For example:

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.

Following are the some commands that come under DCL:

Syntax:

In the above syntax, obj_priv> is the DML statement like Insert, Delete , update and Select and <obj_name> is a table, view etc. and username is the name of the authorized user.

Example

b. Revoke: It is used to take back permissions from the user.

Syntax:

In the above syntax, obj_priv> is the DML statement like Insert, Delete , update and Select and <obj_name> is a table, view etc. and username is the name of the user from whom the permission is revoked.

Example

4. Transaction Control Language

Transactions are atomic i.e. either every statement succeeds or none of statement succeeds. There are number of Transaction Control statements available that allow us to control this behavior. These statements ensure data consistency. TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.

Following are the some commands that come under TCL:

a. Commit: Commit command is used to save all the transactions to the database. It makes your changes permanent and ends the transaction.

Syntax: To permanently save the changes

Example:

b. Rollback: Rollback command is used to undo transactions that have not already been saved to the database. Rollback also serves to end the current transaction and begin a new one.

Consider a Situation where you have completed a series of INSERT, UPDATE or DELETE statements but have not yet explicitly committed them and yiu encounter a problem such as computer failure, then SQL will automatically rollback any uncommitted work.

Syntax: To remove the changes

Example:

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax:

In the above syntax, SAVEPOINT_NAME is the name given to savepoint.

To selectively ROLLBACK a group of statements within a large transaction use the following command is used.

Example:

Multiple Choice Questions on SQL commands:

1. Which command is used to display the records in a table?

1. Create table tablename;
2. Delete from tablename;
3. Select * from tablename;
4. Rename table tablename;

2. What does the following statement perform in a table?

Update student set name = "meet" where roll_no =101;

1. It will not update the name in student table.
2. It will update all the values of name column.
3. It will update the value of name column with "meet".
4. It will update the value of name column with "meet" where roll_no =101.

3. Which of the following is not a Transaction Control Language in a SQL?

1. Delete
2. Save point
3. Commit
4. Rollback

4. Which command is used to add a column in existing table?

1. Alter table table_name ADD column_name data-type;
2. Alter table table_name column_name data-type;
3. Alter table table_name ADD;
4. Alter table ADD column_name data-type;

5. What does the following statement perform in a table?

Insert into student (name,roll_no) values( "meet",101);

1. It will insert the value of rollno in a table.
2. It will insert the values of name, roll_no column in table.
3. It will insert the value of name in a table.
4. All of the above.

6. Which command is used to maintain the structure of table after deleting data in a table?

1. Alter
2. Delete
3. Drop
4. Truncate

7. Which command is used to rename a column in a table?

1. Alter table old_table_name Rename to new_table_name ;
2. Rename old_table_name to new_table_name ;
3. Both of the above
4. None of the above