SQL Server and Oracle Database Administration

Stored procedures CAN be executed on both local and remote SQL Servers. The statement claims the opposite ('cannot execute'), making it false. Remote procedure execution is a standard feature that allows procedures to run on linked servers or across distributed database environments.

The statement is false because stored procedures ARE stored in system tables of the current database after compilation. When first executed, a stored procedure is compiled and its execution plan is cached in system tables (like syscomments in some systems) for reuse. This caching improves performance on subsequent executions.

The SELECT INTO statement creates a new target table and populates it with rows and columns from the source table. It's a combination of CREATE TABLE and INSERT SELECT - the new table is automatically created based on the structure of the selected data, making it useful for quick table creation and data copying.

SQLPlus is an interactive command-line interface provided by Oracle to execute SQL statements and PL/SQL blocks. While SQL and PL/SQL are languages rather than tools/interfaces, and Java is a general-purpose language, SQLPlus acts as the execution environment.

SQL (Structured Query Language) is the standard language for relational database operations including data manipulation (INSERT, SELECT, UPDATE, DELETE), data definition (CREATE, ALTER, DROP), transaction control (COMMIT, ROLLBACK), and database administration.

PL/SQL (Procedural Language extensions to SQL) is Oracle's procedural programming language that combines SQL data manipulation with procedural constructs like variables, conditions, loops, and exception handling. It enables custom program logic to execute within the Oracle database server.

In SQL*Plus, the FEEDBACK setting (configured via SET FEEDBACK n) determines the minimum number of rows a query must return to display the row count message (like '10 rows selected'). LINESIZE sets line width, PAGESIZE sets lines per page, and PAUSE halts output scrolling.

The PAUSE command in SQL*Plus controls output pagination. When enabled (SET PAUSE ON), output pauses after each page and waits for the user to press Enter before continuing. This is useful for viewing large query results that span multiple pages.

Parsing breaks SQL statements into component parts (keywords, table names, column names) and determines the statement type (DML, DDL, DCL, or query). It performs syntactic validation before optimization or execution occurs.

Oracle statement execution follows a sequence: parsing (syntax/semantic validation), optimization (execution plan generation), row source generation (retrieval strategy), and statement execution (actual data access). All steps are required.

A database view is a stored query definition that only produces results when invoked. Unlike tables, views have no physical storage - data is materialized dynamically at runtime from the underlying base tables.

In database hierarchy, a catalog represents a database container hosting one or more named schemas. Each schema then groups related tables, views, and columns. Distractors like multiple rows, tables, or columns represent lower-level database components rather than the high-level schema containers grouped under a catalog.

SQL Server allows only ONE unnamed default instance per server. The default instance is identified by the computer name alone without any additional instance name. However, you can install multiple named instances on the same server (each with a unique instance name), but only one can be the default instance.

Mixed Mode authentication allows both Windows authentication and SQL Server authentication to be used to connect to the database. Windows mode only permits Windows logins, while Kerberos and Network Service are not database authentication modes but rather authentication protocols or service accounts.

In SQL Server 2005, the database engine service (SQL Server) and the SQL Server Agent run as independent Windows services, requiring their own distinct service account configuration. Log Reader and Replication agents run as jobs/sub-processes rather than standalone services.

An in-place upgrade means installing a new version of SQL Server in the same directory as the existing installation, replacing the old files while keeping the same instance. This is different from side-by-side migration where a new instance is created. In-place upgrades are simpler but riskier because they modify the existing installation directly without a fallback option.

The detach/attach method requires exclusive access to the database because detaching removes the database from the SQL Server instance. Users cannot access the database while it's detached. Other methods like backup/restore or Copy Database Wizard can work with active databases or use different mechanisms that don't require taking the database completely offline during the entire process.

To create a new filegroup in SQL Server, you use the ALTER DATABASE statement with the ADD FILEGROUP clause. The ADD FILE clause is used to add actual data files to an existing filegroup, MODIFY FILEGROUP changes filegroup properties, and REMOVE FILEGROUP removes an existing filegroup. Only ADD FILEGROUP creates a new filegroup.

SQL Server supports several filegroup types: Primary (contains the primary data files and system tables), Default (the default filegroup where objects are created if not specified), and Read-only (can be set to prevent modifications). Write-Only is not a valid filegroup type in SQL Server. Filegroups control how data files are organized and managed within a database.