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

  TRUE

  FALSE

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

  insert ID numbers (primary keys) into a table

  insert new records

  insert duplicate records

  delete old 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

  None of these answers are valid

  Answer 3

  Answer 1

  Answer 2

 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

  STRING

  NULL

  ALL(*)

  EXCEPTION

 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 NULL

  WHILE NULL

  EMPTY = 0

  IS EMPTY

 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;

  TRUE

  FALSE

 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

  SET / UPDATE / WHERE

  UPDATE / SET / WHERE

  UPDATE / WHERE / SET

 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

  Answer 2

  Answer3

 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 1

  None of these answers are valid

  Answer 3

  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;

  FALSE

  TRUE

 13. What does the MIN() function do?
SELECT MIN(column_name)
FROM table_name
WHERE condition;

  returns the smallest value of the selected column

  returns the first row of the first column

  returns the largest value in the smallest 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.

  LIKE()

  COUNT()

  AVG()

  SUM()

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

  AVG()

  LIKE()

  SUM()

  COUNT()

 16. What will the following SQL statement do?
SELECT COUNT(ProductID)
FROM Products;

  find the number of tables in the PRODUCT file

  count the number of rows + columns in the PRODUCT table

  find the number of products in the PRODUCT table

  count the number of fields contained 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 plus sign and the underscore (+ and _)

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

  The letter 'L' and the '@' sign

  The percent sign and the underscore (% and _)

 18. 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 have "or" in any position

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

  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 ends with "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

 20. Match the following example to its description
WHERE CustomerName LIKE 'a_%_%'

  Finds any values that start with "a"

  Finds any values that have "or" in any position

  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"

 21. Match the following example to its description
WHERE ContactName LIKE 'a%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

  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 fields starting with 'a'

  None of these answers are valid

  Selects all columns that begin with an '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 NOT

  NOT LIKE

  NOT

  NOT SELECT

 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 and cities that begin and end with "ber"

  selects all cities that begin with "ber"

  selects all customers with a column name beginning 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;

  FIND

  COIN

  MIX

  JOIN