999 SQL Exercises

#Edu #SQL

Table of Contents

Exercise: SQL Syntax

002 SQL Syntax
Which SQL statement is used to select all records from a table named 'Customers'?

SELECT FROM Customers;
SELECT ALL FROM Customers;
SELECT * FROM Customers;
GET ALL FROM Customers;

What is a table in a database?

A collection of databases
A structured set of data organized in rows and columns
A list of SQL statements
A command for executing SQL queries

Are SQL keywords case-sensitive?

Yes, they are case-sensitive.
No, they are not case-sensitive.
Only in certain database systems.
It depends on the SQL statement.

Why is a semicolon used at the end of SQL statements?

To improve readability
To separate multiple SQL statements
To make SQL statements case-insensitive

Exercise: SQL Select

003 SQL SELECT
What is the purpose of the SQL SELECT statement?

To insert data into a database
To select data from a database
To delete data from a database
To update data in a database

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

SELECT * FROM Customers;

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

SELECT City FROM Customers;

How would you select the 'CustomerName' and 'City' columns from a table named 'Customers'?

SELECT CustomerName City FROM Customers;
SELECT * FROM Customers;
SELECT CustomerName, City FROM Customers;
SELECT CustomerName AND City FROM Customers;

Drag and drop the correct syntax to select all columns from a table named 'Customers'

SELECT * FROM Customers;

Exercise: SQL Select Distinct

004 SQL SELECT DISTINCT
Which of the following SQL statements would return a list of all unique countries from a table named 'Customers'?

SELECT UNIQUE Country FROM Customers;
SELECT Country FROM Customers;
SELECT DISTINCT Country FROM Customers;
SELECT * FROM Customers WHERE Country IS UNIQUE;

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

SELECT DISTINCT Country FROM Customers;

What would be the result of omitting the DISTINCT keyword in a statement like SELECT DISTINCT Country FROM Customers;?

It would result in an error
It would return all values in the Country column, including duplicate values.
It would remove all null values from the Country column
It would sort the countries alphabetically

What does the following SQL statement do?
SELECT COUNT(DISTINCT Country) FROM Customers;

Returns all countries in the Customers table, excluding the 10 first countries
Returns the number of different countries in the Customers table
Returns only the first 10 countries in the Customers table

In which scenario would you most likely use the DISTINCT keyword?

To select all rows from a table
To return unique values from a column that contains duplicates
To delete duplicate rows in a table
To filter rows that contain null values

Exercise: SQL Where

005 SQL WHERE
What is the purpose of the SQL WHERE clause?

To specify the table from which to select data
To filter records that meet a specified condition
To join multiple tables together
To sort records in ascending order

Which of the following SQL statements would return all customers from 'Mexico'?

SELECT * FROM Customers WHERE City='Mexico';
SELECT * FROM Customers WHERE Country='Mexico';
SELECT * FROM Customers AND Country='Mexico';
SELECT * WHERE Country='Mexico';

Select all records where the City column has the value "Berlin".

SELECT * FROM Customers WHERE City = 'Berlin';

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

SELECT * FROM Customers WHERE CustomerID = 32;

How should text values be enclosed in the SQL WHERE clause?

With double quotes
With single quotes
With no quotes
With backticks

Drag and drop to select all customers with a CustomerID greater than 50.

SELECT * FROM Customers WHERE CustomerID > 50;

Exercise: SQL Order By

006 SQL ORDER BY
What is the purpose of the SQL ORDER BY keyword?

To filter records based on a condition
To group records with the same value
To sort records in ascending or descending order

Which SQL statement sorts products from highest to lowest price?

SELECT * FROM Products ORDER BY Price;
SELECT * FROM Products ORDER BY Price ASC;
SELECT * FROM Products ORDER BY Price DESC;
SELECT * FROM Products SORT BY Price DESC;

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

SELECT * FROM Customers ORDER BY City;

Select all records from the Customers table, sort the result reversed alphabetically by the column 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;

In the SQL ORDER BY clause, what is the default sorting order if ASC or DESC is not specified?

Descending
Ascending
Random

Exercise: SQL And

007 SQL AND
True or False:
The AND operator is used to filter records based on more than one condition.

True
False

What will this SQL query return?
SELECT * FROM Customers
WHERE Country = 'Spain'
AND CustomerName LIKE 'G%';

All customers from Spain
All customers whose names start with 'G'
All customers from Spain whose names start with 'G'
All customers whose names start with 'Spain'

What result will this query return?
SELECT * FROM Customers
WHERE Country = 'Germany'
AND City = 'Berlin' AND PostalCode > 12000;

Customers from Germany in any city
Customers from Germany in Berlin with a PostalCode over 12000
Customers from Berlin with a PostalCode over 12000
All German customers regardless of city or PostalCode

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

SELECT * FROM Customers WHERE City = 'Berlin' AND PostalCode = '12209';

True or False:
You cannot combine AND and OR operators.

True
False

Exercise: SQL Or

008 SQL OR
What is the primary purpose of the SQL OR operator?

To filter records based on multiple conditions where at least one condition is true
To filter records based on multiple conditions where all conditions must be true
To sort records in descending order
To count the number of rows in a table

What is the primary purpose of the SQL OR operator?

To filter records based on multiple conditions where at least one condition is true
To filter records based on multiple conditions where all conditions must be true
To sort records in descending order
To count the number of rows in a table

Select all records where the City column has the value 'Berlin' OR 'London'.

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

Which SQL query would select all customers from either Germany or Spain?

SELECT * FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
SELECT * FROM Customers
WHERE Country = 'Germany' AND Country = 'Spain';
SELECT * FROM Customers
WHERE Country ARE ('Germany', 'Spain');
SELECT * FROM Customers
WHERE Country FROM 'Germany' OR 'Spain';

What will the following SQL query return?
SELECT * FROM Customers
WHERE City = 'Berlin' OR Country = 'Norway';

All customers from either Berlin or Norway
All customers from Berlin only
All customers from Norway only

Which SQL query would select all Spanish customers whose names start with 'G' or 'R'?

SELECT * FROM Customers
WHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
SELECT * FROM Customers
WHERE Country = 'Spain' OR CustomerName LIKE 'G%' OR CustomerName LIKE 'R%';
SELECT * FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%' AND CustomerName LIKE 'R%';
SELECT * FROM Customers
WHERE Country = 'Spain' OR CustomerName LIKE 'G%';

Exercise: SQL Not

009 SQL NOT
What is the primary purpose of the SQL NOT operator?

To filter records that match a specified condition
To filter records that do not match a specified condition
To filter records that match multiple conditions
To filter records that are exactly equal to a specified condition

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

SELECT * FROM Customers WHERE NOT City = 'Berlin';

Which SQL query would select all customers that are NOT from 'Spain'?

SELECT * FROM Customers WHERE Country = 'Spain';
SELECT * FROM Customers WHERE Country NOT 'Spain';
SELECT * FROM Customers WHERE NOT Country = 'Spain';
SELECT * FROM Customers WHERE Country != 'Spain';

Select the correct statements to return all customers whose names do NOT start with the letter 'A':

SELECT * FROM customers WHERE CustomerName NOT LIKE 'A%';

Which SQL statement would select all customers whose CustomerID is NOT between 10 and 50?

SELECT * FROM Customers WHERE CustomerID = 10 AND 50;
SELECT * FROM Customers WHERE CustomerID BETWEEN 10 AND 50;
SELECT * FROM Customers WHERE CustomerID NOT IN (10, 50);
SELECT * FROM Customers WHERE CustomerID NOT BETWEEN 10 AND 50;

Which query will select customers who are NOT located in 'Paris' or 'London'?

SELECT * FROM Customers WHERE NOT City IN ('Paris', 'London');
SELECT * FROM Customers WHERE City NOT IN ('Paris', 'London');
SELECT * FROM Customers WHERE City != ('Paris', 'London');
SELECT * FROM Customers WHERE City NOT LIKE ('Paris', 'London');

Exercise: SQL Insert Into

010 SQL INSERT INTO
What is the purpose of the SQL INSERT INTO statement?

To update records in a table
To delete records from a table
To add new records to a table
To retrieve records from a table

Drag and drop the correct syntax to insert data into the Customers table for specified columns:

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

Insert a new record in the Customers table.

INSERT INTO Customers (
CustomerName,
Address,
City,
PostalCode,
Country)

VALUES (
'Hekkan Burger',
'Gateveien 15',
'Sandnes',
'4306',
'Norway');

How can you insert multiple rows with a single INSERT INTO statement in SQL?

INSERT INTO table_name VALUES (value1), (value2), (value3);
INSERT INTO table_name VALUES ROWS (value1), (value2), (value3);
INSERT INTO table_name ROWS (value1), (value2), (value3);
INSERT INTO table_name VALUES (value1, value2, value3);

Exercise: SQL Null Values

011 SQL NULL
What is a NULL value in SQL?

A field with no value
A field with a zero value
A field containing spaces
A field that has been deleted

How can you test for NULL values in SQL?

Using the = operator
Using the IS NULL operator
Using the != operator
Using the < operator

Which of the following SQL queries would retrieve customers with no address provided?

SELECT * FROM Customers WHERE Address = NULL;
SELECT * FROM Customers WHERE Address IS NULL;
SELECT * FROM Customers WHERE Address != NULL;
SELECT * FROM Customers WHERE Address <> NULL;

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

SELECT * FROM Customers
WHERE PostalCode IS NULL;

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

SELECT * FROM Customers
WHERE PostalCode IS NOT NULL;

What is the correct SQL syntax to test if a field is NOT NULL?

SELECT column_name FROM table WHERE column_name IS NOT NULL;
SELECT column_name FROM table WHERE column_name != NULL;
SELECT column_name FROM table WHERE column_name <> NULL;
SELECT column_name FROM table WHERE column_name = NOT NULL;

Select the correct syntax to test for empty values in an 'Address' field:

SELECT * FROM Customers WHERE Address IS NULL ;

Exercise: SQL Update

012 SQL UPDATE
What is the purpose of the SQL UPDATE statement?

To add new records to a table
To delete records from a table
To modify existing records in a table
To retrieve records from a table

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

Update Customers SET City = 'Oslo';

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.

UPDATE Customers SET City = 'Oslo', Country = 'Norway' WHERE CustomerID = 32;

What will happen if you omit the WHERE clause in an UPDATE statement?

No records will be updated
Only the first record will be updated
All records will be updated
An error will occur

Select the correct syntax to update the City column of all records in the Customers table.

UPDATE Customers SET City = 'New York';

Exercise: SQL Delete

013 SQL DELETE
What is the purpose of the SQL DELETE statement?

To add new records to a table
To modify existing records in a table
To delete existing records from a table
To retrieve records from a table

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;

What will happen if you omit the WHERE clause in a DELETE statement?

No records will be deleted
Only the first record will be deleted
All records in the table will be deleted
An error will occur

How would you delete all records from a table named 'Customers' without deleting the table itself?

DELETE FROM Customers WHERE CustomerID > 0;
DELETE FROM Customers;
DROP TABLE Customers;
DELETE Customers;

Drag and drop the correct syntax to delete a record where 'CustomerName' is 'Alfreds Futterkiste'.

DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste';

Exercise: SQL Select Top

014 SQL TOP
What would the following query do in SQL Server?
SELECT TOP 5 * FROM Customers;

Select the first 5 records from the Customers table
Select the last 5 records from the Customers table
Select 5 records sorted by CustomerName
Select all records with CustomerID less than 5

Which SQL clause is used to limit the number of returned records in MySQL?

TOP
ROWNUM
LIMIT
FETCH FIRST

Drag and drop the correct syntax to select the first 3 records in MySQL, sorted by CustomerName in descending order.

SELECT * FROM Customers ORDER BY CustomerName DESC LIMIT 3;

Exercise: SQL Min and Max

016 SQL MIN MAX
What does the SQL MIN() function do?

Returns the largest value of the selected column
Returns the smallest value of the selected column
Calculates the average of the selected column
Counts the number of records in the selected column

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

SELECT MIN(Price) FROM Products;

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

SELECT MAX(Price) FROM Products;

Which keyword can be used to give a column a descriptive name?

DESC
ASC
AS
UPDATE
GROUP

Drag and drop the correct syntax to select the highest price in the 'Price' column.

SELECT MAX (Price) FROM Products;

Exercise: SQL Count

017 SQL COUNT
What does the SQL COUNT() function do?

Calculates the sum of all values in a column
Returns the number of rows that match a specified criterion
Finds the minimum value in a column
Returns the average value of all rows in a column

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

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

Which keyword can be used to ignore duplicates?

DISCOUNT
DISTINCT
DESC
DESCRIPTIVE

Which keyword can be used to give a column a name?

DESC
ASC
AS
UPDATE
GROUP

Exercise: SQL Sum

018 SQL SUM
What does the SQL SUM() function do?

Counts the number of rows in a table
Returns the total sum of a numeric column
Calculates the average value of a column
Returns the maximum value in a column

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

SELECT SUM(Price) FROM Products;

Which of the following queries would return the sum of all Quantity values in the 'OrderDetails' table?

SELECT SUM(Quantity) FROM OrderDetails;
SELECT COUNT(Quantity) FROM OrderDetails;
SELECT TOTAL(Quantity) FROM OrderDetails;
SELECT SUM(ProductID) FROM OrderDetails;

What will the following query return?
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = 11;

The total sum of all Quantity values in the OrderDetails table
The total sum of Quantity values where ProductID is 11
The sum of all ProductID values where Quantity is 11
The average of Quantity values where ProductID is 11

Drag and drop the correct function to calculate the total quantity from the OrderDetails table.

SELECT SUM (Quantity) FROM OrderDetails;

Exercise: SQL Avg

019 SQL AVG
What does the SQL AVG() function do?

Returns the sum of a numeric column
Returns the average value of a numeric column
Counts the number of rows in a table
Finds the maximum value in a numeric column

Which of the following queries will return the average price of products?

SELECT SUM(Price) FROM Products;
SELECT COUNT(Price) FROM Products;
SELECT MAX(Price) FROM Products;
SELECT AVG(Price) FROM Products;

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

SELECT AVG(Price) FROM Products;

What does the AVG() function ignore while calculating the average?

Zero values
Negative values
NULL values
Duplicate values

Drag and drop the correct function to calculate the average price of products.

SELECT AVG (Price) FROM Products;

Exercise: SQL Like

020 SQL LIKE
What does the SQL LIKE operator do?

Groups records based on a condition
Returns the largest value in a column
Searches for a specified pattern in a column

Which SQL statement will return all customers with names starting with 'H'?

SELECT * FROM Customers WHERE CustomerName LIKE '%H';
SELECT * FROM Customers WHERE CustomerName LIKE 'H';
SELECT * FROM Customers WHERE CustomerName LIKE 'H%';
SELECT * FROM Customers WHERE CustomerName LIKE 'H_%';

Which wildcard in the LIKE operator represents any single character?

The percent sign (%)
The underscore sign ( _ )
The caret sign (^)
The asterisk sign ( * )

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

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

Select all records where the value of the City column ends with the letter "a".

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

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

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

Select all records where the value of the City column starts with letter "a" and ends with the letter "b".

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

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

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

Drag and drop the correct SQL keywords to select customers whose names start with 'A'.

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

Exercise: SQL Wildcards

021 SQL Wildcards
What does the % wildcard represent in SQL?

Zero or more characters
A single character
Any single character within a range
An exact match for a string

Which wildcard should be used to represent a single character in SQL?

%
_ underscore
[]

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

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

How do you specify a range of characters in SQL using wildcards?

Using '%' with the range inside brackets
Using '_' with the range inside quotes
Using '[]' with the range and a '-' between the characters
Using '{}' with the range and a ',' between the characters

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 '[ acs ]%';

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 '[ a-f ]%';

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 '[ !acf ]%';

Drag and drop the correct wildcard to select cities that start with 'L' and end with 'n'.

SELECT * FROM Customers WHERE City LIKE 'L%n' ;

Exercise: SQL In

022 SQL IN
What is the primary purpose of the SQL IN operator?

To specify a range of values in a WHERE clause
To select all columns from a table
To perform a wildcard search

Which query returns all customers from 'Germany', 'France', or 'UK'?

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

What will the following query return?
SELECT * FROM Customers
WHERE Country NOT IN ('Mexico', 'Sweden');

All customers from Mexico and Sweden
All customers except those from Mexico and Sweden
All customers with NULL values in the Country column
An error due to syntax issues

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

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

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

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

Drag and drop the correct SQL keywords to return customers from the countries 'Norway', 'France', or 'USA'.

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

Exercise: SQL Between

023 SQL BETWEEN

Which SQL statement selects all products with a price between 10 and 20?

SELECT * FROM Products WHERE Price = 10 AND Price = 20;
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
SELECT * FROM Products WHERE Price >= 10 OR Price <= 20;
SELECT * FROM Products WHERE Price LIKE '10-20';

What is the primary purpose of the SQL BETWEEN operator?

To select values within a specified range
To check if a value matches a specific pattern
To filter values based on a list
To combine multiple conditions in a WHERE clause

What does the SQL NOT BETWEEN operator do?

Selects values that do not fall within a specified range
Selects values that are null
Selects values outside of a table
Selects values that match a specified pattern

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 BETWEEN 10 AND 20;

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 NOT BETWEEN 10 AND 20;

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 BETWEEN 'Geitost' AND 'Pavlova';

Which SQL query uses BETWEEN with text values?

SELECT * FROM Products
WHERE ProductName = 'A%' AND ProductName = 'Z%';
SELECT * FROM Products
WHERE ProductName BETWEEN 'Apple' AND 'Orange';
SELECT * FROM Products
WHERE ProductName NOT IN ('Apple', 'Orange');
SELECT * FROM Products
WHERE ProductName LIKE 'Apple%Orange';

Drag and drop the correct syntax to complete the SQL query that selects products with prices between 50 and 70.

SELECT * FROM Products WHERE Price BETWEEN 50 AND 70;

Exercise: SQL Aliases

024 SQL Aliases

Which SQL statement creates an alias for the CustomerID column as 'ID'?

SELECT CustomerID = 'ID' FROM Customers;
SELECT CustomerID AS ID FROM Customers;
SELECT CustomerID TO ID FROM Customers;
SELECT CustomerID 'ID' FROM Customers;

What is the primary purpose of SQL aliases?

To permanently rename a table or column
To create temporary names for columns or tables
To join multiple tables together
To optimize query performance

When displaying the Customers table, make an ALIAS of the PostalCode column, the column should be called Pno instead.

SELECT CustomerName, Address, PostalCode AS Pno FROM Customers;

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

SELECT * FROM Customers AS Consumers;

Drag and drop the correct syntax to create an alias for ProductName as 'Great Products'.

SELECT ProductName AS [ Great Products ];

Exercise: SQL Joins

025 SQL Joins

What is the primary purpose of an SQL JOIN?

To create a new table from existing tables
To delete duplicate rows in a table
To sort data in ascending or descending order
To combine rows from two or more tables based on a related column

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 ON Orders.CustomerID = Customers.CustomerID;

Which SQL JOIN type returns only the records that have matching values in both tables?

LEFT JOIN
FULL OUTER JOIN
INNER JOIN
RIGHT JOIN

What does a LEFT JOIN return?

All records from the left table and matched records from the right table
Only matched records from both tables
All records from the right table and matched records from the left table
All records from both tables

Which SQL statement is correct to join 'Orders' and 'Customers' tables on 'CustomerID'?

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

Which SQL JOIN returns all records when there is a match in either left or right table?

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

Exercise: SQL Inner Join

026 SQL INNER JOIN

What is the purpose of the SQL INNER JOIN keyword?

To return unmatched records from the left table
To return unmatched records from the right table
To select only the records that have matching values in both tables

Which SQL statement is correct for joining the Products and Categories tables on CategoryID?

SELECT * FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;
SELECT * FROM Products
JOIN Categories;
SELECT * FROM Products
INNER JOIN Categories;
SELECT Products.ProductID, ProductName
FROM Categories;

What happens when there is no match between tables in an INNER JOIN?

The unmatched rows from both tables are returned
The unmatched rows from the left table are returned
The unmatched rows from the right table are returned
No rows are returned for the unmatched entries

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.

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

Drag and drop the correct syntax to create an INNER JOIN between Products and Categories.

SELECT ProductID, Products.ProductName, Categories.CategoryName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

Exercise: SQL Left Join

027 SQL LEFT JOIN

What does the SQL LEFT JOIN keyword do?

Returns only matching records from both tables
Returns all records from the left table and matching records from the right table
Returns all records from the right table and matching records from the left table
Returns all records from both tables

True or False?
In some databases LEFT JOIN is called LEFT OUTER JOIN.

True
False

Drag and drop the correct syntax to perform a LEFT JOIN between Customers and Orders.

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

Exercise: SQL Right Join

028 SQL RIGHT JOIN

What does the SQL RIGHT JOIN keyword do?

Returns only matching records from both tables
Returns all records from the left table and matching records from the right table
Returns all records from the right table and matching records from the left table
Returns all records from both tables

True or False?
In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

True
False

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.

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

Exercise: SQL Full Join

029 SQL FULL JOIN

What does the SQL FULL OUTER JOIN keyword do?

Returns all records when there is a match in the left or right table
Returns all records from the left table and matching records from the right table
Returns all records from the right table and matching records from the left table

True or False?
FULL OUTER JOIN and FULL JOIN are the same.

True
False

True or False?
FULL OUTER JOIN can potentially return very large result-sets.

True
False

Exercise: SQL Self Join

030 SQL Self Join

What is the purpose of a self join in SQL?

To join a table with another table
To join a table with itself
To create a new table by combining columns
To perform a full outer join

Which of the following SQL statements correctly implements a self join?

SELECT * FROM Customers;
SELECT A.CustomerName, B.CustomerName
FROM Customers A
JOIN SELF Customers B;
SELECT A.CustomerName, B.CustomerName
FROM Customers A, Customers B
WHERE A.City = B.City;
SELECT A.CustomerName, B.CustomerName
FROM Customers
SELF JOIN Customers;

In a self join, what is the purpose of using table aliases like A and B?

To join different tables
To reference the same table with different roles
To eliminate duplicate records
To use aggregate functions more efficiently

Exercise: SQL Union

031 SQL UNION

What is the primary purpose of the SQL UNION operator?

To create a new table with combined columns
To delete duplicate rows in a table
To perform a self join
To combine the result-sets of two or more SELECT statements

What is the key difference between UNION and UNION ALL?

UNION includes all rows, including duplicates, while UNION ALL removes duplicates
UNION removes duplicates by default, while UNION ALL includes all rows
UNION sorts the rows, while UNION ALL does not
There is no difference between UNION and UNION ALL

Which condition must be met when using the UNION operator?

The tables must have the same number of rows
The SELECT statements must have the same number of columns with similar data types
The tables must have a primary key
The columns must be indexed

Drag and drop the correct syntax to combine cities from Customers and Suppliers with duplicate values.

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

Exercise: SQL Group By

033 SQL GROUP BY

Which aggregate functions are commonly used with the GROUP BY statement?

COUNT(), MAX(), MIN(), SUM(), AVG()
SELECT(), INSERT(), UPDATE(), DELETE()
JOIN(), UNION(), INTERSECT(), EXCEPT()
HAVING(), WHERE(), ORDER BY(), DISTINCT()

List the number of customers in each country.

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

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

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

What is the primary purpose of the SQL GROUP BY statement?

To combine rows from multiple tables
To group rows with the same values into summary rows
To filter rows based on a condition
To sort the rows in a result set

How can you sort the results of a GROUP BY statement?

Use the ORDER BY clause after the GROUP BY clause
Use the HAVING clause before the GROUP BY clause
Use the DISTINCT keyword in the query
You cannot sort results of a GROUP BY query

Exercise: SQL Having

034 SQL HAVING

What is the primary purpose of the SQL HAVING clause?

To filter rows based on a condition before grouping - WHERE
To filter groups based on an aggregate condition after grouping
To order the result set in ascending or descending order
To join multiple tables

Why can't the WHERE clause be used with aggregate functions?

The WHERE clause can only filter columns, not rows
Aggregate functions are evaluated after the WHERE clause
The WHERE clause is not compatible with GROUP BY
Aggregate functions require a JOIN clause

Which SQL query correctly filters groups where the total number of orders is greater than 100?

SELECT COUNT(OrderID), CustomerID
FROM Orders
WHERE COUNT(OrderID) > 100
GROUP BY CustomerID;
SELECT CustomerID, COUNT(OrderID)
FROM Orders
GROUP BY CustomerID
WHERE COUNT(OrderID) > 100;
SELECT CustomerID, COUNT(OrderID)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 100;
SELECT COUNT(OrderID), CustomerID
FROM Orders
HAVING COUNT(OrderID) > 100
GROUP BY CustomerID;

What is the difference between the WHERE and HAVING clauses?

The WHERE clause filters rows; the HAVING clause filters groups
The WHERE clause filters groups; the HAVING clause filters rows
The WHERE clause is used only with JOIN; the HAVING clause is used with UNION
The WHERE clause is used for sorting; the HAVING clause is used for filtering

Drag and drop the correct syntax to lists the employees that have registered more than 10 orders.

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Exercise: SQL Exists

035 SQL EXISTS

What does the SQL EXISTS operator do?

Checks if a subquery returns one or more records
Combines results of multiple queries
Joins two tables based on a common column
Filters rows based on a condition

The EXISTS operator returns ___ when the subquery produces one or more records.

NULL
FALSE
TRUE
ERROR

Drag and drop the correct syntax to create a query that lists suppliers with products priced under 20.

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

Exercise: SQL Any, All

036 SQL ANY ALL

Which SQL statement uses the ANY operator correctly?

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

What does the SQL ALL operator do?

Returns TRUE if all subquery values meet the condition
Returns TRUE if any subquery value meets the condition
Returns all columns from the table
Returns only distinct rows from the table

Which SQL statement uses the ALL operator correctly?

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

Drag and drop the correct SQL keywords to complete the query using the ANY operator.

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

Drag and drop the correct SQL keywords to complete the query using the ALL operator.

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

Exercise: SQL Select Into

037 SQL SELECT INTO

What is the primary purpose of the SQL SELECT INTO statement?

To update data in an existing table
To copy data from one table into a new table
To delete data from a table
To create an index on a table

Which of the following SQL statements creates a backup copy of the 'Customers' table?

SELECT * FROM Customers INTO CustomersBackup;
SELECT INTO CustomersBackup FROM Customers;
SELECT * INTO CustomersBackup FROM Customers;
SELECT INTO Customers FROM CustomersBackup;

How can you copy only specific columns into a new table using SELECT INTO?

SELECT column1, column2 INTO newtable FROM oldtable;
SELECT INTO newtable column1, column2 FROM oldtable;
SELECT column1, column2 INTO oldtable FROM newtable;
SELECT INTO oldtable column1, column2 FROM newtable;

Drag and drop the correct SQL keywords to create a new table with German customers only.

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

Drag and drop the correct SQL keywords to copy data from multiple tables into a new table.

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

Exercise: SQL Insert Into Select

038 SQL INSERT INTO SELECT

What is the purpose of the SQL INSERT INTO SELECT statement?

To copy data from one table and insert it into another table
To delete rows from one table and insert them into another
To test for the existence of any record in a subquery
To combine rows from two or more tables, based on a related column between them

True or False:
The INSERT INTO SELECT statement requires that the data types in source and target tables match.

True
False

How do you copy specific columns from 'Suppliers' to 'Customers'?

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

Drag and drop the correct SQL keywords to complete the query.

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

Exercise: SQL CASE Expression

039 SQL CASE

What does the SQL CASE expression do?

It iterates over table rows and returns a value for each column. - Cursor?
It evaluates conditions and returns a value based on the first true condition.
It specifies an alias for a column or table.
It combines rows from two tables based on a condition.

What happens if no condition is true in a CASE expression and there is no ELSE clause?

It returns NULL.
It throws an error.
It stops execution of the query.
It defaults to the first condition.

Drag and drop the correct keywords to complete the SQL CASE expression.

CASE
WHEN Quantity > 50 THEN 'High'
WHEN Quantity <= 50 THEN 'Low'
ELSE 'Unknown'
END AS QuantityCategory

Exercise: SQL NULL Functions

040 SQL NULL

In SQL Server, which function can be used to replace NULL values with a specified alternative?

NVL()
ISNULL()
IFNULL()
COALESCE()

Which function in MySQL is used to handle NULL values by providing an alternative value?

COALESCE()
ISNULL()
IFNULL()
NVL()

What will happen if you do not handle NULL values in an expression like UnitPrice * (UnitsInStock + UnitsOnOrder)?

The result will be NULL if any value in the expression is NULL.
The result will default to 0.
The query will fail to execute.
The NULL value will be ignored.

Which function is supported by both Oracle and SQL Server to handle NULL values?

NVL()
ISNULL()
IFNULL()
COALESCE()

Which function is specific to Oracle for replacing NULL values?

ISNULL()
IFNULL()
NVL()
COALESCE()

Exercise: SQL Stored Procedures

041 SQL Stored Procedures

What is the primary purpose of a stored procedure in SQL Server?

To optimize indexing in the database
To save reusable SQL code for repeated use
To create tables automatically
To handle NULL values in queries

Which of the following is the correct syntax to create a stored procedure?

CREATE PROCEDURE procedure_name GO sql_statement AS;
EXEC PROCEDURE procedure_name GO sql_statement;
CREATE PROCEDURE procedure_name GO sql_statement;
CREATE PROCEDURE procedure_name AS sql_statement GO;

How do you execute a stored procedure named GetCustomers?

CALL GetCustomers;
EXEC GetCustomers;
RUN PROCEDURE GetCustomers;
EXECUTE GetCustomers();

What is the purpose of the @City parameter in the following stored procedure?

CREATE PROCEDURE SelectCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

To define the column to search in the Customers table
To specify a variable for the City filter in the query
To assign default values to all records in the City column
To replace NULL values in the City column

Drag and drop the correct syntax to create a stored procedure with multiple parameters.

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Which of the following is a valid way to execute a stored procedure with two parameters?

EXEC GetCustomerDetails @City = 'London', @PostalCode = 'WA1 1DP';
CALL GetCustomerDetails 'London', 'WA1 1DP';
EXECUTE PROCEDURE GetCustomerDetails(London, WA1 1DP);
EXEC GetCustomerDetails('London', 'WA1 1DP');

Exercise: SQL Comments

042 SQL Comments

What is the primary purpose of comments in SQL?

To execute additional SQL statements
To explain sections of SQL code or prevent execution
To create aliases for columns
To perform complex calculations

Which symbol is used to start a single-line comment in SQL?

--
//
/*
-#

What happens to the text inside a multi-line comment?

It is executed as part of the SQL statement
It is ignored by the SQL engine
It is treated as a syntax error
It is displayed as output

Which of the following statements contains a valid multi-line comment?

/* SELECT * FROM Customers;
/ * SELECT * FROM Customers * /
-- SELECT * FROM Customers */
/ * SELECT * FROM Customers -- * /

How would you comment out the WHERE clause in the following SQL?

SELECT * FROM Customers
WHERE City='Berlin';

-- WHERE City='Berlin';
SELECT * FROM Customers -- WHERE City='Berlin';
SELECT * FROM Customers /* WHERE City='Berlin'; */
Both B and C

Drag and drop to make the WHERE clause a single-line comment.

SELECT * FROM Customers
-- WHERE City = 'Berlin';

What will the following SQL do?

SELECT * FROM Customers
WHERE (CustomerName LIKE 'A%' /* OR CustomerName LIKE 'B%' */);

Return all customers whose names start with 'A' or 'B'
Return all customers whose names start with 'A'
Return all customers
Return no customers

Exercise: SQL Operators

043 SQL Operators

Which SQL operator is used to add two numbers?

PLUS (+)

/

What does the % operator do in SQL?

Calculates the percentage of a value
Returns the remainder of a division
Performs bitwise AND operation

Which SQL operator is used to check if a value matches a pattern?

=
LIKE
IN
BETWEEN

Which of these operators is a compound operator in SQL?

=
==+= ==
AND
IN

What is the purpose of the BETWEEN operator in SQL?

To check if a value is between a given range
To compare two columns
To check for matching patterns in strings
To combine multiple conditions

The <> operator in SQL is equivalent to:

=
NOT
NOT EQUAL
BETWEEN

Drag and drop the correct SQL operators to complete the query.

SELECT * FROM Customers
WHERE City = 'Berlin' AND Age < 30;

Exercise: SQL Create Database

101 SQL CREATE DATABASE

What is the purpose of the SQL CREATE DATABASE statement?

To create a new table in an existing database
To delete an existing database
To create a new SQL database
To modify an existing database

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

CREATE DATABASE testDB;

What should you ensure before creating a new database?

You have guest privileges
You have admin privileges
You have at least one existing table
You have a backup of all other databases

Drag and drop the correct SQL keywords to create a database named 'SchoolDB'.

CREATE DATABASE SchoolDB;

After creating a database, how can you confirm its creation?

By running the command SHOW DATABASES;
By restarting the SQL server
By creating a table inside the database
By running a SELECT query

Exercise: SQL Drop Database

105 SQL DROP TABLE

What is the purpose of the SQL DROP DATABASE statement?

To delete a table from a database
To delete all records in a table
To delete an existing database
To delete specific rows from a database

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

DROP DATABASE testDB;

What happens when you drop a database?

All tables in the database are removed, but the database structure remains
Only empty tables are removed
The database and all its contents are permanently deleted
The database is temporarily deactivated

Which precaution should you take before dropping a database?

Ensure the database is not currently in use
Have a backup of the database (if needed)
Have admin privileges
All of the above

Drag and drop the correct SQL keywords to drop a database named 'SchoolDB'.

DROP DATABASE SchoolDB;

What SQL command can confirm the database no longer exists after being dropped?

SHOW TABLES;
SELECT DATABASE;
SHOW DATABASES;
DESCRIBE DATABASES;

Exercise: SQL Backup Database

103 SQL BACKUP DATABASE

Which SQL statement creates a full backup of a database named 'myDB'?

BACKUP DATABASE myDB TO FILE = 'C:\backups\myDB.bak';
BACKUP DATABASE myDB TO DISK = 'C:\backups\myDB.bak';
BACKUP DATABASE myDB WITH DIFFERENTIAL;
SAVE DATABASE myDB TO 'C:\backups\myDB.bak';

What is the purpose of the SQL BACKUP DATABASE statement?

To restore a database from a backup file
To create a backup of an existing database
To delete old database backups
To copy tables from one database to another

What is the primary purpose of a differential backup?

To back up the entire database
To back up only the parts that have changed since the last full backup
To back up the database schema
To create a temporary backup

Which syntax is used to create a differential backup in SQL Server?

BACKUP DATABASE myDB TO DISK = 'D:\myDB.bak' DIFFERENTIAL;
BACKUP DATABASE myDB TO DISK = 'D:\myDB.bak' WITH DIFFERENTIAL;
BACKUP DIFFERENTIAL DATABASE myDB TO DISK = 'D:\myDB.bak';
DIFFERENTIAL BACKUP DATABASE myDB TO DISK = 'D:\myDB.bak';

Drag and drop the correct SQL keywords to back up a database named 'SchoolDB' to the D drive.

BACKUP DATABASE SchoolDB
TO DISK = 'D:\backups\Sch001DB.bak';

Exercise: SQL Create Table

104 SQL CREATE TABLE

What is the primary purpose of the SQL CREATE TABLE statement?

To create a new table in a database
To insert data into a table
To join a table
To delete a table from a database

Which of the following SQL statements will create a table named 'Books' with columns for ID, Title, and Author?

CREATE TABLE Books (ID int, Title varchar(255), Author varchar(255));
CREATE Books TABLE (ID int, Title varchar(255), Author varchar(255));
CREATE TABLE (Books) (ID int, Title varchar(255), Author varchar(255));
CREATE DATABASE TABLE (Books) (ID int, Title varchar(255), Author varchar(255));

Write the correct SQL statement to create a new table called Persons.

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

What does the 'datatype' parameter in the CREATE TABLE statement specify?

The default value of the column
The name of the column
The type of data the column can hold
The primary key for the column

Drag and drop the correct SQL keywords and column names to create a table named 'Customers' with columns 'Name', 'ID', and 'Address'.

CREATE TABLE Customers ( Name varchar(255), ID int, Address varchar(255) ) ;

Exercise: SQL Drop Table

105 SQL DROP TABLE

What does the SQL DROP TABLE statement do?

Deletes a table and all its data from the database
Renames an existing table
Copies a table and its data to a new table

Which of the following SQL statements will delete the table 'Orders'?

DROP Orders;
DELETE TABLE Orders;
DROP TABLE Orders;
TRUNCATE Orders;

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

DROP TABLE Persons;

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

TRUNCATE TABLE Persons;

What will happen if you try to DROP a table that does not exist?

An error will occur.
The query will be ignored without any error.
The table will be created first, then dropped.
It will drop all tables in the database.

Drag and drop the correct keywords to form a statement that deletes the 'Customers' table.

DROP TABLE Customers;

Which of the following SQL statements will delete all data inside the table 'Products' but retain the table structure?

DROP TABLE Products;
DELETE TABLE Products;
TRUNCATE TABLE Products;
REMOVE Products;

Exercise: SQL Alter Table

106 SQL ALTER TABLE

Which SQL statement adds a new column named 'Email' to the 'Employees' table?

ALTER TABLE Employees ADD COLUMN Email varchar(255);
ALTER Employees ADD COLUMN Email varchar(255);
ALTER TABLE Employees ADD Email varchar(255);
MODIFY TABLE Employees ADD COLUMN Email varchar(255);

What does the SQL ALTER TABLE statement allow you to do?

Create a new table in the database
Delete all data from a table
Add, delete, or modify columns in an existing table
Insert data into a table

What will the following SQL statement do?
ALTER TABLE Customers DROP COLUMN Email;

Delete all records in the 'Email' column
Delete the 'Email' column from the 'Customers' table
Change the data type of the 'Email' column
Rename the 'Email' column to 'CustomerEmail'

Add a column of type DATE called Birthday.

ALTER TABLE Persons ADD Birthday DATE;

Delete the column Birthday from the Persons table.

ALTER TABLE Persons DROP COLUMN Birthday;

Which syntax is correct for changing the data type of a column in MySQL?

ALTER TABLE table_name ALTER COLUMN column_name datatype;
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
MODIFY COLUMN column_name datatype;
CHANGE table_name column_name datatype;

Drag and drop the correct keywords to form a statement that deletes the 'DateOfBirth' column from the 'Persons' table.

ALTER TABLE Persons DROP COLUMN DateOfBirth;

How do you rename a column in a table in SQL Server?

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUMN';
ALTER COLUMN table_name old_name TO new_name;
CHANGE COLUMN table_name old_name TO new_name;

Exercise: SQL Constraints

107 SQL Constraints

What is the primary purpose of SQL constraints?

To define relationships between databases
To enforce rules for the data in a table
To create new tables in a database
To manage SQL user permissions

Which constraint ensures that all values in a column are unique?

NOT NULL
PRIMARY KEY
UNIQUE
FOREIGN KEY

What does the CHECK constraint do?

Ensures that a column cannot have a NULL value
Ensures that the values in a column satisfy a specific condition
Sets a default value for a column
Uniquely identifies each row in a table

What is the difference between a column-level constraint and a table-level constraint?

Column-level constraints apply to one column; table-level constraints apply to multiple columns or the whole table
Column-level constraints apply to multiple columns; table-level constraints apply to one column
Column-level constraints are defined using ALTER TABLE; table-level constraints are defined using CREATE TABLE
There is no difference; they are interchangeable

What is the purpose of the DEFAULT constraint?

It prevents NULL values in a column
It provides a default value for a column if no value is specified
It ensures all values in a column are unique
It enforces referential integrity between two tables

Exercise: SQL Not Null

108 SQL NOT NULL

What is the purpose of the SQL NOT NULL constraint?

To ensure a column cannot have duplicate values
To enforce that a column must always contain a value
To define a unique identifier for a table
To prevent a table from being dropped

Drag and drop the correct keywords to enforce NOT NULL constraints on the ID and Name columns.

CREATE TABLE Employees (
ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int
);

What happens if you try to insert a NULL value into a column with a NOT NULL constraint?

The value will be automatically converted to a default value
The database will throw an error
The record will be inserted, but the NULL value will be ignored
The record will be inserted, and the column will be filled with a blank space

How do you define a NOT NULL constraint during table creation?

By adding 'NOT NULL' after the column's data type
By adding 'CHECK NOT NULL' after the column's data type
By using the 'ALTER TABLE' statement
By specifying 'DEFAULT NOT NULL' during table creation

Which of the following is true about NOT NULL constraints?

They allow NULL values only during updates
They prevent a column from having NULL values during both insertion and updates
They are optional and do not enforce any restrictions
They apply only to numeric columns

Exercise: SQL Unique

109 SQL UNIQUE

What does the SQL UNIQUE constraint ensure?

That a column cannot contain NULL values
That all values in a column are different
That a column must always contain a value
That a column is a foreign key

How many UNIQUE constraints can a single table have?

Only one
Multiple
One for each numeric column
Only for columns with a NOT NULL constraint

How do you create a UNIQUE constraint on the 'ID' column in an existing table?

ALTER TABLE Persons ADD UNIQUE ID;
ALTER TABLE Persons ADD CONSTRAINT UNIQUE (ID);
ALTER TABLE Persons ADD UNIQUE (ID);
ALTER TABLE Persons MODIFY COLUMN ID UNIQUE;

Drag and drop the correct keywords to add a named UNIQUE constraint on the 'ID' and 'LastName' columns.

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID, LastName);

What is the difference between a PRIMARY KEY and a UNIQUE constraint?

A PRIMARY KEY allows duplicate values, while UNIQUE does not
A UNIQUE constraint can exist multiple times in a table, while a PRIMARY KEY is limited to one per table
A UNIQUE constraint automatically creates a foreign key
A PRIMARY KEY cannot combine multiple columns, while UNIQUE can

Exercise: SQL Primary Key

110 SQL PRIMARY KEY

True or False:
Primary keys must contain UNIQUE values, and cannot contain NULL values.

True
False

How many PRIMARY KEY constraints can a table have?

Only one per table
One per column
Multiple, as long as they are on different columns
None, unless combined with UNIQUE

How do you add a PRIMARY KEY to the 'ID' column in an existing table?

ALTER TABLE Persons ADD PRIMARY KEY (ID);
ALTER TABLE Persons SET PRIMARY KEY (ID);
ALTER TABLE Persons MODIFY PRIMARY KEY (ID);
ALTER TABLE Persons ADD CONSTRAINT PRIMARY KEY ID;

What happens if you try to add a PRIMARY KEY to a column that contains NULL values?

The operation will succeed, but all NULL values will be replaced with 0
The operation will fail with an error
The operation will succeed, and NULL values will remain
The operation will succeed, but duplicates will be ignored

Exercise: SQL Foreign Key

111 SQL FOREIGN KEY

The FOREIGN KEY constraint is used to:

Perform a comparison between a single column value and a range of other values
Prevent actions that would destroy links between tables
Ensure that a column cannot contain NULL values
Automatically generate values for columns

In a database relationship, what is the table containing the FOREIGN KEY called?

Parent table
Child table
Primary table
Master table

How do you drop a FOREIGN KEY constraint named 'FK_PersonOrder' in SQL Server?

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
DROP FOREIGN KEY Orders.FK_PersonOrder;
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
ALTER TABLE Orders
DELETE FOREIGN KEY FK_PersonOrder;

Can a FOREIGN KEY reference a composite PRIMARY KEY in another table?

Yes, but only in MySQL
No, a FOREIGN KEY can only reference single-column PRIMARY KEYs
Yes, a FOREIGN KEY can reference composite PRIMARY KEYs
No, FOREIGN KEYs and composite PRIMARY KEYs are incompatible

What is a key difference between PRIMARY KEY and FOREIGN KEY?

A PRIMARY KEY can reference another table, while a FOREIGN KEY cannot
A PRIMARY KEY ensures uniqueness within its table, while a FOREIGN KEY links tables
A FOREIGN KEY is always unique, while a PRIMARY KEY can have duplicates
A PRIMARY KEY can be NULL, while a FOREIGN KEY cannot

Exercise: SQL Check

112 SQL CHECK

What is the primary purpose of the SQL CHECK constraint?

To ensure that a column cannot have NULL values
To enforce unique values in a column
To limit the value range that can be placed in a column
To link a column to a primary key in another table

Which SQL statement correctly creates a CHECK constraint on the 'Age' column to ensure it is greater than or equal to 18?

ALTER TABLE Persons ADD CHECK (Age > 18);
ALTER TABLE Persons ADD CHECK (Age >= 18);
ALTER TABLE Persons ADD CONSTRAINT CHECK (Age = 18);
ALTER TABLE Persons ADD CHECK Age >= 18;

Can a CHECK constraint refer to multiple columns in a table?

No, CHECK constraints can only apply to a single column
Yes, but only if the table is newly created
Yes, a CHECK constraint can validate conditions across multiple columns
No, for multiple columns, use a UNIQUE constraint instead

Which SQL statement removes a named CHECK constraint in MySQL?

ALTER TABLE Persons DROP CHECK CHK_Age;
ALTER TABLE Persons REMOVE CONSTRAINT CHK_Age;
ALTER TABLE Persons DELETE CHECK CHK_Age;
ALTER TABLE Persons DROP CONSTRAINT CHK_Age;

Exercise: SQL Default

113 SQL DEFAULT

What is the primary purpose of the SQL DEFAULT constraint?

To ensure all values in a column are unique
To set a default value for a column when no value is specified
To limit the range of values in a column
To prevent NULL values in a column

How do you add a DEFAULT constraint to an existing table in SQL Server?

ALTER TABLE Persons ADD DEFAULT 'Sandnes' TO City;
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
ALTER TABLE Persons MODIFY City SET DEFAULT 'Sandnes';
ALTER TABLE Persons ALTER COLUMN City DEFAULT 'Sandnes';

Which SQL statement drops a DEFAULT constraint in MySQL?

ALTER TABLE Persons DROP DEFAULT ON City;
ALTER TABLE Persons ALTER City REMOVE DEFAULT;
ALTER TABLE Persons ALTER City DROP DEFAULT;
ALTER TABLE Persons DELETE DEFAULT City;

What happens when a new row is inserted into a table with a column that has a DEFAULT constraint and no value is provided for that column?

The operation fails
The column is left NULL
The column gets the default value
The column inherits the value from the previous row

Exercise: SQL Create Index

114 SQL CREATE INDEX

What is the purpose of the SQL CREATE INDEX statement?

To create a backup of a table
To retrieve data more quickly by creating indexes
To ensure data integrity in a table
To delete an existing index in a table

How can you create a unique index on the 'ID' column in the 'Persons' table?

CREATE INDEX idx_id ON Persons (ID);
CREATE INDEX UNIQUE idx_id ON Persons (ID);
CREATE UNIQUE INDEX idx_id ON Persons (ID);
CREATE INDEX idx_id UNIQUE ON Persons (ID);

What is the correct syntax to create an index on both the 'LastName' and 'FirstName' columns?

CREATE INDEX idx_fullname ON Persons (LastName, FirstName);
CREATE INDEX idx_fullname ON (LastName, FirstName) IN Persons;
CREATE UNIQUE INDEX idx_fullname ON Persons (LastName AND FirstName);
CREATE INDEX idx_fullname ON Persons {LastName, FirstName};

Which SQL statement is used to delete an index in a table?

DROP INDEX
DROP ALTER
ALTER TABLE
DELETE INDEX

What happens when you update a table with indexes?

The table updates faster because of the index
The index does not affect the update process
The update takes longer because the index also needs to be updated
Indexes are temporarily removed during updates

Exercise: SQL Auto Increment

115 SQL AUTO INCREMENT

What is the primary purpose of an AUTO_INCREMENT field in SQL?

To allow duplicate values in a column
To automatically generate a unique number for a column
To reset column values after deletion
To format column data as text

Which keyword is used to implement auto-increment in MySQL?

IDENTITY
SERIAL
AUTO_INCREMENT
AUTOINCREMENT

Which keyword is used to implement auto-increment in SQL Server?

IDENTITY
SERIAL
AUTO_INCREMENT
AUTOINCREMENT

Which of the following is the correct format for the DATE data type?

YYYY/MM/DD
MM-DD-YYYY
YYYY-MM-DD
DD-MM-YYYY

Which SQL data type is used to store both date and time?

DATE
DATETIME
TIMEDATE
YEAR

Drag and drop the correct column name to filter records with the OrderDate of 2008-11-11.

SELECT * FROM Orders WHERE OrderDate = 2008-11-11;

Exercise: SQL View

117 SQL Views

What is an SQL view?

A real table stored in the database
A virtual table based on the result-set of an SQL statement
A function that modifies data in the database
A temporary table used for backup purposes

Which SQL statement is used to create a view?

CREATE TABLE
CREATE VIEW
CREATE FUNCTION
CREATE DATABASE

What happens when you query a view?

The database retrieves pre-stored static data
The database engine recreates the view with up-to-date data
The view is converted into a real table
The query is ignored if the view is not indexed

Which SQL statement adds or modifies the columns of an existing view?

ALTER VIEW
UPDATE VIEW
CREATE OR REPLACE VIEW
MODIFY VIEW

What is the correct syntax to drop a view?

DELETE VIEW view_name;
DROP VIEW view_name;
REMOVE VIEW view_name;
TRUNCATE VIEW view_name;

Drag and drop the correct keywords to make sense of the following syntax code:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Exercise: SQL Injection

118 SQL Injection

What is SQL injection?

A technique to optimize SQL queries
A code injection technique to access or destroy a database
A method to protect SQL queries from being hacked
A debugging process for SQL queries

What does the statement 1=1 in a SQL query imply?

A logical statement that is always true
A conditional operator for database sorting
To copy data from one table into a new table
To create an index on a table

Which approach helps prevent SQL injection attacks?

Using concatenated SQL strings
Using the NULL operator
Using SQL parameters
Allowing multiple SQL statements in a query

Drag and drop the correct parameterized placeholder to complete the SQL query.

SELECT * FROM Customers WHERE CustomerId = @0;

Exercise: SQL Hosting

Which of the following is a popular SQL database software for high-traffic, database-driven websites?

MS SQL Server
Oracle
MySQL
All of the above

Which SQL database is an inexpensive alternative to Microsoft and Oracle solutions?

MS Access
MySQL
MS SQL Server
Oracle

Which SQL database is suitable for web sites with simple database requirements?

Oracle
MS SQL Server
MySQL
MS Access

Exercise: SQL Data Types

119 SQL Data Types

Which of the following is NOT a string data type in MySQL?

VARCHAR
TEXT
TIMESTAMP
CHAR

What is the maximum size of a VARCHAR column in MySQL?

255
65535
16777215
4GB

Drag and drop the correct MySQL data types for floating point numbers.

A floating point number can be defined using FLOAT or DOUBLE.