BASIC MYSQL QUERY

1:  DROP DATABASE COMPANY;  
2:  SHOW DATABASES;  
3:  CREATE DATABASE COMPANY;  
4:  USE COMPANY;  
5:  SHOW TABLES;  
6:  CREATE TABLE DEPARTMENT(  
7:      DEPT_ID int NOT NULL,  
8:      DEPT_NAME varchar(255) NOT NULL,  
9:      PRIMARY KEY(DEPT_ID)  
10:  );  
11:  CREATE TABLE EMPLOYEE(  
12:      EMPL_ID int NOT NULL,  
13:      DEPT_ID int NOT NULL,  
14:      EMPL_NAME varchar(255) NOT NULL,  
15:      PRIMARY KEY(EMPL_ID),  
16:      FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)      
17:  );  
18:  CREATE TABLE TASK(  
19:      TASK_ID int NOT NULL,  
20:      TASK_NAME varchar(255),  
21:      PRIMARY KEY(TASK_ID)  
22:  );  
23:  CREATE TABLE TASK(  
24:      TASK_ID int NOT NULL,  
25:      TASK_NAME varchar(255) NOT NULL,  
26:      PRIMARY KEY(TASK_ID)  
27:  );  
28:  CREATE TABLE TASKASSIGN(  
29:      TASK_ID int NOT NULL,  
30:      EMPL_ID int NOT NULL,  
31:      DATE_ASSGIN date,  
32:      PRIMARY KEY(TASK_ID,EMPL_ID)  
33:  );  
34:  insert into department() values(21,'Software Development');  
35:  insert into department() values(11,'Marketing');  
36:  insert into department() values(10,'Sale');  
37:  insert into department() values(9,'Quality Assurance');  
38:  insert into department() values(5,'Human Resource');  
39:  insert task_assign() values(1,1,1,now());  
40:  /**Select from multi tables **/  
41:  select empl_name , dept_name   
42:      from department , employee   
43:      where employee.dept_id=department.dept_id;  
44:  /**Add now() column in result **/  
45:  select task_id, task_name , now()   
46:      as date_check   
47:      from task;  
48:  /**Select from result of joint tables **/  
49:  select *   
50:      from department   
51:      left join employee   
52:      on department.dept_id = employee.empl_id;  
53:  /**Create a view from multi tables **/  
54:  create view empl_dept   
55:      as   
56:      select employee.empl_name , department.dept_name   
57:      from employee, department   
58:      where employee.dept_id=department.dept_id;  
59:  /**Count number of person in each department **/  
60:  select empl_dept.dept_name, count(*)   
61:      as number_per   
62:      from empl_dept   
63:      group by empl_dept.dept_name   
64:      order by count(*) desc;  
65:  /** Count number each projects **/  
66:  /*STEP 1 Create a view */  
67:  create view task_person   
68:    as select task.task_name,task_assign.empl_id  
69:    from task, task_assign  
70:    where task.task_id=task_assign.task_id;  
71:  /*STEP 2 Count on view */  
72:  select task_name , count(*) as number_per  
73:    from task_person  
74:    group by task_name  
75:    order by count(*) desc;  
76:  /** RESULT  
77:  +-------------+------------+  
78:  | task_name   | number_per |  
79:  +-------------+------------+  
80:  | Health Care |     9 |  
81:  | Java Fx     |     4 |  
82:  +-------------+------------+  
83:  **/ 

SCREEN SHOT :