Sybase Database Fundamentals

In SQL Server, a table without a clustered index is called a heap. Data in a heap is stored without any organized order based on key values, unlike tables with clustered indexes where rows are physically sorted. The other options (AOL, BOL, DOL) are not SQL Server storage terminology.

SQL Server indexes use a B-tree structure with multiple levels: the root level at the top, intermediate levels that branch between root and leaves, and the leaf level containing actual data or data pointers. 'Intermediate level' is the correct structural term for non-leaf levels between root and leaf.

Domain integrity ensures that data values fall within defined valid domains for each column. Check constraints enforce domain integrity by validating column values against specific conditions (e.g., age > 0, price BETWEEN 0 AND 1000). Primary key and unique constraints enforce entity integrity (row uniqueness), while foreign key enforces referential integrity between related tables.

Stored procedures can execute DDL commands and create various database objects. While stored procedures can technically create views, defaults, and extended procedures, creating indexes within stored procedures is particularly common for maintenance tasks, performance optimization, and automated index management operations.

SQL subqueries can be nested within all major Data Manipulation Language statements. They provide powerful query capabilities in SELECT statements for filtering and projection, and can also be used in UPDATE, DELETE, and INSERT statements to determine which rows are affected or what values are inserted.

The SQL LEFT function extracts a specified number of characters starting from the left of the string. Passing 4 as the second parameter to 'Have a nice day.' returns the first four characters, which is 'Have'.

sp_configure 'allow updates' was a valid configuration parameter in older SQL Server versions, controlling whether users could directly update system tables. This option has been deprecated and removed in modern SQL Server versions for security reasons. The other options (locking, Setup, no of lock) are not valid sp_configure parameter names.

The STUFF function in SQL Server uses four parameters: the source string (first string), start position (where deletion begins), length (number of characters to delete), and the replacement string (second string). There is no 'end position' parameter - the function uses length instead to determine how many characters to remove before inserting the replacement string.

In Sybase, the physical storage file used to store database data is called a 'device.' A device is the actual operating system file or disk partition allocated to Sybase for data storage. The other options are incorrect: 'Datafile' is not standard Sybase terminology, 'Data Object' refers to logical structures, and 'Material' is not a Sybase storage term.

The TRIM function removes leading and trailing blanks from column_name1. LEFT(column_name2,5) returns the first 5 characters from the left side. SUBSTRING(column_name3,2,4) extracts 4 characters starting from position 2, which gives characters at positions 2, 3, 4, and 5. Option A incorrectly states '2 to 4 character part' when it should be 4 characters starting from position 2.

The @@sqlstatus system variable in Sybase returns specific status codes: 0 for successful completion, 1 for an error occurring during execution, and 2 for a data truncation warning. The value 3 is not a valid return value for @@sqlstatus. Option D is incorrect because option B (3) is indeed not a possible value.

In Sybase CASE statements, the valid keywords are CASE, WHEN, THEN, ELSE, and END. 'End case' as two words is not the correct syntax - the proper keyword is just 'END' (or 'END CASE' is sometimes used but 'END' alone terminates the CASE). The question correctly identifies that 'End case' as a phrase is not part of the CASE keyword structure.

CURRENT_DATE is a valid Sybase date function. DATACHANGE (or DATECHANGE) exists in Sybase for tracking data modifications. CONVERT is a standard Sybase function for data type conversion. GETTIME() is not a standard Sybase function - the correct function is GETDATE() which returns both date and time. Sybase does not have a GETTIME() function.

In Sybase cursor declarations, valid keywords and attributes include SENSITIVE (for sensitive cursors that reflect changes), FORWARD (for forward-only cursors), and FETCH (the operation to retrieve rows). 'Position' is not a cursor keyword or attribute - while you can fetch at specific positions, there is no 'POSITION' keyword in cursor syntax. Cursors have attributes like SCROLL, INSENSITIVE, and FORWARD_ONLY.

System tables in Sybase (those with the 'sys' prefix like sysobjects, syscolumns) are critical to database operation and should never be directly updated by users. Direct manipulation of system tables can corrupt the database and is strongly discouraged. All system table modifications should be done through proper system stored procedures. The statement is false.

Local variables in Sybase must begin with the @ symbol (option A is true). They are local to the batch or stored procedure where declared (option B is true). They cannot have text or image datatypes (option D is true). Option C is NOT true - variables cannot be declared in a SELECT statement. They must be declared using the DECLARE statement before being used in SELECT or other statements.

In older versions of Sybase, the maximum number of indexes per user table was 250 (one clustered index plus 249 non-clustered indexes). This limit was increased in later versions. Option B (255) is close but not the standard limit for these older versions. The other values (2065, 1026) are not standard Sybase index limits. This is version-specific knowledge.

You can add constraints to existing tables using ALTER TABLE, but you add constraints to existing columns, not by creating new columns. The statement claims constraints are added 'by creating new column' which is not correct - constraints are added to columns that already exist. You can also add new columns WITH constraints, but the constraint itself is not created by adding a column. The statement is false.