Basic SQLDB Learning

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)