Transcript Creating a Data

Creating a Database
Microsoft SQL Server
Create Database
SQL Management Studio
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Right-click Databases, and then click New Database.
In New Database, enter a database name.
To create the database by accepting all default values, click OK; otherwise, continue with the following
optional steps.
To change the owner name, click (…) to select another owner.
To change the default values of the primary data and transaction log files, in the Database files grid, click the
appropriate cell and enter the new value. For more information, see Add Data or Log Files to a Database.
To change the collation of the database, select the Options page, and then select a collation from the list.
To change the recovery model, select the Options page and select a recovery model from the list.
To change database options, select the Options page, and then modify the database options. For a
description of each option, see Setting Database Options.
To add a new filegroup, click the Filegroups page. Click Add and then enter the values for the filegroup.
To add an extended property to the database, select the Extended Properties page.
1.
2.
12.
In the Name column, enter a name for the extended property.
In the Value column, enter the extended property text. For example, enter one or more statements that describe the
database.
To create the database, click OK.
Ref: http://msdn.microsoft.com/en-us/library/ms186312.aspx
Create Database T-SQL
USE master ;
GO CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON ( NAME = Sales_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
Ref: http://msdn.microsoft.com/en-us/library/ms186312.aspx
Create Table
SQL Management Studio
1.
2.
3.
4.
5.
Right-click the database in which you want the table to appear. A menu is
shown. Select "New Table" from the list of menu items.
Type column names on the left side of the table viewer. The table view is
the place where you add your column names and data types.
Select a data type for each column. The data type is a drop-down menu
item
Right-click one of the columns that is unique to the table and select
"Primary Key."
Click the "Save" button on the SQL Server Management Studio toolbar. A
window pops up asking for a table name. Enter it into the text box and
press the "Ok" button. The table is created and saved in your database.
Ref: http://www.ehow.com/how_5877415_create-tables-sql-management-studio.html
Create Table T-SQL
CREATE TABLE Names
(FirstName VARCHAR (20),
LastName VARCHAR (20) NOT NULL,
DateOfBirth DATETIME,
CONSTRAINT MultiConstraint UNIQUE(FirstName, LastName, DateOfBirth));
Ref: http://devguru.com/technologies/t-sql/7121.asp
Create Tables Exercise
1. Create a database named demo1
2. Use the SQL statements on the following
slides to create and populate the Books,
Authors, and AuthorBook tables.
3. Provide any corrections required for T-SQL
syntax.
Create Books Table
CREATE TABLE Books
(
BookID SMALLINT NOT NULL PRIMARY KEY,
BookTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
);
Insert data into Books
INSERT INTO Books
VALUES (12786, 'Letters to a Young Poet', 1934),
(13331, 'Winesburg, Ohio', 1919),
(14356, 'Hell\'s Angels', 1966),
(15729, 'Black Elk Speaks', 1932),
(16284, 'Noncomformity', 1996),
(17695, 'A Confederacy of Dunces', 1980),
(19264, 'Postcards', 1992),
(19354, 'The Shipping News', 1993);
Create Authors Table
CREATE TABLE Authors
(
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthFN VARCHAR(20),
AuthMN VARCHAR(20),
AuthLN VARCHAR(20)
);
Insert data into Authors
INSERT INTO Authors
VALUES (1006, 'Hunter', 'S.', 'Thompson'),
(1007, 'Joyce', 'Carol', 'Oates'),
(1008, 'Black', NULL, 'Elk'),
(1009, 'Rainer', 'Maria', 'Rilke'),
(1010, 'John', 'Kennedy', 'Toole'),
(1011, 'John', 'G.', 'Neihardt'),
(1012, 'Annie', NULL, 'Proulx'),
(1013, 'Alan', NULL, 'Watts'),
(1014, 'Nelson', NULL, 'Algren');
Create AuthorBook Table
CREATE TABLE AuthorBook
(
AuthID SMALLINT NOT NULL,
BookID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, BookID),
FOREIGN KEY (AuthID) REFERENCES Authors
(AuthID),
FOREIGN KEY (BookID) REFERENCES Books (BookID)
);
Insert Data into AuthorBook
INSERT INTO AuthorBook
VALUES (1006, 14356), (1008, 15729),
(1009, 12786), (1010, 17695),
(1011, 15729), (1012, 19264),
(1012, 19354), (1014, 16284);
Basic Join
SELECT BookTitle, Copyright, Authors.AuthID
FROM Books, AuthorBook, Authors
WHERE
Books.BookID=AuthorBook.BookID
AND
AuthorBook.AuthID=Authors.AuthID
ORDER BY Books.BookTitle;
Basic Join
SELECT BookTitle, Copyright, Authors.AuthID
FROM Books, AuthorBook, Authors
ORDER BY BookTitle;
What happens when we leave off the WHERE
clause?
Basic Join
SELECT BookTitle, Copyright, AuthID
FROM Books AS b, AuthorBook AS ab
WHERE b.BookID=ab.BookID
ORDER BY BookTitle;