MySQL Exercises and Answers

One, the existing three database tables, respectively, the Department table, Staff table, Department and Employee Relations table

1. Department table
CREATE TABLE ' t_dept ' (
' ID ' int (8) not NULL auto_increment,
' Dept_name ' varchar (the DEFAULT NULL COMMENT ' Department '),
PRIMARY KEY (' id ')
) CHARACTER SET UTF8 COLLATE utf8_general_ci comment= ' departmental table ID is the primary key ';

2. Employee table
CREATE TABLE ' T_user ' (
' ID ' int (8) not NULL auto_increment,
' user_number ' varchar DEFAULT NULL,
' user_name ' varchar DEFAULT NULL,
PRIMARY KEY (' id ')
Engine=myisam DEFAULT charset=latin1 comment= ' Employee table ID is the primary key ';

3, the Department Employee Relations table
CREATE TABLE ' T_deptuser ' (
' ID ' int (8) not NULL auto_increment,
' dept_id ' int (8) DEFAULT NULL,
' user_id ' int (8) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT charset=latin1;

Question one, please write a SQL to find out all the employees who work in the "Research and Development department". (MySQL database).

Question two, please write a sentence of SQL, at the same time to find out all employee names and the department name.

Question one, select u.* from T_deptuser du
Left JOIN t_user u
On u.id = du.user_id
Left JOIN t_dept D
On d.id = du.dept_id
where d.dept_name = ' Development Department '

Question Two,
Select U.user_name,d.dept_name from T_user u
Left JOIN t_deptuser du
On u.id = du.user_id
Left JOIN t_dept D
On d.id = du.dept_id

MySQL Exercises and Answers