Q1 - Write a query to display the department name, location name, number of empl

Question

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.

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.

Explanation / Answer

-- 1)

SELECT d.dname AS 'department name',

d.loc AS 'department location',

COUNT(*) AS 'Number of People',

ROUND(AVG(sal),2) AS 'Salary'

FROM dept d INNER JOIN emp e ON d.deptno = e.deptno

GROUP BY d.dname, d.loc;

--2)

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;

--3)

CREATE TABLE equipment (

equipmentId int NOT NULL,

equipmentDesc varchar(255),

equipmentPrice int NOT NULL,

PRIMARY KEY(equipmentId, equipmentPrice)

);

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)

);

--4)

INSERT INTO equipment VALUES(1, 'equipment name', 300);

INSERT INTO rented VALUES(1, '2018-06-27', 1, 280, 'Pending');

--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;

Let me know if you have any clarifications. Thank you...

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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