ITECH 2060 Walden Week 3 Searching Data From Single and Multiple Tables HW In this Assignment, you will use SQL DML commands to find information about employees and departments of a company.
To create the data you need, first run the script schema.sql by following these steps:
Click the following link to download and save the script on your computer.
http://mym.cdn.laureate-media.com/2dett4d/Walden/ITEC/ITEC2060/documents/Schema.sql
When you download the file SCHEMA.SQL make sure it is saved as a .SQL file using Notepad, if you save it as a .TXT file, it will not work. You may also choose to rename the file (E.g. schemaweek3.sql).
The very last statement in the SCHEMA.SQL file is “EXIT”. This causes the Oracle SQL command editor to close. If you delete the command “EXIT” from the file prior to executing it, then you will not have to re-run the Oracle SQL plus command editor after loading the data.
You must include the path when you execute the @schema.sql command in the Oracle Command line editor. In the example shown below, the file is stored as schemaweek3.sql in the C:/ directory. To execute the file at the oracle command line type in @C:/schemaweek3.sql. If you don’t understand the concept of path in windows, you need to Google it. The screen shot below shows the Oracle response if you do not include the path. When the path is included and the file renamed schemaweek3.sql, Oracle will respond with “demonstration table build is complete”.
Now create, run, and test SQL queries to accomplish the tasks listed in the assignment. Note: Be careful to add a semicolon at the end of each statement before you press the Enter key.
Include a screen shot to verify the successful completion of each SQL command. Each Screen shot must be preceded by a comment that includes the student’s name, the instructor’s name, and the date the command was executed.
E.g. SQL>/* Student: John Doe Instructor: Sally Smith Date: 4/4/2021 */
Note: Don’t forget the /* and */ at the start and end of the command to make it into a comment!!
The screen shot must include the SQL command and the output from the command. Include one screen shot per item below. The screen shots must be well formatted and the output easily readable. Points may be subtracted from the total score for poor presentation of the assignment material.
See examples below:
Click on this link to load the Unit 3 Application assignment.
BY DAY 7
Submit your assignment to the online classroom.
SUBMISSION AND GRADING INFORMATION
To submit your completed Assignment for review and grading, do the following:
Please save your Assignment using the naming convention “UN3Assgn+last name+first initial.(extension)” as the name.
Click the Unit 3 Assignment Rubric to review the Grading Criteria for the Assignment.
Click the Unit 3 Assignment link. You will also be able to “View Rubric” for grading criteria from this area.
Next, from the Attach File area, click on the Browse My Computer button. Find the document you saved as “UN3Assgn+last name+first initial.(extension)” and click Open.
If applicable: From the Plagiarism Tools area, click the checkbox for I agree to submit my paper(s) to the Global Reference Database.
Click on the Submit button to complete your submission. — DESCRIPTION
-This script creates the SQL*Plus demonstration tables in the
-current schema. It should be STARTed by each user wishing to
-access the tables.
SET TERMOUT ON
PROMPT Building demonstration tables.
SET TERMOUT OFF
DROP
DROP
DROP
DROP
TABLE
TABLE
TABLE
TABLE
Please wait.
EMPLOYEES;
DEPARTMENTS;
BONUS;
SALGRADE;
CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER(2) NOT NULL,
EMPLOYEE_NAME VARCHAR2(20),
JOB_ID VARCHAR2(2),
JOB_DESCRIPTION VARCHAR2(20),
HOD NUMBER(2),
HIREDATE DATE,
SALARY NUMBER(10, 2),
DEPARTMENT_ID NUMBER(2));
INSERT INTO EMPLOYEES VALUES
(1, ‘SMITH’, ‘J1’, ‘CLERK’,
5,
TO_DATE(’17-DEC-2007′, ‘DD-MON-YYYY’), 5800, 20);
INSERT INTO EMPLOYEES VALUES
(2, ‘ALLEN’, ‘J2’, ‘SALESMAN’, 3,
TO_DATE(’20-FEB-2008′, ‘DD-MON-YYYY’), 7600, 30);
INSERT INTO EMPLOYEES VALUES
(3, ‘WARD’, ‘J2’, ‘SALESMAN’, 5,
TO_DATE(’22-FEB-2008′, ‘DD-MON-YYYY’), 8250, 30);
INSERT INTO EMPLOYEES VALUES
(4, ‘JONES’,’J3′, ‘MANAGER’,
7,
TO_DATE(‘2-APR-2008’, ‘DD-MON-YYYY’), 4900, 20);
INSERT INTO EMPLOYEES VALUES
(5, ‘JACK’, ‘J2′,’SALESMAN’, 7,
TO_DATE(’28-SEP-2008′, ‘DD-MON-YYYY’), 6700, 10);
INSERT INTO EMPLOYEES VALUES
(6, ‘BLAKE’, ‘J3’, ‘MANAGER’,
7,
TO_DATE(‘1-MAY-2008’, ‘DD-MON-YYYY’), 12850,30);
INSERT INTO EMPLOYEES VALUES
(7, ‘CLARK’, ‘J3’, ‘MANAGER’,
NULL,
TO_DATE(‘9-JUN-2008’, ‘DD-MON-YYYY’), 22450, 10);
INSERT INTO EMPLOYEES VALUES
(8, ‘SCOTT’, ‘J4’, ‘ANALYST’,
5,
TO_DATE(’09-DEC-2008′, ‘DD-MON-YYYY’), 13000,20);
INSERT INTO EMPLOYEES VALUES
(9, ‘TURNER’, ‘J2′,’SALESMAN’, 3,
TO_DATE(‘8-SEP-2007’, ‘DD-MON-YYYY’), 5500, 30);
INSERT INTO EMPLOYEES VALUES
(10, ‘ADAMS’, ‘J1′,’CLERK’,
5,
TO_DATE(’12-JAN-2007′, ‘DD-MON-YYYY’), 9100, 20);
CREATE TABLE DEPARTMENTS
(DEPARTMENT_ID NUMBER(2),
DEPARTMENT_NAME VARCHAR2(20),
LOCATION VARCHAR2(20) );
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
DEPARTMENTS
DEPARTMENTS
DEPARTMENTS
DEPARTMENTS
VALUES
VALUES
VALUES
VALUES
(10,
(20,
(30,
(40,
‘ACCOUNTING’, ‘NEW YORK’);
‘PRODUCTION’,’MINNESOTA’);
‘SALES’,
‘CHICAGO’);
‘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
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
SALGRADE
SALGRADE
SALGRADE
SALGRADE
SALGRADE
VALUES
VALUES
VALUES
VALUES
VALUES
(1,
(2,
(3,
(4,
(5,
3000, 8000);
8001, 14000);
14001, 20000);
20001, 25000);
25001, 30000);
COMMIT;
SET TERMOUT ON
PROMPT Demonstration table build is complete.
EXIT
Purchase answer to see full
attachment