SQL exercises

Write the correct SQL statement to create a new table called Persons. Include columns PersonID, LastName, FirstName, Address, and City. PersonID is an integer column whereas the other columns will be characters with a length of 255.

CREATE TABLE Persons( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );

Write the correct SQL statement to create a new database called testDB.

CREATE DATABASE testDB;

Select all records from Customers where the value of the City column contains the letter "a".

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

Select all records from the table Customers where the first letter of the City starts with anything from an "a" to an "f". What about selecting all records where the first letter of the City is NOT an "a" or a "c" or an "f".

SELECT * FROM Customers WHERE City LIKE '[a-f]%'; SELECT * FROM Customers WHERE City LIKE '[!acf]%';

Select all records from Customers where the value of the City column starts with the letter "a".

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

Select all records from the table Customers where the first letter of the City is an "a" or a "c" or an "s".

SELECT * FROM Customers WHERE City Like '[acs]%';

Use the IN operator to select all the records in Customers where Country is either "Norway" or "France". Use the IN operator to select all the records where Country is NOT "Norway" and NOT "France".

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

When displaying the Customers table, refer to the table as Consumers instead of Customers.

SELECT * FROM Customers AS Consumers;

Add a column of type DATE called Birthday to the table Persons Now delete the column Birthday from the Persons table.

ALTER TABLE Persons ADD Birthday DATE; ALTER TABLE Persons DROP COLUMN Birthday;

Delete all the records from the Customers table where the Country value is 'Norway'.

DELETE FROM Customers WHERE Country = 'Norway';

Delete all the records from the Customers table.

DELETE FROM Customers;

Write the correct SQL statement to delete a database named testDB.

DROP DATABASE testDB;

Write the correct SQL statement to delete a table called Persons. What if I want to delete all the records in Persons, but keep the table?

DROP TABLE Persons; TRUNCATE TABLE Persons;

Insert a new record in the Customers table. Specifically I want to insert these values 'Hekkan Burger', 'Gateveien 15', 'Sandnes', '4306', 'Norway', into columns CustomerName, Address, City, PostalCode, Country respectively.

INSERT INTO Customers( CustomerName, Address, City, PostalCode, Country) VALUES( 'Hekkan Burger', 'Gateveien 15', 'Sandnes', '4306', 'Norway');

Use the BETWEEN operator to select all the records from Products where the value of the Price column is between 10 and 20. What about not between 10 and 20?

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

Use the BETWEEN operator to select all the records from Products where the value of the ProductName column is alphabetically between 'Geitost' and 'Pavlova'.

SELECT * FROM Products WHERE ProductName BETWEEN 'Geitost' AND 'Pavlova';

Select all records from the customers table, sort the result alphabetically by the column City. What about reversed alphabetically?

SELECT * FROM customers ORDER BY City; SELECT * FROM customers ORDER BY City DESC;

Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City.

SELECT * FROM customers ORDER BY country, city;

Select all records from the table customers where the City column has the value 'Berlin' or 'London'.

SELECT * FROM customers WHERE City = 'Berlin' OR City = 'London';

Select all records from the Customers where the PostalCode column is empty. What about selecting all records from the Customers where the PostalCode column is NOT empty?

SELECT * FROM customers WHERE postalcode IS NULL; SELECT * FROM customers WHERE postalcode IS NOT NULL;

Select all records from the table Customers where the second letter of the City is an "a".

SELECT * FROM Customers WHERE City LIKE '_a%';

Select all records from Customers where the value of the City column does NOT start with the letter "a".

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

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables. SELECT * FROM Orders ????????? ON Orders.CustomerID=Customers.CustomerID;

SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Insert the missing parts in the JOIN clause to join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables. SELECT * FROM Orders LEFT JOIN Customers ???? = ????

SELECT * FROM Orders LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table. SELECT * FROM Orders ????????? ON Orders.CustomerID=Customers.CustomerID;

SELECT * FROM Orders RIGHT JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Use the correct function to return the number of records that have the Price value set to 18 from the table Products.

SELECT COUNT(*) FROM Products WHERE Price = 18;

List the number of customers in each country, ordered by the country with the most customers first.

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

List the number of customers in each country by counting CustomerID.

SELECT COUNT(CustomerID) FROM Customer GROUP BY Country;

Use the NOT keyword to select all records where City is NOT "Berlin" from the table customers

SELECT City FROM customers WHERE NOT City = 'Berlin';

Use the MIN function to select the record with the smallest value of the Price column from the table Products

SELECT MIN(Price) FROM Products;

Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".

UPDATE customers SET City = 'Oslo' WHERE Country = 'Norway';

Update the City value and the Country value to 'Oslo' and 'Norway' respectively, and where customerID is 32

UPDATE customers SET City = 'Oslo', Country = 'Norway' WHERE customerID = 32;

Update the City column of all records in the Customers table, so city = 'Oslo'

UPDATE customers SET City = 'Oslo';