1 Write four SQL queries to join two or more tables where ea

1. Write four SQL queries to join two or more tables where each query contains multiple combined single-row functions. Explain what each query is intended to do.

CREATE TABLE department
(
dep_Id int NOT NULL,
depart_Name varchar(255) NOT NULL,
manager varchar(255),
PRIMARY KEY (dep_ID_));

CREATE TABLE employee
(
emp_ID int NOT NULL,
dept_code int NOT NULL,
fname varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
DOB varchar(255) NOT NULL,
manager_id int NOT NULL,
hiredate varchar(255) NOT NULL,
classification varchar(255),
PRIMARY KEY (emp_Id));

CREATE TABLE projects
(
prj_Id int NOT NULL,

Project_name varchar(255) NOT NULL,

Start_date varchar(255) NOT NULL,

End_date varchar(255) NOT NULL,

Critiera_level varchar(255),

PRIMARY KEY (prj_ID));

CREATE TABLE salaried_emp

(

Emp_Id int NOT NULL,

Prj_ID int NOT NULL,

Yearly_salary varchar(255),

PRIMARY KEY (emp_Id),

CONSTRAINT FK_PROJECTS FOREIGN KEY (prj_Id) REFERENCES
projects (prj_Id));

CREATE TABLE hourly_emp
(

Emp_Id int NOT NULL,

Hourly_pay int,

PRIMARY KEY (emp_Id),

CONSTRAINT FK_employee FOREIGN KEY (emp_Id) REFERENCES

Employee (emp_Id));

Solution

1. SELECT s.Emp_Id, p.prj_Id FROM salaries_emp s JOIN projects p ON (s.prj_Id = p.prj_Id)

2. SELECT e.Emp_ID, h.Hourly_pay FROM hourly_emp h JOIN employee e ON (e.Emp_ID = h.Emp_ID)

3. SELECT d.depart_Name, e.Emp_ID FROM department d JOIN employee e ON (d.dep_Id = e,dept_code)

4. SELECT d.depart_Name, e.Emp_ID FROM department d JOIN employee e ON (d.dep_id = e.manager_id)