SQL EXAM Question

SQL Server - Reference

Introduction

The Microsoft SQL Server 2008 is a complete database management system. You can download the SQL Server 2008 express edition free of charge from http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx .However SQL Server express is strictly a database server, or database engine. ie. it doesn't provide some of the client tools.

How to start database server

The database server will start automatically each time you start your computer by default.

To stop the server run services and click stop on the right pane( To run services click start --> Run --> and type Services.msc)

How to enable remote connections

Remote connections are disabled by default. To enable remote connections use the Surface Area Configuration tool.

To display Surface Area Configuration select Start--> All Programs --> Microsoft SQL Server 2008 --> Configuration Tools --> SQL Server Surface Area Configuration.

To enable remote connections select Surface Area Configuration for services and connections --> Database Engine --> Local and Remote Connections.

How to open and save queries

To open a new query window, click the New Query that appears on top left hand side of the window. To save the query press ctrl+s.

How to create a Database

To create a database use the CREATE DATABASE statement as below.
Syntax:
CREATE DATABASE {database name}
Example:
CREATE DATABASE Videodb

Once database is created, you can use the the database to create various types of objects like tables by executing the following command.

USE {database name}
example:
USE Videodb

How to create a Table


Syntax:
Example:
CREATE TABLE ItemDetails(ItemNo INT NOT NULL IDENTITY PRIMARY KEY,
Type VARCHAR(10) ,
Artist VARCHAR(20),
DatePurchased DATETIME,
Cost MONEY)

Example explained

• INT Represents integer data type
• NOT NULL This column cannot have any null values
• IDENTITY values will be auto generated. IDENTITY(1,10) will start numbering from 1 and increment the number by 10.
• PRIMARY KEY A unique identifier
• DATETIME is a datatype used to insert a date value. (SMALLDATETIME is also can be used)
• MONEY is a datatype used to insert a currency (SMALLMONEY is also can be used)

How to Alter a Table
Following statement will add a new column to the above table
ALTER TABLE ItemDetails
ADD Title VARCHAR(20)

• Following statement will delete a column

ALTER TABLE ItemDetails


DROP COLUMN Cost

How to Insert data in to Table

Syntax:
INSERT INTO {table name [column name 1 ,column name 2,...]} VALUES({value 1, value2,...})

Example:
INSERT INTO ItemDetails VALUES('DVD','Clarens','10/10/2010',500)

Example Explained

• A value for the InvoiceID is not inserted as it is an IDENTITY
• Date value should be single quated and the format is DD/MM/YYYY

Retrieve data from tables in various ways

• Retrive data from a single table

Use SELECT Statement to retrieve selected columns from a base table. The result of the execution is a result set. The result can include calculated values.
Example:
SELECT ItemNo,Title FROM ItemDetails

• Retrieving data from two or more tables

A join lets you combine data from two or more tables into a single result set.