SQL Interview Questions and Answers

What is SQL?

SQL stands for the Structured Query Language. It is the standard language used to maintain the relational database and perform many different data manipulation operations on the data. SQL was initially invented in 1970. It is a database language used for database creation, deletion, fetching and modifying rows, etc. sometimes, it is pronounced as ‘sequel.’ We can also use it to handle organized data comprised of entities (variables) and relations between different entities of the data.

When SQL appeared(হাজির)?

SQL first appeared in 1974. It is one of the most used languages for maintaining the relational database. In 1986, SQL became the standard of the American National Standards Institute (ANSI) and ISO (International Organization for Standardization) in 1987.

What are the usages of SQL?

SQL is responsible for maintaining the relational data and the data structures present in the database. Some of the common usages are given below:

• To execute queries against a database
• To retrieve data from a database
• To inserts records in a database
• To updates records in a database
• To delete records from a database
• To create new databases
• To create new tables in a database
• To create views in a database
• To perform complex operations on the database.

Does SQL support programming language features?

SQL refers to the Standard Query Language. Therefore, it is true that SQL is a language but does not actually support the programming language. It is a common language that doesn’t have a loop, conditional statements, and logical operations. It cannot be used for anything other than data manipulation. It is a command language to perform database operations. The primary purpose of SQL is to retrieve, manipulate, update, delete, and perform complex operations like joins on the data present in the database.

What are the subsets of SQL?

The following are the four significant subsets of the SQL:

• Data definition language (DDL): It defines the data structure that consists of commands like CREATE, ALTER, DROP, etc.
• Data manipulation language (DML): It is used to manipulate existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc.
• Data control language (DCL): It controls access to the data stored in the database. The commands in this category include GRANT and REVOKE.
• Transaction Control Language (TCL): It is used to deal with the transaction operations in the database. The commands in this category are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.

What is the purpose of DDL Language?

DDL stands for Data definition language. It is the subset of a database that defines the data structure of the database when the database is created. For example, we can use the DDL commands to add, remove, or modify tables. It consists of the following commands: CREATE, ALTER and DELETE database objects such as schema, tables, indexes, view, sequence, etc.

Example

1. CREATE TABLE Students  
2. (  
3. Roll_no INT,  
4. Name VARCHAR(45),  
5. Branch VARCHAR(30),  
6. );  

What is the purpose of DML Language?

Data manipulation language makes the user able to retrieve and manipulate data in a relational database. The DML commands can only perform read-only operations on data. We can perform the following operations using DDL language:

• Insert data into the database through the INSERT command.
• Retrieve data from the database through the SELECT command.
• Update data in the database through the UPDATE command.
• Delete data from the database through the DELETE command.

Example

INSERT INTO Student VALUES (111, ‘George’, ‘Computer Science’)  

What is the purpose of DCL Language?

Data control language allows users to control access and permission management to the database. It is the subset of a database, which decides that what part of the database should be accessed by which user at what point of time. It includes two commands, GRANT and REVOKE.

GRANT: It enables system administrators to assign privileges and roles to the specific user accounts to perform specific tasks on the database.

REVOKE: It enables system administrators to revoke privileges and roles from the user accounts so that they cannot use the previously assigned permission on the database.

Example

GRANT * ON mydb.Student TO javatpoint@localhsot;  

What are tables and fields in the database?

A table is a set of organized data in the form of rows and columns. A table is a set of organized data in the form of rows and columns.

Fields are the components to provide the structure for the table.

Example

Table: Student

Field: Stud_rollno, Stud_name, Date of Birth, Branch, etc.

What is a primary key?

A primary key is a field or the combination of fields that uniquely identify each record in the table.

We can define a primary key into a student table as follows:

1. CREATE TABLE Student (    
2.     roll_number INT PRIMARY KEY,    
3.     name VARCHAR(45),     
4. );

What is a foreign key?

The foreign key is used to link one or more tables together. It is also known as the referencing key.

We can define a foreign key into a table as follows:

1. CONSTRAINT constraint_name]    
2.     FOREIGN KEY [foreign_key_name] (col_name, …)    
3.     REFERENCES parent_tbl_name (col_name,…)   

What is a unique key?

A unique key is a single or combination of fields that ensure all values stores in the column will be unique. It means a column cannot stores duplicate values.

We can define a foreign key into a table as follows:

1. CREATE TABLE table_name(    
2.     col1 datatype,    
3.     col2 datatype UNIQUE,    
4.     …    
5. );  

What is the difference between a primary key and a unique key?

The primary key and unique key both are essential constraints of the SQL. The main difference among them is that the primary key identifies each record in the table. In contrast, the unique key prevents duplicate entries in a column except for a NULL value.

Primary Key	Unique Key
Primary key will not accept NULL values	Unique key can accept NULL values.
A table can have only one primary key	There can be multiple unique key on a table.
The primary key act as a unique identifier for each record in the table.	The unique key is also a unique identifier for records when the primary key is not present in the table.
We cannot change or delete the primary key column values.	We can modify the unique key column values.

What is a Database?

A database is an organized collection of data that is structured into tables, rows, columns, and indexes.

The following are the common features of a database:

• Manages large amounts of data
• Accurate
• Easy to update
• Security
• Data integrity
• Easy to research data

What is meant by DBMS?

DBMS stands for Database Management System. It is a software program that primarily functions as an interface between the database and the end-user.

The following are the components of a DBMS:

• Software
• Data
• Procedures
• Database Languages
• Query Processor
• Database Manager
• Database Engine
• Reporting

What are the different types of database management systems?

The database management systems can be categorized into several types. Some of the important lists are given below:

• Hierarchical databases (DBMS)
• Network databases (IDMS)
• Relational databases (RDBMS
• Object-oriented databases
• Document databases (Document DB)
• Graph databases
• ER model databases
• NoSQL databases

What is RDBMS?

RDBMS stands for Relational Database Management System. It is a database management system based on a relational model. Examples of relational database management systems are Microsoft Access, MySQL, SQL Server, Oracle database, etc.

What is Normalization in a Database?

Normalization is used to minimize redundancy and dependency by organizing fields and table of a database.

There are some rules of database normalization, which is commonly known as Normal From, and they are:

• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Boyce-Codd normal form(BCNF)

Using these steps, the redundancy, anomalies, inconsistency of the data in the database can be removed.

What is the primary use of Normalization?

Normalization is mainly used to add, delete or modify a field that can be made in a single table. The primary use of Normalization is to remove redundancy and remove the insert, delete and update distractions. Normalization breaks the table into small partitions and then links them using different relationships to avoid the chances of redundancy.

What are the disadvantages of not performing database Normalization?

The major disadvantages are:

The occurrence of redundant terms in the database causes the waste of space in the disk.

Due to redundant terms, inconsistency may also occur. If any change is made in the data of one table but not made in the same data of another table, then inconsistency will occur. This inconsistency will lead to the maintenance problem and effects the ACID properties as well.

What is an inconsistent dependency?

An Inconsistent dependency refers to the difficulty of getting relevant data due to a missing or broken path to the data. It leads users to search the data in the wrong table, resulting in an error as an output.

What is Denormalization in a Database?

Denormalization is a technique used by database administrators to optimize the efficiency of their database infrastructure. The denormalization concept is based on Normalization, which is defined as arranging a database into tables correctly for a particular purpose.

Denormalization doesn’t mean that normalization will not be done. It is an optimization strategy that takes place after the normalization process.

What are the different types of SQL operators?

Operators are the special keywords or special characters reserved for performing particular operations.

The SQL operators can be categorized into the following types:

Arithmetic operators: These operators are used to perform mathematical operations on numerical data. The categories of this operators are addition (+), subtraction (-), multiplication (*), division (/), remainder/modulus (%), etc.

Logical operators: These operators evaluate the expressions and return their results in True or False. This operator includes ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE.

Comparison operators: These operators are used to perform comparisons of two values and check whether they are the same or not. It includes equal to (=), not equal to (!= or <>), less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), not less than (!<), not greater than (!>), etc.

Bitwise operators: It is used to do bit manipulations between two expressions of integer type. It first performs conversion of integers into binary bits and then applied operators such as AND (& symbol), OR (|, ^), NOT (~), etc.

Compound operators: These operators perform operations on a variable before setting the variable’s result to the operation’s result. It includes Add equals (+=), subtract equals (-=), multiply equals (*=), divide equals (/=), modulo equals (%=), etc.

String operators: These operators are primarily used to perform concatenation and pattern matching of strings. It includes + (String concatenation), += (String concatenation assignment), % (Wildcard), [] (Character(s) matches), [^] (Character(s) not to match), _ (Wildcard match one character), etc.

What is a view in SQL?

A view is a database object that has no values. It is a virtual table that contains a subset of data within a table. It looks like an actual table containing rows and columns, but it takes less space because it is not present physically. It is operated similarly to the base table but does not contain any data of its own. Its name is always unique. A view can have data from one or more tables. If any changes occur in the underlying table, the same changes reflected in the views also.

	Row1	Row2
Columns1	cdf	bdg
Columns2	bmg	kgl

Query

	Row1	Row1
Columns1	b	h
Columns2	u	b

	Row1	Row1
Columns1	cdf	bmg
Columns2	b	u
Columns3	bdg	kgl
Columns4	h	b

The primary use of a view is to implement the security mechanism. It is the searchable object where we can use a query to search the view as we use for the table. It only shows the data returned by the query that was declared when the view was created.

We can create a view by using the following syntax:

CREATE VIEW view_name AS  

SELECT column_lists FROM table_name  

WHERE condition;  

What is an Index in SQL?

An index is a disc structure associated with a table or view that speeds up row retrieval. It reduces the cost of the query because the query’s high cost will lead to a fall in its performance. It is used to increase the performance and allow faster retrieval of records from the table. Indexing reduces the number of data pages we need to visit to find a particular data page. It also has a unique value meaning that the index cannot be duplicated. An index creates an entry for each value which makes it faster to retrieve data.

For example: Suppose we have a book which carries the details of the countries. If you want to find out information about India, why will you go through every page of that book? You could directly go to the index. Then from the index, you can go to that particular page where all the information about India is given.

What are the different types of indexes in SQL?

SQL indexes are nothing more than a technique of minimizing the query’s cost. The higher the query’s cost, the worse the query’s performance. The following are the different types of Indexes supported in SQL:

• Unique Index
• Clustered Index
• Non-Clustered Index
• Bit-Map Index
• Normal Index
• Composite Index
• B-Tree Index
• Function-Based Index

What is the unique index?

UNIQUE INDEX is used to enforce the uniqueness of values in single or multiple columns. We can create more than one unique index in a single table. For creating a unique index, the user has to check the data in the column because the unique indexes are used when any column of the table has unique values. This indexing does not allow the field to have duplicate values if the column is unique indexed. A unique index can be applied automatically when a primary key is defined.

We can create it by using the following syntax:

Example

CREATE TABLE Employee(      

  ID int AUTO_INCREMENT PRIMARY KEY,       

   Name varchar(45),     

  Phone varchar(15),    

    City varchar(25),   

);  

Suppose we want to make a Phone column as a unique index. We can do this like below:

CREATE UNIQUE INDEX index_name_phone ON Employee (Phone);    

What is clustered index in SQL?

A clustered index is actually a table where the data for the rows are stored. It determines the order of the table data based on the key values that can sort in only one direction. Each table can have only one clustered index. It is the only index, which has been automatically created when the primary key is generated. If many data modifications needed to be done in the table, then clustered indexes are preferred.

What is the non-clustered index in SQL?

The indexes other than(অন্যান্য তারপর) PRIMARY indexes (clustered indexes) are called non-clustered indexes. We know that clustered indexes are created automatically when primary keys are generated, and non-clustered indexes are created when multiple joins conditions and various filters are used in the query. The non-clustered index and table data are both stored in different places. It cannot be able to alter the physical order of the table and maintains the logical order of data.

The purpose of creating a non-clustered index is for searching the data. Its best example is a book where the content is written in one place, and the index is at a different place. We can create 0 to 249 non-clustered indexes in each table. The non-clustered indexing improves the performance of the queries which use keys without assigning the primary key.

What are the differences between SQL, MySQL, and SQL Server?

The following comparison chart explains their main differences:

SQL	MySQL	SQL Server
SQL or Structured Query Language is useful for managing our relational databases. It is used to query and operate the database.	MySQL is the popular database management system used for managing the relational database. It is a fast, scalable, and easy-to-use database.	SQL Server is an RDBMS database system mainly developed for the Windows system to store, retrieve, and access data requested by the developer.
SQL first appeared in 1974.	MySQL first appeared on May 23, 1995.	SQL Server first appeared on April 24, 1989.
SQL was developed by IBM Corporation.	MySQL was developed by Oracle Corporation.	SQL Server was developed by Microsoft Company.
SQL is a query language for managing databases.	MySQL is database software that uses SQL language to conduct with the database.	SQL Server is also a software that uses SQL language to conduct with the database.
SQL has no variables.	MySQL can use variables constraints and data types.	SQL Server can use variables constraints and data types.
SQL is a programming language, so that it does not get any updates. Its commands are always fixed and remain the same.	MySQL is software, so it gets frequent updation.	SQL Server is also software, so it gets frequent updation.

What is the difference between SQL and PL/SQL?

SQL	PL/SQL
SQL is a database structured query language used to communicate with relational databases. It was developed by IBM Corporations and first appeared in 1974.	PL/SQL or Procedural Language/Structured Query Language is a dialect of SQL used to enhance the capabilities of SQL. Oracle Corporation developed it in the early 90’s. It uses SQL as its database language.
SQL is a declarative and data-oriented language.	PL/SQL is a procedural and application-oriented language.
SQL has no variables.	PL/SQL can use variables constraints and data types.
SQL can execute only a single query at a time.	PL/SQL can execute a whole block of code at once.
SQL query can be embedded in PL/SQL.	PL/SQL cannot be embedded in SQL as SQL does not support any programming language and keywords.
SQL can directly interact with the database server.	PL/SQL cannot directly interact with the database server.
SQL is like the source of data that we need to display.	PL/SQL provides a platform where SQL data will be shown.

Is it possible to sort a column using a column alias?

Yes. We can use the alias method in the ORDER BY instead of the WHERE clause for sorting a column.

What is the difference between clustered and non-clustered indexes in SQL?

Indexing is a method to get the requested data very fast. There are mainly two types of indexes in SQL, clustered index and non-clustered index. The differences between these two indexes are very important from an SQL performance perspective. The following comparison chart explains their main differences:

Clustered Index	Non-Clustered Index
A clustered index is a table or view where the data for the rows are stored. In a relational database, if the table column contains a primary key, MySQL automatically creates a clustered index named PRIMARY.	The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes. It has a structure separate from the data row. The non-clustered indexes are also known as secondary indexes.
Clustered indexes store the data information and the data itself.	Non-clustered indexes stores only the information, and then it will refer you to the data stored in clustered data.
There can only be one clustered index per table.	There can be one or more non-clustered indexes in a table.
A clustered index determines how data is stored physically in the table. Therefore, reading from a clustered index is faster.	It creates a logical ordering of data rows and uses pointers for accessing the physical data files. Therefore, reading from a clustered index is slower.
A clustered index always contains an index id of 0.	A non-clustered index always contains an index id>0.

What is the SQL query to display the current date?

There is a built-in function in SQL called GetDate(), which is used to return the current timestamp.

Which are joins in SQL? Name the most commonly used SQL joins?

SQL joins are used to retrieve data from multiple tables into a meaningful result set. It is performed whenever you need to fetch records from two or more tables. They are used with SELECT statement and join conditions.

The following are the most commonly used joins in SQL:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN

What are the different types of joins in SQL?

Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables. According to the ANSI standard, the following are the different types of joins used in SQL:

• INNER JOIN
• SELF JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN

			Type of Joins
			SQL Joins
	Inner Joins		Self Joins		Outer Joins		Cross Joins
			Left Outer Joins		Right Outer Joins		Full Outer Joins

What is a “TRIGGER” in SQL?

A trigger is a set of SQL statements that reside in a system catalog . It allows us to execute a batch of code when an insert, update or delete command is run against a specific table because the trigger is the set of activated actions whenever DML commands are given to the system.

SQL triggers have two main components one is action, and another is an event. When certain actions are taken, an event occurs as a result of those actions.

We use the CREATE TRIGGER statement for creating a trigger in SQL. Here is the syntax:

1. CREATE TRIGGER trigger_name      
2.     (AFTER | BEFORE) (INSERT | UPDATE | DELETE)    
3.          ON table_name FOR EACH ROW      
4.          BEGIN      
5.         –variable declarations      
6.         –trigger code      
7.         END;  

What is self-join and what is the requirement of self-join?

A SELF JOIN is used to join a table with itself.

A SELF JOIN is required when we want to combine data with other data in the same table itself. It is often very useful to convert a hierarchical structure to a flat structure.

The following syntax illustrates the SELF JOIN:

1. SELECT column_lists    
2. FROM table1 AS T1, table1 AS T2    
3. WHERE join_conditions;    

If we want to get retrieve the student_id and name from the table where student_id is equal, and course_id is not equal, it can be done by using the self-join:

1. SELECT  s1.student_id, s1.name    
2. FROM student AS s1, student s2    
3. WHERE s1.student_id=s2.student_id    
4. AND s1.course_id<>s2.course_id;    

What are the set operators in SQL?

we use the set operators to merge data from one or more tables of the same kind. Although the set operators are like SQL joins, there is a significant distinction.

1. UNION: It combines two or more results from multiple SELECT queries into a single result set. It has a default feature to remove the duplicate rows from the tables. The following syntax illustrates the Union operator:

·  SELECT columns FROM table1    

·  UNION    

·  SELECT columns FROM table2;   

B. UNION ALL: This operator is similar to the Union operator, but it does not remove the duplicate rows from the output of the SELECT statements. The following syntax illustrates the UNION ALL operator:

1. SELECT columns FROM table1    
2. UNION  ALL  
3. SELECT columns FROM table2;    

C. INTERSECT: This operator returns the common records from two or more SELECT statements. It always retrieves unique records and arranges them in ascending order by default. Here, the number of columns and data types should be the same. The following syntax illustrates the INTERSECT operator:

1. SELECT columns FROM table1    
2. INTERSECT  
3. SELECT columns FROM table2;    

D. MINUS: This operator returns the records from the first query, which is not found in the second query. It does not return duplicate values. The following syntax illustrates the MINUS operator:

1. SELECT columns FROM table1    
2. MINUS  
3. SELECT columns FROM table2;    

What is the difference between IN and BETWEEN operators?

BETWEEN Operator	IN Operator
This operator is used to selects the range of data between two values. The values can be numbers, text, and dates as well.	It is a logical operator to determine whether or not a specific value exists within a set of values. This operator reduces the use of multiple OR conditions with the query.
It returns records whose column value lies in between the defined range.	It compares the specified column’s value and returns the records when the match exists in the set of values.
The following syntax illustrates this operator: SELECT * FROM table_name WHERE column_name BETWEEN ‘value1’ AND ‘value2’;	The following syntax illustrates this operator: SELECT * FROM table_name WHERE column_name IN (‘value1′,’value 2’);

What is a constraint? Tell me about its various levels.

The constraint is used to specify the rule and regulations that allows or restricts what values/data will be stored in the table. It ensures data accuracy and integrity inside the table. Some of the most commonly used constraints are NOT NULL, PRIMARY KEY, FOREIGN KEY, AUTO_INCREMENT, UNIQUE KEY, etc.

The following syntax illustrates us to create a constraint for a table:

1. CREATE TABLE table_name (    
2.     column1 datatype constraint,    
3.     column2 datatype constraint,   
4.     ………    
5. );

SQL categories the constraints into two levels:

      Column Level Constraints: These constraints are only applied to a single column and      limit the type of data that can be stored in that column.

      Table Level Constraints: These constraints are applied to the entire table and limit the type of data that can be entered.

How to write an SQL query to find students’ names start with ‘A’?

SELECT * FROM student WHERE stud_name like ‘A%’;  

Write the SQL query to get the third maximum salary of an employee from a table named employees.

SELECT * FROM `employees` ORDER BY `salary` DESC LIMIT 1 OFFSET 2  

The following are the alternative way to get the third-highest salary of an employee:

A. Using LIMIT Keyword

1. SELECT salary FROM employees   
2. ORDER BY salary DESC   
3. LIMIT 2, 1;  

B. Using Subquery

1. SELECT salary    
2. FROM   
3.     (SELECT salary   
4.      FROM employees   
5.      ORDER BY salary DESC   
6.      LIMIT 3) AS Temp   
7. ORDER BY salary LIMIT 1;  

C. Using TOP Keyword

1. SELECT TOP 1 salary   
2. FROM   
3.     (SELECT DISTINCT TOP 3 salary   
4.      FROM employees   
5.      ORDER BY salary DESC) AS Temp   
6. ORDER BY salary ASC;  

What is the difference between DELETE and TRUNCATE statements in SQL?

No.	DELETE	TRUNCATE
1)	The delete statement removes single or multiple rows from an existing table depending on the specified condition.	The truncate command deletes the whole contents of an existing table without the table itself. It preserves the table structure or schema.
2)	DELETE is a DML command.	TRUNCATE is a DML command.
3)	We can use the WHERE clause in the DELETE command.	We cannot use the WHERE clause with TRUNCATE.
4)	DELETE statement is used to delete a row from a table.	TRUNCATE statement is used to remove all the rows from a table.
5)	DELETE is slower because it maintained the log.	TRUNCATE statement is faster than DELETE statement as it deletes entire data at a time without maintaining transaction logs.
6)	You can roll back data after using the DELETE statement.	It is not possible to roll back after using the TRUNCATE statement.
7)	DELETE query takes more space.	TRUNCATE query occupies less space.

What is the ACID property in a database?

The ACID properties are meant for the transaction that goes through a different group of tasks. A transaction is a single logical order of data. It provides properties to maintain consistency before and after the transaction in a database. It also ensures that the data transactions are processed reliably in a database system.

The ACID property is an acronym for Atomicity, Consistency, Isolation, and Durability.

Atomicity: It ensures that all statements or operations within the transaction unit must be executed successfully. If one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. Its main features are COMMIT, ROLLBACK, and AUTO-COMMIT.

Consistency: This property ensures that the data must meet all validation rules. In simple words, we can say that the database changes state only when a transaction will be committed successfully. It also protects data from crashes.

Isolation: This property guarantees that the concurrent property of execution in the transaction unit must be operated independently. It also ensures that statements are transparent to each other. The main goal of providing isolation is to control concurrency in a database.

Durability: This property guarantees that once a transaction has been committed, it persists permanently even if the system crashes, power loss, or failed.

Is a blank space or zero the same as a NULL value?

No. The NULL value is not the same as zero or a blank space. The following points explain their main differences:

• A NULL value is a value, which is ‘unavailable, unassigned, unknown or not applicable.’ It would be used in the absence of any value. We can perform arithmetic operations on it. On the other hand, zero is a number, and a blank space is treated as a character.
• The NULL value can be treated as an unknown and missing value, but zero and blank spaces differ from the NULL value.
• We can compare a blank space or a zero to another blank space or a zero. On the other hand, one NULL may not be the same as another NULL. NULL indicates that no data has been provided or that no data exists.

What are functions and their usage in SQL?

SQL functions are simple code snippets (টুকিটাকি) that are frequently used and re-used in database systems for data processing and manipulation. Functions are the measured values. It always performs a specific task. The following rules should be remembered while creating functions:

• A function should have a name, and the name cannot begin with a special character such as @, $, #, or other similar characters.
• Functions can only work with the SELECT statements.
• Every time a function is called, it compiles.
• Functions must return value or result.
• Functions are always used with input parameters.

SQL categories the functions into two types:

• User-Defined Function: Functions created by a user based on their needs are termed user-defined functions.
• System Defined Function: Functions whose definition is defined by the system are termed system-defined functions. They are built-in database functions.

SQL functions are used for the following purposes:

• To perform calculations on data
• To modify individual data items
• To manipulate the output
• To format dates and numbers
• To convert data types

What is meant by case manipulation functions? Explains its different types in SQL.

Case manipulation functions are part of the character functions. It converts the data from the state in which it is already stored in the table to upper, lower, or mixed case. The conversion performed by this function can be used to format the output. We can use it in almost every part of the SQL statement. Case manipulation functions are mostly used when you need to search for data, and you don’t have any idea that the data you are looking for is in lower case or upper case.

There are three case manipulation functions in SQL:

LOWER: This function is used to converts a given character into lowercase. The following example will return the ‘STEPHEN’ as ‘stephen’:

1. SELECT LOWER (‘STEPHEN’) AS Case_Reault FROM dual;  

UPPER: This function is used to converts a given character into uppercase. The following example will return the ‘stephen’ as ‘STEPHEN’:

1. SELECT UPPER (‘stephen’) AS Case_Reault FROM dual;  

INITCAP: This function is used to converts given character values to uppercase for the initials of each word. It means every first letter of the word is converted into uppercase, and the rest is in lower case. The following example will return the ‘hello stephen’ as ‘Hello Stephen’:

1. SELECT INITCAP (‘hello stephen’) AS Case_Reault FROM dual;  

Explain character-manipulation functions? Explains its different types in SQL.

Character-manipulation functions are used to change, extract, and alter the character string. When one or more characters and words are passed into the function, the function will perform its operation on those input strings and return the result.

The following are the character manipulation functions in SQL:

A) CONCAT: This function is used to join two or more values together. It always appends the second string into the end of the first string. For example:

Input: SELECT CONCAT (‘Information-‘, ‘technology’) FROM DUAL;

Output: Information-technology

B) SUBSTR: It is used to return the portion of the string from a specified start point to an endpoint. For example:

Input: SELECT SUBSTR (‘Database Management System’, 9, 11) FROM DUAL;

Output: Management

C) LENGTH: This function returns the string’s length in numerical value, including the blank spaces. For example:

Input: SELECT LENGTH (‘Hello Javatpoint’) FROM DUAL;

Output: 16

D) INSTR: This function finds the exact numeric position of a specified character or word in a given string. For example:

Input: SELECT INSTR (‘Hello Javatpoint’, ‘Javatpoint’);

Output: 7

E) LPAD: It returns the padding of the left-side character value for right-justified value. For example:

Input: SELECT LPAD (‘200′, 6,’*’);

Output: ***200

F) RPAD: It returns the padding of the right-side character value for left-justified value. For example:

Input: SELECT RPAD (‘200′, 6,’*’);

Output: 200***

G) TRIM: This function is used to remove all the defined characters from the beginning, end, or both. It also trimmed extra spaces. For example:

Input: SELECT TRIM (‘A’ FROM ‘ABCDCBA’);

Output: BCDCB

H) REPLACE: This function is used to replace all occurrences of a word or portion of the string (substring) with the other specified string value. For example:

Input: SELECT REPLACE ( ‘It is the best coffee at the famous coffee shop.’, ‘coffee’, ‘tea’);

Output: It is the best tea at the famous tea shop.

What is the usage of the NVL() function?

The NVL() function is used to convert the NULL value to the other value. The function returns the value of the second parameter if the first parameter is NULL. If the first parameter is anything other than NULL, it is left unchanged. This function is used in Oracle, not in SQL and MySQL. Instead of NVL() function, MySQL have IFNULL() and SQL Server have ISNULL() function.

For More : https://www.youtube.com/watch?v=7mzVU–B15I&list=PLX2qHGPZlD6xm99MbNlyHKXy3lPG0HVTq&index=4