SQL Syntax

Question 1

The different parts of a SQL statement

Answer 1

A clause

Question 2

Basic 2 elements to get data from a database

Answer 2

SELECT column1, column2,

FROM table_name;

Question 3

Select all the DIFFERENT values from the Country column in the Customers table

Answer 3

SELECT DISTINCT Country

FROM Customers;

Question 4

The number of different (distinct) customer countries (different countries in the customer table)

Answer 4

SELECT COUNT(DISTINCT Country)
FROM Customers;

Question 5

Conditions

All records where City Column has value “Berlin”

Answer 5

SELECT * FROM Customers

WHERE City = ‘Berlin’;

Question 6

Select all records where City is NOT “Berlin”

Answer 6

SELECT * FROM Customers

WHERE NOT City = ‘Berlin’;

Question 7

Sort result set in ascending or descending order

Answer 7

SELECT column1, column2
FROM table_name
ORDER BY column1, column2 DESC;

Ascending order by default

Question 8

Test for NULL values

Answer 8

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

or IS NOT NULL;

Question 9

Specify a number of records to return

Answer 9

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

This varies greatly across DBMS, including LIMIT, FETCH FIRST, and ROWNUM

Question 10

What are the most popular DBMS?

Answer 10

MySQL, Oracle, Microsoft SQL Server, PostgreSQL, SQLite, MongoDB

Question 11

Smallest or largest value in a selected column

Answer 11

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Question 12

What are some functions and where would you find them?

Answer 12

AVG(), SUM(), COUNT(), MIN(), MAX()
Put them in the select clause
SELECT SUM(Quantity)

Question 13

Used in the WHERE clause to search for a specified pattern in a column
Give an example

Answer 13

Like
WHERE CustomerName LIKE ‘a%’
Will find any values that start with “a”

Question 14

Multiple values in a WHERE clause

Answer 14

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

Also works with NOT IN

Question 15

Selects values within a given range

Answer 15

SELECT * FROM Products

WHERE Price NOT BETWEEN 10 AND 20;

Question 16

How do you create an alias to make column names more readable

Answer 16

AS
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

Question 17

What is the syntax for an inner join

Answer 17

ON

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

Question 18

What is the syntax for left join

Answer 18

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;