SQL Server 2012 Tutorials - download microsoft com

SQL Server 2012 Tutorials: Writing Transact-SQL Statements SQL Server 2012 Books Online Summary: This tutorial is intended for users who are new to writing SQL statements. It will help new users get started by reviewing some basic statements for creating tables and inserting data. This tutorial uses Transact-SQL, the Microsoft implementation of the


Texto en PDF


Contents
Tutorial:Writing TransactSQL Statements...........................................................................................................

Lesson 1: Creating Database Objects......................................................................................................................

Creating a Database (Tutorial)................................................................................................................................

Creating a Table (Tutorial).......................................................................................................................................

Inserting and Updating Data in a Table (Tutorial)..........................................................................................

Reading the Data in a Table (Tutorial).............................................................................................................

Summary: Creating Database Objects..............................................................................................................

Lesson 2: Configuring Permissions on Database Object.............................................................................

Creating a Login.......................................................................................................................................................

Granting Access to a Database...........................................................................................................................

Creating Views and Stored Procedures...........................................................................................................

Granting Access to a Database Object.............................................................................................................

Summary: Configuring Permissions on Database Objects.......................................................................

Lesson 3: Deleting Database Objects...................................................................................................................

Deleting Database Objects................................................................................................................................... 4

Tutorial: Writing TransacSQL Statements
Welcome to the Writing TransactSQL Statements tutorial. This tutorial is intended for users who are new to writing SQL statements. It will help new users get started by reviewing some basic statements for creating tables and inserting data. This tutorial uses TransactSQL, the Microsoft implementation of the SQL standard. This tutorial is intended as a brief introduction to the TransactSQL language and not as a replacement for a TransactSQL class. The statements in this tutorial are intentionally simple, and are not meant to represent the complexity found in a typical production database.Novice users of databases will usually find it easier to work with SQL Server by using SQL Server Management Studio, instead of writing TransactQL statements.Finding More InformationTo find more information about any specific statement, either search for the statement by name in SQL Server Books Online, or use the Contents to browse the 1,800 language elements listed alphabetically under
TransactSQL Reference (Database Engine)
. Another good strategy for finding information is to search for key words that are related to the subject matter you arinterested in. For example, if you want to know how to return a part of a date (such as the month), search the index for dates [SQL Server], and then select dateparts. This takes you to the topic
DATEPART (TransactSQL)
. As another example, to find out how to work with strings, search for string functions. This takes you to the topic
String Functions (TransactSQL)
What You Will LearnThis tutorial shows you how to create a database, create a table in the database, insert data into the table, update the data, read the data, delete the data, and then delete the table. You will create views and stored procedures and configure a user to the database and the data.This tutorial is divided into three lessons:
Lesson 1: Creating Database Objects
In this lesson, you create a database, create a table in the database, insert data into the table, update the data, and read the data.
Lesson 2: Configuring Permissions on Database Objects
In this lesson, you create a login and user. You will also create a view and a stored procedure, and then grant the user permission to the stored procedure.
Lesson 3: Deleting Database Objects
In this lesson, you remove access to data, delete data from a table, delete the table, and then delete the database.
Note 5

RequirementsTo complete this tutorial, you do not have to know the SQL language, but you should understand basic database concepts such as tables. During this tutorial, you will create a database and create a Windows user. These tasks require a high level of permissions; therefore, you should log in to the computer as an administrator.Your system must have the following installed:Any edition of SQL Server.Either SQL Server Management Studio or Management Studio Express.Internet Explorer 6 or later.When you review the tutorials, we recommend that you add the Nextand Previousbuttons to the document viewer toolbar.
Lesson 1: Creating Database Objects
This lesson 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 lesson is an introduction to using TransactSQL, it does not use or describe the many options that are available forthese statements.TransactSQL statements can be written and submitted to the Database Engine in the following ways:By using SQL Server Management Studio. This tutorial assumes that you are using Management Studio, but you can also use Management Studio Express, which is available as a free download from the
Microsoft Download Center
. By using the
sqlcmd utility
. By connecting from an application that you create. The code executes on the Database Engine in the same way and with the same permissions, regardless of how you submit the code statements.To run TransactSQL statements in Management Studio, open Management Studio and connect to an instance of the SQL Server Database Engine. This lesson contains the following topics:
Creating a Database (Tutorial)

Creating a Table (Tutorial)

Inserting and Updating Data In a Table (Tutorial)

Reading the Data in a Table (Tutorial)

Summary: Creating Database Objects

Note 7

Creating a Table (Tutorial)To create a table, you must provide a name for the table, and the names and data types of each column in the table. It is also a good practice to indicate whether null values are allowed in each column.Most tables have a primary key, made up of one or more columns of the table. A primary key is always unique. The Database Engine will enforce the restriction that any primary key value cannot be repeated in the table.For a list of data types and links for a description of each, see
Data Types (TransactSQL)
The Database Engine can be installed as case sensitive or noncase sensitive. If the Database Engine is installed as case sensitive, object names must always have the same case. For example, a table named OrderData is a different table from a table named ORDERDATA. If the Database Engine is installed as noncase sensitive, those two table names are considered to be the same table, and that name can only be used one time.Procedures
Enter the following code into a Query Editor window.
Press the F5 key to execute the code and create the database.

In a Query Editor window, type and execute the following code to change your connection to the database.
USE TestData

Note

To create a database to contain the new table

Switch the Query Editor connection to the TestData database 9

The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted. The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler. In this case, the comment describes a permissible variation of the syntax.Procedures
Execute the following statement to insert a row into the table that was created in the previous task. This is the basic syntax.
The following statement shows how you can change the order in which the parameters
are provided by switching the placement of the ProductIDand in both the field list (in parentheses) and in the values list.The following statement demonstrates that the names of the columns are optional, as long as the values are listed in the correct order. This syntax is common but is not recommended because it might be harder for others to understand your code. is specified for thecolumn because the price for this product is not yet known.
The schema name is optional as long as you are a
ccessing and changing a table in your
default schema. Because the ProductDescriptioncolumn allows null values and no value is being provided, the
ProductDescription
column name and value can be
To insert data into a table
10

dropped from the statement completely.
GO

Type and execute the following statement to change the of the cond product from , to Flat Head Screwdriver
GO

Next Task in Lesson
Reading the Data in a Table (Tutorial)
See Also
INSERT (TransactSQL)

UPDATE (TransactSQL)
Reading the Data in a Table (Tutorial)Use the SELECT statement to read the data in a table. The SELECT statement is one of the most important TransactSQL statements, and there are many variations in the syntax. For this tutorial, you will workwith five simple versions.ProceduresType and execute the following statements to read the data in the table.You can use an asterisk to select all the columns in the table. This is often used in ad
To update the products table

To read the data in a table
12

String Functions (TransactSQL)

Date and Time Functions (TransactSQL)

Mathematical Functions (TransactSQL)

Text and Image Functions (TransactSQL)

Next Task in Lesson
Summary: Creating Database Objects
See Also
SELECT (TransactSQL)
Summary: Creating Database ObjectsIn this tutorial you have created a database, created a table in the database, inserted data into the table, changed the data, and then read the data from the table. The syntax for the statements that were used is only the basic syntax and many syntax options were not covered in this tutorial. To learn more about these statements, read the complete syntax for the statements in SQL Server Books Online, and review the many examples that are provided in those topics.Next Lesson
Lesson 2: Configuring Permissions on Database Objects
See Also
CREATE DATABASE (TransactSQL)

Lesson 2: Configuring Permissions on Database Objects
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 lesson contains the following topics:
Creating a login

Granting Access to a Database

Creating Views and Stored Procedures

Granting Access to a Database Object

Summary: Configuring Permissions on Database Objects
13

Next Task in Lesson
Creating a login
Creating a LoginTo 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 aSQL Server login that exists only inSQL 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. Procedures
Click Start, click Run, in the Openbox, type %SystemRoot%system32compmgmt.msc /s, and then click to open the Computer Management program.Under System Tools, expand Local Users and Groups, rightclick Users
, and then click
New UserIn the User namebox type MaryIn the Passwordand Confirm passwobox, type a strong password, and then click Createto create a new local Windows user.
In a Query Editor window of SQL Server Management Studio, type and execute the following code replacing with the name of your computer. indicates that Windows will authenticate the user. The optional argument connects to the database, unless her connection string indicates another database. This statement introduces the semicolon as an optional termination for a TransactSQL statement..\Mary] FROM WINDOWS WITH DEFAULT_DATABASE = [TestData]; GO This authorizes a user name , authenticated by your computer, to access this instance of SQL Server. If there is more than one instance of SQL Serveron the computer, you must create the login on each instance that
Mary
must access.
To create a new Windows account

To create a login
15

Creating Views and Stored ProceduresNow that Mary can access the TestDatadatabase, you may want to create some database objects,such as a view and a stored procedure, and then grant Mary access to them. A view is a stored SELECT statement, and a stored procedure is one or more TransactSQL statements that execute as a batch. Views are queried like tables and do not accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database.For this example, you will use CREATE VIEW to create a view that selects only two of the columns in the Productstable. Then, you will use CREATE PROCEDURE to create a stored procedure that accepts a price parameter and returns only those products that cost less than the specified parameter value.Procedures
Execute the following statement to create a very simple view that executes a select statement, and returns the names and prices of our products to the user.

Views are treated just like tables. Use a statement to access a view.

The following statement creates a stored procedure name , accepts an input parameter named of data type . The stored procedure prints the statement concatenated with the input parameter that is changed from the data type into a varchar(10)character data type. Then, the procedure executes a statement on the view, passing the input parameter as
part of the
WHERE

clause. This returns all products that cost less than the input
To create a view

Test the view

To create a stored procedure
16

parameter value.

1.
To test the stored procedure, type and execute the following statement. The procedure
should return the names of the two products entered into the table in Lesson 1 with a price that is less than

Next Task in Lesson
Granting Access to a Database Object
See Also
CREATE VIEW (TransactSQL)

CREATE PROCEDURE (TransactSQL)
Granting Access to a Database ObjectAs an administrator, you can execute the SELECT from the Productstable and the vw_Namesview, and execute the pr_Namesprocedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.Procedures
Test the stored procedure

Procedure Title
19

Use the statement to remove the view vw_NamesUse the statement to remove all rows from the table:Use the statement to remove the table:You cannot remove the database while you are in the database; therefore, first switch context to another database, and then use the statement to remove the database:

This concludes the Writing TransactSQL Statements tutorial. Remember, this tutorial is a brief overview and it does not describe all the options to the statements that are used. Designing and creating an efficient database structure and configuring secure access to the data requires a more complex database than that shownin this tutorial.Return to SQL Server Tools Portal
Tutorial: Writing TransactSQL Statements
See Al
REVOKE (TransactSQL)

DROP USER (TransactSQL)

DROP LOGIN (TransactSQL)

DROP PROCEDURE (TransactSQL)

Documentos PDF asociados:

SQL Server 2012 Tutorials - download.microsoft.com
Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012
Microsoft SQL Server 2000 - A Fondo Con CD ROM
Windows Server 2012 - tutorialspoint.com
Guía definitiva de Windows Server 2016 - info.microsoft.com
Guía instalación SQL Server 2012 y 2014 EXPRESS.
Microsoft Visual Studio 2012 Manual PDF - lorraines.co.za
Migrar a Excel - download.microsoft.com
Guía de inicio rápido - download.microsoft.com
Migrar a Access 2010 - download.microsoft.com
Libros para Niños Catálogo 2012 Jóvenes 2012
Help and tutorials - Adobe
VB - Tutorials Point
Arduino - Tutorials - 4tronix
Lynda Photoshop Cs5 Tutorials - WordPress.com
Thematic Apperception Test (TAT) - Tutorials Point
Drawing Tutorials Pdf Ebooks - isabenur.files.wordpress.com
HPE ProLiant DL380 Gen10 Server
CAIN Y ABEL - server.firefighters.org
BASE DE DATOS <<***BIBLIOTECA***>> EN SQL SERVER 2008
Neuromuscular Blocking Agents - UW Faculty Web Server
Oracle WebLogic Server: Una Base Sólida para la ...
Nunca seré tu héroe - ecat.server.grupo-sm.com
Como Instalar Sql Server 2008 R2 Management Studio
training guide installing and configuring windows server 201
Microsoft Access 2010 - ub.edu
Using Microsoft Excel - 2013.net
Introduction to Microsoft Publisher
Introduction to Microsoft Access 2016
Guía de Microsoft Excel 2016 - AST
Microsoft Word 2013 - w3.kcls.org
Microsoft Outlook - Memo Style
MANUAL DE MICROSOFT EXCEL - ispa.pt
Manual de instrucción de Microsoft Excel 2010
Come Installare Microsoft Office 2010 Crack 2
Microsoft Access 2013 A Beginners' Guide
Curso online de Microsoft® Access 2016
CURSO EXPERTO EN MICROSOFT POWERPOINT 2013
MANUAL DE MICROSOFT POWERPOINT - previa.uclm.es
Nuevo Documento de Microsoft Word - libroesoterico.com