Enter Title

Tables

100

What is a table in SQL Server?

A table in SQL Server is a database object that contains all the data in a database. It is organized in a row-and-column format similar to a spreadsheet.

200

What is the maximum number of columns allowed in a standard user-defined table?

A standard user-defined table can have up to 1,024 columns.

300

How do you create a table in SQL Server?

To create a table in SQL Server, you can use the CREATE TABLE statement followed by the table name and column definitions.

400

How is data organized in a table?

1. Data is organized in a table in a row-and-column format. Each row represents a unique record, and each column represents a field in the record.

500

Create a table called Employees with columns for EmployeeID, LastName, FirstName, Title, and HireDate

CREATE TABLE Employees (

  EmployeeID int,

  LastName varchar(255),

  FirstName varchar(255),

  Title varchar(255),

  HireDate date

);

Columns & Keys

100

1. What is the maximum number of columns allowed in a standard user-defined table?
2. What is the maximum number of rows allowed in a table?

1. A standard user-defined table can have up to 1,024 columns.
2. The number of rows in the table is limited only by the storage capacity of the server.

200

What is the difference between a left join and a right join in SQL?

 A left join returns all the rows from the left table and matching rows from the right table. If there are no matching rows in the right table, the result will contain null values for all columns of the right table.

 A right join is similar to a left join, but it returns all the rows from the right table and matching rows from the left table. If there are no matching rows in the left table, the result will contain null values for all columns of the left table.

300

 What is normalization in database design, and why is it important?

 Normalization is a process of organizing data in a database to reduce redundancy and improve data integrity.

It involves breaking down large tables into smaller, more manageable tables and establishing relationships between them.

 Normalization helps to eliminate data inconsistencies and anomalies such as update anomalies, insertion anomalies, and deletion anomalies.

It also helps to improve query performance and reduce storage requirements.

400

How is a foreign key different from a primary key?

A primary key uniquely identifies each record in a table and ensures that it has a unique value. A foreign key, on the other hand, establishes a relationship between two tables by referencing the primary key or a unique key of another table.

500

What is referential integrity, and how does it relate to foreign keys?

Referential integrity ensures that relationships between tables are maintained. It prevents actions that would violate these relationships, such as deleting a record that is referenced by another table’s foreign key. Foreign keys play a crucial role in enforcing referential integrity.

Foreign Key ,Index& Views

100

What happens when you try to insert a row with a foreign key that doesn’t exist in the referenced table?

If you try to insert a row with a foreign key that doesn’t exist in the referenced table, you will receive an error. The database engine will enforce referential integrity and prevent the insertion of invalid data.

200

Can a table have multiple foreign keys?

Yes, a table can have multiple foreign keys. Each foreign key establishes a separate relationship with another table.

300

What is an index in SQL Server?

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently

400

What are the types of indexes available in SQL Server?

The following types of indexes are available in SQL Server:

• Clustered Index: A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values .
  
  
• Nonclustered Index: A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table .
  
  
• Hash Index: With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count .
  
  
• Columnstore Index: An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing. Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size .

500

What is a view in SQL Server?

 A view is a virtual table whose contents are defined by a query.

 It consists of a set of named columns and rows of data. A view does not exist as a stored set of data values in a database.

Instead, the rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.

 A view acts as a filter on the underlying tables referenced in the view.

 The query that defines the view can be from one or more tables or from other views in the current or other databases 

DML

100

What is the purpose of the SELECT statement in SQL Server?

The SELECT statement is used to retrieve data from one or more tables in a database.

It allows you to specify the columns you want to retrieve, apply filters using WHERE clause, sort the result set using ORDER BY clause, and perform various other operations on the data

200

How can I retrieve only the top 10 rows from Customers  table in SQL Server?

SELECT TOP 10 * FROM Customers;

300

How can I sort the result set of a SELECT statement in SQL Server?

To sort the result set of a SELECT statement, you can use the ORDER BY clause.

The ORDER BY clause allows you to specify one or more columns by which you want to sort the result set. For example, to sort the result set of a query by the “LastName” column in ascending order, you can use the following query:

SELECT * FROM Customers ORDER BY LastName ASC;

400

How can I group rows in SQL Server based on a specific column?

To group rows in SQL Server based on a specific column, you can use the GROUP BY clause.

The GROUP BY clause allows you to group rows that have the same values in one or more columns.

SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;

 

500

. How can  you use string functions (LEN, CONCAT ,UPPER)and SUM function in a SELECT statement?

You can use various string functions and data functions in a SELECT statement to manipulate and transform data.

 Some commonly used string functions include LEN, SUBSTRING, LOWER, UPPER, and CONCAT. Some commonly used data functions include SUM, AVG, COUNT, and MAX.

 Here’s an example that demonstrates how to use these functions:

SELECT LEN(FirstName), UPPER(LastName), CONCAT(FirstName, ' ', LastName), SUM(OrderTotal)

FROM Customers

GROUP BY LEN(FirstName), UPPER(LastName), CONCAT(FirstName, ' ', LastName);

 

Functions & Procedures

100

What is a function in SQL Server?

A function is a pre-built SQL statement that performs a specific task.

Functions can accept input parameters, perform actions, and then return the result.

Functions always return either a single value or a table

200

What are the types of user-defined functions available in SQL Server?

The following types of user-defined functions are available in SQL Server:

• Scalar Function: A scalar function returns a single value of any scalar data type 1.
• Table-Valued Function: A table-valued function returns a table as its result set 1.
• Inline Table-Valued Function: An inline table-valued function is a table-valued function that returns a table as its result set. It is defined using a single SELECT statement 3.
• Multi-Statement Table-Valued Function: A multi-statement table-valued function is a table-valued function that returns a table as its result set. It is defined using multiple statements 3

300

How do you  create a user-defined function in SQL Server?

o create a user-defined function in SQL Server, you can use the CREATE FUNCTION statement.

Here’s an example that demonstrates how to create a scalar function named “GetFullName” that concatenates the first name and last name of an employee:

CREATE FUNCTION GetFullName (@FirstName VARCHAR(50), @LastName VARCHAR(50))

RETURNS VARCHAR(100)

AS

BEGIN

    RETURN @FirstName + ' ' + @LastName

END

 

400

 What is a stored procedure in SQL Server? and its advantages

A stored procedure is a precompiled set of SQL statements that perform a specific task. Stored procedures can accept input parameters, perform actions, and then return the result.

 Stored procedures are stored in the database and can be called from an application or another stored procedure

Stored procedures offer several advantages over ad-hoc SQL statements:

• Improved Performance: Stored procedures are precompiled and optimized, which means they execute faster than ad-hoc SQL statements 1.
• Reduced Network Traffic: Stored procedures reduce network traffic by sending only the parameters and return values over the network, instead of sending the entire SQL statement 1.
• Improved Security: Stored procedures can be used to restrict access to sensitive data by granting permissions only to execute the stored procedure, not to access the underlying tables 1.
• Code Reusability: Stored procedures can be reused across multiple applications or queries, which reduces development time and maintenance costs 1.

 

500

 What is a parameterized stored procedure in SQL Server? 

Given example for getting list of customers who is leaving in Hyderabad using stored procedure ?

parameterized stored procedure is a stored procedure that accepts one or more input parameters.

 Input parameters allow you to pass values into the stored procedure at runtime,

Here is the definition of SP to get customer by city
CREATE PROCEDURE GetCustomersByCity

    @City VARCHAR(50)

AS

BEGIN

    SELECT * FROM Customers WHERE City = @City;

END

Execute SP 

Declare @City as varchar(10)='Hyderabad;
 Execute  GetCustomersByCity @City