+
 CIS 336  Slingshot Academy  / cis336aid.com

CIS 336 Slingshot Academy / cis336aid.com

Rating:
Rating
(0)
Author: alertiw jowhz
See More
Try a College Course Free

Sophia’s self-paced online courses are a great way to save time and money as you earn credits eligible for transfer to over 2,000 colleges and universities.*

Begin Free Trial
No credit card required

25 Sophia partners guarantee credit transfer.

221 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 20 of Sophia’s online courses. More than 2,000 colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

 CIS 336  Slingshot Academy  / cis336aid.com

CIS 336 Final Exam (Feb 2016)
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS 336 Final Exam Question 1. 1. (TCO 1) A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions? Question 2. 2. (TCO 1) A relational DBMS provides protection of the _____ through security, control, and recovery facilities. Question 3. 3. (TCO 2) A relationship is an association between _____ Question 4. 4. (TCO 2) A _____ key is a key that is composed of more than one attribute. Question 5. 5. (TCO 3) The PK must uniquely identify each entity instance. A primary key must be able to guarantee unique values. It cannot contain nulls. This rationale belongs to _____ Question 6. 6. (TCO 3) The extended entity relationship model (EERM) is sometimes referred to as the _____ Question 7. 7. (TCO 3) A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in _____ Question 8. 8. (TCO 4) When a constraint is created at the _____ level with the CREATE TABLE command, the constraint definition is simply included as part of the column definition. (Points : 4) Question 1. 1. (TCO 4) When creating a table, which of the following statements is NOT correct? Question 2. 2. (TCO 4) Which of the following SQL statements would you use to create a table named DEVRY? Question 3. 3. (TCO 5) The Crow’s Foot model is more _____-oriented than the Chen model. Question 4. 4. (TCO 5) Knowing the _____ number of entity occurrences is very helpful at the application software level. Question 5. 5. (TCO 6) The _____ statement can be used to enable or disable a constraint. Question 6. 6. (TCO 6) When modifying the data characteristics of a column in Oracle, which of the following is correct? Question 7. 7. (TCO 6) Which of the following keywords can be used to change the size, datatype, or default value of an existing column? Question 8. 8. (TCO 7) Any type of subquery can be used in the _____ clause of a SELECT statement Question 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Final Exam 1 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4)
elemental
 linked
 hierarchical
 relational
2. (TCO 1) Data constitutes the building blocks of _____________. (Points : 4)
information
 processing
 applications
 programming
3. (TCO 2) If a foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key is/are said to exhibit __________ integrity. (Points : 4)
referential
 restrictive
 secondary
 redundant
 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Final Exam 2 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4)
elemental
 linked
 hierarchical
 relational
2. (TCO 1) A relational DBMS provides protection of the _______________________ through security, control, and recovery facilities. (Points : 4)
user
 network
 system
 database
3. (TCO 2) If a foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key is/are said to exhibit __________ integrity. (Points : 4)
referential
 restrictive
 secondary
 redundant
 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Final Exam 3 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
1. (TCO 2) Explain what physical independence means. (Points : 15)
2. (TCO 8) Sally wants to query the EMP table and determine how many of the employees live in each of the cities the company has factories in. She writes the following query but it does not work. What is wrong with the way the query is constructed?
3. (TCO 3) Explain in what two specific cases presented in the text composite primary keys are particularly useful. (Points : 15)
4. (TCO 7) How are comparisons done on character string data in a queries WHERE clause, and why would this be especially useful in comparing certain attributes? (Points : 15)
5. (TCO 6) Oracle will not allow the changing of a column’s data type unless the column is empty, and even then it may still be prohibited. Discuss why? (Points : 15)
 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Final Exam 4 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
1. (TCO 7) For every order, list the order number, order date, part number, part description, and item class for each part that makes up the order.
2. (TCO 8) Write a query that will display the customer number, last name, and first name for every customer I whose first name begins with the letter “D”.
2. (TCO 8) Write a query that will display the earliest date an order was placed.
3. (TCO 7) Using the BETWEEN operator, write a query that will list all details about all orders that were placed during the period of September 3rd thru September 5th of 1998.
5. (TCO 7) Write a query that will list the customer first and last name and the sales rep commission rate using JOIN ON.
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Final Exam 4 Sets of Answers (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS 336 Final Exam 4 Sets of Answers
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Final Exam Guide
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS 336 Final Exam Guide 1)Joe works for a company where the IT department charges him for the number of CRM login accounts that are in his department. What type of IT funding model is his company deploying? 2) This project cycle plan chart looks very much like a bar chart and is easy for management to read because of its visual nature. 3) What is at the center of the project triangle? 4) This is a text message that is stored on a user’s computer by a Web server that helps trace the user’s browsing habits. 5) All of the following are risks associated with IS outsourcing EXCEPT: 6) This project cycle plan tends to be used for projects that have direct relationships between time and resources. 7) This is an IT governance framework that is consistent with COSO controls. 8) The question “What information does a person or an organization have a right or a privilege to obtain?” is part of this ethical issue. 9) ________________ is the process of analyzing data warehouses for “gems”. 10) Which normative theory of business ethics would TJX have displayed if it had informed customers as soon as the breach was corrected and told other retailers how to prevent future security breaches? 11) Scorecards provide a summary of information gathered over a period of time. Another common IT monitoring tool is the _____________. 12) The initial reason organizations outsourced IT was which one of the following? 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Group Project
FOR MORE CLASSES VISIT
www.cis336aid.com
This project is designed to touch all aspects of the fundamental concepts of database design and logical data modeling covered during the class. Each student will be responsible for designing, developing, and demonstrating the functionality of a database created based on a set of business specifications that you create. At the end of the session, each student will submit a report that includes the logical and physical design of the database and demonstrates the database’s ability to deliver meaningful reports. All aspects of the project will be covered during the week or weeks prior to a deliverable, and each task deliverable in the project will be supported through the exercises and labs done in the course. Guidelines You will create a proposal for a commerce business of your choosing. This can be an actual or fictional business that sells products and/or services. You will determine the business rules and report requirements. Entities that must be included in your model are the following. Customer Product (or Service) Order Project Deliverables This task is broken down into four deliverables. • Task 0 • Task 1 • Task 2 • Task 3 Remember that this is a work in progress and in all likelihood, you will have to make numerous changes to your tables and schema as you go along. These changes are to be expected and are a normal part of the database design and implementation process. Please refer to the sections below for details regarding deliverables for each task. Task 0 The first task is to create your business narrative. 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 iLab 1 Normal Forms and Entity Relationship Diagrams
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS336 Lab 1: Normal Forms and Entity Relationship Diagrams Introduction In this exercise, you will analyze a de-normalized data set presented in the form of a spreadsheet. You will next construct a series of dependency diagrams, transforming the evolving data model from First Normal Form (1NF), to Second Normal Form (2NF), and finally to Third Normal Form (3NF). When the model has reached 3NF, you will construct the Entity Relationship Diagram (ERD) depicting the logical design of the database. Your ERD will use Crow’s Foot notation to denote the relationships between tables. Sample Exercise, With Solution Before completing your lab, please review this example problem. The diagram below is a partial depiction of a business spreadsheet for a retail store operation. The store sells books. Books have a 13-digit International Standard Book Number (ISBN), a title, a publisher, and a unit price. Invoices track sales of books. An Invoice contains one or many line items, with each line item reflecting the sale of one or more copies of a specific book. Every publisher has a company name, and a publisher code.
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 iLab 2 The Expanded Entity Relationship Diagram
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS336 Lab 2: The Expanded Entity Relationship Diagram LAB OVERVIEW Scenario and Summary This lab introduces the next step in creating a data model, the Entity Relationship Diagram (ERD). You will be given a business scenario for a University Medical Center, which is a small community hospital. The business specifications will outline a number of things about the business, some of which will apply directly to the database you are being asked to model. There is a table that lists the entities (tables) that will be needed for the database and related attributes (columns) for each entity. There is also a column that lists specific information about the entity that will be helpful in determining its relationship to other entities within the model. Be sure to include the minimum and maximum occurrences of each relationship (cardinality) and to supply a name to the relationship that will work in both directions. Make sure to use Crow’s Feet notation in your ERD. Narrative/Case Study The University Medical Center is a small, community hospital. A new hospital administrator has recently been hired by the Board of Directors, and directed to right-size patient care and pharmacy services and improve profitability. 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 iLab 3 Building the Physical
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS-336: Lab 3: Building the Physical LAB OVERVIEW Scenario and Summary The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the zip file titled CIS336Lab3Files from Doc Sharing. This zip file contains the ERD, Data Dictionary, and test data for the tables you create as you complete this exercise. Your job will be to use the ERD Diagram found below as a guide to define the table structures and constraints using both CREATE TABLE and ALTER TABLE statements. Once this has been done, you will need to write the INSERT INTO TABLE statements to insert the data provided into the table. The data should verify that the constraints you have created are valid and define the correct referential and data integrity constraints asked for. Lastly, you will write SELECT statements to query the tables and verify the data was populated.
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 iLab 4 Introduction to Select, Insert, Update, and Delete Statements (only SQL Command, No output)
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS336: Lab 4: Introduction to Select, Insert, Update, and Delete Statements LAB OVERVIEW Lab 4 will introduce the various aspects of the SQL select statement and the methods of retrieving data from the database tables. This lab will also introduce the fundamentals of updating and deleting records. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the OM database tables. The lab will utilize a set of tables that are represented by the ERD (OM_ERD.docx) and are created and populated by the script file (create_OM_db.sql). Follow the instructions in the file CreateOMTables.docx to create your database, tables, and data. A few IMPORTANT things to note if using EDUPE MySQL: **There can be NO SPACES in alias names given to a column. For example: Select unit_price as “Retail Price “ from items; –this does NOT work in EDUPE MySQL. Any of the following WILL WORK: Select unit_price as “RetailPrice” from items; 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 iLab 5 Joining Tables
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS336: Lab5: Joining Tables LAB OVERVIEW Scenario and Summary Lab 5 will introduce the concept of multi-table JOINS in order to work with data in two or more related tables simultaneously. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the OM database tables. The lab will utilize a set of tables that are represented by the ERD (OM_ERD.docx) and are created and populated by the script file (create_OM_db.sql). Follow the instructions in the file CreateOMTables.docx to create your database, tables, and data. A few IMPORTANT things to note if using EDUPE MySQL: **There can be NO SPACES in alias names given to a column. For example: Select unit_price as “Retail Price “ from items; –this does NOT work in EDUPE MySQL. Any of the following WILL WORK: Select unit_price as “RetailPrice” from items; Select unit_price as “Retail_Price” from items; Select unit_price as Retail_Price from items; Select unit_price as RetailPrice from items; 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 iLab7 Working with Views
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS-336: Lab7: Working with Views LAB OVERVIEW Lab 7 will introduce the concept of database views. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the OM database tables. The lab will utilize a set of tables that are represented by the ERD (OM_ERD.docx) and are created and populated by the script file (create_OM_db.sql). Follow the instructions in the file CreateOMTables.docx to create your database, tables, and data. A few IMPORTANT things to note if using EDUPE MySQL: **There can be NO SPACES in alias names given to a column. For example: Select unit_price as “Retail Price “ from items; –this does NOT work in EDUPE MySQL. Any of the following WILL WORK: Select unit_price as “RetailPrice” from items; Select unit_price as “Retail_Price” from items; Select unit_price as Retail_Price from items; Select unit_price as RetailPrice from items; **Any calculated fields MUST be given an alias (and note above NO SPACES in alias). For example: select unit_price * 2 from items; –this does NOT work in EDUPE MySQL This will work: select unit_price * 2 as NewPrice from items; Deliverables 
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Quiz 1 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
(TCO 3) Normalization works through a series of stages called normal forms. Typically _________ stages must be completed before a table can be considered normalized.
(TCO 3) The conflicts between design efficiency, information requirements, and processing speed are often resolved through
(TCO 3) The PK must uniquely identify each entity instance. A primary key must be able to guarantee unique values. It cannot contain nulls. This rationale belongs to
(TCO 9) The SQL command that lets you insert data into a table, one row at a time, is _____________.
(TCO 4) Which command is used to restore the table’s contents to their default values?
(TCO 4) Which of the following SQL commands would alter the table DEVRY and add a foreign key that references the CITY table?
(TCO 4) The SQL command that lets you save your work to disk, is ___________
(TCO 9) If the INSERT INTO command is used to insert data values that violate an existing constraint in a table, which of the following will happen?
 (TCO 3) Explain under what circumstances a partial dependency can exist in a database.
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Quiz 2 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
(TCO 2) Explain what a composite attribute is
(TCO 1) Data constitutes the building blocks of
(TCO 1) Successful database design is first and foremost based on _____________ requirements.
(TCO 1) The lowest level in a database hierarchy is the ____________ or value as it is often referred
(TCO 2) The term _____________ design is used to refer to the task of creating the conceptual data model represented by an Entity Relationship Diagram (ERD).
(TCO 2) Within a table, the primary key must be unique so that it will identify each row. When this is the case, the table is said to exhibit ___________.
(TCO 2) A field that consists of data values that can be used for arithmetic procedures is a _________ type field.
(TCO 2) A relationship is an association between ___________
(TCO 5) The _____________ model represents a global view of the data.
(TCO 5) In Crow’s Foot notation, a forked end means
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Quiz 3 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
(TCO 7) Which of the following types of columns will automatically right-align the data when it is displayed?
(TCO 7) Which of the following SQL statements will display the profit generated by each book currently stored in the BOOKS table? (You do not need to see the table structure for this question)
(TCO 7) Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
(TCO 7) Which of the following operators would be most appropriate to determine whether or not the retail price of a book is at least $24.00?
(TCO 7) The ______________________ comparison operator is used to indicate that a data value must fall within a range of values to be included in the query results.
(TCO 7) Based upon the contents of the BOOK_ORDER table shown below, which of the following queries will display all orders that were not shipped for at least three days after the order was received?
(TCO 7) ANSI-standard SQL allows the use of a special operator in conjunction with WHERE clause. A special operator used to check whether an attribute matches one from a subquery is_______.
 (TCO 7) A ____ occurs when a relationship is improperly or incompletely identified and, therefore, is represented in a way that is not consistent with the real world.
 (TCO 7) The ______________________ comparison operator is used to search for values which are not NULL.
 TCO 7) You want to write a query that will return the customer number, first and last name from the book_order table (shown below) for all customers who have a P.O. Box and who live in either Trenton or Chicago. Write the query that will accomplish this task.
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Quiz 4 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
(TCO 8) If a Cartesian join is used to link table A which contains five rows to table B which contains eight rows, there will be _______ rows in the results.
(TCO 8) Which of the following statements will produce the value 21.1?
(TCO 8) Which of the following format elements will display insignificant leading zeros?
(TCO 8) Oracle’s pseudo table used only for cases where a table is not really needed is _________?
(TCO 7) Using the BOOK_CUSTOMER and BOOK_ORDER tables shown below for reference which SQL statement will return the same results as the following SQL statement?
(TCO 7) A table alias or qualifier cannot be assigned in the FROM clause of which type of join?
 (TCO 7) The _______ query joins a table to itself using aliases to distinctly identify each instance of the table.
 (TCO 7) Explain the difference between an inner join and an outer join.
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Quiz 5 (Devry)
FOR MORE CLASSES VISIT
www.cis336aid.com
(TCO 6) Which of the following keywords is used to remove a row in a table?
(TCO 6) When using the ALTER TABLE…DROP COLUMN command, which of the following is NOT correct?
(TCO 6) Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS table to store the date that the customer first placed an order with the company?
(TCO 8) Which of the following is NOT a valid statement?
(TCO 8) Based on the contents of the BOOKS table shown below, which of the following SQL statements will return the total profit generated by books in the COOKING category?
(TCO 8) Which comparison operator evaluates a subquery and if it returns false, will not execute the calling query?
 (TCO 8) The following SQL statement contains which type of subquery?
 
 (TCO 9) If the _________ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed.
 (TCO 9) Which of the following is not a valid SQL command?
 (TCO 9) Based on the contents of the PROMOTION table, which of the following commands will delete only the row for the Free Bookmark from the table?
 (TCO 8) In using group functions we cannot place a group function in a WHERE clause. Explain how the WHERE clause and HAVING clause similar and why you can use the HAVING with a group function but not a WHERE clause.
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Week 2 Quiz (New)
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS 336 Week 2 Quiz Question 1. (TCO 1) Raw facts, such as a telephone number, a birth date, and a customer name, are also referred to as _____ Question 2. (TCO 1) The DBMS allows you to extrapolate information from your data by using a _____ Question 3. (TCO 1) A database row (record) consists of _____ Question 4. (TCO 2) In Oracle, a _____ is automatically constrained to be unique and not null. Question 5. (TCO 2) A verb associating two nouns in a business rule usually translates to a(n) ___ in the data model. Question 6. (TCO 2) In a relationship, when a primary key from one table is also defined in a second table, the field is referred to as a _____ in the second table. Question 7. (TCO 2) In a relational database, each table must have _____ Question 8. (TCO 5) The product of a logical model is the entity relationship diagram, also known as a _____
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Week 3 Quiz (New)
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS 336 Week 3 Quiz Question 1. (TCO 3) When designing a database, you should _____ Question 2. (TCO 3) A table that is in 1NF and includes no partial dependencies only is said to be in _____ Question 3. (TCO 3) (TCO 3) A table is not in 1NF if _______________. Question 4. (TCO 9) All of following are examples of DML commands except _____ Question 5. (TCO 4) Which of the following is not considered a database object? Question 6. (TCO 4) In Oracle, the DEFAULT constraint is used to _____ Question 7. (TCO 4) The SQL command that lets you save your work to disk, is _____ Question 8. (TCO 9) Which of the following SQL statements would insert a line of data into the DEVRY table and supply a NULL value for the Student_city column?
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Week 4 Quiz (New)
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS 336 Week 4 Quiz Question 1. (TCO 7) Which of the following types of columns will automatically right-align the data when it is displayed? Question 2. (TCO 7) Which of the following symbols can be used to denote that any and all characters following the sign are eligible when used with LIKE? Question 3. (TCO 7) Based upon the contents of the BOOKS table shown below, which of the following is a valid SQL statement? Question 4. (TCO 7) Which of the following operators would be most appropriate to determine whether or not the retail price of a book is at least $24.00? Question 5. (TCO 7) Based upon the contents of the BOOK_ORDER table shown below, which of the following SQL statements will list all orders placed by customer# 1020 that have not yet been shipped? Question 6. (TCO 7) Based upon the contents of the BOOK_ORDER table shown below, which of the following queries will display all orders shipped between April 4, 2003 and April 5, 2003? Question 7. ((TCO 7) The ____ comparison operator is used to search for values that are not NULL. Question 8. (TCO 7) You want to write a SQL query that lists all employees in the EMP table whose last names in the EMP_LNAME column start with Smith, including values such as Smithfield. What is the correct statement?
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Week 5 Quiz (New)
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS 336 Week 5 Quiz Question 1. (TCO 8) The ____ set operator in SQL combines rows from two queries and returns only the values that are common between the two sets. Question 2. (TCO 8) Which SQL function converts a value to a particular string format? Question 3. (TCO 8) Based upon the contents of the BOOK_ORDER table shown below, which of the following SQL statements will display only those orders shipped to the zip code zone that begins with 323? Question 4. (TCO 8) Oracle’s pseudo table used only for cases where a table is not really needed is _____ Question 5. (TCO 7) Using the BOOK_CUSTOMER and BOOK_ORDER tables shown below for reference, which SQL statement will return the first and last name of each customer who’s order has not yet shipped? Question 6. (TCO 7) A table alias or qualifier cannot be assigned in the FROM clause of which type of join? Question 7. (TCO 7) A(n) ____ join returns not only the rows matching the join condition (that is, rows with matching values in the common columns) but also the rows with unmatched values. Question 8. (TCO 7) Which statement will display the date in this format:
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS 336 Week 6 Quiz (New)
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS 336 Week 6 Quiz Question 1. (TCO 6) Which of the following keywords can be used to change the size, datatype, or default value of an existing column? Question 2. (TCO 9) If the _____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed. Question 3. (TCO 6) 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? Question 4. (TCO 8) Based upon the contents of the BOOKS table shown below, which of the following SQL statements will return the number of different categories contained in the table? Question 5. (TCO 9) Which of the following is not a valid SQL command? Question 6. (TCO 8) Based on the contents of the BOOKS table shown below, which of the following SQL statements will return the total profit generated by books in the COOKING category? Question 7. (TCO 8) Before the COMMIT command is used, you can retrieve deleted records by using the _____ command. Question 8. (TCO 8)Based on the contents of the BOOK_ORDER table shown below, which of the following SQL statements will list the order number and customer number for the most recent orders in the system?
— — — — — — — — — — — — — — — — — — — — — — — — — — —
CIS336 iLab 6 Group Functions and Subqueries
FOR MORE CLASSES VISIT
www.cis336aid.com
CIS336: Lab 6: Group Functions and Subqueries LAB OVERVIEW Scenario and Summary Lab 6 will introduce the concept of group functions and subqueries to meet more complex report requirements. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the OM database tables. The lab will utilize a set of tables that are represented by the ERD (OM_ERD.docx) and are created and populated by the script file (create_OM_db.sql). Follow the instructions in the file CreateOMTables.docx to create your database, tables, and data. A few IMPORTANT things to note if using EDUPE MySQL: **There can be NO SPACES in alias names given to a column. For example: Select unit_price as “Retail Price “ from items; –this does NOT work in EDUPE MySQL. Any of the following WILL WORK: Select unit_price as “RetailPrice” from items; Select unit_price as “Retail_Price” from items; Select unit_price as Retail_Price from items; Select unit_price as RetailPrice from items; **Any calculated fields MUST be given an alias (and note above NO SPACES in alias). For example: select unit_price * 2 from items; –this does NOT work in EDUPE MySQL This will work: select unit_price * 2 as NewPrice from items; Deliverables • Lab Report (Answer Sheet) containing both the student-created SQL command(s) for each exercise, and the output showing the results obtained. Be sure your name is on the file. LAB STEPS: Complete each of the exercises below. 1. Write a query to determine the total number of items on each order. Display the order_id and the total with a heading of TotalItems (note no spaces). Filter to only display information for order_id of 600 or higher. 2. Re-do query 1 but filter to only show those orders with more than 2 items ordered. Sort by the number of items ordered, lowest to highest. 3. The order_details table has a quantity for each item ordered. Show the total amount charged for each item on the order (quantity times price). Display order_id, the item id, the unit price, the quantity times price of the item labeled as “Itemtotal” (note NO spaces). Sort by order id and filter to only display those order ids between 400 and 700. 4. Write a query to display the total amount for each order: show the order id and total. Sort by descending order on the total and only display orders with a total of $40 or more. 5. Re-do query 4 but show the customer name for each order (formatted as a single field with heading of Customer) along with the city, order id and total. Filter to only display customers that live in California. Sort by city. 6. Display the total amount of sales per item. Show title, total quantity sold with a heading of Quantity, total sales with a heading of “TotalSales” (not NO space). 
— — — — — — — — — — — — — — — — — — — — — — — — — — —