*Update : Tested with Microsoft SQL Server 2008 Express Edition when creating this database. If you are getting errors , pls check the monetary values. Instead of 500.00 , they turn into 500,00 which makes into 2 SQL values instead of 1. Just replace , with . and places ” around the numbers.*

SQL Database For Examples

It has dawn on me that our original sample database is inadequate for more advanced SQL commands such as GROUP BY and JOIN statements. Hence, I am going to have to redesign a new database , with perhaps 2 or 3 tables so as to showcase advanced SQL features.

Lets do it differently this time. Lets have a table about an employee of a company. He , or she for that matters , shall have these following attributes ,

• Employee ID (Primary Key)
• Department ID (Foreign Key)
• Address
• Title
• Email
• Office extension number
• Mobile number
• Sex
• Salary
• Date Hired
• D.O.B

These should be enough for us to play with. Now , for the department table ,

• Department ID (Primary Key)
• Department Name
• Department Manager ID (Foreign Key)
• Department Budget

Lets add another table for managers ,

• Manager ID (Primary Key)
• Department ID (Foreign Key)

Ok! This seems fine. Not so simple yet not so complicated. Lets begin by creating the

database

and then use it.

create database sqlhack;
grant all privileges on anime.* to 'sqlhack'@'localhost' identified by 'sqlhack';
flush privileges;

And lets get into the database.

use sqlhack;

Then we shall we move on to creating the tables. Here is the employee table ,

create table employee (emp_id integer primary key , emp_dept_id integer , emp_name varchar(30) , emp_address varchar(60) , emp_title varchar(30) , emp_email varchar(30) , emp_office_num integer , emp_mobile_num integer , emp_sex varchar(6) , emp_salary decimal(10,2) , emp_start_date date , emp_dob date);

Next is the department ,

create table department ( dept_id integer primary key , dept_name varchar(60) , dept_manager_id integer , dept_budget decimal(10,2) );

Finally , the manager table ,

create table manager ( manager_id integer primary key , dept_id integer );

Here is a few thing about this database that we have not covered before.

• Date datatype is used for data such as Date.Of.Birth and starting date.
• For monetary values such as Department’s budget and salary , I have used decimal (10,2) , meaning up to 10 digits with 2 decimal places.

Now to add some data into the tables. Then we can start playing with it

insert into employee (emp_id , emp_dept_id, emp_name , emp_address , emp_title  , emp_email , emp_office_num  , emp_mobile_num , emp_sex  , emp_salary  , emp_start_date  , emp_dob) values (1001 , 101 , 'John Evan' , 'New York , New York' , 'Manager' , 'John.Evan@SQLHack' , 665 , 123456 , 'Male' , '5000.00' , '2011-01-01', '1965-12-24');
insert into department (dept_id , dept_name , dept_manager_id , dept_budget) values ( 101 , 'Finance' , 1001, '500000.00');
insert into manager ( manager_id , dept_id) values ( 1001 , 101);

Lets add more data to the tables so it looks prettier

insert into employee (emp_id , emp_dept_id, emp_name , emp_address , emp_title  , emp_email , emp_office_num  , emp_mobile_num , emp_sex  , emp_salary  , emp_start_date  , emp_dob) values (1002 , 102 , 'Christine May' , 'San Francisco' , 'Sales Executive' , 'Christine.May@SQLHack' , 587 , 658241 , 'Female' , '4000.00' , '2010-06-20', '1972-09-11');
insert into employee (emp_id , emp_dept_id, emp_name , emp_address , emp_title  , emp_email , emp_office_num  , emp_mobile_num , emp_sex  , emp_salary  , emp_start_date  , emp_dob) values (1003 , 100 , 'George Chan' , 'Hong Kong' , 'Vice President' , 'George.Chan@SQLHack' , 952 , 646512 , 'Male' , '50000.00' , '2007-10-08', '1952-06-24');
insert into employee (emp_id , emp_dept_id, emp_name , emp_address , emp_title  , emp_email , emp_office_num  , emp_mobile_num , emp_sex  , emp_salary  , emp_start_date  , emp_dob) values (1000 , 100 , 'Michael Bay' , 'Washington' , 'CEO' , 'Michael.Bay@SQLHack' , 557 , 546474 , 'Male' , '90000.00' , '1997-10-10', '1959-04-13');
insert into department (dept_id , dept_name , dept_manager_id , dept_budget) values ( 102 , 'Sales' , 1010, 5000000.00);
insert into department (dept_id , dept_name , dept_manager_id , dept_budget) values ( 100 , 'Asia' , 1000, 8880000.00);
insert into manager ( manager_id , dept_id) values ( 1000 , 100);
insert into manager ( manager_id , dept_id) values ( 1010 , 102);

Added more departments.

insert into department (dept_id , dept_name , dept_manager_id , dept_budget) values ( 103 , 'Marketing' , 1110 , 86000.00);
insert into department (dept_id , dept_name , dept_manager_id , dept_budget) values ( 104 , 'Delivery' , 1210 , 243500.00);
insert into department (dept_id , dept_name , dept_manager_id , dept_budget) values ( 105 , 'Manufacturing' , 1218 , 34500.00);

Updates on Sample Database

Coming soon!