SQL & Python Exam Study Guide
Uploaded by
shazilshazil300SQL & Python Exam Study Guide
Uploaded by
shazilshazil300Common questions
Powered by AIBuilt-in expressions in programming offer fundamental functionality for diverse computing needs. Arithmetic expressions perform mathematical calculations, e.g., 5 + 3. Comparison expressions evaluate relationships between values, e.g., 5 > 3. Logical expressions, such as AND and OR, combine multiple conditions, e.g., (x > 5) AND (y < 10). Bitwise expressions manipulate data at the bit level, e.g., 5 & 3. Finally, string expressions concatenate or modify strings, e.g., 'Hello' + ' ' + 'World'. These expressions facilitate complex algorithms and control structures across various programming domains .
Aggregate functions in SQL perform calculations on multiple rows of a table's column(s) and return a single result. Common aggregate functions include COUNT(), which counts the number of rows; SUM(), which adds up numeric values; AVG(), which calculates the average value; MIN(), which returns the smallest value; and MAX(), which returns the largest value .
In Python, file operations can be performed in either text mode or binary mode. Text mode (default) reads and writes files as text, suitable for human-readable content, while binary mode deals with binary files, which are more efficient for machine-readable data. For example, using 'r' or 'w' opens a file in text mode for reading or writing, respectively, whereas 'rb' or 'wb' opens a file in binary mode for reading or writing. An example in binary mode is with open('data.bin', 'wb') to write bytes to a file and open('data.bin', 'rb') to read those bytes .
The DROP TABLE command in SQL is used to delete a table entirely from the database, including all its structure, data, indexes, and permissions. This operation is irreversible and should be used with caution. For example, DROP TABLE employee will remove the 'employee' table from the database if it is no longer needed or if its archived data is transferred elsewhere. It's typically used during database cleanup or when restructuring is necessary .
String functions in SQL are used to perform various operations on string data types. These include transforming strings, extracting substrings, and measuring string lengths. Examples include UPPER() which converts a string to uppercase, LOWER() which converts it to lowercase, SUBSTRING(str, start, length) which extracts part of a string, CONCAT(str1, str2) which joins two strings, and LENGTH(str) which returns the length of a string .
Stacks in programming play a vital role in managing function calls and expression evaluations due to their LIFO (Last In, First Out) structure. They help in managing function calls through the call stack by keeping track of active subroutines, enabling the program to return to the correct execution point after a function completes. In expression evaluations, stacks assist in converting infix expressions (e.g., A+B) into postfix (e.g., AB+) for easier computation by algorithmic processes .
Primary key constraints in MySQL uniquely identify each row in a table, ensuring that no duplicate values exist in the specified column(s) and that the values are not NULL, thereby maintaining entity integrity. A foreign key constraint links two tables, ensuring referential integrity by enforcing relationships between tables. It requires that the value in a column, or set of columns, matches a value in the primary key of another table, preventing invalid data entry .
DDL (Data Definition Language) commands in SQL are used to define and manage all database objects, such as tables and indexes, without manipulating data within them. Examples include CREATE, ALTER, and DROP. For instance, CREATE TABLE defines a new table's structure. DML (Data Manipulation Language) commands, on the other hand, are used to manipulate data stored in database objects. This includes INSERT (to add new records), UPDATE (to modify existing records), and DELETE (to remove records). SELECT is used to query and retrieve data .
Creating a table in SQL involves defining its structure with the CREATE TABLE statement, specifying column names and data types. For example, CREATE TABLE Student (Rollno INT, Sname VARCHAR(15), DOB DATE) defines a table with student roll numbers, names, and dates of birth. Modifying a table can include adding columns or altering data types using the ALTER TABLE command. For instance, ALTER TABLE Student ADD Gender CHAR(1) adds a gender column. Reviewing the structure can be done with DESC Student, and viewing all records with SELECT * FROM Student .
Exception handling is crucial in programming as it prevents program crashes and provides mechanisms to handle unexpected errors gracefully, ensuring robustness and security. A try-except-finally block assists in managing these exceptions: the try block contains code that might throw an error, the except block captures and handles the error to prevent program interruption, and the finally block contains cleanup code that executes regardless of an error occurrence, maintaining program order .