SQL Interview Questions

  1. What is SQL?
    SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database etc. SQL is an ANSI(American National Standards Institute) standard. Using SQL, we can do many things, for example – we can execute queries, we can insert records in a table, we can update records, we can create a database, we can create a table, we can delete a table etc.
  2. What is a Database?
    A Database is defined as a structured form of data which is stored in a computer or data in an organised manner and can be accessed in various ways. It is also the collection of schemas, tables, queries, views etc. Database helps us in easily storing, accessing and manipulation of data held in a computer. The Database Management System allows a user to interact with the database.


  3. Does SQL support programming language features ?
    It is true that SQL is a language but it does not support programming as it is not a programming language, it is a command  language. We do not have conditional statements in SQL like for loops or if..else, we only have commands which we can use to query, update , delete etc. data in the database. SQL allows us to manipulate data in a database.
  4. What are the differences between SQL and PL/SQL?
    Ans: Some common differences between SQL and PL/SQL are as shown below:

    SQL PL/SQL
    SQL is a query execution or commanding language PL/SQL is a complete programming language
    SQL is data oriented language PL/SQL is a procedural language
    SQL is very declarative in nature PL/SQL has a procedural nature
    It is used for manipulating data It is used for creating applications
    We can execute one statement at a time in SQL We 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/SQL We can not embed PL/SQL in SQL
  5. What is the difference between BETWEEN and IN operators in SQL?
    BETWEEN
    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.

  6. 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.

  7. What is the difference between CHAR and VARCHAR2 datatype in SQL?
    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.
  8. Name different types of case manipulation functions available in SQL.
    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')
      
  9. What do you mean by data definition language?
    Data definition language or DDL allows to execute queries like CREATE, DROP and ALTER. That is, those queries which define the data.


  10. What do you mean by data manipulation language?
    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.
  11. What is the difference between primary key and unique constraints?
    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 does not.
  12. What is a view in SQL?
    Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.
    The CREATE VIEW statement of SQL is used for creating Views.
    Basic Syntax:

    CREATE VIEW view_name AS
    SELECT column1, column2.....
    FROM table_name
    WHERE condition;
    
    view_name: Name for the View
    table_name: Name of the table
    condition: Condition to select rows
    

    For more details on how to create and use view, please refer to this article.

  13. What do you mean by foreign key?
    A Foreign key is a field which can uniquely identify each row in another table. And this constraint is used to specify a field as Foreign key. That is, this field points to primary key of another table. This usually creates a kind of link between the two tables.
    Consider the two tables as shown below:

    Orders

    O_ID ORDER_NO C_ID
    1 2253 3
    2 3325 3
    3 4521 2
    4 8532 1

    Customers

    C_ID NAME ADDRESS
    1 RAMESH DELHI
    2 SURESH NOIDA
    3 DHARMESH GURGAON

    As we can see clearly that the field C_ID in Orders table is the primary key in Customers table, i.e. it uniquely identifies each row in the Customers table. Therefore, it is a Foreign Key in Orders table.
    Syntax:

    CREATE TABLE Orders
    (
    O_ID int NOT NULL,
    ORDER_NO int NOT NULL,
    C_ID int,
    PRIMARY KEY (O_ID),
    FOREIGN KEY (C_ID) REFERENCES Customers(C_ID)
    )
    
  14. What is a join in SQL? What are the types of joins?
    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.

    To know about each of these joins in details, refer this.

  15. What is an index?
    A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on disk. To support faster access according to different values, faster search like binary search for different values is desired. For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.

This article is contributed by Harsh Agarwal. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



My Personal Notes arrow_drop_up


Article Tags :
Practice Tags :


14


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.