RI Y3 CEP Using DB Browser for SQLite 2021

SQL Databases CPDD Computer Education Unit Version: Mar 2018 1 Name: __________________________ ( ) Class: _________ Date: _________ Lesson 5: 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 standard 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 databases 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 KEY, 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 language 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 manipulating data using tables. Using the article as a premise , Donald D. Chamberlin and Raymond F. Boyce of IBM began developing a relational database. By 1986, SQL had become the defacto data query language used in such 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 proprietary 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 • NUMERIC • BLOB If you click on a table to view its columns in DB Browser for SQLite , 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 maximizes the compatibility between SQLite and other database management system s. For example, 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 feature 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 ii) '2019' iii) 345.2147 iv) NULL v) 'True and False' vi) x'1001' vii) true

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 that 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. a. The typeof() function returns the data type of an expression. It returns the string ‘No such column’ if none of the data types are found. b. If there exists a table column with values ranging from 1 to 200 of type ‘INT’ in MySQL, the affinity type for it in SQLite is REAL. c. SQLite has type BOOLEAN. 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 modify data. • Data Control Language (DCL) is used to control access to a database.

SQL Databases CPDD Computer Education Unit Version: Mar 2018 7 • Transaction Control Language (TCL) is used to manage changes to 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 relevant to industry-specific roles, such as database administrators . For the purposes of our learning, you will need to understand and apply these basic CRUD 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 User Interface (GUI) - based software for the creation and editing of database files compatible 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 familiar with SQLite3 may use command -line tools to manage SQLite database files.