|
SQL Server is a relational database management system which uses the standard programming language for interacting with the relational databases. It is a database server with application software whose primary function is to store and retrieve the data of other software applications which run from the same system or from other systems across the network.
In this article, you can go through the set of SQL Server interview questions most frequently asked in the interview panel. This will help you crack the interview as the topmost industry experts curate these at HKR training.
Let us have a quick review of the SQL Server interview questions.
Ans: A database management system (DBMS) is application software that interacts with the user, applications and the database to represent and analyze the data.
A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved, deleted and the data in the database can be of any type like strings, numbers, images etc.
There are two types of DBMS:
Ans: A join is a clause which combines rows from two or more tables based on a related column between them. The join merges two tables or will retrieve the data from it. There are four joins.
Ans:
| Candidate_ID | Candidate_Name |
| 1 |
Ravi |
| 2 | Gopi |
| 3 | Raju |
| 4 | Deva |
Ans:
Ans:
Delete:
Truncate:
Ans: There are six properties for a relational table.
Ans:
Primary key:
Unique key:
Ans:
Ans:
Ans: There are many levels of normalization which are called normal forms. Each consecutive normal form is dependant on the previous normal form.
Ans:
Ans: The trigger allows us to execute a batch of SQL code whenever the event of table arises such as INSERT, UPDATE or DELETE command is executed. Triggers are stored and managed by DBMS which also executes a stored procedure.
There are three types of triggers available in the SQL Server as follows.
Ans: A subquery is a subset of an SQL query. It is a subset of SELECT statements whose return values are used in filtering conditions of the main query. The subqueries can be applied in SELECT clause, FROM clause and WHERE clause. It can be nested inside a SELECT, INSERT, UPDATE or DELETE statement or inside another subquery.
Types of Sub-query:
Ans:
Ans: It is a virtual table that contains data from one or more tables. Views will select only the required values for restricting the data access of the table and the complex queries simple.
Rows updated or deleted in the view are updated or deleted in the table the view was created with. When the data in the original table changes, so as the data in the view also changes, as views are the way which looks at part of the original table. The results of using a view are not permanently stored in the database.
Ans: ACID properties stand for Atomicity, Consistency, Isolation and Durability. It ensures the data transactions are processed reliably in a database system.
Ans:
Entity: It is a real-world object which can be identified easily.
Example: A college database contains entities such as students, professors, workers, departments and projects. Each entity has an associated property that provides an identity.
Relationships: The relationships are the links or association between the entities.
Example: The company database contains the employee’s table that is associated with the salary table in the same database.
Ans: Alias is a temporary name assigned to the table or table column for the purpose of a particular SQL query. A table alias is also called a correlation name. The alias name can be referred in WHERE clause to identify a particular table or a column. An alias is represented by using the AS keyword.
Example:
Select emp.empID, dept.Result from employee emp, department as dept where emp.empID=dept.empID
In the above example, emp refers to alias name for employee table and dept refers to alias name for department table.
Ans: A stored procedure is a set of SQL queries which take input and send back output. If the procedure is modified, all clients automatically get the new version. Stored procedures will improve performance and reduce network traffic. Stored procedures ensure database integrity.
Syntax:
DELIMITER $$
CREATE PROCEDURE FetchAllStudents()
BEGIN
SELECT * FROM myDB.students;
END $$
DELIMITER ;
Ans: A cursor in a database is a control structure that traverses the records in a database. Cursors also facilitate processing the retrieval, addition and deletion of database records after traversal. It can be viewed as a pointer to one row in a set of rows.
Working with SQL Cursor
Syntax:
DECLARE @name VARCHAR(50) /* Declare All Required Variables */
DECLARE db_cursor CURSOR FOR /* Declare Cursor Name*/
SELECT name
FROM myDB.students
WHERE parent_name IN ('Sara', 'Ansh')
OPEN db_cursor /* Open cursor and Fetch data into @name */
FETCH next
FROM db_cursor
INTO @name
CLOSE db_cursor /*Close cursor and deallocate the resources*/
DEALLOCATE db_cursor
Ans: The relationships in SQL are.
Ans: In SQL, LIKE operator is used for matching the patterns. It can be used as.
select * from candidates where candidatename like ‘a%’
2._ (Underscore): It matches exactly one character.
Example:
select * from candidates where candidatename like ‘abc_’
Ans:
1.JOIN or INNER JOIN: It retrieves the records of matching values in both table involved in a join.
Syntax:
SELECT * FROM Table_A JOIN Table_B;
SELECT * FROM Table_A INNER JOIN Table_B;
2.LEFT JOIN: It retrieves all the records/rows from the left table and the matched records/rows from the right table.
Syntax:
SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.col = B.col;
3.RIGHT JOIN: It retrieves all the records/rows from the right table and the matched records/rows from the left table.
Syntax:
SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.col = B.col;
4.FULL JOIN: It retrieves all the records where there is a match in either the left or right table.
Syntax:
SELECT * FROM Table_A A FULL JOIN Table_B B ON A.col = B.col;
Ans: User-defined function is created as follows.
Example:
CREATE Function fun1(@num int)
returns table
as
return SELECT * from employee WHERE [email protected];
The function can be executed as follows:
SELECT * from fun1(31);
The name of ‘fun1’ is created to fetch employee details of an employee having empid=31.
Ans: The following commands are used in a table for counting the records.
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
Ans:
SELECT TOP 1 salary FROM
(SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
Ans: There are three TCL commands available in SQL Server.
Commit: This command saves the transaction permanently in the database.
Rollback: This command is used to roll back the changes that are done which means restoring the database of the last committed state.
Save Tran: It is used for saving the transaction to provide the convenience that the transaction can be rolled back to the point wherever required.
Wish to make a career in the world of Couchbase? Start with HKR'S Couchbase Training !
Ans: The common clauses used along with SELECT query are as follows.
Example:
SELECT * FROM myDB.students
WHERE graduation_year = 2019
ORDER BY studentID DESC;
3.GROUP BY: It is used to group records with identical data and can be used in conjunction with some aggregation functions for producing the summarized results from the database.
4.HAVING: It is used for filtering the records in combination with the GROUP BY clause. It is different from WHERE as it cannot filter aggregated records.
Example:
SELECT COUNT(studentId), country FROM myDB.students
WHERE country != "INDIA"
GROUP BY country
HAVING COUNT(studentID) > 5;
Ans:
BETWEEN: This operator is used for displaying rows based on range of values in a row
Example:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;
IN: This operator is used to check the values contained in a specific set of values.
Example:
SELECT * FROM students where ROLL_NO IN (8,15,25);
Ans: The collation is a set of rules which determines how the data can be sorted and compared. Character data is sorted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width etc. The different types of collation sensitivity are.
Batch starts on 7th Jun 2023, Weekday batch
Batch starts on 11th Jun 2023, Weekend batch
Batch starts on 15th Jun 2023, Weekday batch
©2023 HKR Trainings. All rights Reserved.
On Our Website all Courses, Technologies, logos, and certification titles we use are their respective owners' property, Trademarks & their intellectual Property belong to them. All the firm, service, or product names on our website are solely for identification purposes. We do not own, endorse or have the copyright of any brand/logo/name in any manner. Few graphics on our website are freely available on public domains. we use all these just for the purpose of training only.
