SELECT Test1, Test2 FROM Scores in Visual Studio .NET

Drawing UCC - 12 in Visual Studio .NET SELECT Test1, Test2 FROM Scores

If you want to get back all the columns, you can either type all the column names (again, separated by commas), or just use an asterisk like this:

When you use the SELECT statement, by default you ll get back the columns you specify for every single row in the table Often that s way more information than you want; you probably only want some of the rows, not all of them Suppose you have a table filled with the million-or-so users who have signed up with your Web site and are paying you $200 per month, and you need to look up one user You don t want to have to grind through a million or so records looking for the data after all, the database is supposed to do that for you! The SELECT statement makes such a search easy by letting you specify exactly which rows you want back The way you specify the rows is by using what s called a WHERE clause Here s an example:

This statement returns the row that has a 1 for the StudentID column (If, for some reason, you put two rows in your table with a StudentID of 1, then you get back the values in both rows) You can put all sorts of conditions in the WHERE clause For numbers you can use less-than (<), greater-than (>), equals (=), and so on You can combine your conditions with Boolean operators such as AND and OR, like so:

This SELECT statement will return all StudentID s where both Test1 is greater than or equal to 90 and Test2 is greater than or equal to 90; that is, you ll find out which students got As on the first two tests (assuming an A is 90 or better)

To add data to a database, you use the INSERT statement This statement is easy to use except it s kind of wordy, and you can only enter one row of data at a time Here s an example:

INSERT Scores (StudentID, Test1, Test2, Test3, FinalExam) VALUES (1, 100, 100, 90, 95)

You start with the word INSERT, followed by the table name Then you list the column names you wish to set for this row you re inserting You put the whole set of column names inside parentheses, and separate the names with commas

Then you type the word VALUES, followed by the corresponding values for the columns The values are also separated by commas and the whole set is surrounded by parentheses Note that the previous example applies only to tables where you don t have an identity column If you do have identity column in your table, you can t specify it in the INSERT statement because only the database server is allowed to fill in the value for such a column Thus the preceding INSERT would have to be modified like so:

INSERT Scores (Test1, Test2, Test3, FinalExam) VALUES (100, 100, 90, 95)

Notice I m only specifying the remaining four columns, and not the identity column; the database server will fill that column in with the next available value for the column In the preceding two INSERT samples, I wrote the statement over three lines You don t have to do that; if you can fit the whole thing on one line, it s okay to do so Or you can break it up as much as you want and add extra spaces provided you don t break up individual words or numbers Thus the following is acceptable as well:

INSERT Scores (StudentID, Test1, Test2, Test3, FinalExam) VALUES (1, 100, 100, 90, 95)