SQL 11g Chapter 3 Multiple Choice + SQL

11. The default width of a VARCHAR2 field is: a. 1 b. 30 c. 255 d. None-there's no default width for a VARCHAR2 field

d. None-there's no default width for a VARCHAR2 field

WHICH line in the following SQL statement raises an error? 1. SELECT name, title 2. FROM books NATURAL JOIN publisher 3. WHERE category = 'FITNESS' 4. OR 5. books.pubid =4; a. line 1 b. line 2 c. line 3 d. line 4 e. line 5

d. line 4

The folowing SQL statement contains which type of join? SELECT title, order#, quantity FROM books FULL JOIN orderitems ON books.isbn = orderitems.isbn; a. equality b. self-join c. non-equality d. outer join

d. outer join

Give the SQL command to create a table form the schema below. Students(sid: string, name:string, age:integer, gpa:real)

CREATE TABLE Students(sid VARCHAR(20), name VARCHAR(20), age INT, gpa REAL)

Give the create command to create the car table for the relationship below and be sure to connect it to the employee relation

CREATE TABLE car(car tags VARCHAR(10) PRIMARY KEY, ss# VARCHAR(9), type VARCHAR(20), FOREIGN KEY (ss#) REFERENCES employee)

State the SQL command to create the table for the employee relation only for the picture below

CREATE TABLE employee(ss# VARCHAR(9) PRIMARY KEY, name VARCHAR(20), salary NUMBER(9,2), car tags VARCHAR (10), FOREIGN KEY (car tags) REFERENCES car)

13. Which of the following characters can be used in a table name? a. - b. ( c. % d. !

a. -

3. 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. A table can be modified only if it doesn't contain any rows of data.

10. Which of the following commands changes a table's name from OLDNAME to NEWNAME? a. RENAME oldname TO newname; b. RENAME table FROM oldname TO newname; c. ALTER TABLE oldname MODIFY TO newname; d. CREATE TABLE newname (SELECT * FROM oldname);

a. RENAME oldname TO newname

Which of the following queries contains an equality join? a. SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20; b. SELECT title, name FROM books CROSS JOIN publisher; c. SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail; d. none of the above

a. SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20;

12. Which of the following is not a valid statement? a. You can change the name of a table only if it doesn't contain any data b. You can change the length of a column that doesn't contain any data. c. You can delete a column that doesn't contain any data d. You can add a column to a table.

a. You can change the name of a table only if it doesn't contain any data

20. Which of the following SQL statements changes the size of the Title column in the BOOKS table from the current length of characters to the length of 35 characters? a. ALTER TABLE books CHANGE title VARCHAR(35); b. ALTER TABLE books MODIFY (title VARCHAR2(35)); c. ALTER TABLE books MODIFY title (VARCHAR2(35)); d. ALTER TABLE books MODIFY (title VARCHAR2(+5));

b. ALTER TABLE books MODIFY (title VARCHAR2(35));

7. Which of the following commands drops any columns marked as unused from the SECUSTOMERORDERS table? a. DROP COLUMN FROM secustomerorders WHERE column_status = UNUSED; b. ALTER TABLE secustomerorders DROP UNUSED COLUMNS; c. ALTER TABLE secustomerorders DROP (unused); d. DROP UNUSED COLUMNS;

b. ALTER TABLE secustomerorders DROP UNUSED COLUMNS;

14. Which of the following is true? a. All data in a table can be recovered if the table is dropped with the PURGE option b. All data in a table can be recovered from the recycle bin if the table is dropped. c. All data in a table is lost if the table is dropped d. All of the above statements are true.

b. All data in a table can be recovered from the recycle bin if the table is dropped

17. Which of the following is a valid table name? a. 9NEWTABLE b. DATE9 c. NEW"TABLE d. None of the above are valid table names.

b. DATE9

Given the following query; SELECT zip, order# FROM customers NATURAL JOIN orders; Which of the following queries is equivalent? a. SELECT zip, order# FROM customers JOIN orders WHERE customers.customer# = orders. customer#; b. SELECT zip, order# FROM customers, orders Where customers.customer# = orders.customer#; c. SELECT zip, order# FROM customers, orders Where customers.customer# = orders.customer# (+); d. none of the above

b. SELECT zip, order#FROM customers, ordersWhere customers.customer# = orders.customer#;

Which of the following queries creates a Cartesian join? a. SELECT title, authorid FROM books, bookauthor; b. SELECT title, name FROM books CROSS JOIN publisher; c. SELECT title, gift FROM books NATURAL JOIN promotion; d. All of the above

b. SLECET title, name FROM books CROSS JOIN publisher;

9. Which of the following commands removes all data from a table but leaves the table's structure intact a. ALTER TABLE secustomerorders DROP UNUSED COLUMNS; b. TRUNCATE TABLE secustomerorders; c. DELETE TABLE secustomerorders; d. DROP TABLE secustomerorders;

b. TRUNCATE TABLE secustomerorders;

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. A table can contain a maximum of 1000 columns.

2. 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. CREATE TABLE newtable AS (SELECT * FROM customers);

Which of the following operators is not allowed in an outer join? a. AND b. = c. OR d. >

c. OR

Which of the following queries is valid? a. SELECT b.title, b.retail, o.quantity FROM books b NATURAL JOIN orders od NATURAL JOIN orderitems o WHERE od.order# = 1005; b. SELECT b.title, b.retail, o.quantity FROM books b, orders od, orderitems o WHERE orders.oroder# = orderitems.order# AND orderitems.isbn=books.isbn AND od.order#=1005 c. SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order#=1005; d. none of the above

c. SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order#=1005;

Given the following query; Select lastname, firstname, order# FROM customers LEFT OUTER JOIN orders USING (customer#) ORDER BY customer#; Which of the following queries returns the same results? a. SELECT lastname, firstname, order# FROM customers c OUTER JOIN orders o ON c.customer# = o.customer# ORDER BY c.customer#; b. SELECT lastname, firstname, order# FROM orders o RICHT OUTER JOIN customers c ON c.customer# = o.customer# ORDER BY c.customer#; c. SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer# (+) ORDER BY c.customer#; d. none of the above

c. SELECT lastname, firstname, order#FROM customers c, orders oWHERE c.customer# = o.customer# (+)ORDER BY c.customer#;

Which of the following queries contains a non-equality join? a. SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20; b. SELECT title, name FROM books JOIN publisher USING (pubid); c. SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail; d. none of the above

c. SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail;

19. Which object in the data dictionary enables you to verify DEFAULT column settings? a. DEFAULT_COLUMNS b. DEF_TAB_COLUMNS c. USER_TAB_COLUMNS d. None of the above

c. USER_TAB_COLUMNS

8. Which of the following statements is correct? a. A table can contain a maximum of only one column marked as unused. b. You can delete a table by removing all columns in the table. c. Using the SET UNUSED clause allows you to free up storage space used by a column. d. None of the above statements are correct.

c. Using the SET UNUSED clause allows you to free up storage space used by a column

15. Which of the following commands is valid? a. RENAME customer# TO customernumber FROM customers; b. ALTER TABLE customers RENAME customer# TO customernum; c. DELETE TABLE customers; d. ALTER TABLE customers DROP UNUSED COLUMNS;

d. ALTER TABLE customers DROP UNUSED COLMUNS

4. 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.

d. All of the above are valid statements

16. Which of the following commands creates a new table containing two columns? a. CREATE TABLE newname (col1 DATE, col2 VARCHAR2); b. CREATE TABLE newname AS (SELECT title, retail, cost FROM books); c. CREATE TABLE newname (col1, col2); d. CREATE TABLE newname (col1 DATE DEFAULT SYSDATE, col2 VARCHAR2(1));

d. CREATE TABLE newname (col1 DATE DEFAULT SYSDATE, col2 VARCHAR2(1));

6. Which of the following commands creates a new table containing a virtual column? a. CREATE TABLE newtable AS (SELECT order#, title, quantity, retail FROM orders); b. CREATE TABLE newtable (price NUMBER(3), total NUMBER(8,2)); c. CREATE TABLE newtable (calc1 NUMBER(4), calc2 NUMBER(4); d. CREATE TABLE newtable (cola NUMBER(3), colb NUMBER(3), colc AS (cola+colb));

d. CREATE TABLE newtable(cola NUMBER(3), colb NUMBER(3),colc AS (cola+colb));

5. 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. If you add a column to an existing table, it's always added as the last column of the table.

18. Which of the following is a valid datatype? a. CHAR3 b. VARCHAR4(3) c. NUM d. NUMBER

d. NUMBER