Transcript SQL CREATE TABLE Statement

Agenda for Class 9/26/2013
• Introduce Microsoft’s SQL Server database
management system.
• Use the lab to discuss how to CREATE, DROP
and populate (INSERT) tables.
• Introduce SELECT statement.
• We will review the basic concepts together and
create 1 table as a class. Then you will finish the
rest of the lab at your own speed during class.
Accessing SQL Server
• Login to COB server (use your netID).
• Accessing from COB labs:
– Execute Microsoft SQL Server Management
Studio.
• Accessing from home:
– Execute Remote Desktop Connection.
– Login to server called sts.coba.unr.edu
• Use your netID as the User name and password.
• When not in the COB labs, your netID for Windows
authentication is UNR\netID.
– Execute Microsoft SQL Server Management
Studio.
Logging into the class server for SQL Server
• After executing SQL Server Management Studio,
either directly when in the COB labs, or through
remote desktop when not in the COB labs, you
must login to our class server.
– Server name is ISSQL\Students
• Use Windows authentication for all work on SQL
Server.
SQL Server Management Studio
•
•
•
•
•
•
•
Object Explorer
New Query editor
Options
File Tabs
Difference between database object and SQL code
Accessing files from the k: or u: drives
Saving SQL code to/from a separate file
Guidelines for writing SQL
• SQL statements start with a command, and then
include few or many modifiers/extensions for the
command.
• SQL statements are not case sensitive.
• The data stored in a data base ARE case sensitive.
• SQL statements can span more than one physical
line; it is a free form language.
• SQL keywords cannot be abbreviated or split
across lines.
5
Much tradition in SQL code
• Keywords and/or main clauses are typically placed
on separate lines.
• Tabs and indentation are used to enhance
readability.
• Keywords are typically aligned in the first column.
• Keywords are usually capitalized.
• Data are usually in lowercase or a combination of
uppercase and lowercase.
• Comments are included sparingly, but usefully.
6
Class Exercise Database
Vendor
PK
VendorID
VendorMaterial
provides
PK,FK1
PK,FK2
VendorName
FirstBuyDate
VendorID
MaterialID
CurrentPrice
RawMaterial
is offered
PK
MaterialID
Description
UnitOfMeasure
StandardPrice
is
located
MaterialLocation
PK,FK1
PK,FK1
PK
VendorID
MaterialID
LocationID
QuantityOnHand
Referential integrity: Table constraint. When
inserting data in the child table, checks to see
whether a related row exists in the parent table.
Let’s create a small table
• Click on the “new query” button. This is “task #1”
on the exercise document.
• Type the following:
CREATE TABLE tblVendor
(VendorID
char(4)
primary key,
VendortName
varchar(30),
FirstBuyDate datetime);
• Click on the “Execute” button.
Deleting a table
• Cannot have more than one data object with the same
name.
• Must delete data objects before re-creating them.
• SQL Statement is:
DROP TABLE tblVendor;
• Let’s try and drop the table named tblVendor. Keep the
current query tab open, and open another “new query” tab.
Drop the table. This is task #2 on the exercise.
• General information: Must delete objects in the order of
referential integrity constraints.
Create a named constraint
• Constraints can be “named” in SQL for easier future
reference. This is task #3 on the exercise.
CREATE TABLE tblVendor
(VendorID
char(4),
VendorName varchar(30),
FirstBuyDate
datetime
CONSTRAINT pkVendor PRIMARY KEY (vendorID));
• Constraint names may be referenced in the future if they
need to be changed (altered).
• Easier to reference a name that you create, rather than one
that SQL Server creates for you.
SQL INSERT Statement
• Used to “populate” a table with data.
• Used to enter one row of data.
• Character data and dates must be entered
surrounded by single quotes.
• Dates can be entered using a variety of formats:
– ‘26-sep-2013’
– ‘09/26/2013’
– ‘09-26-2013’
Let’s put data into that table
• Click on the “new query” button.
• Type the SQL statements on Task #4 on the
exercise document. Each command will produce
one row in the table called “tblVendor”.
Let’s look at the data
• Click on the “new query” button.
• The SQL statement below is task #5 on the
exercise document.
SELECT
FROM
*
tblVendor;
Examples of Retrieving Data from a Table
SELECT
FROM
*
tblVendor;
SELECT
VendorID,
VendorName
tblVendor
VendorID = ‘0062’;
FROM
WHERE
The * means retrieve all columns.
The FROM statement, without a
WHERE statement, means retrieve
all rows.
Now create the other three tables in the
sample ERD
Do tasks #6 through #10 on the
exercise document.
Sharing tables with your team
• You are the owner of your database.
• You have the right to GRANT access to your database.
• There is a handout on the class website discussing how to
change permissions, and we will discuss it next class.
• Best practice for right now:
– Create and populate the tables in each group member’s database.
The data for HW#5-8 is relatively static, so it will lessen the
complexity of the assignments if everyone has direct access to the
tables.
– Each class member should get experience creating and populating
tables in his/her own database, even when working as part of a
team.