1 / 18

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.

nelia
Download Presentation

DT211 Stage 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DT211 Stage 2 Databases Lab 1

  2. 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.

  3. 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.

  4. Connecting to SQL Server Using the Query Analyser

  5. Finding SQL Server • Use the Start menu • Programs • Microsoft SQL Server 2000 • Query Analyser

  6. 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

  7. Connection … When you connect first, the database will default to the one you own.

  8. 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.

  9. 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).

  10. 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.

  11. Example table • Create table BOOK (ISBN varchar(11), BookTitle varchar(80), Author varchar(20), CostPrice numeric(6,2), Genre char(8) )

  12. 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.

  13. 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

  14. 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.

  15. 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.

  16. Tutorial tables • Create the tables that you devised in the tutorial. • Leave them on the Server. Next week we’ll add data to them.

More Related