MySQL Information and Resources

SQLINFO.net is a Community Service from DataExpressions makers of dbOrchestra©                  Bookmark this page

Google
 


MySQL Code Examples

SQL DML

String Functions

Stored Procedures

SQL Views

SQL Table Basics

"Students" table schema and data used in various examples on SQLINFO.net site

Students table schema (ddl)

 

CREATE TABLE IF NOT EXISTS students
     (
       student_id                    INT(11)       NOT NULL   , 
       password                      VARCHAR(15)       NULL   , 
       employee_id_fk                VARCHAR(15)       NULL   , 
       customer_id_fk                VARCHAR(15)       NULL   , 
       active_flg                    TINYINT(4)    NOT NULL   , 
       lastname                      VARCHAR(30)       NULL   , 
       firstname                     VARCHAR(20)       NULL   , 
       middlename                    VARCHAR(20)       NULL   , 
       prefix_name                   VARCHAR(6)        NULL   , 
       suffix_name                   VARCHAR(6)        NULL   , 
       gender_code                   VARCHAR(1)        NULL   , 
       birth_dttm                    DATETIME          NULL   , 
       birth_city_nm                 VARCHAR(20)       NULL   , 
       birth_state_code              VARCHAR(2)        NULL   , 
       birth_country_code            VARCHAR(2)        NULL   , 
       gpa                           INT(11)           NULL   , 
       is_on_staff                   TINYINT(4)    NOT NULL   , 
       soc_sec_num                   VARCHAR(9)        NULL   , 
       driver_license_num            VARCHAR(25)       NULL   , 
       driver_license_state_code     VARCHAR(2)        NULL   , 
       driver_license_country_ocde   VARCHAR(2)        NULL   , 
       other_id_num                  VARCHAR(25)       NULL   , 
       citizen_country_code          VARCHAR(2)        NULL   , 
       marital_status_code           VARCHAR(3)        NULL   , 
       race_code                     VARCHAR(5)        NULL   , 
       ethnic_group_code             VARCHAR(5)        NULL   , 
       veteran_military_status_code  VARCHAR(2)        NULL   , 
       fin_alert_code                VARCHAR(2)        NULL   , 
       inactive_date                 DATETIME          NULL   , 
       inactive_reason               VARCHAR(255)      NULL   , 
       employee_inactivating         INT(11)           NULL   , 
       PRIMARY KEY (student_id) 
     ) ENGINE = INNODB

GO

CREATE UNIQUE INDEX num USING BTREE
ON     students(password) 

GO

CREATE UNIQUE INDEX other_id USING BTREE
ON     students(other_id_num) 

GO

CREATE INDEX driver_license_num USING BTREE
ON     students(driver_license_num) 

GO

CREATE INDEX last_nm USING BTREE
ON     students(lastname) 

GO

CREATE INDEX soc_sec_num USING BTREE
ON     students(soc_sec_num) 

GO

CREATE INDEX student_id USING BTREE
ON     students(student_id) 

GO
 


Copyright 2006-2007 by DataExpressions --- All Rights Reserved            dbOrchestra - MySQL home page