Breadcrumbs

Getting Started with Microsoft Sql Server

[ return to previous page]


Logging in to MS SQL Server for the first time. To log into the MS SQL Server Database you need to have account at SQL Server. If you do not have an account, please mail request at root@uisacad.uis.edu.

If you have already an account, click the Start::Program:: Microsoft SQL Server::SQL Query Analyzer. This will bring you to this screen:

Connect to SQL Server

Supply the necessary information: Server Name, Login Name, and Password. You may click the 3 dots (…) after the SQL Server scroll bar widow for selection of an active SQL Servers.


Changing your password can be made at the http://uis1.uis.edu/scripts/passwd.pl. this would however, change your UIS netid password. The SQL server uses the Windows NT authentication.


Connecting to a Database, selecting a table and displaying contents of the table,

SQL Query Analyzer

To select a database, point and click the database desired as displayed at the upper menu.

By pressing F8 the object browser (the left pane portion as shown in the figure above) will be displayed. Pressing F8 again will not display the object browser.

The list of tablename and its properties can be determined by pointing to the User Table folder at the object browser pane. Click the plus sign adjunct to the appropriate folder and this will display the list of tables. Click the plus sign again to display the properties of the table.

For the SQL command, use the center pane and enter the command:

select * from <tablename>

Then, point mouse at Query::Execute at the top menu bar or press F5 to run the SQl engine. The SQL engine will then execute the sql command. The result will be displayed at the lower pane called result the results pane. The Result Pane can be displayed by pressing ctrl-R at the same time.


Creating a Table In MS SQL can be executed using the SQL command. One simple type of command creates a table (relation). The form is

CREATE TABLE student (name VARCHAR(25), netid VARCHAR(7), birth DATE)

The execute the create command, point and click the mouse at Query::Execute on the top menu bar or press F5.

It creates a table named student with three attributes. The first, named name, is an alphanumberic of 25 characters; the second, named netid, is also an alphanumeric character string of length 7; the third, named birth, is a date field type.

The simplified syntax is:

CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }
| [ { PRIMARY KEY | UNIQUE } [ ,n ]
)

where:
database_name – is the name of an existing database in which the table is created. The login for the current connection must be associated with an existing user ID in the database specified by database_name, and that user ID must have create table permissions.

owner – is the name of the user ID that owns the new table. owner must be an existing user ID in the database specified by database_name. owner defaults to the user ID associated with the login for the current connection in the database specified in database_name.

table_name – is the name of the new table to be created. Table names must conform to the rules for identifiers. The combination of owner.table_name must be unique within the database. table_name can contain a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

column_name – is the name of a column in the table. Column names must conform to the rules for identifiers and must be unique in the table.

computed_column_expression – is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.


Creating a Table With a Primary Key To create a table that declares attribute a to be a primary key:

CREATE TABLE <TableName> (..., a <type> PRIMARY KEY, b, ...);

To create a table that declares the set of attributes (a,b,c) to be a primary key:
     CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY (a,b,c));


Inserting tuples. Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT command:

INSERT [INTO] table_or_view [(column_list)] data_values

For instance, we can insert the tuple (‘John Doe’,’uistud1s ‘1985-01-01′) into relation to the student table by:

INSERT INTO student (
		'John Doe','uistud1s '1985-01-01'
		);
	

The statement causes the data_values to be inserted as one or more rows into the named table or view. column_list is a list of column names, separated by commas, that can be used to specify the columns for which data is supplied. If column_list is not specified, all the columns in the table or view receive data.

When a column_list does not name all the columns in a table or view, a value of NULL (or the default value if a default is defined for the column) is inserted into any column not named in the list. All columns not specified in the column list must either allow null values or have a default assigned.


Getting the Value of a Relation . We can see the contents of a table with the command:

SELECT * FROM <tableName>

It retrieves the rows from the table and allows the selection of one or many rows or columns from one or many tables. The complete syntax for the select statement is:

SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

For example, enter the following commands and then click execute or press F5
USE Northwind
GO
SELECT *
FROM Shippers
ORDER BY CompanyName
GO

The output will be displayed at the result pane normally the lower part pane.
3 Federal Shipping (503) 555-9931
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199


To delete the contents of your table, type and then execute:DELETE table_or_view FROM table_sources WHERE search_condition

where:
table_or_view
names a table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted.

If a WHERE clause is not specified, all the rows in table_or_view are deleted. The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from table_or_view. Rows are not deleted from the tables named in the FROM clause, only from the table named in table_or_view.

Any table that has all rows removed remains in the database. The DELETE statement deletes only rows from the table; the table must be removed from the database by using the DROP TABLE statement.

Example to delete rows using DELETE

This script shows the three DELETE statements needed to delete the rows associated with products supplied by the company named Lyngbysild in the Northwind database. This would not be a typical business operation because it involves deleting lines from existing orders, but it does show a series of deletes of differing complexity.

USE Northwind
GO
DELETE [Order Details]
FROM Suppliers, Products
WHERE Products.SupplierID = Suppliers.SupplierID
  AND Suppliers.CompanyName = 'Lyngbysild'
  AND [Order Details].ProductID = Products.ProductID
GO
DELETE Products
FROM Suppliers
WHERE Products.SupplierID = Suppliers.SupplierID
  AND Suppliers.CompanyName = 'Lyngbysild'
GO
DELETE Suppliers
WHERE CompanyName = 'Lyngbysild'
GO

At times you need to delete a table (for example, when you want to implement a new design or free up space in the database). When you delete a table, its structural definition, data, full-text indexes, constraints, and indexes are permanently deleted from the database, and the space formerly used to store the table and its indexes is made available for other tables. You can explicitly drop a temporary table if you do not want to wait until it is dropped automatically.

If you need to delete tables that are related through FOREIGN KEY and UNIQUE or PRIMARY KEY constraints, you must delete the tables with the FOREIGN KEY constraints first. If you need to delete a table that is referenced in a FOREIGN KEY constraint but you cannot delete the entire foreign key table, you must delete the FOREIGN KEY constraint.


Getting Information About Your Database The system keeps information about your own database in certain system tables. On the right side panel, point mouse to the desired table and right click the mouse as shown in the figure below:

SQL Query Analyzer


Quitting sql server. To leave MS SQL Server, from the top menu bar: point mouse at:

File::Exit

This will exit the MS SQL Server.


Some of the information above is digested from the help menu of Microsoft SQL Server. For more information on Microsoft SQL Server, the help menu provides a comprehensive guide for programming and debugging.


[ return to top]