Database Testing - SQL Lesson 4 - SQL Alias

Previous

1

2

3

4

5

6

7

8

9

10

SQL Alias

SQL Alias is used to make a column more readable when the output is displayed. They are defined for columns and tables. Instead of displaying the column name we use an Alias to bring in more meaning.

For Example: Instead of displaying the column header for an output as ‘First_Name” it is better to display as First Name. Since the column names are already created when the table is created and would be difficult to modify them because of the constraint it is better to give a meaningful name in the query.

Aliases given for columns:

The query can be written in either ways.

SELECT first_name as First Name from EmployeeDetails;
or
SELECT first_name as First Name from EmployeeDetails;

Look at this query this would really give you a clear picture of Alias.

We want to join the first and last name from the EmployeeDetails table.

SELECT FirstName + ' ' + LastName from dbo.EmployeeProfile;

If we write the query like this then it will display full names but the name of the column will not be meaningful.

Output:

No Column Name
-------------
Ravi Kumar
Rahul Patil
Sania Mirza

Now if you rewrite your query using an SQL Alias then it will be readable.

SELECT FirstName + ' ' + LastName as 'Full Name' from dbo.EmployeeProfile

Output:

Full Name
----------
Ravi Kumar
Rahul Patil
Sania Mirza

Aliases given for Tables:

Aliases are more useful when we have to deal with multiple tables or columns which are having difficult names to understand. Look at the below

1. If more than one table is involved in the query.
2. If functions are used in a query.
3. If the column names are big or not understood.

Example:

SELECT e.FirstName from EmployeeDetails e;

From the above query it is understood that e is referred to the table EmployeeDetails and the FirstName is taken from the EmployeeDetails table.

Previous

1

2

3

4

5

6

7

8

9

10

Database Testing - SQL Lesson 3 - SQL SELECT Statement

Previous

1

2

3

4

5

6

7

8

9

10

Next

SQL SELECT Statement

SELECT statement is the most commonly used command in SQL. The SELECT statement allows the user to retrieve records/ data or do a query in a table from the database. The query may retrieve information from column or columns from a table. To create a SELECT Statement the column(s) name must be specified and also the table name from which they have to retrieved.

SQL SELECT Statement Syntax

SELECT column1, column2 from tablename
[WHERE condition]
[GROUP BY condition]
[HAVING condition]
[ORDER BY condition];

• 
  tablename is the name of the table from which the data is retrieved.
• column1, column2 are the names of the columns from which data is retrieved.
• The codes in the brackets are optional.

Conditional selections used in the where clause

= Equal
> Greater Than
<>= Greater Than or Equal To
<= Less Than or Equal To <> Not Equal

Sample Database Table - EmployeeDetails

UserId	EmailAddress	FirstName	LastName	Age	Salary	City
1	ravi@123.com	Ravi	Kumar	20	20,000	Bangalore
2	kiran12@12343.com	kirankani	paul	25	35,000	Mumbai
3	rahul@123.com	Rahul	Patil	28	25,000	Pune
4	mirza@123	Sania	Mirza	23	50,000	Hyderabad
5	shoaib@123.com	shoaib	Malik	28	50,000	Hyderabad
6	prisha@123.com	Prisha	Ruth	21	21,000	Delhi

Please Note: The table mentioned is for better understanding and for explanation purpose but the real tables may contain more or less columns. All the examples are based on the table mentioned above.

Example 1

Let us start learning by selecting all fields from the table EmployeeDetails

SELECT * from EmployeeDetails;

When this query is run it will display all the columns of the table.

Example 2: If you want to retrieve data for some fields from the table than selecting all fields using the “*” in select command. You can do this way. S

SELECT EmailAddress, FirstName from EmployeeDetails;

This select statement would return all Email Address and First Names from the EmployeeDetails Table.

You can also use the clauses with SELECT statement like WHERE, GROUP BY, HAVING, ORDER BY. We will be discussing them at a later stage.

Please Note: It is not mandatory to use the WHERE, GROUP BY, HAVING, ORDER BY with the SELECT statement but in a SELECT Statement only SELECT and FROM are mandatory.

Using expressions in an SQL SELECT Statement

As discussed earlier we can use arithmetic operators in the expressions in the SELECT statement using the WHERE, HAVING, ORDER BY clauses. The operators are executed in a defined and set order of precedence where there is more than one arithmetic operator in the statement.

The order of evaluation of Arithmetic operators in an expression is parentheses, division, multiplication, addition, and subtraction. The evaluation starts from the left to the right of the expression.

Example 3

If you want to list out all the employee details whose salary is greater than 30,000 then you will write the query like this.

SELECT * from EmployeeDetails WHERE Salary >30,000;

Other operators will be discussed in the relevant sections.

Previous

1

2

3

4

5

6

7

8

9

10

Next

Database Testing - SQL Lesson 2 - SQL Commands

Previous

1

2

3

4

5

6

7

8

9

10

Next

What are the different types of SQL Commands?

The SQL commands are used to interact or communicate with the database to perform specific tasks. SQL commands are used to do various functions apart from searching the database. Basically SQL Commands are divided in to four categories they are Data Manipulation Language (DML), Data Definition Language (DDL), Data Control Language (DCL), Transactional Control Language (TCL) but mostly DML and DDL commands are frequently used.

Let us look into details of these different kinds of SQL Commands.

Data Manipulation Language

Data Manipulation Language or what is frequently referred as DML is used to manipulate data in a table or in the database. The basic objective of these commands are to retrieve, store, modify, delete, insert and update data.

Different types of DML Commands

The different kinds of Data Manipulation Language commands are given below. They are
1. INSERT Command: This command is used to insert records or columns in a table.
2. UPDATE command: This command is used to update the records or columns already existing in a table.
3. DELETE Command: This is used to delete an already existing record in a table. This command only deletes the values in the record of the table but not the structure.

Data Definition Language

Data Definition Language is often referred as DDL and these commands are used to create, modify the structure of tables or objects in the database.

Different types of DDL Commands

The different kinds of Data Definition Language commands are given below. They are

1. CREATE Command: The Create command is used to create any object in the database. The objects can be a Table, View, Trigger or Stored procedure.
2. ALTER Command: The Alter command is used to modify any object like Table, View, Trigger or Stored procedure in the database
3. DROP Command: The Drop command is used to delete or remove a table from the database or even a database itself from the server. The Drop command permanently removes the structure from the database or server and there is no way to recover it. One needs to be careful in using this command.
4. TRUNCATE Command: The Truncate command allows the user to delete all the rows of a table in the database.
5. RENAME Command: The Rename command is used to modify the names of the objects in the database.

Data Control Language

Data Control Language is often referred as DCL and these commands are used in providing security to the database like creating roles, setting permissions, maintaining integrity and also to control access to the database. The commands used are GRANT and REVOKE.

Transactional Control Language

Transactional Control Language is often referred as TCL and these commands are used in managing changes affecting the database. The commands used are COMMIT and ROLLBACK.

Previous

1

2

3

4

5

6

7

8

9

10

Next

Database Testing - SQL Lesson 1

What is SQL?

SQL stands for Structured Query Language. SQL is a means to communicate with the Database. SQL is a standard language used to communicate with the Relational Database Management Systems.

The SQL statements are used to perform different operations such as inserting or updating a table or like wise many. Oracle, Sybase, Microsoft SQL Server, Access, Ingres are some of the most common RDBM Systems where SQL is used.

As a Tester it is enough to know how Select, Insert, Update, Delete, Create and Drop commands work because most of the Testing task in Database can be accomplished with these commands.

In the next pages you can find more information on how to use these commands.

1

2

3

4

5

6

7

8

9

10