Basic SQL Statements

Question 0

What is SQL?

Answer 0

SQL is an ANSI (American National Standard Institute) standard. It is a standard for accessing and manipulating databases.

Question 1

What does SQL stand for?

Answer 1

Standard Query Language.

Question 2

What can SQL do?

Answer 2

It can:

   - execute queries against a 
     database
   - retrieve data from a database
   - insert records in a database
   - update records in a database
   - delete records from a database
   - create new database
   - create new tables in a database
   - create stored procedures in a 
      database
   - create views in a database
   - set permissions on tables,
     procedures & views

Question 3

What is a Database Table?

Answer 3

A database most often contains one or more tables. Each table is identified by a name (e.g. “Customer” or “Order”). Tables contain records (rows) with data.

Question 4

What is the syntax for the SELECT statement?

Answer 4

SELECT column_name,column_name
FROM table_name;

Ex.
SELECT * FROM table_name;

•This means to show all records table name•

Question 5

What is the SELECT statement used for?

Answer 5

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

The result is stored in a result table, called the result-set.

Question 6

What is the syntax for the SELECT DISTINCT statement?

Answer 6

SELECT DISTINCT column_name,column_name
FROM table_name;

Ex.
SELECT DISTINCT City FROM Customers;

•This means to selects only the distinct values from the “City” columns from the “Customers” table•

Question 7

What is the SELECT DISTINCT statement used for?

Answer 7

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

Question 8

What is the syntax for the WHERE clause statement?

Answer 8

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

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

•This means to selects all the customers from the country “Mexico”, in the “Customers” table•

SQL requires single quotes around text values (most database systems will also allow double quotes).

Question 9

What is the syntax for the WHERE clause statement for numerical field?

Answer 9

SELECT * FROM Customers
WHERE CustomerID=1;

••This means to selects all the customers with 1 for a customer ID in the “Customers” table•

Numeric fields should not be enclosed in quotes.

Question 10

What are the operators in the WHERE clause?

Answer 10

= Equal

<> Not equal. Note: In some
versions of SQL this operator
may be written as !=

>

Greater than

< Less than

> = Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive
range

LIKE Search for a pattern

IN To specify multiple possible.
values for a column

Question 11

What is the the statement for the AND operator?

Answer 11

SELECT * FROM Customers
WHERE Country=’Germany’
AND City=’Berlin’;

•This statement selects all customers from the country “Germany” AND the city “Berlin”, in the “Customers” table•

Question 12

What is the the statement for the OR operator?

Answer 12

SELECT * FROM Customers
WHERE City=’Berlin’
OR City=’München’;

•This statement selects all customers from the city “Berlin” OR “München”, in the “Customers” table•

Question 13

What is the the statement for the AND & OR operator?

Answer 13

SELECT * FROM Customers
WHERE Country=’Germany’
AND (City=’Berlin’ OR City=’München’);

• This statement selects all customers from the country “Germany” AND the city must be equal to “Berlin” OR “München”, in the “Customers” table•

You can also combine AND and OR (use parenthesis to form complex expressions).

Question 14

What is the syntax for the ORDER BY keyword statement with the DESC keyword?

Answer 14

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name DESC;

Ex.
SELECT * FROM Customers
ORDER BY Country DESC;

• This statement selects all customers from the “Customers” table, sorted DESCENDING by the “Country” column•

Question 15

What is the syntax for the ORDER BY statement keyword?

Answer 15

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC;

Ex.
SELECT * FROM Customers
ORDER BY Country;

•This statement selects all customers from the “Customers” table, sorted by the “Country” column•

Question 16

What is the ORDER BY keyword statement for combining multiple columns?

Answer 16

SELECT * FROM Customers
ORDER BY Country,CustomerName;

•This statement selects all customers from the “Customers” table, sorted by the “Country” and the “CustomerName” column•

Question 17

What is the ORDER BY keyword statement used for?

Answer 17

The ORDER BY keyword is used to sort the result-set by one or more columns.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.

Question 18

What is the WHERE clause used for?

Answer 18

The WHERE clause is used to extract only those records that fulfill a specified criterion.

Question 19

What are the AND & OR operators used for?

Answer 19

The AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

Question 20

What is the INSERT INTO statement used for?

Answer 20

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

Question 21

What is one of the two ways to write the INSERT INTO statement?

Answer 21

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

The first form does not specify the column names where the data will be inserted, only their values:

Ex.
INSERT INTO Customers (CustomerName, City, Country)
VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);

The following SQL statement will insert a new row, but only insert data in the “CustomerName”, “City”, and “Country” columns (and the CustomerID field will of course also be updated automatically):

Question 22

What is the other way to write the INSERT INTO statement?

Answer 22

INSERT INTO table_name (column1,column2,column3,…)
VALUES (value1,value2,value3,…);

The second form specifies both the column names and the values to be inserted:

Ex.
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’,’Tom B. Erichsen’,’Skagen 21’,’Stavanger’,’4006’,’Norway’);

This statement inserts a new row in the “Customers” table.

Question 24

What are the SQL statements that QA does not do but should have knowledge of?

Answer 24

INSERT INTO, UPDATE & DELETE.

Question 25

What is the syntax for the UPDATE statement?

Answer 25

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

Notice the WHERE clause in the SQL UPDATE statement! The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Question 26

What is the UPDATE statement used for?

Answer 26

The UPDATE statement is used to update existing records in a table.

Question 27

What is the syntax for the UPDATE statement keyword?

Answer 27

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

Ex.
UPDATE Customers
SET ContactName=’Alfred Schmidt’, City=’Hamburg’
WHERE CustomerName=’Alfreds Futterkiste’;

This statement update the customer “Alfreds Futterkiste” with a new contact person and city.

WARNING: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Question 28

What is the DELETE statement used for?

Answer 28

The DELETE statement is used to delete records in a table.

Question 29

What is the syntax for the DELETE statement keyword?

Answer 29

DELETE FROM table_name
WHERE some_column=some_value;

Ex.
DELETE FROM Customers
WHERE CustomerName=’Alfreds Futterkiste’ AND ContactName=’Maria Anders’;

WARNING: The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

THIS STATEMENT CANNOT BE UNDONE!