Oracle

Need Help in Oracle Assignment?
We can help you if you are having difficulty with your Oracle Assignment. Just email your Oracle Assignment at admin@assignmentcache.com.
We provide help for students all over the world in Oracle Assignment.

Items 1 to 10 of 46 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

Grid  List 

Set Descending Direction
  1. DBM 449 Lab 2 Sql File

    DBM 449 lab 2 OEM Query optimization

    Regular Price: $20.00

    Special Price: $15.00

    In this lab we will focus on several common performance tuning issues that one might encounter while working with a database.  You will need to refer to both your text book and the lecture material for this week for examples and direction to complete this lab.
    To record your work for this lab use the LAB2_Report.doc found in Doc Sharing. As in your first lab you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Drop Box for Week 2.


    L A B   S T E P S   
    STEP 1: Examine Query Optimization using OEM


    Oracle Enterprise Manager (OEM) provides a graphical tool for query optimization.  The tables that you will be using in this lab are the same ones that were created in the first lab in the DBM449_USER schema.



    1. Start OEM via Citrix iLab. If you need help or instructions on how to do this you can refer to the How_to_use_OEM_in_Citrix iLab.pdf file associated with this link.

    2. Select Database Tools icon from the vertical tool bar and Select SQL Scratchpad icon from the expanded tool bar. If you need help or instructions on how to do this your can refer to theExecuting_and_Analyzing_Queries_in_OEM.pdf file associated with this link.

    3. Write a SQL statement to query all data from table COURSE (you will need to connect as the DBM449_USER). Click on Execute. Take a screen shot that shows the results and paste that into the lab document.

    4. Click on Explain Plan. Take screen shot of the results and past that into the lab document.

    5. Write a comment how this query is executed.

    6. Write a SQL statement to query the course_name, client_name and grade from the COURSE, COURSE_ACTIVITY and CLIENT tables and order the results by course name, and within the same course by client name.

    7. Click on Explain Plan. Take screen shot of the results and past that into the lab document.

    8. Exit out of OEM at this point.

    9. Write a comment on how this query is executed.


    STEP 2: Examine Query Optimization using SQL*Plus


    In this portion of the lab we are going to use SQL*Plus to replicate what we did in Step one using OEM.  At the end of this part of the lab you will be asked to compare the results between the processes.



    1. Before you can analyze an SQL statement in SQL*Plus you first need to create a Plan Table that will hold the results of your analysis.  To do this you will need to download the UTLXPLAN.SQL file associated with this link and run this script in an SQL*Plus session while logged in as the DBM449_USER user.  Once the script has completed then execute a DESC command on the PLAN_TABLE.

    2. Again you are going to write a SQL statement to query all data from table COURSE.  Remember to make the modifications to the query so that it will utilize the plan table that you just created.

    3. Now write the query that will create a results table similar to the one below by using the DBMS_XPLAN procedure.


    PLAN_TABLE_OUTPUT
    Plan hash value:  1263998123
         
      Id      Operation      Name      Rows      Bytes      Cost (%CPU)      Time
      0      SELECT STATEMENT          5      345      3    (0)      00:00:01
      1      TABLE ACCESSFULL      COURSE      5      345      3    (0)      00:00:01
        
    Note
    PLAN_TABLE_OUTPUT
    - dynamic spamling used for this statement



    1. Now execute the second query you used in Step 1 and then show the results in the plan table for that query.  HINT: Before you run your second query you will need to delete the contents of the plan table so that you will get a clean analysis.

    2. Write a short paragraph comparing the output from OEM to the output from the EXPLAIN PLAN process you just ran.  Be sure to copy/paste all of the queries and results set from this step into the lab report section for this step.


    STEP 3: Dealing With Chained Rows


    In this portion of the lab we are going to create a new table and then manipulate some data to generate a series of chained rows within the table.  After you have generated this problem then we are going to go through the process of correcting the problem and tuning the table so that the chained rows are gone.  The process is a little tricky and is going to require you to think through your approach to some of the SQL.  Remember that every table has a hidden column named ROWID that is created implicitly by the system when the table is created.  This column can be queried just like any other column.  You will need this information in step 6 of this part of the lab. 



    1. For this part of the lab you will need to create a new user named GEORGE.  You can determine your own password but you want to make sure that the default tablespace is USERS and the temporary tablespace is TEMP.  Grant both the connect and resource rolls to the new user and then log in to create a session for the new user GEORGE.

    2. Once logged in to the new user then write the SQL to create a new table using the given column information and storage parameters listed below.  NOTE:  the parameters have been chosen intentionally so please do not change them.


    Table name: NEWTAB
    Columns: Prod_id       NUMBER
         Prod_desc VARCHAR2(30)
         List_price NUMBER(10,2)
         Date_last_upd DATE


    Tablespace:    USERS
         PCTFREE    10
         PCTUSED    90
         Initial and Next extents:    1K
         MinExtents    1
         MaxExtents    121
         PCTINCREASE    0



    1. Next, you will need to download both the UTLCHAIN.SQL and LAB2_FILL_NEWTAB.SQL scripts from the links shown.  First run the UTLCHAIN script in your SQL*Plus session and then run the LAB2_FILL_TAB script.  Be sure that you run them in the order just described.

    2. Now execute the ANALYZE command on the table NEWTAB to gather any chained rows.  HINT: refer back to the lecture material for this week and your text book. 

    3. Write and execute the query that will list the owner_name, table_name and head_rowid columns from the CHAINED_ROWS table.  You will have approximately 200+ rows in your result set so please do not copy/paste all of them into the lab report.  You only need the first 10 or 15 rows as a representation of what was returned.

    4. Now you need to go through the steps of getting rid of all the chained rows using these steps.  


      • You can create your temporary table to hold the chained rows of the NEWTAB table as a select statement based on the existing table.  HINT: CREATE TABLE NEWTAB_TEMP AS SELECT * FROM NEWTAB....  You want to be sure that you only pull data from the existing table that matches the data in the CHANED_ROW table.  To assure this you will need a WHERE clause to pull only this records with a HEAD_ROWID value in the CHAINED_ROWS table that matches a ROWID value for the NEWTAB table.  

      • Now you need to delete the chained rows from the NEWTAB table.  To accomplish this you will need a subquery that pulls the HEADROW_ID value from the CHAINED_ROWS table to match against the ROWID value in the NEWTAB table.  The number of rows deleted should be the same as the number that you retrieved in the query for part 5 of this section.

      • Now write an insert statement that will insert all of the rows of data in the temporary table that you created above into the NEWTAB table.  Be sure that you explicitly define the rows that you are pulling data from in the NEWTAB_TEMP table.

      • Next, write and execute the statement that will TRUNCATE the chained_rows table.

      • Now run the same ANALYZE statement you did in step 4 and then the query you did in part 5 above.  This time you should get a return message stating no rows selected.



    Be sure that you copy/paste all of the above SQL code and returned results sets and messages into the appropriate place in the Lab Report for this week.


    Deliverables     

    What is Due
    Submit your completed Lab 2 Report to the Dropbox as stated below.  Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.
     

    Learn More
  2. DBM 449 Lab 3 Sql File

    DBM 449 Lab 3 Distributed Database

    Regular Price: $20.00

    Special Price: $15.00

    L A B O V E R V I E W    


    Scenario/Summary
    To the end user working with databases distributed through out a company's network is not different than working with multiple tables within a single database. The fact that the different databases exist in other locations should be totally transparent to the user. For this lab we are going to take on the roll of a database administrator in a company that has three regional offices in the country. You work in the central regional office, but there is also a West Coast Region located in Seattle and an East Coast Region located in Miami. Your roll is to gather report information from the other two regions.


    For this lab you are going to work with three different databases. You already have your own database instance. You will also be working with the a database named SEATTLE representing the West Coast Region and a database named MIAMI representing the East Coast Region. Login information for these two additional database instances is as follows:


    SEATTLE: Userid - seattle_user
    Password - seattle
    Host String - seattle


    MIAMI: Userid - miami_user
    Password - miami
    Host String - miami


    To record your work for this lab use the LAB3_Report.doc found in Doc Sharing. As in your previous labs you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Drop Box for Week 3.


    L A B S T E P S    
    STEP 1: Setting up Your Environment



    1. Be sure you are connected to the DBM449_USER schema that was created in lab 1. 

    2. To begin this lab you will need to download the LAB3_DEPTS.SQL script file associated with the link and run the script in your DBM449_USER schema of your database instance. This script contains a single table and that you will be using to help pull data from each of the other two database instances.  Notice that the DEPTNO column in this table is the PRIMARY KEY column and can be used to reference or link to the DEPTNO column in the other two database employee tables.

    3. Now you need to create a couple of private database links that will allow you to connect to your other two regional databases. To accomplish this use the connection information listed above in the Lab Overview section. Name your links using your database instance name together with the region name as the name for the link. Separate the two with an underscore (example - DB1000_SEATTLE).

    4. After creating both of your database links, query the USER_DB_LINKS view in the data dictionary to retrieve information about your database links.  The output from your query should look similar to what you see below.  You will need to set your linesize to 132 and format the DB_LINK and HOST columns to be only 25 bytes wide to get the same format that you see.


    DB_LINK                   USERNAME                       HOST                      CREATED
    ------------------------- ------------------------------ ------------------------- ---------
    DB1000_MIAMI              MIAMI_USER                     miami                     09-DEC-08


    STEP 2: Testing your Database Links
    Each of your remote databases has an employee data table. The tables are named SEATTLE_EMP and MIAMI_EMP respective to the database they are in. Using the appropriate database link, query each of the two tables to retrieve the employee number, name, job function, and salary. (HINT: you can issue a DESC command on each of the distributed tables to find out the actual column names just like you would for a table in your own instance.


    STEP 3: Connecting Data in the Seattle Database
    Write a query that will retrieve all employees from the Seattle region who are salespeople working in the marketing department. Show the employee number, name, job function, salary, and department name (HINT: The department name is in the DEPT table) in the result set.


    STEP 4: Connecting Data in the Miami Database
    Write a query that will retrieve all employees from the Miami region who work in the accounting department. Show the employee number, name, job function, salary, and department name (HINT: The department name is in the DEPT table) in the results set.


    STEP 5: Connecting Data in all Three Databases
    Now we need to increase our report. Write a query that will retrieve employees from both the Seattle and Miami regions who work in sales. Show the employee number, employee name, job function, salary and location name in the result set (HINT: The location name is in the DEPT table).


    STEP 6: Improving Data Retrieval from all Three Databases
    Writing queries like the ones above can be fairly cumbersome. It would be much better to be able to pull this type of data as though it was coming from a single table, and in fact this can be done by creating a view.



    1. Using the query written above as a guide, write and execute the SQL statement that will create a view that will show all employees in both the Seattle and Miami regions (you can use your own naming convention for the view name). Show all the employee number, name, job, salary, commission, department number and location name for each employee (HINT: The location name is in the DEPT table).

    2. Now write a query that will retrieve all the data from the view just created.


    Deliverables
    Submit your completed Lab 3 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.

    Learn More
  3. MIS 562 Week 5 Homework Query Optimization

    MIS 562 Week 5 Homework Query Optimization

    Regular Price: $20.00

    Special Price: $15.00

    MIS 562 Week 5 Homework Query Optimization


    Using the student schema from week 2, provide answers to the following questions.


    Question
    SQL statement or Answer
    1. Generate statistics for the student, enrollment, grade, and zipcode tables (15 pts)


    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 and explain plan 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)

    Learn More
  4. Oracle Database to Track Students Grade ERD

    Oracle Database to Track Students Grade

    Regular Price: $15.00

    Special Price: $12.00

    Oracle Database to Track Students Grade


    Build an Oracle database to track students grade in a class:
    The database tracks
    *Student Information
    *Instructor Information
    *Class information
    *Grading Breakdown
    *Students' Grades


    Different queries will show
    *List of students with semester grades
    *List of students who received an A
    *Average grades
    *Above average grades

    Learn More
  5. MIS 562 Week 7 Homework

    MIS 562 Week 7 Homework Roles and Privileges

    Regular Price: $15.00

    Special Price: $12.00

    MIS 562 Week 7 Homework Roles and Privileges


    Part 1
    Using the following Data Dictionary views write the statements that will perform the following actions. Be sure to test your statements. (Do not use SELECT *)
    ROLE_ROLE_PRIVS
    ROLE_SYS_PRIVS
    ROLE_TAB_PRIVS
    USER_ROLE_PRIVS
    USER_SYS_PRIVS
    USER_TAB_PRIVS


    Question SQL statement or Answer
    1. Determine what privileges your account has been granted through a role. (10 points)


    2. Determine what system privileges your account has been granted. (10 points)


    3.Execute the following statement then determine what table privileges your account has been granted. (15 points)
    Grant select on student to public;


    4. Determine what system privileges the DVONLINE role has. (10 points)

    5. Analyze the following query and write a description of the output it produces. (15 points)
    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)


    6. Determine the top three zip codes where most of the students live. Use an analytical function. The query will product 10 rows. (10 points)



    Part 2
    Analyze the file from Doc Share called utlpwdmg.sql and analyze the code in this file. Write a paragraph that describes what the function performs. What are the inputs parameters, the output parameter and what does the function do? (30 points)

    Learn More
  6. CMIS 420 homework 1 spool file

    CMIS 420 Homework 1 Online Vehicle Sales Database

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 420 Homework 1 Online Vehicle Sales Database


    You are part of a development team with Ace Software, Inc. who has recently been contracted to develop various database capabilities for Online Vehicle Sales, Inc. (OVS). OVS is a startup "dotcom" with about 10 dealership locations in Maryland, Virginia and Washington, D.C. They sell new and used cars (compacts, midsizes and full-sizes), sport utility vehicles (SUVs) and light trucks. Currently their business is just based on customers visiting one of the 10 dealership locations in person, but soon they plan to move the bulk of their business the Internet. Initially they have expressed a desire to have a custom OLTP database, and a custom DSS database, designed and built by your company. Each dealership has a staff of salespersons who assist customers in the purchase of different types of vehicles for which various financing plans are available. New and used vehicles are provided to each dealership based on sales and inventory needs.
    An ERD for a 3NF normalized online transaction processing (OLTP) relational database for this application is provided.
    Using an SQL script file create Oracle tables for the entities shown in the ERD. Use the plural form of the entity name for your table names.
    You can create your database on Nova or any other Oracle system you wish.
    Populate the VEHICLES and CUSTOMERS tables in your OLTP database with at least five rows each. Your DEALERSHIPS, SALESPERSONS and FINANCING_PLANS tables should have at least three rows each. Your SALES table should have at least 10 rows, using a variety of different customers, vehicles, salespersons, dealerships and financing plans. All other tables should have at least one row each. Run SELECT * statements on all your tables after they are populated to show all contents.
    Submit the following in either an SQL*Plus SPOOL file or screen snapshots of the output if using iSQL*Plus:
    1) The names of all your tables from the output of the SQL statement “SELECT table_name FROM user_tables”.
    2) A DESC (i.e. DESCRIBE) of all tables to show the column names.
    3) The contents of all tables from SELECT * statements
    4) Do NOT submit your SQL script files. Only submit the output specified in Steps #1 though #3 above.

    Learn More
  7. CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script

    CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script


    Homework Submission Requirements:
    Homework should be submitted as an attached file in your assignment folder. Text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
    You must name your file to include your first initial of your first name and last name.
    Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.


    Part I Data Modeling
    You are working for country club with thousands of members. You have been tasked with designing a database to keep track of the members and their guests.
    The club keeps track of mail and telephone contact information, name and membership number. When a member joins the club they can become a social member with pool, racket ball and weight room privileges or golf member which includes all of the social member privileges plus access to the golf course.
    Develop, document and design an EER for this situation.
    Submit your word document no later than the due date.


    Part II Lab 3
    In lab 2, you will learn how to edit and run a SQL script in your schema in NOVA Oracle database. You also continue to learn simple SQL commands, such as select and insert ...


    Build and execute SQL script
    In this lab, we learn how to write a SQL script that can be run on NOVA Oracle database to execute the SQL commands included in the file. You are provided with SQL Statements and sqlplus commands to add into the script. What you are requested to do is to reorganize the commands in proper order to avoid runtime errors.
    How to write a script: The script file is a text file which contains executable commands. For SQL script, it contains SQL statements which can be executed against a proper database. For our SQL script that runs against Oracle database. Therefore, we include some of Oracle specific SQLPlus commands that help us make output clear and understandable and allow us to save the output file.
    Get the txt file below that contains SQL and SQLPlus commands. Spool commands are for getting the execution results. Set echo on or off are for getting executed commands printed on the screen before the execution result. Others are all SQLcommands. These commands should work as they are without modification.
    Download lab3.txt
    There are a few simple suggested rules that can make your script robust.
    Use remark, rem, to add comment or reminder in the script anything followed rem is not executed.
    Spool on command should be the first command and followed
    Set echo on should be before any execution commands begin.
    Set echo off and spool off at the end of the file.
    For SQL DDL commands. Drop the table before create it. this gives you clean database to begin with. The table created last should be dropped first.
    For SQL DML, delete the records first before insert them into the table. This allow you begin with empty table. delete the records from child table first if primary key and foreign key constraints exist between the two tables. However, insert into parent table first then insert into child table. Don't forget the child record needs a refernce key value from the parent table. Always end with commit command to save the data.
    you can use vi text editor to edit the script on NOVA or use a text editor at local to create and edit the script and upload to Nova using sFTP. The script MUST be saved with .sql extension in order for it to be run in SQLPLus
    After you successfully create and executed your script. Transfer your output file back and Submit it through assignment for grading.

    Learn More
  8. CMIS 320 Lab 2 Homework 2 Data modeling and Sample database

    CMIS 320 Lab 2 Homework 2 Data modeling and Sample database

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 320 Lab 2 Homework 2 Data modeling and Sample database


    Homework Submission Requirements:
    Homework should be submitted as an attached file in your assignment folder. text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
    You must name your file to include your first initial of your first name and last name.
    Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.


    Part I Data Modeling
    For each of the following descriptions:
    a. A piano manufacturer wants to track all pianos it makes. Each piano has a unique serial number and a manufacturing completion date. Each instrument represents exactly one piano model, all of which have an identification number and model. The company produces thousands of pianos of a certain model, and the design is specified before any single piano exists.
    b. A vendor builds multiple types of tablet computers. Each has a type identification number and a name. The key specifications for each type include amount of storage and display type. The company uses multiple processor types, exactly one of which is used for a specific tablet type. The same processor can be used in multiple types of tablets. Each processor has a manufacturer and a manufacturer's unique code that identifies it.
    perform the following tasks:
    1. Identify the degree and cardinalities of the relationship.
    2. Express the relationships graphically with an E-R diagram.
    Document your work into a single, well-organized, well-written word document and submit no later than due date.


    Part II Lab 2
    In lab 2, you will learn how to run a SQL script to create a sample database in your schema in NOVA Oracle database. You also start to learn simple SQL commands, such as select and insert


    Click here to open Lab 2 instruction page


    This part of lab is to learn to run a SQL script on NOVA Oracle database server, and spool screen output to a text file. These techniques will be used when you work on your project 1 and 2. Download script file. This is sql script file containing completed sql commands to create tables and constraints and populate data to each table. The database you create is called JustLee Book Store. The code should be bug free.
    Download lab2.sql script
    Start spool command and run the script:
    Log on NOVA, make a dir named scripts using command mkdir scripts
    Then cd to newly created script directory
    Upload your script to scripts directory using FTP
    (I downloaded a free FTP client CORE FTP LE) and use it to upload and download file)
    Now you have a newly create dir and your script is uploaded. You are ready to run your script.
    Using SQL*Plus, log on NOVA Oracle database with your account
    At SQL prompt,Type the following command to turn on spool function: SQL>spool spool01.lst
    spool01.lst is the spool file name
    Then use this command to run the script: SQL>@lab2.sql
    The command will execute all the SQL commands inside the script and output will be spooled to spool01.lst
    In the script, there are drop table statement as well. When first run, these commands will fail because the tables are not created yet.
    You can run the script multiple times without any problems.
    When you finish the execution, issue command SQL>spool off
    This command will end spool function and save output to the file you defined
    When you have done all of these. Use FTP get spool01.lst to your local computer and submit it as part of homework 2
    You should know how to perform the following tasks using sqlplus on NOVA Oracle database after you complete this lab:
    Using FTP to upload or download a file (scripts...)
    Using sqlplus to log on to NOVA Oracle database
    Using sqlplus command, spool to output screen output to a text file and transfer the file back to your local PC.
    Understand the steps taken place when you perform FTP file transfer and Spooling your output.
    Test the database you create and learn SQL queries.
    You need to spool the execution results of the following commands.
    Do a database query: select table_name from user_tables; USER_TABLES is a database system view that allow users to see the tables they created in their own schema.
    You should be able to list the tables you just created
    use this query: select * from "table_name from the above query"; you can retrieve the data in these tables one a time
    use this query: select count(*) from "table_name from the above query"; you can retrieve the record counts in these tables one a time
    Use SQL*Plus command "desc books" to check columns and their datatypes. And insert one record into the table. Use SQL command "commit" to save the record into your database.
    When you have done all of these. Use FTP get spooled file to your local computer and submit it as part of homework 2

    Learn More
  9. Joan Casteel Oracle 11g SQL Chapters 13 Multiple Choice Questions Solution

    Joan Casteel Oracle 11g SQL Chapters 13 Multiple Choice Questions Solution

    Regular Price: $12.00

    Special Price: $10.00

    Joan Casteel Oracle 11g SQL Chapters 13 Multiple Choice Questions Solution


    To answer the following questions, refer to the tables in the JustLee Books database.
    Questions 1–7 are based on successful execution of the following statement:
    CREATE VIEW changeaddress
    AS SELECT customer#, lastname, firstname, order#,
    shipstreet, shipcity, shipstate, shipzip
    FROM customers JOIN orders USING (customer#)
    WHERE shipdate IS NULL
    WITH CHECK OPTION;


    1. Which of the following statements is correct?
    a. No DML operations can be performed on the CHANGEADDRESS view.
    b. The CHANGEADDRESS view is a simple view.
    c. The CHANGEADDRESS view is a complex view.
    d. The CHANGEADDRESS view is an inline view.


    2. Assuming there’s only a primary key, and FOREIGN KEY constraints exist on the underlying tables, which of the following commands returns an error message?
    a. UPDATE changeaddress SET shipstreet = '958 ELM ROAD' WHERE customer# = 1020;
    b. INSERT INTO changeaddress VALUES (9999, 'LAST', 'FIRST', 9999, '123 HERE AVE', 'MYTOWN', 'AA', 99999);
    c. DELETE FROM changeaddress WHERE customer# = 1020;
    d. all of the above
    e. only a and b
    f. only a and c
    g. none of the above


    3. Which of the following is the key-preserved table for the CHANGEADDRESS view?
    a. CUSTOMERS table
    b. ORDERS table
    c. Both tables together serve as a composite key-preserved table.
    d. none of the above


    4. Which of the following columns serves as the primary key for the CHANGEADDRESS view?
    a. Customer#
    b. Lastname
    c. Firstname
    d. Order#
    e. Shipstreet


    5. If a record is deleted from the CHANGEADDRESS view based on the Customer# column, the customer information is then deleted from which underlying table?
    a. CUSTOMERS
    b. ORDERS
    c. CUSTOMERS and ORDERS
    d. Neither—the DELETE command can’t be used on the CHANGEADDRESS view.


    6. Which of the following is correct?
    a. ROWNUM can’t be used with the view because it isn’t included in the results the subquery returns.
    b. The view is a simple view because it doesn’t include a group function or a GROUP BY clause.
    c. The data in the view can’t be displayed in descending order by customer number because an ORDER BY clause isn’t allowed when working with views.
    d. all of the above
    e. none of the above


    7. Assuming one of the orders has shipped, which of the following is true?
    a. The CHANGEADDRESS view can’t be used to update an order’s ship date because of the WITH CHECK OPTION constraint.
    b. The CHANGEADDRESS view can’t be used to update an order’s ship date because the Shipdate column isn’t included in the view.
    c. The CHANGEADDRESS view can’t be used to update an order’s ship date because the ORDERS table is not the key-preserved table.
    d. The CHANGEADDRESS view can’t be used to update an order’s ship date because the UPDATE command can’t be used on data in the view.


    Questions 8–12 are based on successful execution of the following command:
    CREATE VIEW changename
    AS SELECT customer#, lastname, firstname
    FROM customers
    WITH CHECK OPTION;
    Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.


    8. Which of the following is a correct statement?
    a. No DML operations can be performed on the CHANGENAME view.
    b. The CHANGENAME view is a simple view.
    c. The CHANGENAME view is a complex view.
    d. The CHANGENAME view is an inline view.


    9. Which of the following columns serves as the primary key for the CHANGENAME view?
    a. Customer#
    b. Lastname
    c. Firstname
    d. The view doesn’t have or need a primary key.


    10. Which of the following DML operations could never be used on the CHANGENAME view?
    a. INSERT
    b. UPDATE
    c. DELETE
    d. All of the above are valid DML operations for the CHANGENAME view.


    11. The INSERT command can’t be used with the CHANGENAME view because:
    a. A key-preserved table isn’t included in the view.
    b. The view was created with the WITH CHECK OPTION constraint.
    c. The inserted record couldn’t be accessed by the view.
    d. None of the above—an INSERT command can be used on the table as long as the PRIMARY KEY constraint isn’t violated.


    12. If the CHANGENAME view needs to include the customer’s zip code as a means of verifying the change (that is, to authenticate the user), which of the following is true?
    a. The CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.
    b. The ALTER VIEW . . . ADD COLUMN command can be used to add the necessary column to the existing view.
    c. The CHANGENAME view can be dropped, and then the CREATE VIEW command can be used to re-create the view with the necessary column included in the new view.
    d. All of the above can be performed to include the customer’s zip code in the view.
    e. Only a and b include the customer’s zip code in the view.
    f. Only a and c include the customer’s zip code in the view.
    g. None of the above includes the customer’s zip code in the view.


    13. Which of the following DML operations can’t be performed on a view containing a group function?
    a. INSERT
    b. UPDATE
    c. DELETE
    d. All of the above can be performed on a view containing a group function.
    e. None of the above can be performed on a view containing a group function.


    14. You can’t perform any DML operations on which of the following?
    a. views created with the WITH READ ONLY option
    b. views that include the DISTINCT keyword
    c. views that include a GROUP BY clause
    d. All of the above allow DML operations.
    e. None of the above allow DML operations.


    15. A TOP-N analysis is performed by determining the rows with:
    a. the highest ROWNUM values
    b. a ROWNUM value greater than or equal to N
    c. the lowest ROWNUM values
    d. a ROWNUM value less than or equal to N


    16. To assign names to the columns in a view, you can do which of the following?
    a. Assign aliases in the subquery, and the aliases are used for the column names.
    b. Use the ALTER VIEW command to change column names.
    c. Assign names for up to three columns in the CREATE VIEW clause before the subquery is listed in the AS clause.
    d. None of the above—columns can’t be assigned names for a view; they must keep their original names.


    17. Which of the following is correct?
    a. The ORDER BY clause can’t be used in the subquery of a CREATE VIEW command.
    b. The ORDER BY clause can’t be used in an inline view.
    c. The DISTINCT keyword can’t be used in an inline view.
    d. The WITH READ ONLY option must be used with an inline view.


    18. If you try to add a row to a complex view that includes a GROUP BY clause, you get which of the following error messages?
    a. virtual column not allowed here
    b. data manipulation operation not legal on this view
    c. cannot map to a column in a non-key-preserved table
    d. None of the above—no error message is returned.


    19. A simple view can contain which of the following?
    a. data from one or more tables
    b. an expression
    c. a GROUP BY clause for data retrieved from one table
    d. five columns from one table
    e. all of the above
    f. none of the above


    20. A complex view can contain which of the following?
    a. data from one or more tables
    b. an expression
    c. a GROUP BY clause for data retrieved from one table
    d. five columns from one table
    e. all of the above
    f. none of the above

    Learn More
  10. Joan Casteel Oracle 11g SQL Chapters 12 Multiple Choice Questions Solution

    Joan Casteel Oracle 11g SQL Chapters 12 Multiple Choice Questions Solution

    Regular Price: $12.00

    Special Price: $10.00

    Joan Casteel Oracle 11g SQL Chapters 12 Multiple Choice Questions Solution


    To answer these questions, refer to the tables in the JustLee Books database.
    1. Which query identifies customers living in the same state as the customer named Leila Smith?
    a. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH');
    b. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' OR firstname = 'LEILA');
    c. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA' ORDER BY customer);
    d. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA');


    2. Which of the following is a valid SELECT statement?
    a. SELECT order# FROM orders WHERE shipdate = SELECT shipdate FROM orders WHERE order# = 1010;
    b. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders) AND order# = 1010;
    c. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);
    d. SELECT order# FROM orders HAVING shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);


    3. Which of the following operators is considered a single-row operator?
    a. IN
    b. ALL
    c. <>
    d. <>ALL


    4. Which of the following queries determines which customers have ordered the same books
    as customer 1017?
    a. SELECT order# FROM orders WHERE customer# = 1017;
    b. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn = (SELECT isbn FROM orderitems WHERE customer# = 1017);
    c. SELECT customer# FROM orders WHERE order# = (SELECT order# FROM orderitems WHERE customer# = 1017);
    d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);


    5. Which of the following statements is valid?
    a. SELECT title FROM books WHERE retail <(SELECT cost FROM books WHERE isbn = '9959789321');
    b. SELECT title FROM books WHERE retail = (SELECT cost FROM books WHERE isbn = '9959789321' ORDER BY cost);
    c. SELECT title FROM books WHERE category IN (SELECT cost FROM orderitems WHERE isbn = '9959789321');
    d. none of the above statements


    6. Which of the following statements is correct?
    a. If a subquery is used in the outer query's FROM clause, the data in the temporary table can't be referenced by clauses used in the outer query.
    b. The temporary table created by a subquery in the outer query's FROM clause must be assigned a table alias, or it can't be joined with another table by using the JOIN keyword.
    c. If a temporary table is created through a subquery in the outer query's FROM clause, the data in the temporary table can be referenced by another clause in the outer query.
    d. none of the above


    7. Which of the following queries identifies other customers who were referred to JustLee Books by the same person who referred Jorge Perez?
    a. SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
    b. SELECT referred FROM customers WHERE (customer#, referred) = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
    c. SELECT referred FROM customers WHERE (customer#, referred) IN (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
    d. SELECT customer# FROM customers WHERE customer# = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');


    8. In which of the following situations is using a subquery suitable?
    a. when you need to find all customers living in a particular region of the country
    b. when you need to find all publishers who have toll-free telephone numbers
    c. when you need to find the titles of all books shipped on the same date as an order placed by a particular customer
    d. when you need to find all books published by Publisher 4


    9. Which of the following queries identifies customers who have ordered the same books as customers 1001 and 1005?
    a. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005));
    b. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn <ANY (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005));
    c. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1001 OR 1005));
    d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# IN (1001, 1005));


    10. Which of the following operators is used to find all values greater than the highest value returned by a subquery?
    a. >ALL
    b. <ALL
    c. >ANY
    d. <ANY
    e. IN


    11. Which query determines the customers who have ordered the most books from JustLee Books?
    a. SELECT customer# FROM orders JOIN orderitems USING(order#) HAVING SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orders JOIN orderitems USING(order#) GROUP BY customer#) GROUP BY customer#;
    b. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);
    c. SELECT customer# FROM orders WHERE MAX(SUM(quantity)) = (SELECT MAX(SUM(quantity) FROM orderitems GROUP BY order#);
    d. SELECT customer# FROM orders HAVING quantity = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);


    12. Which of the following statements is correct?
    a. The IN comparison operator can't be used with a subquery that returns only one row of results.
    b. The equals (=) comparison operator can't be used with a subquery that returns more than one row of results.
    c. In an uncorrelated subquery, statements in the outer query are executed first, and then statements in the subquery are executed.
    d. A subquery can be nested only in the outer query's SELECT clause.


    13. What is the purpose of the following query?
    SELECT isbn, title FROM books
    WHERE (pubid, category) IN (SELECT pubid, category
    FROM books WHERE title LIKE '%ORACLE%');
    a. It determines which publisher published a book belonging to the Oracle category and then lists all other books published by that same publisher.
    b. It lists all publishers and categories containing the value ORACLE.
    c. It lists the ISBN and title of all books belonging to the same category and having the same publisher as any book with the phrase ORACLE in its title.
    d. None of the above. The query contains a multiple-row operator, and because the inner query returns only one value, the SELECT statement will fail and return an error message.


    14. A subquery must be placed in the outer query's HAVING clause if:
    a. The inner query needs to reference the value returned to the outer query.
    b. The value returned by the inner query is to be compared to grouped data in the outer query.
    c. The subquery returns more than one value to the outer query.
    d. None of the above. Subqueries can't be used in the outer query's HAVING clause.


    15. Which of the following SQL statements lists all books written by the author of The Wok Way to Cook?
    a. SELECT title FROM books WHERE isbn IN (SELECT isbn FROM bookauthor HAVING authorid IN 'THE WOK WAY TO COOK);
    b. SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK');
    c. SELECT title FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK);
    d. SELECT isbn FROM bookauthor HAVING authorid = SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK';


    16. Which of the following statements is correct?
    a. If the subquery returns only a NULL value, the only records returned by an outer query are those containing an equivalent NULL value.
    b. A multiple-column subquery can be used only in the outer query's FROM clause.
    c. A subquery can contain only one condition in its WHERE clause.
    d. The order of columns listed in the SELECT clause of a multiple-column subquery must be in the same order as the corresponding columns listed in the outer query's WHERE clause.


    17. In a MERGE statement, an INSERT is placed in which conditional clause?
    a. USING
    b. WHEN MATCHED
    c. WHEN NOT MATCHED
    d. INSERTs aren't allowed in a MERGE statement.


    18. Given the following query, which statement is correct?
    SELECT order# FROM orders
    WHERE order# IN (SELECT order# FROM orderitems
    WHERE isbn = '9959789321');
    a. The statement doesn't execute because the subquery and outer query don't reference the same table.
    b. The outer query removes duplicates in the subquery's Order# list.
    c. The query fails if only one result is returned to the outer query because the outer query's WHERE clause uses the IN comparison operator.
    d. No rows are displayed because the ISBN in the WHERE clause is enclosed in single quotation marks.


    19. Given the following SQL statement, which statement is most accurate?
    SELECT customer# FROM customers
    JOIN orders USING(customer#)
    WHERE shipdate-orderdate IN
    (SELECT MAX(shipdate-orderdate) FROM orders
    WHERE shipdate IS NULL);
    a. The SELECT statement fails and returns an Oracle error message.
    b. The outer query displays no rows in its results because the subquery passes a NULL value to the outer query.
    c. The customer number is displayed for customers whose orders haven't yet shipped.
    d. The customer number of all customers who haven't placed an order are displayed.


    20. Which operator is used to process a correlated subquery?
    a. EXISTS
    b. IN
    c. LINK
    d. MERGE

    Learn More

Items 1 to 10 of 46 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

Grid  List 

Set Descending Direction