SQL – MISCELLEANOUS

Question 1. What Is A Schema?

Answer: The set of objects owned by user account is called the schema.

 

Question 2. What Is A Cluster Key?

Answer: The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

 

Question 3. How to remove duplicate rows from table?

Answer: First Step: Selecting Duplicate rows from table

Tip: Use concept of max (rowid) of table.

Select rollno FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

Step 2:  Delete duplicate rows

Delete FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

 

Question 4. How to find count of duplicate rows?

Answer: Select rollno, count (rollno) from Student

Group by rollno

Having count (rollno)>1

Order by count (rollno) desc;

 

Question 5. Explain What Is Rdbms?

Answer: RDBMS stands for Relational Database Management System. RDBMS is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed using Structured Query Language (SQL)

 

Question 6. What Are the Popular Database Management Systems in The It Industry?

Answer: Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.,

 

Question 7. What Is the Use of Nvl Function?

Answer: NVL function is used to convert the null value to its actual value.

 

 Question 8. What Is the Difference Between Rename and Alias?

Answer: ‘Rename’ is a permanent name given to a table or column

‘Alias’ is a temporary name given to a table or column.

 

Question 9. What Is A Trigger?

Answer: A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee Payroll, Employee Time Sheet etc.,

 

Question 10. List Out the Acid Properties and Explain?

Answer: Following are the four properties of ACID. These guarantees that the database transactions are processed reliably.

  • Atomicity

  • Consistency

  • Isolation

  • Durability

 

Question 11. What Is the Difference Between Delete, Truncate and Drop Command?

Answer: The difference between the Delete, Truncate and Drop command is:

Delete command is a DML command, it is used to delete rows from a table. It can be rolled back.

Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It can’t be rolled back.

Drop is a DDL command, it removes the complete data along with the table structure (unlike truncate command that removes only the rows). All the tables’ rows, indexes, and privileges will also be removed.

 

Question 12. What Is the Difference Between Having and Where Clause?

Answer: Where clause is used to fetch data from a database that specifies criteria whereas a Having clause is used along with ‘GROUP BY’ to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.

 

Question 13. How Many Types of Privileges Are Available in Sql?

Answer: There are two types of privileges used in SQL, such as

System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.

Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.

 

 Question 14. What Is Sql Injection?

Answer: SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have access to sensitive data and perform administrative tasks on databases.

 

Question 15. What Is the Difference Between Clustered and Non-clustered Indexes?

Answer: One table can have only one clustered index but multiple nonclustered indexes.

Clustered indexes can be read rapidly rather than non-clustered indexes.

Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in table as it has separate structure from data row