Discovering SQL: A Hands-On Guide for Beginners

POPULATING A TABLE WITH DIFFERENT DATA TYPES

Populating a table with different data types is a snap as long as you match the type and supply the expected format: Strings need to be enclosed in single quotes (though some RDBMSs allow you to mix and match), number literals are provided as-is, and so on.

Literal in programming context means “hard-coded value,” and you might have heard that this is a bad word in software development circles. Yet when SQL statements are constructed, prior to being submitted for execution, all the values (with the exception of these supplied by DEFAULT constraints) must be properly formatted literals.

Let's construct an INSERT statement for the BOOKS table:

INSERT INTO books(
             bk_id
           ‚bk_title
           ‚bk_ISBN
           ‚bk_publisher
           ‚bk_published_year
           ‚bk_price
           ‚bk_page_count
           ‚bk_bought_on
           ‚bk_hard_cover
           ‚bk_cover_pic
           ‚bk_notes)
     VALUES
           (1
           ‚‘SQL Bible’
           ‚‘978-0470229064’
           ‚‘Wiley’
           ‚2008
           ‚39.99
           ‚888
           ‚GETDate()
           ‚0
           ‚NULL
           ‚NULL)

A brief examination of the structure should tell you that the only required value you need to supply is BK_ID because it has a NOT NULL constraint on it; everything else can be populated with default NULLs:

INSERT INTO books (bk_id) VALUES (8)

The preceding statement will insert a new row into the table and leave all fields but one empty, filled with default NULLs signifying absence of any data. Any attempt to insert a new record without involving BK_ID will fail with an error message informing you that the RDBMS can't insert the value NULL into ...