1)Q1 - Write a query to display the department name, location name, number of em

Question

1)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

CREATE TABLE rental (
   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)
);

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.

INSERT INTO equipment VALUES(1, small screwdriver, 10);
INSERT INTO rented VALUES(1, '2018-06-27', 1, 10, 'Pending');

Q5. Now create a query, which will show the equipment rented out with status. You

Q6. Create a tablespace that will have an initial size of 10MB, never runs out of space (as in if a disk is 1 Petabyte) it will use it all without ever having to change the tablespace configuration.

I NEED HELP WITH 5 AND 6

Explanation / Answer

If you have any doubts, please give me comment...

--5)

SELECT equipmentDesc, R.status

FROM equipment E, rented R

WHERE E.equipmentId = R.equipmentId;

--6)

CREATE TABLESPACE tbs_perm_02

DATAFILE 'tbs.dat'

SIZE 10M

AUTOEXTEND ON;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.