Alternative way of finding the rowcount of a table

To get the total row count in a table, we usually use the following select statement:

SELECT count(*) FROM table_name

But the problem with this query is

It performs full table scan to get the row count.

If you enable your execution plan you can know this (Go to Query-> Choose Show execution Plan).

So if you have a table with say lakhs of rows, it gives a performance hit.

There is another way to determine the total row count in a table. You can use the sysindexes system table for this purpose. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

Please remember that this query works irrespective of whether the table has any indexes or not.

But for the second query if you see the execution plan you would find a index scan or an index seek based on the column you have used in your search criteria

Basics of SQL - partIV(Self Join)

Self Join is probably one of the most difficult concepts in database because we often get confused
about the appropriate situation which demands its use.

So its worth discussing.

Self join is essentially joining the same table with itself.

Now the question is why should anybody joins a table with itself.

Well as the business complexities increase, having a situation where the rows of a resultset would be consisting of information from more than one rows of itself becomes very much common. This becomes a classic self join scenario.

Now lets talk about the employee, manager example which probably all the institutes teach whoever teaches SQL . This is one of my favourite examples for self join as well.

Lets say we have a table called employee.

Now in this case if the business expects us to show the employee name and manager name in the same row, it becomes difficult or practically impossible without the use of a self join. But self join makes it very simple.

The query which is used for displaying the employee name and manager name in the same row is

Select a.name as Employee_Name, b.name as Manager_Name
from Employee a inner join Employee b
on a.mgrid=b.employeeid

Is not it very easy..

Basics of SQL - part III(Joins)

As Join is used the most by the Database programmers and the testers, a discussion on this would be very much valuable

What is meant by a join?

Its linking of more than one table on some common columns.

Why joins?

Because you may not get all the information from a single table.

Let me give you an example

Student Table

Topper Table
Lets say that we have two tables. 1) Students 2) Toppers

Lets say that two students have topped in the state. From the above tables you can understand that Shyam and Sonali have topped in the state. But how do we display their names and the scholarships they have received. Here join helps us.

With joins you can combine more than one table and retrieve informations that is required.

For our scnario(where we are interested for the students names and the scholarships they have got) we can go for the inner join.

Inner join links the tables on the common columns. As its visible, the common columns are the Studentid and the Topper id.

The inner join is written like the following

Select a.Name as Student_Name, b.Scholarship
from Student a inner join Toppers b
on a.Studentid=b.topperid

It would return a resultset like the following

Student_Name Scholarship

Now is it not exciting? But would this query be able to help us when we have a need of displaying the information which consists of toppers and the non-toppers. Lets say the principal is interested to see the students who have topped and what scholarships they have got and the students who have not topped.
We have to go for a left outer join here

It can be writeen in the following way

Select a.Name as Student_Name, b.Scholarship
from Student a left outer join Toppers b
on a.Studentid=b.topperid

Now the resultset would look like the following

So a left outer join consists of

1) Matching rows
2) All the non-matching rows from the left table ( as defined in the query) with null values for columns that are from the right table


Similarly right outer join consists of
1) Matching rows
2) All the non-matching rows from the right table ( as defined in the query) with null values for columns that are from the right table
Cross join is even another type of join where resultset is formed from all the possible combinations between two tables.

Basics of SQL - part II

In the last post we discussed about the basic statments like Create, Insert, Update and Delete statements. In this post we will play around with queries. For sorting the resultset, we can use the order by clause.

Lets create a table called Salary for using in our queries.

Execute the following statements.

Create table Salary(name varchar(30), Salary int)

and then insert some rows into the table. Insert the following rows.

Insert Salary values('A',759)
Insert Salary values('A',312)
Insert Salary values('A',456)

Now if you see the contents of the table by a select statement say

Select * from Salary

You would get the resultset like the following.

If you see in the picture the rows are not sorted as per salary. If you want to sort the rows by salary, you can use the following statement

Select * from Salary order by salary.

Now how to get the maximum salary. For this execute a statement like the following.

Select max(Salary) from Salary

For getting the minimum salary, execute the following query

Select min(salary) from Salary

You must have heard about the Desc command in Oracle which displays the schema information about a table. Similarly, in sql you have the sp_help statement which displays the information related to the structure of an object.

Execute the following statement

sp_help Salary

Its output would consists of the table information like name of the table, the columns the table has and any constraint the table is having

Now going back to the queries how could you get the total salary. For this, you can go for the sum function. Execute the following statement.

Select sum(salary) from Salary

What if you are interested to know the average salary. For this you can execute the following query

Select avg(salary) from Salary

Basic SQL

In this post, we will discuss the basics of SQL, the language which is used to communicate with the database. But please note that we will follow the t-sql syntax throughout the topic.

As you might have known earlier, the database essentially has tables to store data and the table in turn contains rows and columns. Columns are for the attributes and rows are for records. To make it simplier to create a table I am not writing the syntax here, in stead I am writing an example. You can run these examples in your PC and create objects.

An example for creating a table is

Create table MyAddress(Address1 varchar(20), Address2 varchar(20),City varchar(10), State varchar(30))

When you run this command in query analyzer, one table will be created and its name would be MyAddress and it would have 4 columns called Address1,Address2,City and State.

Now we have created a table. But a table is a storage structure. So it should be populated with data. For populating it, we use the Insert statement.

An example for inserting values to it is

Insert into MyAddress(Address1,Address2,City,State)
values('Near Satyam Theatre','Ameerpet','Hyderabad','Andhra Pradesh')

Execute the above statement. It would insert one row with values in the respective columns.

In SQL Server into Keyword is optional. You can use only Insert also.

Now you would be interested to see the contents of the table. For seeing the contents of the table you would have to use the Select statement. An example for a select statement is Select * from MyAddress and find out that it has a row with all the values mentioned above. Now inserting is not enough. You might have entered value(/s) wrongly. In that case the RDBMS must give you an option to change the information entered already. Update statement is used to serve this purpose. An example of an update statement is
Update MyAddress set Address2='Begumpet' where Address2='Ameerpet'

This statement would change the Address2 column to Begumpet in the row(/s) whose Address2 was Ameerpet.

In certain instances, we might be interested to delete one or more records from the table. For deleting all the rows of a table you can use the following statement.

Delete from MyAddress

The above statement deletes all the rows from the table MyAddress. You can also add a condition to conditionally delete row(/s) from a table. You can use a statement like the following.

Delete from MyAddress where Address2='Begumpet'

But remember that Delete statment only deletes records from the table but it does not delete the structure. For deleting the table object completely, you can use the Drop statement. Observe the following statement.

Drop table MyAddress

This would drop the table object completely. To check this fact, you can now try to execute the statement Select * from MyAddress.

You should get an error Object does not exist.

Difference between Database and DBMS

Many people who are new to databases wrongly think DBMS as database. But there is a difference between a database and DBMS. Database basically is a collection of interrelated data whereas the DBMS is an application which provides an interface for querying and/or updating data in a database.

History of DBMS

Before the birth of DBMS, there were many types of Data Management System. Out of the different Data Management Systems the best one was the File Based Management System. But it was lacking flexibility. So users wanted a data management system which would be much flexible. This is where the DBMS took birth. Again in DBMS also, there were different types of DBMSs. The first prominent one was Hierarchical DBMS whose concept was entities in the organization always follow a hierchical relationship. But it lost flexibility when the no of layers in the hierarchy became high and interaction was needed across hierarchical layers. The next one was the network dbms which was flexible and it had multiple entry points. But the only problem with this type of DBMS was the relationship between layers had to be implemented by embeded pointers and indexes which would have needed lots of resources. So a more flexible system was needed and the answer to this need was RDBMS where the relationship was implemented only by matching values between the parent table(s) and the child table(s).

Objective

The objective of this room is to educate testers in SQL Server and SQL as many recruiters are expecting the candidate to be good in SQL.