SQL NO SQL quiz
18: Which of the following commands will add a new column named FIRST ORDER DATE to the CUSTOMER table to store the date that the customer first placed an order with the company
ALTER TABLE customer ADD frstorderdate DATE;
34 A constraint for a composite primary key must be created at the column level.
False
36 A table name can consist of numbers, letters, and blank spaces.
False
Question 10: The special operator is used to check whether an attribute value is null.
IS NULL
Question 11: The SQL command that allows a user to list the contents of a table is.
SELECT
27 If you are joining four tables in a SELECT statement, three joining conditions will be required
True
29 If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table
True
32 DDL commands are used to create or modify database tables.
True
33 Both the table-level and column-level approaches to creating a constraint can be included in the same command.
True
Question 30 The following examples are equivalent ways of issuing the same statement, even though they are entered differently and terminated differently: mysql> SELECT NOW(); mysql> SELECT NOW()\g
True
Question 31: The conditional LIKE must be used in conjunction with wildcard characters.
True
Question 33: A user can indicate that all columns of a table should be displayed in the results of a SELECT statement by including an asterisk (*) in the SELECT clause.
True
31 Which of the following set operators will display the results of the combined SQL statements without suppressing duplicate rows
UNION ALL
13 When new rows are being added to a table, the actual data being added are listed in the ____ clause.
VALUES
32 You can simulate a full outer join by using
a union
Question 24: A(n)_________ is an alternate name given to a column or table in any SQL statement.
alias
12 How can constraints be added to a table
as part of the CREATE TABLE command as part of the ALTER TABLE command
11 A PRIMARY KEY that consists of more than one column is called a ____ key
composite
1:The DATE data type can store
dates only
Question 6: Which of the following lets you run a command in the MySQL Monitor?
mysql> source script_name.sql
21: In which of the following examples is the PUBLISHER table used as a column qualifier
publisher.pub_id
28 When you use the USING keyword for a join
the join must be based on a column or columns that have the same name in both tables
7: Which is not true about the USING keyword
you code a USING clause in addition to the ON clause
14 The default date format for MySQL is _________________.
yyyy-mm-dd
4 The product of two tables is also called the ____ product.
Cartesian
3 DBMS is software used for the following purpose:
Creation of databases Insertion, storage, retrieval, update, and deletion of the data in the database Maintenance of databases
2 The correct data type for the BALANCE column in the CUSTOMER table is ____.
DECIMAL
Question 8: What command can be used to view the structure of a table?
DESCRIBE
30 Which of the following is an example of assigning "b" as a table alias for the BOOKS table in the FROM clause
FROM books b, publisher p
20 A(n) ____________________ outer join is necessary when you need rows returned from either table that do not have a matching record in the other table
FULL
40 A foreign key constraint can only be created at the column level
False
41 The JOIN keyword is used in the WHERE clause to indicate the tables that should be joined or linked
False
45 The DROP COLUMN clause of the ALTER TABLE command can only be used to delete one column at a time
False
Question 29 The asterisk symbol (*) can be used in a SELECT statement to indicate that all rows in the table should be displayed in the results.
False
27 Which of the following statements about a PRIMARY KEY is incorrect
It can be NULL, as long as the FOREIGN KEY contains a value.
Question 17: Which of the following would return a maximum of 7 rows, starting with the 4th row?
LIMIT 3, 7
10: Which of the following statements about the INSERT keyword is incorrect
MySQL does not allow column names to be omitted from the INSERT INTO clause.
8 The ____ of two tables is a table containing all rows that are in both tables.
intersect
10 Which of the following lets you run a command in the MySQL Monitor
mysql> \. script_name.sql mysql> source script_name.sql
22 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id The total number of rows returned by this query must equal
the number of rows in the Invoices table
33 A query that changes data is a ____ query
update
42 In a join, column names need to be qualified only
when the same column names exist in both tables
38 When the INSERT command is used to add rows to a table, the column names of the table must be specified
False
Question 4: The SQL statement to create a database with the name Airport is
CREATE DATABASE Airport;
24 The following SQL statement uses a(n) SELECT code, descript, price FROM product, vendor WHERE product.code = vendor.code
"old-style" join
Question 25 What symbol is used to select all columns from a table?
*
23 Which of the following constraints aren't supported in MySQL
A CHECK constraint
36 Structure of the AUTHOR table +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | author_id | varchar(4) | NO | PRI | NULL | | | last_name | varchar(10) | YES | | NULL | | | first_name | varchar(10) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ Which of the following commands will change the name of the last_name NAME column to LNAME in the AUTHOR table
ALTER TABLE author CHANGE last_name lname varchar(10) ;
37 Structure of the PUBLISHER table +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | pub_id | int(11) | NO | PRI | NULL | | | name | varchar(23) | YES | | NULL | | | contact | varchar(15) | YES | | NULL | | | phone | varchar(12) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ Which of the following commands will add a UNIQUE constraint to the phone column of the PUBLISHER table
ALTER TABLE publisher ADD CONSTRAINT phone_uk UNIQUE (phone);
19 Which of the following keywords cannot be used to modify an existing table
ALTER TABLE...AS
16 The ____ constraint prevents the user from adding a NULL value in the specified column.
NOT NULL
12 The ____________________ keywords can be added to the end of the command that creates a FOREIGN KEY constraint to indicate that when a row is deleted from the parent table that is referenced by entries in the child table, the rows in the child table should also be deleted.
ON DELETE CASCADE
9 To create the primary key clause for the Customer table on the CustomerNum field, the correct statement is ____
PRIMARY KEY (CustomerNum)
23 The syntax for a left outer join is _____.
SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition
25 Structure of the BOOKS table +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | isbn | varchar(10) | NO | PRI | NULL | | | pub_date | date | YES | | NULL | | | title | varchar(130) | YES | | NULL | | | Pub_id | int(11) | YES | MUL | NULL | | | list_price | decimal(5,2) | YES | | NULL | | | category_id | int(10) | YES | MUL | NULL | | +-------------+--------------+------+-----+---------+-------+ Structure of the PUBLISHER table +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | pub_id | int(11) | NO | PRI | NULL | | | name | varchar(23) | YES | | NULL | | | contact | varchar(15) | YES | | NULL | | | phone | varchar(12) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ Which of the following SQL statements will display the publisher name, book title, and price of all books that cost more than $35.95
SELECT p.name, b.title, b.list_price FROM books b JOIN publisher p ON (b.pub_id = p.pub_id) WHERE list_price > 35.95;
26 Structure of the BOOKS table: +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | isbn | varchar(10) | NO | PRI | NULL | | | pub_date | date | YES | | NULL | | | title | varchar(130) | YES | | NULL | | | Pub_id | int(11) | YES | MUL | NULL | | | list_price | decimal(5,2) | YES | | NULL | | | category_id | int(10) | YES | MUL | NULL | | +-------------+--------------+------+-----+---------+-------+ Structure of the PUBLISHER table: +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | pub_id | int(11) | NO | PRI | NULL | | | name | varchar(23) | YES | | NULL | | | contact | varchar(15) | YES | | NULL | | | phone | varchar(12) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ Which of the following SQL statements will display the title of each book stored in the BOOKS table, as well as the name of the contact person and the phone number to call to reorder the book
SELECT title, contact, phone FROM publisher JOIN books USING (pub_id);
39 If the WHERE clause of the DELETE command is omitted, then all rows in the referenced table will be removed.
True
43 An INNER JOIN can join a set of columns from one table with a set of columns from another table based on a column that shares the same value
True
44 A LEFT JOIN returns the INNER JOIN results plus everything in the left table of the join not found in the right table
True
35 Contents of the PUBLISHER table +--------+--------------------+----------------+--------------+ | pub_id | name | contact | phone | +--------+--------------------+----------------+--------------+ | 9000 | Addison Wesley | Tommie Seymour | 000-714-8321 | | 9024 | Pearson | Carlsen Benny | 800-987-0000 | | 9102 | O'Reilly | David Davidson | 800-555-1211 | | 9325 | McGraw Hill | Renee Smith | 800-555-9743 | | 9456 | Morgan Kaufmann | Jane Tomlin | 010-410-0010 | | 9561 | Houghton Mifflin | Edwards Donna | 800-444-9723 | | 9725 | Springer | Adams Samuel | 800-333-8344 | | 9745 | American Publishing| NULL | 800-555-8284 | | 9776 | Simon and Schuster | NULL | 888-666-8284 | | 9902 | W.W. Norton | Brahms Johnni | 888-410-0010 | +--------+--------------------+----------------+--------------+ Which of the following commands will delete only publisher 9024 from the PUBLISHER table
DELETE FROM publisher WHERE pub_id = 9024;
Question 39 Contents of the PUBLISHER table +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | pub_id | int(11) | NO | PRI | NULL | | | name | varchar(23) | YES | | NULL | | | contact | varchar(15) | YES | | NULL | | | phone | varchar(12) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ Based upon the contents of the PUBLISHER table, which of the following SQL statements will display only two columns in the results?
SELECT contact name FROM publisher;
Question 37: Contents of the PUBLISHER table +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | pub_id | int(11) | NO | PRI | NULL | | | name | varchar(23) | YES | | NULL | | | contact | varchar(15) | YES | | NULL | | | phone | varchar(12) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ Based upon the contents of the PUBLISHER table, which of the following is a valid SQL statement?
SELECT contact, name FROM publisher;
34 Contents of the PUBLISHER table +--------+--------------------+----------------+--------------+ | pub_id | name | contact | phone | +--------+--------------------+----------------+--------------+ | 9000 | Addison Wesley | Tommie Seymour | 000-714-8321 | | 9024 | Pearson | Carlsen Benny | 800-987-0000 | | 9102 | O'Reilly | David Davidson | 800-555-1211 | | 9325 | McGraw Hill | Renee Smith | 800-555-9743 | | 9456 | Morgan Kaufmann | Jane Tomlin | 010-410-0010 | | 9561 | Houghton Mifflin | Edwards Donna | 800-444-9723 | | 9725 | Springer | Adams Samuel | 800-333-8344 | | 9745 | American Publishing| NULL | 800-555-8284 | | 9776 | Simon and Schuster | NULL | 888-666-8284 | | 9902 | W.W. Norton | Brahms Johnni | 888-410-0010 | +--------+--------------------+----------------+--------------+ Based on the contents of the PUBLISHER table, which of the following SQL statements will change the phone number for Addison Wesley to 800-723-8321
UPDATE publisher SET phone = '800-723-8321' WHERE pub_id = 9000;
Question 1: A good definition for a database is
A collection of related data
14 To insert a new field in a table, the ____ keyword should be used with the ALTER TABLE command
ADD
17: Which of the following commands can be used to make structural changes to an existing table
ALTER TABLE
8 Which of the following commands can be used to make structural changes to an existing table
ALTER TABLE
7 Which of the following keywords cannot be used to modify an existing table
ALTER TABLE...AS
Question 12: The optional keyword ____________________ can be used to distinguish between a column name and a column alias.
AS
15 What does the following SQL statement do ALTER TABLE Customer Add (Type Varchar (2));
Alters the Customer table, and adds a field called "Type"
20 A composite primary key can be defined using the CONSTRAINT phrase in which SQL command
CREATE TABLE
25 Which of the following search conditions can be used to identify records that do not have data stored in a column named ColB
ColB IS NULL
24 Which of the following is a valid statement
Constraints are rules used to enforce business rules, practices, and policies. Constraints prevent errors by not allowing data to be added to tables if the data violates specific rules. Constraints ensure the accuracy and integrity of data.
13 Which command can be used to determine whether or not a column is allowed to contain a NULL value
DESCRIBE
Question 9: Which of the following keywords can be included in a SELECT statement to suppress duplicate data?
DISTINCT
Question 7: The SQL statement to drop the Airport database is:
DROP DATABASE Airport;
26 Which of the following SQL commands would be used to remove both the data and the table structure of a table named STUDENT
DROP TABLE STUDENT;
Question 5: ____ exists when different versions of the same data appear in different places.
Data inconsistency
9: What does the following SQL statement do DELETE FROM Customer WHERE state = 'HI';
Deletes all records from customer where the state is equal to HI
16: Which keywords identify a column that, if it contains a value, it must match data contained in another table
FOREIGN KEY
4 Which keywords identify a column that, if it contains a value, it must match data contained in another table
FOREIGN KEY
Question 21: Which clause specifies the tables from which to retrieve the data?
FROM
30 To view data in a table, use the VIEW command.
False
31 When using SQL to create a table, a column is defined by declaring, in this order: data type, column name, and optional constraints.
False
37 A constraint can only be created as part of the CREATE TABLE command
False
Question 32: The SELECT command can be used to display the structure of a database table. _________________________
False
Question 36: The following two SQL statements will produce different results. SELECT last_name, first_name FROM customer WHERE state = 'MA' OR state = 'NY' OR state = 'NJ' OR state = 'NH' OR state = 'CT'; SELECT last_name, first_name FROM customer WHERE state IN ('MA','NY','NJ','NH','CT');
False
5 The ____ data type is used to store integers.
INT
17 The following attribute can contain more than one value for each entity instance.
Multivalued attribute
Question 3: ___________________________ is a command-line tool that you can use for running SQL statements.
MySQL Command Line Client
Question 15: The order of precedence for the logical operators in a WHERE clause is
NOT, AND, OR
Question 22: Which of the following operators is used when the criteria is based upon a search pattern?
None of the above
11: Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate) Which of the following would find all employees whose name begins with the letter "S" using standard SQL
SELECT * FROM employee WHERE Name LIKE 'S%';
Question 41 Contents of the PUBLISHER table +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | pub_id | int(11) | NO | PRI | NULL | | | name | varchar(23) | YES | | NULL | | | contact | varchar(15) | YES | | NULL | | | phone | varchar(12) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ Based upon the contents of the PUBLISHER table, which of the following is a valid SQL statement?
SELECT * FROM publisher;
Question 18: Which of the following is an invalid SQL statement?
SELECT *, FROM employee;
Question 16: Which of the following is used to select partial table contents?
SELECT <column(s)> FROM <Table name> WHERE <Conditions>;
Question 43 Contents of the BOOKS table +------------+------------+---------------------------------------+--------+------------+-------------+ | isbn | pub_date | title | Pub_id | list_price | category_id | +------------+------------+---------------------------------------+--------+------------+-------------+ | 0060959290 | NULL | Among the Russians | 9000 | 11.20 | 99999 | | 0132149871 | 2001-02-03 | North Carolina Hiking Trails | 9561 | 14.95 | 99999 | | 0201385902 | 2010-03-25 | Intro to DB Systems-7th Ed | 9325 | 80.00 | 98888 | | 0201615711 | 2009-08-07 | Network Programming with Perl | 9776 | 39.95 | 98888 | | 0265615357 | 2009-03-25 | ADO and Oracle Workbook | 9000 | 59.99 | 98888 | | 0299282519 | 2005-09-02 | The tale of the body thief | 9325 | 6.99 | 95555 | | 0312135033 | 2011-05-12 | Great Catherine | 9561 | 13.56 | 99999 | | 0345298063 | 2004-06-09 | Peter the Great : His Life and World | 9000 | 11.20 | 99999 | | 0345438310 | 2008-03-10 | Nicholas and Alexandra | 9561 | 14.40 | 99999 | | 0399501487 | 2000-03-28 | Lord of the Flies: A Novel | 9561 | 6.95 | 95555 | +------------+------------+---------------------------------------+--------+------------+-------------+ Which of the following will display the ISBN column of the BOOKS table as the first column in its results?
SELECT ISBN, title FROM books;
Question 42: Contents of the BORROWER table +---------+-----------+------------+----------------------+--------------+-------+-------+ | card_no | last_name | first_name | address | city | state | zip | +---------+-----------+------------+----------------------+--------------+-------+-------+ | 1001 | Morales | Bonita | p.o. box 651 | Hayward | CA | 94541 | | 1002 | Thompson | Ryan | p.o. box 9835 | Santa Monica | CA | 90404 | | 1003 | Smith | Leila | P.O. BOX 66 | Berekely | CA | 94701 | | 1004 | Pierson | Thomas | 69821 South Avenue | Union City | CA | 94512 | | 1005 | Girard | Cindy | P.O. BOX 851 | Hayward | CA | 94645 | | 1006 | Cruz | Meshia | 82 Dirt Road | Albany | CA | 94234 | | 1007 | Giana | Tammy | 9153 Main Street | Berekely | CA | 94703 | +---------+-----------+------------+----------------------+--------------+-------+-------+ Based upon the contents of the BORROWER table, which of the following SQL statements will display the card number for each borrower, along with the city and state in which the borrower lives.
SELECT card_no, city, state FROM borrower;
Question 44 Contents of the BORROWER table +---------+-----------+------------+----------------------+--------------+-------+-------+ | card_no | last_name | first_name | address | city | state | zip | +---------+-----------+------------+----------------------+--------------+-------+-------+ | 1001 | Morales | Bonita | p.o. box 651 | Hayward | CA | 94541 | | 1002 | Thompson | Ryan | p.o. box 9835 | Santa Monica | CA | 90404 | | 1003 | Smith | Leila | P.O. BOX 66 | Berekely | CA | 94701 | | 1004 | Pierson | Thomas | 69821 South Avenue | Union City | CA | 94512 | | 1005 | Girard | Cindy | P.O. BOX 851 | Hayward | CA | 94645 | | 1006 | Cruz | Meshia | 82 Dirt Road | Albany | CA | 94234 | | 1007 | Giana | Tammy | 9153 Main Street | Berekely | CA | 94703 | +---------+-----------+------------+----------------------+--------------+-------+-------+ Based upon the contents of the BORROWER table, which of the following SQL statements will display the City column including duplicates.
SELECT city FROM borrower;
Question 40 Contents of the AUTHOR table +-----------+------------+------------+ | author_id | last_name | first_name | +-----------+------------+------------+ | A100 | Austin | James | | A105 | Adams | Juan | | A210 | Steven | Rice | | B100 | Jackoby | Jack | | B610 | Tim | Carlos | | C510 | Carolly | Nikos | | D311 | David | Cwalina | | D610 | DeLoura | Miller | +-----------+------------+------------+ Based on the contents of the AUTHOR table, which of the following SQL statements will display all authors whose last name contains the letter pattern "Da." Put the results in order of the last name, then the first name. display each author's last name and first name.
SELECT last_name, first_name FROM author WHERE last_name LIKE '%Da%' ORDER BY last_name, first_name;
Question 38 Contents of the PUBLISHER table: +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | pub_id | int(11) | NO | PRI | NULL | | | name | varchar(23) | YES | | NULL | | | contact | varchar(15) | YES | | NULL | | | phone | varchar(12) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ Based upon the contents of the PUBLISHER table, which of the following SELECT statements will display the publisher's name first in the results?
SELECT name FROM publisher;
Question 27 When you code a SELECT statement, you must code the four main clauses in the following order
SELECT, FROM, WHERE, ORDER BY
Question 19: In MySQL, you use the _____ command to produce a list of tables
SHOW TABLES
28 Which of the following terms is synonymous with "relation"
Table
6 A ____________ is the main structure in a database and represents a single specific subject.
Table A relation or table is the main structure of a database and represents a single specific subject. A field is the smallest structure of a database and represents a characteristic of the table to which it belongs. A record is a unique instance of the subject of a table. A key is a special field that plays a specific role in a table. A view is a virtual table comprised of fields from one or more tables.
15 Which of the following is a valid name for a table
Table_1
Question 13: What is the result set returned from the following query? SELECT Customer_Name, telephone FROM customer WHERE city IN ('Boston','New York','Denver');
The Customer_Name and telephone of all customers living in either Boston, New York or Denver.
19 The UNIQUE constraint differs from the PRIMARY KEY constraint in what way
The UNIQUE constraint allows NULL values.
Question 23: Which of the following statements is correct?
The columns will be listed in the results in the same order they are listed in the SELECT clause of the SELECT statement.
6: When you code a union that combines two result sets, which of the following is not true
The result sets must be derived from different tables.
Question 2: Redundancy refers to what database condition?
The same data is recorded in more than one place in the same database
35 You can create a new table using data in an existing table.
True
38 A PRIMARY KEY constraint will make certain the column designated as the primary key does not contain a NULL value.
True
Question 34: If multiple column names are listed in the SELECT clause of a SELECT statement, the columns must be separated by a comma (,).
True
Question 35: The following two SQL statements will produce the same results. SELECT last_name, first_name FROM customer WHERE credit_limit > 99 and credit_limit < 10001; SELECT last_name, first_name FROM customer WHERE credit_limit between 100 and 10000;
True
Question 28 Which of the following WHERE clauses will return vendor names from A to C?
WHERE vendor_name < 'D'
18 The NOT NULL constraint can only be created at the ____ level.
column
5: Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union
combines the result sets of two or more SELECT statements
21 When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an invoice_total value of $1000 or less
invoice_total IN (0, 1000)
Question 20: When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an invoice_total value of $1000 or less?
invoice_total IN (0, 1000)
22 When coded in a WHERE clause, which search condition will return invoices when payment_date isn't null and invoice_total is greater than or equal to $500
payment_date IS NOT NULL AND invoice_total >= 500
Question 26 When coded in a WHERE clause, which search condition will return invoices when payment_date isn't null and invoice_total is greater than or equal to $500?
payment_date IS NOT NULL AND invoice_total >= 500
3 In a cross join, all of the rows in the first table are joined with all of the
rows from the second table
29 Which uses the least amount of storage
they all use the same amount of storage
Question 14: Which of the following isn't a valid column alias?
total sales
1 A full outer join returns
unmatched rows from both the left and right tables
2 If you assign an alias to one table in a join, you have to
use that alias to refer to the table throughout your query