Skip to content

Instantly share code, notes, and snippets.

@tdnguyen6

tdnguyen6/.gitignore

Last active March 20, 2020 14:14
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tdnguyen6/35e624c3c88c0f4dc9a3ed83aa2da8fb to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tdnguyen6/35e624c3c88c0f4dc9a3ed83aa2da8fb to your computer and use it in GitHub Desktop.
Download ZIP
HW1-Sqlite
Raw
.gitignore
# ignore binary database files
*.db
Raw
README.md

This file is for storing and sharing of my solutions to homework 1 of the course Principles of Database Management Systems at HCMIU.

The homework 1 has 6 problems corresponding to the 6 sql files here:

[X] Problem 1: 15 points [X] Problem 2: 7 points [X] Problem 3: 7 points [X] Problem 4: 7 points [X] Problem 5: 7 points [X] Problem 6: 7 points

Notice: only the source file containing the commands and comments are uploaded. Any binary files will be omitted (the ones that store the database itself and is opened together with sqlite in .db format by convention).

From a binary file storing the database, to generate a source file using .dump:

sqlite> .output /absolute/path/to/somefile.sql`
sqlite> .dump
sqlite> .quit

or using a shorter syntax

$ sqlite3 someDB.db .dump > somefile.sql

Reverting the process

$ sqlite3 someDB.db < somefile.sql

Raw
data1b.csv
Source Destination
10 5
6 25
1 3
4 4
Raw
data3.csv
Name Type_of_Food Distance_In_Minutes Last_Visit_Date iLike
PapaChicken ChickenCuisines 30 2019-02-01 1
KFC ChickenCuisines 25 2020-02-01 0
Lotteria ChickenCuisines 10 2019-06-03 0
Hanuri KoreanCuisines 60 2019-12-19 1
BurgerKing Burgers&FriedChicken&fastfoods 15 2019-07-07 1
MacDonald Burgers&FriedChicken&fastfoods 45 2020-03-06 NULL
BigU Lunch 75 2020-01-03 NULL
HighlandCOffee CoffeeandBreads 20 2019-03-06 1
Raw
P1.sql
-- 1a. Creating the Database
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Edges (
Source INT,
Destination INT
);
-- 1b
INSERT INTO Edges VALUES(10,5);
INSERT INTO Edges VALUES(6,25);
INSERT INTO Edges VALUES(1,3);
INSERT INTO Edges VALUES(4,4);
-- Change to column mode to view easier
.mode column
.headers on
--1c
SELECT * FROM Edges;
--1d
SELECT Source FROM Edges;
--1e
SELECT * FROM Edges
WHERE Source > Destination;
/**
* 1f: No error. We can insert any type of
* values to the table due to type affinity
* supported by Sqlite.
*
* Each column in Edges table has INTEGER type
* affinity, inserted values will be converted
* to INTEGER if appropriate or even store as TEXT
* or any other type if the conversion cannot proceed.
*
* Here both values of the tuple are text, but
* they represent integer literals. Therefore, both
* values can easily be converted to INTEGER prior
* to the insertion to the table.
*/
INSERT INTO Edges VALUES('-1','2000');
--1f: Proving success
SELECT * FROM Edges;
COMMIT;
Raw
P2.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
-- Problem 2, creating a database as required
CREATE TABLE MyRestaurants (
Name VARCHAR(255),
Type_of_Food VARCHAR(255),
Distance_In_Minutes INT,
Last_Visit_Date VARCHAR(255),
iLike INT
);
COMMIT;
Raw
P3.sql
BEGIN TRANSACTION;
-- Problem 3, insert into the database as required
INSERT INTO MyRestaurants VALUES('PapaChicken','ChickenCuisines',30,'2019-02-01',1);
INSERT INTO MyRestaurants VALUES('KFC','ChickenCuisines',25,'2020-02-01',0);
INSERT INTO MyRestaurants VALUES('Lotteria','ChickenCuisines',10,'2019-06-03',0);
INSERT INTO MyRestaurants VALUES('Hanuri','KoreanCuisines',60,'2019-12-19',1);
INSERT INTO MyRestaurants VALUES('BurgerKing','Burgers&FriedChicken&fastfoods',15,'2019-07-07',1);
INSERT INTO MyRestaurants VALUES('MacDonald','Burgers&FriedChicken&fastfoods',45,'2020-03-06','NULL');
INSERT INTO MyRestaurants VALUES('BigU','Lunch',75,'2020-01-03','NULL');
INSERT INTO MyRestaurants VALUES('HighlandCOffee','CoffeeandBreads',20,'2019-03-06',1);
COMMIT;
BEGIN TRANSACTION;
-- Check Problem 3:
.mode column
.headers on
.width 15 15 15 15 15
SELECT * FROM MyRestaurants;
ROLLBACK;
Raw
P4.sql
BEGIN TRANSACTION;
--Problem 4:
-- First do without headers
.headers off
.print ''
.print '4a No headers'
.print ''
--a.
.mode csv
SELECT * FROM MyRestaurants;
.print ''
.print '4b No headers'
.print ''
--b.
.mode list
.separator " | "
SELECT * FROM MyRestaurants;
.print ''
.print '4c No headers'
.print ''
--c.
.mode column
.width 15 15 15 15 15
SELECT * FROM MyRestaurants;
-- Reset the width
.width 0 0 0 0 0
.print ''
.print '4d: do Again with headers'
.headers on
--d. Do a b c again with headers
.print ''
.print '4a With headers'
.print ''
.mode csv
SELECT * FROM MyRestaurants;
.print ''
.print '4b With headers'
.print ''
.mode list
.separator " | "
SELECT * FROM MyRestaurants;
.print ''
.print '4c With headers'
.print ''
.mode column
.width 15 15 15 15 15
SELECT * FROM MyRestaurants;
-- Reset the width
.width 0 0 0 0 0
ROLLBACK;
Raw
P5.sql
BEGIN TRANSACTION;
.mode column
.width 15 15 15 15 15
.headers on
-- Problem 5:
SELECT Name, Distance_In_Minutes
FROM MyRestaurants
WHERE Distance_In_Minutes <= 20
ORDER BY Name ASC;
ROLLBACK;
Raw
P6.sql
BEGIN TRANSACTION;
.mode column
.width 15 15 15 15 15
.headers on
-- Problem 6:
SELECT *
FROM MyRestaurants
WHERE MyRestaurants.iLike == 1
AND date('now', '-3 month') > Last_Visit_Date;
-- Second way: This still works fine but is less accurate
-- AND CAST ((julianday('now') - julianday(Last_Visit_Date))/(365/12) AS INT) > 3;
ROLLBACK;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment