*ADDING COLUMN THROUGH AN EXITING TABLE
In same occasions it may be necessary to add a new column to an existing table. To do this, we add to use the ‘Add’ option of ‘alter table’ command.
Syntax:
ALTER TABLE table name
ADD New_column data type
[CONSTRAINTS] [CONSTRAINT TYPE];
When we have to add new column in table you should keep in mind that initial values of each row of new column in null. We can add a column with a not null constraint only to a table that contain no rows.
Ex. ALTER TABLE department
ADD Manager_Last_Name var char (25);
Modifying column of an exiting table, if there is a need to change a column definition in a table already created, you can use modify option of an ALTER TABLE command.
Syntax
ALTER TABLE table name
MODIFY column_Name [constraint]
[constraint Name] [constrains_Type];
The user should keep in mind that there are some restrictions on use of this command since we can change only the following columns characteristics:
(1) Data type (2) Size (3) Default Value (4) Not null Column constraints.
The modify Clause only need columns_name and modified path rather than entire column declaration. The limitations of use of this command are as follows:
(1) It is possible to change char column to varchar 2 and vice versa. If the column contains null values in all its rows.
(2) It is possible to change data type and decrease the column size if all rows table contain null.
(3) The only constraint that can be added to a table with the modify option is not null constraint, provided that the column does not contain null value.
(4) It is always possible to increase the size of a character or the precision of a numerical column.
Ex: ALTER TABLE department Modify
Name VARCHAR (45);
*REMOVING CONSTRAINS FROM A TABLE
To remove a given constraint form a table we can use the drop option of the alter table command. The variation of this command to drop a name and name constraints are as follows:
a) To remove a PK of a given table use the following command,
ALTER TABLE TABLE_NAME DROP PRIMARY KEY[CASCADE] ;
b) To remove unname unique constraint use the following command,
ALTER TABLE Table_Name DROP UNIQUE(Column, (column [column…]….. ) [CASCADE] ;
c) To remove constraints use following command, ALTER TABLE Table_Name DROP CONSTRAINT(Constraints_Name [CASCADE] ;
In all these command the clause CASCADE remove any constraint that depends on the drop integrity constraint. Without use of this clause we cannot drop a unique or PK i.e. part of a refrestonal integrity constraint without also dropping the foreign key. In addition notice that there is no option to remove specifically check or not null constraint; however there is a command to remove any name constraint therefore, if we name a check or not null constraint we can remove them using this variation of the alter table command.
Ex: ALTER TABLE department DROP CONSTRAINT department_ Name_nn;
The check constraint: In all previous examples we have assumed that the data used to popular the table has been of the proper size, range and data type. However this is not always the case, because the user can make mistake while entering data. For example; while taking an order form customer data entry operation may enter an order that exceeds the credit amount allowed for particular customer. In case like this, it is better to detect this error while inputting the data. To avoid entering incorrect values into a table we can impose a check constraint on the attribute of table. Although it is possible to add the check constraint to a table after the table has been populated, it is better to define this type of constraints before populating the table
For Ex:
Create table Mail_list
C first_Name VARCHART 2(25)
Last _Name VARCHART 2(25)
Address VARCHART 2(25)
City VARCHART 2(25)
Zip code VARCHART 2(5)
CHECK (ZIP-CODE IN (‘2281’, ‘22802’, ‘22803, ‘22804’)));
*COMMIT AND ROLL BACK
It is used to make permanent all changes made per table through the use of insert, update and delete operation. The user needs to commit these changes. The instructions that allows the user to record these changes permanently into the data base is the commit statement.
Syntax:
COMMIT [WORK];
Notice that the keyword work is optional.
The reader should be aware that prior to the execution of a commit command, all change made to the rows of a table are stored in a database buffer or working storage area in main memory if for same reason, the user quits the database before committing the change, no data will be returned on data base file and the changes to the table is working in a multi-user environment and this table is shared by other user, no change made to the table person making a change issue a command commit. This happens because whenever a user modified the rows of a table he or she has exclusive access of a row. We mean that no other user can view the current content of the row that have been changed. The affected rows are said to be a locked. It this time any other user accessing the same table will not notice that the table has changed.
Assuming that the changes made to table by issuing a command insertion, updates and deletion have not been committed. The user can cancel all the intermediate changes made to a table by issuing roll back statement.
This action cause the RDBMS to ignore all the changes made to any table or any other database object since the last commit or since the user begins his or her interactive session. Technically a roll back statement is used to cancel the current transaction. A transaction being with a first executable SQL stat. after commit, rollback or connection to data base. All transaction ends after a commit. Roll back or disconnection from the data base. Most database issue and implicit commit stat after processing DDL stat.
Sometimes it is desirable to go back to particular point in time during an interactive session. For instant we an imagine a user that have made several uncommitted changes to a table and realize that the last few changes are incorrect or unnecessary. If at this moment a user issue an roll back stat., all changes made to the table will be ignored including the changes that you have corrected. It would be nice if the user would go back to a prior state of the data base buffer, where he or she made an incorrect change. The SQL command that can be used to accomplish this is the save point command.
A save point identified a point in the transaction to which we can go back, provided that the transaction has not been committed. In this case we can think a save point stat. it allows us to undo only a part of the current transaction by allowing the user to go back to a particular point in time. Syntax: SAVE POINT save point_Name
Where save point is a unique name within the transaction generation it is single letter, but it can be longer. To go back to a previous and uncommitted step of the database buffer it is necessary to issue a following command .
ROLLBACK Save point _ Name
OR
ROLLBACK TO SAVE POINT save point _ Name;
*BOOLEAN OPERATION: The where clause of a select statement allows the retriever of information according to the value of a specified column. Sometimes however you want to retrieve the information using the value more then one column.
In such a case the Boolean operators are used. The Boolean operators are available for crating a compound condition in the where clause of the statement. The compound condition is;
That contain two or more Boolean operators these operators are and, or, not
a) AND: The and operators is used to connect two Boolean condition one in the where clause of a select statement. When the SQL statement is executed, only those rows that satisfy both conditions are retrieves.
Syntax: SELECT Column_List
FROM table Name
WHERE Cond1 AND Cond 2;
Example: SELECT *
FROM emp
WHERE City = ‘Wardha’ AND
Emp_Des = ‘MANAGER’;
b) OR: The OR operators is also used to connect two Boolean condition in where clause of a select statement. The rows that are retrieving are those that satisfied either or bath condition.
Syntax: SELECT Column_List
FROM table Name
WHERE Cond1 AND Cond 2;
Example: SELECT *
FROM emp
WHERE City = ‘Wardha’ AND
Emp_Des = ‘MANAGER’;
c) NOT : Usually the where clause tells you which. Sometimes however you want to specify the information to execute, the NOT operator is used to specify the column name that are used to exclude a record from table.
The AND and OR may be used to connect two or more conditions. They are binary operator and the NOT acts only on one binary operator and the NOT acts only on one condition it is a unary operator.
Syntax: SELECT Column_List FROM table Name
WHERE NOT Condition;
Example:
SELECT * FROM emp WHERE NOT City = ‘Wardha’;
*PATTERN MATCHING (LIKE STATEMENT AND WILD CARD CHARACTERS)
All the previous example retrieve information based upon the exact value of the column or column. Sometimes you do not know the exact value, or you want to retrieve the information with similar values. The like operator is used to match specific pattern For Example: you might want to retrieve the information for everyone whose name begins with I or for all those who are living in Wardha.
SQL provide pattern matching for character column. The like operator world only with character string column compound condition can also be constructed combining the NOT, AND OR with LIKE.
a) AND – The and operators is used to connect two Boolean condition one in the where clause of a select statement. When the SQL statement is executed, only those rows that satisfy both conditions are retrieves.
Syntax: SELECT Column_List
FROM table Name
WHERE Column_Name
LIKE ‘PATTERN’;
WILD CARD
That is a percentage (%) sign. The percent sign is used as a wild card character that can represent a string of zero or more character.
For Ex: The pattern ‘S%’ sign will represent any column value beginning with upper case S of any length including the single letter S. The value must end in upper case S and contain no trailing block letters. The pattern ‘%S%’ will test for any word containing the upper case in any location. Remember that pattern inside the “ ” are case sensitive
Example :
SELECT *
FROM emp
WHERE City LIKE = ‘%WR’
1) Underline Character (-)
The underline character is used to represent a single character. Use it with character column where you know the exact length and needs a wild card for a specific number of character.
Ex: Select * form emp
Where Ph = ‘240………………’;
2) MATCHING VALUE IN A LIST OR A RANGE OF VALUES:
The like condition is used to match a specific pattern. Sometimes you want to select rows based upon the value of a certain range or value in specific list. Between and in are the operators used for these purposes (funs). Usually the Boolean operator AND/OR can be used to duplicate the queries using between and in operators.
i) Between – we use the between operator to identically range of acceptable value. To indicate range, both low value and high value must be specified and lower value must be specified first the range of value to be selected induces the low value, high and everything between.
ii) In operator – Use the in operator to choose a row based upon value in a particular list of any length. The list is specified in parenthesis is with the individual list item separator by comma.
Syntax:
SELECT column_list FROM Table Name
Where column_Name In (Value 1, Value 2,….);
Example:
SELECT Id, Name, Ph FROM emp
WHERE Id IN (‘305’, ‘400’, ‘430’, ‘520’);
*Data Types:
Explain different data types supported by SQL with example?
(a) Number: The Number data type is used to store numbers. It contains a number, with or without decimal point and a sign.
Integer data type: Integer data type consists of positive or negative whole number. There are four integer data types: Int-4bytes.
Smallint-2bytes.
Tinyint-1byte.
Bigint-8bytes.
Decimal data type: It consists of floating and real number 8 bytes is required in a memory to store float data type.
(b) Character (or text) data type: This data type is used for storing string, alphabet, any number and nay symbols.
Chars (size): This data type is used to store character strings values of fixed length. It holds maximum of 255 characters.
Varchar(size): This data type is used to stroe variable length alphanumeric data. Its holds maximum of 2000 characters.
(c) Date data type: This data type store not only the month, day and year nut also it stroes hour, minute and seconds. The standard format of date is DD-MM-YY.
(d) Long data type: We can used long data type to store characters data upto 2GB in length. Instead of long and locks RAW, we can used LOB data types (BLOB, CLOB). To storage of long data upto 4GB. Long Raw data types is used to store binary data such as images or store binary data such maximum of 255 bytes.
* Creating Tables: In Oracle, table is created by using created by using create table command. To used create table command we must specify name of columns in a table, name of table as well as characteristic of those columns.
Syntax: Create Table Table Name (columnname, data type (size), clumnname, data type (size)------);
For example: Create Table client_master (client_no varchar2(6), name varchar2(20), address varchar2(30));
The output of SQL statement is a table as follows:
Inserting data into a table: If we want to insert a data into a table Insert Into command is used.
Insert Into Tablename (columnname, columnname) values (expression, expression);
For example: Insert Into client_master (client_no, name, address) Values (‘c001’, ‘sachin hiware’, ‘ramnager’, ‘wardha’);
Output of this SQL Statement is:
Syntax: Select (client_no, name, address) from client_master;
* Dropping and Altering tables:
Dropping: Sometimes it is necessary to remove a table and all its components which are already exist in our data base. The SQL allows us to perform this task by using Drop command.
Syntax: Drop Table Tablename;
For example: Drop Table Client_master;
This will remove the table client_master from the database. In oracle, we used truncate command to remove all the rows in a table without removing the table definition in use data base. We cannot Rollback a truncate
statement. A truncate command is faster than delete command because it generates no rollback information. Truncate will allocate the space from delete rows.
Altering tables: Alter command can be used in three ways by. (i) Adding a column to an existing table. (ii) Changing a columns definition. (iii) Droppping a column.
If we want to add a column in an existing table the condition is that the column should be not null. The alter table command defined with add clause.
(i) Adding a new column:
Syntax: Alter Table TableName, Add ( new column data type (size), new column name data type (size),----);
For example: Alter Table client_master. Add (client_tel number (8), client_Id(15));
Modifying existing columns:
Syntax is: Alter Table Table name Modify (column name new data type (new size));
For example: Alter Table client_master Modify (client_tel number(10));
Limitation of alter Table: (i) We cannot change the name of table. (ii) we cannot change the name of column. (iii) Cannot decrease the size of column if data exists.
* Select command: Once the data is inserted into a table it would be view by using Select statements.
(1) To view all data in a table: Syntax is Select (columnname1, ----- columnname n) From tablename;
or
Select * From Tablename;
If we don’t want to specify all columns with select statement we must used * instead of of columnname.
For example:
Select name, address, and salary From Emp_master;
This is also done by using Select * From Emp_master;
It will display all columns in Emp_master.
(2) Eliminating Duplicates with distinct in select statement: The distinct keyword eliminates duplaicate news from the result set of a Select statement. If Distinct is not specified all rows are display.
For example: If we select the marks obtained column in stud_info table without Distinct keyword it will return a long list of marks. But after using Distinct keyword SQL server will return every marks ontained only once. This is done as follows.
Select distinct marks obtained From stud_info;
(3) Use of constants with Select statement: We can use constants with select statement.
For example: Select country + ‘:’ + city_code + ‘→’+ city_name From city_info
Output: India: 1001→Nagpur.
India:1002→Nashik.
India:1003→Pune.
(4) Sorting Data in a table using Select statement: The data can be sorted in either ascending or descending order depending upon the condition which is specified in the Select statement by using Order by statement.
Syntax: Select * from tablename Order By columnname, columnname [sort order];
To sorted data in descending we used ‘desc’ keyword.
For example: Select client_no, name, address From client_master Order By client_no desc;
* Creating Table From a Table: In SQL, we can create a new table from existing table. Here, source table is defined in between Select section and the target table is defined between Create Section.
Syntax: Create Table Tablename (column1, column2,-----)
As Select column1, column2,-----From Tablename;
For example: Create Table supplier_master (supplier_no, supplier_name, address, city, state)
As Select supplier_no, supplier_name, address, city, state From client_master;
1) Inserting data into table from another table:
(a) Inserting all records: To insert number of records at a time into a table from already exist table we use following syntax. Syntax is
Insert Into tablename Select columname, columnname From tablename;
Fr example: Inset Into supplier_master Select client_no, name, address, city, state From client_master;
Here we insert all records having columnname client_no, name, address, city and state into a supplier_master from client_master table.
(b) Insertin a specific record with condition:
syntax is
Insert Into tablename Select columnname, columnname From tablename Where column = expression;
For example: Insert Into supplier_master Select client_no, name, address city, state From client_master where client_no = 1001;
Here we insert a single record whose client_no is 1001 into a supplier_master table from client_master.
(2) Delete operations: This is done in SQL by two ways.
(a) All rows from a table: Syntax is
Delete From tablename;
Example: Delete From client_master;
Above SQL Statement deletes all the records in client_master.
(b) To remove specific rows: Syntax is
Delete from tablename Where condition;
For example:
Delete From client_master Where pin_code = 442001;
This statement will delete the record whose pin_code is 442001.
(3) Updating the contents of a table: Update command is used to change or modify data into a table.
(a) Update all rows from a table: Syntax is
Update tablename Set columnname = expression, columnname = expression;
For example: Update emp_master Set net_sal = net_sal + basic_sal*0.25;
(b) Update specific row from a table: Syntax is
Update tablename Set columnname = expression, columnname = expression Where clumnname = condition;
For example: Update client_master Set name = ‘vijay sahare’, address = ‘sainagar’ Where client_no = ‘c10012’;
Here the fileds name and address is changes by above condition whose client_no is ‘c1001’
(4) Modifying the structure of the table:
(a) Adding new column: This done with ALTER TABLE SQL command. Syntax: Alter Table tablename ADD (new columnname data type (size), New columnname data type (size));
For example: Alter Table client_master ADD (client_no (8), client_taxno (15));
Here, the fields client_no and client_faxno is added into client_master which holds 8 and 15 numbers respectively.
(b) Modifying existing column: Syntax is
Alter Table tableaname Modify (columname, new data type (new size));
For example: Alter Table client_master Modify (client_no varchar2 (25));
Here field client_no holds a maximum of 25 character value.
(5) Remaining a Table: To rename a table following command is used. Syntax is
Rename old Tablename to new Tablename;
For example: Rename client_master to client_supplier;
The above statement changes the name of client master table to new name client_supplier.
(6) Destroying tables: Syntax is
Drop Table tablename;
For example: Drop Table client_master;
This statement remove the table client_master.
(7) Displaying Records: For displaying records in a table we used following command. Syntax is
Describe Tablename;
For example: Describe client_master;
It will display the table client_master.
* Views: A view is a database object that is a logical representation of table. It is derived from a table but it has no storage of its own. It is also used in the same manner as a table. Views are created because when a data security is required. When data redundancy is kept to be minimum.
Creating a view: We can create a view by using create view statement. Syntax is:
Create View viewname As Select columnname, columnname From tablename Where columnname = expression list;
While creating view it is necessary to column of the table are related to the view on a one to on relationship.
For example: Create a view on the salesman_master table for the sales department,
Create View vw_sales As Select * from salesman_master;
In this example we create a view named vw_sales. Actually view do not contain any data. Tables contain a data.
Renaming view: We can declare different names to the column of view.
For example:
Create View vw_clientmaster As Select name, address, city from salesman-master;
Selecting a data from a view: Once a view has been created we can select data from it.
Syntax is
Select columnname, columnname From viewname;
Updating views: Views works as a data manipulator (i.e. user can perform insert, update and delete operation on it). The views on which data manipulation can be done are called as updatable views. Consider a following table,
SQL> insert into vw_client values (‘c001’, ‘Sachin’, ‘Ramnagar’, ‘agpur’);
(b) Modify operation using view:
SQL> update vw_client set Name = ‘Manish’, Where client_no = ‘c001’;
1 row updatd.
(c) Delete operation using view:
SQL> delete from vw_client Where client_no = ‘coo1’;
1 row deleted.
(d) Drop operation using view: Drop view command is used to remove view from the database.
Syntax: Drop View vw_clientmaster;
It removes the view named as vw_clientmaster from the database.
Restriction on updatable views: While updating a view, view definition must not include. (1) Aggregate function. (2) Distinct, group by or having clause. (3) Union, intersect or minus clause.
UNIT III ENDS HERE
<<<< Previous PAGE Next >>>>