admin
Site Admin
Joined: 28 Feb 2006
Posts: 24
|
 Posted: Fri Apr 21, 2006 5:38 pm Post subject: Create table SQL examples |
 |
|
Here are some simple examples of SQL for creating (adding) tables in a MySQL database. First I create a single table, then I create a group of related tables that interoperate as a schema for an online questionnaire.
Create a table with a unique id for a primary key, and two VARCHAR fields. This will be used for a simple search engine:
| Code: |
create table test (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
search_key VARCHAR(80) NOT NULL,
url VARCHAR(255));
|
Create a Database Schema for an online survey
Each question can have multiple options. Each option can be a boolean (true or false), or a number. A question could ask the responders to check all options that apply, or could ask for a rating on a scale from 1 to 10, for example. Each response is the answer for an option of a question.
The question table:
| Code: |
create table questions (question_id int auto_increment primary key, num_options int, num_needed int, q_text varchar(255) not null) ;
|
Each question has a number of options; the option is linked back to it's question with the quesiton_id field. Each option has a text description, and a unique id of its own.
| Code: |
create table options (option_id int auto_increment primary key, question_id int, o_text varchar(255) not null) ;
|
Each responder is identified by a first name and email address, and is assigned a unique id for the table index.
| Code: |
create table responder (responder_id int auto_increment primary key, first_name varchar(100), email varchar(128));
|
Now for the actual answers. We store one value for each responder's answer to each option in each question. So this table should be, using set terminology, the cross-product of questions, options, and responders.
| Code: |
create table response (responder_id int, question_id int, option_id int, answer_int int);
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
