Employee table in SQL

Let’s see the normalized emp table in SQL.

Database: t4tutorials, Table: departments

Dept_Num	Dept_Name	Dept_City
1	CS	Islamabad
2	SE	CALIFORNIA

Database: t4tutorials, Table: employee

Emp_N

Emp_Na

Emp_Mobile

Emp_Email

Emp_Addr

ess

ALI

33333

ALI@ALI.COM

Pakistan

Asad

333333334

Asad@Asad.COM

United States

Database: t4tutorials, Table: mp_department

DepID	Emp_No	Dept_Num
1	1	1
2	2	2

Creating employee table in MySQL

Execute this query to auto-create tables in MYSQL

CREATE TABLE `addresstable` (

  `addressid` int(11) unsigned zerofill NOT NULL auto_increment,

  `custid` int(11) unsigned zerofill default NULL,

  `addressline1` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

  `addressline2` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

  `cityortown` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

  `county` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

  `postcode` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,

  `creationdateaddresstable` timestamp NOT NULL default CURRENT_TIMESTAMP,

  PRIMARY KEY  (`addressid`),

  KEY `custid` (`custid`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

 

CREATE TABLE `customerdetail` (

  `custid` int(11) unsigned zerofill NOT NULL auto_increment,

  `firstname` varchar(50) default NULL,

  `surname` varchar(50) default NULL,

  `dob` date default NULL,

  `permissionnewsletter` varchar(50) default NULL,

  `email` varchar(50) default NULL,

  `userpassword` varchar(50) default NULL,

  `telephone` varchar(50) default NULL,

  `creationdatecustomerdetail` timestamp NOT NULL default CURRENT_TIMESTAMP,

  PRIMARY KEY  (`custid`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=75 ;

CREATE TABLE `addresstable` (

`addressid` int(11) unsigned zerofill NOT NULL auto_increment,

`custid` int(11) unsigned zerofill default NULL,

`addressline1` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

`addressline2` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

`cityortown` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

`county` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

`postcode` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,

`creationdateaddresstable` timestamp NOT NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`addressid`),

KEY `custid` (`custid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

CREATE TABLE `customerdetail` (

`custid` int(11) unsigned zerofill NOT NULL auto_increment,

`firstname` varchar(50) default NULL,

`surname` varchar(50) default NULL,

`dob` date default NULL,

`permissionnewsletter` varchar(50) default NULL,

`email` varchar(50) default NULL,

`userpassword` varchar(50) default NULL,

`telephone` varchar(50) default NULL,

`creationdatecustomerdetail` timestamp NOT NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`custid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=75 ;

Inserting data into employee table in MySQL

Execute this query to auto-insert data in tables using MYSQL.

INSERT INTO `emp_department` (`DepID`, `Emp_No`, `Dept_Num`) VALUES ('1', '1', '1'), ('2', '2', '2');

1	INSERT INTO `emp_department` (`DepID`, `Emp_No`, `Dept_Num`) VALUES ('1', '1', '1'), ('2', '2', '2');

Similarly, you can insert data into all tables, by simplifying modifying queries or by using the end-user interface of your tool.

Database: t4tutorials, Table: departments

Database: t4tutorials, Table: employee

Database: t4tutorials, Table: mp_department

Creating employee table in MySQL

Inserting data into employee table in MySQL

Download emp table in SQL

Database tables in SQL

Related Posts: