TOP [ 25+ ] SQL Interview Questions & Answers | Learn NOW

TOP [ 25+ ] SQL Interview Questions & Answers | Learn NOW

Last updated on 03rd Jul 2020, Blog, Interview Questions

About author

Aravind (Sr Technical Director )

Highly Expertise in Respective Industry Domain with 7+ Years of Experience Also, He is a Technical Blog Writer for Past 4 Years to Renders A Kind Of Informative Knowledge for JOB Seeker

(5.0) | 15212 Ratings 2146

Structured Query Language is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.

SQL offers two main advantages over older read–write APIs such as ISAM or VSAM. Firstly, it introduced the concept of accessing many records with one single command. Secondly, it eliminates the need to specify how to reach a record, 

The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control. Although SQL is essentially a declarative language (4GL), it also includes procedural elements.

SQL was one of the first commercial languages to utilize Edgar F. Codd’s relational model. The model was described in his influential 1970 paper, “A Relational Model of Data for Large Shared Data Banks”.  Despite not entirely adhering to the relational model as described Codd, it became the most widely used database language

1. What is DBMS?

Ans: 

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

2. What is RDBMS?

Ans: 

RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.

3. What is SQL?

Ans: 

SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

Standard SQL Commands are Select.

4. What is a Database?

Ans: 

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.

Example: School Management Database, Bank Management Database.

5. What are tables and Fields?

Ans: 

A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.

Example:.

Table: Employee.

Field: Emp ID, Emp Name, Date of Birth.

Data: 201456, David, 11/15/1960.

6. What is a primary key?

Ans: 

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

7. What is a unique key?

Ans: 

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.

8. What is a foreign key?

Ans: 

A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

9. What is a join?

Ans: 

This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

10. What are the types of join and explain each?

Ans: 

There are various types of join which can be used to retrieve data and it depends on the relationship between tables.

  • Inner Join: Inner join return rows when there is at least one match of rows between the tables.
  • Right Join: Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.
  • Left Join: Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
  • Full Join: Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

11. What is normalization?

Ans: 

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.

12. What is Denormalization.

Ans: 

DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

13. What are all the different normalizations?

Ans: 

The normal forms can be divided into 5 forms, and they are explained below -.

  • First Normal Form (1NF): This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
  • Second Normal Form (2NF): Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
  • Third Normal Form (3NF): This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.
  • Fourth Normal Form (4NF): Meeting all the requirements of third normal form and it should not have multi- valued dependencies.

14. What is a View?

Ans: 

A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.

15. What is an Index?

Ans: 

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

16. What are all the different types of indexes?

Ans: 

There are three types of indexes:

  • Unique Index: This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.
  • Clustered Index: This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.
  • NonClustered Index: NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

17. What is a Cursor?

Ans: 

A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

18. What is a relationship and what are they?

Ans: 

Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:.

  • One to One Relationship.
  • One to Many Relationship.
  • Many to One Relationship.
  • Self-Referencing Relationship.

19. What is a query?

Ans: 

A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database.

20. What is subquery?

Ans: 

A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query

    Subscribe For Free Demo

    21. What is the difference between SQL and MySQL?

    Ans: 

    SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

    sql-mysql

    22. What are Constraints in SQL?

    Ans: 

    Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during creation of table or after creationg using the ALTER TABLE command. The constraints are:

    • NOT NULL – Restricts NULL value from being inserted into a column.
    • CHECK – Verifies that all values in a field satisfy a condition.
    • DEFAULT – Automatically assigns a default value if no value has been specified for the field.
    • UNIQUE – Ensures unique values to be inserted into the field.
    • INDEX – Indexes a field providing faster retrieval of records.
    • PRIMARY KEY – Uniquely identifies each record in a table.
    • FOREIGN KEY – Ensures referential integrity for a record in another table.

    23. What is a Self-Join?

    Ans: 

    A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query.

    • SELECT A.emp_id AS “Emp_ID”,A.emp_name AS “Employee”,
    • B.emp_id AS “Sup_ID”,B.emp_name AS “Supervisor”
    • FROM employee A, employee B
    • WHERE A.emp_sup = B.emp_id;

    24. What is a Cross-Join?

    Ans: 

    Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN.

    • SELECT stu.name, sub.subject 
    • FROM students AS stu
    • CROSS JOIN subjects AS sub;

    cross-join

    Q   =>   Write a SQL statement to CROSS JOIN ‘table_1’ with ‘table_2’ and fetch ‘col_1’ from table_1 & ‘col_2’ from table_2 respectively. Do not use alias.

    Q   =>   Write a SQL statement to perform SELF JOIN for ‘Table_X’ with alias ‘Table_1’ and ‘Table_2’, on columns ‘Col_1’ and ‘Col_2’ respectively.

    25. Explain different types of Index.

    Ans: 

    A database index is a data structure that provides quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.

    • CREATE INDEX index_name /* Create Index */
    • ON table_name (column_1, column_2);
    • DROP INDEX index_name; /* Drop Index */

    There are different types of indexes that can be created for different purposes:

    Unique and Non-Unique Index:
    Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index.
    CREATE UNIQUE INDEX myIndex

    ON students (enroll_no);

    Non-unique indexes, on the other hand, are not used to enforce constraints on the tables with which they are associated. Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently.

    Clustered and Non-Clustered Index:

    Clustered indexes are indexes whose order of the rows in the database correspond to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas, multiple non-clustered indexes can exist in the table.
    The only difference between clustered and non-clustered indexes is that the database manager attempts to keep the data in the database in the same order as the corresponding keys appear in the clustered index.
    Clustering index can improve the performance of most query operations because they provide a linear-access path to data stored in the database.

    Q   =>   Write a SQL statement to create a UNIQUE INDEX “my_index” on “my_table” for fields “column_1” & “column_2”.

    26. What is the difference between Clustered and Non-clustered index?

    Ans: 

    As explained above, the differences can be broken down into three small factors –

    • Clustered index modifies the way records are stored in a database based on the indexed column. Non-clustered index creates a separate entity within the table which references the original table.
    • Clustered index is used for easy and speedy retrieval of data from the database, whereas, fetching records from the non-clustered index is relatively slower.
    • In SQL, a table can have a single clustered index whereas it can have multiple non-clustered indexes.

    27. What is Data Integrity?

    Ans: 

    Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

    28. What are the types of Query?

    Ans: 

    A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.

    • SELECT fname, lname /* select query */
    • FROM myDb.students
    • WHERE student_id = 1;
    • UPDATE myDB.students /* action query */
    • SET fname = ‘Captain’, lname = ‘America’
    • WHERE student_id = 1;

    29. What is a Subquery? What are its types?

    Ans: 

    A subquery is a query within another query, also known as nested query or inner query. It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively. For example, here we fetch the contact information for students who have enrolled for the maths subject:

    • SELECT name, email, mob, address
    • FROM myDb.contacts
    • WHERE roll_no IN (
    • SELECT roll_no
    • FROM myDb.students
    • WHERE subject = ‘Maths’);

    There are two types of subquery – Correlated and Non-Correlated.

    • A correlated subquery cannot be considered as an independent query, but it can refer the column in a table listed in the FROM of the main query.
    • A non-correlated subquery can be considered as an independent query and the output of subquery is substituted in the main query.

    Q   =>   Write a SQL query to update the field “status” in table “applications” from 0 to 1.

    Q   =>   Write a SQL query to select the field “app_id” in table “applications” less than 1000.

    Q   =>   Write a SQL query to fetch the field “app_name” from “apps” where “apps.id” is equal to the above collection of “app_id”.

    30. What is the SELECT statement?

    Ans: 

    SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set.

    • SELECT * FROM myDB.students;

    Course Curriculum

    Get Advanced Practical Oriented SQL Training & Certification Course

    Weekday / Weekend BatchesSee Batch Details

    31. What are some common clauses used with SELECT query in SQL?

    Ans: 

    Some common SQL clauses used in conjuction with a SELECT query are as follows:

    • WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.

    ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).

    • SELECT *
    • FROM myDB.students
    • WHERE graduation_year = 2019
    • ORDER BY studentID DESC;

    • GROUP BY clause in SQL is used to group records with identical data and can be used in conjuction with some aggregation functions to produce summarized results from the database.

    HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since WHERE clause cannot filter aggregated records.

    • SELECT COUNT(studentId), country
    • FROM myDB.students
    • WHERE country != “INDIA”
    • GROUP BY country
    • HAVING COUNT(studentID) > 5;

    32. What is Cursor? How to use a Cursor?

    Ans: 

    A database cursor is a control structure that allows for traversal of records in a database. Cursors, in addition, facilitates processing after traversal, such as retrieval, addition and deletion of database records. They can be viewed as a pointer to one row in a set of rows

    33. What are Entities and Relationships?

    Ans: 

    • Entity: An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated properties that provide it an identity.
    • Relationships: Relations or links between entities that have something to do with each other. For example – The employees table in a company’s database can be associated with the salary table in the same database.

    34. What is a default constraint?

    Ans: 

    Constraints are used to specify some sort of rules for processing data and limiting the type of data that can go into a table. Now, let’s understand the default constraint.

    Default constraint: It is used to define a default value for a column so that the default value will be added to all the new records if no other value is specified. For example, if we assign a default constraint for the E_salary column in the below table and set the default value as 85000, then all the entries of this column will have a default value of 85000 unless no other value has been assigned during the insertion.

    sql-example-03

    Now, let’s see how to set a default constraint. We will start off by creating a new table and adding a default constraint to one of its columns.

    Code:

    • create table stu1(s_id int, s_name varchar(20), s_marks int default 50)
    • select *stu1

    Output:

    sql-output

    Now, we will insert the records.

    Code:

    • insert into stu1(s_id,s_name) values(1,’Sam’)
    • insert into stu1(s_id,s_name) values(2,’Bob’)
    • insert into stu1(s_id,s_name) values(3,’Matt’)
    • select *from stu1

    Output:

    sql-output-01

    35. What is a unique constraint?

    Ans: 

    Unique constraints ensure that all the values in a column are different. For example, if we assign a unique constraint to the e_name column in the below table, then every entry in this column should have a unique value.

    sql-example-02

    First, we will create a table.

    • create table stu2(s_id int unique, s_name varchar(20))

    Now, we will insert the records.

    • insert into stu2 values(1,’Julia’)
    • insert into stu2 values(2,’Matt’)
    • insert into stu2 values(3,’Anne’)

    Output:

    sql-output-02

    36. How would you find the second highest salary from the below table?

    sql-example-01

    Ans: 

    Code:

    • select * from employee
    • select max(salary) from employee where salary not in (select max(salary) from employee)

    Output:

    1. 25000

    37. State the differences between Clustered and Non-clustered indexes.

    Ans: 

    • Clustered index: It is used to sort the rows of data by their key values. A clustered index is like the contents of a phone book. We can open the book at ‘David’ (for ‘David, Thompson’) and find information for all Davids right next to each other. Since the data is located next to each other, it helps a lot in fetching data based on range-based queries. Also, the clustered index is actually related to how the data is stored. There is only one clustered index possible per table.
    • Non-clustered index: It stores data at one location and indexes at some other location. The index has pointers that point to the location of the data. As the index in the non-clustered index is stored in different places, there can be many non-clustered indexes for a table.
    sql-cluster-non-cluster

    Now, we will see the major differences between clustered and non-clustered indexes:

    Parameters Clustered Index Non-clustered Index
    Used for Sorting and storing records physically in memory Creating a logical order for data rows. Pointers are used for           physical data files
    Methods for storing Stores data in the leaf nodes of the index Never stores data in the leaf nodes of the index
    Size Quite large Comparatively, small
    Data accessing Fast Slow
    Additional disk space Not required Required to store indexes separately
    Type of key By default, the primary key of a table is a clustered index It can be used with the unique constraint on the table that acts as a composite key
    Main feature Improves the performance of data retrieval Should be created on columns used in Joins 

    38. How do we use the DISTINCT statement? What is its use?

    Ans: 

    The DISTINCT statement is used with the SELECT statement. If the record contains duplicate values then the DISTINCT statement is used to select different values among duplicate records.

    Syntax:

    • SELECT DISTINCT column_name(s) FROM table_name;

    39.  What are different Clauses used in SQL?

    Ans: 

    sql-clauses

    WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill the given condition.

    Syntax:

    • SELECT column_name(s) FROM table_name WHERE condition;

    GROUP BY Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.

    Syntax:

    • SELECT column_name(s) FROM table_name GROUP BY column_name;

    HAVING clause: This clause is used in association with the GROUP BY clause. It is applied to each group of results or the entire result as a single group. It is much similar as WHERE clause but the only difference is you cannot use it without GROUP BY clause

    Syntax:

    • SELECT column_name(s) FROM table_name GROUP BY column_name HAVING condition;

    ORDER BY clause: This clause is used to define the order of the query output either in ascending (ASC) or in descending (DESC). Ascending (ASC) is set as the default one but descending (DESC) is set explicitly.

    Syntax:

    • SELECT column_name(s) FROM table_name WHERE condition ORDER BY column_name ASC|DESC;

    USING clause: USING clause comes in use while working with SQL JOIN. It is used to check equality based on columns when tables are joined. It can be used instead of the ON clause in JOIN.

    Syntax:

    • SELECT column_name(s) FROM table_name JOIN table_name USING (column_name);

    40. Why do we use SQL constraints? Which constraints we can use while creating a database in SQL?

    Ans: 

    • Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.
    • Constraints are defined while creating the database itself with the CREATE TABLE statement or even after the table is created once with the ALTER TABLE statement.

    There are 5 major constraints are used in SQL, such as

    • NOT NULL: That indicates that the column must have some value and cannot be left NULL.
    • UNIQUE: This constraint is used to ensure that each row and column has a unique value and no value is being repeated in any other row or column.
    • PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one column to identify the particular record with a unique identity.
    • FOREIGN KEY: It is used to ensure the referential integrity of data in the table. It matches the value in one table with another using the PRIMARY KEY.
    • CHECK: It ensures whether the value in columns fulfills the specified condition.

    41. What are different JOINS used in SQL?

    Ans: 

    sql-joins

    4 major types of Joins are used while working on multiple tables in SQL databases:

    INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one matching column.

    Syntax:

    • SELECT column_name(s) FROM table_name1  INNER JOIN table_name2 ON column_name1=column_name2;

    For Example,

    In this example, we have a table Employee with the following data:

    sql-table-01

    The second table’s name is Joining.

    sql-table-02

    Enter the following SQL statement:

    SELECT Employee.Emp_id, Joining.Joining_Date  FROM Employee  INNER JOIN Joining  ON Employee.Emp_id = Joining.Emp_id  ORDER BY Employee.Emp_id;

    There will be 4 records selected. Results are:

    sql-table-03

    Employee and Orders tables have a matching customer_id value.

    LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from the LEFT table and its matched rows from a RIGHT table.

    Syntax:

    • SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON column_name1=column_name2;

    For Example,

    In this example, we have a table Employee with the following data:

    sql-table-04

    The second table’s name is Joining.

    sql-table-05

    Enter the following SQL statement:

    SELECT Employee.Emp_id, Joining.Joining_DateFROM EmployeeLEFT OUTER JOIN JoiningON Employee.Emp_id = Joining.Emp_idORDER BY Employee.Emp_id;

    There will be 4 records selected. You will see the following results:

    sql-table-06

    RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from the LEFT table.

    Syntax:

    • SELECT column_name(s)FROM table_name1RIGHT JOIN table_name2ON column_name1=column_name2;

    For Example,

    In this example, we have a table Employee with the following data:

    sql-table-07

    The second table’s name is Joining.

    sql-table-08

    Enter the following SQL statement:

    SELECT Employee.Emp_id, Joining.Joining_Date FROM EmployeeRIGHT JOIN JoiningON Employee.Emp_id = Joining.Emp_idORDER BY Employee.Emp_id;

    Output:

    Emp_idJoining_Date
    E00122016/04/18
    E00132016/04/19
    E00142016/05/01

    FULL JOIN (FULL OUTER JOIN): This joins returns all results when there is a match either in the RIGHT table or in the LEFT table.

    Syntax:

    • SELECT column_name(s) FROM table_name1 FULL OUTER JOIN table_name2 ON column_name1=column_name2;

    For Example,

    In this example, we have a table Employee with the following data:

    sql-table-09

    The second table’s name is Joining.

    sql-table-10

    42. What are transactions and their controls?

    Ans: 

    A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations like Creating, updating, deleting records performed in the database come from transactions.

    In simple words, we can say that a transaction means a group of SQL queries executed on database records.

    There are 4 transaction controls such as

    • COMMIT: It is used to save all changes made through the transaction.
    • ROLLBACK: It is used to roll back the transaction. All changes made by the transaction are reverted back and the database remains as before.
    • SET TRANSACTION: Set the name of the transaction.
    • SAVEPOINT: It is used to set the point where the transaction is to be rolled back.

    43. What are the properties of the transaction?

    Ans: 

    Properties of the transaction are known as ACID properties. These are:

    • Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully or not. If not, then the transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes are undone.
    • Consistency: Ensures that all changes made through successful transactions are reflected properly on the database.
    • Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on others.
    • Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure.

    44. How many Aggregate functions are available in SQL?

    Ans: 

    SQL Aggregate functions determine and calculate values from multiple columns in a table and return a single value.

    There are 7 aggregate functions in SQL:

    • AVG(): Returns the average value from specified columns.
    • COUNT(): Returns number of table rows.
    • MAX(): Returns the largest value among the records.
    • MIN(): Returns smallest value among the records.
    • SUM(): Returns the sum of specified column values.
    • FIRST(): Returns the first value.
    • LAST(): Returns last value.

    45.  What are Scalar functions in SQL?

    Ans: 

    Scalar functions are used to return a single value based on the input values.

    Scalar Functions are as follows:

    • UCASE(): Converts the specified field in the upper case.
    • LCASE(): Converts the specified field in lower case.
    • MID(): Extracts and returns character from the text field.
    • FORMAT(): Specifies the display format.
    • LEN(): Specifies the length of the text field.
    • ROUND(): Rounds up the decimal field value to a number.

    46. What are triggers?

    Ans: 

    Triggers in SQL is kind of stored procedures used to create a response to a specific action performed on the table such as INSERT, UPDATE or DELETE. You can invoke triggers explicitly on the table in the database.

    Action and Event are two main components of SQL triggers. When certain actions are performed, the event occurs in response to that action.

    Syntax:

    • CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]}ON table_name [FOR [EACH] {ROW|STATEMENT}]EXECUTE PROCEDURE functionname {arguments}

    47. What is View in SQL?

    Ans: 

    A View can be defined as a virtual table that contains rows and columns with fields from one or more tables.

    Syntax:

    • CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

    48. How we can update the view?

    Ans: 

    SQL CREATE and REPLACE can be used for updating the view.

    Execute the below query to update the created view.

    Syntax:

    • CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

    49. Explain the working of SQL Privileges?

    Ans: 

    SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments. The administrator of the database can grant or revoke privileges to or from users of database objects by using commands like SELECT, INSERT, UPDATE, DELETE, ALL, etc.

    GRANT Command: This command is used to provide database access to users other than the administrator.

    Syntax:

    • GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION];

    In the above syntax, the GRANT option indicates that the user can grant access to another user too.

    REVOKE command: This command is used to provide database deny or remove access to database objects.

    Syntax:

    • REVOKE privilege_name ON object_name FROM {user_name|PUBLIC|role_name};

    50. How many types of Privileges are available in SQL?

    Ans: 

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

    • System privilege: System privilege deals with the object of a particular type and provides users the right to perform one or more actions on it. These actions include performing 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.
    Course Curriculum

    Get SQL Training for Beginners Covers Advanced Concepts from Industry Experts

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    51. What is SQL Injection?

    Ans: 

    SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database in a way that once it is executed, the database is exposed to an attacker for the attack. This technique is usually used for attacking data-driven applications to have access to sensitive data and perform administrative tasks on databases.

    For Example,

    • SELECT column_name(s) FROM table_name WHERE condition;

    52.  What is SQL Sandbox in SQL Server?

    Ans: 

    SQL Sandbox is a safe place in the SQL server environment where untrusted scripts are executed. There are 3 types of SQL sandbox:

    • Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory as well as cannot create files.
    • External Access Sandbox: Users can access files without having the right to manipulate the memory allocation.
    • Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

    53. What is the difference between SQL and PL/SQL?

    Ans: 

    SQL is a Structured Query Language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.

    54. What are UNION, MINUS and INTERSECT commands?

    Ans: 

    The UNION operator combines and returns the result-set retrieved by two or more SELECT statements.
    The MINUS operator in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.
    The INTERSECT clause in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.
    Certain conditions need to be met before executing either of the above statements in SQL –

    Each SELECT statement within the clause must have the same number of columns

    The columns must also have similar data types

    The columns in each SELECT statement should necessarily have the same order

    SELECT name FROM Students /* Fetch the union of queries */

    UNION

    SELECT name FROM Contacts;

    SELECT name FROM Students /* Fetch the union of queries with duplicates*/

    UNION ALL

    SELECT name FROM Contacts;

    SELECT name FROM Students /* Fetch names from students */

    MINUS /* that aren’t present in contacts */

    SELECT name FROM Contacts;

    SELECT name FROM Students /* Fetch names from students */

    INTERSECT /* that are present in contacts as well */

    SELECT name FROM Contacts;

    • Q   =>   Write a SQL query to fetch “names” that are present in either table “accounts” or in table “registry”.
    • Q   =>   Write a SQL query to fetch “names” that are present in “accounts” but not in table “registry”.
    • Q   =>   Write a SQL query to fetch “names” from table “contacts” that are neither present in “accounts.name” nor in “registry.name“.

    55. What is the use of the NVL function?

    Ans: 

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

    56. What is the Cartesian product of the table?

    Ans: 

    The output of Cross Join is called a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 rows.

    57. What do you mean by Subquery?

    Ans: 

    Query within another query is called as Subquery. A subquery is called inner query which returns output that is to be used by another query.

    58. How many row comparison operators are used while working with a subquery?

    Ans: 

    There are 3-row comparison operators that are used in subqueries such as IN, ANY and ALL.

    59. What is the difference between clustered and non-clustered indexes?

    Ans: 

    The differences between the two are as follows:

    • One table can have only one clustered index but multiple non-clustered indexes.
    • Clustered indexes can be read rapidly rather than non-clustered indexes.
    • Clustered indexes store data physically in the table or view whereas, non-clustered indexes do not store data in the table as it has separate structure from the data row.

    60. What is the difference between DELETE and TRUNCATE?

    Ans: 

    The differences are:

    • The basic difference in both is DELETE command is DML command and the TRUNCATE command is DDL.
    • DELETE command is used to delete a specific row from the table whereas the TRUNCATE command is used to remove all rows from the table.
    • We can use the DELETE command with WHERE clause but cannot use the TRUNCATE command with it.

    61. What is the difference between DROP and TRUNCATE?

    Ans: 

    TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it also cannot be retrieved back.

    62. How to write a query to show the details of a student from Students table whose name start with K?

    Ans: 

    Query:

    • SELECT * FROM Student WHERE Student_Name like ‘K%’;

    Here ‘like’ operator is used to perform pattern matching.

    63. What is the difference between Nested Subquery and Correlated Subquery?

    Ans: 

    Subquery within another subquery is called Nested Subquery.  If the output of a subquery depends on column values of the parent query table then the query is called Correlated Subquery.

    • SELECT adminid(SELEC Firstname+’ ‘+Lastname  FROM Employee WHERE empid=emp. adminid)AS EmpAdminId FROM Employee;

    The result of the query is the details of an employee from the Employee table.

    64. How many Normalization forms are there?

    Ans: 

    Normalization is used to organize the data in such a manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies.

    There are 5 forms of Normalization:

    • First Normal Form (1NF): It removes all duplicate columns from the table. It creates a table for related data and identifies unique column values.
    • First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines the relationship between tables using the primary key.
    • Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through the primary key.
    • Fourth Normal Form (4NF): Follows 3NF and does not define multi-valued dependencies. 4NF is also known as BCNF.

    65. What is a Relationship? How many types of Relationships are there?

    Ans: 

    The relationship can be defined as the connection between more than one table in the database.

    There are 4 types of relationships:

    • One to One Relationship
    • Many to One Relationship
    • Many to Many Relationship
    • One to Many Relationship

    66. What do you mean by Stored Procedures? How do we use it?

    Ans: 

    A stored procedure is a collection of SQL statements that can be used as a function to access the database. We can create these stored procedures earlier before using it and can execute them wherever required by applying some conditional logic to it. Stored procedures are also used to reduce network traffic and improve performance.

    Syntax:

    • CREATE Procedure Procedure_Name ( //Parameters ) AS BEGIN SQL statements in stored procedures to update/retrieve records END

    67. State some properties of Relational databases?

    Ans: 

    Properties are as follows:

    • In relational databases, each column should have a unique name.
    • The sequence of rows and columns in relational databases is insignificant.
    • All values are atomic and each row is unique.

    68. What are Nested Triggers?

    Ans: 

    Triggers may implement data modification logic by using INSERT, UPDATE, and DELETE statements. These triggers that contain data modification logic and find other triggers for data modification are called Nested Triggers.

    69. What are the steps of Cursor?

    Ans: 

    A cursor is a database object which is used to manipulate data in a row-to-row manner.

    Cursor follows steps as given below:

    • Declare Cursor
    • Open Cursor
    • Retrieve row from the Cursor
    • Process the row
    • Close Cursor
    • Deallocate Cursor

    70. What is Collation?

    Ans: 

    Collation is a set of rules that check how the data is sorted by comparing it. Such as character data is stored using correct character sequence along with case sensitivity, type, and accent.

    SQL Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    71. What do we need to check in Database Testing?

    Ans: 

    In Database testing, the following thing is required to be tested:

    • Database connectivity
    • Constraint check
    • Required application field and its size
    • Data Retrieval and processing with DML operations
    • Stored Procedures
    • Functional flow

    72. What is Database White Box Testing?

    Ans: 

    Database White Box testing involves:

    • Database Consistency and ACID properties
    • Database triggers and logical views
    • Decision Coverage, Condition Coverage, and Statement Coverage
    • Database Tables, Data Model, and Database Schema
    • Referential integrity rules

    73. What is Database Black Box Testing?

    Ans: 

    Database Black Box testing involves:

    • Data Mapping
    • Data stored and retrieved
    • Use of Black Box testing techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA)

    74. What are the advantages of Views?

    Ans: 

    Advantages of Views are:

    • Views restrict access to the data because the view can display selective columns from the table.
    • Views can be used to make simple queries to retrieve the results of complicated queries. For Example, views can be used to query information from multiple tables without the user knowing.

    75. List the various privileges that a user can grant to another user?

    Ans: 

    SELECT, CONNECT, RESOURCES.

    76. What is schema?

    Ans: 

    A schema is a collection of database objects of a User.

    77. What is a Table?

    Ans: 

    A table is the basic unit of data storage in the database management system. Table data is stored in rows and columns.

    78. Does View contain Data?

    Ans: 

    No, Views are virtual structures.

    79. Can a View based on another View?

    Ans: 

    Yes, A View is based on another View.

    80. What is the difference between the HAVING clause and WHERE clause?

    Ans: 

    Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.

    If GROUP BY clause is not used then Having behaved like WHERE clause only.

    81. What is the difference between Local and Global temporary tables?

    Ans: 

    If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the DB but its rows disappear when the connection is closed.

    82. What is CTE?

    Ans: 

    A CTE or common table expression is an expression that contains temporary result set which is defined in a SQL statement.

    83. What are the differences between SQL and PL/SQL?

    Ans: 

    Some common differences between SQL and PL/SQL are as shown below:

    SQLPL/SQL
    SQL is a query execution or commanding languagePL/SQL is a complete programming language
    SQL is data oriented languagePL/SQL is a procedural language
    SQL is very declarative in naturePL/SQL has a procedural nature
    It is used for manipulating dataIt is used for creating applications
    We can execute one statement at a time in SQLWe can execute block of statements in PL/SQL
    SQL tells database, what to do?PL/SQL tells database how to do
    We can embed SQL in PL/SQLWe can not embed PL/SQL in SQL

    84. What is the difference between BETWEEN and IN operators in SQL?

    Ans: 

    The BETWEEN operator is used to fetch rows based on a range of values.

    For example,

    • SELECT * FROM Students 
    • WHERE ROLL_NO BETWEEN 20 AND 30;

    This query will select all those rows from the table Students where the value of the field ROLL_NO lies between 20 and 30.

    IN
    The IN operator is used to check for values contained in specific sets.

    For example,

    • SELECT * FROM Students 
    • WHERE ROLL_NO IN (20,21,23);

    This query will select all those rows from the table Students where the value of the field ROLL_NO is either 20 or 21 or 23.

    Write an SQL query to find names of employee start with ‘A’?
    The LIKE operator of SQL is used for this purpose. It is used to fetch filtered data by searching for a particular pattern in where clause.

    The Syntax for using LIKE is,

    • SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern; 

    LIKE: operator name

    pattern: exact value extracted from the pattern to get related data in result set.

    The required query is:

    • SELECT * FROM Employees WHERE EmpName like ‘A%’ ;

    • You may refer to this article on WHERE clause for more details on LIKE operator.

    85. What is the difference between CHAR and VARCHAR2 datatype in SQL?

    Ans: 

    Both of these datatypes are used for characters but varchar2 is used for character strings of variable length whereas char is used for character strings of fixed length. For example, if we specify the type as char(5) then we will not be allowed to store string of any other length in this variable but if we specify the type of this variable as varchar2(5) then we will be allowed to store strings of variable length, we can store a string of length 3 or 4 or 2 in this variable.

    86. Name different types of case manipulation functions available in SQL?

    Ans: 

    There are three types of case manipulation functions available in SQL. They are,

    LOWER: The purpose of this function is to return the string in lowercase. It takes a string as argument and returns the string by converting it into lower case.

    Syntax:

    • LOWER(‘string’)

    •  UPPER: The purpose of this function is to return the string in uppercase. It takes a string as argument and returns the string by converting it into uppercase.

    Syntax:

    • UPPER(‘string’)

    •  INITCAP: The purpose of this function is to return the string with first letter in uppercase and rest of the letters in lowercase.

    Syntax:

    • INITCAP(‘string’)

    87. What do you mean by data definition language?

    Ans: 

    Data definition language or DDL allows to execute queries like CREATE, DROP and ALTER. That is, those queries which define the data.

    88. What do you mean by data manipulation language?

    Ans: 

    Data manipulation Language or DML is used to access or manipulate data in the database.

    It allows us to perform below listed functions:

    • Insert data or rows in database
    • Delete data from database
    • Retrieve or fetch data
    • Update data in database.

    89. What is the difference between primary key and unique constraints?

    Ans: 

    Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains. The primary key creates the cluster index automatically but the Unique key d

    90. What is a join in SQL? What are the types of joins?

    Ans: 

    An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are:

    • INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.
    • LEFT JOIN:This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN
    • RIGHT JOIN:RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
    • FULL JOIN: FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values

    91. What is difference between varchar and varchar2 datatype?

    Ans: 

    Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes of memory space.Varchar will occupy the space for null values whereas varchar2 can not occupy the space for null values.So varchar2 is good to use not to  face performace related problems.varchar2 is faster than varchar datatype.

    92. How to represent comments in oracle?

    Ans: 

    There are following 2 ways for commenting in oracle:

    • Single Line comment: Two dashes (–) before begining of the line
    • Multiline comment/Block comment:When user wants to comment multiple line /* */ operators are used.

    93. What is raw datatype?

    Ans: 

    Raw datatype is used to store values in binary data format.There are 2 types of RAW datatype.

    • Raw
    • Long Raw.

    Long raw datatype is used to store graphics,sound documents.Raw datatype is variable length datatype like varchar2 but basically it only stores  data in 1 ‘s and 0’s means binary data format.

    94. What is ROWID & ROWNUM?

    Ans: 

    ROWID is nothing but the physical address given to that row which is in hexadecimal format.ROWNUM is nothing but the logical sequence given to the row of that column.

    95. What is Materialized View in SQL?

    Ans: 

    Materialized view is also logical structure of one or more table in which data is stored physically in the view.Data has been stored physically in materialized view so data retrieval is faster as compare to simple view.

    96. What is difference between Truncate ,Drop and DELETE?

    Ans: 

    • Drop:
      • Drop command is DDL command which is used to delete the object from the database.
      • We can not use the “ROLLBACK” after using drop command.
      • Drop command free’s the space of database object.
      • Drop table table_name;
    • Truncate:
      • Truncate command is DDL command which is used to truncate the data from the database table.
      • We can not use the “ROLLBACK” after using Truncate command.
      • It free’s the space of database object but the structure remains same and memory of structure also remains same.
      • Truncate table table_name;
    • Delete:
      • Delete command is DML command which is used to delete the records from table.
      • We can use Rollback to Rollback the records from the table.
      • Delete command not free’s the memory space.
      • Delete table table_name where condition;

    96. Explain About DDL Statements of SQL?

    Ans: 

    DDL – DDL stands for Data Definition Language:

    Statement Description
    CREATECreates a new table, a view of a table, or other object in database
    ALTERModifies an existing database object, such as a table.
    DROPDeletes an entire table, a view of a table or other object in the database.

    97.What is DML in SQL.Explain DML Statements in Details?

    Ans: 

    DML stands for Data Manipulation Language:

    StatementDescription
    INSERTCreates a record
    UPDATEModifies records
    DELETEDeletes records

    98. What is Data Manipulation Language?

    Ans: 

    DML or Data Manipulation Language is a set of commands that are classified pertaining to its capability to give users permission to change entries within the database. This may be through Inserting, Retrieving, Deleting or Updating data within tables. Popular DML statements arise from these core functionalities and are listed below:

    • SELECT – used to highlight a row within a table and retrieve it.
    • SELECT [columnName] FROM [tableName]
    • UPDATE – used to update entries from existing tables.
    • UPDATE [tableName] SET [value]
    •  INSERT – used to insert entries into an existing table.
    • INSERT INTO [tableName]
    • DELETE – used to delete entries from an existing table
    • DELETE FROM [tableName]

    99. What is Transaction Control Language (TCL)?

    Ans: 

    TCL is a category of SQL commands which primarily deals with the database transaction and save points. These keywords implement the SQL functions and logic defined by the developer into the database structure and behavior. Examples of these TCL commands are:
    COMMIT – used to commit a transaction
    ROLLBACK – in any advent of errors, transaction rollback is invoked by this keyword.
    SAVEPOINT – keyword representing the reverting point of rollback
    SET TRANSACTION – sets the specifics of the transaction

    100. What is Data Control Language (DCL)?

    Ans: 

    Data Control Language or DCL oversees the issuance of access and restrictions to users, including the rights and permissions required within the SQL statements.
    Example DCL keywords are:
    GRANT – DCL keyword that provides access to certain databases to users.
    REVOKE – opposite of the GRANT keyword. Revokes or withdraws the privileges given to the user.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free