Transcript SQL CREATE TABLE Statement

Agenda for Class 9/25/2012
• Introduce Microsoft’s SQL Server database
management system.
• Use the lab to discuss how to CREATE, DROP
and populate (INSERT) tables.
• Introduce SELECT statement.
• Discuss connection between MS Access and SQL
Server.
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
SQL Background
• SQL (structured query language) is a nonprocedural language designed to process data.
• Data processing operations include:
– Creating tables
– Create and enforcing constraints
– Adding, changing, deleting data
– Accessing data (either through single rows or
aggregation)
• SQL is an ANSI (American National Standards
Institute) standard language
5
SQL is not...
• A front-end programming tool
• Does not:
– Make pretty output (forms/reports)
– Make pretty websites
– Make anything pretty for users to look at!!
– Do extensive calculations
– Do statistical calculations
6
How does SQL operate?
• All commands are considered to be “queries”
• Any command acts upon the database:
– creating or altering (changing) database objects,
– adding/deleting/changing data inside a database
object,
– looking at data inside a database object.
• Operates with an implied loop; no explicit loops.
The programmer has no control over the execution
of the loop.
7
SQL Commands
Data Definition
Commands
(DDL)
Data Manipulation
Commands
(DML)
Data Control
Commands
(DCL)
CREATE
INSERT
GRANT
ALTER
UPDATE
REVOKE
DROP
DELETE
TRUNCATE
SELECT
COMMIT
ROLLBACK
SET
TRANSACTION
8
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.
• Can span more than one physical line; it is a free
form language.
• SQL keywords cannot be abbreviated or split
across lines.
9
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.
10
Class Exercise Database
TblEmployee
PK employeeID
lastname
billingrate
TblTime
records
PK,FK1 employeeID
PK
Datetimestarted
FK2
Amount
contractID
Is
recorded
for
TblContract
PK contractID
datesigned
datedue
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.
• Type the following:
CREATE TABLE
(employeeID
LastName
BillingRate
tblemployee
char(5) primary key,
varchar(30),
money);
• 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 tblemployee;
• Let’s try and drop the table named tblemployee. Keep the
current query tab open, and open another “new query” tab.
Drop the table.
• 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.
CREATE TABLE tblemployee
(employeeID char(4),
LastName
varchar(30),
BillingRate money,
Constraint
pkemployee
Primary key (employeeID));
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:
– ‘25-sep-2012’
– ‘09/25/2012’
– ‘09-25-2012’
Let’s put data into that table
• Click on the “new query” button.
• Type the following SQL Commands (or copy from the PowerPoint).
Each command will produce one row in the table called “tblemployee”.
• This is task #3 on the exercise sheet.
INSERT INTO tblemployee VALUES
(‘7819', ‘Martinson’, 125);
INSERT INTO tblemployee VALUES
(‘2745', ‘Johnson’, 85.50);
INSERT INTO tblemployee VALUES
(‘0062', Belwin, 54.75);
INSERT INTO tblemployee VALUES
(0062, ‘Smith’, 200);
Let’s look at the data
• Click on the “new query” button.
• The SQL statement below is task #4 on the
exercise sheet.
SELECT
FROM
*
tblemployee;
Examples of Retrieving Data from a Table
SELECT
FROM
*
tblemployee;
SELECT
FROM
WHERE
employeeID, lastname
tblemployee
employeeID = ‘0062’;
The * means retrieve all columns.
The FROM statement, without a
WHERE statement, means retrieve
all rows.
Now create the other two tables in the
sample ERD
TblEmployee
PK employeeID
lastname
billingrate
TblTime
records
PK,FK1 employeeID
PK
Datetimestarted
FK2
Is
recorded
for
Amount
contractID
Do tasks #5 and #6 on the exercise
sheet.
TblContract
PK contractID
datesigned
datedue
Sample Tables with Concatenated Foreign Key
tblOrderLine
order_id
PK,FK1
PK,FK2
PK
order_id
prod_id
ship_date
cust_id
ord_date
FK2
FK2
manufacturer_id
location_id
qty
price
tblOrd
PK
contains
tblProduct
Is
purchased
on
PK
PK
PK
This is NOT a task on the
exercise sheet. This shows
how to handle a concatenated
foreign key. Will be discussed
in class.
prod_id
manufacturer_id
location_id
cost
CREATE TABLE tblOrd
(order_id
CHAR(5) PRIMARY KEY,
cust_id
CHAR(3),
ord_date
DATETIME);
CREATE TABLE tblProduct
(prod_id
INT,
manufacturer_id
CHAR(6),
location_id
INT,
cost
MONEY,
PRIMARY KEY (prod_id, manufacturer_id,
location_id));
CREATE TABLE
tblOrderLine
(order_id
CHAR(5),
prod_id
INT,
ship_date
DATETIME,
manufacturer_id
CHAR(6),
location_id
INT,
qty
DECIMAL(8,2),
price
MONEY,
PRIMARY KEY (order_id,prod_id,ship_date),
CONSTRAINT product_fk
FOREIGN KEY (prod_id,manufacturer_id,location_id)
REFERENCES tblProduct(prod_id,manufacturer_id,location_id),
CONSTRAINT order_fk
FOREIGN KEY (order_id)
REFERENCES tblOrd(order_id));
Creating tables with other constraints
CREATE TABLE tblExample
(field1
INT
PRIMARY KEY,
field2
CHAR(2)
CHECK (code_field in(‘02’,’A1’,’B3’,’04’)),
field3
MONEY
CHECK (cost > .02),
field4
char(5)
NOT NULL,
field5
decimal(6,4)
default 1
);
SQL Server Connections
• Not a “front end” package; not designed for endusers.
• Can be connected as a backend database to a variety
of different packages.
• Access is the most convenient for our environment.
Copy the results into Word
• Select all the columns and rows from tblemployee.
• Copy and paste the results from SQL Server into
Word
• See how ugly?
• Could do a screen capture to improve the look.
Using MS Access as our “front end”
• Open a file on the “k” drive called:
IS475\f12\StoredProcedureTemplate.
CREATE PROCEDURE --Stored Proc Name Here
AS
--Put your code here
GO
Using an MS Access Project for Output
• Refer to the “MSAccess FrontEnd” link on the
“handouts” column for 9/25 on the course web
site.
• Follow the instructions to create an empty project
shell in MS Access that links to SQL Server.
• Then create a query that can be retrieved from MS
Access.
• Look at the output in MS Access.
• Copy and paste output from MS Access to Word.
Sharing tables with your team
•
•
•
•
Discuss database owners.
Discuss database name in object browser.
Discuss permissions.
Best practice: 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.