Search results for 'SELECT'

IT452 Unit 10 Final Project
In this project, you will create an end-to-end solution to meet a business need. This project is worth 200 points.

Scenario
Your good friend, Misty, has a tutoring business in Southern California. It is quite successful. A client will call with a tutoring need, and Misty will look on her Excel spread sheet of tutors for those qualified in the tutoring area the client needs, and who live near the client.
Misty now has approximately 20 freelance tutors registered with her. It has become quite difficult for her to tell what tutors are qualified and live near the client. You have told her you will develop a way for her to get such a list.
She has sent you the TutorList.xls file with the current list of tutors. (This file is available in Doc Sharing.) The Subject column has values of A (tutor is proficient in English and history), B (tutor is proficient in math and science), or C (tutor is proficient in all basic subjects). Hint: You need to create a database that consists of the data in these to files. The DateQualified column has the latest date the tutor was interviewed and checked out by Misty. In addition, she has provided the current term’s client list (Clients.xls). You will need to use BULK INSERT to load the data from the sheets into the tutoring database and write queries that find tutors in the same zip code as the client and provide the tutor in the same zip code in a report. Misty should be able to select the zip code as a parameter from the tutors in the report and the zip code as a parameter from the clients as a parameter to execute the reports.
Misty also requires that she should be able to interact with a Web page, because she does not want to know how to "run database software". This will require creating a web based report that she can access via a web link.
The first thing you should do is list the steps in your plan. What will be the major steps you need to take to accomplish your goal? The first step will be to install SQL Server 2008 Express with Advanced Services on Misty's computer. (She has agreed to that part of the plan.) The last step will be to create a report that asks for parameter values for a stored procedure or query at run time. This does not literally need to be done but should be a step in the plan if you advise her that it is needed on her machine.
For the purpose of this project, you will use your computer to work out the steps and create the report.
The first item to place in your final project document is your outline of major steps that need to be accomplished. Label this section:

1. Outline – 20 points
<Put your plan step list here.> (The outline will be worth 20 points.)
Then, for each step after the first (the installation step), make a section in your project, e.g.

2. <Whatever Step 2 is in your plan outline> - 80 points
In that section, state how you accomplished that step and/or give the code you used. Enough detail should be given so a knowledgeable person could completely recreate what you did. Show at least some of the results you obtained when you tested to see whether your step had been accomplished. (For
queries, 3 or 4 lines of output, and stating the number of lines returned is sufficient.) If a screen shot is the best way to demonstrate the results, include it in your project. Please use ALT+PrintScreen so that only the in-focus or active window, not the entire desktop, is copied.
[Note: You are starting with step 2, because step 1 of your plan is the installation, and it will be assumed that you know how to do that.]
[Hint: One of your steps should be the creation of a stored procedure that uses a variable for the zip code and determines which tutors live in the same zip code as the client. But, this step will be carried out in #3 below so you might want to letter your steps to avoid confusion.

3. Create the query or stored proc. that will produce the result set for the report - 40 points (Creation and Alter)
Create Procedure – 20 points
Alter Procedure – 20 points
The query / stored procedure that generates the result set for the report will be worth 20 points each. You then will also Alter the Stored Procedure one time and execute it. You should determine that it gives the desired results before configuring your report. Hint: The SELECT query portion of the stored procedure will be the query you use in your report datasource when you get to the portion of the design wizard (if you use the wizard instead of manually creating everything).

4. [The last step.] Configuring the Report and the Report Parameters – 60 points
Query for Report 20 points
Configuring Parameters – 20 points
Configuring/Designing 2 reports – 20 points
The final 60 points will be for the last step in your plan – configuring the report and the parameters for the report. The tutor subject choices should be A, B, or C. The date selection should be from the DateQualified values, with a default value of the earliest DateQualified. The customer zip code must be one of those in the TutorList. [Hint: The latter two parameter items require creation of additional data sets, one for the DateQualified values, and one for the zip code values. Hint: Each of the datasets should use a SELECT query as well to provide the data for the parameter. Look at what you did in Project 9 as an example. The DateQualified data set will have datetime data type. Each parameter will become a drop down on your report.
You should place four screen shots under this final step in your project document.
1. For the parameter that specifies Misty's choice of a tutor that resides in the same zip code as the client show one such client/tutor example.
2. For the parameter that specifies Misty's choice of minimum DateQualified, show how you have configured the Available Values for that parameter.
3. Show the Report Data pane of BIDS, showing all parameters and all data sets with their fields.
4. Deploy the report. Run the first report that does not contain parameters for zip code 91016. This one should show only the tutors available for that zip code. Run the second report with parameters for a customer zip code of 91803 for the report with parameters showing which client they qualify to work with, what their skill set is, and date equal to the earliest DateQualified value. Paste the screen shot of this report into your project document.
Because the entire project involves sequential steps, if you become hung somewhere in the middle and cannot proceed, you may send details to your instructor, and receive a hint in exchange for an appropriate point "cost" or deduction. If you cannot figure out what query you need to create the report, the instructor will send it to you, on request from you, for a "cost" of 20 points so you can move on.
Your screen shot of the report without parameters should look similar to this on the Report BIDS screen. It should be for ZipCode 91016 regardless to subject and qualified date:
Your screen shot of the report without parameters should look similar to this on the Report Manager screen:
Your screen shot of the report with parameters should look similar to this on the Report BIDS screen It should be for ZipCode 91803 regardless to subject and qualified date:
Your screen shot of the report with parameters should look similar to this on the Report Manager screen:

IT358 Unit 6 Views

Outcomes addressed in this activity:
Create a view, using CREATE
VIEW command or the CREATE OR REPLACE VIEW command
Explain the effect of the WITH READ ONLY option
Demonstrate the ability to update records in a simple view
Demonstrate the ability to re-create a view
Identify problems associated with adding records to a complex view
Identify the key-preserved table underlying a complex view

Project requirements:
In this project, you will write joins and subqueries:
1. Complete the Review Questions for numbers 1–5 from Chapter 13.
2. Complete the Hands-on Assignments #1-10 from Chapter 13.

Oracle 11G SQL Chapter 13 Hands-On assignment Solution
1. Create a view that will list the name of the contact person at each publisher and the person’s phone number. Do not include the publisher’s ID in the view. Name the view CONTACT.
2. Change the CONTACT view so that no users can accidentally perform DML operations on the view.
3. Create a view called HOMEWORK13 that will include the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make certain the view will be created even if the FIRSTATTEMPT table does not exist.
4. Attempt to view the structure of the HOMEWORK13 view.
5. Create a view that will list the ISBN and title for each book in inventory along with the name and telephone number of the individual to contact in the event the book needs to be reordered. Name the view REORDERINFO.
6. Try to change the name of one of the individuals in the REORDERINFO view to your name. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
7. Select one of the books in the REORDERINFO view and try to change the ISBN of the book. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
8. Delete the record in the REORDERINFO view that contains your name (if that step was not performed successfully, then delete one of the contacts already listed in the table).Was there an error message displayed when performing this step? If so, what was the cause of the error message?
9. Issue a rollback command to undo any changes made with any previous DML operations.
10. Delete the REORDERINFO view.

IT358 Unit 7 Synonyms Index and Sequence

Outcomes addressed in this activity:
Describe how the schema objects work
Describe how sequences and indexes are created, modified, and removed within Oracle
Describe when to use an Index
Utilize Synonyms

Project requirements:
In this project, you will create a view, synonym, and index:
1. Answer the Review Questions #1–5 from Chapter 6.
2. Complete Hands-on Assignments #1-10.

Oracle 11G SQL Chapter 6 Hands-On assignment Solution
1. Create a sequence to use for populating the Customer# column of the CUSTOMERS table. When setting the start and the increment values, keep in mind that data already exists in this table. The options should be set to not cycle the values, not cache any values, and no minimum or maximum values should be declared.
2. Add a new customer row using the sequence created in Question 1. The only data currently available for the customer is as follows: last name = Shoulders, first name = Frank, and zip = 23567.
3. Create a sequence that will generate integers starting with the value 5. Each value should be three less than the previous value generated.The lowest possible value should be 0, and the sequence should not be allowed to cycle. Name the sequence MY_FIRST_SEQ.
4. Issue a SELECT statement that will display NEXTVAL for MY_FIRST_SEQ three times. Because the value is not being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the last SELECT?
5. Change the setting of MY_FIRST_SEQ so the minimum value that can be generated is –1000.
6. Create a private synonym that will allow you to reference the MY_FIRST_SEQ object as NUMGEN.
7. Use a SELECT statement to view the CURRVAL of NUMGEN. Delete the NUMGEN synonym and MY_FIRST_SEQ.
8. Create a Bitmap index on the CUSTOMERS table to speed up queries that search for customers based on their state of residence. Verify that the index exists, and then delete the index.
9. Create a B-tree index on the customer’s Last Name column. Verify that the index exists by querying the data dictionary. Remove the index from the database.
10. Many queries search by the number of days to ship (number of days between the order and shipping dates). Create an appropriate index that might improve the performance of these queries.

PRG 421 Week 2 GUI Components Program

For this assignment, you will choose from the following options:
Option 1: Retail Calculator
A retailer would like a calculator to indicate sale prices when he marks items down at various percentages.
Design, implement, test, and debug a GUI application that allows the retailer to enter the item’s name, the item’s original price, and the percentage discounted.
• The application should include a Calculate button that, when pressed, computes and displays the sale price in a label or text field.
• The program should allow the retailer to enter any number of items in this manner.
• Include an Exit button to end the execution of the application.
• The program should allow the user to select a department from a menu. You may create the menu as a true menu, a JComboBox, or radio buttons.
• Create your own list to populate the menu: men’s clothing, women’s clothing, house wares, and so forth.
• Include at least five departments.
• Create a JTextArea or a JTable to display each of the following for each item entered:
o Item name
o Department
o Original price
o Sale price

Option 2: Payroll Application
An employer is looking for an easier way to track employee pay.
Design, implement, test, and debug an application to compute an employee’s weekly pay. If the employee works more than 40 hours a week, the employee earns 1.5 times the basic hourly pay for any time worked over 40 hours. The program should allow the user to input the employee’s name, the number of hours worked, and the hourly rate.
• The application should include a Calculate button that, when pressed, computes and displays the total pay in a label or text field.
• The program should allow the user to enter information for any number of employees in this manner.
• Include an Exit button to end the execution of the application.
• The program should allow the user to select the employee’s department from a menu. This can be a standard menu, radio buttons, or a JComboBox.
• Create your own list to populate the menu: Marketing, IT, Accounting, and so forth.
• Include at least five departments.
• Validate the employee’s hourly wage and hours worked according to the following rules:
    The hourly wage must be at least $6 per hour and cannot be more than $150 per hour. It must be numeric.
    The hours worked must be a numeric value greater than or equal to 0 and cannot be greater than 60.
• Create a JTextArea, JTable, or other form of a table to your application and use that to list the following for every employee:
o Name
o Department
o Weekly salary
Add each employee to the list after you have computed that employee’s salary.

CIS355A iLab 5 GuessGame and Text File I/O Java Programs

In this lab you will create a single program that creates and reads a sequential file. All outputs must be GUI-based without using JOptionPane.

Deliverables
Program files for each of the following two programs
1. GuessGame
2. Text File I/O

iLAB STEPS
STEP 1: GuessGame
Write a program named GuessGame.java that plays the game “guess the number” as follows: Your program chooses the number to be guessed by selecting an integer at random in the range 1–1000. The program then displays the following in a label.
I have a number between 1 and 1000 -- can you guess my number?
Please enter your guess: ____________________
A JTextField should be used to input the guess. A JButton must be provided to allow the user to press each time a guess is entered. As each guess is input, the background color should change to either red or blue. Red indicates that the user is getting warmer, and blue indicates that the user is getting colder. A JLabel should display either Too High or Too Low to help the user zero in on the correct answer. When the user gets the correct answer, Correct! should be displayed, and the JTextField used for input should be cleared and changed to be uneditable. Also, a JButton should be provided to allow the user to play the game again. When the New Game JButton is clicked, a new random number should be generated and the input JTextField changed to be editable. A JButton must be provided to allow the user to exit the application. Also provide a count of the number of guesses the user entered when the correct number is guessed.

STEP 2: Text File I/O (20 points)
Write a program called TextFileIO.java to create a file named numbers.dat. Then create an algorithm that writes all even numbered integers from 1 to 100, separated by a comma. After the file has been created, close and reopen the file and display the results to the screen. After the results have been displayed append the odd number integers from 1 to 100, separated by a comma to the end of the file. Reopen the file and display the results. The contents of the file should be the even numbers from 1 to 100 separated by a comma followed by the odd number from 1 to 100 separated by a comma. The output of this program would be something like the following
2,4,6,8,10,12,14,……,98,100
2,4,6,8,10,12,14,……,98,100,1,3,5,7,9,……..97,99

MIS562 Week 2 Homework Oracle Queries

Create the tables from demo.sql script as follows:
Download and copy the demo.sql file from the Doc Sharing 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 instructions in the Submission Requirements to create and submit a document with your solutions. Submissions that do not meet the Requirements will be returned with a grade of zero and will be accepted and graded when corrected and re-submitted. Anyone having difficulties can obtain assistance in the TDAs.

Write SQL statements to create result tables showing the solutions to the following requests.
1. (4 pts) List all employee information in department 30.
2. (4 pts) List employees name, job, and salary that is a manager and has a salary > $1,000
3. (4 pts) Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
4. (4 pts) Show all employee names and salary that earn between $1,000 and $2,000.  Use the between operator.
5. (4 pts) Select all employees that are in department 10 and 30. Use the IN operator.
6. (4 pts) Select all employee names with an “A” in the first position of the employee name.  Use the substring function or a wild card.
7. (4 pts) Select all employees with an “A” as the second character of their name.  Use a wildcard.
8. (4 pts) List the employee names in alphabetical sequence.
9. (4 pts) List the job, salary, and employee name in job order and then salary in descending order.
10. (4 pts) Show a list of different jobs.  Eliminate repeating values.
11. (4 pts) Show employee names, salary that has not earned a commission yet.
12. (4 pts) Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
13. (4 pts) 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.
14. (4 pts) 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.
15. (4 pts) Determine which student does not have the first letter the last name capitalized.  Show the STUDENT_ID and LAST_NAME columns.
16. (4 pts) 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.
17. (4 pts) Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.
18. (4 pts) Write a SQL statement that converts a date datatype to a char datatype.
19. (4 pts) Write a SQL statement that convert a number value to a character.

Before doing problems 20 - 25 Download and Execute the script demoaddrows.sql from the Doc Sharing to add rows into the two tables above. Write SQL statements and produce result tables to solve the following requests.
20. (2 pts) Show a list of different jobs.  Eliminate repeating values.
21. (2 pts) How many employees are working at each job in each department and what the sums and averages are for the salary of those employees?
22. (2 pts) Show the employee name with the maximum salary
23. (2 pts) Show the average salary for all employees that are not managers.
24. (2 pts) What is the difference between the highest and lowest salary?
25. (2 pts) Select employee number, name for all employees with salaries above the average salary. Use a subquery.

Before doing problems 26 through 31 Create the text Student tables with the createStudent.sql script. The script and instructions are in the schemaset.zip file available in the Doc Sharing. Download and unzip the files from the Doc Sharing to a C: drive directory such as the \temp directory. At the sql prompt enter the command @V:\temp\createStudent.sql. This will create  tables exactely as they are portrayed in the text and insert data into them.
26. (2 pts) 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.
27. (2 pts) Which city has the most zip codes assigned to it? Use a TOP-N query.
28. (2 pts) 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.
29. (2 pts) 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.
30. (2 pts) Show the number of students enrolled and zip code for New York and where the city begins with 'W'. Use a Scalar subquery.
31. (2 pts) Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.

Penn Foster Exam 40254500 Construction Company Java Program

Write an application for a construction company to handle a customer's order to build a new home. Use separate ButtonGroups to allow the customer to select one of four models, the number of bedrooms, and a garage type. Assume that the models are the Aspen, $100,000; the Brittany, $120,000; the Colonial, $180,000; or the Dartmoor, $250,000. Assume that any model can have two, three, or four bedrooms and that each bedroom adds $10,500 to the base price. Assume that the garage type can be zero-, one-, two-, or three-car, and that each car adds $7,725 to the price. Save the file as JMyNewHome.java.

MIS562 Week 4 Homework Oracle Queries Database

Part 1
Using the tables created in week 2:
Question ( 4 pts per question)
1. Show a list of all employee names and their department names and the employees for each department. Be sure to show all departments whether there is an employee in the department or not. Use an outer join.
2. Select all employee names and their department names. Be sure to show all employees whether they are assigned to a department or not. Use an outer join.

Using the student schema:
Question ( 3 pts per question)
3. Write a query that that performs an inner join of the grade, student, and grade_type tables using ANSI SQL 99 syntax.
4. Write a query that that performs an inner join of the grade, student, and grade_type tables using the Oracle inner join convention.
5. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables. Use a set operator.
6. Write a SQL statement using a set operator to show which students enrolled in a section that are not enrolled in any classes. Exclude students with student id less than 300.

Part 2
Question ( 5 pts per question)
1. Write and execute two INSERT statements to insert rows into the ZIPCODE table for the following two cities of your choice. After your INSERT statements are successful, make the changes permanent.
2. Create a sequence called STUDENT_ID_NEW that begins with 900 and increments by 1. If the sequence already exists, drop the sequence and recreate it.
3. Make yourself a student by writing and executing an INSERT statement to insert a row into the STUDENT table with data about you. Use one of the zip codes you inserted in Exercise 1. Only insert values into the columns STUDENT_ID using the sequence you created in step 2, FIRST_NAME, LAST_NAME, ZIP, REGISTRATION_DATE (use a date that is five days after today), CREATED_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE. Issue a COMMIT command afterwards.
4. Write an UPDATE statement to update the data about you in the STUDENT table. Update the columns SALUTATION, STREET_ADDRESS, PHONE, and EMPLOYER. Be sure to also update the MODIFIED_DATE column and make the changes permanent
5. Delete the row in the STUDENT table and the two rows in the ZIPCODE table you created. Be sure to issue a COMMIT command afterwards
6. Create a table called TEMP_STUDENT with the following columns and constraints: a column STUDID for student ID that is NOT NULL and is the primary key, a column FIRST_NAME for student first name; a column LAST_NAME for student last name, a column ZIP that is a foreign key to the ZIP column in the ZIPCODE table, a column REGISTRATION_DATE that is NOT NULL and has a CHECK constraint to restrict the registration date to dates after January 1st, 2000.
7. Write an INSERT statement violating at least two of the constraints for the TEMP_STUDENT table you just created.
8. Write another INSERT statement that succeeds when executed, and commit your work.
9. Alter the TEMP_STUDENT table to add two more columns called EMPLOYER and EMPLOYER_ZIP. The EMPLOYER_ZIP column should have a foreign key constraint referencing the ZIP column of the ZIPCODE table. Update the EMPLOYER column.
10. Alter the table once again to make the EMPLOYER column NOT NULL.
11. Write a statement that drops the EMPLOYER_ZIP column.
12. Drop the TEMP_STUDENT table once you're done with the exercise.
13. Create a non unique index called crse_modified_by_i on the MODIFIED_BY column of the course table
14. Change the registration date of Paula Valentine to today’s date. Create a view called CURRENT_REGS reflecting all students that registered today. (You will need to execute this query in question 6 on the same day you change the date)
15. Create a view called roster reflecting all students taught by the instructor Marilyn Frantzen. Query the view.
16. Given the MY_EMPLOYEE view, what information is the user allowed to retrieve? Who can update the SALARY column through the view? Hint: The USER function returns the name of the currently logged in user.
CREATE OR REPLACE VIEW my_employee AS
SELECT employee_id, employee_name, salary, manager
FROM employee
WHERE manager = USER
WITH CHECK OPTION CONSTRAINT my_employee_ck_manager

COMP274 Week 6 Programming Assignment The Calendar Program

The purpose of this lab is to give you a chance to use some of the data stream tools we have been discussing in a simple application. The assignment is to write a calendar application which allows the user to select a date, and either retrieve a previously stored calendar entry, or save a calendar entry.

Your program should present a GUI interface which allows the user to specify the month, day, and year of the calendar entry. The GUI should also have a text area for displaying and editing a particular entry. It will also need two buttons, one for saving an entry, and the other for retrieving an entry.

Required program elements:
1. Your user interface must allow the user to enter the month, day, and year. You can do this using text fields for input, or you can use ComboBoxes if you feel adventurous.
2. The only GUI components which create events that your program needs to handle are the save and retrieve buttons.
3. Don’t go overboard making your GUI beautiful! We are just looking for basic functionality here!
4. You must have a separate class which manages the calendar data. You will have a minimum of three classes in your application, a user interface class, the calendar manager class, and a calendar test class. The user interface class creates an instance of the calendar manager in its constructor and stores it in a member variable.
5. The calendar manager must provide methods which support saving a specific calendar entry and retrieving a specific calendar entry. The interfaces must be defined to only pass a single day’s calendar entry across the interface.
6. The calendar manager must store calendar data into files according to month+year. That is, the calendar entries for December 2011 must all be stored in the same file.
7. The calendar manager must use ObjectInput/OutputStreams to read/write data from/to files. The calendar manager will use an array to store String objects. The position of a String in this array corresponds to the calendar entry for a specific day.
8. The save method of the calendar manager will need to determine if a file exists for the requested month and year. If so, the object from that file must be read into the calendar manager. Otherwise, the calendar manager must create an empty String array. The new entry must be saved to the appropriate day’s location in the array. The modified array must be saved to the appropriate file.
9. The retrieve method of the calendar manager will need to determine if a file exists for the requested month and year. If not, return an error string indicating that there is no such entry. If the file exists, read the String array from the file and locate the requested day’s entry. If this entry is null, return an error string indicating that there is no such entry, otherwise return the entry.

Take screen shots of the output of your program. Paste the screen shots and your source code for your programs into a Word document. Submit your Word document.