Why Do You Need Indexing Where Is Stored And What Do You Mean By Schema Object For What Purpose We Are Using View
We cant create an Index on an Index… The index is stored in the user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table. So that is a view.
Indexing is used for faster search or to retrieve data faster from the various tables. Schema containing a set of tables, basically schema means logical separation of the database. The view is crated for faster retrieval of data. Its a customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.
Do You Work Better On A Team With Just One Partner Or Alone
With this , interviewers are aiming to assess your personality as well as your preferred methods for completing tasks. Even if you lean more toward one way than the other, its important to demonstrate that youre motivated enough to work alone yet are a good team player and able to collaborate.
A sample answer could be:
Id have to say that Im equally comfortable working independently or as a member of a team. Im a self-starter who can maintain an efficient pace to complete projects on my own, but I also enjoy collaborating with and learning from others on a team.
What Do You Mean By Scheduled Tasks In Sql Server
Ans: To update the backups and statistics, databases must be maintained regularly. It can be achieved using scheduled jobs. In this way, the SQL server agent supports scheduling tasks in the SQL server to perform maintenance at regular intervals. In addition, the SQL server agent helps to view and modify the scheduled tasks.
| Related Article: How to Learn Error Handling in SQL Server |
Recommended Reading: How To Prepare For A Vet Tech Interview
Compare The Difference Between Clustered Index And The Non
Ans:
| Non-clustered Index | |
| It describes the order in which data is stored in tables physically. | It doesnt sort tables physically inside a table but creates a logical order for stored data. |
| Each table will have only one clustered index. | There could be many non-clustered indexes for a table. |
| Less storage is required since they only sort tables. | A non-clustered index is stored in one location, and data is stored in another. So, large storage is required. |
| Supports faster operations than the non-clustered index. |
Xml In Sql Interview Questions And Answers
Q. What is XDR?
A. In Microsoft® SQL Server⢠2000, the XML-Data Reduced language is used to create the schemas. The XDR is flexible and overcomes some of the limitations of the Document Type Definition , which also describes the document structure. Unlike DTDs, XDR schemas describe the structure of the document using the same syntax as the XML document. Additionally, in a DTD, all the data contents are character data. XDR language schemas allow you to specify the data type of an element or an attribute.
Q. What is the difference between FOR AUTO and FOR NESTED?
A. The NESTED mode of the client-side FOR XML is similar to the AUTO mode of the server-side FOR
Q. What is the difference between FOR XML RAW and FOR XML AUTO?A. RAW: Takes the query result and transforms each row in the result set into an XML element with a generic identifier < row /> as the element tag.
For e.g.
SELECT AppID, CommonName FROM Application for XML RAW
< row AppID=â6â³ CommonName=âInformation services Intranet systemsâ/> < row AppID=â127â³ CommonName=âPulp Sales Systemâ/>
< row AppID=â153â³ CommonName=âPulp Dryer Systemâ/> < row AppID=â154â³ CommonName=âChip Systemsâ/> < row AppID=â155â³ CommonName=âTime Collection Systemâ/>
AUTO: Returns query results in a simple, nested XML tree. Each table in the FROM clause is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes.
Q.Explain FOR XML EXPLICIT Mode?
Read Also: What Are The Most Asked Questions In An Interview
What Is The Difference Between Drop And Truncate Commands
If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints.
To create and use the table again in its original form, all the elements associated with the table need to be redefined.
However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.
How Can You Hide An Instance Of The Sql Server Database Engine
Ans: You can hide the SQL server database engine using the SQL Server configuration manager. The following two steps can accomplish it:
Don’t Miss: How To Analyze Interview Answers
Q62 Explain Different Types Of Normalization
There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one.The first three normal forms are usually adequate.
Normal Forms are used in database tables to remove or decrease duplication. The following are the many forms:
First Normal Form: When every attribute in a relation is a single-valued attribute, it is said to be in first normal form. The first normal form is broken when a relation has a composite or multi-valued property.
Second Normal Form:
A relation is in second normal form if it meets the first normal forms requirements and does not contain any partial dependencies. In 2NF, a relation has no partial dependence, which means it has no non-prime attribute that is dependent on any suitable subset of any table candidate key. Often, the problem may be solved by setting a single column Primary Key.
Third Normal Form: If a relation meets the requirements for the second normal form and there is no transitive dependency, it is said to be in the third normal form.
Q63. What is OLTP?
OLTP, or online transactional processing, allows huge groups of people to execute massive amounts of database transactions in real time, usually via the internet. A database transaction occurs when data in a database is changed, inserted, deleted, or queried.
What are the differences between OLTP and OLAP?
Q64. How to create empty tables with the same structure as another table?
To create empty tables:
What Are Database Normalisation And Denormalisation
Ans: Database normalization is the process of restructuring a relational database to reduce data redundancy and improve data integrity. On the other hand, denormalization is a reverse engineering process that helps increase the read performance of a database. And it is achieved by either adding copies of data or grouping data. Hence, data can be read in a shorter time.
Also Check: How To Answer System Design Interview Questions
Why Should We Hire You
Your answer to this question is an opportunity for you to not only sell yourself to the interviewer, but also a chance for you to set yourself apart from the competition. Be specific in your answer and concisely summarize your , experience, qualifications, and achievements:
I know that Id be an asset in this position as Ive already proven my skills in previous internships at high-profile companies and have a portfolio of successful projects. I started programming at a young age, and I regularly contribute to open-source coding projects, which proves my ambition and overall passion for what I do. I have the knowledge to start positively contributing on my first day, and I know that I could bring a new and unique perspective to the company.
What Is A Unique Key
A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.
A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.
There can be many unique constraint defined per table, but only one Primary key constraint defined per table.
Read Also: What Are The Most Questions Asked In An Interview
Sql Query Interview Questions For Experienced Professionals
The interviewers might present a SQL query and ask questions on the same. They can also ask you to write SQL queries. Here are some SQL query interview questions for experienced professionals.
EmpID
Answer the following SQL query interview questions with reference to the above table:
- Write a SQL query to fetch the EmpFname in the upper case. Use the ALIAS name as EmpName.
- Write a SQL query to retrieve the number of employees who are a part of the HR department.
- Write a query to fetch the first four characters of EmpLname whose name starts with ‘W.’
- Write a SQL query to retrieve the place name, i.e., the string before brackets, from the Address column.
- Write a SQL query to fetch the names of employees that begin with ‘R.’
Recommended Reading:SQL query interview questions based on a sample data table
What Is Blocking And How Would You Troubleshoot It
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
| If you want to build your career with a SQL Server certified professional, then visit our SQL Server Online Training Course. This course will help you to achieve excellence in this domain. |
Read Also: What Are The Top 10 Phone Interview Questions
What Do You Understand By Self Join Explain Using An Example
Self Join in SQL is used for joining a table with itself. Here, depending on some conditions, each row of the table is joined with itself and with other rows of the table.
Syntax:
SELECT a.column_name, b.column_nameFROM table a, table bWHERE condition
Example:
Consider the customer table given below.
Example:
Consider the customer table given below.
| ID |
Output:
What Is A Live Lock
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Also Check: What Is An Exit Interview For
Q25 What Are Some Common Clauses Used With Select Query In Sql
The following are some frequent SQL clauses used in conjunction with a SELECT query:
WHERE clause: In SQL, the WHERE clause is used to filter records that are required depending on certain criteria. ORDER BY clause: The ORDER BY clause in SQL is used to sort data in ascending or descending order depending on specified field . GROUP BY clause: GROUP BY clause in SQL is used to group entries with identical data and may be used with aggregation methods to obtain summarised database results. HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.
Sql Interview Questions And Answers For Experienced
SQL Interview Questions And Answers For Experienced
SQL is one of the most asked topic in the Interview. Here below you will get the SQL interview questions for experienced 2020. This page will help you in preparing for Interviews.
PrepInsta provides you the most asked Interview Question of SQL.
Along with SQL Interview Questions and Answers for Experienced you also need to prepare well for SQL Structured Query Language
This page will provide you detailed information about all the type of questions asked in SQL Interview Questions and Answers for Experienced
You May Like: How To Pass The Us Citizenship Test And Interview
How Can You Distinguish Between Getdate And Sysdatetime Functions
Ans: The GETDATE function returns the date and time of a location. In contrast, the SYSDATETIME function returns the date and time with the precision of 7 digits after the decimal point.
You can understand this from the following examples.
SELECT SYSDATETIME AS CURRENT_DATE_TIME CURRENT_DATE_TIME2015-1016 12:37:06 . 6151778
What Are The Key Differences Between Sql And P/l Sql
SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early ’90s. It adds procedural features of programming languages in SQL.13. ### What is data definition language?
DDL or Data Definition Language pertains to the SQL commands directly affecting the database structure. DDL is a category of SQL command classifications that also include DML , Transactions, and Security. A particular attribute of DDL commands is statements that can manipulate indexes, objects, tables, views, triggers, etc. Three popular DDL keywords in SQL are:
CREATE â which is used to create a table
CREATE TABLE tableName
ALTER â used to modify entries or existing columns within a table.
ALTER TABLE tableName
DROP â used to Delete or Drop an existing table along with its entries, constraints, triggers, indexes, and permissions. Essentially deletes the table.
DROP TABLE tableName
Also Check: How To Prepare To Interview Someone
Q1 What Is Normalization What Are The Different Normalizations
The process of organizing fields and tables of a database to minimize redundancy and dependency. It allows you to add, delete or modify fields that can be incorporated into a single table. The different normalizations are
- First Normal Form : This should remove a table’s duplicate columns. It is for the identification of unique columns and the creation of tables for the related data.
- Second Normal Form : A table is in its second normal form if it meets all requirements of the first normal form and places the subsets of data in separate tables. The relationships between tables are created using primary keys.
- Third Normal Form : The table should be in the second normal form. There should be no dependency on primary key constraints.
- Fourth Normal Form : It should meet all the requirements of the third normal form and should not have multi-valued dependencies.
Explain Inner Join With An Example
Inner Join basically gives us those records that have matching values in two tables.
Let us suppose that we have two tables, Table A and Table B. When we apply Inner Join on these two tables, we will get only those records that are common to both Table A and Table B.
Syntax:
SELECT columnsFROM table1INNER JOIN table2ON table1.column_x=table2.column_y
Example:
select * from employeeselect * from department
Output:
Now, we will apply Inner Join to both these tables, where the e_dept column in the employee table is equal to the d_name column of the department table.
Syntax:
select employee.e_name, employee.e_dept, department.d_name, department.d_locationfrom employee inner join departmentonemployee.e_dept=department.d_name
Output:
After applying Inner Join, we have only those records where the departments match in both tables. As we can see, the matched departments are Support, Analytics, and Sales.
Don’t Miss: How To Prepare For A Marketing Interview
What Is A Transaction Log And Why Is It Important
Ans: Transaction log records all transactions and related database modifications of every transaction. To be precise, it records the beginning of a transaction, the changes during the transaction, and the last COMMIT or ROLLBACK of the transaction. The transaction log is one of the vital components in database management since it is used to retrieve the database to a consistent state if there is a system failure.
What Are The Advantages Of Using Stored Procedures In An Sql Server
Ans:
- Stored procedures provide faster performance since they are compiled and stored in executable form.
- A stored procedure is nothing but a piece of code that can be used many times, increasing productivity.
- It can be stored in a database server instead of on a client machine. As a result, it increases the speed of query execution.
- It can be used in many applications after the successful compilation
Read Also: How To Prepare For Immigration Interview
How Can You Differentiate Between Union And Union All
Ans: UNION is the operator that combines two queries into a single result set using select statements. Note that it functions based on specified conditions.
Syntax: query1 UNION query2
UNION ALL is the operator that combines two queries into a single result set, extracting all the rows from the tables without any conditions.
Syntax: query1 UNION ALL query2
Provide A Sample Of A Correlated Subquery
As with any interview for a job in which youll be writing specific programming languages, you can bet that youll have multiple questions that require you to show your skills firsthand. Interviewers will want to see your coding abilities as well as test your general adaptability.
You could provide something like:
SELECT employee_number, nameWHERE department =emp.department)
Read Also: How To Sell Yourself In An Interview
