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, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. TEXT The value is a text string, stored using the database encoding (UTF-8, UTF 16BE or UTF-16LE). REAL The value is a floating point value, stored as an 8-byte IEEE floating point number. NUMERIC Values entered are converted to any one of the datatypes which is most appropriate. BLOB The value is a blob of data, stored exactly as it was input. Usually used to store large binary data, such as images or multimedia in a database. SQLite does not have a separate Boolean datatype. Instead, Boolean values are stored as integers 0 (false) and 1 (true). SQLite recognizes the keywords "TRUE" and "FALSE", as of version 3.23.0 (2018-04-02), but those keywords are just alternative spellings for the integer literals 1 and 0 respectively. Type affinities are relevant in database migrations. This maximizes the compatibility between SQLite and other database management systems. 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. In the current syllabus, only INTEGER, TEXT and REAL will be used. 2 3 Database Operations In industry -based database applications, SQL commands are grouped into four categories listed below. • 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. • Transaction Control Language ( TCL ) is used to manage changes to a database, usually at transactional level. SQL Commands DDL DML DCL TCL CREATE ALTER DROP RENAME TRUNCATE COMMENT SELECT INSERT UPDATE DELETE MERGE CALL EXPLAIN PLAN LOCK GRANT REVOKE COMMIT SAVEPOINT ROLLBACK

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 C REATE CREATE, INSERT R EAD (RETRIEVE) SELECT U PDATE (MODIFY) UPDATE D ELETE (DESTROY) DELETE, DROP 3 4 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 use interface for performing the same database operations. The downloadable version of it is available at http://sqlitebrowser.org/. An online version of it is known as SQLite Online is found at https://sqliteonline.com/ 5 CREATE In this section, we will explore how to create a library database with four tables (Borrower, Publisher, Book and Loan) using SQL commands. The library contains books that can be on loan to borrowers. ● A borrower can take one or more loans. ● Each loan record belongs to only one borrower. ● A book can be loaned many times. ● A publisher publishes one or more books. ● A book can be published by zero or one publisher. For example, school lecture notes are not published by an official publishing house. 5.1 Create tables When we create tables, we can set constraints . Constraints are the rules enforced on data columns in a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

The following constraints are commonly used in SQL: ● NOT NULL – ensures that a field cannot be empty or has a NULL value ● UNIQUE - ensures that all values in a column are different ● PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row/record in a table. ● AUTOINCREMENT – value increases automatically with each new record inserted. ● FOREIGN KEY – references to the PRIMARY KEY in another table which is used to enforce "exists" relationships between tables. Prevents invalid data from being inserted into