Ques 41. How to read data in a table with "SELECT" statements?
Now 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
Ques 42. How To Generate CREATE TABLE Script on an Existing Table in MS SQL Server?
If you want to know how an existing table was created, you can use SQL Server Management Studio to automatically generate a "CREATE TABLE" script The following tutorial shows you how to do this:
1. Run SQL Server Management Studio and connect to SQL server.
2. On the Object Explorer window, follow the object tree: Databases > WithoutbookDatabase > Tables > dbo.wb.
3. Click right mouse button on dbo.wb. The context menu shows up.
4. Select "Script Table as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:
USE [WithoutbookDatabase]
GO
/****** Object: Table [dbo].[wb]
Script Date: 05/05/2008 11:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wb](
[id] [int] NOT NULL,
[subject] [varchar](80)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [varchar](256)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[create_date] [datetime] NULL,
PRIMARY KEY CLUSTERED (
[id] ASC
)WITH (PAD_INDEX = OFF,
IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Ques 43. How to create a login account in MS SQL Server to access the database engine using "CREATE LOGIN" statements?
Now this answer will teach you that how to create login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
To access the Database Engine, users require a login. The login can represent the user's identity as a Windows account or as a member of a Windows group, or the login can be a SQL Server login that exists only in SQL Server. Whenever possible you should use Windows Authentication.
By default, administrators on your computer have full access to SQL Server. For this lesson, we want to have a less privileged user; therefore, you will create a new local Windows Authentication account on your computer. To do this, you must be an administrator on your computer. Then you will grant that new user access to SQL Server. The following instructions are for Windows XP Professional.
Ques 44. How to create new tables with "SELECT ... INTO" statements in MS SQL Server?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "SELECT ... INTO" statement. The tutorial script below gives you a good example:
INSERT INTO wb VALUES (1, 'Learn SQL',
'Visit www.Withoutbook.com','2006-05-01')
GO
SELECT * INTO wbBackup FROM wb
GO
(1 rows affected)
SELECT * FROM wbBackup
GO
id subject description create_date
1 Learn SQL Visit www.Withoutbook.com 2008-05-01
sp_columns wbBackup
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo wbBackup id int ...
dbo wbBackup subject varchar ...
dbo wbBackup description varchar ...
dbo wbBackup create_date datetime ...
As you can see, the "SELECT ... INTO" statement created a table called "wbBackup" using the same column definitions as the "wb" table and copied all data rows into "wbBackup".
Ques 45. How to create a user to access a database in MS SQL Server using "CREATE USER" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
Mary now has access to this instance of SQL Server 2005, but does not have permission to access the databases. She does not even have access to her default database YourDataBaseName until you authorize her as a database user.
To grant Mary access, switch to the YourDataBaseName database, and then use the CREATE USER statement to map her login to a user named Mary.
To create a user in a database - Type and execute the following statements (replacing computer_name with the name of your computer) to grant Mary access to the YourDataBaseName database.
USE [YourDataBaseName];
GO
CREATE USER [Mary] FOR LOGIN [computer_nameMary];
GO
Now, Mary has access to both SQL Server 2005 and the YourDataBaseName database.