MS SQL Server Concepts and Programming Interview Preparation Guide

Download PDF

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It runs on Windows systems and uses Transact-SQL as the query language.

Microsoft SQL Server release history:

* 1993 - SQL Server 4.21 for Windows NT
* 1995 - SQL Server 6.0, codenamed SQL95
* 1996 - SQL Server 6.5, codenamed Hydra
* 1999 - SQL Server 7.0, codenamed Sphinx
* 1999 - SQL Server 7.0 OLAP, codenamed Plato
* 2000 - SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
* 2003 - SQL Server 2000 64-bit, codenamed Liberty
* 2005 - SQL Server 2005, codenamed Yukon (version 9.0)
* 2005 - SQL Server 2005 Express Edition, restricted free version

Microsoft SQL Server 2005 Express Edition is the free version of the Microsoft SQL Server 2005. If you are interested to try SQL Server 2005, you should follow this tutorial to download Microsoft SQL Server 2005 Express Edition:

1. Go to the Microsoft SQL Server 2005 Express Edition download page.

2. Go to the Files in This Download section, and click Download button next to the "SQLEXPR.EXE - 53.5 MB" file. The File Download box shows up.

3. Save the download file to C: emp directory. When the download is done, you should get the following file:

Name: SQLEXPR.EXE
Description: Microsoft SQL 2005 Server Express Edition
Location: C: emp
Size: 56,105,688 bytes
Version: 9.0.1399.6

The following system requirements cover the SQL Server 2005 Express Edition:

Processor
32-bit Processor of 600-megahertz (MHz) or faster

Operating System
Windows XP with Service Pack 2 or later
Windows 2000 Professional with SP4
Windows 2000 Server with Service Pack 4 or later
Windows Server 2003 Standard, or Enterprise SP1
Windows Server 2003 Web Edition SP1
Windows Small Business Server 2003 with SP1
Vista Home Basic and above

Framework
.NET Framework 2.0

Memory
512 megabytes (MB) or more recommended

Hard Disk
Approximately 425 MB of available hard-disk space

When you try to install SQL Server 2005 Express Edition, you may get a Microsoft SQL Server 2005 Setup error box with this message: "The Microsoft .Net Framework 2.0 in not installed. Please install before running setup."

You are getting this error, because .NET Framework 2.0 is not installed on your system yet. Read the next tutorial to download and install .NET Framework 2.0.

.NET Framework Version 2.0 is required by many Microsoft applications like SQL Server 2005. If you want download and install .NET Framework Version 2.0, you should follow this tutorial:

1. Go to the Microsoft .NET Framework Version 2.0 Redistributable Package (x86) page.

2. Click the Download button. Save the download file dotnetfx.exe to c: emp.

3. Look at and compare the downloaded file properties with:

Name: dotnetfx.exe
Location: C: emp
Size: 23,510,720 bytes
Version: 2.0.50727.42

4. Close all IE (Internet Explorer) windows.

5. Double click the downloaded file: to c: empdotnetfx.exe. The Microsoft .NET Framework 2.0 Setup windows shows up. Follow the instructions to finish the installation.

Process mscorsvw.exe is installed as a system service as part of the .NET Framework 2.0. You can disable it, if you are not using any applications that require .NET Framework 2.0.

mscorsvw.exe process and program file info:

CPU usage: 00%
Memory usage: 2,704K
Launching method: System Service
Directory: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727
File name: mscorsvw.exe
Description: .NET Runtime Optimization Service
Size: 66,240 bytes
Date: Friday, September 23, 2005, 7:28:56 AM
Version: 2.0.50727.42
Company name: Microsoft
System essential: No
Virus/Spyware/Adware: No

Once you have downloaded SQL Server 2005 Express Edition, you should follow this tutorial to install it on your system:

1. Double click SQLEXPR.EXE. The setup window shows up.

2. Click Next to let the setup program to unpack all files from the downloaded file.

3. When unpack is down, the setup program will check all required programs on your system.

4. Then the setup program will start the installation process.

5. On the Authentication Mode window, click the radio button for Mixed Mode (Windows Authentication and SQL Server Authentication). And enter "GlbalGuideLine" in the "Specify the sa logon password below:" fields.

6. Continue to finish the installation process.

7. When installation is done, you will see a new program menu entry as: Start > Programs > Microsoft SQL Server 2005 > Configuration Tools.

Process sqlservr.exe is the Microsoft SQL Server system service installed as part of the Microsoft SQL Server 2005 Express Edition.

mscorsvw.exe process and program file info:

CPU usage: 00%
Memory usage: 1,316K
Launching method: System Service
Directory: C:Program FilesMicrosoft SQL ServerMSSQL.1
MSSQLBinn
File name: sqlservr.exe
Description: SQL Server Windows NT
Size: 28,768,528 bytes
Date: Friday, October 14, 2005, 3:51:46 AM
Version: 2005.90.1399.0
Company name: Microsoft
System essential: No
Virus/Spyware/Adware: No

After installing SQL Server 2006 Express Edition, it will be running on your local system quietly as a background process.

If you want to see this process is running, run Windows Task Manager. You should see a process called sqlservr.exe running in the process list:

sqlservr.exe 00 1,316 K

If you select sqlservr.exe and click the "End Process" button, SQL Server will be stopped.

If you can not find sqlservr.exe in the process list, you know that your SQL Server is running.

Once you have SQL Server 2005 Express installed and running on your local machine, you are ready to connect SQL Server Management Studio Express to the server:

Click Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express to launch SQL Server Management Studio Express.

The "Connect to Server" box shows up. The Server Name field has a default value of "LOCALHOSTSQLEXPRESS". So don't change it. Select "SQL Server Authentication" as the Authentication. Enter enter "sa" as the Login, and "GlbalGuideLine" as the Password.
SQL Server 2005 Connect Window.

Click the Connect button, you should see the SQL Server Management Studio Express window comes up.

Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services. If you want to download and install it to your system, follow this tutorial:

1. Go to SQL Server Management Studio Express home page.

2. Go to the "Files in This Download" section.

3. Click the Download button next to "SQLServer2005_SSMSEE.msi - 38.5 MB" And save it to c: emp directory.

4. Look at and compare the downloaded file properties with:

Name: SQLServer2005_SSMSEE.msi
Location: C: emp
Size: 40,364,032 bytes

5. Double click to install. The setup window shows up. Follow the instructions to finish the installation process.

6. When installation is done, you will see a new program menu entry as: Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express

1. Go to the SQL Server 2005 Books Online download page.

2. Click the download button, the File Download box shows up. Save the download file to c: emp.

3. Double click on the downloaded file: c: empSqlServer2K5_BOL_Feb2007.msi. The installation setup window shows up. Follow the instructions to finish the installation.

4. When the installation is done. You will see a new entry in the Start menu: Start > Programs > Microsoft SQL Server 2005 > Documentation and Tutorials

1. Launch and connect SQL Server Management Studio Express to the local SQL Server 2005 Express.

2. Click on the "New Query" button below the menu line. Enter the following SQL statement in the query window:

SELECT 'Welcome to GlobalGuideLine.com Tips on SQL Server!'

3. Click the Execute button in the toolbar area. You should get the following in the result window:

Welcome to GlobalGuideLine.com Tips on SQL Server!

SQL Server 2005 Books Online can be accessed by a Web browser over the Internet. But you can also download it and read it on your local system. If you have downloaded and installed SQL Server 2005 Books Online package, you follow this tutorial to run it:

1. Click Start > Programs > Microsoft SQL Server 2005 > Documentation and Tutorials > Tutorials > SQL Server Tutorials. The SQL Server 2005 Books Online window shows up.

2. Click the plus sign (+) next to "SQL Server 2005 Tutorials in the Contents window".

3. Click the plus sign (+) next to "SQL Server Tools Tutorials".

4. Click "Lesson 1: Basic Navigation in SQL Server Management Studio". The book content shows up for you to read.

Transact-SQL statements can be used to access the database engine directly. Here are some good tutorials provided by the SQL Server 2005 Books Online. See the SQL Server 2005 Tutorials > Database Engine Tutorials > Writing Transact-SQL Statements Tutorial section in the SQL Server 2005 Books Online document.

This SQL Questions Guide 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 SQL standard. This tutorial is intended as a brief introduction to the Transact-SQL language and not as a replacement for a Transact-SQL class. The statements in this tutorial are intentionally simple, and are not meant to represent the complexity found in a typical production database.

This is the first SQL Questions Guide of a quick lesson on creating database objects with Transact-SQL statements. This section 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 is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This SQL Questions Guide assumes that you are running SQL Server Management Studio Express.

Like many Transact-SQL statements, the CREATE DATABASE statement has a required parameter: the name of the database. CREATE DATABASE also has many optional parameters, such as the disk location where you want to put the database files. When you execute CREATE DATABASE without the optional parameters, SQL Server uses default values for many of these parameters. This tutorial uses very few of the optional syntax parameters.

To create a database - In a Query Editor window, type but do not execute the following code:

CREATE DATABASE YourDataBaseName
GO

This is the second tutorial of a quick lesson on creating database objects with Transact-SQL statements. This section 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 section is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This SQL Guide assumes that you are running SQL Server Management Studio Express.

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.

Now you how to create a database, create a table in the database, and then access and change the data in the table. Because here is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. Now we assumes that you are running SQL Server Management Studio Express.

Now that you have created the Products table, you are ready to insert data into the table by using the INSERT statement. After the data is inserted, you will change the content of a row by using an UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict the update to a single row. The four statements will enter the following data.

ProductID ProductName Price ProductDescription
1 Clamp 12.48 Workbench clamp
50 Screwdriver 3.17 Flat head
75 Tire Bar Tool for changing tires
3000 3mm Bracket .52

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.

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

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.

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.

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.

Now that Mary can access the YourDataBaseName database, 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 Transact-SQL 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.

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 answer 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.

As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.

Procedure Title - Execute the following statement to give Mary the EXECUTE permission for the pr_Names stored procedure.


GRANT EXECUTE ON pr_Names TO Mary;
GO

To remove all database objects created by previous tutorials, you could just delete the database. However, in this tutorial, you will go through the steps to reverse every action you took doing the tutorial.

Removing permissions and objects - Before you delete objects, make sure you are in the correct database:

USE YourDataBaseName;
GO

Use the REVOKE statement to remove execute permission for Mary on the stored procedure:

REVOKE EXECUTE ON pr_Names FROM Mary;
GO

Use the DROP statement to remove permission for Mary to access the YourDataBaseName database:

DROP USER Mary;
GO

Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005:

DROP LOGIN [Mary];
GO

Use the DROP statement to remove the store procedure pr_Names:

DROP PROC pr_Names;
GO

Use the DROP statement to remove the view vw_Names:

DROP View vw_Names;
GO

Use the DELETE statement to remove all rows from the Products table:

DELETE FROM Products;
GO

Use the DROP statement to remove the Products table:

DROP Table Products;
GO

You cannot remove the YourDataBaseName database while you are in the database; therefore, first switch context to another database, and then use the DROP sta

A database is a logical container that contains a set of related database objects:

* Tables - Storages of structured data.
* Views - Queries to present data from tables.
* Indexes - Sorting indexes to speed up searches.
* Stored Procedures - Predefined SQL program units.
* Users - Identifications used for data access control.
* Other objects.