SQL Queries and Answers: Mastering Database Operations
Common SQL Questions and Solutions
With SQL, what is the keyword to represent a missing value for a field?
Answer: IS NULL
With SQL, how do you select all the records with all the attributes from a table named “Person” where the value of the attribute “FirstName” is “Peter”?
Answer: SELECT * FROM Person WHERE FirstName = "Peter"
If an aggregate function needs to be used in the selection criteria, it will be placed in the WHERE clause.
Answer: False
Set the value of the City columns to ‘Hartford’, but only the ones where the Country column has the value “USA”.
Answer:
UPDATE Customer
SET City = 'Hartford'
WHERE Country = 'USA';
Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.
Answer: SELECT * FROM Customers LEFT JOIN Orders ON Orders.CustomerID=Customers.CustomerID;
Which of the following statements lists the number of customers in each country?
Answer:
SELECT Country, COUNT(CustomerID)
FROM Customer
GROUP BY Country;
Which of the following statements deletes all the records from the Customers table where the Country value is ‘USA’?
Answer:
DELETE FROM Customers
WHERE Country="USA";
When running a query without an intended open-ended question and an “enter parameter” dialog box still pops out, What should you do?
Answer: Check the criteria in the WHERE clause
When you see an error message that a specified field courseID could refer to more than one table listed in the FROM clause, what should you correct?
Answer: Prefix the attribute courseID with a table name
If you see an error message saying a specified attribute was not as part of an aggregate function, you should add the attribute in the GROUP BY clause.
Answer: True
If a subquery may return one or more records, what is the best strategy to use for the criteria in the main query?
Answer: Use IN
The following SQL statement selects all products with a price BETWEEN 10 and 20:
Answer: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
Which of the following SQL statements selects all customers with a City starting with “a”, “b”, or “c”?
Answer: SELECT * FROM Customers WHERE City LIKE '[a-c]*';
SELECT * from Customer WHERE username="nobody" AND password="idk" OR 1=1 will still return all Customers’ records even though the username and password don’t match.
Answer: True
Which of the following displays the bottom 3 customers with the account balance?
Answer: SELECT TOP 3 * from Customer ORDER BY Balance;
Use the correct function to return the number of records that have the Price value set to 18.
Answer: SELECT COUNT(*) FROM Products WHERE Price = 18;
Which of the following SQL statements selects all fields from “Customers” where the country is NOT “Germany” and NOT “USA”?
Answer: SELECT * FROM Customer WHERE Country NOT IN ("Germany", "USA");
Which SQL keyword is used to sort the result set?
Answer: ORDER BY
The following SQL statement selects all products with a price BETWEEN 10 and 20:
Answer: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
What is the correct SQL statement to retrieve customers living in CT state with an income level of 150000 or higher?
Answer: SELECT FirstName, LastName FROM Customer WHERE Income>=150000 AND state="CT";