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