Hands On Practical Experience
Hands On Practical Experience READ: For each problem, write a QUERY. ─ Answer any questions asked, and ─ Include screenshot of code (no extra whitespace) ─ Include screenshot of code result (no extra whitespace) ─ Your assignment will be graded if the images are legible ─ What is the benefit of using a VIEW for Queries, compared to a TABLE? ─ Select a database and create a view on a table (or tables). ─ For each column, give it an alias. ─ Include a WHERE clause. ─ Briefly explain your selection ─ Use the VIEW created in Problem #2. ─ SELECT all from the VIEW ─ REPLACE the VIEW created in Problem #2, add some additional element to the VIEW. Oracle Academy Section 15, Lesson 2: DML Operations and Views 5. CREATE a copy of the d_songs table CREATE TABLE new_dsongs AS (SELECT * (FROM d_songs) ─ ─ Create a VIEW against the new_dsongs table that includes every column. You will use this view to INSERT data. Show the Design Spec of the View for verification. 6. INSERT into the VIEW created in Problem #1, 3 rows of data. ─ Execute a SELECT * to verify your DML command. 7. You are going to create a VIEW with READ ONLY OPTION. Use the new_DCDS table created in Module 13. ─ ─ ─ Create a VIEW using all columns. Restrict the data to a couple of YEARS of your choosing (must represent data). Define the table AS READ ONLY 8. Using the view created in problem 7, execute a DELETE FROM where the cd_number is a specific cd number (make sure it exists). ─ ─ What do you notice? Were you able to execute the DELETE FROM? 9. Using the view created in problem 7. ─ Write a CREATE or CREATE or REPLACE to modify the view you created. (please maintain all columns and a couple of years) ─ Please replace the READ ONLY option with WITH CHECK OPTION CONSTRAINT. ─ You may name this new view whatever you would like (some organizations request CHECK to be in the VIEW name) 10. Now use the view created in problem 9 to delete any CD. ─ Show all programming code. Results before and after DELETE. ─ Explain what we deleted, and how it was able to be deleted. Oracle Academy Section 15, Lesson 3: Managing Views, Top-N-Analysis, and Inline Views 11. You will create a TOP-N-Analysis VIEW. ─ Please run a query that selects the last name and the salary from the h_employees table. ─ In the query return the salaries ranked from highest to lowest for the top __ employees. You can choose a number to fill in the blank (i.e. top 3, top 2, top 4…) 12. Please create an inline view (a view in the FROM Clause) using the h_employees table that lists at least the salary and maximum salary for each department. You may choose to list more columns. Hint: One query will need to calculate maximum salary by department ID. Module 16 Oracle Academy Section 16, Lesson 1: Working with Sequences Hands On Practical Experience READ: For each problem, write a QUERY. ─ Answer any questions asked, and ─ Include screenshot of code (no extra whitespace) ─ Include screenshot of code result (no extra whitespace) ─ Your assignment will be graded if the images are legible 1. In Module 15, you created new_Dsongs table. Using the CREATE TABLE as a subquery only carried over any NOT NULL constraint. ─ Create a SEQUENCE that you can use on the ID column for creating the SONG IDs. ─ To avoid assigning primary-key numbers to these tables that already exist, the sequence should start at 100 and have a maximum value of 1000. ─ Have your sequence increment by 2 and have NOCACHE and NOCYCLE. ─ Please include in your sequence name “seq” ─ Please explain each aspect of your code in detail. ─ Please query the USER_SEQUENCES data dictionary to verify the seq_d_songs_seq SEQUENCE settings. 2. Using the SEQUENCE created in Problem #1. ─ Please insert four rows of data into the table created in problem 1. ─ Be sure to use the sequence that you created for the ID column. ─ The other columns that need data are: title, duration, artist, type_code. ─ Include both your INSERT Statements and the Table Data Oracle Academy Section 16, Lesson 2: Indexes and Synonyms 3. What is an index and what is it used for? (In your own words) 4. What is a ROWID, and how is it used? (In your own words) 5. When will an index be created automatically? 6. Please create a non-unique index (FK) for the cd_number column in the d_track_listings table. Then use the Oracle Application Express SQL Workshop Data Browser to confirm that the index was created. Please explain your syntax. 7. Please create a function-based index that allows for the last_name column, in the d_partners table, to not be capitalized for searches. Please then write a SELECT statement that uses this index. Please explain each aspect of your code.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
