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 | [email protected] | Pakistan |
| 2 | Asad | 333333334 | [email protected] | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | 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.
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.