Question

SQL STATEMENTS: Create a database called CompanyDb. (Make sure to use it as well) Construct a table called Department. Department has the following columns: Id- int PK auto-increment Name - nvarchar 25 NOT NULL Hiring - bit Location - nvarchar 50 Add the two departments from above to the table. Construct a table named Employee. Employee has the following columns. SSN - nchar 9 PK DOB - DATE Phone - nchar 10 FirstName - nvarchar 30 NOT NULL LastName - nvarchar 30 NOT NULL DepartmentId- This is a foreign key reference to Departments. Choose the appropriate data type. (Note this is a many-to-one relationship: many employees to one department.) Add the 4 employees from above to the table. Update the youngest employee to have a LastName of ""Spiderman"". Select all employees with the LastName that starts with ""J"". Remove the oldest employee. Select all employees with a 313 area code. Select all employees that have a DOB before 9/9/1999. Update the employees with DOB after 2000 to have no phone number. Select all emp

Answer 1

To create the requested database and tables in SQL, use the CREATE DATABASE and CREATE TABLE statements with appropriate column definitions and constraints. To manipulate data, use UPDATE, SELECT, and DELETE statements with conditions based on employee details such as DOB and Phone.

Step-by-step explanation:

To answer the student's question about SQL statements, here's how to create and manipulate a database, tables, and records in SQL:

Create Database and Table:

To create a database named CompanyDb and switch to using it:

CREATE DATABASE CompanyDb;
USE CompanyDb;

To create a table named Department:

CREATE TABLE Department (
Id INT PRIMARY KEY IDENTITY,
Name NVARCHAR(25) NOT NULL,
Hiring BIT,
Location NVARCHAR(50)
);

For the Employee table, we need to reference the Department table with a foreign key:

CREATE TABLE Employee (
SSN NCHAR(9) PRIMARY KEY,
DOB DATE,
Phone NCHAR(10),
FirstName NVARCHAR(30) NOT NULL,
LastName NVARCHAR(30) NOT NULL,
DepartmentId INT,
FOREIGN KEY (DepartmentId) REFERENCES Department(Id)
);

Manipulate Data:

To update the last name of the youngest employee:

UPDATE Employee SET LastName = 'Spiderman' WHERE DOB = (SELECT MAX(DOB) FROM Employee);

To select all employees with a last name starting with 'J':

SELECT * FROM Employee WHERE LastName LIKE 'J%';

To remove the oldest employee:

DELETE FROM Employee WHERE DOB = (SELECT MIN(DOB) FROM Employee);

To select all employees with a 313 area code:

SELECT * FROM Employee WHERE Phone LIKE '313%';

To select employees with a DOB before 9/9/1999:

SELECT * FROM Employee WHERE DOB < '1999-09-09';

To update employees with DOBs after 2000 to remove their phone numbers:

UPDATE Employee SET Phone = NULL WHERE DOB > '2000-01-01';