Queries (note the first one did not need to be edited, the rest did) : #1 SOL> select empno, ename, job, mgr, sal, comm, deptno 2 from empcopy 3 where deptno = 10 and sal > 1500; #2 SOL> edit Wrote file afiedt.buf 1 select empno, ename, job, mgr, sal, comm, deptno 2 from empcopy 3* where mgr > 7782 or job - 'CLERK' SOL> SOL> / #3 SQL> edit Wrote file afiedt.buf 1 select empno, ename, job, sal, comm, deptno 2 from empcopy 3* where deptno - 20 and (comm is null or sal > 1500) SQL> / #4 SOL> edit Wrote file afiedt.buf 1 select empno, ename, job, sal, comm, deptno from empcopy 3* where deptno - 20 and comm is null or sal > 1500 SOL> / 2
Queries (note the first one did not need to be edited, the rest did) : #1 SOL> select empno, ename, job, mgr, sal, comm, deptno 2 from empcopy 3 where deptno = 10 and sal > 1500; #2 SOL> edit Wrote file afiedt.buf 1 select empno, ename, job, mgr, sal, comm, deptno 2 from empcopy 3* where mgr > 7782 or job - 'CLERK' SOL> SOL> / #3 SQL> edit Wrote file afiedt.buf 1 select empno, ename, job, sal, comm, deptno 2 from empcopy 3* where deptno - 20 and (comm is null or sal > 1500) SQL> / #4 SOL> edit Wrote file afiedt.buf 1 select empno, ename, job, sal, comm, deptno from empcopy 3* where deptno - 20 and comm is null or sal > 1500 SOL> / 2
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
Please answer these 4 questions!
Transcribed Image Text:### SQL Table Description and Data
#### Table Structure: `SURGRAV`
The table `SURGRAV` has the following structure:
- **EMPNO**: NUMBER(4)
- **ENAME**: VARCHAR2(10)
- **JOB**: VARCHAR2(9)
- **MGR**: NUMBER(4)
- **HIREDATE**: DATE
- **SAL**: NUMBER(7,2)
- **COMM**: NUMBER(7,2)
- **DEPTNO**: NUMBER(2)
These columns define the data types and constraints for each attribute in the table.
#### Example Data
The following data is part of the `SURGRAV` table, displaying employee details:
1. **EMPLOYEES IN DEPARTMENT 20**
- `7369 SMITH`: Clerk, Manager 7902, Hired 17-DEC-80, Salary 800, No Commission.
- `7566 JONES`: Manager, No Manager Details, Hired 02-APR-81, Salary 2975, No Commission.
- `7902 FORD`: Analyst, Manager 7566, Hired 03-DEC-81, Salary 3000, No Commission.
2. **EMPLOYEES IN DEPARTMENT 30**
- `7499 ALLEN`: Salesman, Manager 7698, Hired 20-FEB-81, Salary 1600, Commission 300.
- `7521 WARD`: Salesman, Manager 7698, Hired 22-FEB-81, Salary 1250, Commission 500.
- `7698 BLAKE`: Manager, No Manager Details, Hired 01-MAY-81, Salary 2850, No Commission.
- `7844 TURNER`: Salesman, Manager 7698, Hired 08-SEP-81, Salary 1500, Commission 0.
- `7900 JAMES`: Clerk, Manager 7698, Hired 03-DEC-81, Salary 950, No Commission.
3. **EMPLOYEES IN DEPARTMENT 10**
- `7782 CLARK`: Manager, No Manager Details, Hired 09-JUN-81, Salary 2450, No Commission.
- `7839 KING`: President, No Manager Details, Hired 17-NOV-81, Salary
Transcribed Image Text:The image contains a series of SQL queries, each with slight modifications, demonstrating how to manipulate data retrieval from a database table named `empcopy`. Below is a transcription and explanation of each query section.
---
**Queries (note the first one did not need to be edited, the rest did):**
**#1**
```
SQL> select empno, ename, job, mgr, sal, comm, deptno
2 from empcopy
3* where deptno = 10 and sal > 1500;
```
- **Explanation**: This query selects the employee number (`empno`), name (`ename`), job title (`job`), manager ID (`mgr`), salary (`sal`), commission (`comm`), and department number (`deptno`) from the `empcopy` table. It filters the results to include only those employees in department 10 whose salary is greater than 1500.
**#2**
```
SQL> edit
Wrote file afiedt.buf
1 select empno, ename, job, mgr, sal, comm, deptno
2 from empcopy
3* where mgr > 7782 or job = 'CLERK'
SQL> /
```
- **Explanation**: This edited query again selects the same columns but applies a different filter condition. It retrieves employees who have a manager ID greater than 7782 or whose job title is 'CLERK'.
**#3**
```
SQL> edit
Wrote file afiedt.buf
1 select empno, ename, job, sal, comm, deptno
2 from empcopy
3* where deptno = 20 and (comm is null or sal > 1500)
SQL> /
```
- **Explanation**: This query variation selects similar columns but omits `mgr`. It targets employees in department 20, where the commission is either null or the salary is greater than 1500.
**#4**
```
SQL> edit
Wrote file afiedt.buf
1 select empno, ename, job, sal, comm, deptno
2 from empcopy
3* where deptno = 20 and comm is null or sal > 1500
SQL> /
```
-
Expert Solution
Step 1
Below are the answers for all the queries:
Step by step
Solved in 5 steps
Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education