Explore topic-wise InterviewSolutions in .

Yes we can.
CREATE TABLE part_sample
(part_nmbr int PRIMARY KEY,
part_name CHAR(30),
part_weight DECIMAL(6,2),
part_color char(15) )
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION ON UPDATE NO ACTION,
qty_ordered int)
GO.

Yes we can.
CREATE TABLE part_sample
(part_nmbr int PRIMARY KEY,
part_name char(30),
part_weight decimal(6,2),
part_color char(15) )
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION ON UPDATE NO ACTION,
qty_ordered int)
GO.

It WORKS as TEMPLATE DATABASE for the CREATE Database SYNTAX.

It works as Template Database for the Create Database Syntax.

Master database KEEPS the INFORMATION about SQL SERVER configuration, databases USERS etc.

Master database keeps the information about sql server configuration, databases users etc.

Tempdb database KEEPS the INFORMATION about the temporary objects (#TABLENAME, #Procedure). Also the SORTING, DBCC operations are PERFORMED in the TempDB.

Tempdb database keeps the information about the temporary objects (#TableName, #Procedure). Also the sorting, DBCC operations are performed in the TempDB.

USE command is USED for to SELECT the DATABASE. For i.e Use Database Name.

Use command is used for to select the database. For i.e Use Database Name.

Yes.

Yes.

PARSE query BUTTON is USED to CHECK the SQL Query Syntax.

Parse query button is used to check the SQL Query Syntax.

SELECT.

SELECT.

NO.

NO.

Order By CLAUSE is USED for sorting records in Ascending or DESCENDING order.

Order By clause is used for sorting records in Ascending or Descending order.

ORDER By CLAUSE CHANGE only the OUTPUT of the DATA.

Order By clause change only the output of the data.

ASCENDING ORDER.

Ascending Order.

OR, AND, IN and BETWEEN.

OR, AND, IN and BETWEEN.

No NULL VALUE means nothing. We can’t CONSIDER space as NULL value.

No NULL value means nothing. We can’t consider space as NULL value.

YES, it will INCLUDE the NULL COLUMN in COUNT.

Yes, it will include the null column in count.

Aliases can make it easier to WORK with table names. Using aliases is helpful when:

•  You want to make the statement in the SQL Pane shorter and easier to read.
• You refer to the table NAME often in your query — such as in qualifying COLUMN names — and want to be sure you STAY within a specific character-length limit for your query. (Some databases impose a maximum length for queries).
•  You are working with multiple instances of the same table (such as in a self-join) and need a way to refer to ONE instance or the other.

Aliases can make it easier to work with table names. Using aliases is helpful when:

No it is not REQUIRED.

No it is not required.

No its not NECESSARY.

No its not necessary.

SYNONYM is a single-part name that can replace a two, three or four-part name in MANY SQL statements. Using SYNONYMS in RDBMS cuts down on typing.
SYNONYMs can be created for the following objects:

• Table.
•  View.
• Assembly (CLR) Stored Procedure.
• Assembly (CLR) Table-valued Function.
• Assembly (CLR) Scalar Function.
• Assembly Aggregate (CLR) Aggregate Functions.
• Replication-filter-procedure.
• EXTENDED Stored Procedure.
• SQL Scalar Function.
• SQL Table-valued Function.
• SQL Inline-table-valued Function.
• SQL Stored Procedure.

SYNONYM is a single-part name that can replace a two, three or four-part name in many SQL statements. Using SYNONYMS in RDBMS cuts down on typing.
SYNONYMs can be created for the following objects:

Yes.

Yes.

YES we can.

Yes we can.

Not NECESSARY.

Not Necessary.

INSERT INTO .. SELECT is used insert DATA in to table from DIFFERENT tables or condition BASED insert.
INSERT INTO .. VALUES you have to specify the insert values.

INSERT INTO .. SELECT is used insert data in to table from different tables or condition based insert.
INSERT INTO .. VALUES you have to specify the insert values.

UPDATE COMMAND will MODIFY the EXISTING RECORD.

Update command will modify the existing record.

YES we can. ALTER Table MODIFY COLUMN.

Yes we can. Alter Table Modify Column.

YES it ALLOWS.

Yes it allows.

NULL.

NULL.

SQL SERVER treats CHAR as fixed length COLUMN.

SQL Server treats CHAR as fixed length column.

Variable length columns only use a very small AMOUNT of space to store a NULL so VARCHAR DATATYPE is the good OPTION for null values.

Variable length columns only use a very small amount of space to store a NULL so VARCHAR datatype is the good option for null values.

The COLUMN INITIALLY CONTAINS the NULL VALUES.

The column initially contains the NULL values.

ALTER TABLE TABLENAME ADD column_name DATATYPE.

ALTER TABLE tablename ADD column_name DATATYPE.

Yes.

Yes.

CREAT INDEX INDEXNAME ON TABLE(COLUMN NAME).

CREAT INDEX INDEXNAME ON TABLE(COLUMN NAME).

CONSTRAIN will not ALLOW NULL VALUES in the COLUMN.

Constrain will not allow NULL values in the column.

DEFAULT, WITH CHECK or WITH NOCHECK.

DEFAULT, WITH CHECK or WITH NOCHECK.

UNIQUE + NOT NULL.

Unique + NOT NULL.

A UNIQUE CONSTRAINT is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table. When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

•  The NUMBER of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.
• Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then EVERY field can accept NULL and can have some values on them, as long as the combination values is unique.

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table. When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

Each table has ONE and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you MIGHT find several columns, or GROUPS of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can BECOME the primary key for that table.

Each table has one and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you might find several columns, or groups of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can become the primary key for that table.

Referential integrity in a relational database is consistency between coupled tables. Referential integrity is USUALLY enforced by the combination of a PRIMARY key or candidate key (alternate key) and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table’s primary key or a candidate key. For INSTANCE, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by RETURNING an error and not performing the delete. Which method is used would be determined by the referential integrity CONSTRAINT, as defined in the data dictionary.

Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key (alternate key) and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table’s primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used would be determined by the referential integrity constraint, as defined in the data dictionary.

ON DELETE CASCADE:

• Specifies that if an attempt is MADE to delete a row with a key referenced by foreign KEYS in existing ROWS in other tables, all rows containing those foreign keys are also deleted. If cascading REFERENTIAL actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.
• Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.

ON DELETE CASCADE:

ON UPDATE NO ACTION:

• Specifies that if an ATTEMPT is made to update a key value in a row whose key is referenced by foreign KEYS in existing rows in other tables, an error is raised and the UPDATE is rolled back.
• Specifies that if an attempt is made to DELETE a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE is rolled back.

ON UPDATE NO ACTION:

File stream data type, ACTIVITY MONITOR, RESOURCE governor, data COMPRESSION, compressed backup.

File stream data type, Activity Monitor, Resource governor, data compression, compressed backup.

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

GRANT, DENY and REVOKE.

GRANT, DENY and REVOKE.

Fill FACTOR is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much DATA is written to an INDEX PAGE when it is created / rebuilt.

Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.

DCL STANDS for DATA CONTROL LANGUAGE.

DCL stands for Data Control Language.

•  A DATABASE can CONTAIN a maximum of 32,767 files.
•  There are Primarily 2 types of data files Primary data file and Secondary data file(s). There can be only ONE Primary data file and multiple secondary data files as long as the TOTAL # of files is LESS than 32,767 files.

1433.

1433.

Inside SSMS, in OBJECT explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you MAY choose the execution time (if the job failed multiple times during the same DAY). There WOULD information such as the time it took to execute that Job and details about the error occurred.

Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.