SQLite Tutorial | What is SQLite?

1. Introduction of SQLite
2. What is SQLite?
3. SQLite History
4. Who Uses SQLite
5. Features and Limitation of SQLite
6. SQLite Statements
7. Data Types

Introduction of SQLite

SQLite is an open source, embedded relational database. SQLite originally released in 2000, it is designed to provide a suitable way for applications to manage data without the overhead that often comes with dedicated relational database management systems. 

What is SQLite?

In the simplest terms, SQLite is a free, open source, public-domain software package that provides a RDBMS (relational database management system).  RDBMS are used to store user-defined records in large tables. In data storage and management system, a database engine can process complex query commands that combine data from multiple tables to generate reports and data summary. 

Other popular RDBMS servers include Oracle Database, IBM’s DB2, and Microsoft’s SQL Server on the commercial uses, with MySQL and PostgreSQL being popular open source products.   The SQLite library accesses its storage files directly. Zero Configuration No server means no setup.

To understand the concept better, check out Great Learning Academy’s free course on SQLite for Data Science. A certification will improve your chances of getting hired.

SQLite History

Hipp and his team creating a simple embedded SQL database in January 2000, that would use for GNU DBM B-Tree library (gdbm) as a back-end, that would not require for any installation or administrative support whatsoever.  in August 2000, SQLite 1.0 was released. 

SQLite version 2to3 upgrade in 2004. SQLite primary goal was enhanced internationalization support for UTF-8 and UTF-16 text. In 2011 Hipp announced to add UNQI interface to SQLite DB and to develop UNQLite.

Who Uses SQLite

Today, SQLite is used in a spacious variety of software and products. It is also used in Apple’s Mac OS operating system as a part of their Core Data application framework. and also used in the system’s Safari web browser, Mail.app email program, RSS manager, as well as Apple’s Aperture photography software. 

SQLite can be viewed in Sun’s Solaris OS (operating system) environment, specifically the database backing the Service Management Facility that debuted with Solaris 10, a core component of its predictive is self-healing technology.  SQLite, has been added as a part of    PHP 5 standard library.

Features and Limitation of SQLite

SQLite is defined by the following features: 

Serverless: –

 SQLite does not require a server process or system to operate database. The SQLite library accesses its storage files directly. 

Zero Configuration: –

No server means no setup. SQLite is not required to install any application, configure, and nothing to worry about. 

Cross-Platform: –

The entire database instance resides in a single cross-platform file, it is not required any administration. 

Self-Contained: – 

A single library contains the entire database system, which integrated directly into a host application. 

Small Runtime Footprint: –

The default build is less than a megabyte of code and requires only a few megabytes of memory. With some adjustments, both the library size and memory use can be significantly reduced.

Transactional: –

SQLite transactions are follows ACID property, it is  allowing safe access from multiple processes or threads.

 Full-Featured: –

SQLite supports most of the query language features found in the SQL92 (SQL2) standard.

 Highly Reliable: –

The purpose of a database is to keep your data safe and organized. To maintain a high level – reliability of database, the core SQLite library is tested before each release. In full, the standard SQLite test suites consist of over 10 million-unit tests and query tests.

 Limitations: –

 Foreign key constraints: – Foreign keys are the foundation of referential integrity in relational databases. While SQLite parses them, it currently does not have support for foreign keys. It does support check constraints, and foreign key support is estimated to be completed by sometime in 2006. 

Trigger support: –There is some support for triggers but it is not complete. Missing features include FOR EACH STATEMENT triggers (currently all triggers must be FOR EACH ROW), INSTEAD OF triggers on tables (currently INSTEAD OF triggers are only allowed on views), and recursive triggers—triggers that trigger themselves.  

ALTER TABLE support: – Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, and ADD CONSTRAINT are not implemented. 

Nested transactions: – SQLite allows only a single transaction to be active at one time. Nested transactions allow for fine-grained control over larger, more complex operations in that parts of a transaction can be defined and rolled back in case of an error rather than the entire transaction. 

RIGHT and FULL OUTER JOIN: – LEFT OUTER JOIN is implemented, but RIGHT OUTER JOIN and FULL OUTER JOIN are not implemented. LEFT OUT JOIN can be implemented as a right outer join by simplified reversing the order of the tables and modify the join constraint. Furthermore, FULL OUTER JOIN can be implemented as a combination of other relational operations supported by SQLite. 

Updatable views: – VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger. 

GRANT and REVOKE: – GRANT and REVOKE commands in general are aimed at much higher end systems where there are multiple users who have varying access levels to data in the database.

Installation: 

1. Go to the SQLite website, www.sqlite.org. Follow the download link, which will take you to the download page.

 2. On the download page, find the Source Code section. 

3. The first two files should be zip files containing the source code for Windows. The file you want to download should have a name with the form sqlite-source-3-x_y.zip, where x and y are the minor version numbers. The important thing here is that you want sqlite-source-3-x_y.zip, which corresponds to SQLite version 3, not sqlite-source2-x_y.zip, which corresponds to SQLite version 2.

4. Extract or unzip the file to a directory of your choosing. The extracted contents will be the complete SQLite version 3 source code for Windows.

Also follow same process for Linux and Mac-OS.

Working with SQLite Databases: –

The SQLite CLP is the most common means you can use to work with and manage SQLite databases. It runs on as many platforms as the SQLite library, so learning how to use it ensures you will always have a common and familiar way to manage your databases. The CLP is really two programs in one. It can run from the command line to perform various administration tasks, or it can be run in shell mode and act as an interactive query processor.

Commands: –

 To invoke the CLP as in shell mode, type sqlite3 from a command line, followed by an optional database name. If you do not specify a database name, SQLite will use an in-memory database (the contents of which will be lost when the CLP exits). 

These commands are reserved for specific shell operations, a complete list of which can be obtained by typing  .help as shown:

  $ sqlite3 

SQLite version 3.3.4

 Enter “.help” for instructions

 sqlite> .help

                                                Commands & Description

.databases	List names and files of attached databases
.dump ?TABLE? …	Dump the database in a SQL text format
.echo ON|OFF	Turn command echo on or off
.explain ON|OFF	Turn on/off output mode suitable for EXPLAIN on or off
.header(s) ON|OFF	Turn display of headers on or off
.help	Show the instructions and messages
.import FILE TABLE	Import data from FILE into TABLE
.indices TABLE	Show names of all indices on TABLE
.mode MODE ?TABLE?	Set output mode where MODE is one of: csv               Comma-separated values column         Left-aligned columns. (See .width) html              HTML table codeinsert            SQL insert statements for TABLE  Line                  One value per line list                    Values delimited by .separator string tabs                  Tab-separated values tcl                  TCL list elements
.nullvalue STRING	Print STRING in place of NULL values
.output FILENAME	Send output to FILENAME
.output stdout	Send output to the screen
.prompt MAIN CONTINUE	Replace the standard prompts
.quit	Exit this program
.read FILENAME	Execute SQL in FILENAME
.schema ?TABLE?	Show the CREATE statements
.separator STRING	Change separator used by output mode and .import
.show	Show the current values for various settings
.tables ?PATTERN?	List names of tables matching a LIKE pattern
.timeout  MS	Try opening locked tables for MS milliseconds
width NUM NUM …	Set column widths for “column” mode
.backup ?DB? FILE	Backup DB (default “main”) to FILE
.bail ON|OFF	Stop after hitting an error. Default OFF
Sqlite > .exit	For exit to window

Syntax: – 

SQL’s declarative syntax reads a lot like a natural language. Statements are expressed in the imperative mood, beginning with the verb describing the action.

Case Sensitivity: –

SQLite is case insensitive, i.e. CREATE and create have the same meaning in SQLite      statements.

Comments: –

Comments in SQL are denoted by two consecutive hyphens (–), which comment the remaining line, or by the multiline C-style notation (/* */), which can span multiple lines. For example:

 Sqlite > .help      — This is a comment on one line 

                           /* This is a comment  of

                               two lines */

SQLite Statements: –

All the SQLite statements start with any of the keywords like CREATE, SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., and all the statements end with a semicolon (;).

ANALYZE Statement

ANALYZE;

or

ANALYZE database_name;

or

ANALYZE database_name.table_name;

 AND/OR Clause

SELECT column1, column2….columnN

FROM table_name

WHERE CONDITION-1 {AND|OR} CONDITION-2;

ALTER TABLE Statement

ALTER TABLE table_name ADD COLUMN column_def…;

ALTER TABLE Statement (Rename)

ALTER TABLE table_name RENAME TO new_table_name;

ATTACH DATABASE Statement

ATTACH DATABASE ‘DatabaseName’ As ‘Akshit-Name’;

BEGIN TRANSACTION Statement

BEGIN;

or

BEGIN EXCLUSIVE TRANSACTION;

BETWEEN Clause

SELECT column1, column2….columnN

FROM table_name

WHERE column_name BETWEEN val-1 AND val-2;

COMMIT Statement

COMMIT;

CREATE INDEX Statement

CREATE INDEX index_name

ON table_name ( column_name COLLATE NOCASE );

CREATE UNIQUE INDEX Statement

CREATE UNIQUE INDEX index_name

ON table_name ( column1, column2,…columnN);

CREATE TABLE Statement

CREATE TABLE table_name(

   column1 datatype,

   column2 datatype,

   column3 datatype,

   …..

   columnN datatype,

   PRIMARY KEY( one or more columns )

);

 CREATE TRIGGER Statement

CREATE TRIGGER database_name.trigger_name 

BEFORE INSERT ON table_name FOR EACH ROW

BEGIN 

   stmt1; 

   stmt2;

   ….

END;

CREATE VIEW Statement

CREATE VIEW database_name.view_name AS

SELECT statement….;

CREATE VIRTUAL TABLE Statement

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );

or

CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

COMMIT TRANSACTION Statement

COMMIT;

COUNT Clause

SELECT COUNT(column_name)

FROM table_name

WHERE CONDITION;

DELETE Statement

DELETE FROM table_name

WHERE {CONDITION};

DETACH DATABASE Statement

DETACH DATABASE ‘ Akshit-Name’;

DISTINCT Clause

SELECT DISTINCT column1, column2….columnN

FROM table_name;

DROP INDEX Statement

DROP INDEX database_name.index_name;

DROP TABLE Statement

DROP TABLE database_name.table_name;

DROP VIEW Statement

DROP INDEX database_name.view_name;

DROP TRIGGER Statement

DROP INDEX database_name.trigger_name;

EXISTS Clause

SELECT column1, column2….columnN

FROM table_name

WHERE column_name EXISTS (SELECT * FROM   table_name );

EXPLAIN Statement

EXPLAIN INSERT statement…;

or 

EXPLAIN QUERY PLAN SELECT statement…;

GLOB Clause

SELECT column1, column2….columnN

FROM table_name

WHERE column_name GLOB { PATTERN };

GROUP BY Clause

SELECT SUM(column_name)

FROM table_name

WHERE CONDITION

GROUP BY column_name;

HAVING Clause

SELECT SUM(column_name)

FROM table_name

WHERE CONDITION

GROUP BY column_name

HAVING (arithematic function condition);

 INSERT INTO Statement

INSERT INTO table_name( column1, column2….columnN)

VALUES ( value1, value2….valueN);

IN Clause

SELECT column1, column2….columnN

FROM table_name

WHERE column_name IN (val-1, val-2,…val-N);

Like Clause

SELECT column1, column2….columnN

FROM table_name

WHERE column_name LIKE { PATTERN };

NOT IN Clause

SELECT column1, column2….columnN

FROM table_name

WHERE column_name NOT IN (val-1, val-2,…val-N);

  

ORDER BY Clause

SELECT column1, column2….columnN

FROM table_name

WHERE CONDITION

ORDER BY column_name {ASC|DESC};

PRAGMA Statement

PRAGMA pragma_name;

For example:

PRAGMA page_size;

PRAGMA cache_size = 1024;

PRAGMA table_info(table_name);

RELEASE SAVEPOINT Statement

RELEASE savepoint_name;

REINDEX Statement

REINDEX collation_name;

REINDEX database_name.index_name;

REINDEX database_name.table_name;

ROLLBACK Statement

ROLLBACK;

or

ROLLBACK TO SAVEPOINT savepoint_name;

SAVEPOINT Statement

SAVEPOINT savepoint_name;

SELECT Statement

SELECT column1, column2….columnN

FROM table_name;

UPDATE Statement

UPDATE table_name

SET column1 = value1, column2 = value2….columnN=valueN

[ WHERE  CONDITION ];

VACUUM Statement

VACUUM;

 WHERE Clause

SELECT column1, column2….columnN

FROM table_name

WHERE CONDITION;

Data Types

Create Database: – 

$sqlite3 DatabaseName.db

sqlite3 command use to create a database.

After the creation of database you can verify it by using   .database command: –

.database

.quite command used to come back command prompt

.quite

Creating Tables: –

You create a table using CREATE TABLE command, which is defined as follows:

CREATE TABLE table_name(   column1 datatype PRIMARY KEY(one or more columns),   column2 datatype,   column3 datatype,   …..   columnN datatype);

Example: –

CREATE TABLE Student(    Student_id   INT     PRIMARY KEY   NOT NULL,    Student_name   TEXT           NOT NULL,    course                TEXT           NOT NULL, );

Create one more table: – 

CREATE TABLE Teacher(    Teacher_id   INT     PRIMARY KEY   NOT NULL,    Teacher_name   TEXT      NOT NULL,    department       TEXT           NOT NULL,  );

Here NOT NULL constraints  is used because these fields cannot be null hile creating records in the table.

.table

table command is used to  show the list of all tables in an attached database.

Altering Tables

 You can change parts of a table with the ALTER TABLE command. SQLite’s version of alter    table can either rename a table or add columns. 

The general form of the command is as follows: 

ALTER TABLE  table_name RENAME TO new_table_name;

  You can also add columns using ALTER TABLE command

ALTER TABLE  table_name ADD COLUMN column_def… ;

Example: –

ALTER TABLE  Student ADD COLUMN  Branch VARCHAR(20) NOT NULL ;

DROP TABLE: –

SQLite DROP TABLE command is used to drop a table definition and all associated data, indexes, triggers, constraints, and permission specifications for that table.

DROP TABLE table_name;

Example: –

DROP TABLE Student;

You can see the tables in your database using   .table command. Student table is not longer shown you.

 INSERT INTO :-

 INSERT INTO command is used to add new rows of data into a table in the database.

Syntax:

INSERT INTO table_name[(column1, column2, column3,…columnN)]  VALUES (value1, value2, value3,…valueN);

Here, column1, column2,…columnN are the names of the columns of the table where you   insert the data. 

You may not need to  define the column(s) name in the  query if you are adding values for all the columns of the table.

 Syntax:

INSERT INTO  table_name VALUES (value1,value2,value3,…valueN);

Example:

CREATE TABLE Student(

   id                 INT        PRIMARY KEY     NOT NULL,

  name          TEXT       NOT NULL

 course         CHAR(50) NOT NULL,

);

Now insert the values in to table

INSERT INTO Student (ID, name, course)

VALUES (1, ‘Akshit’, MCA );

INSERT INTO Student (ID, name, course)

VALUES (2, ‘Shubham’, BCA );

INSERT INTO Student (ID, name, course)

VALUES (3, ‘ MAYANK’, B.TECH);

SELECT command: –

  SELECT  command is used to fetch the data from a database table .    

 Syntax:

Following is the basic syntax of SQLite SELECT statement.

SELECT column1, column2, columnN FROM table_name;

  column1, column2 … are the fields of a table, whose values you want to fetch.

  if you want to fetch all the fields available in the table, then you can use the following     command −

SELECT * FROM table_name;

Operators:

An operator is a symbol or a character which is used in a SQLite statement’s WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

 There are four types of operator:

• Arithmetic operators
• Comparison operators
• Logical operators
• Bitwise operators

Arithmetic Operators (+ – * / %)

 Standard arithmetic operators for addition, subtraction, multiplication, division, and modulus.

Logical Operators:

1ANDThe AND operator use to check multiple conditions in an SQL statement’s WHERE clause. 2BETWEENThe BETWEEN operator is used to search for minimum value and the maximum value in a given data. 3EXISTSThe EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria. 4INThe IN operator is used to compare a value to a list. 5NOT INThe negation of IN operator which is used to compare a value to a list of literal values that have been specified. 6LIKEThe LIKE operator is used to compare its similar values. 7GLOBThe GLOB operator is used to compare a value to similar values. GLOB is case sensitive. 8NOTThe NOT operator reverses the meaning of the logical operator.  Ex- NOT EXISTS, NOT BETWEEN, NOT IN, etc.   9ORThe OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause. 10IS NULLThe NULL operator is used to compare a value with a NULL value (it is null or not ). 11ISThe IS operator work like (=). 12IS NOTThe IS operator work like (!=). 13||It is Use to String concatenation 14UNIQUEThe UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

 Comparison Operators:

== , =	Both Equality test operators. Both “=” and “==” will test for equality
!=  ,  <>	both “!=” and “<>” use test for inequality
< <= => >	Comparison test operators.  less-than, less-than or equal, greater-than or equal, and greater than.

 Bitwise Operators:

| & << >>

These bitwise operators OR, AND, and shift-high/shift-low. These operators are not part of the SQL standard.

Expression: – 

An expression is a combination of one or more values and operators that evaluate a value.

Consider the basic syntax of the SELECT statement as follows −

SELECT column1, column2, columnN FROM table_name WHERE [CONDITION | EXPRESSION];

 Boolean expression:

 Boolean Expressions used to fetch the data on the basis of matching single value

SELECT  * FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION;

Example:

SELECT * FROM Teacher WHERE SALARY = 10000;

ID          NAME        AGE         ADDRESS     SALARY

———-  ———-  ———-  ———-  ———-

4           James        24           Delhi      10000.0

 Numeric Expression:

 It is used to perform any mathematical operation.  

SELECT numerical_expression as OPERATION_NAME[FROM table_name WHERE CONDITION] ;

it is used for mathematical expression or any formula.  

 Example:

 SELECT (15 + 6) AS ADDITION

ADDITION = 21

  SELECT COUNT(*) AS “RECORDS” FROM  Students; 

RECORDS = 7

Date Expressions

it returns the current system date and time.

SELECT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP = 2013-03-17 10:43:35

WHERE clause:

Where clause is used to fetching the data from one table or multiple table. 

Syntax:

SELECT * FROM table_nameWHERE [condition];

Example:

SELECT * FROM Students WHERE marks >=70 ;Id            name          marks 01           Shubham    8505           Mayank      7610            Akshit         77

SELECT * FROM Students WHERE name LIKE ‘Ak%’;Id            name          marks 10            Akshit         7712           Akash           5517           Akshay        60

SELECT * FROM Teachers WHERE Age IN (22 , 28);Id            name          Age11           Anuj            2212          Rajesh          2513             JAY             26

AND Operator:

And Operator is used to check one and multiple conditions.It checks all condition, if all condition is true then it assume true.

Ex: if [condition1] And [condition2] And [condition3] will be true if condition1, condition2, condition3 are true.

SELECT * FROM table_nameWHERE [condition1] AND [condition2]…AND [conditionN];

Example:

SELECT * FROM Teacher WHERE Age >= 25 AND SALARY = 20000;

ID          NAME        AGE         ADDRESS     SALARY

———-  ———-  ———-  ———-  ———-

4           James        25           Delhi      20000.0

6            Rakesh      26           Delhi      20000.0

OR Operator:

The OR operator is used to combine multiple conditions in a statement WHERE clause. In OR operator we don’t need to check all conditions, if one condition is true then we assume that all condition is true.

 For example, [condition1] OR [condition2] OR [condition3]will be true if either condition1 or condition2 or condition3 is true.

Syntax:

SELECT * FROM table_name WHERE [condition1] OR [condition2]…..OR[conditionN];

Example:

SELECT * FROM Teachers WHERE Age >=25 OR Salary =30000;

ID          NAME        AGE         ADDRESS     SALARY

———-  ———-  ———-  ———-  ———-

4           James        25           Delhi           20000.0

6            Rakesh     26           Delhi           30000.0

8            Vikas         28           Mumbai     25000.0 

 Here condition1 is true. Then we assume that condition2 is also true.

Update Query:

Update command is used to update and modify existing records, table and data. and we can use update query with where clause to modify selected raw.

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueNWHERE [condition];

Example:

UPDATE Teachers SET Name = ‘Akshit’ WHERE ID = 4 ;

ID          NAME        AGE         ADDRESS     SALARY

———-  ———-  ———-  ———-  ———-

4            Akshit       25           Delhi           20000.0

6            Rakesh     26           Delhi           30000.0

8            Vikas         28           Mumbai     25000.0 

Delete Query:

Delete Query is used to delete a table or records. And we can use where clause to delete selected raw.

Syntax:

DELETE FROM table_name WHERE [condition];

Example:

DELETE FROM Teachers where ID = 4;

ID          NAME        AGE         ADDRESS     SALARY

———-  ———-  ———-  ———-  ———

6            Rakesh     26           Delhi           30000.0

8            Vikas         28           Mumbai     25000.0 

DELETE FROM Teachers;

Now all records delete from teacher table.

Like clause:

Like clause is used to check/matched the values against a pattern (using wildcard).

If the search expression can be matched to the pattern expression, then LIKE operator will return true. There are two wildcards used in conjunction with the LIKE operator −

•  percent sign (%)
•  underscore (_)

1)The percent sign represents- zero, one, or multiple numbers or characters. 

2)The underscore represents- a single number or character. 

3)These are used in combinations.

Syntax:

 

SELECT FROM table_nameWHERE column LIKE ‘AAAAAA%’;               //  (or %AAAAAA%)or SELECT FROM table_nameWHERE column LIKE ‘_XXXX_’  ;                    //(or _xxxxx) also we can use like this.

Example

Uses of LIKE clause with ‘%’ and ‘_’ operators.

WHERE column LIKE ‘200%’Finds any values that start with 200

WHERE column LIKE ‘%200%’Finds any values that have 200 in any position

WHERE column LIKE ‘_00%’Finds any values that have 00 in the second and third positions

WHERE column LIKE ‘2_%_%’Finds any values that start with 2 and are at least 3 characters in length

WHERE column LIKE ‘%2’Finds any values that end with 2

WHERE column LIKE ‘_2%3’Finds any values that has a 2 in the second position and ends with a 3

WHERE column LIKE ‘2___3’Finds any values in a five-digit number that starts with 2 and ends with 3

ORDER BY and  LIMIT clause:

ORDER clause is used to set the data in ascending and descending order.

Syntax:

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

Example:

SELECT * FROM Teachers ORDER BY salary ASC;

ID          NAME        AGE         ADDRESS     SALARY

———-  ———-  ———-  ———-  ———-

5            Rajesh       21           Agra             10000.0

4            Akshit       25           Delhi           20000.0

8            Vikas         28           Mumbai     25000.0 

6            Rakesh     26           Delhi           30000.0

Also this method used for descending order.

LIMIT clause:

Example:

select * from foods where name like ‘B%’ order by type_id desc, name limit 10;

 

Id      type_id    name                                                                                                        —– ——– ——————–                                                                                                                       382        10       Baked Beans                                                                                                            383        14        Baked Potato w/Sour                                                                                       384        01        Big Salad                                                                                                          385        05        Broccoli                                                                                                                        362        05        Bouillabaisse                                                                                                      328        02        BLT                                                                                                                           327        12        Bacon Club (no turke                                                                                   326        03        Bologna                                                                                                              329        12        Brisket Sandwich                                                                                                    274        10        Bacon

Distinct keyword:

DISTINCT keyword is used to fetch unique data. This keyword avoid duplicasy. 

Syntax:

SELECT DISTINCT column1, column2,…..columnN FROM table_nameWHERE [condition];

Example:

SELECT DISTINCT name FROM Teachers;

Name

Akshit 

Rajesh 

Rahul

Shubham

These are unique name in teachers table. There is no duplicate value.

Grouping:

An essential part of aggregation is grouping. In addition to computing aggregates over an entire result, you can also split that result into groups of rows with like values and compute aggregates on each group—all in one step

Syntax:

select type_id from foods group by type_id;

This command show only type id of food table.

select type_id, count(*) from foods group by type_id;

CONSTRAINTS:

SQL constraints are used to specify rules and set limits for the data in a table.    

Types of constraints:

       1)NOT NULL Constraint  

 2)DEFAULT Constraint  

         3)UNIQUE Constraint      

         4)PRIMARY Key                  

          5)FOREIGN Key

NOT NULL Constraint:  By default all columns can hold null values. if any user don’t want to a column have null value then you need to pass any constraint in the column.

Example:

CREATE TABLE  Company(

   ID                INT        NOT NULL,

   NAME           TEXT    NOT NULL,

   AGE            INT     NOT NULL,

   ADDRESS        CHAR(50),

);

DEFAULT constraints: 

it is used to provide default value to columns.

Example:

insert into contacts (name) values (‘ Akshit);

select * from contacts;     name      phone     Akshit     unknown    //here unknown is a default value

UNIQUE constraint:

A UNIQUE constraint ensure that all values in a column and a group of columns are distinct and both UNIQUE and PIMARY constraints used for uniqueness.

CREATE TABLE Company (

   ID                 INT        NOT NULL,

   NAME           TEXT    NOT NULL,

   AGE            INT     NOT NULL UNIQUE,

);

PRIMARY Key:

A primary key is always created time of a table creation. Primary key used for uniquely identifies rows, columns in a table. primary key is used for store unique values like ID,s Roll-no. using primary key we cannot store duplicate values in a table. 

If multiple fields used primary key then its is called composite key.

CREATE TABLE Company (

   ID                  INT         PRIMARY KEY     NOT NULL,

   NAME           TEXT                                  NOT NULL,

   AGE            INT                                        NOT NULL ,

);

Check constraints:

Check constraints allow you to define expressions to test values whenever they are inserted into or update within a column.

Syntax:

CREATE TABLE contacts(id INT PRIMARY KEY NOT NULL,name  TEXT NOT NULL,phone text not null default ‘UNKNOWN’, unique (name,phone), Check (length(phone)>=7) );

This is an example of a check constraint to ensure that the value of a phone number field is at least seven characters long.

FOREIGN Key:

A FOREIGN KEY is a key used to connect two tables together. A FOREIGN KEY is a collection of fields that refers to the PRIMARY KEY in second/another table. The table containing foreign key is known as child table, and the table containing the candidate key is known as parent table.

FOREIGN Key on create table:

CREATE TABLE Orders(OrderID int NOT NULL,OrderNumber int NOT NULL,PersonID int NOT NULL, PRIMARY KEY (OrderID),FOREIGN KEY (PersonID) REFERENCES Persons(PersonID));

FOREIGN KEY on ALTER TABLE:

ALTER TABLE OrdersADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint:

ALTER TABLE OrdersDROP FOREIGN KEY PersonOrder;

Indexes:

Indexes are used to fetch the data from the database more-faster than otherwise. indexes are just used to speed up searches/queries.

CREATE INDEX:

CREATE INDEX index_name ON table_name;

UNIQUE INDEX:

CREATE UNIQUE INDEX index_nameon table_name (column_name);

Composite Indexes:

A composite index contains two or more columns of a table. 

CREATE INDEX index_nameon table_name (column1, column2);

DROP INDEX:

DROP INDEX index_name;

Triggers:

Triggers execute specified SQL commands when specific database events occur on specific tables.  

Syntax:

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_nameBEGIN — Trigger logic goes here….END;

Example:

First we create two table first table name is transport_company and second is audit. Where we keep audit trials for every records .

CREATE TABLE Company  (ID INT PRIMARY KEY NOT NULL,Name TEXT NOT NULL,};

CREATE TABLE AUDIT (

   EMP_ID INT NOT NULL,

   ENTRY_DATE TEXT NOT NULL

);

Now create trigger ON Company-

CREATE TRIGGER audit_log AFTER INSERT 

ON COMPANY

BEGIN

   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime(‘now’));

END;

Now insert the value in the table.

INSERT INTO COMPANY (ID,Name )

VALUES (1, ‘Rahul’ );

Record:

ID    Name1      Rahul

Same record create in audit log

EMP_ID           ENTRY_DATE1                        26/10/2020 17:19:0

Update Trigger:

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_nameBEGIN   — Trigger logic goes here….END;

Drop Trigger:

DROP TRIGGER trigger_name;

Transactions

 Transactions define boundaries around a group of SQL commands such that they either all successfully execute together or not at all.

Transaction Scopes :

 There are following three commands to used control transactions: 

Begin

Commit

Rollback

BEGIN:

Begin is used for start a transaction. Every operation following a begin can be undone until the Commit and Rollback is terminate.  

Syntax:

BEGIN;or BEGIN TRANSACTION;

COMMIT:

The commit command save the work performed by all transactions and changes in to database.

COMMIT;orEND TRANSACTION;

ROLLBACK:

 Similarly, the rollback command undo all the work performed by all transactions since the start of the transaction. A transaction is a scope in which operations are performed and committed, or rolled back, together. 

ROLLBACK;

Here we can use begin commit and rollback like these examples given below:

Example:

BEGIN;

 DELETE FROM COMPANY WHERE AGE = 25;

 ROLLBACK;

 BEGIN;

  DELETE FROM COMPANY WHERE AGE = 25;

  COMMIT;

JOINING Tables:

joins clause is the key to combine data from multiple tables (or relations) and are the first operation(s) of the select command. The result of a join is provided as the input or starting point for all subsequent transaction in the select command.

These are the major types of JOINS:

1) INNER JOIN

2)CROSS JON

3)OUTER JOIN

INNER JOINS:

An inner join is where two tables are joined by a relationship between two columns in the tables.

Here we have a two tables

Id      type_id    name                                                                                                        —– ——– ——————–                                                                                                                        1        10       Baked Beans                                                                                                            2        14        Baked Potato w/Sour                                                                                       3        01        Big Salad                                                                                                          4        05        Broccoli                                                                                                                        5        05        Bouillabaisse                                                                                                      6        02        BLT                                                                                                                           7        12        Bacon Club (no turke                                                                                   8        03        Bologna                                                                                                              9        12        Brisket Sandwich                                                                                                    10      10        Bacon

Id      type_id    name                                                                                                        —– ——– ——————–                                                                                                                       1       10         Banana2       14         Apple 3        01        Guava                                                                                                         4        05        Orange                                                                                                                      5        05        Pineapple                                                                                                     6        02        Mango                                                                                                                        7        12        Watermelon                                                                                  8        03        Grapes                                                                                                       9        12        Coconut

Syntax:

Select * From foods inner join food_types on foods.id = food_types.id

This command provide the intersection between first table(type_id) and second table(id).

CROSS JOINS:

CROSS JOIN matches every row of the first table with every row of the second table.

Syntax:

SELECT … FROM table1 CROSS JOIN table2 …

Example:

SELECT ID,  type_id , name FROM food CROSS JOIN fruit_food;

It shows both table together like:

Id          type_id         name1             10                  baked beans2              10                  Banana3              14                  baked potato4               14                 Apple

OUTER JOINS:

 OUTER JOIN is an extension of INNER JOIN. Though SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL, SQLite only supports the LEFT OUTER JOIN.

SELECT … FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,… ) ..

SELECT … FROM table1 LEFT OUTER JOIN table2 ON conditional_expression …

AUTOINCREMENT: 

AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment.

Syntax:

CREATE TABLE table_name(

   column1 INTEGER AUTOINCREMENT,

   column2 datatype,

   column3 datatype,

   …..

   columnN datatype,

);

Example:

  CREATE TABLE COMPANY(

   ID INTEGER PRIMARY KEY AUTOINCREMENT,

   NAME           TEXT      NOT NULL,

   AGE            INT       NOT NULL,

   ADDRESS        CHAR(50),

   SALARY         REAL

);

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( ‘Rahul’, 32, ‘Delhi’, 20000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES (‘Akshit’, 25, ‘UP’, 15000.00 );

ID          NAME        AGE         ADDRESS     SALARY

———-  ———-  ———-  ———-     ———-

1           Rahul        32          Delhi    20000.0

2           Akshit       25          UP       15000.0