1) Create table COUNTRY with the following colums:county_code Integercountry_Name Varchar(50) 2) Create table CITY with the following colums:county_code Integercity_code Integercity_name Varchar(50) 3) Two of the queries must include JOIN operation onebeing LEFT OUTER JOIN. Write SQL queries similar to { # LIKE keyword sql = "SELECT * FROM customers WHERE address LIKE '%way%'" c.execute(sql) myresult = c.fetchall() for x in myresult: print(x)

# Preventing SQL injection sql = "SELECT * FROM customers WHERE address = ?" adr = ("Park Lane 38", ) c.execute(sql, adr) myresult = c.fetchall() for x in myresult: print(x)

# ORDER BY name sql = "SELECT * FROM customers ORDER BY name" c.execute(sql) myresult = c.fetchall() for x in myresult: print(x)

# DELETE sql = "DELETE FROM customers WHERE address = 'Mountain 21'" c.execute(sql) c.execute("COMMIT") print(c.rowcount, "record(s) deleted")

# UPDATE sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'" c.execute(sql) c.execute("COMMIT") print(c.rowcount, "record(s) affected")

#LIMIT number of rows to be displayed c.execute("SELECT * FROM customers LIMIT 3") myresult = c.fetchall() for x in myresult: print(x)

#LIMIT by starting from another position c.execute("SELECT * FROM customers LIMIT 5 OFFSET 2") myresult = c.fetchall() for x in myresult: print(x)

# JOIN

# First create dependant table c.execute("CREATE TABLE dependant (cust_id int, dep_name VARCHAR(50))") c.execute("INSERT INTO dependant (cust_id, dep_name) VALUES (1, 'Ayse Yilmaz')") c.execute("INSERT INTO dependant (cust_id, dep_name) VALUES (5, 'Veli Ayvaz')") c.execute("INSERT INTO dependant (cust_id, dep_name) VALUES (2, 'Okan Durmaz')") c.execute("COMMIT")

# SELECT with JOIN c.execute( "SELECT cus.id, cus.name, dep.dep_name\ FROM customers cus \ JOIN dependant dep ON cus.id = dep.cust_id" ) myresult = c.fetchall() for x in myresult: print(x)

# LEFT JOIN c.execute( "SELECT cus.id, cus.name, dep.dep_name\ FROM customers cus \ LEFT JOIN dependant dep ON cus.id = dep.cust_id" ) myresult = c.fetchall() for x in myresult: print(x)

#RIGHT JOIN not supported in SQLite yet. # Prevent SQL injection with LIKE operator #Display all fields from customers who live in an address including "way" in it in such a way to prevent SQL injection. sql = "SELECT * FROM customer WHERE address LIKE ?" adr = '%way%' c.execute(sql, (adr,)) myresult = c.fetchall() for x in myresult: print(x) }