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.