MIS/562 MIS 562 MIS562 Week 2 Individual Assignment – SQL File + Screenshots – A+ Guaranteed!

MIS/562 MIS 562 MIS562

IS/562 IS 562 IS562

DeVry University

Use as a guide only!

NOTE: Thanks for purchasing this! Please open the files when you buy the assignment! Please let me know if you have any questions, comments, or concerns. You can find my contact info at my profile:

http://www.homeworkmarket.com/users/algebraexpert

Thanks! 

AE

Name:  _______________________

Homework (100 points) 

Preliminary Tasks

From Doc Sharing download the file scripts.exe. This self-extracting zip file contains the sample database files you will need to complete your homework assignments.

Take the following steps to download and extract the file:

1. Download the schemasetup.zip to the C:Temp folder on your machine.
2. Open Windows Explorer and Unzip button to extract the files to C:temp.
3. The readme.pdf in the c:temp directory will provide instructions on how to install (viewing requires the Adobe Acrobat Reader). Please read the instructions carefully. Alternatively, you can also read the text-based version of the readme file called readme.txt.  When you are logged into the ILab using SQL Plus, the Citrix software uses the V logical drive as you C drive on your computer.  So you have to execute the script as:

@V:tempcreateStudent.sql

If you are using Oracle through iLab, use the logon account you were assigned at the beginning of the course.  Do not try to create an account (step 1).  Permissions have not been granted for that step. Start at Step II. STEPS TO BUILD STUDENT SCHEMA OBJECTS AND LOAD ASSOCIATED DATA. When you are logged into the ILab using SQL Plus, the Citrix software uses the V logical drive as your C drive on your computer.  So you have to execute the script as:

@V:tempcreateStudent.sql

You may also find it convenient to print the STUDENT Database Schema Diagram (requires the Adobe Acrobat Reader).

Submission Requirements

Grading assignments can be a long and lengthy task when the output is not orderly and neat.

The problem with not following the submission requirements is extra time required for research, detective work, mistakes in grading, executing queries etc.   Accordingly the following requirement should be followed for submitting Assignments.

·         The original problem statements are to be repeated.  (With numbers and in order)  Penalty – half the point value of the problem statement

·         After the problem statement any student comments can optionally be included. (Example number two and the way the text book inserts comments) Number a penalty

·         The SQL statement as you typed it at the prompt. ( Submit only one, some people include all of their attempts along with errors and retries – that is not acceptable) Penalty for submitting more than one – one quarter the point value of the problem.  Penalty for not submitting any, 0 points

·         The result table, in a neat, Readable format. In the case of a very large result table no more than the first 20 rows, with the row number count at the bottom.  Penalty -if there is no result table -the full value of the problem is assessed.  If there is a table but it is not neat and readable – one half the point value of the problem.  If there are more than 20 rows or there is no row count – one quarter of the point value is subtracted.

·          All of the homework submission is to be included in one document and that document is to be sent via the appropriate drop box. ( .txt from notepad is highly recommended).  No penalty, the submission will be returned asking that everything be put into one document.  I did not used to have this one, so people started driving me nuts and I had to make this rule.

Please bear in mind that I hate taking points off because of these issues.  I will make every effort in week 1 thread 2 to make certain that everybody can comply with these requirements quickly and easily and painlessly.  Failure to adhere to these requirements can result to up to 10% off the total score (one letter grade).

The neatness formatting requirements are to demonstrate mastery of the SQL*Plus environment from chapter 2 and the SQL*Plus Guide.   Notepad usually works better than WORD in retaining neatness.

EXAMPLE

Homework  week 2                                                          (Your name)

1.    List all employee information in department 30.

      SELECT * FROM emp WHERE deptno = 30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

———- ———- ——— ———- ——— ———- ———- ———-

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6        rows selected.

2.       List all employees who report to Blake.

n  If a problem is encountered “I could not get this one to work?? I tried the following without getting any results?”.   BUT, it would be better to get assistance in the graded threads.

SELECT ename FROM emp where MGR LIKE ‘Blake’;

3.        List all employee information in department 30.

      Note:    What if this query returned more than 20 rows.

SELECT * FROM emp WHERE deptno = 30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

———- ———- ——— ———- ——— ———- ———- ———-

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7900 JAMES      CLERK           7698 03-DEC-81        950                   30

……..

7777  rows selected.

 All formatting requirements can be easily accomplished by following the instructions in chapter 2; using the set line size and the set page size commands along with the Column Format commands from Appendix C.   These SQL*Plus commands should be kept in a script file and run every time a session is started. Problems and questions can be taken to the threaded discussions.

Part 1

Create the tables from demo.sql script

Download and

Copy the demo.sql file from the Doc Share to the C:temp directory.   At the sql prompt enter the command @V:tempdemo.sql.  This will create some tables and insert data into them.  View the script in notepad to determine the table names that were created.  Use the describe command to view the structure of the tables. Please use the template below to provide your solutions.

Write SQL statements to solve the following requests. 

Part 2

Using the two tables created in Part 1:

EMPNO   ENAME      JOB                MGR      HIREDATE     SAL    COMM  DEPTNO

———-     ———         ———              ——        ————–       —–      ——–    ————

7369        SMITH        CLERK            7902       17-DEC-80     800                 20

7499        ALLEN        SALESMAN     7698       20-FEB-81      1600    300       30

7521        WARD        SALESMAN     7698       22-FEB-81      1250    500       30

7566        JONES       MANAGER      7839       02-APR-81     2975                20

7654        MARTIN      SALESMAN     7698       28-SEP-81     1250    1400     30

7698        BLAKE       MANAGER      7839       01-MAY-81     2850                30

7782        CLARK       MANAGER      7839       09-JUN-81      2450                10

7788        SCOTT        ANALYST        7566       09-DEC-82     3000                20

7839        KING          PRESIDENT                  17-NOV-81     5000                10

7844        TURNER     SALESMAN     7698       08-SEP-81     1500                30

7876        ADAMS      CLERK            7788       12-JAN-83      1100                20

7900        JAMES       CLERK            7698       03-DEC-81     950                 30

7902        FORD         ANALYST        7566       03-DEC-81     3000                20

7934        MILLER       CLERK            7782       23-JAN-82      1300                10

9999        KIRK           CAPTAIN                      14-APR-02     6000                50

DEPTNO     DNAME              LOC

———-        ————–            ————-

10               ACCOUNTING     NEWYORK

20               RESEARCH        DALLAS

30               SALES               CHICAGO

40               OPERATIONS     BOSTON

60               STARFLEET       SAN FRANCISCO

Execute the script demoaddrows.sql to add rows into the two tables above. Write SQL statements to solve the following requests. 

Using the ZIPCODES table from the text schema, write a SQL query that displays the following:

Using the text schema, write a SQL query that displays the following: