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)
#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
chinook = SQLiteDatabase("Chinook_Sqlite.sqlite")
chinook.pretty_print("""
SELECT
Album.ArtistId AS Album,
Artist.ArtistId AS Artist
FROM Album
JOIN Artist
USING (ArtistId)
WHERE Artist.ArtistId=1;
""")
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("Chinook_Sqlite.sqlite")
#What order (Invoice) was the most expensive? Which one was the cheapest?
db.pretty_print(
"""SELECT Invoice.InvoiceId, Invoice.InvoiceDate, Invoice.CustomerId, MAX(Invoice.Total)
FROM Invoice;""")
db.pretty_print(
"""SELECT Invoice.InvoiceId, Invoice.InvoiceDate, Invoice.CustomerId, MIN(Invoice.Total)
FROM Invoice;""")
#Which city (BillingCity) has the most orders?
db.pretty_print("""SELECT Invoice.BillingCity, COUNT(*) AS CountOrders
FROM Invoice
GROUP BY Invoice.BillingCity
ORDER BY CountOrders DESC;""")
#Calculate (or count) how many tracks have this MediaType: Protected AAC audio file.
db.pretty_print("""SELECT MediaType.Name, COUNT(Track.MediaTypeId)
FROM Track
INNER JOIN MediaType ON MediaType.MediaTypeId=Track.MediaTypeId
WHERE MediaType.Name='Protected AAC audio file';""")
#Find out what Artist has the most albums? (hint: check ORDER BY)
db.pretty_print("""SELECT Artist.Name, COUNT(*) AS CountAlbum
FROM Artist
INNER JOIN Album ON Artist.ArtistId = Album.ArtistId
GROUP BY Album.ArtistId
ORDER BY CountAlbum DESC;""")
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("Chinook_Sqlite.sqlite")
db.print_tables(verbose=True)
#db.pretty_print("SELECT * FROM Invoice LIMIT 10;")
#db.pretty_print("SELECT * FROM Customer LIMIT 10;")
db.pretty_print("SELECT * FROM Track LIMIT 10;")
# What order (Invoice) was the most expensive? Which one was the cheapest?
db.pretty_print("SELECT MAX(Invoice.Total) AS Max_Total,* FROM Invoice;")
db.pretty_print("SELECT MIN(Invoice.Total) AS Min_Total,* FROM Invoice;")
# Which city (BillingCity) has the most orders?
db.pretty_print(
"""SELECT Invoice.BillingCity, COUNT(*) AS 'Invoice_Total_Count'
FROM Invoice GROUP BY Invoice.BillingCity
ORDER BY Invoice_Total_Count DESC limit 10;""")
# Calculate (or count) how many tracks have this MediaType: Protected AAC audio file.
db.pretty_print("""SELECT COUNT(*) AS 'Protected ACC AF' FROM Track
JOIN MediaType ON MediaType.MediaTypeId = Track.MediaTypeId
WHERE MediaType.Name='Protected AAC audio file';""")
# Find out what Artist has the most albums? (hint: check ORDER BY)
db.pretty_print("""SELECT Artist.Name, COUNT(*) AS 'Album_Count' FROM Artist
INNER JOIN Album ON Album.ArtistId= Artist.ArtistId
GROUP BY Album.ArtistId
ORDER BY Album_Count DESC LIMIT 10;""")
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("Chinook_Sqlite.sqlite")
db.print_tables(verbose=True)
print('---------------------------------')
a = db.pretty_print("""
SELECT
Artist.Name
FROM Artist;
""")
print('---------------------------------')
b = db.pretty_print("""
SELECT *
FROM Invoice
Where Invoice.BillingCountry = "Germany";
""")
print('---------------------------------')
c = db.pretty_print("""
SELECT COUNT (*)
FROM Album;
""")
print('---------------------------------')
from smartninja_sql.sqlite import SQLiteDatabase
from HW_Lesson1 import hw_lesson1
db = SQLiteDatabase("testing.sqlite")
# db.execute("""CREATE TABLE IF NOT EXISTS User (
# id integer PRIMARY KEY AUTOINCREMENT,
# name text NOT NULL,
# age integer);""")
#
# db.print_tables(verbose=True)
hw_lesson1()
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)
chinook.pretty_print("SELECT * FROM Album;")
chinook.pretty_print("SELECT * FROM Artist;")
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
chinook = SQLiteDatabase('Chinook_Sqlite.sqlite')
chinook.print_tables(verbose=True)
chinook.pretty_print("SELECT Name FROM Artist;")
chinook.pretty_print("""SELECT * FROM Invoice
WHERE BillingCountry='Germany';
""")
chinook.pretty_print("SELECT COUNT(AlbumId) FROM Album;")
chinook.pretty_print("""SELECT COUNT(*)
FROM Customer
WHERE Country='France'""")
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("Chinook_Sqlite.sqlite")
# Print the number of albums in the table
db.pretty_print(
"SELECT AlbumId FROM Album WHERE AlbumId=(SELECT max(AlbumId) FROM Album)")
# Print all the artist names in the table
db.pretty_print("SELECT Name FROM Artist")
# Print all the invoices where BillingCountry is Germany
db.pretty_print("SELECT * FROM Invoice WHERE BillingCountry = 'Germany'")
# Print how many customers are from France
db.pretty_print("SELECT COUNT(Country) FROM Customer WHERE Country = 'France'")
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)
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(
"/Users/peter/Documents/Smartninja/WD2/20200113_Lesson1/Chinook_Sqlite.sqlite"
)
#This database has many tables. Write an SQL command
#that will print Name from the table Artist (for all the database entries)
db.pretty_print("SELECT Name FROM Artist;")
#Print all data from the table Invoice where BillingCountry is Germany
db.pretty_print("SELECT * FROM Invoice WHERE BillingCountry = '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.
db.pretty_print("SELECT COUNT(*) FROM Album;")
#How many customers are from France?
db.pretty_print(
"SELECT COUNT(Country) FROM Customer WHERE Country = 'France';")
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)
from smartninja_sql.sqlite import SQLiteDatabase
db = SQLiteDatabase("Chinook_Sqlite.sqlite")
db.pretty_print("SELECT * FROM Invoice LIMIT 10;")
db.pretty_print("SELECT * FROM Customer LIMIT 10;")
#Inner Join
db.pretty_print("""
SELECT Invoice.CustomerId, Invoice.BillingCity, Customer.FirstName, Customer.LastName, Invoice.Total
FROM Invoice INNER JOIN Customer ON Invoice.CustomerId=Customer.CustomerId
WHERE Invoice.BillingCity='Stockholm' limit 10;
""")
# Sum/ AVG/ COUNT/ MAX/ MIN
db.pretty_print("""
SELECT COUNT(Invoice.Total) AS 'COUNT', AVG(Invoice.Total) AS 'AVG'
FROM Invoice INNER JOIN Customer ON Invoice.CustomerId=Customer.CustomerId
WHERE Invoice.BillingCity='Stockholm' limit 10;
""")
# Group By
db.pretty_print("""
SELECT Customer.FirstName, SUM(Invoice.Total)
FROM Invoice INNER JOIN Customer ON Invoice.CustomerId=Customer.CustomerId
GROUP BY Invoice.CustomerId limit 10;
""")
#db.print_tables(verbose=True)
