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 duplicate records

  insert new records

  insert ID numbers (primary keys) into a table

 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 1

  None of these answers are valid

  Answer 3

  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

  NULL

  EXCEPTION

  STRING

  ALL(*)

 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.

  FALSE

  TRUE

 6. The _________ operator is used to test for empty values
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address _________;

  WHILE NULL

  IS EMPTY

  EMPTY = 0

  IS 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

  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!)

  TRUE

  FALSE

 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 2

  None of these answers are valid

  Answer 1

  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 2

  Answer 1

  None of these answers are valid

  Answer 3

 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.

  AVG()

  COUNT()

  LIKE()

  SUM()

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

  LIKE()

  SUM()

  AVG()

  COUNT()

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

  find the number of products in the PRODUCT table

  count the number of fields contained in the PRODUCT table

  count the number of rows + columns in the PRODUCT table

  find the number of tables in the PRODUCT file

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

  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

 19. Match the following example to its description
WHERE CustomerName LIKE '%or%'

  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"

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

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

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

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

  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 columns that begin with an 'a'

  Selects all customers with a CustomerName starting with "a"

  Selects all fields starting with 'a'

  None of these answers are valid

 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

  NOT LIKE

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

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

  selects all customers with a City starting with "ber"

  selects all cities that begin 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;

  COIN

  FIND

  MIX

  JOIN