SQL NO SQL quiz

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

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

ALTER TABLE author CHANGE last_name lname varchar(10) ;

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

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;

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;

SELECT contact name FROM publisher;

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.

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%';

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;

SELECT card_no, city, state FROM borrower;

SELECT city FROM borrower;

SELECT last_name, first_name FROM author WHERE last_name LIKE '%Da%' ORDER BY last_name, first_name;

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

SQL NO SQL quiz

Ensembles d'études connexes

APCOGO 3

A&P 2 - Lesson 8

history test #4

ch 7

CH. 4 Probability

Computing Essentials 2017 Chapters 1-13

ICU Exam 2 (class questions)

Evaluating the Six Trigonometric Functions Quiz

19

Ch. 1-3 Review Exam 1

ch 30

Personal and Community Health

FIN

Chapter 10

Entrepreneurship Exam #1 Study Guide

Chapter 3: Relational Database Model

Economics Final

TCP/IP Protocols

Postpartum

Dental Management Ch. 14: Diabetes Mellitus