SQL Query Interview Questions

1. What is SQL and why is it used?

SQL (Structured Query Language) is a programming language used to communicate with and manipulate databases. It is used to create, alter, and query relational databases, which store data in tables consisting of rows and columns.

2. What are the different types of SQL commands?

There are several types of SQL commands, including:

• Data Definition Language (DDL) commands: These commands are used to define the database structure or schema. Examples include CREATE, ALTER, and DROP.
• Data Manipulation Language (DML) commands: These commands are used to manipulate data within the database. Examples include SELECT, INSERT, UPDATE, and DELETE.
• Data Control Language (DCL) commands: These commands are used to control access to the database. Examples include GRANT and REVOKE.

3. How do you select all columns from a table?

To select all columns from a table, use the following syntax:

 SELECT * FROM table_name;

4. How do you select a specific column from a table?

To select a specific column from a table, use the following syntax:

 SELECT column_name FROM table_name;

5. How do you select distinct values from a column?

To select distinct values from a column, use the DISTINCT keyword in your SELECT statement:

 SELECT DISTINCT column_name FROM table_name;

6. How do you count the number of rows in a table?

To count the number of rows in a table, use the COUNT function:

 SELECT COUNT(*) FROM table_name;

7. How do you create a new table in a database?

To create a new table in a database, use the CREATE TABLE statement:

CREATE TABLE table_name (
  column_1 datatype constraint,
  column_2 datatype constraint,
  ...
);

8. How do you add a column to an existing table?

To add a column to an existing table, use the ALTER TABLE and ADD COLUMN statements:

ALTER TABLE table_name
ADD COLUMN column_name datatype constraint;

9. How do you update data in a table?

To update data in a table, use the UPDATE and SET statements:

UPDATE table_name
SET column_name = new_value
WHERE condition;

10. How do you delete data from a table?

To delete data from a table, use the DELETE FROM statement:

DELETE FROM table_name
WHERE condition;

11. How do you insert data into a table?

To insert data into a table, use the INSERT INTO and VALUES statements:

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_1, value_2, ...);

12. How do you create a primary key in a table?

To create a primary key in a table, use the PRIMARY KEY constraint when defining the column:

CREATE TABLE table_name (
  column_name datatype PRIMARY KEY,
  ...
);

13. How do you create a foreign key in a table?

In SQL, you can create a foreign key constraint on a table using the ALTER TABLE statement, along with the ADD CONSTRAINT clause. The syntax for creating a foreign key is as follows:eign key

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);

Here, child_table is the name of the table that will have the foreign key, constraint_name is the name you assign to the foreign key constraint, and column1, column2, ... column_n are the columns in the child table that make up the foreign key. The REFERENCES clause specifies the parent table and the columns in the parent table that the foreign key references.

It’s worth noting that foreign key constraints enforce referential integrity, meaning they ensure that the values in the foreign key columns in the child table correspond to existing values in the referenced columns in the parent table.

14.  Write an SQL query to fetch the current date-time from the system.

 TO fetch the CURRENT DATE IN SQL Server
 SELECT GETDATE();
 TO fetch the CURRENT DATE IN MYSQL
 SELECT NOW();
 TO fetch the CURRENT DATE IN Oracle
 SELECT SYSDATE FROM DUAL();

15. Write a SQL query to fetch the PatientName in uppercase and state as lowercase. Also use the ALIAS name for the result-set as PatName and NewState.

 TO fetch the CURRENT DATE IN SQL Server
 SELECT GETDATE();
 TO fetch the CURRENT DATE IN MYSQL
 SELECT NOW();
 TO fetch the CURRENT DATE IN Oracle
 SELECT SYSDATE FROM DUAL();

16. Find the Nth highest consultation fees from the PatientsCheckup table with and without using the TOP/LIMIT keywords.

Nth highest consultation fees from the PatientsCheckup table with using the TOP keywords

SELECT TOP 1 ConsultationFees
FROM(SELECT TOP N ConsultationFees
FROM PatientsCheckup
ORDER BY ConsultationFees DESC) AS FEES
ORDER BY ConsultationFees ASC;

The Nth highest consultation fees from the PatientsCheckup table using the LIMIT keywords.

SELECT ConsultationFees
FROM PatientsCheckup
ORDER BY ConsultationFees DESC LIMIT N-1,1;

Nth highest consultation fees from the PatientsCheckup table without using the TOP/LIMIT keywords.

SELECT ConsultationFees
FROM PatientsCheckup F1
WHERE N-1 = (
      SELECT COUNT( DISTINCT ( F2.ConsultationFees ) )
      FROM PatientsCheckup F2
      WHERE F2.ConsultationFees >  F1.ConsultationFees );

17. Write a query to fetch top N records using the TOP/LIMIT, ordered by ConsultationFees.

TOP Command – SQL Server

SELECT TOP N * FROM PatientsCheckup ORDER BY ConsultationFees DESC;
 
LIMIT Command - MySQL
SELECT * FROM PatientsCheckup ORDER BY ConsultationFees DESC LIMIT N;

18. Write a SQL query to create a table where the structure is copied from other table.

• Create an Empty Table
• Create a table consisting data

To create an Empty table

CREATE TABLE NewPatientsTable
SELECT * FROM Patients WHERE 1=0;

Create a table consisting of data

-USING SELECT command
SELECT * INTO NewPatientsTable FROM Patients WHERE 1 = 0;
Copy code
 – USING CREATE command IN MySQL
CREATE TABLE NewPatientsTable AS SELECT * FROM Patients;
Copy code

19. Write a query to fetch even and odd rows from a table.

If you have an auto-increment field like PatientID then you can use the MOD() function:

Fetch even rows using MOD() function:

SELECT * FROM Patients WHERE MOD(PatientID,2)=0;

Fetch odd rows using MOD() function:

SELECT * FROM Patients WHERE MOD(PatientID,2)=1;

In case there are no auto-increment fields then you can use the Row_number in SQL Server or a user-defined variable in MySQL. Then, check the remainder when divided by 2.

Fetch even rows in SQL Server

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY PatientId) AS RowNumber
    FROM Patients
) P
WHERE P.RowNumber % 2 = 0;


Fetch even rows in MySQL


SELECT * FROM (SELECT *, @rowNumber := @rowNumber+ 1 rn
      FROM Patients
      JOIN (SELECT @rowNumber:= 0) r
     ) p
WHERE rn % 2 = 0;


In case you wish to find the odd rows, then the remainder when divided by 2 should be 0

20. Write an SQL query to fetch duplicate records from Patients, without considering the primary key.

SELECT PatientName, DoctorID, RegDate, State, COUNT(*)FROM Patients
GROUP BY PatientName, DoctorID, RegDate, State
HAVING COUNT(*) > 1;

SQL Query Interview Questions

21. Write a query to fetch the number of patients whose weight is greater than 68.

SELECT COUNT(*) FROM PatientsCheckup WHERE Weight > '68';

22. Write a query to retrieve the list of patients from the same state.

SELECT DISTINCT P.PatientID, P.PatientName, P.State
FROM Patients P, Patient P1
WHERE P.State = P1.State AND P.PatientID != P1.PatientID;

23. Write a query to retrieve two minimum and maximum consultation fees from the PatientsCheckup Table.

[code]
– TWO MINIMUM CONSULTATION FEES
SELECT DISTINCT ConsultationFees FROM PatientsCheckup P1
 WHERE 2 >= (SELECT COUNT(DISTINCT ConsultationFees)FROM PatientsCheckup P2
  WHERE P1.ConsultationFees >= P2.ConsultationFees) ORDER BY P1.SConsultationFees DESC;
 
– TWO MAXIMUM CONSULTATION FEES
SELECT DISTINCT ConsultationFees FROM PatientsCheckup P1
 WHERE 2 >= (SELECT COUNT(DISTINCT ConsultationFees)FROM PatientsCheckup P2
  WHERE P1.ConsultationFees <= P2.ConsultationFees) ORDER BY P1.ConsultationFees DESC;
[/code]

24. Write a query to fetch patient details along with the weight fees, even if the details are missing.

SELECT P.PatientName, C.ConsultationFees
FROM Patients P
LEFT JOIN
PatientsCheckup C
ON P.PatientId = C.PatientId;

25. Write a SQL query to fetch doctor wise count of patients sorted by the doctors.

SELECT DoctorID, COUNT(PatientID) AS DocPat
FROM Patients GROUP BY DoctorID
ORDER BY DocPat;

26. Write a SQL query to fetch the first and last record of the Patients table.

–FETCH FIRST RECORD
SELECT * FROM Patients WHERE PatientID = (SELECT MIN(PatientID) FROM Patients);
 
–FETCH LAST RECORD
SELECT * FROM Patients WHERE PatientID = (SELECT MAX(PatientID) FROM Patients);

27. Write a SQL query to fetch consultation fees – wise count and sort them in descending order.

SELECT ConsultationFees, COUNT(PatientId) CFCount
FROM PatientsCheckup
GROUP BY ConsultationFees
ORDER BY CFCount DESC;

28. Write a SQL query to retrieve patient details from the Patients table who have a weight in the PatientsCheckup table.

SELECT * FROM Patients P
WHERE EXISTS(SELECT * FROM PatientsCheckup C WHERE P.PatientID = C.PatientID);

29. Write a SQL query to retrieve the last 2 records from the Patients table.

SELECT * FROM Patients WHERE
PatientID <=2 UNION SELECT * FROM(SELECT * FROM Patients P ORDER BY P.PatientID DESC)AS P1 WHERE P1.PatientID <=2;

30. Write a SQL query  to find all the patients who joined in the year 2022.

–USING BETWEENSELECT * FROM Patients
WHERE RegDate BETWEEN '2021/01/01' AND '2021/12/31';
 
– USING YEARSELECT * FROM Patients WHERE YEAR(RegDate ) = '2021'; 

31. Write a SQL query to fetch 50% records from the PatientsCheckup table.

SELECT *FROM PatientsCheckup WHERE
PatientID <= (SELECT COUNT(PatientD)/2 FROM PatientsCheckup);

32. Write a query to find those patients who have paid consultation fees between 400 to 700.

SELECT * FROM Patients WHERE PatientID IN
(SELECT PatientID FROM PatientsCheckup WHERE ConsultationFees BETWEEN '400' AND '700');

33. Write a query to update the patient names by removing the leading and trailing spaces.

UPDATE Patients
SET PatientName = LTRIM(RTRIM(PatientName));

34. Write a query to add email validation to your database.

SELECT email FROM Patients WHERE NOT REGEXP_LIKE(email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

35. Write a query to find all patient names whose name:

• Begin with A
• Ends with S and contains 3 alphabets
• Staying in the state Telangana

SELECT * FROM Patients WHERE PatientName LIKE 'A%';
SELECT * FROM Patients WHERE PatientName LIKE '___S';
SELECT * FROM Patients WHERE State LIKE 'Telangana%’;

36. Write a SQL query to fetch details of all patients excluding patients with name  “Sheela” and “Anay”.

SELECT * FROM Patients WHERE PatientName NOT IN ('Sheela','Anay'); 

37. Write a query to fetch the total count of occurrences of a particular character – ‘x’ in the PatientName.

SELECT PatientName, PatientID
LENGTH(PatientName) - LENGTH(REPLACE(PatientName, 'x', ''))FROM Patients;

38. Write a query to retrieve the first three characters of  PatientName from the Patients table.

SELECT SUBSTRING(PatientName, 1, 3) FROM Patients; 

39. Write a query to fetch only the Address (string before space).

USING the MID FUNCTION IN MySQL
SELECT MID(Address, 0, LOCATE(' ',Address)) FROM Patients;
 
USING SUBSTRINGSELECT SUBSTRING(Address, 1, CHARINDEX(' ',Address)) FROM Patients;

40. Write a query to combine Address and state into a new column – NewAddress.

SELECT CONCAT(Address, ' ', State) AS 'NewAddress' FROM Patients; 

41. Write a query to fetch PatientIDs  which are present in: 

• Both tables
• One of the table. Let us say, patients present in Patients and not in the PatientsCheckup table.

–Present IN BOTH TABLESSELECT PatientId FROM Patients
WHERE PatientId IN(SELECT PatientId FROM PatientsCheckup);
 
– Present IN One OF the TABLESELECT PatientId FROM Patients
WHERE PatientId NOT IN(SELECT PatientId FROM PatientsCheckup);

42. Write a query to find the number of patients whose RegDate is between 01/04/2021 to 31/12/2022 and are grouped according to state.

SELECT COUNT(*), State FROM Patients WHERE RegDate BETWEEN '01/04/2021' AND '31/12/2022' GROUP BY State;

43. Write a query to fetch all records from the Patients table; ordered by PatientName in ascending order, State in descending order.

SELECT * FROM Patients ORDER BY PatientName ASC, State DESC;

With this, we end this article on the top 40+ SQL query interview questions. We hope you found it informative.