How to read data in a table with "SELECT" statements?
Submitted by: AdministratorNow this part is for creating database objects with Transact-SQL statements. This Question shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this Answer is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This Guide assumes that you are running SQL Server Management Studio Express.
Use the SELECT statement to read the data in a table. The SELECT statement is one of the most important Transact-SQL statements, and there are many variations in the syntax. For this Answer, you will work with five simple versions.
To read the data in a table - Type and execute the following statements to read the data in the Products table.
-- The basic syntax for reading data from a single table
SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
GO
You can use an asterisk to select all the columns in the table. This is often used in ad hoc queries. You should provide the column list in you permanent code so that the statement will return the predicted columns, even if a new column is added to the table later.
-- Returns all columns in the table
-- Does not use the optional schema, dbo
SELECT * FROM Products
GO
Submitted by: Administrator
Use the SELECT statement to read the data in a table. The SELECT statement is one of the most important Transact-SQL statements, and there are many variations in the syntax. For this Answer, you will work with five simple versions.
To read the data in a table - Type and execute the following statements to read the data in the Products table.
-- The basic syntax for reading data from a single table
SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
GO
You can use an asterisk to select all the columns in the table. This is often used in ad hoc queries. You should provide the column list in you permanent code so that the statement will return the predicted columns, even if a new column is added to the table later.
-- Returns all columns in the table
-- Does not use the optional schema, dbo
SELECT * FROM Products
GO
Submitted by: Administrator
You can omit columns that you do not want to return. The columns will be returned in the order that they are listed.
-- Returns only two of the columns from the table
SELECT ProductName, Price
FROM dbo.Products
GO
Use a WHERE clause to limit the rows that are returned to the user.
-- Returns only two of the records in the table
SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
WHERE ProductID < 60
GO
You can work with the values in the columns as they are returned. The following example performs a mathematical operation on the Price column. Columns that have been changed in this way will not have a name unless you provide one by using the AS keyword.
-- Returns ProductName and the Price including a 7% tax
-- Provides the name CustomerPays for the calculated column
SELECT ProductName, Price * 1.07 AS CustomerPays
FROM dbo.Products
GO
Submitted by: Administrator
-- Returns only two of the columns from the table
SELECT ProductName, Price
FROM dbo.Products
GO
Use a WHERE clause to limit the rows that are returned to the user.
-- Returns only two of the records in the table
SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
WHERE ProductID < 60
GO
You can work with the values in the columns as they are returned. The following example performs a mathematical operation on the Price column. Columns that have been changed in this way will not have a name unless you provide one by using the AS keyword.
-- Returns ProductName and the Price including a 7% tax
-- Provides the name CustomerPays for the calculated column
SELECT ProductName, Price * 1.07 AS CustomerPays
FROM dbo.Products
GO
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How to download and install the scaled-down database AdventureWorksLT? |
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | How To Convert a Numeric Expression from One Data Type to Another? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |