DBMS INTERVIEW QUESTIONS

75 flashcards · Computer Science · by Moses John Prakash

1. What is SQL?

   SQL stands for ‘Structured Query Language’ and is used for communicating with databases.

2. What is a database?

   Organised collection of inter related data.

3. What is DBMS?

   DBMS stands for Database Management System. DBMS is a system software responsible for managing data in the database.

4. What is RDBMS?

   RDBMS stands for Relational Database Management System. RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables

5. What are the common RDBMS systems

   MySQL, Microsoft SQL Server, Oracle, IBM - DB2, and Amazon Redshift

6. What are the different types of DBMS?

   RDBMS Hierarchical DBMS Network DBMS Object-oriented DBMS

7. What are the advantages of DBMS?

   Redundancy control Data security Ensure data consistency Easy accessibility

8. What are the subset/different languages available in SQL?

   DDL (Data Definition Language) DML (Data Manipulation Language) TCL ( Transaction Control Language) DCL ( Data Control Language)

9. Mention the keywords available in DDL

   Create Alter Truncate Rename Drop

10. Mention the keywords available in DML

    Insert Update Delete Select

11. Mention the keywords available in TCL

    Commit Rollback

12. Mention the keywords available in DCL

    Grant Revoke

13. What is meant by ACID properties in DBMS?

    ACID stands for Atomicity, Consistency, Isolation, and Durability in a DBMS these are those properties that ensure a safe and secure way of sharing data among multiple users.

14. What is a database transaction?

    A transaction is a single logical unit of operation in the database. Transactions access data using read and write operations.

15. What is atomicity in dbms?

    Means that the entire transaction must take place at once or does not happen at all

16. What is Consistency in dbms transaction?

    The Database must remain consistent before and after the transaction.

17. What is Isolation in DBMS?

    Multiple transactions occur independently without any interference.

18. What is Durability?

    The changes of successful transactions remain even if the system failure occurs.

19. What are the three major types of Relationships?

    One to One One to Many Many to Many

20. What are the three anomalies in DBMS

    Insert anomaly update anomaly delete anomaly

21. What is a key in SQL ?

    A key is a value which is used to uniquely identifies the data in a table.

22. What is a primary key?

    A primary is a single column value used to identify a database record uniquely.

23. What are the characteristics of Primary key?

    Cannot not be NULL must be unique Table can have only one Primary key.

24. What are the constraints available in DBMS?

    Primary Key Default key Foreign Key Not Null Check Unique key Composite key

25. What is database Normalisation?

    It is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.

26. What are the numeric datatypes available in SQL

    Int Bigint Smallint tinyint float double

27. What are the string datatypes available in SQL

    char varchar BLOB Text etc..

28. What are the date datatypes available in SQL

    Date Datetime Timestamp Time Year

29. What is the difference between char and varchar

    Char is fixed length character data while varchar is variable length char data.

30. What is the difference between Delete and truncate

    Delete command is used to remove specific records while truncate removes all the records. Delete can be rolled back but truncate cannot be rolled back. Delete will not release the space occupied while truncate frees up the space.

31. What are the three capabilities of select statements

    Projection Selection Join

32. Which clause is used to select the records that satisfies the given condition?

    WHERE CALUSE

33. Which keyword is used to eliminate duplicate values?

    DISTINCT

34. Which clause is used to sort the output?

    Order by

35. Which keyword is used to sort the output in descending order

    desc

36. How will you list the databases available in MYSQL

    show databases

37. How will list the tables available in MYSQL?

    Show tables

38. Which clause is used to group the identical records to a single set of values

    GROUP BY

39. How will you restrict the output after grouping?

    Having

40. What is a join?

    Used to read records from multiple tables

41. What are the types of joins in SQL

    cross join or Cartesian product inner join Natural join outer join ( left , right, full) self join

42. What is the difference between Natural join and Inner join?

    Natural Join joins two tables based on same attribute name and datatypes. Inner Join joins two table on the basis of the column which is explicitly specified in the ON clause.

43. What is the difference between Inner and outer joins?

    Inner join selects only the matched records while the outer join select both matched and unmatched records.

44. What is a super key?

    It is a single key or set of multiple keys that identifies rows of a table.

45. What is a compound key

    Consists of two or more attributes that uniquely identify a record, even when the column is not unique by itself

46. What is a composite primary key

    Primary key that has two or more attributes

47. What is a foreign key?

    A common column that defines the relationship between two tables. It maintains data integrity.

48. What is CLAUSE in SQL?

    CLAUSE is an optional statement in SQL that defines a condition to present data.

49. What are the different clauses available in SQL?

    Where , Group by, Having, Order by

50. What is the main difference between union and union all

    Union removes duplicate records and union all will not remove the duplicate records in the output.

51. What is an entity

    It is a real time place or object or thing or event that exists in the real world about which data can be stored in the database.

52. What is a database?

    Organized collection of inter related data.

53. What is an Index ?

    It is a database object which is used to improve the query performance.

54. What is data integrity

    Assurance of accurate and consistent data.

55. What is a query?

    It is a request for data or information from the database.

56. what is a subquery?

    It is a query within a query or

57. What are set operators?

    These are special type of operators used to combine the results of two Queries.

58. What are the types of set operators?

    UNION , UNION ALL, INTERSECT, MINUS

59. What is union operator?

    Combine results of two or more select statements and eliminate duplicate records.

60. What is union all?

    Combine results of two or more select statements and will not eliminate duplicate records.

61. What is intersect ?

    Returns only the results common from both the select statements.

62. What is minus?

    Displays the rows which are present in the first query but absent in the second query.

63. What is an ALIAS?

    A temporary name assigned to the table or table column for the purpose of a particular SQL query

64. What is a view?

    A view in SQL is a virtual table based on the result-set of an SQL statement.

65. Which operator is used for pattern matching?

    LIKE operator

66. Which operator is used for comparing null values

    IS opeator

67. What are the three capabilities of select statement

    Projection Selection Join

68. What are common clauses used in SQL

    From clause where clause group by clause ON clause Using clause Having clause

69. What is a null value?

    It is an unknown or un assigned value. It is not equal to Zero or space. It will not occupy space.

70. What are types of Single row functions in SQL

    Character functions number functions date functions conversion functions General functions

71. Which function is used to return remainder in a division operator in SQL?

    MOD

72. What is the difference between the WHERE and HAVING clauses?

    WHERE clause is used to filter records before any groupings are established, whereas the HAVING clause is used to filter values from a group.

73. List the aggregate functions in SQL

    count min max avg sum

74. How will you establish IF... THEN ... ELSE.... (conditional control) in sql ?

    Using CASE expression.

75. How will you print current date in Oracle?

    select sysdate from dual.