SQL Interview Questions And Answers Pdf
1. What is BCP? When is it used?
Answer: It is a tool used to duplicate an enormous quantity of information from tables and views. It does not facsimile the structures the same as a foundation to target.
BULK INSERT command helps to bring in a data folder into a record, table or view in a user-specific arrangement.
2. Explain the steps needed to create a scheduled job?
Answer:
Steps to create a Scheduled Job:
Connect to the database of SQL Server in SQL Server Management Studio. On the SQL Server Agent, we will find a Jobs folder.
Right-click on jobs and choose Add New.
A New Job window will come into view. Give an associated name for the same.
Click next on the “Steps” in the left list of options. An SQL job can have multiple steps either in the form of SQL declaration or a stored practice call.
Click on the “Schedules” in the left list of options. An SQL job can comprise of one or supplementary schedules. It is the instance at which the SQL job will jog itself. We can spell out returning schedules also.
3. What is the native system stored procedure to execute a command against all databases?
Answer: The sp_MSforeachdb system stored procedure accepts the @Command parameter which can be exetecuted against all databases. The ‘?’ is used as a placeholder for the database name to execute the same command.
The alternative is to use a cursor to process specific commands against each database.
4. Describe how to use the Linked Server?
Answer: MS SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred to as Linked Server.
The following are the steps to use Linked Server for any OLE DB. You can refer to this to use an MS-Excel workbook.
Open SQL Server Management Studio in SQL Server.
Expand Server Objects in Object Explorer.
Right-click on Linked Servers. Click on New Linked Server.
Select General page in the left pane and
Type any name for the linked server in the first text box.
Select the Other Data Source option.
Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list.
Type the Excel as the name of the OLE DB data source.
Type the full path and file name of the Excel file in the Data Source box.
Type the Excel version no. (7.0, 8.0 etc) in the Provider String. Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
5. Find What is Wrong in this Query?
Answer: SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code; The WHERE clause cannot be used to restrict groups. Instead, the HAVING clause should be used.
SELECT subject_code, AVG (marks)
FROM students
HAVING AVG(marks) > 75
GROUP BY subject_code;
6. What are the risks of storing a hibernate-managed object in a cache? How do you overcome the problems?
Answer: The primary problem here is that the object will outlive the session it came from. Lazily loaded properties won’t get loaded if needed later. To overcome the problem, perform cache on the object’s id and class and then retrieve the object in the current session context.
7. Can you explain about buffer cash and log Cache in SQL Server?
Answer: Buffer Cache: Buffer cache is a memory pool in which data pages are read. The ideal performance of the buffer cache is indicated as 95% indicates that pages that were found in the memory area 95% of the time. Another 5% is needed for physical disk access.
If the value falls below 90%, it is the indication of more physical memory requirement on the server.
Log Caches: Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log cache separately from the buffer cache.
8. What is SQL Profiler?
Answer: Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.
Use SQL Profiler to monitor only the events in which you are interested.
If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period.
9. What is de-normalization in SQL database administration? Give examples?
Answer: De-normalization is used to optimize the readability and performance of the database by adding redundant data. It covers the inefficiencies in the relational database software.
De-normalization logical data design tends to improve the query responses by creating rules in the database which are called constraints.
Examples include the following:
Materialized views for implementation purpose such as :
Storing the count of “many” objects in a one-to-many relationship.
Linking attribute of one relation with other relations.
To improve the performance and scalability of web applications.
10. What is a Database?
Answer: A Database is defined as a structured form of data that is stored in a computer or data in an organized manner and can be accessed in various ways. It is also the collection of schemas, tables, queries, views, etc. The database helps us in easily storing, accessing and manipulation of data held on a computer. The Database Management System allows a user to interact with the database.
11. Write an SQL query to find the names of employees start with ‘A’?
Answer: The LIKE operator of SQL is used for this purpose. It is used to fetch filtered data by searching for a particular pattern in where clause.
The Syntax for using LIKE is,
SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern;
LIKE: operator name
pattern: exact value extracted from the pattern to get related data in
result set.
The required query is:
SELECT * FROM Employees WHERE EmpName like ‘A%’ ;
You may refer to this article on the WHERE clause for more details on the LIKE operator.
12. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
Answer: When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
13. What is the difference between Local and Global temporary tables?
Answer: If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the DB but its rows disappear when the connection is closed.
14. What are the advantages of Views?
Answer:
Some of the advantages of Views are
Views occupy no space
Views are used to simply retrieve the results of complicated queries that need to be executed often.
Views are used to restrict access to the database or to hide data complexity.
15. What are the triggers in SQL?
Answer:
Triggers are special types of stored procedures that get executed when a specified event occurs. Syntax-
- CREATE TRIGGER
- triggerName
- triggerTime{Before or After}
- triggerEvent{Insert, Update or Delete}
- ON tableName
- FOR EACH ROW
- trigger body.
16. What are orphan records?
Answer: Orphan records are the records having a foreign key to a parent record that doesn’t exist or got deleted.
17. What XML support does the SQL server extend?
Answer:
SQL Server (server-side) supports 3 major elements:
Creation of XML fragments: This is done from the relational data using FOR XML to the select query.
Ability to shred xml data to be stored in the database.
Finally, storing the xml data.
Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of :
XML Views: providing a bidirectional mapping between XML schemas and relational tables.
Creation of XML Templates: allows the creation of dynamic sections in XML.
SQL Server can return XML document using FOR XML clause. XML documents can be added to the SQL Server database and you can use the OPENXML clause to display the data from the document as a relational result set. SQL Server 2000 supports XPath queries.
18. What are the different DML commands in SQL?
Answer: DML commands are used for managing data present in the database.
SELECT: To select specific data from a database
INSERT: To insert new records into a table
UPDATE: To update existing records
DELETE: To delete existing records from a table
19. Are NULL values the same as that of zero or a blank space?
Answer: A NULL value is not at all same as that of zero or a blank space. The NULL value represents a value that is unavailable, unknown, assigned or not applicable whereas zero is a number and blank space is a character.
20. What do you mean by data manipulation language?
Answer: Data manipulation Language or DML is used to access or manipulate data in the database.
It allows us to perform below-listed functions:
Insert data or rows in a database
Delete data from a database
Retrieve or fetch data
Update data in the database.
21. What are the aggregate functions in SQL?
Answer: Aggregate functions are the SQL functions that return a single value calculated from multiple values of columns. Some of the aggregate functions in SQL are-
Count() – Returns the count of the number of rows returned by the SQL expression
Max() – Returns the max value out of the total values
Min() – Returns the min value out of the total values
Avg() – Returns the average of the total values
Sum() – Returns the sum of the values returned by the SQL expression
22. What is the difference between GUI Testing and Database Testing?
Answer: GUI Testing is AKA User Interface Testing or Front-end testing
Database Testing is AKA back-end testing or data testing.
GUI Testing deals with all the testable items that are open to the user to interaction such as Menus, Forms, etc.
Database Testing deals with all the testable items that are generally hidden from the user.
The tester who is performing GUI Testing doesn’t need to know Structured Query Language
The tester who is performing Database Testing needs to know Structured Query Language
GUI Testing includes invalidating the text boxes, checkboxes, buttons, drop-downs, forms, etc., majorly the look and feel of the overall application
Database Testing involves verifying the integrity of data in the front end with the data present in the back end. It validates the schema, database tables, columns, indexes, stored procedures, triggers, data duplication, orphan records, junk records. It involves updating records in a database and verifying the same on the front end.
23. What is the difference between primary key and unique constraints?
Answer: The primary key cannot have a NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple uniques constrains. The primary key creates the clustered index automatically but the Unique key does not.
24. What is subquery in SQL?
Answer: A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. In a subquery, the outer query is called the main query whereas the inner query is called subquery. Subqueries are always executed first and the result of the subquery is passed on to the main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any comparison operators such as >,<.
25. What is a Primary Key?
Answer: A primary key is a column or a combination of columns that uniquely identifies a record in the database. A primary key can only have unique and not NULL values and there can be only one primary key in a table.
26. What is a column in a Table?
Answer: A column is a vertical entity in a table that contains all information associated with a specific field in a table.
27. What is an index?
Answer: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on disk. To support faster access according to different values, faster search like a binary search for different values is desired. For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.
This article is contributed by Harsh Agarwal. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeek’s main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
ArticlesDBMSSQL placement preparation Login to Improve this Article
Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.
Recommended Posts:
SQL | Functions (Aggregate and Scalar Functions)
Basic SQL Injection and Mitigation with Example
Commonly asked DBMS interview questions | Set 1
SQL | Join (Inner, Left, Right and Full Joins)
SQL | Views
Going to an Interview
28. What is a Unique constraint?
Answer: A unique constraint is used to ensure that the field/column will have a unique value(no duplication).
29. What is the difference between a unique key and primary key?
Answer: A unique key allows null value(although only one) but a primary key doesn’t allow null values. A table can have more than one unique key column while there can be only one primary key. A unique key column creates a non-clustered index whereas the primary key creates a clustered index on the column.
30. What is a clustered index?
Answer: Clustered indexes physically sort the rows in the table based on the clustering key(by default primary key). A clustered index helps in the fast retrieval of data from the databases. There can be only one clustered index in a table.
31. What is a Not Null constraint?
Answer: A composite key is a primary key with multiple columns as in the case of some tables a single field might not guarantee unique and not null values, so a combination of multiple fields is taken as the primary key.
32. What is a Field in a Database?
Answer: A field in a database table is a space allocated to store a particular record within a table.
33. What is a Foreign Key??
Answer: A foreign key is used for enforcing referential integrity in which a field marked as a foreign key in one table is linked with the primary key of another table. With this referential integrity, we can have only the data in a foreign key which matches the data in the primary key of the other table.
34. What is a non-clustered index?
Answer: Non clustered indexes have a jump table containing key-values pointing to a row in the table corresponding to the keys. There can be multiple clustered indexes in a table.
35. What is a NULL value?
Answer: A NULL value in SQL is an unknown or blank value. Since NULL is unknown value so, the NULL value cannot be compared with another NULL value. Hence we cannot use ‘=’ operator in where condition with NULL. For this, we have an IS NULL clause that checks if the value in the field is NULL or not.
36. What is Database Normalisation?
Answer: Database normalization is the process of organization of data to reduce the redundancy and anomalies in the database. We have different Normalisation forms in SQL like – First Normal Form, Second Normal Form, Third Normal Form, and BCNF.
37. Explain the Third Normal Form(3NF)?
Answer: For a table to be Third Normal Form, it must follow 2NF and each non-prime attribute must be dependent on the primary key of the table.
For each functional dependency X -> Y either-
X should be the super key or Y should be the prime attribute(part of one of the candidate keys) of the table.
38. What is the difference between cross join and full outer join?
Answer: A cross join returns cartesian product of the two tables, so there is no condition or on clause as each row of table is joined with each row of tables whereas a full outer join will join the two tables on the basis of condition specified in the on clause and for the records not satisfying the condition null value is placed in the join result.
39. What is the difference between Local and Global temporary tables?
Answer: If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the DB but its rows disappear when the connection is closed
40. How can we remove orphan records from a table?
Answer: To remove orphan records from the database we need to create a join on the parent and child tables and then remove the rows from the child table where id IS NULL.
DELETE PT
FROM ParentTable PT
LEFT JOIN ChildTable CT
ON PT.ID = CT.ID
WHERE PT.ID IS NULL
*Remember: Delete with joins requires name/alias before from clause to specify the table of which data is to be deleted.
41. What is the difference between SQL and PL/SQL?
Answer: SQL is a structured query language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.
42. What is the Cartesian product of the table?
Answer: The output of Cross Join is called a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 Rows.
43. What is DBMS?
Answer: Database – SQL Interview Questions – EdurekaA database is a structured collection of data.
A Database Management System (DBMS) is a software application that interacts with the user, applications and the database itself to capture and analyze data.
A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved and deleted and can be of any type like strings, numbers, images, etc.
There are two types of DBMS:
Relational Database Management System: The data is stored in relations (tables). Example – MySQL.
Non-Relational Database Management System: There is no concept of relations, tuples, and attributes. Example – Mongo
44. What do you mean by table and field in SQL?
Answer: A table refers to a collection of data in an organized manner in the form of rows and columns. A field refers to the number of columns in a table. For example:
Table: StudentInformation
Field: Stu Id, Stu Name, Stu Marks
45. What are the different subsets of SQL?
Answer:
The different subsets of SQL are:
DDL (Data Definition Language): It allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects.
DML ( Data Manipulation Language) – It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
DCL ( Data Control Language) – It allows you to control access to the database. Example – Grant, Revoke access permissions.
46. What is the difference between CHAR and VARCHAR2 datatype in SQL?
Answer: Both of these data types are used for characters but varchar2 is used for character strings of variable length whereas char is used for character strings of fixed length. For example, if we specify the type as char(5) then we will not be allowed to store string of any other length in this variable but if we specify the type of this variable as varchar2(5) then we will be allowed to store strings of variable length, we can store a string of length 3 or 4 or 2 in this variable.
Name different types of case manipulation functions available in SQL.
There are three types of case manipulation functions available in SQL. They are,
LOWER: The purpose of this function is to return the string in lowercase. It takes a string as an argument and returns the string by converting it into a lower case.
Syntax:
LOWER(‘string’)
UPPER: The purpose of this function is to return the string in uppercase. It takes a string as an argument and returns the string by converting it into uppercase.
Syntax:
UPPER(‘string’)
INITCAP: The purpose of this function is to return the string with the first letter in uppercase and the rest of the letters in lowercase.
Syntax:
INITCAP(‘string’)
47. What is the difference between DELETE and TRUNCATE statements?
Answer: The difference between truncate and delete command are represented in the below table:
DELETE TRUNCATE
Delete command is used to delete a row in a table. Truncate is used to delete all the rows from a table.
You can rollback data after using the delete statement. You cannot rollback data.
It is a DML command. It is a DDL command.
It is slower than a truncate statement. It is faster.
48. What is the difference between SQL and MySQL?
Answer: SQL is a standard language that stands for Structured Query Language based on the English language whereas MySQL is a database management system. SQL is the core of the relational database which is used for accessing and managing database, MySQL is an RDMS (Relational Database Management System) such as SQL Server, Informix, etc.
49. What is the difference between clustered and non clustered index in SQL?
Answer:
The differences between the clustered and non clustered index in SQL are:
A clustered index is used for easy retrieval of data from the database and its faster whereas reading from non clustered index is relatively slower.
Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index whereas in a non clustered index, it does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching.
One table can only have one clustered index whereas it can have many non clustered index.
50. List the different types of joins?
Answer: There are various types of joins that are used to retrieve data between the tables. There are four types of joins, namely:
Joins – SQL interview Questions -Inner joins: Inner Join in MySQL is the most common type of join. It is used to return all the rows from multiple tables where the join condition is satisfied.
Left Join: Left Join in MySQL is used to return all the rows from the left table but only the matching rows from the right table where the join condition is fulfilled.
Right, Join: Right Join in MySQL is used to return all the rows from the right table but only the matching rows from the left table where the join condition is fulfilled.
Full Join: Full join returns all the records when there is a match in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
Note: Browse latest SQL Interview Questions and SQL Tutorial Videos. Here you can check SQL Training details and SQL Training Videos for self learning. Contact +91 988 502 2027 for more information.