Databases

Question 1

How to join two databases together?

Answer 1

SELECT *
FROM student
JOIN enroll ON student.SID = enroll.SID

Question 2

How to join three databases together?

Answer 2

SELECT *
FROM Investigation
JOIN Cases ON Cases.CID = Investigation.CID
JOIN Officers ON Officers.OID = Investigation.OID

Question 3

What are the main features of SQLite?

Answer 3

• Serverless
• Zero configuration
• Cross-platform
• Small Runtime Footprint
• Highly Reliable
• Self-contained

Question 4

SQLite syntax:
How to create database

Answer 4

sqlite3 <filename></filename>

Question 5

SQLite syntax:
List names of tables

Answer 5

sqlite3 <filename><br></br>.tables</filename>

Question 6

SQLite syntax:
Show structure of a table

Answer 6

sqlite3 <filename><br></br>.schema <table name>
</table></filename>

Question 7

What import is needed for SQLite dbs?

Answer 7

import sqlite3

Question 8

How to create a connection and run a query?

Answer 8

import sqlite3

conn = sqlite3.connect("dbfile.db")
curs = conn.cursor()
results = curs.execute("SELECT \* FROM table;")

for row in results:
print(row[0])

conn.close()

Question 9

Create a table with Python

Answer 9

curs = conn.cursor()
curs.execute(“CREATE TABLE addressbook (\
ID INTEGER PRIMARY KEY AUTOINCREMENT,\
NAME TEXT);”)

Question 10

How to split SQL on multiple lines?

Answer 10

result = cur.execute(“SELECT *\
FROM visits;”)

or

sql_query = “”” SELECT *
FROM visits
“”“

result = cur.execute(sql_query)

Question 11

How to insert data into a table?

Answer 11

INSERT INTO lecturers VALUES (NULL,
‘Rian Bloggs’, ‘Dr’, ‘Head of School’,
’1990-12-10’);

Question 12

How to update an entry in SQL?

Answer 12

UPDATE lecturers
SET Position=”Senior Lecturer’
WHERE Name=’Yves’;

Question 13

How to delete an entry in SQL?

Answer 13

DELETE FROM lecturers
WHERE Name = ‘Yves’