VJC Chapter 20 SQL

Chapter 20 Structured Query Language (SQL) Contents 1 Structured Query Language 2 SQLite 3 Database Operations 4 DB Browser for SQLite 5 CREATE 5.1 Create tables 5.2 Insert data 6 UPDATE 7 DELETE 8 READ 8.1 SELECT 8.2 ORDER BY 8.3 GROUP BY 8.4 JOIN 8.4.1 Cross join 8.4.2 Inner join and Left outer join Annex 1 – SQL Statements Annex 2 – Operators Annex 3 – Using typeof Function Annex 4 – Using DB Browser to enter SQL statements Annex 5 – Using Import table from CSV file Annex 6 – Using Import database from SQL file Annex 7 – Insert Records using GUI Annex 8 – Update Records using GUI Annex 9 – Delete Records using GUI Annex 10 – Revert Changes Annex 11 – Summary of DB Browser for SQLite Features Syllabus Learning Outcomes 3.3 Databases and Data Management Understand, create and use SQL and NoSQL databases, as well as understand techniques to protect the privacy and integrity of data. 3.3.1 Determine the attributes of a database: table, record and field. 3.3.2 Explain the purpose of and use primary, secondary, composite and foreign keys in tables. 3.3.3 Explain with examples, the concept of data redundancy and data dependency. 3.3.4 Reduce data redundancy to third normal form (3NF). 3.3.5 Draw entity-relationship (ER) diagrams to show the relationship between tables. 3.3.6* Understand how NoSQL database management system addresses the shortcomings of relational database management system (SQL). 3.3.7* Explain the applications of SQL and NoSQL. 3.3.8* Use a programming language to work with both SQL and NoSQL databases.

*Note: NoSQL will be addressed in later chapter 1 1 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 communicate with a database. SQL can be used to query, insert, update and modify data. SQL There are many types of SQL database engines, e.g. MySQL, Microsoft SQL, SQLite, PostgreSQL etc. A database engine is the software that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database. For our syllabus, you will be using SQLite. 2 SQLite SQLite supports the concept of type affinity on columns/fields. Type affinity refers to the preferred data type stored in a column. Each column in a SQLite table is assigned one of the following type affinities: Type affinities Meanings INTEGER The value is a signed integer, stored in 0, 1,