MIS562Assignment Week 2 Individual
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:
Download the schemasetup.zip to the C:\Temp folder on your machine.
Open Windows Explorer and Unzip button to extract the files to C:\temp.
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:\temp\createStudent.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:\temp\createStudent.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)
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
rows selected.
List all employees who report to Blake.
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’;
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
……..
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:\temp\demo.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.
Question (4 pts per question)
SQL statement or answer
List all employee information in department 30.
List employees name, job, and salary that is a manager and has a salary > $1,000
Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
Show all employee names and salary that earn between $1,000 and $2,000. Use the between operator.
Select all employees that are in department 10 and 30. Use the IN operator.
Select all employee names with an “A” in the first position of the employee name. Use the substring function or a wild card.
Select all employees with an “A” as the second character of their name. Use a wildcard.
List the employee names in alphabetical sequence.
List the job, salary, and employee name in job order and then salary in descending order.
Show a list of different jobs. Eliminate repeating values.
Show employee names, salary that has not earned a commission yet.
Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
Write an SQL query that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions do not start with ‘Intro’, sorted by their prerequisites in descending order.
Write an SQL query that retrieves data from the STUDENT table for students whose last names begin with “A” though “T” and who work for ‘Competrol Real Estate’, sorted by the last names.
Determine which student does not have the first letter the last name capitalized. Show the STUDENT_ID and LAST_NAME columns.
Check if any of the phone numbers in the INSTRUCTOR table have not been entered in the (###)###-#### format. Show the instructor last name and the phone number that is in the incorrect format.
Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.
Write a SQL statement that converts a date datatype to a char datatype.
Write a SQL statement that convert a number value to a character.
Part 2
Execute the script demoaddrows.sql to add rows into the two tables above. Write SQL statements to solve the following requests.
Question ( 2 points per question)
SQL statement or answer
Show a list of different jobs. Eliminate repeating values.
How many employees are working at each job in each department and what the sums and averages are for the salary of those employees?
Show the employee name with the maximum salary
Show the average salary for all employees that are not managers.
What is the difference between the highest and lowest salary?
Select employee number, name for all employees with salaries above the average salary. Use a subquery.
Using the ZIPCODES table from the text schema, write a SQL query that displays the following:
Question ( 2 points per question)
SQL statement or answer
Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence. Use the ZIPCODES table.
Which city has the most zip codes assigned to it? Use a TOP-N query.
Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence and exclude all states having less than 5 zip codes.
Using the text schema, write a SQL query that displays the following:
Question ( 2 points per question)
SQL statement or answer
Show the Student_ID, last name, and the number of enrollments for the student. Show only students with more than 2 enrollments. Use an Inline view.
Show the number of students enrolled and zip code for New York and where the city begins with 'W'. Use a Scalar subquery.
Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.
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:
Download the schemasetup.zip to the C:\Temp folder on your machine.
Open Windows Explorer and Unzip button to extract the files to C:\temp.
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:\temp\createStudent.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:\temp\createStudent.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)
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
rows selected.
List all employees who report to Blake.
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’;
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
……..
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:\temp\demo.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.
Question (4 pts per question)
SQL statement or answer
List all employee information in department 30.
List employees name, job, and salary that is a manager and has a salary > $1,000
Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
Show all employee names and salary that earn between $1,000 and $2,000. Use the between operator.
Select all employees that are in department 10 and 30. Use the IN operator.
Select all employee names with an “A” in the first position of the employee name. Use the substring function or a wild card.
Select all employees with an “A” as the second character of their name. Use a wildcard.
List the employee names in alphabetical sequence.
List the job, salary, and employee name in job order and then salary in descending order.
Show a list of different jobs. Eliminate repeating values.
Show employee names, salary that has not earned a commission yet.
Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
Write an SQL query that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions do not start with ‘Intro’, sorted by their prerequisites in descending order.
Write an SQL query that retrieves data from the STUDENT table for students whose last names begin with “A” though “T” and who work for ‘Competrol Real Estate’, sorted by the last names.
Determine which student does not have the first letter the last name capitalized. Show the STUDENT_ID and LAST_NAME columns.
Check if any of the phone numbers in the INSTRUCTOR table have not been entered in the (###)###-#### format. Show the instructor last name and the phone number that is in the incorrect format.
Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.
Write a SQL statement that converts a date datatype to a char datatype.
Write a SQL statement that convert a number value to a character.
Part 2
Execute the script demoaddrows.sql to add rows into the two tables above. Write SQL statements to solve the following requests.
Question ( 2 points per question)
SQL statement or answer
Show a list of different jobs. Eliminate repeating values.
How many employees are working at each job in each department and what the sums and averages are for the salary of those employees?
Show the employee name with the maximum salary
Show the average salary for all employees that are not managers.
What is the difference between the highest and lowest salary?
Select employee number, name for all employees with salaries above the average salary. Use a subquery.
Using the ZIPCODES table from the text schema, write a SQL query that displays the following:
Question ( 2 points per question)
SQL statement or answer
Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence. Use the ZIPCODES table.
Which city has the most zip codes assigned to it? Use a TOP-N query.
Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence and exclude all states having less than 5 zip codes.
Using the text schema, write a SQL query that displays the following:
Question ( 2 points per question)
SQL statement or answer
Show the Student_ID, last name, and the number of enrollments for the student. Show only students with more than 2 enrollments. Use an Inline view.
Show the number of students enrolled and zip code for New York and where the city begins with 'W'. Use a Scalar subquery.
Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.
No comments:
Post a Comment