SQL Query Interview Questions : Part 1 | Code Factory


Donate : Link

Medium Blog : Link

Applications : Link

1. Database

  • A database is a collection of information that is organized so that it can easily be accessed, managed, and updated.

2. Structured Query Language (SQL)

  • Structured Query Language (SQL) is a standardized language that allows you to perform operations on a database, such as creating entries, reading content, updating content, and deleting entries.

3. JDBC

  • JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

4. Open Database Connectivity (ODBC)

  • Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems.

5. What is MySQL?

  • MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. This is the world’s second most popular and widely used open source database.

6. How to Back Up and Restore a MySQL Database?

  • $ mysqldump -u root -p DB_Name > tut_backup.sql
  • $ mysql -u root -p DB_Name < tut_backup.sql

7. What are the difference between DDL, DML, DCL and TCL commands?

DDL:

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

  • CREATE – to create objects in the database
  • ALTER – alters the structure of the database
  • DROP – delete objects from the database
  • TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT – add comments to the data dictionary
  • RENAME – rename an object

DML:

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

  • SELECT – retrieve data from the a database
  • INSERT – insert data into a table
  • UPDATE – updates existing data within a table
  • DELETE – deletes all records from a table, the space for the records remain
  • MERGE – UPSERT operation (insert or update)
  • CALL – call a PL/SQL or Java subprogram
  • EXPLAIN PLAN – explain access path to data
  • LOCK TABLE – control concurrency

DCL:

Data Control Language (DCL) statements. Some examples:

  • GRANT – gives user’s access privileges to database
  • REVOKE – withdraw access privileges given with the GRANT command

TCL:

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

  • COMMIT – save work done
  • SAVEPOINT – identify a point in a transaction to which you can later roll back
  • ROLLBACK – restore database to original since the last COMMIT
  • SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use

8. What is Trigger? How many Triggers are possible in MySQL?

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

  • Before Insert
  • After Insert
  • Before Update
  • After Update
  • Before Delete
  • After Delete

9. SQL – Indexes

  • Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

10. Maximum size of table in MySQL.

Operating SystemFile-size Limit
Win32 w/ FAT/FAT322GB/4GB
Win32 w/ NTFS2TB (possibly larger)
Linux 2.2-Intel 32-bit2GB (LFS: 4GB)
Linux 2.4+(using ext3 file system) 4TB
Solaris 9/1016TB
OS X w/ HFS+2TB

11. Create statement

CREATE TABLE MyGuests (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  email VARCHAR(50),
  reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
  • NOT NULL – Each row must contain a value for that column, null values are not allowed
  • DEFAULT value – Set a default value that is added when no other value is passed
  • UNSIGNED – Used for number types, limits the stored data to positive numbers and zero
  • AUTO INCREMENT – MySQL automatically increases the value of the field by 1 each time a new record is added
  • PRIMARY KEY – Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT

12. Insert statement

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)

13. Update statement

UPDATE table_name
SET column1=value, column2=value2, ...
WHERE some_column=some_value

14. Delete statement

DELETE FROM table_name
WHERE some_column = some_value

// Delete All Records
// It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
DELETE FROM table_name;

15. Difference between Delete and Truncate.

Sr. No.KeyDeleteTruncate
1BasicIt is used to delete specific dataIt is used to delete the entire data of the table
2Where clauseWe can use with where clauseIt can’t be used with where clause
3LockingIt locks the table row before deleting the rowIt locks the entire table
4RollbackWe can rollback the changesWe can’t rollback the changes
5PerformanceIt is slower than truncateIt is faster than delete

16. Difference between Delete and Drop.

Sr. No.KeyDeleteDrop
1PurposeDelete Command, removes some or all tuples/records from a relation/tableDrop Command, removes named elements of schema like relations/table, constraints or entire schema.
2LanguageDelete is DMLDrop is DDL
3ClauseWhere clause is used to add filteringNo where clause is available
4RollbackDelete command can be rollbacked as it works on data bufferDrop command can’t be rollbacked as it works directly on data
5Memory SpaceTable memory space is not free if all records are deleted using Delete CommandDrop command frees the memory space
6ProblemDelete command may face shortage of memoryDrop Command may cause memory fragmentation
7InteractionSQL directly interacts with database serverPL/SQL does not directly interacts with database server
8OrientationSQL is data oriented languagePL/SQL is application oriented language
9ObjectiveSQL is used to write queries, create and execute DDL and DML statmentsPL/SQL is used to write program blocks, functions, procedures, triggers and packages

17. Different SQL JOINs

A    B
-    -
1    3
2    4
3    5
4    6

1. Inner Join

Note: INNER is optional. Simple JOIN is also considered as INNER JOIN

  • Returns all rows when there is at least one match in BOTH tables
select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

2. Left join

  • Return all rows from the left table, and the matched rows from the right table
select * from a LEFT OUTER JOIN b on a.a = b.b WHERE b.b IS NULL;

a |  b
--+-----
1 | null
2 | null

3. Left outer join

  • Returns all rows of table on left side of join. The rows for which there is no matching row on right side, result contains NULL in the right side.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

4. Right join

  • Return all rows from the right table, and the matched rows from the left table
select * from a RIGHT OUTER JOIN b on a.a = b.b WHERE a.a IS NULL;

a    |  b
-----+----
null |  5
null |  6

5. Right outer join

  • Returns all rows of table on Right side of join. The rows for which there is no matching row on Left side, result contains NULL in the Left side.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

6. Fulll join

  • Return all rows when there is a match in ONE of the tables
select * from a FULL OUTER JOIN b on a.a = b.b
  WHERE a.a IS NULL OR b.b IS NULL;

 a   |  b
-----+-----
   1 | null
   2 | null
null |    6
null |    5

7. Full outer join

  • Contains results of both Left and Right outer joins.
select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

18. Transaction

  • A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction. Atomicity.
  • A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

19. ACID properties in SQL.

  • ACID (an acronym for Atomicity, Consistency Isolation, Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all these four attributes should be achieved.
  • Atomicity is an all-or-none proposition.
  • Consistency guarantees that a transaction never leaves your database in a half-finished state.
  • Isolation keeps transactions separated from each other until they’re finished.
  • Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

20. Difference between WHERE and HAVING in SQL.

  • Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.
  • WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
  • One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.

21. What are the different types of indexes?

  • Unique – Guarantees unique values for the column(or set of columns) included in the index
  • Covering – Includes all of the columns that are used in a particular query (or set of queries), allowing the database to use only the index and not actually have to look at the table data to retrieve the results
  • Clustered – This is way in which the actual data is ordered on the disk, which means if a query uses the clustered index for looking up the values, it does not have to take the additional step of looking up the actual table row for any data not included in the index.

22. Normalization

  • Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.

23. Denormalization

  • In computing, denormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data.

24. Difference between statement and preparedstatement.

  • Statement: Use the for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.
  • PreparedStatement: Use the when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.

25. JDBC connection types / JDBC driver types

  • Type 1: JDBC-ODBC Bridge Driver
  • Type 2: JDBC-Native API
  • Type 3: JDBC-Net pure Java
  • Type 4: 100% Pure Java

26. PL/SQL Exceptions

  • System-defined exceptions
    • ACCESS_INTO_NULL
    • CASE_NOT_FOUND
    • COLLECTION_IS_NULL
    • DUP_VAL_ON_INDEX
    • INVALID_CURSOR
    • INVALID_NUMBER
    • LOGIN_DENIED
    • NO_DATA_FOUND
    • NOT_LOGGED_ON
    • PROGRAM_ERROR
    • ROWTYPE_MISMATCH
    • SELF_IS_NULL
    • STORAGE_ERROR
    • TOO_MANY_ROWS
    • VALUE_ERROR
    • ZERO_DIVIDE
  • User-defined exceptions

27. Procedure

  • subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the ‘Modular design’. A subprogram can be invoked by another subprogram or program which is called the calling program.

28. Function

29. Difference Between Procedure and Function

ProcedureFunction
Used mainly to a execute certain processUsed mainly to perform some calculation
Use OUT parameter to return the valueUse RETURN to return the value
It is not mandatory to return the valueIt is mandatory to return the value
procedures can’tFunctions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE
We can call function from procedureWe can’t call procedure from function

30. Cursors

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

There are two types of cursors –

  • Implicit cursors: Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.
  • Explicit cursors: Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

31. Triggers

  • Triggers are stored programs, which are automatically executed or fired when some events occur.

32. Sort EMP by salary and get 2nd EMP

  • MySQL – SELECT * FROM employee ORDER BY salary LIMIT 1, 1;

33. CHAR vs VARCHAR in SQL

CHARVARCHAR
CHAR datatype is used to store character string of fixed lengthVARCHAR datatype is used to store character string of variable length
In CHAR, If the length of string is less than set or fixed length then it is padded with extra memory space.In VARCHAR, If the length of string is less than set or fixed length then it will store as it is without padded with extra memory spaces.
CHAR stands for “Character”VARCHAR stands for “Variable Character”
Storage size of CHAR datatypes is equal to n bytes i.e. set lengthStorage size of VARCHAR datatype is equal to the actual length of the entered string in bytes.
We should use CHAR datatype when we expect the data values in a column are of same length.We should use VARCHAR datatype when we expect the data values in a column are of variable length.
Better performance than VARCHARPerformance is not good as compared to CHAR

34. SQL Data Types

https://www.w3schools.com/sql/sql_datatypes.asp

35. Difference between Primary key and Unique key

ParamenterPRIMARY KEYUNIQUE KEY
BasicUsed to serve as a unique identifier for each row in a table.Uniquely determines a row which isn’t primary key.
NULL value acceptanceCannot accept NULL values.Can accept one NULL value.
Number of keys that can be defined in the tableOnly one primary keyMore than one unique key
IndexCreates clustered indexCreates non-clustered index

36. What are indexes and how can I use them to optimize queries in my database?

  • Indexes can play an important role in query optimization and searching the results speedily from tables. So it is most important step to select which columns to be indexed. There are two major places where we can consider indexing: columns referenced in the WHERE clause and columns used in JOIN clauses.
SELECT
 buyers.buyer_id, /* no need to index */
 country.name    /* no need to index */
FROM buyers LEFT JOIN country
ON buyers.country_id=country.country_id /* consider to use index */
WHERE
 first_name='Tariq' /* consider to use index */
AND
 last_name='Iqbal' /* consider to use index */
  • According to the above queries first_name, last_name columns can be indexed as they are located in the WHERE clause. Also an additional field, country_id from country table, can be considered for indexing because it is in a JOIN clause. So indexing can be considered on every field in the WHERE clause or a JOIN clause.
  • The following list also offers a few tips that you should always keep in mind when intend to create indexes into your tables:
    • Only index those columns that are required in WHERE and ORDER BY clauses. Indexing columns in abundance will result in some disadvantages.
    • Try to take benefit of “index prefix” or “multi-columns index” feature of MySQL. If you create an index such as INDEX(first_name, last_name), don’t create INDEX(first_name). However, “index prefix” or “multi-columns index” is not recommended in all search cases.
    • Use the NOT NULL attribute for those columns in which you consider the indexing, so that NULL values will never be stored.
    • The EXPLAIN statement helps you to reveal that how MySQL will execute a query. It shows how and in what order tables are joined. This can be much useful for determining how to write optimized queries, and whether the columns are needed to be indexed.

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s