from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("hiking.sqlite")
db.execute("DROP TABLE;")
db.execute(
"CREATE TABLE User(id integer primary key autoincrement, name text, age integer);"
)
db.print_tables(
verbose=True) #ta verbose v oklepaju je, da izpiše datile tabele
#db.execute("INSERT INTO User(name, age) VALUES ('rajo', 29);")
result = db.execute("SELECT * FROM User;")
print(result)
db.pretty_print("SELECT*FROM User;")
db.execute("""
UPDATE User
SET age=22
WHERE id=1;
""")
db.execute("DELETE FROM User WHERE id >2;")
result = db.execute("SELECT * FROM User;")
db.pretty_print("SELECT*FROM User;")
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("hiking.sqlite")
db.execute("""
CREATE TABLE IF NOT EXISTS HikingGroupUser(
id integer primary key autoincrement,
UserId integer,
HikingGroupId integer
);
""")
db.pretty_print("""
SELECT
HikingGroup.Destination AS Destination,
HikingGroup.Name AS [Group],
COUNT(*) AS UsersInDestination
FROM User
JOIN HikingGroup
JOIN HikingGroupUser
USING (UserId, HikingGroupId)
""")
# db.pretty_print("SELECT * FROM HikingGroupUser")
# db.print_tables(verbose=True)
#This database has many tables. Write an SQL command that will print Name from the table Artist (for all the database entries)
#Print all data from the table Invoice where BillingCountry is Germany
#Count how many albums are in the database. Look into the SQL documentation for help. Hint: look for Min&Max and Count, Avg, Sum.
#How many customers are from France?
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("Chinook_Sqlite.sqlite")
#TASK 1
db.execute("SELECT Name FROM Artist;")
result = db.execute("SELECT Name FROM Artist;")
db.pretty_print("SELECT Name FROM Artist;")
#TASK 2
db.execute("SELECT * FROM Invoice WHERE Invoice.BillingCountry = 'Germany';")
db.pretty_print(
"SELECT * FROM Invoice WHERE Invoice.BillingCountry = 'Germany';")
#TASK 3
db.execute("SELECT COUNT(*) FROM Album;")
db.pretty_print("SELECT COUNT(*) FROM Album;")
#TASK 4
db.execute("SELECT COUNT(*) FROM Customer WHERE Customer.Country = 'France';")
db.pretty_print(
"SELECT COUNT(*) FROM Customer WHERE Customer.Country = 'France';")
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("Student.sqlite")
db.execute(
"CREATE TABLE IF NOT EXISTS Student (id integer primary key autoincrement, name text, grade text);"
)
#db.execute("INSERT INTO Student(name, grade) VALUES ('TEENA', 'A');")
#db.execute("INSERT INTO Student(name, grade) VALUES ('ETHAN', 'A');")
#result= db.execute("SELECT * FROM Student;")
#print(result)
#db.execute("UPDATE Student SET grade='A+' WHERE id=2;")
#db.execute("ALTER TABLE Student ADD age text;")
#db.execute("UPDATE Student SET age=20 WHERE id=2;")
db.execute("DROP TABLE Student;")
db.pretty_print("SELECT * FROM Student;")
db.print_tables(verbose=True)
from smartninja_sql.sqlite import SQLiteDatabase
# create database
db = SQLiteDatabase() # database saved in RAM (is deleted after program ends)
# db = SQLiteDatabase("hiking.sqlite") # database stored on disk
# create a User table
db.execute("""CREATE TABLE IF NOT EXISTS User(
id integer primary key autoincrement,
name text,
age integer);
""")
db.print_tables(verbose=True)
# insert data into table
db.execute("INSERT INTO User(name, age) VALUES ('Matt', 31);")
# query data from the table
result = db.execute("SELECT * FROM User;")
print(result)
# update data in the table
db.execute("""
UPDATE User
SET age=22
WHERE id=1;
""")
result = db.execute("SELECT * FROM User;")
print(result)
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("BlogDB_main.sqlite")
db.execute("""CREATE TABLE IF NOT EXISTS User(
UserId integer primary key autoincrement,
Username text,
Mailadress text,
FirstName text,
LastName text,
Birthday real,
Password text);
""")
db.pretty_print("SELECT * FROM User;")
db.execute("""CREATE TABLE IF NOT EXISTS Posts(
PostId integer primary key autoincrement
);
""")
db.pretty_print("SELECT * FROM Posts;")
db.execute("""CREATE TABLE IF NOT EXISTS Posts_Default(
PostDefaultId integer primary key autoincrement,
PostName text,
PostText text);
""")
db.pretty_print("SELECT * FROM Posts_Default;")
from smartninja_sql.sqlite import SQLiteDatabase
chinook = SQLiteDatabase("Chinook_Sqlite.sqlite"
) # download and save this DB first! (see readme.md)
chinook.print_tables(
verbose=True) # just to see the tables and fields in the database
print("----------------")
print(
"1) Write an SQL command that will print Name from the table Artist (for all the database objects)."
)
artist_names = chinook.execute("SELECT Artist.Name FROM Artist;")
for artist_name in artist_names:
print(artist_name[0])
print("----------------")
print(
"2) Print all data from the table Invoice where BillingCountry is Germany."
)
invoices_de = chinook.execute("""
SELECT *
FROM Invoice
WHERE Invoice.BillingCountry = 'Germany';
""")
for invoice in invoices_de:
print(invoice)