DB SQL Quiz

What returns all rows from the right table, and the matched rows from the left table?

RIGHT JOIN

What is the SQL statement to select all customers with a City of "Paris" or "London"?

SELECT * FROM Customers WHERE City IN ('Paris', 'London');

With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?

SELECT * FROM Persons ORDER BY FirstName DESC;

With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Joe" and the "LastName" is "Blow"?

SELECT * FROM Persons WHERE FirstName = 'Joe' AND LastName = 'Blow';

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Jones" and "Smith"?

SELECT * FROM Persons WHERE LastName BETWEEN 'Jones' AND 'Smith';

With SQL, how do you select all the columns from a table named "Persons"?

SELECT * FROM Persons;

With SQL, how do you select all the records from a table named "Persons"?

SELECT * FROM Persons;

Which SQL statement selects all rows from a table called Products and orders the result set by ProductID column?

SELECT * FROM Products ORDER BY ProductID (ASC | DESC);

What is the SQL statement that states which customers live in a city starting with the letter "s"?

SELECT * FROM table WHERE City LIKE 'S%';

What is the SQL statement that states which customers live in a city ending with "se"?

SELECT * FROM table WHERE City Like '%se';

What is the SQL syntax to copy all columns of a table into a backup table?

SELECT * INTO newtable [IN externaldb] FROM Table1;

If you want to create aliases for the tables Customers and Orders in a SELECT query WHERE Customers.CustomerID=Orders.CustomerID, how would you write the query?

SELECT O.OrderID, O.OrderDate, C.CustomerName FROM Customers AS C, Orders AS O WHERE C.CustomerName="Around the Horn" AND C.CustomerID=O.CustomerID

What is the SQL statement to select the product name and price for today from the "Products" table?

SELECT ProductName, Price, NOW() AS PerDate FROM Products;

What is the SQL statement to select the product name and the price rounded to the nearest integer from the "Products" table?

SELECT ProductName, ROUND (Price, 0) AS RoundedPrice FROM Products;

What type of SQL field creates a value of the primary key field automatically every time a new record is inserted?

Sequence

When does the OR operator display a record?

When at least one condition is true

In a table, a column may contain many duplicate values and you want to list only the different values, what is the SQL syntax?

DISTINCT

Which SQL statement is used to return only different values?

DISTINCT

What function returns the number of records in a table?

COUNT

What values can be used for the range specified with the BETWEEN operator?

Number, Text, and Date

Which SQL keyword is used to sort the result-set?

ORDER BY

The GROUP BY statement is used in conjunction with what type of functions to group the result-set by one or more columns?

Aggregate Function

What returns all rows when there is a match in one of the tables?

FULL JOIN

What type of JOIN returns all rows from Customers and from Orders: FROM Customers ____ Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName?

FULL OUTER JOIN

What returns all rows when there is at least one match in BOTH tables?

INNER JOIN

What type of JOIN selects all rows from two tables as long as there is a match between the columns in both tables?

INNER JOIN

With SQL, how can you insert a new record into the "Persons" table?

INSERT INTO Persons (......,) VALUES (...., )

With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?

INSERT INTO Persons(LastName) VALUES ('Olsen');

What returns all rows from the left table, and the matched rows from the right table?

LEFT JOIN

What type of JOIN returns all rows from table1, with the matching rows in table2: FROM table1 ____ table2 ON table1.column_name=table2.column_name?

LEFT JOIN

What operator is used to search for a specified pattern in a column?

LIKE

Which SQL keyword is used to retrieve the greatest/highest value?

MAX ()

What constraint ensures that a field always contains a value?

NOT NULL

What SQL operator combines the result of two or more SELECT statements?

UNION

Which SQL statement is used to update data in a database?

UPDATE

How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

UPDATE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen';

Can you use both HAVING and WHERE SQL clauses in a single SQL statement?

Yes