Spaces:
Sleeping
Sleeping
| # import sqlite3 | |
| # # Create and initialize the database | |
| # def initialize_database(): | |
| # conn = sqlite3.connect("college.db") | |
| # cursor = conn.cursor() | |
| # # Create student table | |
| # cursor.execute(""" | |
| # CREATE TABLE IF NOT EXISTS student ( | |
| # student_id INTEGER PRIMARY KEY, | |
| # first_name TEXT, | |
| # last_name TEXT, | |
| # date_of_birth DATE, | |
| # email TEXT, | |
| # phone_number TEXT, | |
| # major TEXT, | |
| # year_of_enrollment INTEGER | |
| # ); | |
| # """) | |
| # # Create employee table | |
| # cursor.execute(""" | |
| # CREATE TABLE IF NOT EXISTS employee ( | |
| # employee_id INTEGER PRIMARY KEY, | |
| # first_name TEXT, | |
| # last_name TEXT, | |
| # email TEXT, | |
| # department TEXT, | |
| # position TEXT, | |
| # salary REAL, | |
| # date_of_joining DATE | |
| # ); | |
| # """) | |
| # # Create course_info table | |
| # cursor.execute(""" | |
| # CREATE TABLE IF NOT EXISTS course ( | |
| # course_id INTEGER PRIMARY KEY, | |
| # course_name TEXT, | |
| # course_code TEXT, | |
| # instructor_id INTEGER, | |
| # department TEXT, | |
| # credits INTEGER, | |
| # semester TEXT | |
| # ); | |
| # """) | |
| # # Insert sample data into student table | |
| # cursor.execute("DELETE FROM student;") | |
| # cursor.executemany("INSERT OR IGNORE INTO student VALUES (?, ?, ?, ?, ?, ?, ?, ?);", [ | |
| # (1, "Alice", "Johnson", "2001-06-15", "alice@example.com", "1234567890", "Computer Science", 2019), | |
| # (2, "Bob", "Smith", "2000-04-22", "bob@example.com", "0987654321", "Mechanical Engineering", 2018), | |
| # ]) | |
| # # Insert sample data into employee table | |
| # cursor.execute("DELETE FROM employee;") | |
| # cursor.executemany("INSERT OR IGNORE INTO employee VALUES (?, ?, ?, ?, ?, ?, ?, ?);", [ | |
| # (1, "Drake", "Miller", "drake@example.com", "CSE", "Professor", 75000.00, "2015-08-01"), | |
| # (2, "Laura", "White", "laura@example.com", "ECE", "Assistant Professor", 65000.00, "2018-07-10"), | |
| # ]) | |
| # # Insert sample data into course_info table | |
| # cursor.execute("DELETE FROM course;") | |
| # cursor.executemany("INSERT OR IGNORE INTO course VALUES (?, ?, ?, ?, ?, ?, ?);", [ | |
| # (1, "Database Systems", "CS301", 1, "CSE", 4, "Fall"), | |
| # (2, "Digital Electronics", "EC202", 1, "ECE", 3, "Spring"), | |
| # ]) | |
| # conn.commit() | |
| # conn.close() | |
| # # Function to execute SQL query and return results | |
| # def run_sql_query(sql_query): | |
| # try: | |
| # conn = sqlite3.connect("college.db") | |
| # cursor = conn.cursor() | |
| # cursor.execute(sql_query) | |
| # rows = cursor.fetchall() | |
| # column_names = [description[0] for description in cursor.description] if cursor.description else [] | |
| # conn.commit() | |
| # conn.close() | |
| # if not rows: | |
| # return "No rows returned." | |
| # result = "\t".join(column_names) + "\n" | |
| # for row in rows: | |
| # result += "\t".join(str(cell) for cell in row) + "\n" | |
| # return result.strip() | |
| # except Exception as e: | |
| # return f"Error executing query: {e}" | |
| # # Initialize database when this script runs directly | |
| # if __name__ == "__main__": | |
| # initialize_database() | |
| import sqlite3 | |
| # Create and initialize the database | |
| def initialize_database(): | |
| with sqlite3.connect("college.db") as conn: | |
| cursor = conn.cursor() | |
| # Enable foreign key support | |
| cursor.execute("PRAGMA foreign_keys = ON;") | |
| # Create student table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS student ( | |
| student_id INTEGER PRIMARY KEY, | |
| first_name TEXT, | |
| last_name TEXT, | |
| date_of_birth DATE, | |
| email TEXT, | |
| phone_number TEXT, | |
| major TEXT, | |
| year_of_enrollment INTEGER | |
| ); | |
| """) | |
| # Create employee table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS employee ( | |
| employee_id INTEGER PRIMARY KEY, | |
| first_name TEXT, | |
| last_name TEXT, | |
| email TEXT, | |
| department TEXT, | |
| position TEXT, | |
| salary REAL, | |
| date_of_joining DATE | |
| ); | |
| """) | |
| # Create course table with foreign key | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS course ( | |
| course_id INTEGER PRIMARY KEY, | |
| course_name TEXT, | |
| course_code TEXT, | |
| instructor_id INTEGER, | |
| department TEXT, | |
| credits INTEGER, | |
| semester TEXT, | |
| FOREIGN KEY (instructor_id) REFERENCES employee(employee_id) | |
| ); | |
| """) | |
| # Clear existing data | |
| cursor.execute("DELETE FROM student;") | |
| cursor.execute("DELETE FROM employee;") | |
| cursor.execute("DELETE FROM course;") | |
| # Insert 10 students | |
| students = [ | |
| (1, "Alice", "Johnson", "2001-06-15", "alice@example.com", "1234567890", "Computer Science", 2019), | |
| (2, "Bob", "Smith", "2000-04-22", "bob@example.com", "0987654321", "Mechanical Engineering", 2018), | |
| (3, "Charlie", "Brown", "2002-01-12", "charlie@example.com", "1111111111", "Electrical Engineering", 2020), | |
| (4, "Daisy", "Miller", "2001-11-05", "daisy@example.com", "2222222222", "Electronics", 2020), | |
| (5, "Ethan", "Hunt", "1999-09-30", "ethan@example.com", "3333333333", "Mechanical Engineering", 2017), | |
| (6, "Fiona", "Gray", "2003-03-10", "fiona@example.com", "4444444444", "Civil Engineering", 2021), | |
| (7, "George", "King", "2000-08-20", "george@example.com", "5555555555", "Computer Science", 2018), | |
| (8, "Hannah", "Scott", "2001-02-25", "hannah@example.com", "6666666666", "Information Technology", 2019), | |
| (9, "Ian", "Clark", "2002-07-07", "ian@example.com", "7777777777", "Electronics", 2020), | |
| (10, "Julia", "Anderson", "1998-12-18", "julia@example.com", "8888888888", "Civil Engineering", 2016), | |
| ] | |
| cursor.executemany("INSERT OR IGNORE INTO student VALUES (?, ?, ?, ?, ?, ?, ?, ?);", students) | |
| # Insert 10 employees | |
| employees = [ | |
| (1, "Drake", "Miller", "drake@example.com", "CSE", "Professor", 75000.00, "2015-08-01"), | |
| (2, "Laura", "White", "laura@example.com", "ECE", "Assistant Professor", 65000.00, "2018-07-10"), | |
| (3, "Nina", "Gomes", "nina@example.com", "IT", "Lecturer", 55000.00, "2020-01-15"), | |
| (4, "Oscar", "Fernandez", "oscar@example.com", "ME", "Professor", 80000.00, "2014-03-12"), | |
| (5, "Priya", "Singh", "priya@example.com", "CSE", "Assistant Professor", 62000.00, "2019-06-01"), | |
| (6, "Rahul", "Kumar", "rahul@example.com", "EEE", "Lecturer", 50000.00, "2021-09-20"), | |
| (7, "Sana", "Shaikh", "sana@example.com", "CIV", "Lecturer", 52000.00, "2022-02-11"), | |
| (8, "Tom", "Andrews", "tom@example.com", "ECE", "Associate Professor", 70000.00, "2016-12-09"), | |
| (9, "Uma", "Joshi", "uma@example.com", "IT", "Professor", 78000.00, "2013-05-25"), | |
| (10, "Vikram", "Reddy", "vikram@example.com", "ME", "Assistant Professor", 60000.00, "2017-11-03"), | |
| ] | |
| cursor.executemany("INSERT OR IGNORE INTO employee VALUES (?, ?, ?, ?, ?, ?, ?, ?);", employees) | |
| # Insert 10 courses (linked to instructors from employee table) | |
| courses = [ | |
| (1, "Database Systems", "CS301", 1, "CSE", 4, "Fall"), | |
| (2, "Digital Electronics", "EC202", 2, "ECE", 3, "Spring"), | |
| (3, "Operating Systems", "CS302", 1, "CSE", 4, "Spring"), | |
| (4, "Thermodynamics", "ME101", 4, "ME", 3, "Fall"), | |
| (5, "Signals and Systems", "EC301", 2, "ECE", 3, "Fall"), | |
| (6, "Data Structures", "CS201", 5, "CSE", 4, "Spring"), | |
| (7, "Electrical Machines", "EE101", 6, "EEE", 4, "Fall"), | |
| (8, "Structural Analysis", "CIV301", 7, "CIV", 3, "Spring"), | |
| (9, "Web Technologies", "IT401", 3, "IT", 3, "Fall"), | |
| (10, "Fluid Mechanics", "ME203", 10, "ME", 4, "Spring"), | |
| ] | |
| cursor.executemany("INSERT OR IGNORE INTO course VALUES (?, ?, ?, ?, ?, ?, ?);", courses) | |
| # Function to execute SQL query and return results | |
| def run_sql_query(sql_query): | |
| try: | |
| with sqlite3.connect("college.db") as conn: | |
| cursor = conn.cursor() | |
| cursor.execute("PRAGMA foreign_keys = ON;") | |
| cursor.execute(sql_query) | |
| rows = cursor.fetchall() | |
| column_names = [description[0] for description in cursor.description] if cursor.description else [] | |
| if not rows: | |
| return "No rows returned." | |
| result = "\t".join(column_names) + "\n" | |
| for row in rows: | |
| result += "\t".join(str(cell) for cell in row) + "\n" | |
| return result.strip() | |
| except Exception as e: | |
| return f"Error executing query: {e}" | |
| # Initialize database when this script runs directly | |
| if __name__ == "__main__": | |
| initialize_database() | |