Brief on Structured Query Language with a python program. Explain the function involved in the creation of a database table in python. (08 Marks)
This question was asked in Python Application Programming Jan 2019 question Paper for 8 Marks.
Solution:
A relational database management system is made up of components such as tables, rows, and columns. The columns generally have different attributes such as text, numeric, date, time, or date data. When we create a new table, we indicate the names and types of each column:
CREATE TABLE student (name TEXT, roll_no INTEGER)
SQL INSERT command is used to insert a new row into a table.
INSERT INTO student (name, roll_no) VALUES ('Rahul', 15)The INSERT statement specifies the table name, then a list of the field or column names that you would like to set in the new row followed by keyword VALUES, and finally a list of corresponding values for each of the columns to be inserted.
The SQL SELECT command is used to retrieve rows and columns from a database. Using the SELECT statement we can specify which field or column one would like to retrieve, also one can use the WHERE clause to select a particular row. an optional ORDER BY clause can be used with the SELECT statement which controls the sorting of the returned rows.
SELECT * FROM student WHERE title = 'Rahul'
* returns all the columns for each row that matches the WHERE clause in the SELECT statement.
Following logical operations are allowed in a WHERE clause <, >, <=, >=, !=, AND, OR and parentheses to build your logical expressions.
You can request that the returned rows be sorted by one of the fields as follows:
SELECT name, roll_no FROM student ORDER BY roll_no
One can remove a row using the DELETE statement, you need a WHERE clause. The WHERE clause determines which rows are to be deleted:
DELETE FROM student WHERE name= 'Rahul'
It is possible to UPDATE a value of a column or columns within one or more rows in a table using the SQL UPDATE statement as follows:
UPDATE student SET roll_no = 16 WHERE name = 'Rahul'
The UPDATE statement takes table name followed by a list of fields and values to change after the SET keyword and then a WHERE clause optional which is used to select the rows that are to be updated.
Program to create a table, insert data into the table and delete rows having values 15 for roll_no and Rahul for the name. This demonstrates the Structured Query Language with a python program.
import sqlite3
conn = sqlite3.connect('student.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Student)
cur.execute('CREATE TABLE Student (Name TEXT, Roll_No INTEGER)')
cur.execute('INSERT INTO Student (Name, Roll_No) VALUES (?, ?)', ('Rahul', 20))
cur.execute('INSERT INTO Student (Name, Roll_No) VALUES (?, ?)', ('Sachin', 15))
conn.commit()
cur.execute('SELECT Name, Roll_No FROM Student')
print (''Student: Before Delete')
print ('-------------')
for row in cur:
print(row)
cur.execute('DELETE FROM Student WHERE Roll_No = 15 and Name = \'Sachin\'')
conn.commit()
cur.execute('SELECT Name, Roll_No FROM Student')
print (''Student: After Delete')
print ('-------------')
for row in cur:
print(row)
cur.close() Output
Student: Before Delete
-------------
('Rahul', 20)
('Sachin', 15)
Student: After Delete
-------------
('Rahul', 20) Click here to read Solution to Python Application Programming Question Paper Jan 2019 15CS664
Summary:
This tutorial discusses the usage of Structured Query Language with a simple python program. If you like the tutorial do share it with your friends. Like the Facebook page for regular updates and YouTube channel for video tutorials.