Oracle and SQL Basics

An ORDBMS offers all the features of a RDBMS plus it offers the benefits of creating user defined objects and types. Oracle offers the user an option to not only create and maintain relationships between different tables but also user defined data types such as Varrays, Types, Nested Table etc..

The alter statement is used to resize a column.  The size of a column can both be increased or decreased using the alter keyword. Normally, the size of a column can be increased to a large value but it can only be decreased to the limit that if there is data present in the column, it should not get trimmed.

It is the language to handle basic database operations like creating a table and managing it. Some of the keywords of SQL are Create,Select, Delete, Update etc.. SQL is also sometimes called SEQUEL which stands for Simple English Like Query Language. SQL is supported by all the major RDBMS products like Oracle, Ms-SQL Server, MySQL to name a few.

The Like operator is used for pattern matching in VARCHAR columns. It is useful in conditions where we do not to compare a value exactly with another. For example, if we want to search for all the persons whose names start with  A, we can use the Like operator with the wildcard character. % is used for substituting a string. For conducting a character replacement we can use the _ (underscore) operator. It is used for matching values that are of a particular length.

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.

The resource role allows a user to create and modify new objects like Table, View etc. It is the basic permission that a user requires to create objects. When a user in Oracle is created, it is a two step process - first a new user is created by issuing the create user statement which is create user username identified by password;For example : create user amit identified amit_manager; Once the user is created , it is assigned permissions so that it can carry on with its work. To grant permissions, the create grant statement is issued. For example : Grant connect,resource to amit. This statement will permit the user amit to login(connect) and create objects(resource) 

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.

The Insert statement in SQL has the following syntax Insert into tablename values(value1,value2,value3....) For example, in a table named Patients which has the following structure.&#xA0

The Select statement is used to extract or display data of a table. Using a Select statement, a table can be printed either fully or partially. We can also add a condition so that only those records that fit the criteria can be displayed. Example: To print all the columns of a table the command will be Select * from tablename where * denotes that all the columns have to be printed. In case we want to print only selected columns we can replace * with the column names as select rollno, name from students. To add a condition, the where clause can be added to the select statement Select rollno, name from students where rollno=1.

The alter keyword is used to alter (modify) the structure of the table. It is different from the other options because it is the only option which belongs to the DDL (Data Definition Language) part of the SQL. DDL statements are those statements that are used to define or modify the structure of the table. SQL statements like Create and Alter are DDL statements because they work on the structure of the table rather than the data.

The Delete statement is used to delete all or selected rows from a table. It is a part of DML or Data Manipulation Language part of SQL. Its syntax is delete from tablename where conditionexample: Delete from students where marks>50. If the 'Where clause' is omitted from the above statement, it will delete all the records from a table.

There are situations where the values in a column are repeated. For example, the city column of a table can have many records of Mumbai, Delhi etc. To display the repeated values just once we can put the distinct clause with the where clause. For example, the statement select distinct(city) from list will display the name of a city only once. If the name of a city is present in the table more than once it will be shown just once.