Lecture Notes
Monday, November 22, 2010
Tuesday, March 23, 2010
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.
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)
Following statement will add a new column to the above table
ALTER TABLE ItemDetails
ADD Title VARCHAR(20)
- Following statement will delete a column
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
- Retrive data from a single table
Example:
SELECT ItemNo,Title FROM ItemDetails
- Retrieving data from two or more tables
Subscribe to:
Posts (Atom)