Write a SQL create table statement for a table named \"student\" in the \"colleg

Question

Write a SQL create table statement for a table named "student" in the "college" database. The table has three attributes. "class" varying character string not exceeding 25 bytes, "semester", 15 characters, and "grade" numeric supporting real number. All attribute must be populated with values. write a complete python program that connects to the "college" Using the table database and executes a select statement that returns all rows from the student table if the grade is 3.5 or higher Write a stored procedure named myProc that implements the sql statement

Explanation / Answer

SQL create table statement:

CREATE TABLE Student (
class varchar(25), ----------------------------------------stores upto 25bytes of data
semester varchar(15), -----------------------------------stores upto 15bytes or characters of data
grade float(2) -----------------------------------------------stores the real number with 2 digits after the decimal point
);

Populating values to the attributes :
INSERT INTO Student (class, semester,grade) VALUES ('class 2', 'semester 2', '3.4');
INSERT INTO Student (class, semester,grade) VALUES ('class 4', 'semester 5', '8.9');
INSERT INTO Student (class, semester,grade) VALUES ('class 3', 'semester 4', '4.5');

PYTHON PROGRAM:

------------------------------------------------------------------------------------------------------------------------------------

;function definition

import sqlite3 ------------------------------------------importing this library if we are connecting MYSQL,DB2 or some other database but all of them provides same function when connected to any database. (you can use import MYSQLdb for MYSQL database)

def get_Results(database_file) :
connection = sqlite3.connect(database_file)   
cursor = connection.cursor()
cursor.execute("SELECT * FROM Student WHERE grade >= '3.5'; ")
results = cursor.fetchall()
for r in results :
print(r)
cursor.close()
connection.close()

;function call
get_Results('college.db')

--------------------------------------------------------------------------------------------------------------------------------------------------

Output :
class 4 semester 5 8.90
class 3 semester 4 4.50

------------------------------------------------------------------------------------------------------------------------------------------------

Requirement has not mentioned any SQL statement to write the procedure so using SQL statement mentioned in the PYTHON program.

SELECT * FROM Student WHERE grade >= 3.5

//Stored procedue is created as below

CREATE PROCEDURE usp_StudentCreate
AS
SELECT *
FROM Student
WHERE grade >= 3.5

//Command to execute this stored procedure
EXEC usp_StudentCreate

----------------------------------------------------------------------------------------------------------------------------------------

Please be noted that , since this is database problem...the statements may differ from one database to other. So please check and if needed, please change few statments according to your requirements.

Thank you.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.