Database and SQL

The INSERT command in SQL is specifically designed to add new rows of data into a table. It is not used for retrieving (SELECT) or modifying (UPDATE) existing data - those are separate SQL commands.

DISTINCT is the SQL keyword used in SELECT statements to return only unique (different) values, eliminating duplicates. UNIQUE is a constraint for creating indexes, not a query keyword.

The TRUNCATE TABLE command is a DDL operation that removes all rows from a table. It is faster than DELETE because it deallocates the data pages instead of logging individual row deletions, but it keeps the table structure intact.

ORDER BY is the standard SQL clause used to sort query results in ascending or descending order. ARRANGE and SORT are not valid SQL clauses, making ORDER BY the correct choice for sorting result sets.

In T-SQL, SUBSTRING(string, start, length) uses 1-based indexing. However, if start is 0, it starts 'before' the first character. For SUBSTRING('123456', 0, 3), it takes the character at index 0 (nothing), index 1 ('1'), and index 2 ('2'), resulting in '12'.

The DATE data type was introduced in SQL Server 2008. In SQL Server 2005 and earlier versions, attempting to use 'declare @d date' will result in an error because the DATE type doesn't exist in those versions. The code will fail at the declaration stage itself, never reaching the assignment statement. This is a common compatibility issue when migrating code between SQL Server versions.

In SQL Server, the LEN function returns the number of characters in a string, excluding trailing spaces. The string ' SQL is cool ' has 1 leading space and 11 characters ('SQL is cool'), totaling 12. The trailing space is ignored by LEN.

The unicode prefix N'hello' makes the string a unicode string (nvarchar), while replicate('-', 8000) produces a regular varchar string. When concatenating unicode and non-unicode strings, SQL Server implicitly converts to unicode (nvarchar), but the variable @c is declared as varchar(8000), not nvarchar. The implicit conversion truncates to 4000 characters because unicode strings require 2 bytes per character. Therefore, len(@c) returns 4000, not 8000.

SQL Server's SUBSTRING function uses 1-based indexing, not 0-based. When you call SUBSTRING('123456', 0, 3), it starts from position 0, which is treated as position 1. It then returns 3 characters starting from that position, giving you '12'. This is different from many programming languages that use 0-based indexing and is a common source of confusion for developers new to SQL Server.

The TO_CHAR function is highly versatile and can convert various datatypes, such as numbers and dates, into character strings. While aggregate functions like MAX work on many types, TO_CHAR is specifically designed for type conversion across different data categories.

Primary key access is generally the fastest method for Oracle to retrieve a single row because primary keys are unique and have mandatory indexes. When you query by primary key, Oracle can use the index to quickly locate the exact row. Access by ROWID is equally fast when you already have the ROWID, but primary key is typically the standard fastest method. Full table scans are slow for single row retrieval, and unique index access is similar but secondary to primary key access in most scenarios.

The TRUNCATE command deletes all rows from a table without generating individual row deletion logs and does not write to the rollback segment, making it much faster than DELETE. DROP removes the table structure entirely.

In Oracle, SELECT, INSERT, and DELETE are object privileges, while CONNECT and RESOURCE are legacy roles (collections of system privileges). While the question asks for 'privileges', the first option represents the standard administrative roles often granted to new users to allow them to connect and create objects.

A primary key can consist of a single column or multiple columns. When multiple columns are used to uniquely identify a row, it is known as a composite primary key. This is a standard feature in relational database management systems.

The DISTINCT keyword is used in a SELECT statement to remove duplicate rows from the result set, ensuring each row is unique. While UNIQUE is a constraint used during table creation to prevent duplicate entries in a column, it is not a valid SQL command for filtering query results.