SQL

Question 1

SQL is a standard language for _____________ in databases.

Answer 1

storing, manipulating and retrieving data

Question 2

SQL stands for ___________

Answer 2

Structured Query Language

Question 3

RDBMS stands for _________________

Answer 3

Relational Database Management System.

Question 4

Most of the actions you need to perform on a database are done with ____________

Answer 4

SQL statements.
SELECT * FROM Customers;

Question 5

___ - extracts data from a database
____ - updates data in a database
____ - deletes data from a database
______ - inserts new data into a database
______ - creates a new database

Answer 5

SELECT
UPDATE
DELETE
INSERT INTO
CREATE DATABASE

Question 6

___ - modifies a database
____ - creates a new table
____ - modifies a table
____ - deletes a table
____ - creates an index (search key)
_______ - deletes an index

Answer 6

ALTER DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX

Question 7

The _____ statement is used to select data from a database.

Answer 7

SELECT

Question 8

Syntax of Select:

Answer 8

SELECT column1, column2, …
FROM table_name;
SELECT CustomerName, City FROM Customers;

Question 9

If you want to return all columns, without specifying every column name, you can use the ______ syntax

Answer 9

SELECT *
SELECT * FROM Customers;

Question 10

The ________statement is used to return only distinct (different) values.

Answer 10

SELECT DISTINCT

Question 11

The ____ clause is used to filter records.

Answer 11

WHERE
SELECT * FROM Customers
WHERE Country=’Mexico’;

Question 12

The ____ BY keyword is used to sort the result-set in ascending or descending order.

Answer 12

ORDER
SELECT * FROM Products
ORDER BY Price;

Question 13

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the ___ keyword.

Answer 13

DESC
SELECT * FROM Products
ORDER BY Price DESC;

Question 14

For ____ values the ORDER BY keyword will order alphabetically:

Answer 14

string

Question 15

To sort the table reverse alphabetically, use the ___ keyword:

Answer 15

DESC
SELECT * FROM Products
ORDER BY ProductName DESC;

Question 16

Using Both ASC and DESC:

Answer 16

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Question 17

The WHERE clause can contain one or many ____ operators.

Answer 17

AND/OR

Question 18

The AND operator is used to ____ records based on more than one condition, like if you want to return all customers from Spain that starts with the letter ‘G’:

Answer 18

filter

SELECT * FROM Customers
WHERE Country = ‘Spain’ AND CustomerName LIKE ‘G%’;

Question 19

OR operator syntax

Answer 19

SELECT *FROM Customers
WHERE Country = ‘Germany’ OR Country = ‘Spain’;

Question 20

The ____ operator is used in combination with other operators to give the opposite result, also called the negative result.

Answer 20

NOT

SELECT * FROM Customers
WHERE NOT Country = ‘Spain’;

Question 21

Select customers that does not start with the letter ‘A’

Answer 21

SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘A%’;

Question 22

Select customers with a customerID not between 10 and 60:

Answer 22

SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;

Question 23

Select customers that are not from Paris or London

Answer 23

SELECT * FROM Customers
WHERE City NOT IN (‘Paris’, ‘London’);

Question 24

Select customers with a CustomerId not greater than 50:

Answer 24

SELECT * FROM Customers
WHERE NOT CustomerID > 50;

Question 25

Select customers with a CustomerID not less than 50:

Answer 25

SELECT * FROM Customers
WHERE NOT CustomerId < 50;

Question 26

The ________ statement is used to insert new records in a table.

Answer 26

INSERT INTO Customers (CustomerName, ContactName)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’);

Question 27

The ______ operator is used to test for empty values (NULL values).

Answer 27

IS NULL

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

Question 28

The _________ operator is used to test for non-empty values (NOT NULL values).

Answer 28

IS NOT NULL

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

Question 29

The _____ statement is used to modify the existing records in a table.

Answer 29

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

Question 30

Be careful when updating records. If you omit the ____ clause, ALL records will be updated!

Answer 30

WHERE

Question 31

The _____ statement is used to delete existing records in a table.

Answer 31

DELETE
DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName=’Khan anas’;

Question 32

SQL statement deletes all rows in the “Customers” table, without deleting the table:

Answer 32

DELETE FROM Customers;

Question 33

To delete the table completely, use the _______ statement:

Answer 33

DROP TABLE

DROP TABLE Customers;

Question 34

The ___________ clause is used to specify the number of records to return.

Answer 34

SELECT TOP

SELECT TOP 3 * FROM Customers;

Question 35

MySQL Syntax:

Answer 35

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Question 36

Select the first 3 records of the Customers table: (MySQL)

Answer 36

SELECT * FROM Customers LIMIT 3;

Question 37

Oracle 12 Syntax:

Answer 37

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;

Question 38

Select the first 3 records of the Customers table: (Oracle)

Answer 38

SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;

Question 39

SQL Server / MS Access Syntax:

Answer 39

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

Question 40

The SQL statement selects the first 50% of the records from the “Customers” table: (for SQL Server/MS Access):

Answer 40

SELECT TOP 50 PERCENT * FROM Customers;

Question 41

The SQL statement selects the first three records from the “Customers” table, where the country is “Germany” (for SQL Server/MS Access):

Answer 41

SELECT TOP 3 * FROM Customers
WHERE Country=’Germany’;

Question 42

Add the ___________ keyword when you want to sort the result, and return the first 3 records of the sorted result.

For SQL Server and MS Access:

Answer 42

ORDER BY
SELECT TOP 3 * FROM Customers
ORDER BY CustomerName DESC;

Question 43

Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result.

For MySQL:

Answer 43

SELECT * FROM Customers
ORDER BY CustomerName DESC
LIMIT 3;

Question 44

Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result.

For oracle:

Answer 44

SELECT * FROM Customers
ORDER BY CustomerName DESC
FETCH FIRST 3 ROWS ONLY;

Question 45

An aggregate function is a function that performs a ________ on a set of values, and returns a ______ value.

Answer 45

calculation
single

Question 46

The most commonly used SQL aggregate functions are:

MIN() - returns the ____ value within the selected column
MAX() - returns the _______ value within the selected column
COUNT() - returns the ________
SUM() - returns the _______
AVG() - returns the _________ of a numerical column

Answer 46

smallest
largest
number of rows in a set
total sum of a numerical column
average value

Question 47

Find the lowest price in the Price column:

Answer 47

SELECT MIN(Price)
FROM Products;

Question 48

Find the highest price in the Price column:

Answer 48

SELECT MAX(Price)
FROM Products;

Question 49

To give the column a descriptive name, use the ___ keyword:

Answer 49

AS
SELECT MIN(Price) AS SmallestPrice
FROM Products;

output:
SmallestPrice
2.5