Transcript DT211 Stage 2

DT211 Stage 2
Databases
Lab 1
Get to know SQL Server
• SQL server has 2 parts:
– A client, running on your machine, in the lab.
• You access the database from here, using SQL.
• You can store your SQL files locally or on your home drive.
– A server, running on a remote machine.
• This stores your table descriptions and data.
• Later you will store functions on this also.
• You can only read the databases on the server to which you
have been granted read access.
• You can only write to or update databases to which you have
been granted full access.
• You each have one database to which you have ownership
access and several to which you have read access.
Connecting to SQL Server
• The server you are using this year is on
the remote machine called CIAN.
• The IP address of this machine is
– 147.252.224.68
• Use the instructions following to connect to
SQL Server.
Connecting to SQL Server
Using the Query Analyser
Finding SQL Server
• Use the Start menu
– Programs
– Microsoft SQL Server 2000
– Query Analyser
Connecting…
• The SQL Server: is cian.comp.dit.ie
– Sometimes the name CIAN will do
– Sometimes you need to use the IP address
– The IP address is currently 147.252.224.68
• Use Windows NT authentication
• OK
Connection …
When you connect first, the database will default to the one
you own.
Creating a table
• To create a table you must know:
– The table name.
– The domain (field / column) names in the
table.
– The column(s) that form the unique key to the
table.
– The datatypes of all of the columns.
Datatypes in SQL Server
• The most commonly used datatypes in SQL Server are:
– Numeric
•
•
•
•
Followed by the full number of digits
Followed by the number after the decimal place
E.g. StaffId Numeric(7,0)
E.g. CostPrice Numeric(9,2)
– Datetime
• This can be used to store a date and time. No further specification is
required.
• E.g. Order_Date datetime
– Varchar
• This can be used to store a variable length string, with a maximum number
of characters specified.
• E.g.
Delivery_Address varchar(80)
– Char
• This can be used for fixed length strings – usually short.
• E.g. CourseCode Char(5).
The CREATE statement
• Each table is set up on the server using
the CREATE statement.
• The basic syntax is:
Create table (
{Column-name datatype,}
)
– {} denotes that this can be repeated
– Italics denote that the designer names the
item.
Example table
• Create table BOOK
(ISBN varchar(11),
BookTitle varchar(80),
Author varchar(20),
CostPrice numeric(6,2),
Genre char(8)
)
Improving your table
• The table given above does not constrain
the data much at all.
• To be more rigorous about the data that
can go into the table, constraints are
required.
• There are table constraints and column
constraints.
Column constraints
• Unique
– This stops the user from entering the same value for
this column twice.
– E.g. ISBN varchar(11) unique,
• Default
– This gives a default value to any field that has not
been assigned a value
– E.g. CostPrice numeric(6,2) default 7.99
• Not Null
– This means that the value CANNOT be null
– E.g. Booktitle varchar(80) not null
Creating and Dropping
• Once you have created a table in your
database, you cannot create it again.
• To delete the table, use DROP
– E.g. DROP TABLE BOOK
– This deletes the table and all its contents.
Today’s exercises
• Connect to your database on the SQL
Server on CIAN through the Query
Analyser.
• Create a table BOOK as shown above.
• Using the browser in the Query analyser,
check that BOOK is there.
• Drop the BOOK table.
• Put constraints on the table. Create it
again.
Tutorial tables
• Create the tables that you devised in the
tutorial.
• Leave them on the Server. Next week
we’ll add data to them.