Q1 - Write a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively.

SELECT d.dname AS department name ,

d.loc AS department location,

COUNT(*) "Number of People",

ROUND(AVG(sal),2) "Salary"

FROM dept d INNER JOIN emp e ON (d.deptno = e.deptno) GROUP BY d.dname, d.loc;

Q2 - Display the employees name, username, hire date, salary and salary review date, which is the first Monday after six months of service. Label the column REVIEW DATE. Format the dates to appear in the format mm/dd/yyyy. Salary should be rounded. Username is first two letters of the name in the lower case concatenated with their hiredate in MMDDYY format.

SELECT ename AS "Name",

LOWER(SUBSTR(TRIM(ename), 1, 2)) AS "Username",

TO_CHAR(hiredate, 'MM/DD/YY') AS "Hiredate",ROUND(sal, 0) AS

"Salary",

TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate, 6), 'MONDAY'), 'MM/DD/YY') AS "REVIEW DATE" FROM emp;

Q3. a. Create a equipment table with the following information: (Create a composite key) equipmentId - primary key equipmentDesc equipmentPrice - primary key

CREATE TABLE equipment ( equipmentId int NOT NULL, equipmentDesc varchar(255), equipmentPrice int NOT NULL,, PRIMARY KEY (equipmentId, equipmentPrice) );

b. Create an rental table with the following fields: Rentalid- primary key RentalDate - primary key equipmentId - foreign key equipmentPrice - foreign key Status

Rentalid int NOT NULL, RentalDate datetime, equipmentId int, equipmentPrice int, Status varchar(255), PRIMARY KEY (Rentalid, RentalDate), FOREIGN KEY (equipmentId) REFERENCES equipment(equipmentId) FOREIGN KEY (equipmentPrice) REFERENCES equipment(equipmentPrice) );

c. Use appropriate data types and CONSTRAINTS, while creating tables.

Q4. Insert 1 item in equipment table and then insert a row to show that it is rented out. You can choose the values as needed.