Task: Learning Journal

Open your Learning Journal by clicking on the image below

Good notes will help you organise and process data and information

Using SQL we can perform various operations on data in out database tables.

We can, for instance, SELECT, INSERT, DELETE and UPDATE tables, records and fields.

Query is another word for questioning the Database using search criteria.

The most commonly used SQL statements include:

• SELECT
  • FROM
  • WHERE
  • ORDER BY
• INSERT
• UPDATE
• DELETE

SELECT .. FROM

• The SELECT statement is used to retrieve(Extract) a collection of fields from one or more tables in a database.
• The syntax is:
• SELECT will retrieve data(List of Fields) from one or more tables.
• SELECT has optional keywords such as:
  • FROM to list the table(s) where the data is to be retrieved from.
  • WHERE to allow selection of data using search criteria.
  • ORDER BY lists the column(fields) that the results are to be sorted on, either Ascending or Descending(default is Ascending).
• The asterisk (*) is also important in writing SQL statements, indicating that all columns(Fields) should be selected.

Retrieving Data from a Relational Database

Simple: SQL- SELECT

• Lets do some SQL by working through the following SQL Select Tutorial using theW3 Schools SQL link below :

SELECT .. FROM .. WHERE

• EXAMPLE 1: Let's look at a Customer table example.

• A valid SQL statement to select All People in the Customer table who was born between 01/01/1960 and 01/01/2000 and displaying the results in descending order of Surname would be:

SELECT *
       FROM Customer
       WHERE DoB BETWEEN #01/01/1960# AND #01/01/2000#
       ORDER BY Surname DESC;

• This SQL query will return the following records:

• EXAMPLE 2: In this example we will use the Books table to select BookID, Title, Genre, Rating and PublishDate with a rating of 5 or above or with a publish date after 1970 and displaying the results in ascending order of Title would be:

SELECT BookID, Title, Genre, Rating, PublishDate
       FROM Books
       WHERE Rating > 5 OR PublishDate >= #01/01/1970#
       ORDER BY Title ASC;

• This SQL query will return the following records:

>

Standard SQL Operators

• The standard operators used in SQL are shown below.

SQL- SELECT FROM WHERE

• Lets do some more SQL by working through the following SQL SELECT FROM WHERE Tutorial using theW3 Schools SQL link below then complete the Exercises:

When you have completed the Exercises go on to the SQL AND, OR, NOT and ORDER BY tutorials and complete the exercises. Then attempt to complete the questions below.

Questions: SQL- SELECT

• Try practising your SQL skills by working through the following SQL Select Questions using theW3 Schools SQL link below :

1. Write a Query that selects all the columns(fields) from the Employees table.
2. Write a statement that will select the City column from the Customers table.
3. Select all records from the Customers table where the City column has the value "Berlin".
4. Select all records from the Customers table where the CustomerID column has the value 32
5. Select all records from the Customers table where the City column has the value 'Berlin' and the PostalCode column has the value 12209.
6. Select all records from the Customers table where the City column has the value 'Berlin' or 'London'.
7. Select the city column from the Customers table, sort the result alphabetically by the column City.
8. Select the city column from the Customers table, sort the result reversed alphabetically by the column City.
9. Select the name,address,country and city fileds from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City.
10. Create a Query that only selects the ProductID 11 from the OrderDetails database. How many orders are there?
11. Show all of the suppliers that are based in the UK. How many records are there?