MySQL Quiz

2. What can you do with MySQL?

1. Retrieve records 2. Join (combine) tables 3. Insert records 4. Delete records 5. Update records 6. Add & delete tables

22. What other functions can we use in SQL?

1. SELECT MAX() 2. SELECT MIN() 3. SELECT AVG() 4. SELECT SUM()

29. What is a subselect statement?

A SELECT statement nested inside another SELECT statement.

30. What is a temporary table?

A table that stores the distinct states (outputs) queried.

31. What is a SQL Alias?

A temporary name given to a table or a column

11. What is a wildcard? What does it mean?

A wildcard returns every column. It is the * sign. Syntax: SELECT * FROM [username]orderdb.Customer;

10. How do you retrieve information from multiple columns?

Add a comma SELECT FirstName, State FROM [username]orderdb.Customer;

4. What is an SQL statement?

Any command that interacts with a database

What is the essential statement required to make a table in MySQL?

CREATE Ex: CREATE TABLE schema_name.table_name (columnName1 datatype [NULL][NOT NULL],...;

27. What should you check before naming tables in your database?

Check the most up-to-date list of reserved words in MySQL, and name your tables so you don't have to worry about including ` `.

24. Does it matter which field in the table you use in the SELECT COUNT query?

Depends. If all values are full, doesn't matter. If table has empty values, it does matter.

25. How do you join tables using WHERE in MySQL?

Ex: SELECT * FROM orderdb.Customer, orderdb.`Order` WHERE Customer.CustomerID='Order'.CustomerID; Data in Customer.CustomerID is exactly the same as Order.CustomerID; the only difference between them is their location. Customer.CustomerID is located in the Customer table, and Order.CustomerID is located in the Order table.

19. What is the purpose of GROUP BY?

It organizes the results by column values

21. What is the purpose of ORDER BY?

It sorts query results from lowest to highest based on a field

12. Is SQL syntax case-sensitive?

No, but there is a proper way to type syntax. You should use the proper syntax. Best practices: Keywords = Uppercase Ex: SELECT and FROM Tables/Columns = Refer to case of individual characters Ex: FirstName Use space appropriately for readability

6. Is there a standard syntax for MySQL?

No.

5. Is SQL a programming language?

No. But it's used by programming languages to communicate with databases

32. Does MySQL remember alias names?

No. Once query runs, MySQL forgets alias names.

16. Do we enclose numeric values in quotes?

Nope!

14. What if we want to return information that is not connected to a particular value?

SELECT * FROM orderdb.Customer WHERE State <> 'NJ';

17. What are examples of AND & OR operator statements?

SELECT * FROM orderdb.Product WHERE Price > 2 AND Price <= 3.5; SELECT * FROM orderdb.Customer WHERE City = 'Princeton' OR City = 'Pittsgrove';

23. What is an example of a WHERE COUNT combination? What does the statement ask?

SELECT COUNT (FirstName) FROM orderdb.Customer WHERE State= 'NJ'; Statement asks "How many customers live in New Jersey?"

20. How can you make the counting function more specific?

SELECT State, COUNT(FirstName) FROM orderdb.Customer GROUP BY State;

What is the syntax of a simple SELECT statement with only one column?

SELECT column_names(s) FROM schema_name.table_name; Example: SELECT FirstName FROM [username]orderdb.Customer;

7. What is syntax?

The grammar for a programming language.

3. How is a query used?

To retrieve information.

15. How do the =, >, <, >=, <=, and <> statements interact with the database?

Useful when retrieving numerical data. Ex: SELECT * FROM orderdb.Product WHERE Price > 2;

28. Can you perform arithmetic operations using MySQL?

Yes, as long as the query refers to numeric data.

18. Does SQL include functions?

Yes. Ex: COUNT(FirstName) FROM orderdb.Customer;

13. Can you return unique combinations?

Yes. Use comma. Ex: SELECT DISTINCT City, State FROM [username]orderdb.Customer; Returns unique combinations like Princeton, NJ; Warminster, PA

34. When creating a field for zipcode, why would you define it as a VARCHAR() datatype instead of an INT()?

Zipcode can't be added, subtracted, etc. with other integers. It is a descriptive number.

26. What are ` `, when are they used, and where are they located on your keyboard?

` ` are "back quotes;" they are used to distinguish a table name from a reserved function in MySQL. Back quotes are located on the tilde key on the top left-hand side of the keyboard.

9. What is query result called?

a "View"

1. How do we retrieve information from a database?

with MySQL