Creating a table

1. Creating a table Using SQL Server

2. What is a table? • Tables are equivalent to files • A table has a set of columns • Each column holds data • The columns are equivalent to headings.

3. A Book table • If I decide that the information I need to know about (or headings for) a book is: • ISBN • Title • Author • Price • Number In Stock • Genre • Date published

4. I need to… • Decide how many column entries there will be for each heading. • E.g. how many authors are there for a book? • How will we accommodate more than one? • Decide on how to describe an ISBN, Title, etc. • Decide on the format of the information • Is it numeric or textual? • Are there any conditions it should follow? • Can it be empty for a specific book?

5. To set up a table • I need to have a database in which to put it. • I need to OWN the database, or have write access to it. • I need to have a client by which I can access the database. • I need to use the CREATE statement.

6. Format of the CREATE CREATE TABLE tablename ( {column_name datatype columnconstraint,} {tableconstraint} )

7. Example CREATE TABLE book ( ISBN varchar(13), Title varchar(40), Price numeric(7,2), NumberInStock integer, Genre varchar(10), DatePublished datetime )

8. CREATE TABLE book ( ISBN varchar(13), Title varchar(40), Price numeric(7,2), NumberInStock integer, Genre varchar(10), DatePublished datetime ) Varchar is a variable length character string with maximum length specified. Numeric (a,b) specifies a number with a digits, b of which are after the decimal place. Integer is an integer. Datetime takes a date and a time. Data types

9. Dropping a table • When you have created a table, you have set up a definition in the database of that table. • You cannot create the table again, while the first definition is still there. • To remove the table, use the DROP command. • DROP table BOOK

10. A better CREATE • To improve on our previous CREATE statement: • Define one of the fields as a key • Allow null values to be entered against the number of books in stock. • Don’t allow null values to be entered against the Title or ISBN. • Check that the Price is less that €100. • Put in a default value of ‘Drama’ for Genre.

11. New CREATE CREATE table BOOK ( ISBN varchar(13) not null, Title varchar(40) not null, Price numeric(7,2) check (Price < 100.00), NumberInStock integer null, Genre varchar(10) default ‘Drama’, DatePublished datetime, Primary key (ISBN) )

12. CREATE table BOOK ( ISBN varchar(13) not null, Title varchar(40) not null, Price numeric(7,2) check (Price < 100.00), NumberInStock integer null, Genre varchar(10) default ‘Drama’, DatePublished datetime, Primary key (ISBN) ) Not null – no nulls allowed in this field Null – nulls allowed in this field. Check – check the condition in brackets. Default – if no value is given in a new entry, this is the value that will be stored. Primary key – this is the key to the table. It can be one or more fields. Constraints used

13. Inserting data into a table • This is done using the INSERT statement. • The INSERT has two formats: • Full insert: INSERT into BOOK values(‘1-56592-744-3’,’SQL in a Nutshell’,0,23.99,’Technical’,’01/01/01’) • Or partial insert: INSERT into BOOK(ISBN, Title,DatePublished) values(‘0-07-135953-2’,’Fundamentals of SQL Programming’,’01/01/00’)

14. To look at the table contents.. • Use the SELECT instruction: select * from book • Literally means show everything from the book table • The result will be:

15. Exercises • Add the BOOK table to your database. • Try to insert the following rows: INSERT INTO BOOK values ( '0-13-085033-0', 'Programming Oracle Triggers and Stored Procedures', 49.99, 40, 'Computing', '01/01/2004') INSERT INTO BOOK values ( '0-201-11803-0', 'Visual Quickstart Guide SQL', 29.50, 10, 'Computing', '01/01/2002') update book set price = 40 where isbn = '1-56592-744-3' • Find out what the errors are, and why they happened.