COP 4703 Final Exam Review Solved 100% Correct

COP 4703 Final Exam Review Solved
100% Correct
How can deadlocks be prevented in a database management system? - ANSWER-
Deadlocks can be prevented by implementing techniques such as lock timeouts,
deadlock detection algorithms, ordered locking, or the "Wait-Die" and "Wound-Wait"
schemes.

How can primary keys be designated in Application-Time Period Tables? - ANSWER-
Primary keys uniquely identify each record in a table and can be defined when creating
the table or later using ALTER TABLE.

How do Stored Functions differ from Stored Procedures in SQL? - ANSWER-Stored
Functions, like Stored Procedures, allow code reuse, but they're invoked within SQL
expressions instead of using the CALL statement.

How do you connect to a database using Python's sqlite3 library? - ANSWER-import
sqlite3
conn = sqlite3.connect('database_name.db')

How do you create a table using Python's sqlite3 library? - ANSWER-cur =
conn.cursor()
cur.execute('CREATE TABLE table_name (column1 datatype, column2 datatype)')

How do you create an index on a column in SQL? - ANSWER-CREATE INDEX
index_name ON table_name (column_name);

How do you declare and use a variable in SQL? - ANSWER-Variables are declared
using the DECLARE statement, and they can be assigned a value using the SET
statement. For example:
BEGIN
DECLARE @temp INT;
SET @temp = 10;
SELECT @temp;
END;

How do you delete data from a table using Python's sqlite3 library? - ANSWER-
cur.execute('DELETE FROM table_name WHERE condition')

How do you insert data into a table using Python's sqlite3 library? - ANSWER-
cur.execute("INSERT INTO table_name VALUES (?, ?)", (value1, value2))

How do you query data from a table using Python's sqlite3 library? - ANSWER-
cur.execute('SELECT * FROM table_name')

, rows = cur.fetchall()

How do you remove an index in SQL? - ANSWER-DROP INDEX index_name;

How do you rename a column in SQL? - ANSWER-ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

How do you select all columns from a table in SQL? - ANSWER-SELECT * FROM
table_name;

How do you select specific columns from a table in SQL? - ANSWER-SELECT
column1, column2 FROM table_name;

How do you update data in a table using Python's sqlite3 library? - ANSWER-
cur.execute('UPDATE table_name SET column1 = value1 WHERE condition')

How do you write a query to get the average value of a numeric column in SQL? -
ANSWER-SELECT AVG(column_name) FROM table_name;

How do you write a query to get the sum of a numeric column in SQL? - ANSWER-
SELECT SUM(column_name) FROM table_name;

How do you write a SQL statement to count the number of rows in a table? - ANSWER-
SELECT COUNT(*) FROM table_name;

How to filter results using a WHERE clause in SQL? - ANSWER-SELECT
column_name FROM table_name WHERE condition;

How to group rows that have the same value in a specific column in SQL? - ANSWER-
SELECT column_name, COUNT(column_name) FROM table_name GROUP BY
column_name;

How to join two tables on a common column in SQL? - ANSWER-SELECT
column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name =
table2.column_name;

How to limit the number of results returned by an SQL query? - ANSWER-SELECT
column_name FROM table_name LIMIT number;

How to order the results of a query in descending order in SQL? - ANSWER-SELECT
column_name FROM table_name ORDER BY column_name DESC;

How to select distinct values of a column in SQL? - ANSWER-SELECT DISTINCT
column_name FROM table_name;