Some Feature not Supported in SQLite

Feature
Support
Not Support
Right Outer Join
No
Only Left Outer Join
Full Outer Join
No
Only Left outer Join
Alter Table
Rename Table
Add Column
Alter Table commands are supported
Drop Column
Alter Column
Add Constraint
Above commands are not supported
View
Yes(Read Only)
But View is read only
You can’t execute following command in view
Delete
Insert
Update



SQLite

How to create Database?

How to Get All Database Name?

SQLite>.Database
Above query retrieve all database name with location
“ Main “ is default database name .

How to Create New Database Name?
Database name same as “Main “but location of database storage we can store different name
Like “Testdb.db

Step 1:

Go to Run

Step 2:

Type CMD

Step 3:

Sync location. Where exe located
My Exe located in C:\Sqlite.exe

Step 4:

C:\>sqlite3.exe Sample.db
Example Screen below

.db file store location ? Is it store C:\Sample.db?
No
C:\Users\<YourName>\AppData\Local\VirtualStore\sample.db

How to modify above location?

Step 1:

Go to My computer

Step 2:

Right Click èProperty

Step 3:

Advanced System Settings

Step 4:

Environment variables

Step 5:


Note:

Whenever open New Sqlite window execute below query
C:\>sqlite3.exe Sample.db












SQLite

Define:

SQLite is an embedded relational database engine.

Other Names:
Self –Contained(no external dependencies)
Server Less
Zero – Configuration
Transactional Sql Database Engine

The SQLite engine is not a standalone process. Instead, it is statically or dynamically linked into the application

History:

Started Year 2000
SQLite was written in the C Programming language

Why SQLite?

The SQLite library is small
No Server
No installation
It is require less than 300 KIB
SQLite located any ware in directory
Cross platform file, it can be used various operating system(MAC OS,Windows,Linux,Unix)

step 1:

CREATE TABLE COLLEGE(SID NVARCHAR(10),SNAME NVARCHAR(20),PLACE NVARCHAR(20))

Step 2:

INSERT INTO COLLEGE VALUES(1,'SURESH',NULL),(NULL,'RAMESH','PUDUKKOTTAI'),(3,'RAMU','COIMBATORE')

-- ABOVE QUERY WILL RUN ONLY SQL 2008,2012

STEP 3:

SELECT COALESCE(SID,SNAME,PLACE) AS 'STUDNAME' FROM COLLEGE

STEP 4:

Post your Ans http://jssql.blogspot.in/







A trigger is a SQL procedure that initiates an action .when an event (INSERT,UPDATE,DELETE) occures. Triggers can be viewed as similar to stored procedure in that both consist of procedural logic that is stored at the database level.

STEP 1:

CREATE TABLE Student(SID int IDENTITY, SNAME varchar(10))

Create a trigger that displays the count student table when a row is inserted into the table to which it is attached.

STEP 2:

CREATE TRIGGER tr_student_insert
ON STUDENT
FOR INSERT
AS
SELECT COUNT(*) AS 'NUMBER OF RECORD' FROM Student

STEP 3:

INSERT INTO Student VALUES('SUTHAHAR')

RESULT



Use the inserted and deleted Tables

STEP 1:

CREATE TABLE STUMARK(SID int IDENTITY, MARK NUMERIC(10))

STEP 2:

CREATE TRIGGER tr_STUMARK_insert
ON STUMARK
FOR INSERT
AS
IF((SELECT MARK FROM inserted) < 40)
BEGIN
PRINT 'FAIL'
END
ELSE
BEGIN
PRINT 'PASS'
END

STEP 3:

INSERT INTO STUMARK VALUES(78)

OUTPUT


AFTER UPDATE

CREATE TRIGGER tr_STUDENT_UPDATE
ON STUDENT
AFTER UPDATE
AS
AFTER DELETE
CREATE TRIGGER tr_STUDENT_DELETE
ON DELETE
AFTER UPDATE
AS
INSTEAD OF TRIGGERS
CREATE TRIGGER tr_STUDENT_INSERT_InsteadOf
ON STUDENT
INSTEAD OF INSERT
AS
PRINT 'Updateable Views are Messy'
go




The CUBE,COMPUTE,COMPUTE BY and ROLLUP operators are useful in generating reports that contain subtotals and totals.

There are extensions of the GROUP BY clause.

The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY; however, ROLLUP has these advantages:
ROLLUP returns a single result set; COMPUTE BY returns multiple result sets that increase the complexity of application code.

ROLLUP can be used in a server cursor; COMPUTE BY cannot.

The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

STEP 1:

create table JSSTUD(COL nvarchar(20),PER numeric(10))

STEP 2:

INSERT INTO JSSTUD VALUES('JJ COLLEGE',100)
INSERT INTO JSSTUD VALUES('JJ COLLEGE',200)
INSERT INTO JSSTUD VALUES('JJ COLLEGE',300)
INSERT INTO JSSTUD VALUES('PSG',150)
INSERT INTO JSSTUD VALUES('PSG',50)
INSERT INTO JSSTUD VALUES('PSG',1050)
INSERT INTO JSSTUD VALUES('GRG',2000)

STEP 3:

ROLLUP
SELECT COL,SUM(PER) FROM JSSTUD GROUP BY COL
WITH ROLLUP


OR
COMPUTE bY

SELECT COL,PER FROM JSSTUD ORDER BY COL
COMPUTE SUM(PER) BY COL



OR

SELECT COL,PER FROM JSSTUD ORDER BY COLCOMPUTE SUM(PER)

CUBE

select COL, sum(per)  from JSSTUD group by COL with CUBE

Error in SQL Server

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager. The specified module could not be found. [0x8007007e]


Solution 

Start ==> Run => Type below command
mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Hope it will work for you .
CREATE TABLE WITHOUT PRIMARY KEY

CREATE TABLE JSSTUDTABLE (SNO NUMERIC(10) NOT NULL)ADD PRIMARY KEY EXISTING TABLE

ALTER TABLE JSSTUDTABLE ADD PRIMARY KEY (SNO);

REMOVE PRIMARY KEY EXISTING TABLE

ALTER TABLE JSSTUDTABLE DROP constraint PK__JSSTUDTA__CA1EE06C63D8CE75
“ COALESCE “ Method in Sql Server

Table Have so many column like that

Name
HomeAddress
OfficeAddress
Temp_Address
Suthahar
Null
Null
Pudukkottai
Suresh
Pullanviduthi
Null
Null
Sumathi
Null
Alangudi
Null
Sujatha
Pullanviduthi
Pudukkottai
Trichy

If someone have home address or office address suppose if you display available first record means you can use coalesce method
CREATE TABLE devenvexe(Name nvarchar(10),homeaddress nvarchar(10),officeaddress nvarchar(10), Temp_addressnvarchar(10))

Query :

SELECT name,COALESCE(homeaddree,officeaddress,temp_address) Addreess FROM devenvexe

Output:

Name
Address
Sutahhar
Pudukkottai
Suresh
Pullanviduthi
Sumathi
Alangudi
Sujatha
Pullanviduthi

Concatinate Column in Single Column

CREATE TABLE JS(SNAME NVARCHAR(10))
INSERT INTO JS VALUES('SUTHAHAR')
INSERT INTO JS VALUES('SURESH')
INSERT INTO JS VALUES('SUMATHI')
INSERT INTO JS VALUES('SUJATHA')

DECLARE @VAL NVARCHAR(1024)
SELECT @VAL=COALESCE(@VAL+',', '')+ SNAME FROM JS
SELECT JS= @VAL

Output:

Suthahar,Suresh,Sumathi,Sujatha

View in Sql Server

  • View is a virtual table
  • It s contains columns and data in different table
  • View does not contain any data directly. Its a set of select query
Table 1

Sno
Sname
Table 2
Sno
Lname
             
View_table1_table2

Sname
Lname

Above drawing table 1 and table we will write join query after we can create view

Syntax

CREATE VIEW JS_VIEW_NAME
AS
[SELECT STATEMENT]

Why we are use View ?
 View is used for security mechanism .if you restricted particular column for users .

Sql Server Syntex for View
CREATE VIEW
CREATE VIEW JS_VIEW
AS
SELECT *FROM STUD A,LIB L WHERE A.SNO=L.SON
ALTER VIEW
ALTER VIEW JS_VIEW
AS
SELECT *FROM STUDENTTABLE A,LIB L WHERE A.SNO=L.SON
SELECT VIEW
SELECT*FROM JS_VIEW
DROP VIEW:
DROP VIEW JS_VIEW

Featured Post

Improving C# Performance by Using AsSpan and Avoiding Substring

During development and everyday use, Substring is often the go-to choice for string manipulation. However, there are cases where Substring c...

MSDEVBUILD - English Channel

MSDEVBUILD - Tamil Channel

Popular Posts