SQL Terms

Question 1

SELECT DISTINCT

Answer 1

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Country FROM Customers;

SELECT COUNT(DISTINCT Country) FROM Customers;

Question 2

Example of SQL AND, OR and NOT Operators

Answer 2

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Question 3

The SQL ORDER BY Keyword

Answer 3

SELECT * FROM Customers
ORDER BY Country DESC;

Question 4

The SQL SELECT LIMIT Clause

Answer 4

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

Question 5

The SQL MIN() and MAX() Functions

Answer 5

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

SELECT MAX(Price) AS LargestPrice
FROM Products;

Question 6

The SQL COUNT(), AVG() and SUM() Functions

Answer 6

The COUNT() function returns the number of rows that matches a specified criterion.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

SELECT COUNT(ProductID)
FROM Products;

SELECT AVG(Price)
FROM Products;

Question 7

The SQL LIKE Operator

Answer 7

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

E.g The following SQL statement selects all customers with a CustomerName starting with “a”:

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

Question 8

The SQL IN Operator

Answer 8

The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
~~~
SELECT * FROM Customers
WHERE Country IN (‘Germany’, ‘France’, ‘UK’);

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

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

Question 9

The SQL BETWEEN Operator

Answer 9

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

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

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

Question 10

SQL Aliases

Answer 10

SQL aliases are used to give a table, or a column in a table, a temporary name.
An alias is created with the AS keyword.

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

Question 11

Different Types of SQL JOINs

Answer 11

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

SELF JOIN: A self join is a regular join, but the table is joined with itself.

Question 12

SQL INNER JOIN Keyword

Answer 12

The INNER JOIN keyword selects records that have matching values in both tables.

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

Question 13

SQL SELF JOIN Keyword

Answer 13

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

Question 14

SQL UNION Operator

Answer 14

The UNION operator is used to combine the result-set of two or more SELECT statements.

Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order

E.g The following SQL statement returns 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;

Question 15

What are the different subsets of SQL?

Answer 15

Data Definition Language (DDL) – It allows you to perform various operations on the database such as CREATE, ALTER, and DELETE objects.

Data Manipulation Language(DML) – It allows you to access and manipulate data.

Data Control Language(DCL) – It allows you to control access to the database. E.g – Grant, Revoke access permissions.