General DB Questions

 What are Cursers?

• A curser holds multiple rows returned by SQL statement.
• There are two types of cursor
• Implicit cursor
• They are generated by database itself. These are created by user.
• Explicit cursor
• These are created by user.
• Cursor has following steps
• We first declare a cursor
• Then we open cursor
• Then we fetch values from the cursor
• Then we close the cursor
• Declare
• CID Customer.id : id%type
• Cname Customer.name : id%type
• Cursor C1 is select statement 
• Open C1
• Beginning 
• Loop Open C1
• Fetch C1 into cid,cname
• Exit when c1% not found
• DBMS_Output.put_line(cid||” “||cname)
• End Loop
• Close C1
• End

What is the difference between primary key and foreign key?

• Prime key uniquely identifies the records.Foreign is a field in the table that is primary key in another table.
• Primary key cannot accept null values, foreign key can accept multiple null values.
• Primary  key is a clustered Index and data in the table is physically organised Foreign key do not automatically create an index Clustered or non-clustered.We can manually create an index on foreign key.
• We can have only one primary key In a table.We can have more than one foreign key in a table.
• Primary key is always unique.Foreign key can be duplicated.

What is the difference between stored procedure and function?

• Stored procedure may or may not return the value.But function must return a value.
• Stored procedures can have input parameters, functions only have output parameters.
• We can call a function inside a store procedure, but we cannot call a store procedure from a function.
• We cannot use Store procedure in SQL statements like selection, insert, update, delete, merge, et cetera, but we can use them with function.
• We can use, try catch exception handling in stored procedure, but we cannot do in function.
• We can use transactions in store procedure, but not possible in functions

What is CTE?

• CTE or common table expression Is a temporary result set, which you can reference within another SQL statement, including select, insert, update or delete.
• There are two types of CTE
• Normal CTE
• Recursive CTE
• Advantages
• Improve readability of complex queries.
• You can have reclusive CTE’s as well
• Disadvantages
• Not good for huge dataset
• Need to consume CTE Immediately after its declaration.
• Recursive CTE Can be used to find hierarchy of any data.
• Syntax
• With CTE_name as {
• query
• }
• Consume it as a joint table.

What is the use of indexing in database?

• Indexing reduces input output cost while we form blocks
• Index tells about location of a tupple in a block
• Index is used to optimise the performance of DB by minimising the number of access. Required when a query is possessed.
• Indexing provides reference to data
• Index, where all primary keys are written is called as dense index
• Index, where some primary keys are written is known as sparse index.

What are the various steps that you can follow to optimise a query?

• Select only the columns you need.
• Use limit to preview query results.
• Use wild cards only at the end of the phrases
• Example, where state like “A%”
• Avoid distinct if possible
• Takes a lot of possessing power
• Use Group By
• Run large queries during off-peak hours.

What are the various SQL versus No SQL databases?

• No SQL are document, key-value, graph or white column, data stores.Sql databases are table based.
• SQL databases are vertical is scalable no SQL databases are Horizontally scalable.
• SQL databases are relational, which means they allow easy querying on data among multiple tables. Table relationship is important for organising data. No sequel databases are better choice for unstructured data. They allow shading of data across different data stores which allows distributed databases. This makes horizontal scaling much easier.
• Data is well, structured and room for potential error is reduced. SQL schemas require Data model and format of data known before storing anything.
• SQL databases are acid compliance
• SQL transactions are executed atomically.
• SQL databases, take more time to set up Then no SQL databases. No sequel databases are more flexible and simple to set up as data is stored in documents.
• No sequel are designed for distributed, use cases and right heavy systems and can be supported by having multiple write shards for same data partition.
• They are not effective for querying unstructured data Where the format is unknown.
• No sequel Face a delay when an update has to be propagated to other applicants.
• SQL databases are difficult to scale horizontally. We can have read replica, but for write, we have to scale up database, which is costly.
• If a data has complex relationships, we use SQL based database
• SQL base database is designed to create relationships between chunks of data.