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