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 aff