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 11 to 20 of 63 total

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

Grid  List 

Set Ascending Direction
  1. 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
  2. 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
  3. 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
  4. MSCD610 Week 7 Logical ERD

    MSCD610 Week 7 Course Project Details Hotel Database

    Regular Price: $30.00

    Special Price: $25.00

    MSCD610 Week 7 Course Project Details Hotel Database


    The course project is to develop a Data Model and Database Design for a set of business case requirements. Students should informally review their ER model with the facilitator in week 4 or 5.


    This project should follow a top-down database design process and produce the following outputs:
    1. A set of business information requirements in the form of a business case abstract.


    2. A complete ERD that models those requirements.
    This model should include the following:
    a. A definition of each entity
    b. The unique identifier for each entity
    c. The attributes associated with each entity
    d. The relationships between the entities including their cardinality, optionally, and names


    3. A physical implementation of a database from the logical design (ERD).
    The tables should be normalized to 3NF. For this design include:
    a. The name for each table
    b. The primary key for the table, and any secondary keys
    c. Any foreign keys
    d. A data dictionary
    e. Any sample data available


    4. The DDL scripts for implementing the physical database design for this database. Include the referential integrity constraints for these tables. Also include the DDL for any needed indexes and/or views. If necessary, indicate any database tuning which is anticipated on this database.


    5. Create the designed Oracle database, and load it with sample data. Then print the definition of the tables, indexes, and any views. Print sample report(s) showing the query definitions in the business requirements have been met.

    Learn More
  5. MSCD610 Exam SQL

    MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

    Regular Price: $30.00

    Special Price: $25.00

    MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel


    True/False (2 points each)
    Indicate whether the sentence or statement is true or false.
    1. A one-to-many relationship means that an occurrence of a specific entity can only exist once in each table.
    2. A table name can consist of numbers, letters, and blank spaces.
    3. A constraint can only be created as part of the CREATE TABLE command.
    4. The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
    5. If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
    6. By default, the lowest value that can be generated by a sequence is 0.
    7. Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
    8. Data stored in multiple tables can be reconstructed through the use of an ORDER BY clause.
    9. Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
    10. By default, the column headings displayed in a report are in upper-case characters.


    Multiple Choice (3 points each)
    Identify the letter of the choice that best completes the statement or answers the question.
    11. Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
    a. one-to-many c. one-to-all
    b. many-to-many d. one-to-one


    Contents of the BOOKS table
    12. Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
    a. SELECT title, cost+.20 "New Retail Price" FROM books;
    b. SELECT title, cost*.20 "New Retail Price" FROM books;
    c. SELECT title, cost*1.20 "New Retail Price" FROM books;
    d. none of the above


    Structure of the CUSTOMERS table
    13. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
    a. ALTER TABLE customers
    MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
    b. ALTER TABLE customers
    MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
    c. ALTER TABLE customers
    MODIFY (city (+8), lastname (+4));
    d. ALTER TABLE customers
    MODIFY (city (20), lastname (14));


    14. Which of the following statements about the FOREIGN KEY constraint is incorrect?
    a. The constraint exists between two tables, called the parent table and the child table.
    b. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
    c. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
    d. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.


    15. Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
    a. ALTER USER rthomas PASSWORD EXPIRE ;
    b. ALTER USER rthomas CHANGE PASSWORD;
    c. ALTER USER rthomas UPDATE PASSWORD;
    d. ALTER USER rthomas EXPIRE PASSWORD;


    16. To instruct Oracle to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
    a. Asc c. ascending
    b. A d. either a or c


    17. Which of the following is an accurate statement?
    a. When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
    b. When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user's session.
    c. When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
    d. none of the above


    18. Which of the following functions allows for different options, depending upon whether a NULL value exists?
    a. NVL c. IFNVL
    b. IFNL d. NVL2


    Contents of the ORDERS table
    19. Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
    a. SELECT order#, COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL;
    b. SELECT order#, COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL
    GROUP BY order#;
    c. SELECT COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL;
    d. SELECT COUNT(*)
    FROM orders
    WHERE shipdate IS NULL;


    20. Which of the following is not an example of formatting code available with the FORMAT option of the COLUMN command?
    a. Z
    b. 9
    c. ,
    d. .



    Completion (4 points each)
    Complete each sentence or statement.
    21. A(n) ____________________ is a group of interrelated files.
    22. In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle.
    23. If a constraint applies to more than one column, the constraint must be created at the ______Table______________ level.
    24. After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
    25. The ____________________ function is used to round numeric fields to a stated position.



    SQL
    26. (5 points) Consider an employee database with relations where the primary keys are underlined defined as:
    EMPLOYEE (employee name, street, city)
    WORKS (employee name, company_name, salary)
    A – Using sql functions as appropriate, write a query to find companies whose employees earn a higher salary, on average, than the average salary at ABC Corporation


    27. (7 points) Write a SQL script to create this relational schema. Execute the script against the ORACLE database to implement physical database tables. Integrity constraints are listed below.
    EMPLOYEE (name, SSN, BDate, Sex, Salary, SuperSSN, DNO)
    DEPARTMENT (DName, DNumber, MGRSSN, MGRStartDate)
    DEPTLOCATION (DNumber, DLocation)
    PROJECT (PName, PNumber, PLocation, DNum)
    WORKSON (ESSN, PNO, Hours)
    DEPENDENT (ESSN, DEPENDENT_NAME, Sex, BDate, Relationship)


    Integrity Constraints:
    Primary key = Foreign Key
    EMPLOYEE.SSN = DEPENDENT.ESSN
    EMPLOYEE.SSN = WORKSON.ESSN
    EMPLOYEE.SSN = DEPARTMENT.MGRSSN
    EMPLOYEE.SSN = EMPLOYEE.SuperSSN
    DEPARTMENT.DNumber = EMPLOYEE.DNO
    DEPARTMENT.DNumber = DEPTLOCATION.DNumber
    DEPARTMENT.DNumber = PROJECT.DNum
    PROJECT.PNumber = WORKSON.PNO


    28. (18 points) Write SQL syntax to resolve the following queries.
    - Find the names of all employees who are directly supervised by the employee named “John Doe”
    - List the name of employees whose salary is greater than the average salary of his or her corresponding department
    - For each department, retrieve the department name and the average salary of all employees working in that department.

    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 420 Homework 2 Spool File

    CMIS 420 Homework 2 Online Vehicle Sales Database

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 420 Homework 2 Online Vehicle Sales Database


    1) Complete the creation and population of your Homework #1 tables and include all necessary integrity constraints including primary keys, foreign keys, CHECK constraints, UNIQUE constraints, and NOT NULL constraints. All tables must have a primary key and you must have a foreign key on the many side of each one-to-many relationship in the instructor-supplied ERD. Note that you must use Oracle for your database and and you must use actual SQL statements vs. a graphical approach. You may use Nova or any other Oracle instance you wish. You may use SQL*Plus, iSQL*Plus, or other command line tool (e.g. SQL Developer).
    2) Add 20 more rows to both your VEHICLES and CUSTOMERS tables to have 25 rows total in each table and display the row counts of these tables.
    3) Add 40 more rows to your SALES table to have 50 rows total and display the row count.
    4) Create the SALE_FINANCINGS table if necessary, populate it as necessary, and show the row count.
    5) Execute a multi-table join SELECT statement to display the number of sales by make of car, sorted by make.
    6) Execute a multi-table join SELECT statement to display the number of sales by customer zip code, sorted by zip code.
    7) Execute a multi-table join SELECT statement to display the number of sales by make of car and customer zip code, sorted by make and then by zip code.
    8) Using a set operator execute a single SELECT statement to display together all the customer last names and all the salesperson last names, with no duplicates.
    9) Using a set operator, subquery, and/or join query execute a SELECT statement to display the customers by customer ID, last name, and zip code who have not been involved in a sale, sorted by customer ID.
    10) Using a correlated subquery execute a SELECT statement to display the sale ID and plan ID of all sales in the last 30 days, sorted by sale ID.

    The SQL for Steps #5 through #10 above must all be a single SELECT statement.
    You must show ALL your SQL statements and ALL your results, together, for ALL SQL statements in Steps #1 through #10 above.
    If using SQL*Plus then submit a single SPOOL file with everything. If using iSQL*Plus or another interface tool then include a single Word or PDF file of screen snapshots for everything.
    In your single file label your SQL statements (#1 through #10) and results and put them in order.
    You must use an SQL script file vs. typing ad hoc SQL, but do NOT submit your SQL script file.

    Learn More
  8. ITSE 2309 LAB 1 Database and SQL Queries

    ITSE 2309 LAB 1 Database and SQL Queries

    Regular Price: $12.00

    Special Price: $10.00

    ITSE 2309 LAB 1 Database and SQL Queries


    Submit: Query Statements Query Output
    Chapters 2,3,4,5 11g SQL book
    Objectives: Be able to access ORACLE/SQL Plus Be able to do simple SQL operations


    Steps:
    - Log onto the sample Oracle 11g database created.
    - See Chapter 7 in "Database Systems" for information on coding, saving, and executing your queries.
    - Save the output of the queries by using the SPOOL option ( see chpt 14 in 11g SQL in lab resources tab)
    - Save both the queries code and the output of the queries as "Lab1_LastName.txt and submit via Blackboard—using the - Attach file – Upload function


    Do the following queries:
    1. List all columns and rows in the stock table.
    2. List the last name, first name, and company of all customers (List the columns in that order). Place the list in alphabetical order by company name.
    3. List the company names for all customers from Sunnyvale, Redwood City, or San Francisco.
    4. List all orders that were placed between the dates 12/31/1999 and 01/03/2000. List order number, order date, customer number, ship date, and paid date. (Hint: Specify year in single quotes 'DD-MMM-YYYY')
    5. List the order number, order date, and shipping charges for all orders that are not on backlog and for which the shipping charge is over $15.00.
    6. List all stock items which are baseball items which have a unit price greater than $200.00 and a manufacturer code which starts with 'H'. (Hint: use LIKE)
    7. List the company name for all customers who have orders. Don not list a company more than once.
    8. List the customer number and the description (from the stock table) of all items ordered by customers with customer numbers 104-108. Order the output by customer number and description. (There should be no duplicate rows in your output).
    9. List the number of (distinct) customers having an order. Label the column "Total_Customers_with_Orders".
    10. For each customer having an order, list the customer number, the number of orders that customer has, the total quantity of items on those orders, and the total price for the items. Order the output by customer number. (Hint: You must use a GROUP BY clause in this query).

    Learn More
  9. Assignment 9-8 Maintaining an Audit Trail of Product Table Changes

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-5 to 9-8

    Regular Price: $30.00

    Special Price: $25.00

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-5 to 9-8

    Assignment 9-5: Processing Discount
    Brewbean's is offering a new discount for return shoppers. Every fifth completed order gets a 10% discount. The count of orders for a shopper is placed in a packaged variable named pv_disc_num during the ordering process. The count needs to be tested at checkout to determine whether a discount should be applied. Create a trigger named BB_DISCOUNT_TRG so that when an order is confirmed (the ORDERPLACED value is changed from 0 to 1), the pv_disc_num packaged variable is checked. If it's equal to 5, set a second variable named pv_disc_txt to Y. This variable is used in calculating the order summary so that a discount is applied, if necessary.
    create a package specification named DISC_PKG containing the necessary packaged variables. Use an anonymous block to initialize the packaged variables to use for testing the trigger. Test the trigger with the following UPDATE statement:
    UPDATE bb_basket
    SET orderplaced = 1
    WHERE idBasket = 13;
    If you need to test the trigger multiple times, simply reset the ORDERPLACED column to 0 for basket 13 and then run the UPDATE again. Also, disable this trigger when you're finished so that it doesn't affect other assignments.


    Assignment 9-6: Use Triggers to Maintain Referential Integrity
    At times, Brewbean's has changed the ID numbers for existing products. In the past, developers had to add a new product row with the new id to the BB_PRODUCT table, modify all the corresponding BB_BASKETITEM and BB_PRODUCTOPTION table rows, and then delete the original product row. Can a trigger be developed to avoid all these steps and handle the update of the BB_BASKETITEM and BB_PRODUCTOPTION table rows automatically for a given change in product ID? If so, create the trigger and test by issuing an update statement, which changes the IDPRODUCT of 7 to 22. Do a rollback to return the data back to its original state. Also, disable the new trigger after you have completed the assignment.


    Assignment 9-7: Updating Summary Data Tables
    The Brewbean's owner uses several summary sales data tables every day to monitor business activity. The BB_SALES_SUM table holds the product ID, total sales in dollars, and total quantity sold for each product. A trigger is needed so that every time an order is confirmed or the ORDERPLACED column is updated to 1, the BB_SALES_SUM table is updated accordingly. Create a trigger named BB_SALESUM_TRG that perform this task. Before testing, reset the ORDERPLACED column to 0 for basket 3, as shown in the following code, and use this basket to test the trigger.
    UPDATE bb_basket
    SET orderplaced = 0
    WHERE idBasket = 3;
    Notice that the BB_SALES_SUM table already contains some data. Test the trigger with the following UPDATE statement, and confirm that the trigger is working correctly:
    UPDATE bb_basket
    SET orderplaced = 1
    WHERE idBasket = 3;
    Do a rollback and disable the trigger when you're finished so it doesn't affect the other assignments.


    Assignment 9-8: Maintaining an Audit Trail of Product Table Changes
    The accuracy of product table data is critical and the Brewbean's. owner wants to have an audit file that containing information on all DML activity on the BB_PRODUCT table. This information should indicate the ID of the user performing a DML action, the date, the original values of the changed row, and the new values. This audit table needs to track specific columns of concern, including PRODUCTNAME, PRICE, SALESTART, SALEEND, and SALEPRICE. Create a table named BB_PRODCHG_AUDIT that can hold the relevant data. Then create a trigger named BB_AUDIT_TRG that fires an update to this table whenever one of the specified columns in the BB_PRODUCT table is changed.
    Be sure to issue the following command. If you created the SALES_DATE_TRG trigger in the chapter, it conflicts with this assignment.
    ALTER TRIGGER SALES_DATE_TRG DISABLE;
    Use the following update statement to test your trigger:
    UPDATE bb_product
    SET salestart = '05-MAY-03', saleend = '12-MAY-03', saleprice = 9
    WHERE idproduct = 10;
    When you have finished, do a rollback and disable the trigger so that it does not affect other assignments.

    Learn More
  10. Assignment 9-4 Updating Stock Levels When an Order ls Cancelled

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4

    Regular Price: $25.00

    Special Price: $20.00

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4

    Assignment 9-1: creating a Tigger to Handle Product Restocking
    Brewbean's has a couple of columns in the Product table to assist in inventory tracking. The REORDER column contains the stock level at which the product should be reordered. If the stock fall to this level. Brewbean's wants the application to insert a row in the BB_PRODUCT_REQUEST table automatically to alert the ordering clerk that additional inventory is needed. Brewbean's currently uses the reorder level amount as the quantity that should be ordered. This task can be handled by using a trigger.
    1. Take out some scrap paper and a pencil. Think about the tasks the triggers needs to perform. Including checking. whether the new stock level falls below the reorder point. If so, check whether the product is already on order by viewing the product request table; if not, enter a new product request. Try to write the trigger code on paper. Even though you learn a lot by reviewing code, you improve your skills faster when you create the code on your own.
    2. Open the c9reorder.txt file in the Chapter09 folder. Review this trigger code, and determine how it compares with your code.
    3. In SQL Developer, create the trigger with the provided code.
    4. Test the trigger with product ID 4. First, run the query shown in Figure 9-36 to verify the current stock data for this product. Notice that a sale of one more item should initiate a reorder.
    5. Run the UPDATE statement shown in Figure 9-37. It should cause the trigger to fire. Notice the query to check whether the trigger fired and weather a product stock request was inserted in the BB_PRODUCT_REQUEST table.
    6. Issue a ROLLBACK statement to undo these DML actions to restore data to its original state for use in later assignments.
    7. Run the following statement to disable this trigger so that it doesn't affect other projects:
    ALTER TRIGGER bb_reorder_trg DISABLE;


    Assignment 9-2: Updating Stock Information When a Product Is Filled
    Brewbean's has a BB_PRODUCT_REQUEST table where requests to refill stock level was inserted automatically via a trigger. After the stock level falls below the reorder level, this trigger fires and enters a request in the table. This procedure works great; however, when store clerks record that the product request has been filled by updating the table's DTRECD and COST columns. they want the stock level in the product table to be updated. Create a trigger named BB_REQFILL_TRG to handle this task, using the following steps as a guideline:
    1. In SOL Developer, run the following INSERT statement to create a product request you can use in this assignment:
    INSERT INTO bb_product_requect (idRequest, idProduct, dtRequest, qty)
    VALUES (3, 5, SYSDATE, 45);
    COMMIT;
    2. Create the trigger (BB_REQFILL_TRG) so that it fires when a received date is entered in the BB_PRODUCT_REQUEST table. This trigger needs to modify the STOCK column in the BB_PRODUCT table to reflect the increased inventory.
    3. Now test the trigger. First, query the stock and reorder data for product 5. as shown in Figure 9-38.
    4. Now update the product request to record it as fulfilled by using UPDATE statement shown in figure 9-39.
    5. Issue queries to verify that the trigger fired and the stock level of product 5 has been modified correctly. Then issue the ROLLBACK statement to undo modifications.
    6. If you aren't doing assignment 9-3, disable the trigger so that it doesn't affect other assignments.


    Assignment 9-3: Updating the Stock Level If a Product Fulfillment is Cancelled
    The Brewbean's developers have made progress on the inventory-handling processes; however, they hit a snag when a store clerk incorrectly recorded a product request as fulfilled. When the product request was updated to record a DTRECD value, the product stock level was updated automatically via an existing trigger, BB_REQFILL_TRG. If the clerk empties the DTRECD column to indicate that the product request has been filled, the product stock level need to be corrected or reduced, too. Modify the BB_REQFILL_TRG to solve this problem.
    1. Modify the trigger code from Assignment 9-2 as needed. Add code to check whether the DTRECD column already has a data in it and is now being set to NULL.
    2. Issue the following DML actions to create or update rows that you can use to test the trigger:
    INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty, dtRecd, cost)
    VALUES (4, 5, SYSDATE, 45, '15-JUN-2012', 225);
    UPDATE bb_product
    SET stock = 86
    WHERE idProduct = 5;
    COMMIT;
    3. Run the following UPDATE statement to test the trigger, and issue queries to verify that the data has been modified correctly.
    UPDATE bb_product_request
    SET dtRecd = NULL
    WHERE idRequest = 4;
    4. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
    ALTER TRIGGER bb_reqfill_trg DISABLE;


    Assignment 9-4: Updating Stock Levels When an Order ls Cancelled
    At times, customers make mistakes in submitting orders and call to cancel an order. Brewbean's wants to create a trigger that automatically updates the stock level of all products associated with a cancelled order and updates the ORDERPLACED column of the BB_BASKET table to zero, reflecting that the order wasn't completed. Create a trigger named BB_ORDCANCEL_TRG to perform this task, taking into account the following points:
    The trigger need to fire when a new status record is added to the BB_BASKETSTATUS table and when the IDSTAGE column is set to 4, which indicates an order has been cancelled.
    Each basket can contain multiple items in the BB_BASKETITEM table, so a CURSOR FOR loop might be a suitable mechanism for updating each item's stock level
    Keep in mind that coffee can be ordered in half or whole pounds.
    Use basket 6, which contains two items, for testing.
    1.Run this INSERT statement to test the trigger:
    INSERT INTO bb_basketstatus (idStatus, idBasket, idStage, dtStage)
    VALUES (bb_status_seq.NEXTVAL, 6, 4, SYSDATE);
    2. Issue the queries to confirm that the trigger has modified the basket's order status and product stock level correctly.
    3. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
    ALTER TRIGGER BB_ORDCANCEL_TRG DISABLE;

    Learn More

Items 11 to 20 of 63 total

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

Grid  List 

Set Ascending Direction