Introduction to Transact-SQL
Types of Transact-SQL Statements
• Data Definition Language Statements
• Data Control Language Statements
• Data Manipulation Language Statements
1. Data Definition Language (DDL) statements, which allow you to create objects in the database.
• CREATE
• ALTER
• DROP
EXAMPLE
USE northwind
CREATE TABLE customer
(cust_id int, company varchar(40),
contact varchar(30), phone char(12) )
GO
2. Data Manipulation Language (DML) statements, which allow you to query and modify the data.
• SELECT
• INSERT
• UPDATE
• DELETE
EXAMPLE
USE northwind
SELECT categoryid, productname, productid, unitprice
FROM products
GO
3. Data Control Language (DCL) statements, which allow you to determine who can see or modify the data.
• GRANT
• DENY
• REVOKE
EXAMPLE
USE northwind
GRANT SELECT ON products TO public
GO
Enterprise Manager
SQL Server Enterprise Manager is the primary administrative tool for Microsoft® SQL Server™ 2000 and provides a Microsoft Management Console (MMC)–compliant user interface that allows users to:
• Define groups of servers running SQL Server.
• Register individual servers in a group.
• Configure all SQL Server options for each registered server.
• Create and administer all SQL Server databases, objects, logins, users, and permissions in each registered server.
• Define and execute all SQL Server administrative tasks on each registered server.
• Design and test SQL statements, batches, and scripts interactively by invoking SQL Query Analyzer.
• To start the Enterprise Manager, click your mouse on the Start button, move your mouse pointer to Programs on the Start menu, select Microsoft SQL Server 7.0, and click your mouse on Enterprise Manager.
lIKE
To display the list of SQL servers, click your mouse on the plus (+) to the left of SQL Server Group
HOW TO Create a database
Press Right Click to database in enterprise manager – then chose new database like
Then
Write database name in text (Name) and then press OK
Now you can see the database file in this page
Open the database file and you can see that item in this page
Understanding the Components of a Table
An SQL table consists of scalar (single-value) data arranged in columns and rows. Relational database tables have the following components:
• A unique table name
• Unique names for each of the columns in the table
• At least one column
• Data types, domains, and constraints that specify the type of data and its range of values for each column in the table
• A structure in which data in one column of the table has the same meaning in every row of the table
• Zero or more rows that represent physical or logical entities
Create table
Press Right Click -- table in database items – then chose new table
like
And then
Write the field name and data type
And then press save button in bar and write database name
Types of Data
Numbers
This type of data represents numeric values and includes integers such as int,
tinyint, smallint, and bigint. It also includes precise decimal values such as
numeric, decimal, money, and smallmoney. It includes floating point values
such as float and real.
Dates
This type of data represents dates or spans of time. The two date data types are
datetime, which has a precision of 3.33 milliseconds, and smalldatetime,
which has a precision of 1-minute intervals.
Characters
This type of data is used to represent character data or strings and includes
fixed-width character string data types such as char and nchar, as well as
variable-length string data types such as varchar and nvarchar
Binary
This type of data is very similar to character data types in terms of storage and
structure, except that the contents of the data are treated as a series of byte
values. Binary data types include binary and varbinary. A data type of bit
indicates a single bit value of zero or one. A rowversion data type indicates a
special 8-byte binary value that is unique within a database.
Unique Identifiers
This special type of data is a uniqueidentifier that represents a globally unique
identifier (GUID), which is a 16-byte hexadecimal value that should always
be unique.
SQL Variants
This type of data can represent values of various SQL Server supported data
types, with the exception of text, ntext, image, timestamp and rowversion.
Image and Text
These types of data are binary large object (BLOB) structures that represent
fixed- and variable-length data types for storing large non-Unicode and
Unicode character and binary data, such as image, text, and ntext.
Tables
The table data type can be used only to define local variables of type table or
the return value of a user-defined function.
User-defined Data Types
This data type is created by the database administrator and is based on system
data types. Use user-defined data types when several tables must store the same
type of data in a column and you must ensure that the columns have exactly the
same data type, length, and nullability.
Integers
bigint
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
int
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
smallint
Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
tinyint
Integer data from 0 through 255.
bit
bit
Integer data with either a 1 or 0 value.
decimal and numeric
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
Approximate Numerics
float
Floating precision number data from -1.79E + 308 through 1.79E + 308.
real
Floating precision number data from -3.40E + 38 through 3.40E + 38.
datetime and smalldatetime
datetime
Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
Character Strings
char
Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar
Variable-length non-Unicode data with a maximum of 8,000 characters.
text
Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
Unicode Character Strings
nchar
Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar
Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.
ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
Binary Strings
binary
Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary
Variable-length binary data with a maximum length of 8,000 bytes.
image
Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
Other Data Types
cursor
A reference to a cursor.
sql_variant
A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.
table
A special data type used to store a result set for later processing .
timestamp
A database-wide unique number that gets updated every time a row gets updated.
uniqueidentifier
A globally unique identifier (GUID).
Query analyzer
To start the Query analyzer, click your mouse on the Start button, move your mouse pointer to Programs on the Start menu, select Microsoft SQL Server 7.0, and click your mouse on Query analyzer.
Now you can see that page.
In th SQl Server Field write the Server name (Local) or (.)
And prees Ok Button
now Query Analyzer is open
solimanwf@yahoo.com
solimanwf@hotmail.com