Question

Create a Database from blank (scratch) for a manager and name it. Create and design a table and name it. For each fields click and choose proper a data type such as short text and name the field. Make at least three fields. Enter your records. Make sure to add your name as a record.

Similarly create two more tables. By design tool, make a relationship between each of two tables at a time and drag a primary key one table to a secondary key of another table. For example one to one, one to many, many to one or many to many)

Run at least 5 queries of different important tasks on the data base

For this question I was given no data

Answer 1

create table manager(m_id int, m_name char(10), dept_name char(20) , emp_id int);

insert into manager values ( 1, 'Tom', 'computer',101);
insert into manager values ( 2, 'Harry', 'economics',102);
insert into manager values ( 3, 'France', 'biology', 103);

create table employee( emp_id int, emp_name char(10), m_id int, dept_name char(20), salary decimal,job_name char(20) ) ;

insert into employee values( 101, 'John', 1, 'computer', 20000.00, 'ANALYST ');
insert into employee values( 102, 'Marry', 2, 'economics', 55000.00,'ANALYST ');
insert into employee values( 103, 'Ram', 3, 'biology', 40000.00,'SALESMAN');
insert into employee values( 102, 'Geeta', 2, 'economics', 30000.00,'CLERK ');

create table department(dept_id int, dept_name char(20));

insert into department values ( 201, 'economics');
insert into department values ( 202, 'computer');
insert into department values ( 203, 'biology');

/* display names of all employees who work for manager 'Harry' */

select e.emp_name from employee as e
join manager as m on m.m_id = e.m_id
where m.m_name ='Harry';

/* display names of managers from all departments */

select m.m_name from manager as m
join department as d on d.dept_name = m.dept_name;

/* display information of all employees whose salary is between 10000 and 40000 */

select emp_id, emp_name, salary from employee as e
where salary BETWEEN 10000 AND 40000;

/* SQL to list the manager no, name and the name of employees working for those managers in ascending order on manager id.*/

SELECT m.m_id, m_name, e.emp_name
  
FROM manager m,employee e
WHERE m.m_id = e.m_id
ORDER BY m.m_id ASC;

/* list list emp number ,name ,job, department in which employee works and job type ='analyst' */

select e.emp_id, e.emp_name, e.dept_name, e.job_name
from employee as e
join manager as m on m.m_id=e.m_id
join department as d on d.dept_name= e.dept_name
where job_name = 'ANALYST';