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 o	Emp_Na me	Emp_Mobile No	Emp_Email	Emp_Addr ess
1	ALI	33333	ALI@ALI.COM	Pakistan
2	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 ;

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');

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