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 Ascending Direction
  1. ITSE 2309 LAB 2 More SQL Queries and Modification

    ITSE 2309 LAB 2 More SQL Queries and Modification

    Regular Price: $12.00

    Special Price: $10.00

    ITSE 2309 LAB 2 More SQL Queries and Modification


    Oracle 11g SQL–Chapters- 3,6,8,9,11,12,
    You will continuing using items created in Lab 1


    Lab 2a -- Problems 1–4,
    1. For each customer, list each stock item ordered,
    1) the manufacturer,
    2) the quantity ordered, and
    3) the total price paid.
    Include the following columns in the order given below:
    - From Customer Table: Company
    - From Stock Table: Description
    - From the Manufact Table: Manu_Name
    - From the Items Table: Quantity, Total Price
    Order the output by Company and Description.
    Submit/hand in Output from SQL query


    2. List all orders with a shipping date between December 25, 1999 and January 5, 2000
    Include
    1) the Order Number,
    2) Order Date,
    3) Customer company name, and
    4) Shipping Date.
    Order by
    Customer Company Name and Order Number.
    Submit/hand in Output from SQL query


    3. Count the number of customers who do not have any orders placed.
    Submit/hand in Output from SQL query


    4. List all customers –
    I) Who are ordering equipment whose description begins with 'tennis' or 'volleyball'.
    II )Include
    1) Customer number,
    2) Stock number, and
    3) Description.
    Submit/hand in Output from SQL query
    Do not repeat any rows.


    Lab 2b Problems 5, 6, 7 and 8
    5. Use the following SQL CREATE commands to CREATE the following tables in your
    CREATE TABLE Professor
    (Prof_ID NUMBER(3) Constraint pk_Professor Primary Key,
    Prof_Lname VARCHAR2(15) NOT NULL,
    Prof_Hiredate DATE,
    Prof_Sal NUMBER(8,2),
    Prof_Dept CHAR(6),
    );
    CREATE TABLE Student
    (Stu_ID NUMBER(4) Constraint pk_Student Primary Key,
    Stu_Lname VARCHAR2(15) NOT NULL,
    Stu_Major CHAR(6),
    Stu_CredHrs NUMBER(4),
    Stu_GradePts NUMBER(5),
    Prof_ID NUMBER(3),
    CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)
    REFERENCES Professor
    );
    Submit/Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables created.


    6. Insert the following data into the tables created above using SQL INSERT commands.
    Professor Table:
    Prof_ID Prof_Lname Prof_Hiredate Prof_Sal Prof_Dept
    123 Hilbert 20-MAY-1992 58000.00 MATH
    243 Newell 15-JUL-1997 65500.00 CMPSCI
    389 Lessing 04-APR-1988 40250.00 ENG
    Student Table:
    Stu_ID Stu_Lname Stu_Major Stu_CredHrs Stu_GradePts Prof_ID
    2001 Parker CMPSCI 52 160 243
    2166 Smith ENG 30 75 389
    3200 Garcia MATH 62 248 123
    4520 Smith CMPSCI 45 157 NULL
    BE SURE TO ISSUE A COMMIT AFTER TABLE MODIFICATION COMMANDS HAVE BEEN RUN SUCCESSFULLY.
    Submit a
    Listing of each INSERT command,
    The systems response and the resulting tables after the INSERTS are completed
    (Example: SELECT * FROM Student;).


    7. Perform the following SQL DELETE statements. Be sure to do them in order.
    Issue a COMMIT command after all DELETEs have run.
    a. Try to delete Professor 389. What message do you get? ___________________________
    b. Delete Student 2166.
    c. Now Delete Professor 389. Explain why the first attempt in a. was unsuccessful, and this time the DELETE was successful.
    Submit/hand in : A listing of the DELETE statements.
    The answers to questions a. b. and c.
    A listing of the two tables after the deletes have run.


    8. Perform the following UPDATE commands.
    Issue a COMMIT command after all UPDATEs have run.
    a. Replace the value of the Prof_ID for Student 4520 with 243.
    b. Add 10% to the salary for each professor
    Submit/hand in : A listing of the UPDATE statements
    A listing of the two tables after the UPDATEs have run.

    Learn More
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. Joan Casteel Oracle 11g SQL Chapters 11 Multiple Choice Solution

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

    Regular Price: $12.00

    Special Price: $10.00

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


    To answer these questions, refer to the tables in the JustLee Books database.
    1. Which of the following statements is true?
    a. The MIN function can be used only with numeric data.
    b. The MAX function can be used only with date values.
    c. The AVG function can be used only with numeric data.
    d. The SUM function can’t be part of a nested function.


    2. Which of the following is a valid SELECT statement?
    a. SELECT AVG(retail-cost) FROM books GROUP BY category;
    b. SELECT category, AVG(retail-cost) FROM books;
    c. SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-cost) > 8.56 GROUP BY category;
    d. SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit > 8.56;


    3. Which of the following statements is correct?
    a. The WHERE clause can contain a group function only if the function isn’t also listed in the SELECT clause.
    b. Group functions can’t be used in the SELECT, FROM, or WHERE clauses.
    c. The HAVING clause is always processed before the WHERE clause.
    d. The GROUP BY clause is always processed before the HAVING clause.


    4. Which of the following is not a valid SQL statement?
    a. SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4;
    b. SELECT MIN(pubdate) FROM books WHERE category = 'COOKING';
    c. SELECT COUNT(*) FROM orders WHERE customer# = 1005;
    d. SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;


    5. Which of the following statements is correct?
    a. The COUNT function can be used to determine how many rows contain a NULL value.
    b. Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause.
    c. The HAVING clause restricts which rows are processed.
    d. The WHERE clause determines which groups are displayed in the query results.
    e. none of the above


    6. Which of the following is a valid SQL statement?
    a. SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer# = 1001;
    b. SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#;
    c. SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#);
    d. SELECT customer#, COUNT(order#) FROM orders GROUP BY order#;


    7. Which of the following SELECT statements lists only the book with the largest profit?
    a. SELECT title, MAX(retail-cost) FROM books GROUP BY title;
    b. SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-cost);
    c. SELECT title, MAX(retail-cost) FROM books;
    d. none of the above


    8. Which of the following is correct?
    a. A group function can be nested inside a group function.
    b. A group function can be nested inside a single-row function.
    c. A single-row function can be nested inside a group function.
    d. a and b
    e. a, b, and c


    9. Which of the following functions is used to calculate the total value stored in a specified column?
    a. COUNT
    b. MIN
    c. TOTAL
    d. SUM
    e. ADD


    10. Which of the following SELECT statements lists the highest retail price of all books in the Family category?
    a. SELECT MAX(retail) FROM books WHERE category = 'FAMILY';
    b. SELECT MAX(retail) FROM books HAVING category = 'FAMILY';
    c. SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail);
    d. none of the above


    11. Which of the following functions can be used to include NULL values in calculations?
    a. SUM
    b. NVL
    c. MAX
    d. MIN


    12. Which of the following is not a valid statement?
    a. You must enter the ALL keyword in a group function to include all duplicate values.
    b. The AVG function can be used to find the average calculated difference between two dates.
    c. The MIN and MAX functions can be used on any type of data.
    d. all of the above
    e. none of the above


    13. Which of the following SQL statements determines how many total customers were referred by other customers?
    a. SELECT customer#, SUM(referred) FROM customers GROUP BY customer#;
    b. SELECT COUNT(referred) FROM customers;
    c. SELECT COUNT(*) FROM customers;
    d. SELECT COUNT(*) FROM customers WHERE referred IS NULL;


    Use the following SELECT statement to answer questions 14–18:
    1 SELECT customer#, COUNT(*)
    2 FROM customers JOIN orders USING (customer#)
    3 WHERE orderdate > '02-APR-09'
    4 GROUP BY customer#
    5 HAVING COUNT(*) > 2;


    14. Which line of the SELECT statement is used to restrict the number of records the query processes?
    a. 1
    b. 3
    c. 4
    d. 5


    15. Which line of the SELECT statement is used to restrict groups displayed in the query results?
    a. 1
    b. 3
    c. 4
    d. 5


    16. Which line of the SELECT statement is used to group data stored in the database?
    a. 1
    b. 3
    c. 4
    d. 5


    17. Because the SELECT clause contains the Customer# column, which clause must be included for the query to execute successfully?
    a. 1
    b. 3
    c. 4
    d. 5


    18. The COUNT(*) function in the SELECT clause is used to return:
    a. the number of records in the specified tables
    b. the number of orders placed by each customer
    c. the number of NULL values in the specified tables
    d. the number of customers who have placed an order


    19. Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?
    a. COUNT function
    b. MAX function
    c. MIN function
    d. STDDEV function
    e. VARIANCE function


    20. Which of the following is not a valid SELECT statement?
    a. SELECT STDDEV(retail) FROM books;
    b. SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#;
    c. SELECT order#, TO_CHAR(SUM(retail),'999.99') FROM orderitems JOIN books USING (isbn) GROUP BY order#;
    d. SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;

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

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

    Regular Price: $12.00

    Special Price: $10.00

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


    To answer the following questions, refer to the tables in the JustLee Books database.
    1. Which of the following is a valid SQL statement?
    a. SELECT SYSDATE;
    b. SELECT UPPER(Hello) FROM dual;
    c. SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual;
    d. all of the above
    e. none of the above


    2. Which of the following functions can be used to extract a portion of a character string?
    a. EXTRACT
    b. TRUNC
    c. SUBSTR
    d. INITCAP


    3. Which of the following determines how long ago orders that haven’t shipped were received?
    a. SELECT order#, shipdate-orderdate delay FROM orders;
    b. SELECT order#, SYSDATE – orderdate FROM orders WHERE shipdate IS NULL;
    c. SELECT order#, NVL(shipdate, 0) FROM orders WHERE orderdate is NULL;
    d. SELECT order#, NULL(shipdate) FROM orders;


    4. Which of the following SQL statements produces “Hello World” as the output?
    a. SELECT "Hello World" FROM dual;
    b. SELECT INITCAP('HELLO WORLD') FROM dual;
    c. SELECT LOWER('HELLO WORLD') FROM dual;
    d. both a and b
    e. none of the above


    5. Which of the following functions can be used to substitute a value for a NULL value?
    a. NVL
    b. TRUNC
    c. NVL2
    d. SUBSTR
    e. both a and d
    f. both a and c


    6. Which of the following is not a valid format argument for displaying the current time?
    a. 'HH:MM:SS'
    b. 'HH24:SS'
    c. 'HH12:MI:SS'
    d. All of the above are valid.


    7. Which of the following lists only the last four digits of the contact person’s phone number at American Publishing?
    a. SELECT EXTRACT(phone, -4, 1) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING';
    b. SELECT SUBSTR(phone, -4, 1) FROM publisher WHERE name = 'AMERICAN PUBLISHING';
    c. SELECT EXTRACT(phone, -1, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';
    d. SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';


    8. Which of the following functions can be used to determine how many months a book has been available?
    a. MONTH
    b. MON
    c. MONTH_BETWEEN
    d. none of the above


    9. Which of the following displays the order date for order 1000 as 03/31?
    a. SELECT TO_CHAR(orderdate, 'MM/DD') FROM orders WHERE order# = 1000;
    b. SELECT TO_CHAR(orderdate, 'Mth/DD') FROM orders WHERE order# = 1000;
    c. SELECT TO_CHAR(orderdate, 'MONTH/YY') FROM orders WHERE order# = 1000;
    d. both a and b
    e. none of the above


    10. Which of the following functions can produce different results, depending on the value of a specified column?
    a. NVL
    b. DECODE
    c. UPPER
    d. SUBSTR


    11. Which of the following SQL statements is not valid?
    a. SELECT TO_CHAR(orderdate, '99/9999') FROM orders;
    b. SELECT INITCAP(firstname), UPPER(lastname) FROM customers;
    c. SELECT cost, retail, TO_CHAR(retail-cost, '$999.99') profit FROM books;
    d. all of the above


    12. Which function can be used to add spaces to a column until it’s a specific width?
    a. TRIML
    b. PADL
    c. LWIDTH
    d. none of the above


    13. Which of the following SELECT statements returns 30 as the result?
    a. SELECT ROUND(24.37, 2) FROM dual;
    b. SELECT TRUNC(29.99, 2) FROM dual;
    c. SELECT ROUND(29.01, -1) FROM dual;
    d. SELECT TRUNC(29.99, -1) FROM dual;


    14. Which of the following is a valid SQL statement?
    a. SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;
    b. SELECT ROUND(TRUNC(125.38, 0) FROM dual;
    c. SELECT LTRIM(LPAD(state, 5, ' '), 4, -3, "*") FROM dual;
    d. SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1) FROM dual;


    15. Which of the following functions can’t be used to convert the letter case of a character string?
    a. UPPER
    b. LOWER
    c. INITIALCAP
    d. All of the above can be used for case conversion.


    16. Which of the following format elements causes months to be displayed as a three-letter abbreviation?
    a. MMM
    b. MONTH
    c. MON
    d. none of the above


    17. Which of the following SQL statements displays a customer’s name in all uppercase
    characters?
    a. SELECT UPPER('firstname', 'lastname') FROM customers;
    b. SELECT UPPER(firstname, lastname) FROM customers;
    c. SELECT UPPER(lastname, ',' firstname) FROM customers;
    d. none of the above


    18. Which of the following functions can be used to display the character string FLORIDA in the query results whenever FL is entered in the State field?
    a. SUBSTR
    b. NVL2
    c. REPLACE
    d. TRUNC
    e. none of the above


    19. What’s the name of the table provided by Oracle 11g for completing queries that don’t involve a table?
    a. DUMDUM
    b. DUAL
    c. ORAC
    d. SYS


    20. If an integer is multiplied by a NULL value, the result is:
    a. an integer
    b. a whole number
    c. a NULL value
    d. None of the above—a syntax error message is returned.

    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 Ascending Direction