Database and SQL

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.  ||| |---|---| |Column |Datatype| |Patcd|number| |Patname|varchar2(10) | The statement to insert a new record will be Insert into patients values(1,'Sanjay') Make sure that all the VARCHAR and DATE columns are enclosed in a pair of single quotes.

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.