SQL SERVER INTERVIEW QUESTIONS
The acronym for Structured Query Language (SQL) stands for Structured Query Language. It’s the de facto industry standard for relational database management systems. It’s especially useful when dealing with structured data made up of entities (variables) and their relationships.
SQL is a programming language that is used to retrieve and manipulate organised databases. MySQL, on the other hand, is a relational database management system used to manage SQL databases, similar to SQL Server, Oracle, or IBM DB2.
The rules that govern the table’s data are defined by constraints. When a SQL table is created or after it has been created using the ALTER TABLE command, it can be used on a single or multiple fields. The following are some of the restrictions:
NOT NULL – Prevents a NULL value from being inserted into a column.
CHECK – Determines whether all values in a field satisfy a set of requirements.
DEFAULT – The field will be assigned a default value if no value has been specified.
UNIQUE – Assures that the values in the field are unique.
INDEX – Indexes a field to make record retrieval faster.
PRIMARY KEY – A key that uniquely identifies each record in a table.
A FOREIGN KEY – ensures that the referential integrity is maintained.
The PRIMARY KEY constraint uniquely identifies each row in a table. It must include UNIQUE data and has an implied NOT NULL constraint.
A table can only have one primary key in SQL, which can be made up of single or multiple fields (columns).
MAKE A TABLE Students (/* Make a table with a single primary key field */)
NOT NULL ID INT
VARCHAR is a unique name for a character (255)
THE MAIN KEY (ID)
MAKE A TABLE (/* Make a table with multiple fields as the primary key */)
NOT NULL ID INT
VARCHAR LastName (255)
VARCHAR FirstName (255) ALTER TABLE Students /* Set a column as primary key */ ADD PRIMARY KEY (ID); ALTER TABLE Students /* Set a column as primary key */ ADD PRIMARY KEY (ID); ALTER TABLE Students /* Set a column as primary key */ ADD PRIMARY KEY (ID); ALTER TABLE Students /* Set a column as primary key */ ADD PRIMARY KEY
A UNIQUE constraint ensures that each column’s value is distinct. This ensures that each row can be identified separately and that the column(s) are unique. Each table, unlike primary keys, can have multiple unique constraints. UNIQUE and PRIMARY KEY have very similar code syntax and can be used interchangeably.
CREATE TABLE Students (/* Create table with a single field as unique */ ID INT NOT NULL UNIQUE Name VARCHAR(255) ); CREATE TABLE Students (/* Create table with multiple fields as unique */ ID INT NOT NULL UNIQUE Name VARCHAR(255) ); CREATE TABLE Students (/* Create table with multiple fields as unique */ ID INT NOT NULL UNIQUE Name VARCHAR(255) ); */ ID INT NOT NULL LastName VARCHAR(255) NOT NULL CONSTRAINT PK Student UNIQUE (ID, FirstName) );
ALTER TABLE STUDENTS ADD UNIQUE (ID); /* Make a column unique */
A database is a logically organised collection of data. Schemas, tables, procedures, code functions, and other items make up the database. A variety of query languages are used to access and change data. In SQL Server, a table is an object that stores data in a tabular (columns and rows) format.
To create a new database, use the SQL command CREATE DATABASE.
CREATE DATABASE DatabaseName is the syntax.
CREATE A DATABASE FOR STUDENTS, FOR EXAMPLE
You can also create a database with SQL Server Management Studio. Select New Database from the drop-down menu by right-clicking on Databases. Pay attention to the wizard’s instructions.
Relationships are formed by linking the columns of one table to the columns of another table. There are four different kinds of relationships that can be formed.
The following are some of the links:
• One-on-one interaction
• Relationships that are one-to-many
• Relationship of Many-to-Many
One-to-Many and One-to-Many Relationships
In a One-to-Many connection, a single column value in one table has one or more dependent column values in another table.
• One-to-many Relationship Many-to-Many Relationship
The third table connects tables that are attempting to form a Many-to-Many relationship. The bridge table is used to store data that is shared by multiple tables with many-to-many relationships.
To eliminate duplicate records, SQL Server’s CTE and ROW NUMER features can be used.
An alias is a SQL feature that is supported by most, if not all, RDBMSs. For the purposes of a SQL query, it’s a made-up name for a table or table column. Furthermore, aliasing can be used to hide the true names of database columns using obfuscation. A table alias is also known as a correlation name.
Although the AS keyword is usually used to indicate an alias, it can be used without it in some cases. Using the AS keyword, on the other hand, is always a good idea.
Triggers are used to run a batch of SQL code when insert, update, or delete commands are run on a table. Triggers are automatically triggered or executed when data is updated. When inserting, deleting, or updating data, it can be run automatically.