You can refer the following data tables for examples. Here, we can use Self-Join as the requirement wants us to analyze the StudentDetails table as two different tables, each for Student and enrollment records.
In the mentioned below query first, we fetch the project-wise count and then sort the result by count. SQL queries to find the nth highest stipend form the table for various Databases are as described below. We can also use LEFT which returns the left part of a string till specified number of characters. We have to use of correlated subquery for finding the 3rd highest stipend the inner query will return us the count of till we find that there are two rows that stipend higher than other distinct stipends.
A hotfix is a single, cumulative package that includes one or more files that are used to address a problem in a software product i. Ex: If a select query returning duplicate rows with aggregations the result may be wrong…. Which key provides the strongest encryption? Ans: AES bit The longer the key, the better the encryption, so choose longer keys for more encryption. However, there is a larger performance penalty for longer keys.
What is the difference between memory and disk storage? Ans: Memory and disk storage both refer to internal storage space in a computer. What port do you need to open on your server firewall to enable named pipes connections? SQL Server retains backups of the previous six logs, naming each archived log file sequentially.
OUT, whereas archived files are numbered sequentially. The security log records authentication information, and the system log records service startup and shutdown information. If the summary. You can find the Profiler logs in the log. There are two authentication modes — Windows Mode Mixed Mode Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page.
This is mainly used to capture and save data about each event of a file or a table for analysis. What is recursive stored procedure? SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels. What are the differences between local and global temporary tables?
Local temporary tables are visible when there is a connection, and are deleted when the connection is closed. A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column.
Check constraint is to enforce integrity. Can SQL servers linked to other servers? What is sub query and its properties? A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements.
This can be used when expression is allowed. Properties of sub query can be defined as A sub query should not have order by clause A sub query should be placed in the right hand side of the comparison operator of the main query A sub query should be enclosed in parenthesis because it needs to be executed first before the main query More than one sub query can be included 8.
What are the types of sub query? There are three types of sub query — Single row sub query which returns only one row Multiple row sub query which returns multiple rows Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.
What is SQL server agent? What are scheduled tasks in SQL Server? Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval. This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.
This function is used to return a non-null from more than one column in the arguments. FLOOR function is used to round up a non-integer value to the previous least integer.
Can we check locks in database? If so, how can we do this lock check? Yes, we can check locks in the database. What is the use of SIGN function? Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations. What are the types of Triggers? There are four types of triggers and they are: Insert.
I enjoyed writing questions and answers. I have gotten many emails about complete series. Top most requests were to collect series in one big post so they can be easily used. I was asked to provide links to download them so they can be printed and referred. Another question I received is done I ask the same questions in interviews which I administrate. The answer is YES. Though, I have a few other questions, which I ask. All of them, I came up myself and answers are unique to questions and not available on-line.
Well, long story short, I have compiled list of questions in one PDF. Please download them and use them in your next interview or just for reading purpose. An interview Question and Answer discussion can be very helpful to both these individuals. It is simply a way to go back over the building blocks of a topic. It is not a way to re-learn a topic, but a way to remind yourself of what you already know.
What are functional Based indexes? Explain with Example Answer: 1. Function based indexes allows us to index on the functional columns so that oracle engine will take the index and improves the performance of the query.
As per requirements we are using lot of SQL functions to fetch the results. Function based indexs gives ability to index the computed columns. The role of the unique key is to make sure that each column and row are unique.
The syntax will be the same as the Primary key. So, the query using a Unique Key for the Employee table will be:. Both Primary and Unique key carry unique values but the primary key can not have a null value where the Unique key can. And in a table, there cannot be more than one Primary key but unique keys can be multiple. A foreign key is an attribute or a set of attributes that references to the primary key of some other table. Basically, it is used to link together two tables.
Indexes help speed up searching in the database. If there is no index on any column in the WHERE clause, then SQL Server has to skim through the entire table and check each and every row to find matches, which might result in slow operation on large data.
Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches. Composite-column Indexes: A composite-column index is an index created for two or more columns of the table. Unique Indexes: Unique indexes are used for maintaining the data integrity of the table. They do not allow multiple values to be inserted into the table. Entities: Entity can be a person, place, thing, or any identifiable object for which data can be stored in a database.
Relationships: Relationships between entities can be referred to as the connection between two tables or entities. For example: In a college database, the student entity and department entities are associated with each other.
That is all in the section of Basic SQL practice questions. The FLOOR function helps us to find the largest integer value to a given number which can be an equal or lesser number. Clustered index : It is used to sort the rows of data by their key values.
A clustered index is like the contents of a phone book. Since the data is located next to each other, it helps a lot in fetching data based on range-based queries. Also, the clustered index is actually related to how the data is stored. There is only one clustered index possible per table. Non-clustered index : It stores data at one location and indexes at some other location.
The index has pointers that point to the location of the data. As the index in the non-clustered index is stored in different places, there can be many non-clustered indexes for a table. Now, we will see the major differences between clustered and non-clustered indexes:. CDC is called change data capture. It records changes to SQL Server tables in a compatible format.
SQL MySQL It is a structured query language used in a database It is a database management system It is used for query and operating database system It allows data handling, storing, and modifying data in an organized manner. MySQL supports multiple storage engines. The server is independent in SQL During backup sessions, the server blocks the database. It is a programming language for a database that uses SQL. SQL is a declarative and data-oriented language. It is mainly used for the manipulation of data.
It is used for creating an application. It provides interaction with the database server. It does not provide interaction with the database server. To check the reliability of the transactions, ACID properties are used. Group functions operate on a series of rows and return a single result for each group. The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table.
The trigger is activated when the commands like insert, update, and delete are given. Normalization is used in reducing data redundancy and dependency by organizing fields and tables in databases. It involves constructing tables and setting up relationships between those tables according to certain rules. The redundancy and inconsistent dependency can be removed using these rules to make it more flexible.
Denormalization is contrary to normalization. In this, we basically add redundant data to speed up complex queries involving multiple tables to join. Here, we attempt to optimize the read performance of a database by adding redundant data or by grouping the data.
The FROM clause defines the tables and views from which data can be interpreted. The tables and views listed must exist at the time the question is given. The WHERE clause defines the parameters that would be used to limit the contents of the results table. You can test for basic relationships or for relationships between a column and a series of columns using subselects.
This GROUP BY clause is commonly used for aggregate functions to produce a single outcome row for each set of unique values in a set of columns or expressions. A function is an SQL Server database object. It is basically a set of SQL statements that allow input parameters, perform processing, and return results only. The function can only return a single value or table. The ability to insert, update, and delete records in database tables is not available.
The stuff function deletes a part of the string and then inserts another part into the string starting at a specified position. Here, String1 is the one that would be overwritten. Position indicates the starting location for overwriting the string.
Length is the length of the substitute string, and String2 is the string that would overwrite String1. Views are virtual tables used to limit the tables that we want to display, and these are nothing but the result of a SQL statement that has a name associated with it. Since views are not physically present, they take less space to store. We can create a view-only table for the female employees from the entire employee table.
A stored procedure is a prepared SQL code that can be saved and reused. In other words, we can consider a stored procedure to be a function consisting of many SQL statements to access the database system.
0コメント