Sql interview questions pdf tutorial point download

A sequence is a schema-bound, user-defined object which aids to generate a sequence of integers. This is most commonly used to generate values to identity columns in a table. They are character sequences bound within single quotes. These are using during data insertion or updation to characters in the database.

There are special string constants that are quoted in dollars. They are database transaction properties which are used for guaranteeing data validity in case of errors and failures. MVCC or Multi-version concurrency control is used for avoiding unnecessary database locks when 2 or more requests tries to access or modify the data at the same time.

This ensures that the time lag for a user to log in to the database is avoided. The transactions are recorded when anyone tries to access the content. For more information regarding this, you can refer here.

The command enable-debug is used for enabling the compilation of all libraries and applications. When this is enabled, the system processes get hindered and generally also increases the size of the binary file. Hence, it is not recommended to switch this on in the production environment.

This is most commonly used by developers to debug the bugs in their scripts and help them spot the issues. For more information regarding how to debug, you can refer here. SQL standards state that the following three phenomena should be prevented whilst concurrent transactions. SQL standards define 4 levels of transaction isolations to deal with these phenomena. To tackle these, there are 4 standard isolation levels defined by SQL standards. They are as follows:.

Write Ahead Logging is a feature that increases the database reliability by logging changes before any changes are done to the database. This ensures that we have enough information when a database crash occurs by helping to pinpoint to what point the work has been complete and gives a starting point from the point where it was discontinued.

For more information, you can refer here. In case our requirement entails just remove the data, then we would need to recreate the table to store data in it. For example:. The steps are as follows:. Full-Text Search is the method of searching single or collection of documents stored on a computer in a full-text based database.

However, the feature is present but is pretty basic in PostgreSQL. Parallel Queries support is a feature provided in PostgreSQL for devising query plans capable of exploiting multiple CPU processors to execute the queries faster.

The commit action ensures that the data consistency of the transaction is maintained and it ends the current transaction in the section. Whereas, a checkpoint is used for writing all changes that were committed to disk up to SCN which would be kept in datafile headers and control files. SQL is a language for the database.

There are many such commands which provide a large amount of control to the programmer to interact with the database in an efficient way without wasting many resources.

The popularity of SQL has grown so much that almost every programmer relies on this to implement their application's storage functionalities thereby making it an exciting language to learn. Learning this provides the developer a benefit of understanding the data structures used for storing the organization's data and giving an additional level of control and in-depth understanding of the application.

PostgreSQL being an open-source database system having extremely robust and sophisticated ACID, Indexing, and Transaction supports has found widespread popularity among the developer community.

PostgreSQL Download. PostgreSQL Tutorial. SQL Guide. MongoDB Interview Questions. An SQL query to delete a table from the database and memory while keeping the structure of the table intact? What is a pre-requisite for creating a database in PostgreSQL? Instantly practice and assess yourself with questions from top companies chosen by experts.

Before you go! Download PDF. Enter the name of your college. Computer Science. Information Technology. Mathematics and Computing. Before After Enter company name. Forgot Password. SQL Interview Questions 1. What is Database? What is DBMS? How is it different from DBMS? What is SQL? What are Tables and Fields?

What are Constraints in SQL? What is a Primary Key? What is a Foreign Key? What is a Join? List its different types. What is a Self-Join? What is a Cross-Join? What is an Index? Explain its different types. What is the difference between Clustered and Non-clustered index? What is Data Integrity? What is a Query? What is a Subquery? What are its types? What is Cursor?

How to use a Cursor? What are Entities and Relationships? List the different types of relationships in SQL. What is an Alias in SQL?

What is a View? What is Normalization? What is Denormalization? What are the various forms of Normalization? What are Aggregate and Scalar functions? What is User-defined function? What are its various types? What is OLTP? What is Collation? What are the different types of Collation Sensitivity?

What is a Stored Procedure? What is a Recursive Stored Procedure? How to create empty tables with the same structure as another table? What is PostgreSQL? How will you change the datatype of a column? What is the command used for creating a database in PostgreSQL? How can we start, restart and stop the PostgreSQL server?

What are partitioned tables called in PostgreSQL? Define tokens in PostgreSQL? What is the capacity of a table in PostgreSQL? Define sequence. What are string constants in PostgreSQL? How can you get a list of all databases in PostgreSQL? How can you delete a database in PostgreSQL? What are ACID properties? Can you explain the architecture of PostgreSQL? What do you understand by multi-version concurrency control?

What do you understand by command enable-debug? How do you check the rows affected as part of previous transactions? How do you perform case-insensitive searches using regular expressions in PostgreSQL? How will you take backup of the database in PostgreSQL? Does PostgreSQL support full text search? What are parallel queries in PostgreSQL? Differentiate between commit and checkpoint. Multiple Choice Questions. Click here to download.

What type of integrity constraint does the foreign key ensure? Do not use alias. Write a SQL query to update the field "status" in table "applications" from 0 to 1. Write a SQL query to fetch "names" that are present in either table "accounts" or in table "registry". Write a SQL query to fetch "names" that are present in "accounts" but not in table "registry". Write a SQL query to fetch "names" from table "contacts" that are neither present in "accounts.

Write a SQL statement to wipe a table 'Temporary' from memory. Write a SQL query to remove first records from table 'Temporary' based on 'id'. Write a SQL statement to delete the table 'Temporary' while keeping its relations intact. 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. Atomicity : This property ensures that the transaction is completed in all-or-nothing way. Consistency : This ensures that updates made to the database is valid and follows rules and restrictions.

Isolation : This property ensures integrity of transaction that are visible to all other transactions. Durability : This property ensures that the committed transactions are stored permanently in the database.

The architecture of PostgreSQL follows the client-server model. The client application does the task of connecting to this instance and requests data processing to the services. Dirty reads : If a transaction reads data that is written due to concurrent uncommitted transaction, these reads are called dirty reads. Phantom reads : This occurs when two same queries when executed separately return different rows.

For example, if transaction A retrieves some set of rows matching search criteria. Assume another transaction B retrieves new rows in addition to the rows obtained earlier for the same search criteria.

The results are different. Non-repeatable reads : This occurs when a transaction tries to read the same row multiple times and gets different values each time due to concurrency. This happens when another transaction updates that data and our current transaction fetches that updated data, resulting in different values.

They are as follows: Read Uncommitted — The lowest level of the isolations. Here, the transactions are not isolated and can read data that are not committed by other transactions resulting in dirty reads. Read Committed — This level ensures that the data read is committed at any instant of read time. Hence, dirty reads are avoided here. Repeatable Read — The most restrictive level of isolation. This holds read and write locks for all rows it operates on.

Due to this, non-repeatable reads are avoided as other transactions cannot read, write, update or delete the rows. Serializable — The highest of all isolation levels. This guarantees that the execution is serializable where execution of any concurrent operations are guaranteed to be appeared as executing serially.

Conclusion: SQL is a language for the database. Primary key defines a realtionship between two tables. A table in SQL must have a primary key associated with it to uniquely identify its records. A table in SQL is indexed by default based on its primary key. Primary key may or may not be unique but can be comprised of multiple fields.

Foreign key defines a relationship between two tables. Foreign Key is automatically created when two tables are joined. Foreign Key uniquely identifies all the records in the referenced table. Foreign key may or may not be unique but can be comprised of multiple fields. A request for data from a table in the database. A request to input data from the user. A request for data from single or multiple tables in the database.

What does SQL stand for? Structured Question Language. Strong Query Language. Structured Query Language. Strong Question Language. Which statement is used to update data in the database? Query to select all records with "bar" in their name? Sorts the result set in descending order using the DESC keyword.

Can sort based on multiple columns. None of the above. SQL query used to fetch unique values from a field? What is the main advantage of a clustered index over a non-clustered index? It is easier to create and manipulate. It requires extra memory but allows for speedy retrieval of records. It does not require additional memory and allows for speedy retrieval of records.

Normalization which has neither composite values nor partial dependencies? Second Normal Formal. Third Normal Form. Boyce-Codd Normal Form. You need to execute the below query for finding nth highest salary. Note: If you run the above query it will ask for entering the value of topnth, if you enter 2 it will show the result for 2 and if you enter 3 it will give the result for 3 likewise this query is generic.

Execute as same as 2nd query execute. Answer: There are multiple ways to solve this question, below two are the easiest solution for it. Note: This solution is only to find the 2nd lowest salary, if the question gets changed, find the 3rd or 4th lowest salary then this will not work. Note: If you run the above query it will ask for entering the value of lownth, if you entering 2 it will show the result for 2 and if you enter 3 it will give the result for 3 likewise this query is generic.

If expr1 is null then, NVL2 returns expr3. The data type of the return value is always the same as the data type of expr2 unless expr2 is character data. Example: select nvl2 ,, from dual; Output: Select nvl2 null,, from dual; Output: So write the query to get the result only gmail. You may also look at the following articles to learn more —. Submit Next Question. By signing up, you agree to our Terms of Use and Privacy Policy.

Forgot Password? This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy.