CSC 484 Review
Which of the following types of constraints can't be created at the table level? a. NOT NULL b. PRIMARY KEY c. CHECK d. FOREIGN KEY e. None of the above constraints can be created at the table level.
A
Which of the following types of restrictions can be viewed with the DESCRIBE command? a. NOT NULL b. FOREIGN KEY c. UNIQUE d. CHECK
A
What is the maximum number of columns you can define as a primary key when using the column-level approach to creating a table? a. 0 b. 1 c. 30 d. 255
B
What is the maximum number of PRIMARY KEY constraints allowed for a table? a. 1 b. 2 c. 30 d. 255
A
Which clause is not required in a SELECT statement? a. SELECT b. FROM c. WHERE d. All of the above clauses are required.
C
Which clause is used to restrict rows or perform selection? a. SELECT b. FROM c. WHERE d. ORDER BY
C
Which of the following symbols is used for a column alias containing spaces? a. '' b. || c. "" d. //
C
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;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
C
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;Which line of the SELECT statement is used to group data stored in the database? a. 1 b. 3 c. 4 d. 5
C
The FOREIGN KEY constraint should be added to which table? a. the table representing the "one" side of a one-to-many relationship b. the parent table in a parent-child relationship c. the child table in a parent-child relationship d. the table that doesn't have a primary key
C
Which of the following SELECT statements displays a list of customer names from the CUSTOMERS table? a. SELECT customer names FROM customers; b. SELECT "Names" FROM customers; c. SELECT firstname, lastname FROM customers; d. SELECT firstname, lastname, FROM customers; e. SELECT firstname, lastname, "Customer Names" FROM customers;
C
How many NOT NULL constraints can be created at the table level by using the CREATE TABLE command? a. 0 b. 1 c. 12 d. 30 e. 255
A
What's the default sort sequence for the ORDER BY clause? a. ascending b. descending c. the order in which records are stored in the table d. There's no default sort sequence.
A
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
A
Which of the following commands can be used to add rows to a table? a. INSERT INTO b. ALTER TABLE ... ADD c. UPDATE d. SELECT ... FOR UPDATE
A
Which of the following commands displays a listing of the category for each book in the BOOKS table? a. SELECT title books, category FROM books; b. SELECT title, books, and category FROM books; c. SELECT title, cat FROM books; d. SELECT books, | | category "Categories" FROM books
A
Which of the following displays all books published by Publisher 1 with a retail price of at least $25.00? a. SELECT * FROM books WHERE pubid ¼ 1 AND retail >¼ 25; b. SELECT * FROM books WHERE pubid ¼ 1 OR retail >¼ 25; c. SELECT * FROM books WHERE pubid ¼ 1 AND WHERE retail > 25; d. SELECT * FROM books WHERE pubid ¼ 1, retail >¼ 25; e. SELECT * FROM books WHERE pubid ¼ 1, retail >¼ $25.00;
A
Which of the following doesn't include the display of books published by Publisher 2 and having a retail price of at least $35.00? a. SELECT * FROM books WHERE pubid ¼ 2, retail >¼ $35.00; b. SELECT * FROM books WHERE pubid ¼ 2 AND NOT retail < 35; c. SELECT * FROM books WHERE pubid IN (1, 2, 5) AND retail NOT BETWEEN 1 AND 29.99; d. All the above statements display the specified books. e. None of the above statements display the specified books
A
Which of the following doesn't return a customer with the last name THOMPSON in the query results? a. SELECT lastname FROM customers WHERE lastname ¼ "THOMPSON"; b. SELECT * FROM customers; c. SELECT lastname FROM customers WHERE lastname > 'R'; d. SELECT * FROM customers WHERE lastname < 'V';
A
Which of the following includes the book HOW TO GET FASTER PIZZA in the query results? a. SELECT * FROM books WHERE title LIKE '%AS_E%'; b. SELECT * FROM books WHERE title LIKE 'AS_E%'; c. SELECT * FROM books WHERE title ¼ '%AS_E%'; d. SELECT * FROM books WHERE title ¼ 'AS_E%'
A
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;
A
Which of the following is not a correct statement? a. A table can be modified only if it doesn't contain any rows of data. b. The maximum number of characters in a table name is 30. c. You can add more than one column at a time to a table. d. You can't recover data contained in a table that has been truncated.
A
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#
A
Which of the following is not a valid constraint type? a. PRIMARY KEYS b. UNIQUE c. CHECK d. FOREIGN KEY
A
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
A
Which of the following lines of the SELECT statement contains an error? 1 SELECT ISBN, || ' is the ISBN for the book named ' || 2 title 3 FROM books; a. line 1 b. line 2 c. line 3 d. There are no errors.
A
Which of the following lines of the SELECT statement contains an error? 1 SELECT name, contact, 2 "Person to Call", phone 3 FROM publisher; a. line 1 b. line 2 c. line 3 d. There are no errors.
A
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
A
Which of the following commands can you use to rename a constraint? a. RENAME b. ALTER CONSTRAINT c. MOVE d. NEW NAME e. None of the above commands can be used.
E
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;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
B
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;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
B
In the initial creation of a table, if a UNIQUE constraint is included for a composite column that requires the combination of entries in the specified columns to be unique, which of the following statements is correct? a. The constraint can be created only with the ALTER TABLE command. b. The constraint can be created only with the table-level approach. c. The constraint can be created only with the column-level approach. d. The constraint can be created only with the ALTER TABLE ... MODIFY command
B
Which of the following SELECT statements returns all fields in the ORDERS table? a. SELECT customer#, order#, orderdate, shipped, address FROM orders; b. SELECT * FROM orders; c. SELECT ? FROM orders; d. SELECT ALL FROM orders;
B
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;
B
Which of the following functions can be used to include NULL values in calculations? a. SUM b. NVL c. MAX d. MIN
B
Which of the following includes a customer with the first name BONITA in the results? a. SELECT * FROM customers WHERE firstname ¼ 'B%'; b. SELECT * FROM customers WHERE firstname LIKE '%N%'; c. SELECT * FROM customers WHERE firstname ¼ '%N%'; d. SELECT * FROM customers WHERE firstname LIKE '_B%';
B
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#
B
Which of the following is not a valid SQL statement? a. CREATE TABLE anothernewtable(newtableid VARCHAR2(2)); b. CREATE TABLE anothernewtable(date, anotherdate) AS (SELECT orderdate, shipdate FROM orders); c. CREATE TABLE anothernewtable(firstdate, seconddate) AS (SELECT orderdate, shipdate FROM orders); d. All of the above are valid statements.
B
Which of the following lines of the SELECT statement contains an error? 1 SELECT * 2 FROM publishers; a. line 1 b. line 2 c. There are no errors
B
Which of the following lines of the SELECT statement contains an error? 1 SELECT title, category 2 FORM books; a. line 1 b. line 2 c. There are no errors.
B
Which of the following symbols represents concatenation? a. * b. || c. [] d. ''
B
You're creating a new table consisting of three columns: Col1, Col2, and Col3. Col1 should be the primary key and can't have any NULL values, and each entry should be unique. Col3 must not contain any NULL values either. How many total constraints do you have to create? a. 1 b. 2 c. 3 d. 4
B
Which of the following SQL statements is invalid and returns an error message? a. ALTER TABLE books ADD CONSTRAINT books_pubid_uk UNIQUE (pubid); b. ALTER TABLE books ADD CONSTRAINT books_pubid_pk PRIMARY KEY (pubid); c. ALTER TABLE books ADD CONSTRAINT books_pubid_nn NOT NULL (pubid); d. ALTER TABLE books ADD CONSTRAINT books_pubid_fk FOREIGN KEY (pubid) REFERENCES publisher (pubid); e. All of the above statements are invalid.
C
Which of the following SQL statements isn't valid? a. SELECT address || city || state || zip "Address" FROM customers WHERE lastname ¼ 'SMITH'; b. SELECT * FROM publisher ORDER BY contact; c. SELECT address, city, state, zip FROM customers WHERE lastname ¼ "SMITH"; d. All the above statements are valid and return the expected results.
C
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
C
Which of the following is a correct statement? a. You can restore the data deleted with the DROP COLUMN clause, but not the data deleted with the SET UNUSED clause. b. You can't create empty tables—all tables must contain at least three rows of data. c. A table can contain a maximum of 1000 columns. d. The maximum length of a table name is 265 characters.
C
Which of the following is a valid SELECT statement? a. SELECT TITLES * TITLE! FROM BOOKS; b. SELECT "customer#" FROM books; c. SELECT title AS "Book Title" from books; d. all of the above
C
Which of the following is a valid SQL statement? a. ALTER TABLE secustomersspent ADD DATE lastorder; b. ALTER TABLE secustomerorders DROP retail; c. CREATE TABLE newtable AS(SELECT * FROM customers); d. ALTER TABLE drop column *
C
Which of the following is a valid SQL statement? a. CREATE TABLE tablel (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20) PRIMARY KEY, col3 DATE DEFAULT SYSDATE, col4 VARCHAR2(2)); b. CREATE TABLE tablel (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 DATE, col4 VARCHAR2 (2) NOT NULL, CONSTRAINT tablel_col3_ck CHECK (col3=SYSDATE)); c. CREATE TABLE tablel (col1 NUMBER, col2 VARCHAR2(20), col3 DATE, col4 VARCHAR2(2), PRIMARY KEY (coll)); d. CREATE TABLE tablel (coll NUMBER, col2 VARCHAR2(20), col3 DATE DEFAULT SYSDATE, col4 VARCHAR2(2)
C
Which of the following lines of the SELECT statement contains an error? 1 SELECT title, cost, 2 cost*2 3 'With 200% Markup' 4 FROM books; a. line 1 b. line 2 c. line 3 d. line 4 e. There are no errors.
C
Which of the following represents exactly one character in a pattern search? a. ESCAPE b. ? c. - d. % e. none of the above
C
Which of the following returns the book HANDCRANKED COMPUTERS in the results? a. SELECT * FROM books WHERE title ¼ 'H_N_%'; b. SELECT * FROM books WHERE title LIKE "H_N_C%"; c. SELECT * FROM books WHERE title LIKE 'H_N_C%'; d. SELECT * FROM books WHERE title LIKE '_H%';
C
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.
C
Which of the following symbols is used in a SELECT clause to display all columns from a table? a. / b. & c. * d. "
C
Which of the following symbols represents any number of characters in a pattern search? a. * b. ? c. % d. -
C
Which type of constraint should you use on a column to allow entering only values above 100? a. PRIMARY KEY b. UNIQUE c. CHECK d. NOT NULL
C
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;Which line of the SELECT statement is used to restrict groups displayed in the query results? a. 1 b. 3 c. 4 d. 5
D
Suppose you created a PRIMARY KEY constraint at the same time you created a table and later decide to name the constraint. Which of the following commands can you use to change the constraint's name? a. ALTER TABLE ... MODIFY b. ALTER TABLE ... ADD c. ALTER TABLE ... DISABLE d. None of the above commands can be used
D
When must a comma be used in the SELECT clause of a query? a. when a field name is followed by a column alias b. to separate the SELECT clause and the FROM clause when only one field is selected c. It's never used in the SELECT clause. d. when listing more than one field name and the fields aren't concatenated e. when an arithmetic expression is included in the SELECT clause
D
Which clause is required in a SELECT statement? a. WHERE b. ORDER BY c. GROUP BY d. FROM e. all of the above
D
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
D
Which of the following SQL statements is valid? a. SELECT order# FROM orders WHERE shipdate ¼ NULL; b. SELECT order# FROM orders WHERE shipdate ¼ 'NULL'; c. SELECT order# FROM orders WHERE shipdate ¼ "NULL"; d. None of the statements are valid.
D
Which of the following commands can be used to enable a disabled constraint? a. ALTER TABLE ... MODIFY b. ALTER TABLE ... ADD c. ALTER TABLE ... DISABLE d. ALTER TABLE ... ENABLE
D
Which of the following data dictionary objects should be used to view information about the constraints in a database? a. USER_TABLES b. USER_RULES c. USER_COLUMNS d. USER_CONSTRAINTS e. None of the above objects should be used.
D
Which of the following finds all orders placed before April 5, 2009 that haven't yet shipped? a. SELECT * FROM orders WHERE orderdate < '04-05-09' AND shipdate ¼ NULL; b. SELECT * FROM orders WHERE orderdate < '05-04-09' AND shipdate IS NULL; c. SELECT * FROM orders WHERE orderdate < 'APR-05-09' AND shipdate IS NULL; d. SELECT * FROM orders WHERE orderdate < '05-APR-09' AND shipdate IS NULL; e. none of the above
D
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
D
Which of the following is a valid SQL statement? a. SELECT * WHERE amid = 'J100' FOR UPDATE; b. INSERT INTO homeworkl0 VALUES (SELECT * FROM acctmanager); c. DELETE amid FROM acctmanager; d. rollback; e. all of the above
D
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;
D
Which of the following is not a valid SELECT statement? a. SELECT cost-retail FROM books; b. SELECT retail+cost FROM books; c. SELECT retail * retail * retail FROM books; d. SELECT retail^3 from books;
D
Which of the following is not a valid SELECT statement? a. SELECT lastname, firstname FROM customers; b. SELECT * FROM orders; c. Select FirstName NAME from CUSTOMERS; d. SELECT lastname Last Name FROM customers;
D
Which of the following is the valid syntax for adding a PRIMARY KEY constraint to an existing table? a. ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (columnname); b. ALTER TABLE tablename ADD CONSTRAINT (columnname) PRIMARY KEY constraintname; c. ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY; d. None of the above is valid syntax.
D
Which of the following is true? a. If you truncate a table, you can't add new data to the table. b. If you change the default value of an existing column, all existing rows containing a NULL value in the same column are set to the new DEFAULT value. c. If you delete a column from a table, you can't add a column to the table with the same name as the previously deleted column. d. If you add a column to an existing table, it 's always added as the last column of the table.
D
Which of the following lines of the SELECT statement contains an error? 1 SELECT ISBN, 2 retail-cost 3 FROM books; a. line 1 b. line 2 c. line 3 d. There are no errors.
D
Which of the following lines of the SELECT statement contains an error? 1 SELECT name, contact 2 "Person to Call", phone 3 FROM publisher; a. line 1 b. line 2 c. line 3 d. There are no errors.
D
Which of the following lines of the SELECT statement contains an error? 1 SELECT title, isbn, 2 Pubdate "Date of Publication" 3 FROM books; a. line 1 b. line 2 c. line 3 d. There are no errors.
D
Which of the following lists each book having a profit of at least $10.00 in descending order by profit? a. SELECT * FROM books WHERE profit ¼> 10.00 ORDER BY "Profit" desc; b. SELECT title, retail-cost "Profit" FROM books WHERE profit ¼> 10.00 ORDER BY "Profit" desc; c. SELECT title, retail-cost "Profit" FROM books WHERE "Profit" ¼> 10.00 ORDER BY "Profit" desc; d. SELECT title, retail-cost profit FROM books WHERE retail-cost >¼ 10.00 ORDER BY "PROFIT" desc; e. SELECT title, retail-cost "Profit" FROM books WHERE profit ¼> 10.00 ORDER BY 3 desc;
D
Which of the following returns a list of all customers' names sorted in descending order by city within state? a. SELECT name FROM customers ORDER BY desc state, cityb. SELECT firstname, lastname FROM customers SORT BY desc state, city; c. SELECT firstname, lastname FROM customers ORDER BY state desc, city; d. SELECT firstname, lastname FROM customers ORDER BY state desc, city desc; e. SELECT firstname, lastname FROM customers ORDER BY 5 desc, 6 desc;
D
Which of the following statements is correct? a. A PRIMARY KEY constraint allows NULL values in the primary key column(s). b. You can enable a dropped constraint if you need it in the future. c. Every table must have at least one PRIMARY KEY constraint, or Oracle 12c doesn't allow the table to be created. d. None of the above statements is correct.
D
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.
D
Which of the following is a correct statement? a. A commit is issued implicitly when a user exits SQL Developer or SQL*Plus. b. A commit is issued implicitly when a DDL command is executed. c. A commit is issued automatically when a DML command is executed. d. All of the above are correct. e. Both a and b are correct. f. Both a and c are correct.
E
Which of the following is a valid SQL command? a. ALTER TABLE books ADD CONSTRAINT UNIQUE (pubid); b. ALTER TABLE books ADD CONSTRAINT PRIMARY KEY (pubid); c. ALTER TABLE books MODIFY (pubid CONSTRAINT NOT NULL); d. ALTER TABLE books ADD FOREIGN KEY CONSTRAINT (pubid) REFERENCES publisher (pubid); e. None of the above commands is valid.
E
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
E
Which of the following keywords allows the user to delete a record from a table, even if rows in another table reference the record through a FOREIGN KEY constraint? a. CASCADE b. CASCADE ON DELETE c. DELETE ON CASCADE d. DROP e. ON DELETE CASCADE
E
Which of the following lists all books published before June 2, 2004 and all books published by Publisher 4 or in the Fitness category? a. SELECT * FROM books WHERE category ¼ 'FITNESS' OR pubid ¼ 4 AND pubdate < '06-02-2004'; b. SELECT * FROM books WHERE category ¼ 'FITNESS' AND pubid ¼ 4 OR pubdate < '06-02-2004'; c. SELECT * FROM books WHERE category ¼ 'FITNESS' OR (pubid ¼ 4 AND pubdate < '06-02-2004'); d. SELECT * FROM books WHERE category ¼ 'FITNESS' OR pubid ¼ 4, pubdate < '06-02-04'; e. none of the abov
E
Which of the following lists books generating at least $12.00 in profit? a. SELECT * FROM books WHERE retail-cost > 12; b. SELECT * FROM books WHERE retail-cost <¼ 12; c. SELECT * FROM books WHERE profit >¼ 12; d. SELECT * FROM books WHERE retail-cost ¼> 12.00; e. none of the above
E
Which of the following returns all books published after March 20, 2005? a. SELECT * FROM books WHERE pubdate > 03-20-2005; b. SELECT * FROM books WHERE pubdate > '03-20-2005'; c. SELECT * FROM books WHERE pubdate NOT < '20-MAR-05'; d. SELECT * FROM books WHERE pubdate NOT < 'MAR-20-05'; e. none of the above
E
Which of the following returns the book HANDCRANKED COMPUTERS in the results? a. SELECT * FROM books WHERE title ¼ 'H_N_%'; b. SELECT * FROM books WHERE title LIKE "H_N_C%"; c. SELECT * FROM books WHERE title LIKE 'H_N_C%'; d. SELECT * FROM books WHERE title LIKE '_H%';
E