QUERY practice

Question 1

Insert the missing statement to get all the columns from the Customers table.

________ * FROM Customers;

Answer 1

SELECT * FROM Customers

Question 2

Write a statement that will select the City column from the Customers table.

______ _____ _____ customers;

Answer 2

SELECT City FROM Customers;

Question 3

Select all the different values from the Country column in the Customers table.

____ _____ country FROM customers

Answer 3

SELECT DISTINCT country FROM customers

Question 4

Select all records where the City column has the value “Berlin”.

SELECT * FROM customers ____ ___ = ______ ;

Answer 4

SELECT * FROM customers WHERE city = ‘Berlin’

Question 5

Use the NOT keyword to select all records where City is NOT “Berlin”.

SELECT * FROM customers _________ = _______

Answer 5

SELECT * FROM customers WHERE NOT city = ‘Berlin’;

Question 6

Select all records where the CustomerID column has the value 32.

SELECT * FROM customers ______ customer id __ __;

Answer 6

SELECT * FROM customers WHERE customerID = 32;

Question 7

Select all records where the City column has the value ‘Berlin’ and the PostalCode column has the value 12209.

______ * FROM customers ___ city = ‘Berlin’ __ ____ = 12209

Answer 7

SELECT * FROM customers WHERE city = ‘Berlin’ AND postalcode = 12209

Question 8

Select all records where the City column has the value ‘Berlin’ or ‘London’.

______ * FROM customers ___ city = ‘Berlin’ __ ___ = _______

Answer 8

SELECT * FROM customers WHERE City = ‘Berlin’ OR city = ‘London’

Question 9

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

SELECT * FROM customers ______ _____

Answer 9

SELECT * FROM customers ORDER BY city;

Question 10

Select all records from the Customers table, sort the result reversed alphabetically by the column City.

SELECT * FROM customers ____ ___ ___ ;

Answer 10

SELECT * FROM customers ORDER BY city DESC

Question 11

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

SELECT * FROM customers _____ ___ ____;

Answer 11

SELECT * FROM customers ORDER BY country, city;

Question 12

Insert a new record in the Customers table.

_____ Customers __ customername, address, city, postalcode, country__
_____ __ ‘Hekkan Burger’, ‘Gatevein 15’, ‘Sandnes’, ‘4306’, ‘Norway’__;

Answer 12

INSERT INTO Customers (customername, address, city, postalcode, country)
VALUES (‘Hekkan Burger’, ‘Gatevein 15’, ‘Sandnes’, ‘4306’, ‘Norway’);

Question 13

Select all records from the Customers where the PostalCode column is empty.

SELECT * FROM customers WHERE ____ __ ____;

Answer 13

SELECT * FROM customers WHERE postalcode IS NULL;

Question 14

Select all records from the Customers where the PostalCode column is NOT empty.

SELECT * FROM customers WHERE ___ ___ ___ ____;

Answer 14

SELECT * FROM customers WHERE postalcode IS NOT NULL

Question 15

Update the City column of all records in the Customers table.

________ customers ___ city = ‘Oslo’

Answer 15

UPDATE customers SET city = ‘Oslo’;

Question 16

Set the value of the City columns to ‘Oslo’, but only the ones where the Country column has the value “Norway”.

_____ customers __ city = ‘oslo’ ____ country = ‘Norway’

Answer 16

UPDATE customers SET city = ‘Oslo’ WHERE country = ‘Norway’

Question 17

Update the City value and the Country value.

_____ customers __ city = ‘Oslo’ __ ______ = ‘Norway’ WHERE customerID = 32

Answer 17

UPDATE customers SET city = ‘Oslo’, country = ‘Norway’ WHERE customerID = 32

Question 18

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

_______ ______ customers _____ country =’Norway’

Answer 18

DELETE FROM customers WHERE country = ‘Norway’

Question 19

Delete all the records from the Customers table.

________ customers;

Answer 19

DELETE FROM customers

Question 20

Use the MIN function to select the record with the smallest value of the Price column.

SELECT ______ FROM products

Answer 20

SELECT MIN(price) FROM products

Question 21

Use an SQL function to select the record with the highest value of the Price column.

SELECT________ FROM products

Answer 21

SELECT MAX(price) FROM products

Question 22

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

SELECT ____ (*) FROM products ____ price = 18

Answer 22

SELECT COUNT(*) FROM products WHERE price = 18

Question 23

Use an SQL function to calculate the average price of all products.

SELECT ________ FROM products

Answer 23

SELECT AVG(price) FROM products

Question 24

Use an SQL function to calculate the sum of all the Price column values in the Products table.

SELECT _____ FROM products

Answer 24

SELECT SUM(price) FROM products

Question 25

Select all records where the value of the City column starts with the letter "a". SELECT * FROM customers _______ ________

Answer 25

SELECT * FROM customers WHERE city LIKE 'a%'

Question 26

Select all records where the value of the City column ends with the letter "a". SELECT * FROM customers ____ ___ ___ ___

Answer 26

SELECT * FROM customers WHERE city LIKE '%a'

Question 27

Select all records where the value of the City column contains the letter "a". SELECT * FROM customers ___ _____ ____ __

Answer 27

SELECT * FROM customers WHERE city LIKE '%a%'

Question 28

Select all records where the value of the City column starts with letter "a" and ends with the letter "b". SELECT * FROM customers ___ _____ ____ __

Answer 28

SELECT * FROM customers WHERE city LIKE 'a%b'

Question 29

Select all records where the value of the City column does NOT start with the letter "a". SELECT * FROM customers ___ _____ ____ ___ __

Answer 29

SELECT * FROM customers WHERE city NOT LIKE 'a%'

Question 30

Select all records where the second letter of the City is an "a". SELECT * FROM customers WHERE city LIKE '_%'

Answer 30

SELECT * FROM customers WHERE city LIKE '_a%'

Question 31

Select all records where the first letter of the City is an "a" or a "c" or an "s". SELECT * FROM customers WHERE city LIKE '___%'

Answer 31

SELECT * FROM customers WHERE city LIKE '[acs]%'

Question 32

Select all records where the first letter of the City starts with anything from an "a" to an "f". SELECT * FROM customers WHERE city LIKE '____%'

Answer 32

SELECT * FROM customers WHERE city LIKE '[a-f]%'

Question 33

Select 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 '____%'

Answer 33

SELECT * FROM customers WHERE city LIKE '[^acf]%'

Question 34

Use the IN operator to select all the records where Country is either "Norway" or "France". SELECT * FROM customers _____ _____ 'France' _

Answer 34

SELECT * FROM customers WHERE country IN ('Norway', 'France')

Question 35

Use the IN operator to select all the records where Country is NOT "Norway" and NOT "France". SELECT * FROM customers _____ ___ __ ('Norway', 'France')

Answer 35

SELECT * FROM customers WHERE country NOT IN ('Norway', 'France')

Question 36

Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20. SELECT * FROM products WHERE price ______

Answer 36

SELECT * FROM products WHERE price BETWEEN 10 AND 20

Question 37

Use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20. SELECT * FROM products WHERE price ______

Answer 37

SELECT * FROM products WHERE price NOT BETWEEN 10 AND 20

Question 38

Use the BETWEEN operator to select all the records where the value of the ProductName column is alphabetically between 'Geitost' and 'Pavlova'. SELECT * FROM products WHERE productname _________

Answer 38

SELECT * FROM products WHERE productname BETWEEN 'Geitost' AND 'Pavlova'

Question 39

When displaying the Customers table, make an ALIAS of the PostalCode column, the column should be called Pno instead. SELECT customername, address, postalcode ____ FROM customers

Answer 39

SELECT customername, address, postalcode AS pno FROM customers

Question 40

When displaying the Customers table, refer to the table as Consumers instead of Customers. SELECT * FROM customers ________

Answer 40

SELECT * FROM customers AS Consumers

Question 41

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 _________ = ___________

Answer 41

SELECT * FROM orders LEFT JOIN Customers ON orders.customerid=customers.customerID

Question 42

# 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

Answer 42

SELECT * FROM orders INNER JOIN Customers ON orders.customerid=customers.customerID

Question 43

# 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

Answer 43

SELECT * FROM orders RIGHT JOIN Customers ON orders.customerid=customers.customerID

Question 44

List the number of customers in each country. SELECT ____(customerID), country FROM customers _________

Answer 44

``` SELECT COUNT(customerID), Country FROM customers GROUP BY country ```

Question 45

List the number of customers in each country, ordered by the country with the most customers first. ``` SELECT _______ (CustomerID), country FROM customers __________ ORDER BY _____________ ```

Answer 45

``` SELECT COUNT(customerID), Country FROM customers GROUP BY country ORDER BY COUNT(CustomerID) DESC ```

Question 46

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

Answer 46

CREATE DATABASE testdb

Question 47

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

Answer 47

DROP DATABASE testDB

Question 48

Write the correct SQL statement to create a new table called Persons. ``` _____________ ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); ```

Answer 48

``` CREATE TABLE persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) ```

Question 49

Write the correct SQL statement to delete a table called Persons.

Answer 49

DROP TABLE persons

Question 50

Use the TRUNCATE statement to delete all data inside a table.

Answer 50

TRUNCATE TABLE persons

Question 51

Add a column of type DATE called Birthday.

Answer 51

ALTER TABLE persons | ADD birthday DATE

Question 52

Delete the column Birthday from the Persons table.

Answer 52

ALTER TABLE persons | DROP COLUMN birthday