Q&A Hero

Q: Count(*) function returns a count of records including those with nulls.

Q: Aggregate functions operate on a set of fields rather than a single field.

Q: The result of two conditions with Or operator in between will be true if either of the conditions are true.

Q: The result of two conditions with And operator in between will be true only if both of the conditions are true.

Q: The Like operator could be used to retrieve all the clients in a particular area code.

Q: Projection operation consists of choosing to list certain fields after the Select statement, and omitting the others.

Q: Equal to (=), greater than (>), and less than (<) are all examples of conditional operators.

Q: The Where clause must consist of only one condition.

Q: For each particular record that is being examined, the Where clause evaluates either to True or False.

Q: The selection retrieval process is implemented using Where clause in the Select statement.

Q: In Oracle, the statement used to display all the employees, sorted first by their names, and then by birth dates would be: Select * From employees Order by birthdate, name.

Q: An example of a multiple sort is ordering all the employees first by their social security numbers and then by their names.

Q: In Oracle's iSQL Plus interface, SQL commands are typed into the window and then run using the Execute button.

Q: In Oracle, queries are created in iSQL Plus interface accessible over the Web.

Q: For years, QuickTax, Inc. has helped small businesses manage their taxes. The management wants a database to keep track of clients, employees, and jobs. Managers need to keep track of client names, addresses, contact persons, phone numbers, and email addresses. They also need to track the employee's social security numbers, names, and birth dates. Also, the company needs to be able to keep track of the data on each job an employee performs for each of their clients.For questions 5"26, please refer to the preceding paragraph.The statement used to retrieve all the data from the client table would be: Select Client From *.

Q: The only required elements of an SQL statement are: (1) the Select statement (followed by a list of tables), and the From keyword (followed by a list of columns).

Q: SQL implements selection, projection, and joining operations through a single statement called the Select statement.

Q: Selection operation retrieves a subset of columns.

Q: Selection, projection, and joining are the three core data retrieval operations for relational databases.

Q: The most appropriate data type used to store social security numbers would be which of the following? a) Varchar(9) b) Char(9) c) Number(9,2) d) Date

Q: The most appropriate data type to store product description is which of the following? a) Varchar b) Char c) Number d) Date

Q: The most appropriate data type used to store 349.82 would be which of the following? a) Number(3,2) b) Number(4,2) c) Number(5,2) d) Varchar(6)

Q: In Oracle, inserting a child record that does not have a matching parent record will generate an Orphan Cannot Exist error.

Q: In Oracle, an extra comma at the end of a value list in an Insert statement will generate a Missing Expression error.

Q: Oracle's default date format can be changed using alter session set nls_date_format command.

Q: In Oracle, using level as a field name would generate Invalid Identifier error.

Q: In Oracle, errors are always traced to the line that generated them.

Q: In Oracle, each complete statement ends in a semicolon.

Q: In Oracle, listing all the fields followed by the primary key reserved words would create a concatenated primary key.

Q: In Oracle, the reserved word for defining a foreign key is references.

Q: In Oracle, the script line used to enter the first record into the client table would be: insert into client ("[email protected]", "Dentist Pros', "James Tucker", "63 Elm Street", "Manchester", "NH", "03308", "(603)774-6698")

Q: In Oracle, the script line setting up Email as the primary key would read: email char(30) as primary key.

Q: The data type for Zip Code field would be Number.

Q: Email would be set as the primary key for the client table.

Q: For years, QuickTax, Inc. has helped small businesses manage their taxes. The management wants a database to keep track of clients, employees, and jobs. Managers need to keep track of client names, addresses, contact persons, phone numbers, and email addresses. They also need to track the employee's social security numbers, names, and birth dates. Also, the company needs to be able to keep track of the data on each job an employee performs for each of their clients.ClientEmail Name Contact Address City State Zip Code [email protected] Dentist Pros James Tucker 63 Elm Street Manchester NH 03308 (603)[email protected] Danny's Hardware Danny Stewart 5 Main Street Campton NH 03223 (603)[email protected] Jo's Furniture Jo Collins 54 High Street Brattleboro VT 05301 (802)[email protected] Johny's Pizza Anthony Vicente 54 Main Street Campton NH 03223 (603)[email protected] Plymouth Professionals Margaret Jones 84 Highland Street Plymouth NH 03264 (603)[email protected] The Corner Grocery Joan Jenkins 34 State Street Concord NH 03301 (603)[email protected] Mountain Coffee Terry McDougal 23 Main Street Plymouth NH 03264 (603)536-1288For questions 14-26, please refer to the preceding paragraph and table.In Oracle, the data type of Email field would be Char(10).

Q: Oracle scripts are retrieved using Load Script button.

Q: In Oracle, the workspace is a user interface where SQL commands are typed and executed.

Q: In Oracle, Save statement is used to save changes made to the database.

Q: Set Echo On statement is used to display script results.

Q: A series of SQL commands saved in a file is called a script.

Q: Drop Table command is typically placed at the bottom of the script.

Q: Values of numeric fields must be enclosed in single quotation marks.

Q: All the data for a table can be entered with a single Insert statement.

Q: All the fields in the Create Table statement are separated by commas, except for the last field.

Q: Using a reserved word "level" as a column name is not allowed.

Q: Social security number would be stored in Char data type.

Q: Phone number would be stored in Number data type.

Q: Varchar data type cannot contain numerical digits.

Q: Complete the design of the line item table in an Oracle database using appropriate SQL script. Make sure to indicate which of the fields is the primary key.

Q: Complete the design of the order table in an Oracle database using appropriate SQL script. Assume that any necessary parent tables have been created. Make sure to indicate which of the fields is the primary key and which fields are the foreign keys.

Q: Complete the design of the product table in an Oracle database using appropriate SQL script. Make sure to indicate which of the fields is the primary key.

Q: Exposures is a specialty retailer of fine gifts, picture frames, albums, scrapbooks, as well as occasional gift ideas. Visit their Web site at www.exposureonline.com, and using the registration, billing, catalog, and shopping cart screens below, go through each of the five ER design steps.For questions 89"92, please refer to the preceding paragraph and Web pages.Complete the design of the customer table in an Oracle database using appropriate SQL script. Make sure to indicate which of the fields is the primary key.

Q: Complete the design of the order table in an Oracle database using appropriate SQL script. Assume that any necessary parent tables have been created. Make sure to indicate which of the fields is the primary key and which fields are the foreign keys.

Q: Complete the design of the supplier table in an Oracle database using appropriate SQL script. Make sure to indicate which of the fields is the primary key.

Q: Jerry, the owner of Exotic Flower's Inc., built a small greenhouse to store several types of exotic flowers that he purchases from wholesale suppliers around the world. Each exotic flower Jerry buys and resells falls into one of several flower groups that differ with respect to their storage needs, duration, time in bloom, price, etc. Each of the orders placed by customers specifies the type of exotic flower, the supplier it came from, the date of order, expected delivery date, flower condition on delivery, as well as the quantity ordered. Customer's data contain all the standard information needed to collect payment and deliver the flowers.For questions 86"88, please refer to the preceding paragraph.Complete the design of the flower table in an Oracle database using appropriate SQL script. Make sure to indicate which of the fields is the primary key.

Q: Complete the design of the payment table in an Oracle database using appropriate SQL script. Make sure to indicate which of the fields is the primary key.

Q: Complete the design of the booking table in an Oracle database using appropriate SQL script. Assume that any necessary parent tables have been created. Make sure to indicate which of the fields is the primary key and which fields are the foreign keys.

Q: Complete the design of the vehicle table in an Oracle database using appropriate SQL script. Make sure to indicate which of the fields is the primary key.

Q: Plymouth Car Rental started with two compact cars and has expanded its fleet of vehicles with several sedans and trucks. When a customer books a rental, his/her age has to be at least 18 for compact cars and sedans, which are rented by the day and must be returned the following morning by 11 AM. Trucks are rented for a maximum of six hours. The owner requires a deposit on the reservation to be paid within seven days of booking. Vehicle records consist of the makes and types (Honda sedan), color, seating capacity, required deposit, rental rate and rental limit (in hours). Customer data consists of customer's names, addresses, phone numbers, and birth dates. Bookings identify the customer, vehicle, as well as the time rented and returned. There can be several payments up until the reservation date. Payments must reflect the payment status for each booking, including deposit, when the deposit was made, then each of the payments made, and when the entire payment was completed.For questions 82"85, please refer to the preceding paragraph.Complete the design of the customer table in an Oracle database using appropriate SQL script. Make sure to indicate which of the fields is the primary key.

Q: In Oracle, using Number for a column name, would generate an _ error.

Q: In Oracle, an extra comma at the end of the column list in the Insert statement will generate an __ error.

Q: In Oracle, omitting a comma between fields in the Create Table statement will generate a __ error message.

Q: A children table without a reference to a parent table is called a(n) __.

Q: In Oracle, if the primary key for the job table was client's email, employee's social security number, and job date, the script line defining this concatenated key would be: __.

Q: In Oracle, the script line used to define the foreign key in job table, creating a relationship with client table, would be: __.

Q: In Oracle, the script line used to define the foreign key in job table, creating a relationship with client table, would be: __.

Q: In Oracle, after all the data has been inserted into the client table, the script line used to check that the correct data was entered, would be: __.

Q: In Oracle, the script line used to enter the first record into the client table, would be: __

Q: In Oracle, the script line used to describe the fields in the client table that was just created, would be: __.

Q: In Oracle, the script line used to create the client table, would be: __.

Q: In Oracle, the script line used to remove any previous version of the client table, would be: __.

Q: For years, QuickTax, Inc. has helped small businesses manage their taxes. The management wants a database to keep track of clients, employees, and jobs. Managers need to keep track of client names, addresses, contact persons, phone numbers, and email addresses. They also need to track the employee's social security numbers, names, and birth dates. Also, the company needs to be able to keep track of the data on each job an employee performs for each of their clients.In Oracle, the script line setting Email as the primary key, would be: __.

Q: Oracle workspace screen can be customized using __ link.

Q: In Oracle, previously executed scripts during the current session can be viewed using _ link.

Q: In Oracle, saved scripts are retrieved using __ button.

Q: The command __ is used to verify that the tables were properly created.

Q: The developer can enhance script documentation by adding __.

Q: The command __ is used to destroy the database.

Q: In the Create Table statement, each of the field names is followed by the appropriate __ and separated from other fields by a __.

Q: The primary key is identified by typing the words __ after the data type of the appropriate field.