MIS562 Week 2,4,5,7 labs in MySQL – Perfect Solution – Instant Delivery

Lab Price = $50
Please feel free to send us your queries at: support@iqrajavaid.com
Payment methods

Add to Cart

Buy Now

View Cart


MIS562 Lab 2 – 31 queries – Instant Delivery – Perfect Solution



Name: _______________________
Homework (100 points)
Resources:
- demo.sql
- demoaddrows.sql
-createStudent.sql
Part 1
Create the tables from demo.sql script
1. Download the demo.sql and createStudent.sql file from the Doc Sharing. Run demo.sql and createStudent.sql in MySQL Omnymbus Environment. 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.
2. Please use the template below to provide your solutions. You must submit your Input SQL Query in the second column in the template and you must also include executed output of each input query below input SQL Query. Please truncate to 20 rows, if output is more than 20 rows. Any answers without executed output WILL NOT earn any credit.
3. Please use demo.sql for questions Q1 through Q12 and remaining questions use createStudent.sql
4. If you combined demo.sql and createStudent.sql under MIS562_xxxx database, then there is no need to change database.
Write SQL statements to solve the following requests.
Question (4 pts per question) SQL statement or answer
1. List all employee information in department 30.
2. List employees name, job, and salary that is a manager and has a salary > $1,000
3. Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
4. Show all employee names and salary that earn between $1,000 and $2,000. Use the between operator.
5. Select all employees that are in department 10 and 30. Use the IN operator.
6. Select all employee names with an “A” in the first position of the employee name. Use the substring function or a wild card.
7. Select all employees with an “A” as the second character of their name. Use a wildcard.
8. List the employee names in alphabetical sequence.
9. List the job, salary, and employee name in job order and then salary in descending order.
10. Show a list of different jobs. Eliminate repeating values.
11. Show employee names, salary that has not earned a commission yet.
12. Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
13. 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 descending order.
14. 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. Determine which student does not have the first letter the last name capitalized. Show the STUDENT_ID and LAST_NAME columns.
16. 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. Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.
18. Write a SQL statement that converts a date datatype to a char datatype.
19. Write a SQL statement that convert a number value to a character.
Part 2
Please use demo.sql for questions Q1 through Q6 and remaining questions use createStudent.sql
Execute the script demoaddrows.sql to add rows if necessary. Write SQL statements to solve the following requests.
If you combined demo.sql and createStudent.sql under MIS562_xxxx database, then there is no need to change database.
Question ( 2 points per question) SQL statement or answer
1. Show a list of different jobs. Eliminate repeating values.
2. How many employees are working at each job in each department and what the sums and averages are for the salary of those employees?
3. Show the employee name with the maximum salary
4. Show the average salary for all employees that are not managers.
5. What is the difference between the highest and lowest salary?
6. 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
7. 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.
8. Which city has the most zip codes assigned to it?
9. 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
10. 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.
11. Show the number of students enrolled and zip code for New York and where the city begins with ‘W’. Use a Scalar subquery.
12. Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.





MIS562 Week 4 – 22 queries – Instant Delivery – Perfect Solution



Name: _______________________
Homework (100 points)
Please use the template below to provide your answers. Be sure to add your SQL Query and executed output from your input SQL Query. Any answers without executed output WILL NOT earn any credit.
Part 1
Using the tables created in week 2:
Question ( 4 pts per question) SQL Statement or Answer
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) SQL Statement or Answer
3. Write a query that that performs an inner join of the grade, student, and grade_type tables.
4. Write a query that that performs an inner join of inner join of the grade, student, and grade_type tables using inner join convention.
5. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables.
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 greater than 300.
Part 2
Question ( 5 pts per question) SQL Statement or Answer
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
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.
Use Google as your employer and use zip code 94043
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 Regina Gates 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





MIS562 Week 5 – 4 queries – Instant Delivery – Perfect Solution


ame: _______________________
Homework (50 points)
Please use the template below to provide your answers. Be sure to add your SQL Query and executed output from your input SQL Query. Any answers without executed output WILL NOT earn any credit.
Using the student schema from Week 4, provide answers to the following questions.
Question SQL statement or Answer
1. Generate statistics for the student, enrollment, grade, and zipcode tables (15 pts)
Use following.
ANALYZE TABLE XXXXXX;
SHOW TABLE STATUS FROM MIS562_XXXX WHERE NAME IN (‘STUDENT’,’ENROLLMENT’,’GRADE’,’ZIPCODE’);
2. Write a query that performs a join, a subquery, a correlated subquery using the student, enrollment, grade, and zipcode tables. Execute each query to show that it produces the same results. (15 pts)
3. Produce an autotrace output using Explain for each query. (10 pts)
4. Analyze the results and state which performs best and why. Write an analysis of what operations are being performed for each query. Determine which query is the most efficient and explain why (10 pts)





MIS562 Week 7 – Stored Procedures and triggers – Instant Delivery – Perfect Solution



Name: _______________________
Homework (100 points)
For Part 1 Q1, you don’t need to run SQL and no output is required.
For Part 1 Q2, be sure to include output
For part 2 running stored procedure, please refer to document under Announcement. You must include output for all three questions
Part 1
Question SQL statement or Answer
1. Analyze the following query and write a description of the output it produces. (20 points)
Do not run query.
SELECT COUNT(DECODE(SIGN(total_capacity-20), -1, 1, 0, 1)) “<=20",
COUNT(DECODE(SIGN(total_capacity-21), 0, 1, -1, NULL,
DECODE(SIGN(total_capacity-30), -1, 1)))"21-30",
COUNT(DECODE(SIGN(total_capacity-30), 1, 1)) "31+"
FROM
(SELECT SUM(capacity) total_capacity, course_no
FROM section
GROUP BY course_no)
You don’t need to execute SQL Query and you are not required to provide executed output
2. Determine the top three highest salary from department 10, 20 and 30 (20 points)
Part 2
Write a stored procedure using the student schema: submit the output with your answer (20 points)
Write a stored function using the student schema: submit the output with your answer (20 points)
Write a trigger using the student schema: submit the output with your answer (20 points)






Relevant Material
Screenshots
Lab2: query13_output

Lab2: query13_output

Lab2: query11_output

Lab2: query11_output

Lab2: query16_output

Lab2: query16_output

Lab4: Screenshot2

Lab4: Screenshot2

Lab4: Screenshot1

Lab4: Screenshot1

Lab5: Screenshot2

Lab5: Screenshot2

Lab5: Screenshot1

Lab5: Screenshot1

Lab 7: Screenshot2

Lab 7: Screenshot2

Lab 7: Screenshot1

Lab 7: Screenshot1

Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at support@iqrajavaid.com.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at support@iqrajavaid.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at support@iqrajavaid.com.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on support@iqrajavaid.com
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Payment Details
Lab Price = $50
Please feel free to send us your queries at: support@iqrajavaid.com

Payment methods

Add to Cart

Buy Now

View Cart