Question

Based on this code:

CREATE TABLE DEPARTMENT (

DepartmentName Char(35) NOT NULL,

BudgetCode Char(30) NOT NULL,

OfficeNumber Char(15) Not Null,

DepartmentPhone Char(12) NOT NULL,

CONSTRAINT DEPARTMENT_PK primary key(DepartmentName)

);

CREATE TABLE EMPLOYEE(

EmployeeNumber Int NOT NULL AUTO_INCREMENT,

FirstName Char(25) NOT NULL,

LastName Char(25) NOT NULL,

Department Char(35) NOT NULL DEFAULT 'Human Resources',

Position Char(35) NULL,

Supervisor Int NULL,

OfficePhone Char(12) NULL,

EmailAddress VarChar(100) NOT NULL UNIQUE,

CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeNumber),

CONSTRAINT EMP_DEPART_FK FOREIGN KEY(Department)

REFERENCES DEPARTMENT(DepartmentName)

ON UPDATE CASCADE,

CONSTRAINT EMP_SUPER_FK FOREIGN KEY(Supervisor)

REFERENCES EMPLOYEE(EmployeeNumber)

);

ALTER TABLE EMPLOYEE AUTO_INCREMENT=1;

CREATE TABLE PROJECT(

ProjectID Int NOT NULL AUTO_INCREMENT,

ProjectName Char(50) NOT NULL,

Department Char(35) NOT NULL,

MaxHours Numeric(8,2) NOT NULL DEFAULT 100,

StartDate Date NULL,

EndDate Date NULL,

CONSTRAINT PROJECT_PK PRIMARY KEY(ProjectID),

CONSTRAINT PROJ_DEPART_FK FOREIGN KEY(Department)

REFERENCES DEPARTMENT(DepartmentName)

ON UPDATE CASCADE

);

ALTER TABLE PROJECT AUTO_INCREMENT=1000;

CREATE TABLE ASSIGNMENT (

ProjectID Int NOT NULL,

EmployeeNumber Int NOT NULL,

HoursWorked Numeric(6,2) NULL,

CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(ProjectID, EmployeeNumber),

CONSTRAINT ASSIGN_PROJ_FK FOREIGN KEY(ProjectID)

REFERENCES PROJECT(ProjectID)

ON UPDATE NO ACTION

ON DELETE CASCADE,

CONSTRAINT ASSIGN_EMP_FK FOREIGN KEY(EmployeeNumber)

REFERENCES EMPLOYEE(EmployeeNumber)

ON UPDATE NO ACTION

How would I retrieve these commands?

Retrieve all the information for the assignments with hours worked less than 40.

Retrieve all the columns for employee(s) whose first name starts with M or J and ends with n

or e.

Retrieve all the columns for employee(s) whose first name is 5 letters.

Retrieve all the columns for employee(s) whose first name is 5 letters and en

ds with an a.

Retrieve all the columns for employee(s) whose first name is 5 letters and starts with a J and

ends with an a.

Answer 1

Retrieve all the information for the assignments with hours worked less than 40 :

select * from ASSIGNMENT where HoursWorked<40;

• This SQL query return all the columns from ASSIGNMENT table

**********************************

Retrieve all the columns for employee(s) whose first name starts with M or J and ends with n or e :

select * from EMPLOYEE where FirstName like 'M%N' or FirstName like 'J%N' and
FirstName like 'M%E' or FirstName like 'J%E';

• This SQL query is using Like clause and OR , AND operator to return the result

*******************************

Retrieve all the columns for employee(s) whose first name is 5 letters :

select * from EMPLOYEE where length(FirstName)=5;

• This SQL query is using length() function to get the result

********************************

Retrieve all the columns for employee(s) whose first name is 5 letters and ends with an a :

select * from EMPLOYEE where length(FirstName)=5 and FirstName like '%a';

• This SQL query is using length() function and like clause to get the result.

***********************************

Retrieve all the columns for employee(s) whose first name is 5 letters and starts with a J and ends with an a :

select * from EMPLOYEE where length(FirstName)=5 and FirstName like 'J%a';

• This SQL query is using length() function and like clause to return the result.