Sql plsql interview questions and answers pdf

It causes the cursor to open C. It loads the current row values into variables D. It creates the variables to hold the current row values Ans: B 9. Only one B. All that apply C. All referenced D. None Ans: A Statement and Row B. Statement only C. Row only D.

Oracle Forms trigger Ans: D Which identifier is valid? For which task is it best to use a character string enclosed by double quotes? Please select the best answer. Referring to a column in the database B. Using a reserved word to declare an identifier C. Using a hyphen to concatenate two columns D.

Referring to a number variable by using a logical operator Ans: B. None B. All exceptions that are referenced in the block C. One D. All exceptions that apply Ans: C. Which functions can be used with any datatype? Please select all the correct answers. SUM B. MIN C. MAX D. Select incorrect variable declarations A.

Select invalid variable types A. CHAR B. List the correct sequence of commands to process a set of records when using explicit cursors A. None of the above ANS: B None of the above ANS: D Incantination B. Integration C. Continuation D. Concatenation E. Which of the following is not an Oracle DML function? NVL E. None of the above. All are valid. ANS: C. Select the best answer. Which listed attribute is an invalid attribute of an Explicit cursor.

All of these are valid. ANS: E Which of the following is not a grouping function. SUM C. MIN E. Due to its fault-tolerant nature, PostgreSQL has gained widespread popularity among developers. Indexes are the inbuilt functions in PostgreSQL which are used by the queries to perform search more efficiently on a table in the database.

Consider that you have a table with thousands of records and you have the below query that only a few records can satisfy the condition, then it will take a lot of time to search and return those rows that abide by this condition as the engine has to perform the search operation on every single to check this condition.

This is undoubtedly inefficient for a system dealing with huge data. Now if this system had an index on the column where we are applying search, it can use an efficient method for identifying matching rows by walking through only a few levels. This is called indexing. How will you change the datatype of a column? What is the command used for creating a database in PostgreSQL? The first step of using PostgreSQL is to create a database.

How can we start, restart and stop the PostgreSQL server? What are partitioned tables called in PostgreSQL? Partitioned tables are logical structures that are used for dividing large tables into smaller structures that are called partitions. This approach is used for effectively increasing the query performance while dealing with large database tables. To create a partition, a key called partition key which is usually a table column or an expression, and a partitioning method needs to be defined.

There are three types of inbuilt partitioning methods provided by Postgres: Range Partitioning: This method is done by partitioning based on a range of values. This method is most commonly used upon date fields to get monthly, weekly or yearly data. In the case of corner cases like value belonging to the end of the range, for example: if the range of partition 1 is and the range of partition 2 is , and the given value is 10, then 10 belongs to the second partition and not the first.

List Partitioning: This method is used to partition based on a list of known values. Most commonly used when we have a key with a categorical value.

For example, getting sales data based on regions divided as countries, cities, or states. Hash Partitioning: This method utilizes a hash function upon the partition key. This is done when there are no specific requirements for data division and is used to access data individually. For example, you want to access data based on a specific product, then using hash partition would result in the dataset that we require.

The type of partition key and the type of method used for partitioning determines how positive the performance and the level of manageability of the partitioned table are. Define tokens in PostgreSQL? A token in PostgreSQL is either a keyword, identifier, literal, constant, quotes identifier, or any symbol that has a distinctive personality.

They may or may not be separated using a space, newline or a tab. If the tokens are keywords, they are usually commands with useful meanings. Tokens are known as building blocks of any PostgreSQL code. What is the capacity of a table in PostgreSQL? Define sequence. A sequence is a schema-bound, user-defined object which aids to generate a sequence of integers. These data can be manipulated using MySQL programming language. In every industry, database is used for different functions, so there is a huge demand for the professionals having the knowledge of MySQL programming language.

Following is a list of MySQL interview questions and answers, which are frequently asked during the interview or viva exam. Candidates are likely to be asked basic to advanced level MySQL programming interview questions depending on their experience and various other factors. The below list covers all the important MySQL questions for freshers as well as experienced professionals.

What is MySQL? What are the technical features of MySQL? MySQL database software is a client or server system which includes Multithreaded SQL server supporting various client programs and libraries Different backend Wide range of application programming interfaces and Administrative tools. Why MySQL is used?

MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet. What are Heap tables? HEAP tables are present in memory and they are used for high speed storage on temporary basis. The numbers are same for Latin characters but they are different for Unicode and other encodings. ENUM is a string object used to specify set of predefined values and that can be used during table creation.

The length value ranges from 1 and When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved. Give string types available for column? It is used to delete all the records row by row It is used to delete all the records at a time By using the delete command we can delete a specific record By using truncate we cannot delete a specific record Where condition we can use with the delete command Where condition will not work with truncate Delete will work slow compare with truncate Truncate will work fast compare with delete Delete will not reset auto-generate id.

Once when we delete all the records from the table. Truncate will reset auto-generate id from starting number. What is the SQL Buffer? Only One Line i. What are SQL functions? Answer: We can achieve consistency by setting the appropriate isolation level. Answer: The error end-of-file on communication channel ORA means that there is a broken connection between the client and server channels. It could be a timeout because of which the connection was lost. You can troubleshoot by pinging the server and checking the connectivity.

Answer: SYS. If do you have some other questions, which we have not covered in this list, and you want to know the answer to those questions, just comments below.

A cheerful, full of life and vibrant person, I hold a lot of dreams that I want to fulfill on my own. My passion for writing started with small diary entries and travel blogs, after which I have moved on to writing well-researched technical content. I find it fascinating to blend thoughts and research and shape them into something beautiful through my writing. View all posts by the Author. Thank you , it is very nice questions and answers.

Thanks, Manoj. I got one question, in the answer to the the question of 'Difference between Procedure and Function', you saying 'A stored procedure can not be called from a function' is this statement correct? Also you said 'Exception handling is not possible' in Function, is this correct also. Pasting a simple code to disapprove the above two statements. Came across "TOP 30 SQL Interview Coding Tasks" by Matthew Urban, which contains deeply-explained, most commonly used data manipulation queries, relationships modeling, aggregation, sorting, table joins, index and other top topics to discuss during the job interview, on less then pages.

Really good book. Don't have an account? Sign Up. Already have an account? Interview Questions. Related Tutorials Data Science. Intro to Programming. Ramya Shankar. Table of Contents. Question: Define cursor and its use. Question: Why do we use database triggers? Give the syntax of a trigger.

Give an example. Question: What are the various predefined exceptions? Question: What is the difference between syntax and runtime errors? Question: Explain Character Functions? Explain with examples. Question: Explain the difference between procedure and function. Question: Explain the difference between procedure and trigger. Question: What are the different types of constraints?

Question: What are the differences between triggers and constraints? Question: Explain the difference between commit and savepoint.