ISYS 2263 Exam 2 practice

The_________________________ SQL command defines the portion of database owned by the particular user.

Create SCHEMA

Which of the following is true about the given SQL code? SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE customers.customer_id = orders.customer_id); The WHERE command selects the columns based on conditional expression It selects a few columns from the Customer table and a few columns from the Orders table and filters them based on Primary Key, Foreign Key relationship It selects all Customer and Order information based on primary key and foreign keys relationships that help connect the record level data. CustomerID is the foreign key in the Orders table being used to join two tables i.e. Customers and Orders CustomerID is the primary key in the Orders table being used to join two tables i.e. Customers and Orders

CustomerID is the foreign key in the Orders table being used to join two tables i.e. Customers and Orders It selects all Customer and Order information based on primary key and foreign keys relationships that help connect the record level data.

When creating a database, which of the following are valid questions you need to ask at the definition stage? What queries will I build? Are there any other constraints? What queries to build for visualizing database contents? What are the tables and fields in tables? Which forms will I create? What is the schema? Which reports will I create? What are the data types?

Are there any other constraints? What are the tables and fields in tables? What is the schema? What are the data types?

The number of occurrences of an expression i.e. number of rows with non-null values is determined using the ____ aggregate function. MAX SUM COUNT AVERAGE

COUNT

Whenever you execute a query to retrieve data from a database table by using SELECT statements, the following feature helps you in manipulating the column titles to something that is more legible than pre-defined information. Note: As you have seen, the query places the outputs in a structure which looks similar to a table. Semantic Column Redefining Semantic Column Renaming Column Aliasing Interpretative Column Renaming

Column Aliasing

In a table,__________________________is a range of possible values a field can take. Note that field means column in Access terminology. One advantage of using this feature in database design is that it allows to show values in drop downs in the user interface design, for example, course grades. Domain Range Options Data Range Domain Domain Selections

Domain

A primary key is non-unique identifier of a record in the table. True False

False

DELETE TABLE FROM CUSTOMERORDERS_T is one SQL statement to use when you want to completely remove a table from the table schema that you created earlier. As discussed in the class, it is important that you need to exercise caution and discretion when executing this type of SQL statements because it will remove the tables from the database and creates issues during data fetching.

False

From the examples you executed in the class, a query can display data from one and only one table in an ordered manner based on the question pre-defined by the user. To retrieve data from multiple tables using the keys and relationships, you need to create a parameter query. This parameter query helps to connect across multiple tables, pre-define the question and take advantage of created table connections to retrieve answers to the question. True False

False

SQL is a non-procedural language and it gives you flexibility to focus on what the program should do rather than what sequence of tasks. Because of this non-procedural advantage of SQL, when you drop the tables which are not helpful for your analysis, the order of dropping need not be considered. This is because SQL allows you to focus on what the program should do. SQL will handle the ordering and sequence of tasks. Your job as data analyst is to identify the tables to drop based on their lack of value in your business tasks. True False

False

When you are creating tables in the database during schema creation stage of your database management project, you should not create queries for table creation. One of the uses of queries you noted in this course is that they are pre-defined by users and display data by creating a view on the main data table. Queries are good for repetitive usage to display data. Hence, it is not a good practice to use them for table creation which is data definition language (DML) based activity you execute in the beginning. It is better to always stay away from table level operations using queries. Note: We discussed about source tables in Excel, Tableau, SQL and Access. True False

False

You know that a primary key is not mandatory but recommended per the best practices in database design. Let us say you created two tables - Table 1 and Table 2. The foreign key in Table 2 always references Table 1. It is acceptable to define the foreign key column in Table 2 without NOT NULL constraint. It is enough if the primary key column in Table 2 is defined with NOT NULL constraint. However, because the tables are connected, it is mandatory that any primary keys and foreign keys in Table 1 should be NOT NULL because Table 2 depends on Table 1. True False

False

In MS-Access terminology,_________________________ is a table component that contains a category of information that pertains to all records. Category View Field Query

Field

Which options are FALSE about the SQL Statement below (one or more options)? CREATE TABLE inventory ( INV_ID INTEGER NOTNULL, INV_OPER_ID VARCHAR(30) NOTNULL ); INV_OPER_ID cannot accommodate IDs with 12 characters This creates three new columns - inventory, INV_ID and INV_OPER_ID in the database This is not a valid SQL code example per the DML notations This is a valid DML Statement Data types will have no major validity issues during compilation

INV_OPER_ID cannot accommodate IDs with 12 characters This creates three new columns - inventory, INV_ID and INV_OPER_ID in the database This is not a valid SQL code example per the DML notations This is a valid DML Statement Data types will have no major validity issues during compilation

Which two of the following are FALSE about the following SQL code? SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; It sorts columns by Country and then by CustomerName It displays only the data from the Country and CustomerName columns It operates on the Customers and Country Tables It sorts rows by Country and then by CustomerName

It sorts columns by Country and then by CustomerName Correct It displays only the data from the Country and CustomerName columns

When fetching data from two tables, the _______________________ command is used in FROM clause before doing an equality check on common columns using the identifier keys. COMBINE JOIN CONCATENATE MERGE

Join

When you are executing SQL statements specifically in MS-Access database as seen in the course, which of the following pattern match selection criteria would return zip codes with the characters 704 in the first three spaces. LIKE "704%" LIKE "704*" LIKE "%704" LIKE "*704"

LIKE "704*"

We executed the following code fragment in MS-Access during class activities. This code fragment is an example of a ___________________________ SELECT Instructor.InstructorID, Instructor.InstructorLName, Instructor.InstructorFName FROM Instructor WHERE (((Instructor.InstructorLName)=[Enter Last Name]));

Par

In the practice examples, you were searching for records matching a criteria you specified. You searched for Bingham or Bing by hard coding the search criteria into the SQL statement. You have also seen another example where you did it differently to allow the system to show a dialog box for you to enter an input and search by the input which was more flexible. It is like keyword search in Google to act on that column of information. When doing this activity, you were using the feature of MS-Access which is ________________________________ Parameter query Keyword match search query Column dialog match query Column input match query

Parameter query

To distinguish one row i.e. one record from another, tables contain: Primary Key Specifications Existence Independence Specifications Row Integrity Specifications Relational Row Independence Specifications

Primary Key Specifications

Any foreign key field must agree with the primary key that is referenced by the foreign key. In other words, for every foreign key, there exists a primary key in another table used as a foreign key in the current table. This is a definition of: Referential Integrity Relational Row Referencing Key Dependence Concurrence Logical Model Referring Integrity

Referential Integrity

A_____________________database is a type of database that stores information in multiple tables but appears to the user as if it is getting data only from one table.

Relational

A_______________helps to view, format and summarize information in your database. Attribute Field Query Report

Report

The WHERE Command restricts the selection of_______________based on a conditional expression Values Columns Primary Keys Rows

Rows

A ___________________is a storage container for data. View Query Report Table

Table

Which of the following is TRUE about the SQL code snippet? CREATE TABLE OrderLine_T (OrderID NUMBER(11,0) NOT NULL, ProductID INTEGER, OrderedQuantity NUMBER(11,0) CONSTRAINT OrderLine_PK PRIMARY KEY (OrderID), CONSTRAINT OrderLine_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID), CONSTRAINT OrderLine_FK1 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID)); A constraint is required on OrderLine column It creates 4 columns in the database table - Order ID, ProductID, OrderedQuantity, OrderLine The OrderLine table is linked to two other tables A constraint is required on ProductID. It contains one primary key and one foreign key It contains one foreign key to link to other tables A constraint is required on OrderedQuantity column

The OrderLine table is linked to two other tables A constraint is required on ProductID.

Which of the following are (more than one) true about the following code? CREATE VIEW [Current Product List] As SELECT ProductID, ProductName FROM Products WHERE Discontinued = No; The WHERE command helps to select and display all discontinued products The CREATE statement creates new table 'Current Product List' in the database It creates a dynamic subset of rows and columns based on matching the criteria The query searches three columns in the database It fetches results from ProductID and ProductName tables in the database

The query searches three columns in the database It creates a dynamic subset of rows and columns based on matching the criteria

Which one or more of the following is TRUE about the following code fragment? SELECT SUM(salary) AS "Total Salary" FROM employees WHERE salary > 25000; It operates on "manager" column before aggregating records The query returns the output as salary. This operates on the salary table This operates on the employees table

This operates on the employees table

In the database tables, latest records of the incoming data will be found at the bottom of the tables. True False

True

Per what you learned in the class, which of the following is TRUE about INSERT statement? Input of records that has some null attributes does require identifying the fields that actually get the data INSERT helps to circumvent constraints and insert duplicate values into the primary key field INSERT is a data definition language statement because it creates new tables in the database You can insert rows and columns from one table into another table, for example, when taking a subset of data to populate another table It updates current values in one or more rows in a table

You can insert rows and columns from one table into another table, for example, when taking a subset of data to populate another table Input of records that has some null attributes does require identifying the fields that actually get the data

A ___________________ is a characteristic of a field that determines what type of data it can hold. data type category value type designated data type column characteristic

data type