Creating a Database Microsoft SQL Server Create Database

Creating a Database Microsoft SQL Server

Create Database SQL Management Studio 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 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. 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/ms 186312. aspx

Create Database T-SQL USE master ; GO CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = 'C: Program FilesMicrosoft SQL ServerMSSQL 11. MSSQLSERVERMSSQLDATAsaledat. mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log, FILENAME = 'C: Program FilesMicrosoft SQL ServerMSSQL 11. MSSQLSERVERMSSQLDATAsalelog. ldf', SIZE = 5 MB, MAXSIZE = 25 MB, FILEGROWTH = 5 MB ) ; GO Ref: http: //msdn. microsoft. com/en-us/library/ms 186312. 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 (First. Name VARCHAR (20), Last. Name VARCHAR (20) NOT NULL, Date. Of. Birth DATETIME, CONSTRAINT Multi. Constraint UNIQUE(First. Name, Last. Name, Date. Of. Birth)); Ref: http: //devguru. com/technologies/t-sql/7121. asp

Create Tables Exercise 1. Create a database named demo 1 2. Use the SQL statements on the following slides to create and populate the Books, Authors, and Author. Book tables. 3. Provide any corrections required for T-SQL syntax.

Create Books Table CREATE TABLE Books ( Book. ID SMALLINT NOT NULL PRIMARY KEY, Book. Title 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 ( Auth. ID SMALLINT NOT NULL PRIMARY KEY, Auth. FN VARCHAR(20), Auth. MN VARCHAR(20), Auth. LN 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 Author. Book Table CREATE TABLE Author. Book ( Auth. ID SMALLINT NOT NULL, Book. ID SMALLINT NOT NULL, PRIMARY KEY (Auth. ID, Book. ID), FOREIGN KEY (Auth. ID) REFERENCES Authors (Auth. ID), FOREIGN KEY (Book. ID) REFERENCES Books (Book. ID) );

Insert Data into Author. Book INSERT INTO Author. Book VALUES (1006, 14356), (1008, 15729), (1009, 12786), (1010, 17695), (1011, 15729), (1012, 19264), (1012, 19354), (1014, 16284);

Basic Join SELECT Book. Title, Copyright, Authors. Auth. ID FROM Books, Author. Book, Authors WHERE Books. Book. ID=Author. Book. ID AND Author. Book. Auth. ID=Authors. Auth. ID ORDER BY Books. Book. Title;

Basic Join SELECT Book. Title, Copyright, Authors. Auth. ID FROM Books, Author. Book, Authors ORDER BY Book. Title; What happens when we leave off the WHERE clause?

Basic Join SELECT Book. Title, Copyright, Auth. ID FROM Books AS b, Author. Book AS ab WHERE b. Book. ID=ab. Book. ID ORDER BY Book. Title;