HCI SQL Databases 3 - Writing SQL Statements

SQL Databases CPDD Computer Education Unit Version: July 2018 1 Name: __________________________ ( ) Class: _________ Date: _________ Lesson 3: Writing SQL Statements Instructional Objectives: By the end of this task, you should be able to:  Use DB Browser for SQLite to select record(s) using specified criteria  Select data in the database using the SELECT statement  Understand the concepts of inner join and left outer join  Update records in database using UPDATE statement  Insert records in database using INSERT INTO statement  Delete records from database using DELETE statement  Create table from database using CREATE TABLE statement  Understanding the result of using AUTOINCREMENT in creating ta bles  Delete table from database using DROP TABLE statement  Use arithmetic, comparison and logical operators in SQL statem ents  Use aggregate functions MIN, MAX, SUM and COUNT Database Recall that a database is a set of data stored on one or more computers or servers. Take as an example, a librarian has keyed in the book and borro wer details given in the previous lessons. The book details are stored in Book table, with the publisher details stored in Publisher table, the borrower’s details in Borrower table and the loans m ade in Loan table. The intention is to be able to retrieve, edit or delete the data easily. This can be done through Data Manipulation Language (DML) state ments like SELECT, INSERT, UPDATE and DELETE. In this lesson, we will learn the SQL syntax to run the various queries in the database.

SQL Databases CPDD Computer Education Unit Version: July 2018 2 Entering SQL into DB Browser for SQLite To enter SQL into DB Browser, after loading the database, click on the Execute SQL tab. There is a text area for you to type in your SQL commands. You can also click on the buttons above the text area. Creates a new tab for entering SQL. Loads SQL file Saves SQL entered as a text file (You may save it as .sql) Execute all SQL statements in that tab. Execute the current line only. After executing SQL commands or making changes, you can click the following: Save changes made into the file Undo changes made to the database, i.e. reload from file. Let us now look at the SQL statements that you can enter. The database used in the following statements uses library.db. You can load the database into SQLite.

SQL Databases CPDD Computer Education Unit Version: July 2018 3 SELECT The SELECT statement allows the user to retrieve data from the database. To select all fields, use *. For example, typing SELECT * FROM Book and clicking will give you details of all the books in library. Conditions may be added using WHERE. For example, SELECT * from Book WHERE Damaged = 1 The statement will return all the damaged books. You can also find NULL values, for example books with no publishers (no value on the PublisherID field), using the IS operator. SELECT * from Book WHERE PublisherID IS NULL You can also search for terms which are not NULL, for example: SELECT * from Book WHERE PublisherID IS NOT NULL This statement returns all records where there is a publisher. You can insert more than one condition using AND and OR binary operators. For example, SELECT * FROM Book WHERE Title = 'Life of Pie' AND Damaged = 0 This statement returns the books with Title ‘Life of Pie’ and are not damaged. What if you only need the book titles? In that case, include the fields you want in the SELECT statement. For example, SELECT Title FROM Book

SQL Databases CPDD Computer Education Unit Version: July 2018 4 This statement will give you all the Title of the books in the Book table. Title The Lone Gatsby A Winter’s Slumber Life of Pie A Brief History Of Primates To Praise a Mocking Bird The Catcher in the Eye H2 Computing Ten Year Series Try out the SQL statements yourself! Quiz 1. Which of the following SQL statements show all data inside the Publisher table? (Tick the statements) Select all FROM Publisher √ Select * FROM Publisher Select ID, Title FROM Publisher √ Select ID, Title, PublisherID, Damaged FROM Publisher 2. Write down the SQL statement to show all the Names of the pu blishers inside the Publisher table. Select Name FROM Publisher 3. What does the following SQL statement do? SELECT Title FROM Book WHERE PublisherID=1 AND Damaged=0 The statement shows all Book titles of undamaged books by Publ isher with PublisherID 1. 4. Write down the SQL statement to show all the Titles of the b ooks which have PublisherID 1 or 2. SELECT Title FROM Book WHERE PublisherID=1 OR PublisherID=2 Do you know? You can execute multiple SQL statements, but each statement must end with a colon ;

SQL Databases CPDD Computer Education Unit Version: July 2018 5 Another example is to look at all the loans. To list all loans, the SQL statement SELECT * FROM Loan is used. ID BorrowerID BookID Date Borrowed 1 3 2 20180220 2 3 1 20171215 3 2 3 20171231 4 1 5 20180111 The list can be ordered by the BookID (in ascending order) instead by using the SQL statement SELECT * FROM Loan ORDER BY BookID ASC The result is as follows. ID BorrowerID BookID Date Borrowed 2 3 1 20171215 1 3 2 20180220 3 2 3 20171231 4 1 5 20180111 The list can be ordered by the BookID (in descending order) by using the SQL statement SELECT * FROM Loan ORDER BY BookID DESC The result is as follows. ID BorrowerID BookID Date Borrowed 4 1 5 20180111 3 2 3 20171231 1 3 2 20180220 2 3 1 20171215 What happens if the following SQL statement is executed? SELECT * FROM Loan ORDER BY BookID By default, it will order by BookID in ascending order. Try out the SQL statements using DBViewer in SQLite. Quiz 5. Write down the SQL statement to select all the records in Lo an table arranged in ascending order of BookID with BorrowerID = 1. SELECT * FROM Loan WHERE BorrowerID=1 ORDER BY BookID ASC

SQL Databases CPDD Computer Education Unit Version: July 2018 6 What if you want to find out the total number of loans? You can use function COUNT to get the answer. The SQL can be the following: SELECT COUNT(*) FROM Loan SQL JOIN A SQL JOIN allows the combination of data from two sets of data (i.e. two tables). There are different types of joins. We look at three: cross join, inner join and left outer join. Cross join returns the Cartesian product of rows from the tables in the join. It combines each row in the first table with each row in the second table. For example, the following SQL statement SELECT * FROM Book, Publisher produces the following table. ID Title PublisherID Damaged ID Name 1 The Lone Gatsby 5 0 1 NPH 1 The Lone Gatsby 5 0 2 Unpop 1 The Lone Gatsby 5 0 3 Appleson 1 The Lone Gatsby 5 0 4 Squirrel 1 The Lone Gatsby 5 0 5 Yellow Flame 2 A Winter’s Slumber 4 1 1 NPH 2 A Winter’s Slumber 4 1 2 Unpop 2 A Winter’s Slumber 4 1 3 Appleson 2 A Winter’s Slumber 4 1 4 Squirrel 2 A Winter’s Slumber 4 1 5 Yellow Flame 3 Life of Pie 4 0 1 NPH 3 Life of Pie 4 0 2 Unpop 3 Life of Pie 4 0 3 Appleson 3 Life of Pie 4 0 4 Squirrel 3 Life of Pie 4 0 5 Yellow Flame 4 A Brief History Of Primates 3 0 1 NPH 4 A Brief History Of Primates 3 0 2 Unpop 4 A Brief History Of Primates 3 0 3 Appleson 4 A Brief History Of Primates 3 0 4 Squirrel 4 A Brief History Of Primates 3 0 5 Yellow Flame 5 To Praise a Mocking Bird 2 0 1 NPH 5 To Praise a Mocking Bird 2 0 2 Unpop 5 To Praise a Mocking Bird 2 0 3 Appleson 5 To Praise a Mocking Bird 2 0 4 Squirrel 5 To Praise a Mocking Bird 2 0 5 Yellow Flame 6 The Catcher in the Eye 1 1 1 NPH 6 The Catcher in the Eye 1 1 2 Unpop

SQL Databases CPDD Computer