Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Create a Testing Table for Tutorial Exercises

By: FYIcenter.com

(Continued from previous topic...)

How To Create a Testing Table with Test Data?

If you want to practice DML statements, like INSERT, UPDATE and DELETE statements, you should create a testing table. The tutorial exercise shows you a good example:

CREATE TABLE fyi_links (id INTEGER PRIMARY KEY,
  url VARCHAR(80) NOT NULL,
  notes VARCHAR(1024),
  counts INT,
  created DATETIME NOT NULL DEFAULT(getdate()))
GO

SELECT c.column_id as seq, c.name, x.name as type, 
      c.max_length, c.is_nullable
   FROM sys.columns c, sys.tables t, sys.systypes x
   WHERE c.object_id = t.object_id
   AND c.system_type_id = x.xtype
   AND t.name = 'fyi_links'
   ORDER BY c.column_id
GO
seq  name     type      max_length  is_nullable
1    id       int       4           0
2    url      varchar   80          0
3    notes    varchar   1024        1
4    counts   int       4           1
5    created  datetime  8           0

You should keep this table to practice other tutorial exercises presented in this collection.

(Continued on next topic...)

1. What Are DML (Data Manipulation Language) Statements?
2. How To Create a Testing Table with Test Data?
3. How To Insert a New Row into a Table with "INSERT INTO" Statements?
4. How To Use Column Default Values in INSERT Statements?
5. How to provide column names in INSERT Statements?
6. What Happens If You Insert a Duplicate Key for the Primary Key Column?
7. How To Insert Multiple Rows with One INSERT Statement?
8. How To Update Values in a Table with UPDATE Statements?
9. How To Update Multiple Rows with One UPDATE Statement?
10. How to use old values to define new values in UPDATE statements?
11. Is the Order of Columns in the SET Clause Important?
12. How To Use Values from Other Tables in UPDATE Statements?
13. What Happens If the UPDATE Subquery Returns No Rows?
14. What Happens If the UPDATE Subquery Returns Multiple Rows?
15. How To Delete an Existing Row with DELETE Statements?
16. How To Delete Multiple Rows with One DELETE Statement?
17. How To Delete All Rows with DELETE Statements?
18. How To Delete All Rows with TRUNCATE TABLE Statement?