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("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("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("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 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 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 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") 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") # 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;")
for invoice in invoices_de: print(invoice) print("----------------") print("3) Count how many albums are in the database.") album_num = chinook.execute("SELECT COUNT(*) FROM Album;") print(album_num) print(album_num[0][0]) print("----------------") print("4) How many customers are from France?") fr_num = chinook.execute(""" SELECT COUNT(*) FROM Customer WHERE Customer.Country = 'France'; """) print(fr_num[0][0]) print("--------ALTERNATIVE (pretty print)--------") # pretty_print() does the same as execute(), except that it also prints the data in a nice table in the terminal chinook.pretty_print(""" SELECT COUNT(*) FROM Customer WHERE Customer.Country = 'France'; """)
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 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)