Question
Given the following code in Oracle SQL Developer , please help me answer the question below : The relational schemas are: Dept (DEPTNO, DNAME, LOC)
Given the following code in Oracle SQL Developer, please help me answer the question below:
The relational schemas are:
- Dept(DEPTNO, DNAME, LOC)
pk = {DEPTNO}
- Emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
pk = {EMPNO}
fk = {DEPTNO} references Dept(DEPTNO)
- (Join) Use SELECT statements to execute. Display the employee number, employees name, department number, department name, and hire date information. The hire date should include only the date component of the hire date. The hire date should be formatted as follows: Month DD, YYYY
----------------------------------------------------------------------------------------------------------------------------
set termout on prompt Building demonstration tables. Please wait. set termout off set feedback off
ALTER SESSION SET NLS_LANGUAGE = AMERICAN; ALTER SESSION SET NLS_TERRITORY = AMERICA;
DROP TABLE EMP cascade constraint; DROP TABLE DEPT cascade constraint; DROP TABLE BONUS cascade constraint; DROP TABLE SALGRADE cascade constraint; DROP TABLE DUMMY cascade constraint;
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1111,'SMITH','CLERK',7902,SYSDATE,800,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,SYSDATE,1600,300,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,SYSDATE,1250,500,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,SYSDATE,2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,SYSDATE,1250,1400,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,SYSDATE,950,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE BONUS ( ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER, COMM NUMBER );
CREATE TABLE SALGRADE ( GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999);
CREATE TABLE DUMMY ( DUMMY NUMBER );
INSERT INTO DUMMY VALUES (0);
COMMIT;
SET TERMOUT ON PROMPT Demonstration table build is complete.
set feedback ON
--------------------------------------------------------------------------------------------------
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started