Preview

07 - SQL Part 2

1. SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce after learning about the relational model from Ted Codd

FALSE

TRUE

2. The INSERT INTO statement is used to __________________ in a table

delete old records

insert ID numbers (primary keys) into a table

insert new records

insert duplicate records

3. Which of the following 'answers' is the correct way to insert a new record into the 'Customers' table

The following SQL statement inserts a new record in the "Customers" table:

Answer 1
========

VALUES Customers (CustomerName, City, Job)
INSERT INTO ('Jonathan Marvin', 'London', 'Lawyer');

Answer 2
========

INSERT INTO Customers (CustomerName, City, Job)
VALUES ('Jonathan Marvin', 'London', 'Lawyer');


Answer 3
========

SELECT Customers (CustomerName, City, Job)
SELECT ('Jonathan Marvin', 'London', 'Lawyer');
INSERT * into Customers

Answer 3

Answer 2

None of these answers are valid

Answer 1

4. A field with a _____ value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a ____ value

EXCEPTION

NULL

ALL(*)

STRING

5. A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

TRUE

FALSE

6. The _________ operator is used to test for empty values

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address _________;

IS EMPTY

IS NULL

EMPTY = 0

WHILE NULL

7. The IS NOT NULL operator is also used to test for empty values (NOT NULL values). The following SQL lists all customers with NO value in the "Address" field

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

FALSE

TRUE

8. The UPDATE statement is used to modify the existing records in a table. Fill in the three blanks to update the first customer with a new contact person and city

________ Customers
_____ ContactName = Hannah Pigachee', City= 'London'
_______CustomerID = 1;

WHERE / SET / UPDATE

UPDATE / WHERE / SET

SET / UPDATE / WHERE

UPDATE / SET / WHERE

9. While updating records. If you omit the WHERE clause, ALL records will be updated. (it is a reminder to be careful!)

FALSE

TRUE

10. Which of the following 'answers' is the correct way to delete a customer from the 'Customers' table.

Which of the following SQL statements deletes the customer 
"Hannah Asha" from the "Customers" table:

Answer 1
=========
WHERE CustomerName='Hannah Asha'DO DELETE FROM Customers 


Answer 2
=========
DELETE 'Hannah Asha' FROM Customers IF CustomerName='Hannah Asha';


Answer 3
=========
DELETE FROM Customers WHERE CustomerName='Hannah Asha';

Answer 1

None of these answers are valid

Answer3

Answer 2

11. It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact. Which answer is correct?

The following SQL statement deletes all rows in the "Customers" table, 
without deleting the table:

Answer 1
=========
DELETE FROM Customers;


Answer 2
=========
Customers DELETE *


Answer 3
=========
DROP Customers

Answer 3

None of these answers are valid

Answer 1

Answer 2

12. The following SQL statement selects the first three records from the "Customers" table. Select TOP is useful when you want specific returns in a large database.

SELECT TOP 3 * FROM Customers; SELECT TOP 50 PERCENT * FROM Customers;

TRUE

FALSE

13. What does the MIN() function do?

SELECT MIN(column_name)
FROM table_name
WHERE condition;

returns the largest value in the smallest column

returns the smallest value of the selected column

returns the first row of the first column

returns the minimal value (i.e the first or the last) of the given row

14. The_______ function returns the number of rows that matches a specified criteria.

SUM()

COUNT()

AVG()

LIKE()

15. The ______ function returns the average value of a numeric column.

AVG()

LIKE()

COUNT()

SUM()

16. What will the following SQL statement do?

SELECT COUNT(ProductID)
FROM Products;

count the number of rows + columns in the PRODUCT table

count the number of fields contained in the PRODUCT table

find the number of tables in the PRODUCT file

find the number of products in the PRODUCT table

17. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator:

The percent sign and the underscore (% and _)

The letter 'L' and the '@' sign

The plus sign and the underscore (+ and _)

The percent sign and the dolla sign (% and $)

18. Match the following example to its description

WHERE CustomerName LIKE 'a%'

Finds any values that start with "a" and are at least 3 characters in length

Finds any values that start with "a" and ends with "o"

Finds any values that have "or" in any position

Finds any values that start with "a"

19. Match the following example to its description

WHERE CustomerName LIKE '%or%'

Finds any values that start with "a" and are at least 3 characters in length

Finds any values that start with "a"

Finds any values that start with "a" and ends with "o"

Finds any values that have "or" in any position

20. Match the following example to its description

WHERE CustomerName LIKE 'a_%_%'

Finds any values that start with "a" and ends with "o"

Finds any values that start with "a" and are at least 3 characters in length

Finds any values that start with "a"

Finds any values that have "or" in any position

21. Match the following example to its description

WHERE ContactName LIKE 'a%o'

Finds any values that start with "a"

Finds any values that start with "a" and are at least 3 characters in length

Finds any values that have "or" in any position

Finds any values that start with "a" and ends with "o"

22. Match the following example to its description

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

Selects all customers with a CustomerName starting with "a"

Selects all columns that begin with an 'a'

None of these answers are valid

Selects all fields starting with 'a'

23. Fill in the blanks. The following SQL statement selects all customers with a CustomerName that does NOT start with "a"

SELECT * FROM Customers
WHERE CustomerName ________ 'a%';

NOT SELECT

NOT NOT

NOT LIKE

NOT

24. A wildcard character is used to substitute any other character(s) in a string. The following SQL statement _________________________

SELECT * FROM Customers
WHERE City LIKE 'ber%';

selects all customers with a City starting with "ber"

selects all customers with a column name beginning with "ber"

selects all cities that begin with "ber"

selects all customers and cities that begin and end with "ber"

25. A ____ clause is used to combine rows from two or more tables, based on a related column between them.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER _______Customers ON Orders.CustomerID=Customers.CustomerID;

JOIN

FIND

COIN

MIX

Report an error