Displaying records using SELECT & WHERE Query in MSSQLWe can collect records from MSSQL database tables by using SELECT query. We can even apply SELECT query to more than one table and retrieve linked records from the table.
We will focus on how to use the query in different combinations and you can read all details about record set object, connection strings etc here.
The above command will collect all the records for the table. But this is not a efficient way to display. Let us try to display all records of class four using one where clause. Here is the query.
Using ANDHere is the query to collect records of class four and who secured mark more than 50
Before we move further let us learn the full code where the records taken from database will be displayed inside an html table.
You can read more about SELECT query in our SQL section
Retrieving all the records is not a good idea so let us learn different ways to collect part of the records.
You can create the student table by using this query
You can download the csv file for all student table data by suing this student.csv file.
Delete records from table in MSSQL database with where conditionTo delete all records we can use simple delete command like this
This will remove all the records of members table of MSSQL database. We can also add some conditions to the query by adding a Where clause to delete a perticular record like this.
Random record from MSSQL table by using TOP & NEWID()We can get random records from MSSQL tables by using TOP and NEWID() functions. Here is the SQL to get one random record each time the query is executed.
To get more than one ( say 3 ) records here is the query.
Using TOPWe can collect some records from a large number or records of a MSSQL table by using TOP clause in our query. We have to specify the field where we want to apply the TOP command and number of records we want to retrieve.
Using this command we can collect first n records ( say 5 here ) from the student table. Here is the query.
Note that the above query is to be applied to the script we have written in our SQL select section. You have to replace the above query line in appropriate place. ( we are not repeating the same script again here ) .
The command TOP only searches for 5 records and it does not go in any order. Let us try to collect to 10 students based on the mark they have secured. Here we will use another sql command ORDER BY ( Read more on ORDER BY command in our SQL section ) . Here is the command.
The above query will return 5 lowest mark records. By default the order by clause return in ascending order so to change that we have to add DESC to the query to get top 5 rank holders in student table.
You can read more on order by command in our sql section.
Pattern Matching in a query by using LIKE commandWe can use LIKE command inside a query with SELECT & WHERE combination to get records with some string matching. This is of great use while we are developing a search query or any other type of application where we expect to get a matching pattern to the query.
LIKE command is used for MySQL in our sql section.
Here is the query we designed to collect all the records from student table starting with letter J
Now let us find out the records ending with a single char e
Now let us find out the records having two chars inside their name any where.
This way we can use wildcard to get the required pattern combinations in our query. Now let us try to match underscore ( _ ) as a replacement for a single position.
The above query will return all the records with mark is between 50 and 59 ( both ends inclusive ) . Here we have specified that the first digit should be 5 and second digit can be any thing. We can sue underscore for more than one place also.
Counting the records in a MSSQL TableIt is very often required to know the total records present in a table or number of records matching to our query. There is a built in RecordCount method of the RecordSet object. To use this RecordCount method in our MSSQL table we have to use scrollable cursor. If we are not using a scrollable cursor and using a forward-only cursor then RecordCount method will return -1.
There is a line opening the cursor in forward only mode.
Change this line to
Now we can add the line to display the total number of records before the Do while loop line this
Using Count SQL command to know the total records.Now you must have understood the purpose of using scrollable cursor to count the records. We can use the command COUNT ( read more on SQL COUNT Here )to get the total records present in our query or table. This command is a SQL part so no need to use any scrollable cursor. Here is a simple query to get the total number of records present.
However Count command can be used along with GROUP BY command to develop powerful reports. We will discuss that in our next tutorial.
Group by query in MSSQL TableIn our student database we have seen how count query works. Now we will discuss how to display total number of records based on GROUP By SQL command. You can read more on GROUP BY command here.
Let us try to display total number of students in each class. Here we will display class name and number of students in the class.
Within the Do While loop we will keep this code to display the data.
GROUP BY command with SUMWe can find out total mark of students in each class by using SUM and GROUP BY sql commands. Here is the query and display part
GROUP BY command with AVGSame way we can get the average mark in each class by using AVG command with GROUP BY
GROUP BY command with MAX and MINSame way we can find out the highest mark in each class by using MAX command and the minimun mark by using MIN query
SELECT WHERE Query with AND OR & NOT combinationsWe will try to add AND and OR combination to our SELECT query.
You can also read how AND and OR is used in SQL script for MySQL tables
Let us say we are interested in getting records of class Five only.
Here is the query.
Now we will collect records of the student of class five who has secured more than 80 marks.
Now let us try to collect all the records who have secured more than 80 marks. But we will add one more condition . Along with all these records we also want the records of class Four. Here is the query
Note how we have added two conditions with one OR.
Now let us add one NOT combination to get all the student records who has secured more than 80 mark but we don't want student of class Four.
Delete table in MSSQL databaseWe can delete a table from MSSQL database by using DROP table command ( Query ) .
You can test this query in your Microsoft SQL Server Management Studio
The above command will delete the table member from the database. What happens we run the same query again. You will get an error message like this
How to check the existence of the table before deleting to avoid the error message ? We will modify our query and add the command IF EXISTS to it.
We can also search INFORMATION_SCHEMA to check if table exists or not.
This article is written by plus2net.com team.
Be the first to post comment on this article :