Write an SQL script, using appropriate syntax and formatting, that creates a relational database named "Company" with three tables: "Employees", "Departments", and "Projects". Each table should contain relevant attributes with appropriate data types and constraints. Write queries that join the tables to output the following information: total number of employees in each department, the names and salaries of employees who are assigned to a specific project, and the total budget of each department's projects.
Write an SQL script, using appropriate syntax and formatting, that creates a relational database named "Company" with three tables: "Employees", "Departments", and "Projects". Each table should contain relevant attributes with appropriate data types and constraints. Write queries that join the tables to output the following information: total number of employees in each department, the names and salaries of employees who are assigned to a specific project, and the total budget of each department's projects.
user
CREATE DATABASE Company;

USE Company;

CREATE TABLE Employees (
    employee_id INT NOT NULL,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10,2),
    department_id INT,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (department_id) REFERENCES Departments (department_id)
);

CREATE TABLE Departments (
    department_id INT NOT NULL,
    name VARCHAR(50),
    location VARCHAR(50),
    PRIMARY KEY (department_id)
);

CREATE TABLE Projects (
    project_id INT NOT NULL,
    name VARCHAR(50),
    budget DECIMAL(10,2),
    department_id INT,
    PRIMARY KEY (project_id),
    FOREIGN KEY (department_id) REFERENCES Departments (department_id)
);

-- Query 1: Total number of employees in each department
SELECT Departments.name, COUNT(Employees.employee_id) AS total_employees
FROM Departments
LEFT JOIN Employees ON Departments.department_id = Employees.department_id
GROUP BY Departments.name;

-- Query 2: Names and salaries of employees who are assigned to a specific project
SELECT Employees.name, Employees.salary
FROM Employees
JOIN Projects ON Employees.department_id = Projects.department_id
WHERE Projects.name = 'Project X';

-- Query 3: Total budget of each department's projects
SELECT Departments.name, SUM(Projects.budget) AS total_budget
FROM Departments
JOIN Projects ON Departments.department_id = Projects.department_id
GROUP BY Departments.name;
assistant
Can you add a table to track the project deadlines? It would be helpful to see which projects are due soon and which ones have more time.
user
Sure! Here is an updated SQL script with the new "Deadlines" table and a join query to retrieve the project deadlines:

```
CREATE DATABASE Company;

USE Company;

CREATE TABLE Employees (
    employee_id INT NOT NULL,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10,2),
    department_id INT,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (department_id) REFERENCES Departments (department_id)
);

CREATE TABLE Departments (
    department_id INT NOT NULL,
    name VARCHAR(50),
    location VARCHAR(50),
    PRIMARY KEY (department_id)
);

CREATE TABLE Projects (
    project_id INT NOT NULL,
    name VARCHAR(50),
    budget DECIMAL(10,2),
    department_id INT,
    PRIMARY KEY (project_id),
    FOREIGN KEY (department_id) REFERENCES Departments (department_id)
);

CREATE TABLE Deadlines (
    deadline_id INT NOT NULL,
    project_id INT,
    due_date DATE,
    PRIMARY KEY (deadline_id),
    FOREIGN KEY (project_id) REFERENCES Projects (project_id)
);

-- Query 1: Total number of employees in each department
SELECT Departments.name, COUNT(Employees.employee_id) AS total_employees
FROM Departments
LEFT JOIN Employees ON Departments.department_id = Employees.department_id
GROUP BY Departments.name;

-- Query 2: Names and salaries of employees who are assigned to a specific project
SELECT Employees.name, Employees.salary
FROM Employees
JOIN Projects ON Employees.department_id = Projects.department_id
WHERE Projects.name = 'Project X';

-- Query 3: Total budget of each department's projects
SELECT Departments.name, SUM(Projects.budget) AS total_budget
FROM Departments
JOIN Projects ON Departments.department_id = Projects.department_id
GROUP BY Departments.name;

-- Query 4: Project deadlines
SELECT Projects.name, Deadlines.due_date
FROM Projects
JOIN Deadlines ON Projects.project_id = Deadlines.project_id
ORDER BY Deadlines.due_date;
```

Note that the "Deadlines" table has a foreign key constraint to the "Projects" table on the "project_id" column. This ensures that only valid project ids can be inserted into the "Deadlines" table. 

Also note that the final join query retrieves project names and their due dates from the "Projects" and "Deadlines" tables, respectively. The results are ordered by the due dates in ascending order, which shows which projects are due soonest.
assistant
This looks great! Can you also add a column to the "Employees" table for job title? It would be useful to see what positions everyone holds in the company.
user
Sure! Here is the updated SQL script with the new "job_title" column added to the "Employees" table:

```
CREATE DATABASE Company;

USE Company;

CREATE TABLE Employees (
    employee_id INT NOT NULL,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10,2),
    job_title VARCHAR(50),
    department_id INT,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (department_id) REFERENCES Departments (department_id)
);

CREATE TABLE Departments (
    department_id INT NOT NULL,
    name VARCHAR(50),
    location VARCHAR(50),
    PRIMARY KEY (department_id)
);

CREATE TABLE Projects (
    project_id INT NOT NULL,
    name VARCHAR(50),
    budget DECIMAL(10,2),
    department_id INT,
    PRIMARY KEY (project_id),
    FOREIGN KEY (department_id) REFERENCES Departments (department_id)
);

CREATE TABLE Deadlines (
    deadline_id INT NOT NULL,
    project_id INT,
    due_date DATE,
    PRIMARY KEY (deadline_id),
    FOREIGN KEY (project_id) REFERENCES Projects (project_id)
);

-- Query 1: Total number of employees in each department
SELECT Departments.name, COUNT(Employees.employee_id) AS total_employees
FROM Departments
LEFT JOIN Employees ON Departments.department_id = Employees.department_id
GROUP BY Departments.name;

-- Query 2: Names and salaries of employees who are assigned to a specific project
SELECT Employees.name, Employees.salary
FROM Employees
JOIN Projects ON Employees.department_id = Projects.department_id
WHERE Projects.name = 'Project X';

-- Query 3: Total budget of each department's projects
SELECT Departments.name, SUM(Projects.budget) AS total_budget
FROM Departments
JOIN Projects ON Departments.department_id = Projects.department_id
GROUP BY Departments.name;

-- Query 4: Project deadlines
SELECT Projects.name, Deadlines.due_date
FROM Projects
JOIN Deadlines ON Projects.project_id = Deadlines.project_id
ORDER BY Deadlines.due_date;
```

Note that the "Employees" table now has a new "job_title" column, which has a VARCHAR(50) data type to store job titles with up to 50 characters. The other queries remain the same as before.
assistant