Lab (50 points) Preliminary \" Login into Linux machine (oraclelinux.eng.fau.edu

Question

Lab (50 points) Preliminary " Login into Linux machine (oraclelinux.eng.fau.edu) Connect to database (e.g., sqlplus username/password) ALTER SESSION SET CURRENT_SCHEMA ksyang; * Execute the following SQL and identify all required tables. SELECT table name from all tables where owner = 'KSYANG'; - Change line size: SET LINESIZE 400 Use Spool command to create log file for the output of SQL (e.g., SPOOL filename and SPOOL OFF) Consider the following relational schema " employee fname CHARACTER VARYING(15) minit CHARACTER VARYING(1) Iname CHARACTER VARYING(15) essn CHARACTERD) bdate DATE addiess CHARACTER VARYING(OD) dependent works on CHARACTERI) dependent name CHARACTER VARYING(15) essn CHARACTERp) pno NUMERIC hours NUMERIC CHARACTER(1) bdate CHARACTERC1) dept locations relationship CHARACTER VARYING(B) salary NUMERIC supet sn CHARACTERIP) dno dno NUMERIC dlocstion CHARACTER VARYING(15) NUMERIC department CHARACTER VARYINO(25) NUMERIC CHARACTERO) project mgisn pname CHARACTER VARYINO(25) pno plocation CHARACTER VARYING(15) dno NUMERIC ngstartdate DATE NUMERIC Write SQL statements for the following queries 1) (10 points) Retrieve essns of employees who worked more than 45 hours on projects 2) (10 points) Retrieve fnames of employees who have no working experience 3) (10 points) Retrieve fnames of employees who have the highest salary 4) (10 points) Retrieve the minimum, maximum and average salaries of employees whose department is Research'. 5) (10 points) Retrieve fnames of employees who have more than two dependents

Explanation / Answer

Retrieve essns of employees who worked more than 45 hours on projects

SELECT essn
FROM (SELECT essn,
Sum(hours)
FROM works_on
GROUP BY essn
HAVING Sum(hours) > 45)

Retrieve fnames of employesss who have no working experience

SELECT fname
FROM employee
WHERE essn NOT IN
(
SELECT DISTINCT(essn)
FROM works_on )

Retrieve fnames of employesss who have highest salary

SELECT fname
FROM employee
WHERE salary IN (SELECT Max(salary)
FROM employee)

Retrieve minumum, maximum and averaged salaries of employesss whose
departmenst is 'Research'

SELECT Min(salary),
Max(salary),
Avg(salary),
FROM employee
WHERE dno IN (SELECT dno
FROM department
WHERE dname = 'Research')


Retrieve fnames of employesss who have more than 2 dependents

SELECT fname
FROM employee
WHERE essn IN (SELECT essn
FROM dependent
GROUP BY essn
HAVING Count(essn) >= 2)

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

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