Databases Midterm - SQL and ER Practice Problems

To change the data type of a column in a table:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Select all customers that are located in "Germany", "France" and "UK":

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

Select all customers that are from the same countries as the suppliers:

SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);

Select all customers that are NOT located in "Germany", "France" or "UK":

SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');

Find the average price of all products:

SELECT AVG(Price) FROM Products;

The following SQL statement creates a backup copy of Customers:

SELECT * INTO CustomersBackup2017 FROM Customers;

Select all orders with customer information: (join)

SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Select all orders with customer and shipper information: (join)

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

Delete the customer "Alfreds Futterkiste" from the "Customers" table:

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

The following SQL statement drops the existing database "testDB":

DROP DATABASE testDB;

The following SQL statement drops the existing table "Shippers":

DROP TABLE Shippers;

Select all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;

Select all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

SELECT * FROM Customers ORDER BY Country DESC;

Select all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column:

SELECT * FROM Customers ORDER BY Country, CustomerName;

Select all customers from the "Customers" table, sorted by the "Country" column:

SELECT * FROM Customers ORDER BY Country;

Select all fields from "Customers" where city is "Berlin" OR "München":

SELECT * FROM Customers WHERE City='Berlin' OR City='München';

Select all customers with a ContactName that starts with "a" and ends with "o":

SELECT * FROM Customers WHERE ContactName LIKE 'a%o';

Select the first three records from the "Customers" table, where the country is "Germany":

SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;

Select all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München":

SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');

Select all fields from "Customers" where country is "Germany" AND city is "Berlin":

SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';

Select all the customers from the country "Mexico", in the "Customers" table:

SELECT * FROM Customers WHERE Country='Mexico';

Select all customers with a CustomerName ending with "a":

SELECT * FROM Customers WHERE CustomerName LIKE '%a';

Select all customers with a CustomerName that have "or" in any position:

SELECT * FROM Customers WHERE CustomerName LIKE '%or%';

Select all customers with a CustomerName that have "r" in the second position:

SELECT * FROM Customers WHERE CustomerName LIKE '_r%';

Select all customers with a CustomerName starting with "a":

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

Select all customers with a CustomerName that starts with "a" and are at least 3 characters in length:

SELECT * FROM Customers WHERE CustomerName LIKE 'a_%_%';

Select all customers with a CustomerName that does NOT start with "a":

SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';

Select all fields from "Customers" where country is NOT "Germany" and NOT "USA":

SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';

Select all fields from "Customers" where country is NOT "Germany":

SELECT * FROM Customers WHERE NOT Country='Germany';

Select all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:

SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

Select all products with a price BETWEEN 10 and 20:

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

Select all products with a price outside the range of 10 and 20:

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

Select all products with a ProductName BETWEEN 'Carnarvon Tigers' and 'Mozzarella di Giovanni':

SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;

The following SQL statement uses the IN clause to copy the table into a new table in another database:

SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers;

The following SQL statement copies only the German customers into a new table:

SELECT * INTO CustomersGermany FROM Customers WHERE Country = 'Germany';

Find the price of the cheapest product:

SELECT MIN(Price) AS SmallestPrice FROM Products;

The following SQL deletes the "Email" column from the "Customers" table:

ALTER TABLE Customers DROP COLUMN Email;

The following SQL statement copies only the German suppliers into "Customers":

INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';

The following SQL statement copies "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):

INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers;

Insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');

Insert a new record in the "Customers" table:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

List the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;

List the number of customers in each country. Only include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

List the number of customers in each country, sorted high to low:

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;

List the number of customers in each country:

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

Find the number of products:

SELECT COUNT(ProductID) FROM Products;

Return the cities (only distinct values) from both the "Customers" and the "Suppliers" table:

SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

Return the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;

Create two aliases, one for the CustomerID column and one for the CustomerName column:

SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;

Create an alias named "Address" that combine four columns (Address, PostalCode, City and Country)

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers;

The following SQL statement copies only a few columns into a new table:

SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers;

Select all customers, and all orders: (join)

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

Select all customers, and any orders they might have: (join)

SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; *The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

The following SQL statement copies data from more than one table into a new table:

SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2017 FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Use the IS NOT NULL operator to list all persons that do have an address:

SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NOT NULL;

Use the IS NULL operator to list all persons that have no address:

SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NULL;

Return all employees, and any orders they might have placed: (join)

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;

The following SQL statement returns TRUE and lists the productnames if ALL the records in the OrderDetails table has quantity = 10:

SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

The following SQL statement returns TRUE and lists the productnames if it finds ANY records in the OrderDetails table that quantity = 10:

SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

Find the sum of the "Quantity" fields in the "OrderDetails" table:

SELECT SUM(Quantity) FROM OrderDetails;

The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:

SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);

Delete the data inside a table, but not the table itself:

TRUNCATE TABLE table_name;

Update the first customer (CustomerID = 1) with a new contact person and a new city.

UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;

Update the contactname to "Juan" for all records where country is "Mexico":

UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico';