3.7.2 Structured Query Langauage (SQL)

• Structured Query Language (SQL) is a language that was developed as a method for creating, selecting, inserting, editing(updating) and deleting tables, records and fields within a relational database.
• 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.
• SQL is a standardized query language for requesting information from a database.
• Query is another word for questioning the Database using search criteria.
• The Customer, Orders and Books tables in the Book_Store database will be used to demonstrate the main SQL statements.
• The most commonly used SQL statements include:
  • SELECT
    • FROM
    • WHERE
    • ORDER BY
  • INSERT
  • UPDATE
  • DELETE

• 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.

• EXAMPLE 1: Let's look at our 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:

• The Book_Store database consists of three tables, with relational databases, we might want to select from multiple tables.
• For example, we might want to find the names and phone numbers of people who had previously bought a book in 2020.
• Using SQL, we can combine data from two or more tables by specifying which data the table is held in.
• When more than one table is involved, SQL uses the syntax tablename.tablefield to specify the field. However, the table name is optional unless the field name appears in more than one table.

Customer Table:

Books Table:

Orders Table:

• EXAMPLE 1: We will write an SQL query to find the Title, Initial, Surname and Phone number of each person, together with the date customer bought a book during 2020.
• We need to query the tables Customer and Orders to find the matching OrderDate >= 01/01/2020. These two tables are linked by a primary key CustID in the Customer table and a foreign key CustID in the Orders table. This kind of query is called inner join.

SELECT Customer.Title, Initial, Surname, Phone, Orders.CustID, OrderDate
      FROM Customer INNER JOIN Orders ON Customer.[CustID] = Orders.[CustID]
      WHERE Orders.OrderDate >= #1/1/2020#;      
      ORDER BY Surname ASC

• This SQL query will return the following three records:

• Example 1: To insert a new record into an existing table we use the SQL INSERT INTO statement. The syntax is:

INSERT INTO tableName (column1, column2,...)
VALUES (value1, value2,...)

• For example, to insert a record for a new customer, Mrs T Rush, 78 Oak Ave, Peterborough, 01733873194, 19/04/1974

INSERT INTO Customer(CustID, Title, Initial, Surname, Address, City, Phone, DoB)
       VALUES(C005, "Mrs", "T", "Rush", "78 Oak Ave", "Pboro", "01733873194", "19/04/1974");

• Example 2: To insert a record for a new book, C456, You Care Too Much, Carl Vernon, Self Help, 4, 07/01/2019:

INSERT INTO Books(BookID, Title, Author, Genre, Rating, PublishDate)
        VALUES(C456, "You Care Too Much", "Self Help", "4", "07/01/2019");

• To update an existing record's attribute(s) that meeting a specified condition, we use the SQL UPDATE statement. The syntax is:

UPDATE tableName
SET column1 = value1, column2 = value2, ...
WHERE column = value

• Example: Update the record of CustID C001, Mrs Flowers, who has changed her address to 63 River Way, and her phone number to 01778 886210.

UPDATE Customer
       SET Address = "63 River Way", Phone = "01778 886210"
       WHERE CustID = "C001"

• To delete an existing record from a table that meeting a specified condition, we use the SQL DELETE statement. The syntax is:

DELETE FROM tablename
WHERE column = value

• Example: Delete the record for CustID C004, Ms, P, Jones.

DELETE FROM Customer
WHERE CustID = C004