SQL: Structured Query Language
This language is comonnly used to comunicate with any database like oracle,mysql,postgress...etc Sqlite
Oracle and MySQL is used for Enterprise purpose
Sqlite is opensource.
Sqlite is light weight compare to other DB and it is used in our daily activities like ( music systems in car).
Basically we can perform Four operations in Database called CRUD Operations.
C - Create,R- Read,U- Update,D- Delete
SQL Syntax fro Create:
CREATE TABLE Contacts2 (First_Name VARCHAR(30),Second_Name VARCHAR(30),Mobile INTEGER(10));
CREATE table Artist (id INTEGER NOt NULL PRIMARY KEY AUTOINCREMENT,name TEXT); Variable ID as primary Key
Contacts- Table name
firstname and second name,Mobile are columns in table or Schema
Read from Database:
SELECT First_Name FROM 'Contacts2';
SELECT * FROM 'Contacts2';
SELECT * FROM 'Contacts2' WHERE First_Name='Sukesh' ;
SELECT * FROM 'Contacts2' ORDER BY First_Name='*' ;
SELECT * FROM 'Contacts2' ORDER BY First_Name='Sukesh' ;
Order by will sort based on given column and display in ascending order
Insert Value to Database :
INSERT INTO Contacts2 (First_Name,Second_Name,Mobile) VALUES ('Sukesh','Yatakar',9637071671),('Manasa','Yatakar',9637071671)
Delete the contact from
we can delete row based on single value only
DELETE from Contacts2 WHERE First_Name = 'Nandika'
Update in Table with New Values:
Update Contacts2 SET Mobile= '8237378289' where First_Name='Manasa'
Inetgration of database with Python:
If your application needs to support not only the SQLite database but also other databases such as MySQL, PostgreSQL, and Oracle, the PySQLite is a good
Relations:
Primary Key, - Key for for every row, which point to that row in other tables.Ending Point of URL
Logical Key - which is used to search from outside world()
Foriegn Key- Starting point of URL
Artist Table:
CREATE table Artist (id INTEGER NOt NULL PRIMARY KEY AUTOINCREMENT,name TEXT);
Insert into Artist (name) VALUES('Led Zepllin')
Insert into Artist (name) VALUES('AC\DC')
Genere Table:
CREATE table Genere (id INTEGER NOt NULL PRIMARY KEY AUTOINCREMENT,name TEXT);
Insert into genere (name) VALUES('Rock')
Insert into genere (name) VALUES('Metal')
Album Table:
CREATE table Album(id INTEGER NOt NULL PRIMARY KEY AUTOINCREMENT UNIQUE,artist_id INTEGER,title TEXT);
Insert into album (title,artist_id) VALUES('who made who',2) It linked to Artiest 2nd row
Insert into album (title,artist_id) VALUES( 'IV',1) It is linked to artist 1st row
Track Table:
CREATE table Track(id INTEGER NOt NULL PRIMARY KEY AUTOINCREMENT UNIQUE,title TEXT,album_id INTEGER,genere_id INTEGER,len INTEGER,rating INTEGER,counnt INTEGER);
Insert into Track(title,rating,len,counnt,album_id,genere_id) VALUES ('about to rock',5,313,0,1,2),('who made who',5,207,0,1,2)
Joing Tables:
Joining Table Album to Artist
SELECT Album.title,Artist.name from Album join Artist ON Album.artist_id=Artist.id
If we dont give "on" clause it will make all combinations and display
Joining Table Track to Genere
SELECT Track.title,Track.rating,Genere.name from Track join Genere on Track.genere_id=Genere.id
Joinig Table Track to Album:
SELECT Track.title,Album.title from Track join Album on Track.album_id=Album.id
Combing all together:
SELECT Track.title,Album.title,Genere.name,Artist.name from Track join Album join Artist join Genere on Track.album_id=Album.id and Track.genere_id=Genere.id and Album.artist_id=Artist.id
Note: When create Id give this "INTEGER NOT NULL PRIMARY KEY UNIQUE" other wise u get null issue while inserting values.
Many to Many Relations Table:
Create User Table:
CREATE TABLE User (id INTEGER NOT NULL PRIMARY KEY UNIQUE,name TEXT,Email TEXT)
Create Course Table:
CREATE TABLE Member (id INTEGER NOT NULL UNIQUE,title TEXT)
create Member Table:
CREATE TABLE Member (Userid INTEGER,Courseid INTEGER,role INTEGER, PRIMARY KEY (Userid,Courseid))
INsert values to User table :
INSERT INTO User (name,email) Values ('Jae','Jae@gmail.org');INSERT INTO User (name,email) Values ('Ed','Ed@gmail.org');INSERT INTO User (name,email) Values ('Sue','Sue@gmail.org')
INsert into Course table:
INSERT INTO Course (title) Values ('python');INSERT INTO Course (title) Values ('SQL');INSERT INTO Course (title) Values ('java')
Insert Values to Members Table:
INsert into Member (userid,courseid,role) Values(1,1,1);INsert into Member (userid,courseid,role) Values(2,1,0);INsert into Member (userid,courseid,role) Values(3,1,0);INsert into Member (userid,courseid,role) Values(1,2,0);INsert into Member (userid,courseid,role) Values(2,2,0);INsert into Member (userid,courseid,role) Values(2,3,1);INsert into Member (userid,courseid,role) Values(3,3,0)