What is SQL?

SQL (sometimes pronounced SEQUEL) stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.

Table Basics

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their name and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather". The values city, state, high, and low are the columns. The rows contain the data for this table:

Our example - Library system

We have built a library database with the following tables: book, bookcopy, student, loan, author and authorship.

The tables are populated and we will use this data in our queries. The schema is stored in library_script.sql.

• Open MySQL Workbench and log in using password root.

To run the script:

• Under the File menu, choose Run SQL Script ….
• When the dialog box opens, select library_script.sql.
• Click the button Open and on the next dialog box, click Run.
• When the Operation has run successfully, click Close.

Alternatively:

• Choose Open SQL Script ….
• When the dialog box opens, select library_script.sql.
• Click the button Open. This will open the script in a new tab.
• To execute the script, choose the third option on the window.

Describing Data

Before you work with a database you must specify the database (by name) that you want to work with.

Enter the following command:

USE LIBRARY;

We have already set up tables for our library system. If you want to see their structure, use:

DESCRIBE 'tablename';

Or

DESC 'tablename';

Example

DESC book;

This will return a description of the book table including field names and their corresponding data types.

To Do:

Now, describe the other tables.

The SELECT statement

The select statement is used to query the database and retrieve selected data that match the criteria that you specify. The data returned is stored in a result table, called the result-set. Here is the format of a simple select statement:

  SELECT [DISTINCT|ALL]{*|[columnExpression [AS newName]][,...]}
  FROM Tablename [alias] 
  [JOIN Tablename ON ...]
  [WHERE condition]
  [GROUP BY columnlist][HAVING condition]
  [ORDER BY columnList];

Note: [] = optional

• columnExpression represents a column name or an expression.
• TableName is the name of an existing database table or view that you have access to, and alias is an optional abbreviation for TableName.
• The sequence of processing in a SELECCT statement is:

Simple Select examples

As we know, the select statement is used to query the database and retrieve selected data that match the criteria that you specify.

Conditional selections used in the where clause:

Examples

SELECT * 
FROM book;

This will return all records with all columns included.

SELECT title, publisher 
FROM book;

This will return a subset of columns. This query will return all records with just 2 columns (title and publisher) included.

SELECT title 
FROM book 
WHERE publisher = 'Addison Wesley';

This will return a subset of records only. Records will only be selected if the publisher value is equal to Addison Wesley. When these records are selected (in memory) the column title is then returned.

Practice: Try out all the examples yourself

Enter the following sample select statements, one at a time to see what is returned:

SELECT * 
FROM student; 

SELECT fname, lname 
FROM student; 

SELECT * 
FROM student 
WHERE county = 'Waterford';

Exercises

1. Display the full name and full address for every student that's in the table.

   

2. Display the first name, last name, and county for everyone that's not from Waterford.

   

3. Display all columns for every student that is in first year.

   

More on Select

To change the column heading returned, we can use AS:

SELECT fname AS 'First Name', lname AS 'Last Name' FROM student;

OR

SELECT fname 'First Name', lname 'Last Name' FROM student;

To concatenate (join) two (or more) columns together use CONCAT as follows:

SELECT concat(fname, ' ', lname) AS Name FROM Student;

The result of concatenating two character strings is another character string. With the concat feature, we are usually joining fieldnames and separation characters (such as a hyphen (-) or a colon (:) or a space ( )). Use a comma to separate each element.

Distinct

Enter the following:

SELECT publisher FROM book;

This returns the publisher for each book record, so you will see values repeated. But if we only want to know the list of publishers that we are using at any one time, we do not want to see for example Addison Wesley more than once.

To eliminate duplicates, use the keyword DISTINCT. Now enter the following:

SELECT DISTINCT publisher FROM book;

Notice the difference in the number of returned values.

Exercises

1. Display the title of all books that are categorised as Computing. Output the result as Computing Titles.

   

2. Display the counties that the students come from. Output the result as Student County. List each county only once.

   

3. Display the student id, first name and last name for all students that are on course wd155. Concatenate first name and last name together and output the value as Student Name.

   

4. Return the first name, last name and Street for all Students from Tramore. Output the student name concatenated and labelled as Name.

   

5. Retrieve the titles of all books published by Sitepoint. Label the returned titles as 'Sitepoint books'.

   

Pattern Matching

Sometimes we do not always have the exact search criteria we need.
Maybe we need to find some books with JavaScript in the title. If we enter:

SELECT title FROM book WHERE title = 'JavaScript';

This will only return books with the title equal to JavaScript, so titles like JavaScript-The Guide or Learning JavaScript would not be returned.

So we need to broaden our search criteria so that it includes the term JavaScript and is preceded by and/or followed by other term(s). To do this we use Pattern Matching with the LIKE clause. To denote several characters, use %:

SELECT title FROM book WHERE title LIKE '%JavaScript%';

This statement returns book titles, that includes JavaScript in the title.

The LIKE operator allows us to pattern match. The % is a wildcard which stands for zero or more letters or numbers.

What will each of the following return?

SELECT title FROM book WHERE title LIKE 'JavaScript%';    
SELECT title FROM book WHERE title LIKE '%JavaScript';

The underscore is also a wildcard but it stands in for just one letter or number, try out the following:

SELECT * FROM student WHERE county LIKE '_aterford';

Exercises

1. Retrieve the title and publisher of all Books whose title starts with the word Database.

   

2. Retrieve the title and publisher of all Books whose title includes the word Engineering.

   

3. Retrieve the names (first and last) of all Students whose last name start with D.

   

BETWEEN ... AND

We can use the BETWEEN ... AND operators to search within an inclusive range of values, for example:

For Example, to return students from years 1, 2, and 3:

SELECT * FROM student WHERE year BETWEEN 1 AND 3;

To return students (by name) with course codes between wd150 and wd159:

select concat(fName, ' ', lName) as Name, course from student where course between 'wd150' and 'wd159';

NOT BETWEEN ... AND

To return students (by name) with course codes outside the range of the previous example:

select concat(fName, ' ', lName) as Name, course from student where course not between 'wd150' and 'wd159';

Exercise

1. Retrieve all book details that were published from 2008-01-01 to 2013-12-31 inclusive.

IN

The IN operator allows us to query records based on matching one of a number of values. We can use the NOT operator to negate those results. So if we are only interested in Computing, Business, and Engineering books, instead of writing the query as follows:

SELECT * FROM book WHERE category = 'Computing' OR category = 'Business' OR category ='Engineering';

We can write it as follows:

SELECT * FROM book WHERE category IN ('Computing', 'Business', 'Engineering');

NOT IN

To select from outside the above range, use:

SELECT * FROM book WHERE category NOT IN ('Computing', 'Business', 'Engineering');

Exercise

1. Retrieve the names, towns, and county of all Students from Kilkenny, Waterford or Wexford. Again, output the student name concatenated and labelled as Name.

AND/OR

When one search criteria is not enough we use the AND, OR operators to use more than one search condition. The following commands give very different results - can you work out why?

SELECT * FROM book WHERE category = 'Computing' AND publisher = 'Sitepoint';
SELECT * FROM book WHERE category = 'Computing' OR publisher = 'Sitepoint';
SELECT * FROM book WHERE category = 'Computing' OR category = 'Business';

Exercises

1. Retrieve the names of all Students in first year from Waterford. Output the student name concatenated and labelled as Name.

   

2. Retrieve the title and price of all books that contain the Database keyword and are priced at 50 euro or less.

   

NULL

A NULL value (not white space or a zero) exists in a database where a piece of data is missing or not filled in.

For example when entering information into a table not all the information may be to hand at the time of data entry and so may be left NULL. Sometimes we may want to find out where these NULL values are. Again we can use IS NOT NULL for negation.

Return the ISBN from the bookcopy table for all books whose datedestroyed is unknown or unavailable:

SELECT ISBN FROM bookcopy WHERE datedestroyed IS NULL;

Return the ISBN from the bookcopy table for all books whose datedestroyed is known:

SELECT ISBN FROM bookcopy WHERE datedestroyed IS NOT NULL;

ORDER BY

To sort your query results you should use the ORDER BY clause. This clause always comes last in your SELECT statement. Ascending order is the default.

Return the title and category of all books from the book table whose category is Computing or Business or Engineering sorted in alphabetical order by category.

SELECT title, category FROM book WHERE category IN ('Computing', 'Business', 'Engineering') ORDER BY category;

To reverse the order you must specify descending order using the keyword DESC.

SELECT title, category FROM book WHERE category IN ('Computing', 'Business', 'Engineering') ORDER BY category DESC;

You can also sort by more than one column, where the first column is the outer sorted value.

Return all the student records sorted in alphabetic order by lastname (lname) and within each lastname by firstname (fname).:

SELECT * FROM student ORDER BY lname, fname;

For Example, Alice Ryan would appear before Anne Ryan.

Exercises:

1. Retrieve the names of students whose last name is Ryan sorted by first name. Again, output the student name concatenated and labelled as Name.

   

2. Retrieve the names, towns, and county of all Students from Kilkenny, Waterford or Wexford. Again, output the student name concatenated and labelled as Name. Sort the returned records by county and within each county by town.

   

3. Retrieve the price and title of all business books. Output the results in reverse order of price and in ascending order of title.

   

Solutions

Solutions for the exercises in this lab are available here: Week1.zip