· 7 years ago · Oct 13, 2018, 07:10 AM
1--Command Line
2>sqlite3 -version
33.8.7.2 2014-11-18 20:57:56 2ab564bf9655b7c7b97ab85cafc8a48329b27f93
4--OR
5>sqlite3 --version
63.8.7.2 2014-11-18 20:57:56 2ab564bf9655b7c7b97ab85cafc8a48329b27f93
7> sqlite3
8sqlite> .quit
9--OR
10sqlite> .q
11--OR
12sqlite> .exit
13--OR
14sqlite> .ex
15
16
17--Git Bash
18$ winpty sqlite3 -version
193.8.7.2 2014-11-18 20:57:56 2ab564bf9655b7c7b97ab85cafc8a48329b27f93
20--OR
21$ winpty sqlite3 --version
223.8.7.2 2014-11-18 20:57:56 2ab564bf9655b7c7b97ab85cafc8a48329b27f93
23$ winpty sqlite3
24> sqlite3
25sqlite> .quit
26--OR
27sqlite> .q
28--OR
29sqlite> .exit
30--OR
31sqlite> .ex
32
33
34$ winpty sqlite3 customerdb.sqlite4
35SQLite version 3.8.7.2 2014-11-18 20:57:56
36Enter ".help" for usage hints.
37sqlite> .databases
38seq name file
39--- --------------- ----------------------------------------------------------
400 main E:\Working\SQL\SQLite\customerdb.sqlite4
41sqlite> DROP TABLE IF EXISTS Customer;
42sqlite> CREATE TABLE Customer
43(
44 CustomerId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
45 FirstName TEXT,
46 LastName TEXT,
47 Technology TEXT
48);
49sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
50VALUES('Bill', 'Gates', 'Microsoft');
51sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
52VALUES('Larry', 'Page', 'Google');
53sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
54VALUES('Steve', 'Jobs', 'Apple');
55sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
56VALUES('Anders', 'Hejlsberg', 'C#');
57sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
58VALUES('Bjarne', 'Stroustrup', 'C++');
59sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
60VALUES('James', 'Gosling', 'Java');
61sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
62VALUES('Scott', 'Guthrie', 'ASP.NET');
63sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
64VALUES('Don', 'Syme', 'F#');
65sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
66VALUES('Dennis', 'Ritchie', 'C');
67sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
68VALUES('Hasso', 'Plattner', 'SAP');
69sqlite> SELECT * FROM Customer;
70sqlite> INSERT INTO Customer(FirstName, LastName, Technology)
71SELECT 'Brendan', 'Eich', 'JavaScript'
72UNION ALL
73SELECT 'Guido', 'van Rossum', 'Python'
74UNION ALL
75SELECT 'Yukihiro', 'Matsumoto', 'Ruby';
76sqlite> SELECT * FROM Customer;
77sqlite> INSERT INTO Customer (FirstName, LastName, Technology)
78VALUES
79('Rasmus', 'Lerdorf', 'PHP'),
80('Martin', 'Odersky', 'Scala'),
81('Donald', 'D. Chamberlin', 'SQL');
82sqlite> SELECT * FROM Customer;
83sqlite> .q
84
85
86$ winpty sqlite3 customerdb.sqlite4
87SQLite version 3.8.7.2 2014-11-18 20:57:56
88Enter ".help" for usage hints.
89sqlite> .help
90.backup ?DB? FILE Backup DB (default "main") to FILE
91.bail on|off Stop after hitting an error. Default OFF
92.clone NEWDB Clone data into NEWDB from the existing database
93.databases List names and files of attached databases
94.dump ?TABLE? ... Dump the database in an SQL text format
95 If TABLE specified, only dump tables matching
96 LIKE pattern TABLE.
97.echo on|off Turn command echo on or off
98.eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN
99.exit Exit this program
100.explain ?on|off? Turn output mode suitable for EXPLAIN on or off.
101 With no args, it turns EXPLAIN on.
102.fullschema Show schema and the content of sqlite_stat tables
103.headers on|off Turn display of headers on or off
104.help Show this message
105.import FILE TABLE Import data from FILE into TABLE
106.indices ?TABLE? Show names of all indices
107 If TABLE specified, only show indices for tables
108 matching LIKE pattern TABLE.
109.load FILE ?ENTRY? Load an extension library
110.log FILE|off Turn logging on or off. FILE can be stderr/stdout
111.mode MODE ?TABLE? Set output mode where MODE is one of:
112 csv Comma-separated values
113 column Left-aligned columns. (See .width)
114 html HTML <table> code
115 insert SQL insert statements for TABLE
116 line One value per line
117 list Values delimited by .separator string
118 tabs Tab-separated values
119 tcl TCL list elements
120.nullvalue STRING Use STRING in place of NULL values
121.once FILENAME Output for the next SQL command only to FILENAME
122.open ?FILENAME? Close existing database and reopen FILENAME
123.output ?FILENAME? Send output to FILENAME or stdout
124.print STRING... Print literal STRING
125.prompt MAIN CONTINUE Replace the standard prompts
126.quit Exit this program
127.read FILENAME Execute SQL in FILENAME
128.restore ?DB? FILE Restore content of DB (default "main") from FILE
129.save FILE Write in-memory database into FILE
130.schema ?TABLE? Show the CREATE statements
131 If TABLE specified, only show tables matching
132 LIKE pattern TABLE.
133.separator STRING ?NL? Change separator used by output mode and .import
134 NL is the end-of-line mark for CSV
135.shell CMD ARGS... Run CMD ARGS... in a system shell
136.show Show the current values for various settings
137.stats on|off Turn stats on or off
138.system CMD ARGS... Run CMD ARGS... in a system shell
139.tables ?TABLE? List names of tables
140 If TABLE specified, only list tables matching
141 LIKE pattern TABLE.
142.timeout MS Try opening locked tables for MS milliseconds
143.timer on|off Turn SQL timer on or off
144.trace FILE|off Output each SQL statement as it is run
145.vfsname ?AUX? Print the name of the VFS stack
146.width NUM1 NUM2 ... Set column widths for "column" mode
147 Negative values right-justify
148sqlite> .databases
149seq name file
150--- --------------- ----------------------------------------------------------
1510 main E:\Working\SQL\SQLite\customerdb.sqlite4
152sqlite> .tables
153Customer
154sqlite> .schema Customer
155CREATE TABLE Customer
156(
157 CustomerId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
158 FirstName TEXT,
159 LastName TEXT,
160 Technology TEXT
161);
162sqlite> .fullschema
163CREATE TABLE Customer
164(
165 CustomerId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
166 FirstName TEXT,
167 LastName TEXT,
168 Technology TEXT
169);
170/* No STAT tables available */
171sqlite> SELECT * FROM Customer;
1721|Bill|Gates|Microsoft
1732|Larry|Page|Google
1743|Steve|Jobs|Apple
1754|Anders|Hejlsberg|C#
1765|Bjarne|Stroustrup|C++
1776|James|Gosling|Java
1787|Scott|Guthrie|ASP.NET
1798|Don|Syme|F#
1809|Dennis|Ritchie|C
18110|Hasso|Plattner|SAP
18211|Brendan|Eich|JavaScript
18312|Guido|van Rossum|Python
18413|Yukihiro|Matsumoto|Ruby
18514|Rasmus|Lerdorf|PHP
18615|Martin|Odersky|Scala
18716|Donald|D. Chamberlin|SQL
188sqlite> SELECT * FROM Customer UNION SELECT * FROM Customer;
1891|Bill|Gates|Microsoft
1902|Larry|Page|Google
1913|Steve|Jobs|Apple
1924|Anders|Hejlsberg|C#
1935|Bjarne|Stroustrup|C++
1946|James|Gosling|Java
1957|Scott|Guthrie|ASP.NET
1968|Don|Syme|F#
1979|Dennis|Ritchie|C
19810|Hasso|Plattner|SAP
19911|Brendan|Eich|JavaScript
20012|Guido|van Rossum|Python
20113|Yukihiro|Matsumoto|Ruby
20214|Rasmus|Lerdorf|PHP
20315|Martin|Odersky|Scala
20416|Donald|D. Chamberlin|SQL
205sqlite> SELECT * FROM Customer UNION ALL SELECT * FROM Customer;
2061|Bill|Gates|Microsoft
2072|Larry|Page|Google
2083|Steve|Jobs|Apple
2094|Anders|Hejlsberg|C#
2105|Bjarne|Stroustrup|C++
2116|James|Gosling|Java
2127|Scott|Guthrie|ASP.NET
2138|Don|Syme|F#
2149|Dennis|Ritchie|C
21510|Hasso|Plattner|SAP
21611|Brendan|Eich|JavaScript
21712|Guido|van Rossum|Python
21813|Yukihiro|Matsumoto|Ruby
21914|Rasmus|Lerdorf|PHP
22015|Martin|Odersky|Scala
22116|Donald|D. Chamberlin|SQL
2221|Bill|Gates|Microsoft
2232|Larry|Page|Google
2243|Steve|Jobs|Apple
2254|Anders|Hejlsberg|C#
2265|Bjarne|Stroustrup|C++
2276|James|Gosling|Java
2287|Scott|Guthrie|ASP.NET
2298|Don|Syme|F#
2309|Dennis|Ritchie|C
23110|Hasso|Plattner|SAP
23211|Brendan|Eich|JavaScript
23312|Guido|van Rossum|Python
23413|Yukihiro|Matsumoto|Ruby
23514|Rasmus|Lerdorf|PHP
23615|Martin|Odersky|Scala
23716|Donald|D. Chamberlin|SQL
238sqlite> .q
239
240
241DROP TABLE IF EXISTS users;
242CREATE TABLE users
243(
244 --id INTEGER NOT NULL,
245 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
246 username VARCHAR(50) NOT NULL,
247 login_date DATE DEFAULT CURRENT_DATE,
248 login_time TIME DEFAULT CURRENT_TIME,
249 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
250 updated_at TIMESTAMP DEFAULT LOCALTIMESTAMP,
251 --CONSTRAINT pk_id PRIMARY KEY(id),
252 CONSTRAINT idx_username UNIQUE(username)
253);
254
255SELECT * FROM users;
256
257INSERT INTO users(username, login_date, login_time, created_at, updated_at)
258VALUES('Foo', '2016-11-06', '10:49:35', '2016-11-06 10:49:35.0', '2016-11-06 10:49:35.0');
259
260SELECT * FROM users;
261
262INSERT INTO users
263VALUES(0, 'Bar', '2016-11-06', '10:49:35', '2016-11-06 10:49:35.0', '2016-11-06 10:49:35.0');
264
265SELECT * FROM users;
266
267SELECT ROWID,* FROM users WHERE ROWID > 0;
268
269--------------------------------------------------------------------------------
270
271-- SQLite JOIN
272
273DROP TABLE Table1
274
275CREATE TABLE Table1
276(
277 ID INT,
278 Value VARCHAR(10)
279);
280
281INSERT INTO Table1 (ID, Value)
282SELECT 1, 'First'
283UNION ALL
284SELECT 2, 'Second'
285UNION ALL
286SELECT 3, 'Third'
287UNION ALL
288SELECT 4, 'Fourth'
289UNION ALL
290SELECT 5, 'Fifth';
291
292
293DROP TABLE Table2
294
295CREATE TABLE Table2
296(ID INT, Value VARCHAR(10));
297
298INSERT INTO Table2 (ID, Value)
299SELECT 1, 'I'
300UNION ALL
301SELECT 2, 'II'
302UNION ALL
303SELECT 3, 'III'
304UNION ALL
305SELECT 6, 'VI'
306UNION ALL
307SELECT 7, 'VII'
308UNION ALL
309SELECT 8, 'VIII';
310
311
312SELECT * FROM Table1;
313SELECT * FROM Table2;
314
315
316/* (INNER) JOIN */
317SELECT t1.*, t2.* FROM Table1 t1
318JOIN Table2 t2 ON t1.ID = t2.ID;
319
320
321/* LEFT (OUTER) JOIN */
322SELECT t1.*, t2.* FROM Table1 t1
323LEFT JOIN Table2 t2 ON t1.ID = t2.ID;
324
325
326/*
327--RIGHT and FULL OUTER JOINs are not currently supported
328SELECT t1.*, t2.* FROM Table1 t1
329RIGHT JOIN Table2 t2 ON t1.ID = t2.ID;
330
331SELECT t1.*, t2.* FROM Table1 t1
332FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID;
333
334SELECT * FROM Table1 t1
335LEFT JOIN Table2 t2 ON t1.id = t2.id
336UNION
337SELECT * FROM Table1 t1
338RIGHT JOIN Table2 t2 ON t1.id = t2.id;
339*/
340
341
342/* CROSS JOIN --NOTE: Pivot t1.ID */
343SELECT t1.*, t2.* FROM Table1 t1
344CROSS JOIN Table2 t2;
345
346--------------------------------------------------------------------------------
347
348-- SQLite nth Highest
349
350$ winpty sqlite3 employeedb.sqlite4
351SQLite version 3.8.7.2 2014-11-18 20:57:56
352Enter ".help" for usage hints.
353sqlite> .databases
354seq name file
355--- --------------- ----------------------------------------------------------
3560 main E:\Working\SQL\SQLite\employeedb.sqlite4
357
358DROP TABLE IF EXISTS Employee;
359
360CREATE TABLE Employee
361(ID INT, Name NVARCHAR(50), Salary numeric(15, 2));
362
363INSERT INTO Employee
364VALUES
365(1, 'A', 10000), --4th
366(2, 'B', 8000), --5th
367(3, 'C', 8000),
368(4, 'D', 6000), --6th
369(5, 'E', 6000),
370(6, 'F', 6000),
371(7, 'G', 5000), --7th
372(8, 'H', 5000),
373(9, 'I', 5000),
374(10, 'J', 5000),
375(11, 'K', 4000), --8th
376(12, 'L', 4000),
377(13, 'M', 3000), --9th
378(14, 'N', 3000),
379(15, 'O', 1000), --10th
380(16, 'P', 14000), --2nd
381(17, 'Q', 14000),
382(18, 'R', 12000), --3rd
383(19, 'S', 12000),
384(20, 'T', 16000), --1st
385(21, 'U', 16000),
386(22, 'V', 16000),
387(23, 'W', 14000),
388(24, 'X', 12000),
389(25, 'Y', 12000),
390(26, 'Z', 10000);
391
392SELECT * FROM Employee;
393
394-- 16000 --1st
395-- 14000 --2nd
396-- 12000 --3rd
397-- 10000 --4th
398-- 8000 --5th
399-- 6000 --6th
400-- 5000 --7th
401-- 4000 --8th
402-- 3000 --9th
403-- 1000 --10th
404
405-- nth Highest -- 5th Highest -- 8000
406SELECT Salary FROM
407(
408SELECT DISTINCT Salary FROM Employee
409ORDER BY Salary DESC LIMIT 5
410)
411A ORDER BY Salary LIMIT 1;
412-- Alternatively
413SELECT *
414FROM Employee Emp1
415WHERE (5 - 1) = (
416SELECT COUNT(DISTINCT(Emp2.Salary))
417FROM Employee Emp2
418WHERE Emp2.Salary > Emp1.Salary) LIMIT 1;
419
420-- 2nd Highest -- 14000
421SELECT MAX(Salary) FROM Employee
422WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);
423
424-- Alternatively
425SELECT MAX(Salary) from Employee
426WHERE Salary <> (select MAX(Salary) from Employee);
427
428--------------------------------------------------------------------------------