Database and SQL

The above statement is a valid statement and will display a list of tables available in a user's account. The statement will also print the names of the available view, synonyms etc. The select * from tab statement actually queries a synonym named tab which is in the System user account. All the users created in Oracle have a default access to the synonym. Therefore when we are issuing the above statement we are in fact querying the said synonym.

Rollback is a part of Transaction Control Language or (TCL). It is used to cancel or undo any changes made in a table. For example, it the user has accidently deleted the data, it can be easily rollbacked to the previous state. The other commands of the same category (TCL) are commit, savepoint etc.

Alias is the temporary name given to a table or a column. An alias is quite useful where the name of the table is lengthy and the name has to be written more than once in a statement. A table alias is generally a word with one or two letters that can substitute a table name. For example, if the name of a table is sales_1999, then instead of writing sales_1999 everytime it is required, we can create an alias an use it. Consider the following statement: Select s.data from sales_1999 where s.no=101 in the above statement. S is an alias and it is used as a substitution for the word sales_1999.

The Truncate Table statement is used to delete all the rows of a table at once. The statement allows the user to delete all the records without specifying a condition using the where clause. Truncate  deletes all the records but keeps the structure of the table intact. The output of this statement is equivalent to the delete statement without the where statement.

To modify a column that already has data in it, it is compulsory to first empty the whole column. When all the values of the column are emptied we can modify the datatype of the column. To empty a column, we can use the following SQL statement.Update tablename set columnname=nullFor example, to empty all the values of the column address of the table customer the statement will beupdate customer set address = nullTo modify the datatype of the column we can use the following SQL Statementalter table customer modify(address varchar(10))  

The where clause is an optional statement in a Select/Delete/Update statement. If it is omitted then the command will affect all the records of the table. For example,  the statement delete from employees will delete all the records in the table.

Comma is the valid separator in an in clause. The in keyword is used with where to provide a list of values to select. For example, to select all those records where the value in column sport is either Cricket or Football, the statement will select * from players where sport in ('Cricket','Football') The above statement will separate all the rows where the value of the column sport is Cricket or Football.

To save changes made to a table, the Commit statement can be issued. Commit is a part of TCL or Transaction Control Language. TCL statements are those statements that are used to save or cancel the changes made to a table. Commit is used for saving the changes whereas the rollback statement is used to cancel the changes. Oracle also provides the user to commit the changes automatically by typing the statement Set autocommit on.

In SQL to combine 2 or more condition in a statement, we can use the logical operators like - and & or. The or operator joins 2 or more conditions are return those records that satisfy any of the given conditions. The and operator is used to display those records that satisfy all the given conditions. For example to see the records of a table named students that have a percentage between 50 and 60 the following SQL can be usedselect * from students where percentage>50 and percentage<60.< em=""> 60.<>

The where clause is used to limit the effect of a select/delete/update to only those records that meet the given condition. The above statement will display all those records where company is IBM. Although SQL is a case insensitive language, special care must be taken while comparing a text value with another since it matches the text and the case also.

The Order By statement is used to display the records in a particular order. For example, the statement select * from students order by address will print all the records alphabetically on the basis of address. By adding a desc clause we can sort the records in descending order. The statement select * from players order by match desc and will print all the records from the players table in descending order of the match column.

Count is an aggregate or group function. It is used to count the number of records in a table or number of not null values in a column. It can be used in the following ways:1. Select count(*) from tablename, the above statement will display the number of records in a table.2. Select count(address) from tablename, the above statement will display the number of values in the address column. It will not count the rows where the column address has a NULL value.Some other aggregate functions are1. Sum, 2. Min, 3. Max, 4. Avg etc.

The above statement will print all those records where the city name starts with 'A'. The wildcard character '%' is used to substitute a whole string. Therefore all those city names will be printed that begins with 'A'. For example, 'Ahemdabad', 'Akola' etc.

To create a copy of an existing table we can use a select statement. The target table in this case will be automatically created. The select statement can be any valid select statement and can also include a where clause. The statement will create a table named target having the same data and structure of the source table. For example, if we want to create a copy of table A with the name B, but we we want to copy only those records where the value of mark column is more than 50. We can use the following statement Create table B as select * from A where marks>50 .

The above statement will set the name column to null or empty values. The null keyword is used both with the = operator and is operator. When a statement like select/delete/update is used with a where clause, null is used with is for example select * from papers where name is null.But while accessing records with a null value, the = operator is used for example delete from papers where names is null.