1. Get data from the database.

The easiest way to verify if a person knows the basics of SQL is to ask them to retrieve data from a database.

Solution

Given three tables:  customers ,  products  and  orders  you need to create queries which retrieve all rows from them. Listing 1.1 presents simple  SELECT  statements.

Listing 1.1 – Example of simple  SELECT  statements.

SELECT * FROM customers;

SELECT * FROM products;

SELECT * FROM orders;

Please notice that the wildcard ( * ) causes all columns to be retrieved. In many cases, it is necessary to retrieve only part of the data. Listing 1.2 presents an example  SELECT  statement which retrieves only the first and last name of a customer.

Listing 1.2 – The  SELECT  statement which gets specified columns.

SELECT first_name, last_name FROM customers;

2. Get data from the database using a conditional statement.

Preparing conditional statements is one of the necessary skills every programmer must have. A developer needs to create queries which return only those records that fulfill a specified condition. To retrieve filtered data, the  WHERE  clause combined with  AND ,  OR  and  NOT  operators should be used.

WHERE

First, you are asked to prepare a query which returns all customers which are from the USA.

Listing 2.1 – Example of  SELECT  statement with  WHERE  clause.

SELECT * FROM customers

WHERE country = 'USA';

OR

Second, you are asked to prepare a query which returns all customers which are from the USA or Canada.

Listing 2.2 – Example of  SELECT  statement with  OR  operator.

SELECT * FROM customers

WHERE country = 'USA' OR country = 'Canada';

AND

Finally, the last most basic operator. You need to prepare a query which returns all products from supplier ‘Brandon’ and price lower than $20.

Listing 2.3 – Example of  SELECT  statement with  AND  operator.

SELECT * FROM products

WHERE supplier = 'Brandon' AND price < 20;

3. Get data from the database using the IN operator.

The  IN  operator is very often used in  SELECT  statements. The  IN  operator can be seen as shorthand for multiple  OR  conditions, but it can also take the results from other  SELECT  queries as input.

List of values

You are asked to prepare a query which returns all customers which are from the following list of countries:

- USA,

- Canada,

- Australia,

- Great Britain,

- New Zealand.

Listing 3.1 presents the correct implementation of such a query.

Listing 3.1 – Example of  SELECT  statement with  IN  operator.

SELECT * FROM customers

WHERE country IN ('USA', 'Canada', 'Australia' , 'Great Britain', 'New Zealand');

Subquery

Another way to use the  IN  clause is to pass a list of values by selecting data from another table. For example, you may be asked to retrieve products which were sold in quantities higher than 100. Listing 3.2 presents an example of such a query. From the  order_items  table, you retrieve a list of products identifiers which sold more than 100 items in one order. Next, such a list is passed to the  IN  clause. Finally, the  SELECT  statement returns all products which match previously selected identifiers.

Listing 3.2 – Example of subquery.

SELECT * FROM products

WHERE id IN (SELECT product_id FROM order_items

WHERE quantity > 100);

Contents

1. Cover

2. Title

3. Colofon

4. Preface

5. Data-set

6. 1. Get data from the database.

7. 2. Get data from the database using a conditional statement.

8. 3. Get data from the database using the IN operator.

9. 4. Save data in the database.

10. 5. Modify data in the database.

11. 6. Remove data from the database.

12. 7. Find rows which values are NULL.

13. 8. Get list of unique values from table column.

14. 9. Sort results.

15. 10. Get results based on text pattern.

16. 11. Create and use an index.

17. 12. Create and use composite index.

18. 13. Prepare inner, left outer, right outer, and full outer table joins.

19. 14. Download aggregated data from the database.

20. 15. Download aggregated results grouped by column.

21. 16. Use a function in SQL query.

22. 17. Create a table.

23. 18. Modify database schema.

24. 19. Reference integrity.

25. 20. Design one-to-many relationship.

26. 21. Design many-to-many relationship.

27. 22. Copy data from one table to another table.

28. 23. Combine the results of more than one SELECT statement.

29. 24. Create and execute a stored procedure.

30. 25. Create a view and get data from it.

31. 26. Create and use a partition.

32. 27. Create a query which allows pagination.

33. 28. Create and use a sequence.

34. 29. Create a database trigger.

35. 30. Define a transaction.

Table of Contents

1. Get data from the database.

2. Get data from the database using a conditional statement.

3. Get data from the database using the IN operator.