SQL job interview: 50 Questions to Prepare in 2021-22
1. What does DBMS stand for?
DBMS: Database Management System or Database Management System.
It is a program that controls the creation, maintenance and use of a database.
DBMS can be thought of as a management file, which manages data in a Database rather than saving it to file systems.
2. What does RDBMS mean?
RDBMS: Relational Database Management System or Relational Database Management System
RDBMS stores data in a collection of tables, which are linked by common values in table columns. It also provides relational operators to manipulate data stored in a table.
3. What does SQL mean?
SQL is the abbreviation for Structured Query Language: Structured Query Language.
SQL is used to communicate with a Database. It is a standard language used to perform tasks such as retrieving, updating, inserting, and deleting data from a database.
4.What is a database?
Database is a form of data organized for easy access, storage, retrieval and management of data. It is a form of structured data that can be accessed in different ways.
5. What are the two authentication modes on the SQL server?
the two authentication modes on the SQL server are:
.Windows Mode
.Mixed Mode
The modes can be changed in the SQL server configuration menu.
6. What is an SQl Profiler?
Sql profiler is a tool that allows administration systems to monitor events in the SQL server. it is used to capture and save data on each event of a file or table, for analysis.
7. What is the difference between local temporary tables and global temporary tables?
Local temporary tables are visible on connection and deleted on disconnection. Basically, they are not permanently saved in the server.
Global temporary tables are visible to all users, and the connection that created them is deleted is closed.
8. What are the tables and fields?
The table is a set of data organized on rows and columns. Columns can be vertical and lines horizontal. A table has a number of columns called fields and rows called records.
Example:
Table: Employee,
The fields: ID, Name_employee, Date_of_birth, function_employee
Recordings: 5654, Gavid, 06/04/1987
9. What does CHECK constraint mean ?
The check constraint is applied to a column of a table to limit the values that can be in that column. Check constrained enforces integrity.
10. Could the SQL server be linked to other servers?
The SQL server can be connected to all databases which have the link OLE-DB provider. Example: Oracle has the OLE-DB provider which a connection link with all SQL servers.
11. What is a primary key ?
A primary key is used to identify each row or each record in a database table. The value of this key must always be non-zero, and each record must have a unique primary key.
The primary key is an index, each of the tables can only contain one primary key, made up of one or more fields or columns.
12. What is a unique key?
Unique key is used to identify a unique record in a table, and a table can have multiple unique keys. Unique key constraints can only accept one NULL value for a column.
13. What is the difference between primary key and unique key?
.When an attribute is declared as a primary key, it does not accept NULL values. However, when an attribute is declared Unique, it can accept a NULL value.
.A table can only have one primary key while there can be multiple unique keys.
.A clustered index created automatically when a primary key is set. In contrast, the Unique key generates the nonclustered index.
14. What is a foreign key ?
The foreign key represents a field (or fields) that points to the primary key of another table. The purpose of this key is to ensure the referential integrity of the data.
15. What is a join?
Joins allow you to combine several tables in the same query. This achieves results that combine data from multiple tables in an efficient manner.
16. List the different types of joins
.INNER JOIN : inner join to return records when the condition is true in both tables. This is one of the most common joins.
.CROSS JOIN : cross join allowing to make the Cartesian product of 2 tables. In other words, allows to join each row of a table with each row of a second table. Please note, the number of results is generally very high.
.LEFT JOIN (or LEFT OUTER JOIN): outer join to return all the records of the left table (LEFT = left) even if the condition is not verified in the other table.
.RIGHT JOIN (or RIGHT OUTER JOIN): outer join to return all the records of the right table (RIGHT = right) even if the condition is not checked in the other table.
.FULL JOIN (or FULL OUTER JOIN): outer join to return the results when the condition is true in at least one of the 2 tables.
.SELF JOIN : allows you to join a table with itself as if it were another table.
.NATURAL JOIN : natural join between 2 tables if there is at least one column with the same name between the 2 SQL tables
.UNION JOIN : union join
17. What does standardization mean in databases?
Normalization is the process of minimizing redundancy and dependency while organizing fields and tables in a database.
18. What does DeNormalization mean?
DeNormalisation is a technique used to access data from normal database forms. It is also a matter of introducing redundancy into a table by integrating data from other tables.
19. What are the different standardizations?
.1NF: A table must have a primary key. 1NF should eliminate all duplicate columns from a table.
.2NF:
.3NF
.4NF
20. What is a view?
A view is a virtual table that consists of a subset of data contained in a table. Views can have the combination of data from multiple tables if there are links between the tables.
21. What is an index?
This is the method of performance tuning for faster retrieval of records from a table. An index creates an entry for each value to retrieve data quickly.
22. What are the different types of indexes?
.Unique index
.Clustered index
.NonClustered Index
23. What are the relationships in the databases?
Database relationships are the links between tables in the database. There are different types of relationships:
.1 to 1
.1 to Several
.Many to 1
.Self-Referencing Relationship.
24. What is a request?
A request is a code written to retrieve information stored in a database. Quite simply, a query is a question put to the database.
25. What is a Sub query and its properties?
A subquery is a query within another query.
A subquery is a query that can be nested within a main query like Select, Update, Insert, or Delete. The outer query is called the main query, and the inner query is called a subquery.
The subquery is always executed first, and its result is passed to the main query for use.
26. What are the properties of the subquery?
A subquery:
. must not contain the ORDER BY clause
.must be placed to the right of the comparison operators
.must be written between two parentheses because it must be executed first
27. What is an SQL server agent?
The SQL Server Agent plays a primary role in the daily tasks of the SQL Server Administrator (DBA). The purpose of the server agent is to schedule tasks to run on a specific date and time.
28. What is a Trigger?
Triggers are used to used to execute SQL code when insert, update, or delete commands are executed on a table.
Triggers are automatically fired when data is changed. They can be executed automatically during insert, delete and update operations.
29. What does CDC mean?
CDC is the abbreviation for Change Data Capture.
CDC is used to capture recently modified data. This feature exists on sql server 2008.
30. What are the different types of triggers?
The 4 types of triggers are:
.Insert for insertion
.Delete for deletion
.Update for the update
.Instead of for modification31. What is the difference between DELETE and TRUNCATE?
DELETE is used to delete record rows from a table based on a condition defined in the WHERE clause of the query. Commit and Rollback functions can be executed after deletion.
TRUNCATE removes all rows from the table. Commit and Rollback functions cannot be executed TRUNCATE.
32. What is a constraint?
The constraint can be used to specify the limit on the data type of the table. The constraint can be specified when creating or modifying the table statement. Here are some examples of constraints:
.NOT NULL.
.CHECK.
.DEFAULT.
.UNIQUE.
.PRIMARY KEY.
.FOREIGN KEY.
33. What does Data Integrity mean?
Data integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to be respected in certain cases.34. What does Auto Increment mean?
The term AUTO INCREMENT allows the user to create a unique number to be generated each time a record row is inserted into the table. For example, the ID of each record can be auto incremented.
AUTO INCREMENT is used in Oracle and IDENTITY in SQL servers.
Usually, the attribute that is set in AUTO INCREMENT is used as the primary key.
35. What is a clause?
An SQL clause is used to limit the result rows by giving a condition to the query. In other words, The clause allows you to filter the records.
36. How do I select a single record from a table?
In order to select a single record with a particular condition, we use DISTINCT. Example:Select DISTINCT StudentID, StudentName from Student.
37. What is the difference between UNION and UNION ALL?
UNION: is used to select related information from two tables. It is similar to the JOIN command.
UNION ALL: similar to the UNION command. On the other hand, UNION ALL selects all records and does not erase duplicates. If the same record is present in the results of the two concatenated queries, UNION ALL will return the same result twice.
38. How will we be able to retrieve the version from the SQL server?
Select SERVERPROPERTY ('productversion')
39. How can we display the number of records in a table?
Select count (*) from <tablename>
40. How can we create a login?
CREATE LOGIN MyLogin WITH PASSWORD = '123';
41. What is the ISNULL () operator for?
The ISNULL () function is used to check if a value is null in SQL server. This function also allows to replace a value by the null value.
42. What is the use of the FOR clause?
The FOR clause is mainly used for XML and browser options. It is mainly used to display the results of a query in XML format or in the browser.
43. What is the difference between varchar and nvarchar data types?
Varchar and nVarchar are almost the same, the only difference is that nvarchar can be used to store Unicode characters for multiple languages and takes more storage space in the database compared to Varchar.
44. How to remove duplicate rows in SQL server?
Duplicate rows can be removed by using SQL Server's CTE and ROW NUMER function.
45. Where are the usernames and passwords stored in the SQL server?
the usernames and passwords in sys.server_principals and sys.sql_logins. But, the passwords are encoded.
46. What is the difference between GETDATE and SYSDATETIME?
Both have the same function. GETDATE can give the time down to milliseconds while SYSDATETIME can give the precision down to nanoseconds. SYSDATE TIME is more precise than GETDATE.
47. How can data be copied from one table to another?
INSERT INTO SELECT: This command is used to insert data into a table that is already created.
SELECT INTO: this command is used to create a new table with its structure, and the data can be copied from another existing table.
48. What is TABLESAMPLE?
TABLESAMPLE is used to extract a sample of random rows which are all needed for the application. the line sample taken is based on the percentage of lines.
49. What is the command used to display error messages?
RAISEERROR is the command used to initiate error processing for a given session. These user-defined messages are stored in the sys.messages table.
50. What is the command used to display the first 5 characters of a string or string?
Select SUBSTRING (StudentName, 1,5) as studentname from student
Select LEFT (Studentname, 5) as studentname from student
Comments