Database Midterm

Consider the following expression: CEIL(33.65) Which of the following will be the output of this expression?

34

Consider the following expression: floor(35.29) Which of the following will be the output of this expression?

35

Which of the following are single row operators? Each correct answer represents a complete solution. Choose two.

<> >=

Which of the following statements are true about a PRIMARY KEY constraint? Each correct answer represents a complete solution. Choose two.

A PRIMARY KEY enforces the integrity of a table. A PRIMARY KEY constraint does not allow duplicate values in a column

Which of the following operators must always be preceded by any of the single-row conditional operators and are used to compare a value to each value returned by the subquery? Each correct answer represents a complete solution. Choose three.

ALL ANY SOME

Consider the following table named Customer. You want to keep records of phone numbers of customers. For this, you have to add a column named PhoneNo in the Customer table. Which of the following commands will allow you to accomplish the task?

ALTER

Which of the following group functions returns the mean of an expression returned by a SELECT statement and ignores NULL values?

AVG

You want to replace a condition that uses the <= and >= operators. Which of the following operators will allow you to accomplish this task?

BETWEEN

Which of the following conversion functions converts data into programmer-defined data type

CAST

Consider the following expression: SELECT SUBSTR('ABCDEFG',3,4) "Subs" FROM DUAL; Which of the following will be the output of this query?

CDEF

Which of the following constraints enforces domain integrity by limiting the values that are accepted by a column?

CHECK

Which of the following numeric functions returns the next higher integer?

CIEL

You want to retrieve the first non-NULL value in a series of expressions. Which of the following functions will allow you to accomplish this task?

COALESCE

Which of the following is a transaction control statement that ends the current database transaction and makes all changes made in that transaction permanent?

COMMIT

Which of the following functions is used to convert a string from one character set to another?

CONVERT

Which of the following are formats of the COUNT function? Each correct answer represents a complete solution. Choose three.

COUNT (*) COUNT (ALL ColumnName) COUNT (DISTINCT ColumnName)

Which of the following commands are classified as Data Definition Language (DDL) commands? Each correct answer represents a complete solution. Choose three.

CREATE DROP ALTER

You need to duplicate the structure and data of the EMP table in the EMPLOYEES table. Which of the following statements will allow you to accomplish this task?

CREATE TABLE EMPLOYEES AS SELECT * FROM EMP;

Which of the following are schema objects? Each correct answer represents a complete solution. Choose three.

Cluster Database link Constraint

Which of the following schemas have their own namespaces? Each correct answer represents a complete solution. Choose three.

Clusters Indexes Dimensions

You need to create a table named Customers that meets all these requirements: Create five columns in this sequence: CustomerID, LastName, FirstName, Address, and City. The data type of each column should be varchar. The length of all fields should be 150 characters except CustomerID, which should be 20 characters. The value of the CustomerID column must be unique and not null. Which of the following SQL statements would allow you to meet these requirements. Each correct answer represents a complete soluion. Choose two.

Create table Customers(CustomerID varchar(20) CONSTRAINT cust_id_nn NOT NULL,LastName varchar(150),FirstName varchar(150),Address varchar(150),City varchar(150),CONSTRAINT cust_id_uk UNIQUE (CustomerID)); Create table Customers(CustomerID varchar(20),LastName varchar(150),FirstName varchar(150),Address varchar(150),City varchar(150),CONSTRAINT cust_id_uk UNIQUE (CustomerID),CONSTRAINT cust_id_nn NOT NULL (CustomerID));

Which of the following conversion functions is used to convert a Unicode string to a string?

DECOMPOSE

Which of the following statements will delete all rows in a table without deleting the table? Each correct answer represents a complete solution. Choose two.

DELETE FROM table_name DELETE table_name

Which of the following types of schema objects can be created and manipulated with SQL? Each correct answer represents a complete solution. Choose all that apply.

Database links Database triggers Tablespaces

Which of the following modifications to a table's structure are possible by using the ALTER TABLE statement? Each correct answer represents a complete solution. Choose all that apply.

Dropping a column with a timestamp data type Adding or dropping columns Changing the nullability constraint for a column Renaming the table Adding or dropping indexes

Which of the following multiple row operators is true when the subquery returns any rows?

EXISTS

Which of the following types of join combines rows that have equivalent values for the specified columns?

Equijoin

Which of the following clauses limits the number of rows returned by a given query in Oracle Database 12c?

FETCH FIRST

Which of the following constraints establishes a parent-child relationship between tables by using common columns?

FOREIGN KEY

You want to specify a table to be accessed. Which of the following clauses will allow you to accomplish this task?

FROM

Mark works as a Database Administrator for uCertify Inc. He has to create a view in a table named Sales that is included in a database named Northwind. However, he notices that he does not have permission to make changes in the Northwind database. Which of the following commands will provide him access or privileges to the database?

GRANT

Which of the following character functions finds the numeric starting position of a string within a string?

INSTR

In which of the following conditions does Oracle Database return the Cartesian product?

If two tables in a join query have no join condition

Which of the following statements are true about PL/SQL? Each correct answer represents a complete solution. Choose all that apply.

It is an Oracle specific language that adds procedural capabilities to SQL. It allows you to create processes that have loops, conditional logic, and other functionality associated with 3GL programming languages.

Which of the following statements correctly describes the GROUPING function? Each correct answer represents a complete solution. Choose three.

It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE. It returns a single result based on a number of rows as opposed to the single row functions. It operates on one or more groups of rows and gives one result per group.

You want to find out the combined Total Salary of all employees whose salary is above $15,000 per year. You write the following SQL statements: SELECT SUM(salary) as "Total Salary" FROM employees WHERE salary > 15000; Which of the following will happen when you execute this query?

It will execute successfully and give the desired output.

Which of the following elements in the database management system manages memory and storage?

Kernel code

Which of the following operators returns unique rows selected by the first query but not the rows selected from the second query?

MINUS

Which of the following schema objects share one namespace? Each correct answer represents a complete solution. Choose three.

Materialized views Private synonyms Packages

In which of the following situations can aliases be useful? Each correct answer represents a complete solution. Choose three.

More than one table is used in a query. Functions are used in a query. More than one column is used in a query.

Consider the exhibit given below: SQL> DESC CUSTOMER_KNOWN_GOOD; SQL> SELECT * FROM CUSTOMER_KNOWN_GOOD; 6 rows selected SQL> DESC CUSTOMER_TEST; SQL> SELECT * FROM CUSTOMER_TEST; What will be the output of the following query? (SELECT * FROM CUSTOMER_KNOWN_GOODMINUSSELECT * FROM CUSTOMER_TEST)UNION ALL(SELECT * FROM CUSTOMER_TESTMINUSSELECT * FROM CUSTOMER_KNOWN_GOOD);

No Rows

Which of the following are features of the UNION operator? Each correct answer represents a complete solution. Choose three.

Null values are not ignored by the UNION operator during duplicate checking. It sorts the output by default. It merges the output of two or more queries into a single set of rows and columns.

Which of the following types of joins retrieves all values in a certain table regardless of whether these values are present in other tables?

OUTER JOIN

Which of the following commands is used to terminate a transaction?

ROLLBACK without the TO SAVEPOINT clause

Which of the following terms in relational theory is referred to as a set of tuples?

Relation

Which of the following tasks can you perform using SQL statements? Each correct answer represents a complete solution. Choose all that apply.

Retrieving records from a database Inserting, updating, and deleting rows in a table Creating, replacing, altering, and dropping objects

Which of the following DML commands retrieves data from a database table?

SELECT

Consider the following table named Customer. You want to find the customer records for those customers headquartered in Atlanta, Chicago, or Washington. Which of the following queries will allow you to accomplish the task? Each correct answer represents a complete solution. Choose two.

SELECT * FROM CUSTOMER WHERE HQCITY IN ('Atlanta', 'Chicago', 'Washington'); SELECT * FROM CUSTOMER WHERE (HQCITY='Atlanta' OR HQCITY='Chicago' OR HQCITY='Washington');

You want to retrieve the entire record of a table named SALESPERSON. Which of the following queries will allow you to accomplish this task?

SELECT * FROM SALESPERSON;

Which of the following are correct syntaxes of using the ORDER BY clause? Each correct answer represents a complete solution. Choose all that apply.

SELECT * FROM tables WHERE condition ORDER BY column ASC/DESC; SELECT columns FROM tables WHERE condition ORDER BY column ASC/DESC; SELECT columns FROM tables ORDER BY column ASC/DESC;

Consider the following table named CUSTOMER: Find the customer numbers, customer names, and headquarter cities of those customers with customer numbers greater than 1000 and list the results in alphabetical order by headquarter cities.Which of the following statements will allow you to retrieve the desired output?

SELECT CUSTNUM, CUSTNAME, HQCITY FROM CUSTOMER WHERE CUSTNUM>1000 ORDER BY HQCITY;

Consider the following table named Customer. Which of the following statements will return the CUSTNUM, CUSTNAME, and SPNUM of all customers arranged by HQCITY in ascending order? Each correct answer represents a complete solution. Choose two.

SELECT CUSTNUM, CUSTNAME, SPNUM FROM CUSTOMER ORDER BY HQCITY; SELECT CUSTNUM, CUSTNAME, SPNUM FROM CUSTOMER ORDER BY HQCITY ASC;

You work as a Database Administrator for uCertify Inc. The company uses Oracle Database 12c in its environment. The company has a database named uCertifydb that includes a table named Employees. You want to retrieve the EmpID, EmpName, and EmpSal for all those employees whose EmpSal is below 50000 after the addition of 20000 bonus in their salaries. Which of the following queries will allow you to accomplish this task?

SELECT EmpID, EmpName, EmpSal+20000 AS ActualEmpSal from Employees WHERE EmpSal+20000 < 50000;

Consider the following table named EMPLOYEE. You are required to retrieve the first names of those employees whose commission values are unknown. Which of the following queries will allow you to accomplish this task?

SELECT FirstName FROM EMPLOYEE WHERE Commission IS NULL;

Which of the following statements retrieves data from one or more database tables and assigns the selected values to variables or collections?

SELECT INTO

Consider the following two tables named Customer and Orders: You want to retrieve the OrderID and CustomerName columns of the Orders table. Which of the following queries will allow you to accomplish this task?

SELECT Orders.OrderID, Customer.CustomerName FROM Orders RIGHT JOIN Customer ON Orders.CustomerID=Customer.CustomerID ORDER BY Orders.OrderID;

Consider the following table named SALESPERSON. You want to find out which salespersons with salesperson numbers greater than 200 have the lowest commission percentage. You need to identify salespersons by their salesperson number. Which of the following queries will allow you to accomplish this task?

SELECT SPNUM, MIN(COMMPERCT) FROM SALESPERSON WHERE SPNUM>200;

Consider the following two simplified Branch and PropertyForRent tables, which we refer to as Branch1 and PropertyForRent1, respectively: You have to list the branch offices and properties that are in the same city along with any unmatched branches or properties. Which of the following queries will fulfill this requirement?

SELECT b.*, p.* FROM Branch1 b FULL OUTER JOIN PropertyForRent1 p ON b.bCity = p.pCity;

Which of the following SQL statements will retrieve all rows from the Dealer table and only the matching rows from the Car table? Note The Dealer table is on the right side and the Car table is on the left side.

SELECT c.CarName, c.CarModel, c.CarPrice, d.DealerName, d.Commission FROM Car c RIGHT OUTER JOIN Dealer d ON c.CarID=d.CarID;

At which of the following locations can single row functions be used? Each correct answer represents a complete solution. Choose three.

SELECT lists HAVING clauses CONNECT BY clauses

You work as a Database Administrator for uCertify Inc. You maintain a database named Northwind that contains the tables named Orderdetails and Orders. Orderdetails table is as follows: The Orders table is as follows: The OrderID column in the Orderdetails table is defined as the primary key, and the OrderID column in the Orders table is defined as the foreign key. You want to retrieve all rows from the Orderdetails table and only the matching rows from the Orders table, as shown in the table below: Which of the following SQL queries will allow you to retrieve the required output?

SELECT od.ProductID, od.UnitPrice, od.Quantity, o.CustomerID, o.EmployeeID FROM Orderdetails od LEFT OUTER JOIN Orders o ON od.OrderID=o.OrderID;

Consider the following table named Staff. You want to retrieve all managers and supervisors from the Staff table. Which of the following query will allow you to accomplish this task?

SELECT staffNo, fName, IName, position FROM Staff WHERE position IN ('Manager', 'Supervisor');

Which of the following types of work does the SELECT statement perform to restrict the number of rows based on a condition while retrieving data from a database?

SELECTION

Which of the following subqueries returns exactly one column value from one row?

Scalar subquery

You are working on a database that has two tables named Customers and Suppliers. The tables have a common column named City. Which of the following operators will allow you to retrieve all cities (duplicate values also) from the Customers and Suppliers tables?

Select City from Customers UNION ALL Select City from Suppliers;

You want to return rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. Which of the following types of join will you use to accomplish this task?

Semijoin

You want to execute a query that will delete all the data of a table named Customers but not affect the structure of the table. Which of the following queries will allow you to meet this requirement?

TRUNCATE TABLE Customers;

Which of the following statements are true for the ORDER BY clause? Each correct answer represents a complete solution. Choose two.

The ORDER BY clause sorts the result set based on the columns specified. The ORDER BY clause is used with the SELECT statement.

Which of the following statements are true about the SQL SELECT statement? Each correct answer represents a complete solution. Choose two.

The SQL SELECT statement can also be used to accomplish a relational Project operation. SELECT statements always end with a single semicolon (;) at the very end of the entire statement.

You have a table named SALES: To find the total number of units of all products sold by each salesperson, you write the following query: SELECT SPNUM, COUNT(QUANTITY) FROM SALES GROUP BY SPNUM; You are not getting the desired output. Which of the following corrections should be done in the query to get the desired output?

The SUM function should be used in the query instead of the COUNT function.

Which of the following are characteristics of uncommitted transactions? Each correct answer represents a complete solution. Choose three.

The changed data is NOT visible to any other user. The data that existed prior to the DML operation can be recovered by rolling back the transaction. The changed data is visible to the user that issued the DML.

Which of the following statements are true about set operators? Each correct answer represents a complete solution. Choose two.

The data type of each column in the second result set must match the data type of its corresponding column in the first result set. The order of columns in both the result sets should be the same.

Consider the following two expressions: SELECT LTRIM('Hello World','Hello')FROM dual; SELECT TRIM ('OLD' FROM 'OLDMAN') FROM DUAL; What will be the output of the following two expressions?

The output of first expression will be World and the output of the second expression will be MAN.

You have a table named Car that contains ten rows and a table named Dealer that contains twelve rows. You write the following SQL statement: SELECT c.CarName, c.CarModel, c.CarPrice, d.DealerName, d.Commission FROM Car c CROSS JOIN Dealer d ON c.CarID=d.CarID; What will be the output of this statement?

The result will be 120 rows.

For which of the following purposes can subqueries be used? Each correct answer represents a complete solution. Choose three.

To define the set of rows to be included in a view or materialized view To define the set of rows to be inserted into the target table To define a table to be operated on by a containing query

Consider the following table named Staff. Which of the following SQL statements will successfully update the salary of John to 60000?

UPDATE StaffSET Salary=60000WHERE fName='John';

A table named "employees" is given below: Which of the following statements will return the full names of the employees in ascending order, based on their last name? Each correct answer represents a complete solution. Choose all that apply.

select fname, lname from employees order by lname asc; select fname, lname from employees order by lname;