HCI SQL Databases 2 - Using DB Browser for SQLite

SQL Databases CPDD Computer Education Unit Version: Mar 2018 1 Name: __________________________ ( ) Class: _________ Date: _________ Lesson 2: Using DB Browser for SQLite Instructional Objectives: By the end of this task, you should be able to:  Understand that Structured Query Language (SQL) is the standar d language for relational database management systems (RDBMS)  Understand the concept of type affinities found in SQLite and how it affects table columns  State that the basic database operations in relational databas es are CREATE, READ, UPDATE and DELETE (CRUD)  Use DB Browser for SQLite to perform basic database operations : o create a database o create a table o change a table definition o insert a record o update a record o delete a record o remove a table o delete a database  Apply constraints: UNIQUE, AUTOINCREMENT, NOT NULL, PRIMARY KE Y, FOREIGN KEY in DB Browser for SQLite

SQL Databases CPDD Computer Education Unit Version: Mar 2018 2 Structured Query Language Structured Query Language (SQL) is a standard computer language for the operation and management of relational databases. It is a l anguage used to query, insert, update and modify data. For an introduction to relational databases and SQL, watch the following video: Khan Academy (2018, March 23). Intro to SQL: Querying and managing data. Retrieved from https://www.khanacademy.org/computing/computer-programming/sql/sql- basics/v/welcome-to-sql SQLite There are many types of SQL database engines. A database engine is the software that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database. SQLite is a widely used database engine. Python’s IDLE comes with a built-in module for SQLite3. History of SQL In 1970, Dr. E.F. Codd published "A Relational Model of Data for Large Shared Data Banks," an article that outlined a model for storing and m anipulating data using tables. Using the article as a premise, Donald D. Chamber lin and Raymond F. Boyce of IBM began developing a relational database. By 1986, SQL had become the defacto data query language used in s u c h databases. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987. Since then, the standard was updated several times. Most major relational databases support this standard but would have their own propri etary extensions. SQL

SQL Databases CPDD Computer Education Unit Version: Mar 2018 3 Type Affinities SQLite supports the concept of type affinity on columns. Type affinity refers to the preferred type for data stored in a column. This means that you can store any type of data in a column with the recommended types, but they are not enforced. Each column in a SQLite table is assigned one of the following type affinities:  INTEGER  TEXT  REAL  N U M E R I C  B L O B If you click on a table to view its columns in DB Browser for S QLite, you will notice these types as well.

SQL Databases CPDD Computer Education Unit Version: Mar 2018 4 In summary, you will only need to know and use these three types: Types Meaning INTEGER Used to store a signed integer value. The value is stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value TEXT Used to store a text string using the database encoding (UTF-8, UTF-16BE or UTF-16LE) REAL Used to store a floating point value, as an 8-byte IEEE floating point number BLOB stands for Binary Large Object (BLOB) which is used to store large binary data, such as images or multimedia in a database. Type affinities are relevant in database migrations. This maxim izes the compatibility between SQLite and other database management systems. For examp le, a column declared as VARCHAR in one database is assigned the type affinity of TEXT if it was migrated to and stored in a SQLite database instead. This featu re helps to ensure portability across databases. Limitation of DB Browser for SQLite Note that in DB Browser for SQLite, even if a column is assigned a specific type, you can still insert values of other types into the column! Table: class_list Table column of a single type affinity

SQL Databases CPDD Computer Education Unit Version: Mar 2018 5 Typeof Function SQLite provides the typeof() function that allows you to check the data type of an expression. sqlite3.typeof() Returns a string that indicates the data type of an expression. The only return values are: "null", "integer", "real", "text", or "blob". Task 1 – Using typeof Function 1. Open DB Browser for SQLite. 2. Create a new database. 3. Click on the Execute SQL tab. 4. Enter a simple query using typeof() function to find the type for each of the value below. An example of how typeof is used is shown here. Value Type i) 2019 INTEGER ii) '2019' TEXT iii) 345.2147 REAL iv) NULL NULL v) 'True and False' TEXT vi) x'1001' BLOB vii) true ‘No such column’ error returned

SQL Databases CPDD Computer Education Unit Version: Mar 2018 6 Boolean SQLite does not have a type for Boolean values. Boolean values are stored as INTEGER with 0 (False) and 1 (True) values. For example, in the Light table below, the status of each LED lamp is of type INTEGER. 0 indicates tha t the light is not functioning, while 1 indicates that the light is functioning. Light led_id status 1001 0 1002 1 1003 0 Quiz  For each sentence, write True or False. True a. The typeof() function returns the data type of an expre ssion. It returns the string ‘No such column’ if none of the data types are found. False b. If there exists a table column with values ranging fro m 1 to 200 of type ‘INT’ in MySQL, the affinity type for it in SQLite is REAL. False c. SQLite has type BOOLEAN. True d. If data is imported from another database management system into DB Browser for SQLite, it is important to check on the type affinities as they may not be preserved during the migration. Database Operations In industry-based database applications, all four categories of SQL commands listed below will be required.  Data Definition Language (DDL) defines database schemas.  Data Manipulation Language (DML) is used to retrieve and modif y data.  Data Control Language (DCL) is used to control access to a dat abase.

SQL Databases CPDD Computer Education Unit Version: Mar 2018 7  Transaction Control Language (TCL) is used to manage changes t o a database, usually at transactional level. CREATE SELECT GRANT COMMIT ALTER INSERT REVOKE SAVEPOINT DROP UPDATE ROLLBACK RENAME DELETE TRUNCATE MERGE COMMENT CALL EXPLAIN PLAN LOCK TABLE Some of the more advanced commands under DCL and TCL are more r elevant to industry-specific roles, such as database administrators. For t he purposes of our learning, you will need to understand and apply these basic CRU D database operations: Operation SQL Command CREATE INSERT READ (RETRIEVE) SELECT UPDATE (MODIFY) UPDATE DELETE (DESTROY) DELETE DB Browser for SQLite DB Browser for SQLite is a simple and easy to use Graphical Use r Interface (GUI) - based software for the creation and editing of database files c ompatible with SQLite. It abstracts and hides the details of complex SQL commands while providing an easy to user interface for performing the same database operations. SQL Commands  DDL  DML  DCL  TCL

SQL Databases CPDD Computer Education Unit Version: Mar 2018 8 The downloadable version of it is available at http://sqlitebrowser.org/. An online version of it known as SQLite Online is found at https://sqliteonline.com/ Users and developers who are famil