Tuesday, May 27, 2014

SQLite Short Notes [Basics]

Introduction:

 SQL stands for Structured Query Language. It is used for maintaining data held in relational database systems (RDBMS). SQL has many dialects including MySQL, SQLite, Postgre SQL. Most of these dialects existed even before SQL was standardized. To maintain compatibility with previous versions, some of them don’t (fully) follow the standards. SQLite is closer to the standards than most of the others, so we’ll be using SQLite here for the examples.


The SQL language is subdivided into several language elements, including:
·         Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)
·         Expressions, which can produce either scalar values, or tables consisting of columns and rows of data.
·         Predicates, which specify conditions that can be evaluated to SQL three-valued logic (3VL) (true/false/unknown) or Boolean truth values.
·         Queries, which retrieve the data based on specific criteria. This is an important element of SQL.
·         Statements, which may have a persistent effect on schemata and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
·         SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.
·         Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.

Things to note:
i.                    White spaces are ignored.
ii.                  Case doesn’t matter.
iii.                Databases can be manipulated in the memory and can be stored on a disk.
iv.                 The extension for the database file id .db
v.                   The database file is portable i.e. same file can be used on a Windows PC, Linux PC or a Mac

2. SQLite Download Page  http://www.sqlite.org/download.html

    Getting a GUI := http://sqliteman.com/page/4.html


3. SQLite statements:

How to create a table?



i. The CREATE TABLE command is used to create a table.
CREATE TABLE People(
  Id INT,
  Name TEXT,
  Age INT,
  Gender TEXT
);

This creates a table “People” with 4 columns named “Id”, “Name”, “Age”, “Gender” which have respective data types “INT (or can be INTEGER)” and “TEXT” for the rest.

The table is created:

Id
Name
Age
Gender
1
James
33
M
2
Bond
44
M
3
Someone
55
F






ii. The SELECT Keyword (To view particular data from the table)

Suppose we have a table named  “People” either in memory or in a database file .db


Id
Name
Age
Gender
1
James
33
M
2
Bond
44
M
3
Someone
55
F

The select statement returns the rows specified.

SELECT * FROM People;

Returns every row in the database. * for everything
1
James
33
M
2
Bond
44
M
3
Someone
55
F


Or we can user WHERE keyword to return rows matching specific column values

SELECT * FROM People WHERE id = 2;

So that it returns the entire row number 2 (actually returns all the rows where id column has value = 2).
2
Bond
44
M

To count the number of rows present, use the COUNT Keyword.

SELECT COUNT(*) FROM People;

Returns 3 as we have 3 rows in the table.

SELECT COUNT(*) FROM People WHERE Age > 33;

This returns row 2 as only two rows match the condition specified.
AND and OR conditionals can also be used:

SELECT COUNT(*) FROM People WHERE Age>33 and Gender = ‘M’;

Returns 1 as there is only on entry satisfying both the conditions.
A database can consist of multiple tables.

SELECT Name, Age, Gender FROM People;

Returns :
Name
Age
Gender
James
33
M
Bond
44
M
Someone
55
F

We can also change the name to values as needed when retrieving using SELECT as:


SELECT Name AS ‘Person Name’ Age AS AgeOfPerson Gender AS Sex FROM People;

(Note that Strings are to be enclosed in ‘ ‘ )

This returns:

Person Name
AgeOfPerson
Sex
James
33
M
Bond
44
M
Someone
55
F

We can also use LIMIT Keyword to limit the number of results as:

SELECT * FROM People LIMIT = 1;

Returns just a single row.
Id
Name
Age
Gender
1
James
33
M


We can use ORDER BY to sort the results according to some specific order:

SELECT * FROM People ORDER BY Age;

Returns rows with ascending order by age:
Id
Name
Age
Gender
1
James
33
M
2
Bond
44
M
3
Someone
55
F

Or we can specify descending order by

SELECT * FROM People ORDER BY Age DESC;

So that the table is returned but the order or rows is reversed.
Id
Name
Age
Gender
3
Someone
55
F
2
Bond
44
M
1
James
33
M

If we want to return the columns where value is NULL, we don’t use Gender = NULL, instead Gender IS NULL is ued.




iii. INSERT Keyword

INSERT INTO People ( Name, Age, Gender) VALUES(  ‘Jake’, 23, M);

Returns :
Id
Name
Age
Gender
1
James
33
M
2
Bond
44
M
3
Someone
55
F
NULL
Jake
23
M

Notice that we have specified values to be inserted only for Name, Age and Gender Column and not the Id. So NULL is inserted which means “no data is available”
If you want id to increment automatically every time a new entry is made :
You can do so when creating the table:

CREATE TABLE People(
  Id INT PRIMARY KEY,
  Name TEXT,
  Age INT,
  Gender TEXT
);

“PRIMARY KEY” specifies that every time a new entry is made, Id field is incremented.

iv. Updating Data


UPDATE People SET Name = ‘Katy’, Gender = ‘F’ WHERE id = 3;
SELECT * FROM People;


Returns:
Id
Name
Age
Gender
1
James
33
M
2
Bond
44
M
3
Katy
55
F

vi. Deleting Data:


DELETE FROM People WHERE id = 2;
Returns :
Id
Name
Age
Gender
1
James
33
M
3
Katy
55
F

vii. Deleting a TABLE:


DROP TABLE People;


The table is now deleted.
Check if the table exists: (Conditional Check) (Otherwise an error occurs if the table does not exist)


DROP TABLE People IF EXISTS;



---------------------------------------------------------------------------------------------------------------------

[[[  Setting Constraints While Creating Tables: ]]]


CREATE TABLE People(
  Id INT UNIQUE,
  Name TEXT DEFAULT ‘NoName’,
  Age INT NOT NULL,
  Gender TEXT
);

It creates a table that can only have a unique Id for every entry and the default name will be ‘NoName’ if Name is not specified (instead of NULL) , Age can not be set to null so it is necessary to set the age when a new entry is made.
---------------------------------------------------------------------------------------------------------------------

[[[ Changing The Structure of the Table (Schema) with ALTER Keyword ]]]

CREATE TABLE People(
  Id INT,
  Name TEXT,
  Age INT,
  Gender TEXT
);

INSERT INTO People VALUES ( 1, “Katy”, 33, F );
INSERT INTO People VALUES (2, “Perry”, 44, F);
INSERT INTO People VALUES(3, “Some”, 22, F);
INSERT INTO People VALUES(4, “Chick”, 33, F);


Creates a table:
Id
Name
Age
Gender
1
Katy
33
F
2
Perry
44
F
3
Some
22
F
4
Chick
33
F


Suppose that now you need to create a new Column in the table People:


ALTER TABLE People ADD PhoneNumber INTEGER;


Id
Name
Age
Gender
PhoneNumber
1
Katy
33
F
NULL
2
Perry
44
F
NULL
3
Some
22
F
NULL
4
Chick
33
F
NULL


As you can see, NULL Values are inserted in existing Rows.


---------------------------------------------------------------------------------------------------------------------

[[[ The LIKE Keyword ]]]

Is used to filter data.


SELECT * FROM People WHERE Name LIKE ‘%y%’ ORDER BY AGE;


Will result in a table:
Id
Name
Age
Gender
1
Katy
33
F
2
Perry
44
F


The % means – Any string or empty string
So %y% means any number of characters before y or no character before y. Same for characters after.
ORDER BY returns the results in ASCENDING order (You can use DESC to return in Descending order)
Similar to % is _ but _ matches a single character or empty.
The IN Keyword is used in queries like:


SELECT * FROM People WHERE Gender IN (‘F’);


Returns the whole table as every row has a Gender entry ‘F’


SELECT * FROM People WHERE Gender IN(‘M’);


Returns nothing, as expected.

-------------------------------------------------------------------------------------------------------------------- 

[[ Removing Duplicates with SELECT DISTINCT]


Suppose we have a table People:
Id
Name
Age
Gender
1
Katy
33
F
2
Perry
44
F
3
Some
22
F
4
Dude
33
M



SELECT DISTINCT Gender FROM  People;


Returns
Gender
F
M

Now if we run:


SELECT DISTINCT Gender, Age FROM People;


We get:
Gender
Age
F
44
F
55
M
33
We can use ORDER BY Age to sort by age:


SELECT DISTINCT Gender, Age FROM People ORDER BY Age;


Gender
Age
M
33
F
44
F
55

 --------------------------------------------------------------------------------------------------------------------

[[[ Conditional Expressions With CASE ]]]

Suppose We create a table:


CREATE TABLE bool(
  a INT,
  b INT
);
INSERT INTO bool VALUES (1 , 0);


We get a table:
A
b
1
0

Now run:


SELECT 
  CASE a WHEN 1 THEN ‘true’ ELSE ‘false’ END AS boolForA,
  CASE b WHEN 1 THEN ‘true’ ELSE ‘Not True’ END AS boolForB
  FROM bool
;


It returns:
boolForA
boolForB
True
Not True


No comments:

Post a Comment