SQLite Basics Quiz

SQLite Overview

• SQLite requires no configuration or setup, making it easy to get started.
• It is a cross-platform database management system (DBMS) compatible with various operating systems, including Windows, macOS, Linux, Unix, and embedded systems like Symbian and Windows CE.
• Data storage is efficient, allowing variable-length columns that optimize space usage.

SQLite Features

• Variable-length columns allow storing only necessary space for field data, enhancing efficiency.
• A broad array of APIs is available for multiple programming languages, including .NET (C#, Visual Basic), PHP, Java, Objective C, and Python.
• Written in ANSI-C, SQLite offers a simple and user-friendly API.

Advantages of SQLite

• Lightweight and suitable as an embedded database in devices such as TVs, mobile phones, and cameras.
• Provides fast reading and writing operations, significantly faster than a typical file system, with data loading on a need-to-know basis.
• No installation required; simply download SQLite libraries to start creating databases.
• Reliable with continuous updates to data to prevent loss during power failures or crashes.

Basic SQLite Commands

• Create Table Example:
  • CREATE TABLE STUDENT (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), FEES REAL);
• Drop Table Syntax:
  • DROP TABLE database_name.table_name;
• Insert Records:
  • INSERT INTO TABLE_NAME (column1, column2) VALUES (value1, value2);
  • Example: INSERT INTO student VALUES (1, "Heta Desai", 27, "Bhatar", 28999.50);

Data Retrieval and Manipulation

• SELECT Statement:
  • Used to fetch data: SELECT column1, column2 FROM table_name;
  • Example: SELECT * FROM student;
• UPDATE Statement:
  • Modifies existing records with conditions: UPDATE table_name SET column1 = value1 WHERE [condition];
  • Example: UPDATE student SET address = "Althan" WHERE id = 1;
• DELETE Statement:
  • Deletes records using conditions: DELETE FROM table_name WHERE [condition];

Filtering and Operators

• DISTINCT Operator:
  • Eliminates duplicate records: SELECT DISTINCT column1 FROM table_name;
• IN Operator:
  • Checks if a value matches any in a list: SELECT * FROM TABLE_NAME WHERE Column_name IN (value1, value2);
• BETWEEN Operator:
  • Retrieves values within a specified range: SELECT * FROM TABLE_NAME WHERE Column_name BETWEEN value1 AND value2;

Advanced SQL Operators

• UNION Operator:
  • Combines results from multiple SELECT statements while removing duplicates.
• INTERSECT Operator:
  • Returns common records from two or more SELECT statements.

Joins in SQLite

• Joins combine records from two or more tables using common values.
• Types of Joins:
  • Inner Join: Combines rows from both tables that satisfy the join condition.
  • Cross Join: Produces a Cartesian product of both tables.
  • Self-Join: Joins a table with itself using aliases.

Example of Self-Join

• Retrieve employee and manager details using a self-reference in the EMPLOYEE table:
  • Example Query:

  SELECT x.emp_id, x.name AS Employee, y.emp_id AS 'Manager ID', y.name AS 'Manager Name'
  FROM EMPLOYEE x, EMPLOYEE y
  WHERE x.manage_id = y.emp_id;
  

SQLite Functions

• datetime() Function:
  • Translates a given string into a date and time format YYYY-MM-DD HH:MM:SS.
  • Syntax: datetime(datetimestring, [modifier1, modifier2…]);