Database Knowledge for a Tester Part II

1 SQL Concepts

1.1 Basic Data Types

1.2 Basic SQL Commands

1.1.1 Create Table

SYNTAX:
CREATE TABLE [Table Name] ([ColumnName1] [Datatype] ([size]), [ColumnName..n] [Datatype] ([size]));
Rule for creating table:
· Name can have maximum upto 30 chars.
· Name should begin with an Alphabet (A-Z, a-z) and optionally followed by numbers (0-9)
· Special character “_” underscore is allowed and also recommended.
· SQL reserved words like create, select etc are not allowed.
E.g.
CREATE TABLE employee (employee_no varchar2 (10), employee_name varchar2 (25));

1.1.2 Insert Data into Tables

SYNTAX:
INSERT INTO [TABLE NAME] ([COLUMNNAME1], [COLUMNNAME...N]) VALUES ([EXPRESSION1], [EXPRESSION2]);
For e.g.
INSERT INTO employee (employee_no, employee_name) VALUES (‘E001’, ‘Sachin Kumar’);
Note: Character & Date expressions placed with in the INSERT INTO statement must be enclosed in single quotes.
In INSERT INTO if the number of values is same as the number of column then there is no need to provide the column name in the INSERT INTO command provided the expression are provided in the same sequence and data type as defined during CREATE TABLE.
However if there is less values being inserted into the table, then it is mandatory to include both the Column Name and corresponding value in the INSERT INTO SQL command.

1.1.3 Viewing Table Data

View All Rows and All ColumnsSELECT * FROM [TABLE NAME];
Selected Column and All RowsSELECT [COLUMNNAME1], [COLUMNNAME…N] FROM [TABLE NAME];
Selected Rows and All ColumnsSELECT * FROM [TABLE NAME] WHERE [CONDITION];
Here [CONDITION] is always quantified as [ColumnName=Value]
Note: All Standard operators such as logical, arithmetic, predicates etc can be used when specifying where clause.Selected Column and Selected Rows
SELECT [COLUMNNAME1], [COLUMNNAME…N] FROM [TABLE NAME] WHERE [CONDITION];
Here [CONDITION] is always quantified as [ColumnName=Value]
Note: All Standard operators such as logical, arithmetic, predicates etc can be used when specifying where clause.Distinct Data or Eliminate Duplicate RecordsSELECT DISTINCT [COLUMNNAME1], [COLUMNNAME…N] FROM [TABLE NAME];
Display unique column values.SELECT DISTINCT * FROM [TABLE NAME];
Display unique rows from all the records.

1.1.4 Sorting Table Data

Syntax:
SELECT * FROM [TABLE NAME] ORDER BY [COLUMNNAME1], [COLUMNNAME…N] [[SORT ORDER]];
Note: The ORDER BY Clause can only be used in Select Statements.The Default sort order is ASC (ascending). To sort the record in descending order use DESC keyword as Sort Order.

1.1.5 Updating Table

Updating All RowsUPDATE [TABLENAME] SET [COLUMNAME1] = [EXPRESSION1], [COLUMNAME…N] = [EXPRESSION…N];
Conditional Update of RecordsUPDATE [TABLENAME] SET [COLUMNAME1] = [EXPRESSION1], [COLUMNAME…N] = [EXPRESSION…N] WHERE [CONDITION];

1.1.6 Alter Table

ALTER TABLE allows:
· Add/Delete columns
· Create or Destroy indexes
· Change the data type of existing columns
ALTER TABLE cannot be used for:
· Changing the name of the table & column
· Decrease the size of a column if table data exists.

SYNTAX:
Adding New ColumnALTER TABLE [TABLE NAME] ADD ([NEWCOLUMNNAME1] [DATATYPE]([SIZE]), [NEWCOLUMNNAME…N] [DATATYPE]([SIZE]));
Dropping A Column From a TableALTER TABLE [TABLE NAME] DROP COLUMN [COLUMNNAME];
Modifying Existing ColumnsALTER TABLE [TABLE NAME] MODIFY ([COLUMNNAME] [NEWDATATYPE] ([NEW SIZE]));
Note: To use Alter Table command, the ALTER, INSERT & CREATE privileges for the table are required.

1.1.7 Delete Table

Removal of All RowsDELETE FROM [TABLE NAME];
Removal of Specific RowsDELETE FROM [TABLE NAME] WHERE [CONDITION];

1.1.8 Truncate Table

Truncate Table like Delete Table deletes all rows from the table but there are some practical differences under some circumstances:
· Truncate is much faster than Delete as Truncate first drop the table and then re-create it.
· Truncate operations are not Transaction safe in case of any error while execution.
· The number of deleted rows are not returned.
Syntax
TRUNCATE TABLE [TABLENAME];

1.1.9 Drop Table

Drop table dropped all records from the table and permanently destroy table from the database which cannot be recovered.
Syntax:
DROP TABLE [TABLENAME];

1.2 Examining Objects Created by a User

1.2.1 Finding out the table/s Created by a user

SELECT * FROM TAB;
The tables created under the currently selected tablespace are displayed.

1.2.2 Displaying the table structure

DESCRIBE [TABLENAME];
The command displays the column names, the data types, and the special attributes connected to the table.

2. References

SQL, PL/SQL Third Edition by Ivan Bayross.

3. Download

Click here to download a PDF copy of this Article.