Download SQL SELECT Statement Exercises and more Exams Nursing in PDF only on Docsity!
CSC 484 Review
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; - correct answer C Which clause is required in a SELECT statement? a. WHERE b. ORDER BY c. GROUP BY d. FROM e. all of the above - correct answer 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; - correct answer D Which of the following symbols represents concatenation? a. * b. || c. [] d. '' - correct answer 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; - correct answer B Which of the following symbols is used for a column alias containing spaces? a. '' b. || c. "" d. // - correct answer 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 - correct answer C
Which of the following symbols is used in a SELECT clause to display all columns from a table? a. / b. & c.
- d. " - correct answer C 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; - correct answer 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 - correct answer D 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 - correct answer A Which clause is not required in a SELECT statement? a. SELECT b. FROM c. WHERE d. All of the above clauses are required. - correct answer C 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. - correct answer 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. - correct answer D 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. - correct answer C
FROM orders); c. CREATE TABLE anothernewtable(firstdate, seconddate) AS (SELECT orderdate, shipdate FROM orders); d. All of the above are valid statements. - correct answer B 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. - correct answer 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. - correct answer D Which of the following is not a valid constraint type? a. PRIMARY KEYS b. UNIQUE c. CHECK d. FOREIGN KEY - correct answer A 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. - correct answer C What is the maximum number of PRIMARY KEY constraints allowed for a table? a. 1 b. 2 c. 30 d. 255 - correct answer A 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. - correct answer E 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 - correct answer A
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 - correct answer C 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 - correct answer B 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. - correct answer E 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, col 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) - correct answer C 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 - correct answer B 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 - correct answer C 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 - correct answer D 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 - correct answer E
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 - correct answer A 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. - correct answer C Which clause is used to restrict rows or perform selection? a. SELECT b. FROM c. WHERE d. ORDER BY - correct answer C 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. - correct answer 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; - correct answer D 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'; - correct answer 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; - correct answer 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. - correct answer 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 - correct answer A 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%'; - correct answer B Which of the following represents exactly one character in a pattern search? a. ESCAPE b.? c. - d. % e. none of the above - correct answer 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%'; - correct answer 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%'; - correct answer E Which of the following SQL statements 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 > '20-MAR-05'; d. SELECT * FROM books WHERE pubdate > 'MAR-20-05'; - correct answer 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
data. d. The SUM function can't be part of a nested function. - correct answer C 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; - correct answer A 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. - correct answer D 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# - correct answer 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 - correct answer A 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# - correct answer B 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 - correct answer D
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 - correct answer E 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 - correct answer D 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 - correct answer A Which of the following functions can be used to include NULL values in calculations? a. SUM b. NVL c. MAX d. MIN - correct answer B 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 - correct answer A 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; - correct answer 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 - correct answer 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 groups displayed in the query results? a. 1 b. 3 c. 4 d. 5 - correct answer D
