1. Write the Python code for creating a table based on the following SQL statement:
   • CREATE TABLE phone (
   • phone_id INT,
   • country_code INT NOT NULL,
   • phone_number INT NOT NULL,
   • phone_type VARCHAR(12),
   • PRIMARY KEY(phone_id)
   • );
2. Write the Python code to select rows from the table based on the following SQL statement:
   • SELECT phone_number
   • FROM phone
   • WHERE country_code = 'US'
3. Write the Python code to update rows in the table based on the following SQL statement:
   • UPDATE phone
   • SET phone_type = 'MOBILE
   • WHERE phone_type = 'CELLULAR'
4. Write the Python code to delete rows in the table based on the following SQL statement:
   • DELETE FROM phone
   • WHERE country_code = 'XX'
5. Write the Python code to drop the table based on the following SQL statement:
   • DROP TABLE phone

 

Transcribed Image Text:

CACATIU IMDLC. import salite3 # connecting to the database connection = sqlite3.connect("CIS261.db") # cursor crsr= connection.cursor() # SQL command to create a table in the database sql_command = """CREATE TABLE emp ( staff number INTEGER PRIMARY KEY, fname VARCHAR (20), 1name VARCHAR(30), city VARCHAR (30), gender CHAR(1), joining DATE);""" # execute the statement crsr.execute(sql_command) # close the connection connection.close() SELECTING DATA: #importing the module import sqlite3 # connect with the database. connection = sqlite3.connect("CIS261.db") #cursor object crsr= connection.cursor() # execute the command to select the data from the table emp crsr.execute("SELECT fname, 1name FROM emp Where EmpCity = 'Boston'") UPDATING DATA: import sqlite3 # Connecting to database conn = sqlite3.connect('CIS261.db') # Creating a cursor object using # the cursor() method cursor = conn.cursor() # Updating cursor.execute('''UPDATE emp SET 1name = "Jyoti" WHERE fname="Rishabh"; ''') # Commit your changes in the database conn.commit() # Closing the connection. conn.close() DELETING DATA: # Import module import sqlite3 # Connecting to the database conn = sqlite3.connect ('CIS261.db') # Creating a cursor object using # the cursor() method cursor = conn.cursor() # Updating cursor.execute(' 'DELETE FROM emp WHERE fname="Rishabh"; ''') # Commit your changes in the database conn.commit() # Closing the connection conn.close() DROP TABLE: # Import module import sqlite3 # Connecting to database conn = sqlite3.connect('CIS261.db') # Creating a cursor object using # the cursor() method cursor conn.cursor() # Updating cursor.execute(''DROP TABLE Student;''') # Commit your changes in the database conn.commit() # Closing the connection conn.close() CACATIU IMDLC. import salite3 # connecting to the database connection = sqlite3.connect("CIS261.db") # cursor crsr= connection.cursor() # SQL command to create a table in the database sql_command = """CREATE TABLE emp ( staff number INTEGER PRIMARY KEY, fname VARCHAR (20), 1name VARCHAR(30), city VARCHAR (30), gender CHAR(1), joining DATE);""" # execute the statement crsr.execute(sql_command) # close the connection connection.close() SELECTING DATA: #importing the module import sqlite3 # connect with the database. connection = sqlite3.connect("CIS261.db") #cursor object crsr= connection.cursor() # execute the command to select the data from the table emp crsr.execute("SELECT fname, 1name FROM emp Where EmpCity = 'Boston'") UPDATING DATA: import sqlite3 # Connecting to database conn = sqlite3.connect('CIS261.db') # Creating a cursor object using # the cursor() method cursor = conn.cursor() # Updating cursor.execute('''UPDATE emp SET 1name = "Jyoti" WHERE fname="Rishabh"; ''') # Commit your changes in the database conn.commit() # Closing the connection. conn.close() DELETING DATA: # Import module import sqlite3 # Connecting to the database conn = sqlite3.connect ('CIS261.db') # Creating a cursor object using # the cursor() method cursor = conn.cursor() # Updating cursor.execute(' 'DELETE FROM emp WHERE fname="Rishabh"; ''') # Commit your changes in the database conn.commit() # Closing the connection conn.close() DROP TABLE: # Import module import sqlite3 # Connecting to database conn = sqlite3.connect('CIS261.db') # Creating a cursor object using # the cursor() method cursor conn.cursor() # Updating cursor.execute(''DROP TABLE Student;''') # Commit your changes in the database conn.commit() # Closing the connection conn.close()

Answer rating: 100% (QA)

You can use Python with a library like SQLAlchemy to interact with a SQL database Heres how you can ... View the full answer