0% found this document useful (0 votes)
9 views

SQL Interview Questions

psql

Uploaded by

ruthiselva
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
9 views

SQL Interview Questions

psql

Uploaded by

ruthiselva
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 13

Certainly!

Let's go through each question listed in your image with detailed explanations and
real-time examples to help you prepare for HR interviews.

1. **What is SQL?
- **Answer**: SQL (Structured Query Language) is a standard
programming language specifically designed for managing and
manipulating relational databases. It is used to perform tasks such as
querying data, updating data, and managing database structures.
- **Example**: If you have a database of employees, you can use SQL to
retrieve all employees' names who work in the 'Sales' department.

2. **What are SQL dialects? Give some examples.


- **Answer**: SQL dialects are variations of SQL specific to different
database management systems (DBMS). While they all adhere to the basic
SQL standard, they include proprietary extensions.
- **Examples**:
- **MySQL**: Uses `LIMIT` to limit the number of rows returned.
- **PostgreSQL**: Has a `RETURNING` clause to return values after
an `INSERT` or `UPDATE`.
- **Oracle SQL**: Uses `ROWNUM` for row limiting.

3. **What are the main applications of SQL?**


- **Answer**: SQL is used for various database operations such as:
- Querying data
- Updating records
- Inserting new data
- Deleting data
- Creating and modifying database structures (schemas)
- **Example**: A web application can use SQL to fetch user data from a
database when a user logs in.
4. **What is an SQL statement? Give some examples.**
- **Answer**: An SQL statement is a text string that contains a
command to be executed on a database.
- **Examples**:
- `SELECT * FROM Employees;` - Retrieves all columns from the
Employees table.
- `INSERT INTO Employees (Name, Department) VALUES ('John Doe',
'HR');` - Adds a new employee record.
- `UPDATE Employees SET Department = 'Finance' WHERE Name =
'John Doe';` - Updates John's department.

5. **Types of SQL commands**


- **Answer**: SQL commands can be categorized into:
- **DDL (Data Definition Language)**: `CREATE`, `ALTER`, `DROP`
- **DML (Data Manipulation Language)**: `SELECT`, `INSERT`,
`UPDATE`, `DELETE`
- **DCL (Data Control Language)**: `GRANT`, `REVOKE`
- **TCL (Transaction Control Language)**: `COMMIT`,
`ROLLBACK`, `SAVEPOINT`
- **Example**: `CREATE TABLE Employees (ID INT, Name
VARCHAR(100));` - A DDL command to create a new table.

6. **What is a Database?**
- **Answer**: A database is an organized collection of structured data,
typically stored electronically in a computer system.
- **Example**: An online retail store uses a database to store information
about products, customers, orders, and inventory.

7. **What is DBMS and what types of DBMS do you know?**


- **Answer**: A Database Management System (DBMS) is software that
interacts with end-users, applications, and the database itself to capture
and analyze data.
- **Types**:
- **RDBMS (Relational DBMS)**: MySQL, PostgreSQL, Oracle
- **NoSQL DBMS**: MongoDB, Cassandra
- **NewSQL**: CockroachDB, NuoDB

8. **What is RDBMS?**
- **Answer**: RDBMS (Relational Database Management System) is a
type of DBMS that stores data in tables with rows and columns. It uses
SQL for database access.
- **Example**: MySQL is a widely used RDBMS that powers many web
applications.

9. **What are tables and fields in SQL?**


- **Answer**:
- **Tables**: Structures within a database that organize data into rows
and columns.
- **Fields**: Columns in a table that represent data attributes.
- **Example**: In an `Employees` table, `Name` and `Department`
would be fields, and each row would represent an employee's record.

10. **What is an SQL query and what types of queries do you know?**
- **Answer**: An SQL query is a request to perform a specific action on
the database. Common types include:
- **SELECT**: Retrieve data from the database.
- **INSERT**: Add new data.
- **UPDATE**: Modify existing data.
- **DELETE**: Remove data.
- **Example**: `SELECT Name FROM Employees WHERE
Department = 'HR';` - Retrieves names of employees in HR.

11. **What is a subquery?**


- **Answer**: A subquery is a query nested within another SQL query.
It is used to perform operations that require multiple steps or intermediate
results.
- **Example**:
```sql
SELECT Name FROM Employees
WHERE DepartmentID = (SELECT ID FROM Departments WHERE
Name = 'HR');
```
- Here, the subquery finds the ID of the 'HR' department.

12. **What types of SQL subqueries do you know?**


- **Answer**:
- **Single-row subqueries**: Return a single row.
- **Multiple-row subqueries**: Return multiple rows.
- **Correlated subqueries**: Depend on the outer query for their
values.
- **Example**:
- Single-row: `SELECT * FROM Employees WHERE DepartmentID =
(SELECT ID FROM Departments WHERE Name = 'HR');`
- Multiple-row: `SELECT Name FROM Employees WHERE
DepartmentID IN (SELECT ID FROM Departments WHERE Location =
'New York');`

13. **What is a constraint and why use constraints?**


- **Answer**: Constraints are rules enforced on data columns to ensure
data integrity and accuracy.
- **Example**:
- **Primary Key**: Ensures each record is unique.
- **Foreign Key**: Maintains referential integrity between tables.
- **NOT NULL**: Ensures a column cannot have NULL values.
- **Why use constraints**: To maintain data accuracy and reliability by
preventing invalid data entry.

14. **What SQL constraints do you know?


- **Answer**: Common SQL constraints include:
- **PRIMARY KEY**
- **FOREIGN KEY**
- **UNIQUE**
- **NOT NULL**
- **CHECK**
- **Example**:
```sql
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(ID),
CHECK (Salary > 0)
);
```
16. **What is a Join?**
- **Answer**: A JOIN clause is used to combine rows from two or more
tables based on a related column between them.
- **Example**:
```sql
SELECT Employees.Name, Departments.Name
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.ID;
```
- This query retrieves employee names and their corresponding
department names.

17. **Types of Join**


- **Answer**: Common types of joins include:
- **INNER JOIN**: Returns only the matching rows from both tables.
- **LEFT JOIN (or LEFT OUTER JOIN)**: Returns all rows from the
left table, and the matched rows from the right table.
- **RIGHT JOIN (or RIGHT OUTER JOIN)**: Returns all rows from
the right table, and the matched rows from the left table.
- **FULL JOIN (or FULL OUTER JOIN)**: Returns rows when there
is a match in one of the tables.
- **Example**:
```sql
SELECT Employees.Name, Departments.Name
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID =
Departments.ID;
```
18. **What is a Primary Key?**
- **Answer**: A primary key is a column (or a set of columns) that
uniquely identifies each row in a table.
- **Example**:
```sql
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100)
);
```
- `ID` is the primary key.

19. **What is a Unique Key?**


- **Answer**: A unique key is a column (or a set of columns) that
ensures all values in the column are unique.
- **Example**:
```sql
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);
```
- `Email` is a unique key.

20. **What is a Foreign Key?**


- **Answer**: A foreign key is a column (or a set of columns) that
creates a relationship between two tables by referencing the primary key of
another table.
- **Example**:
```sql
CREATE TABLE Employees (
ID INT PRIMARY KEY,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(ID)
);
```
- `DepartmentID` is a foreign key.

21. **What is an Index?**


- **Answer**: An index is a database object that improves the speed of
data retrieval operations on a table at the cost of additional storage and
slower write operations.
- **Example**:
```sql
CREATE INDEX idx_employee_name ON Employees(Name);
```
- Creates an index on the `Name` column of the `Employees` table.

22. **What types of Indexes?**


- **Answer**: Common types of indexes include:
- **Single-column index**: An index on a single column.
- **Composite index**: An index on multiple columns.
- **Unique index**: Ensures all values in the indexed column(s) are
unique.
- **Full-text index**: For fast searching of text data.
- **Example**:
```sql
CREATE UNIQUE INDEX idx_email ON Employees(Email);
```

23. **What is a Schema?**


- **Answer**: A schema is a logical container for database objects like
tables, views, indexes, etc. It helps organize and manage the database
structure.
- **Example**: In PostgreSQL, you can have multiple schemas within a
single database, each containing its own set of tables.
```sql
CREATE SCHEMA hr;
CREATE TABLE hr.Employees (ID INT, Name VARCHAR(100));
```

24. **SQL Operators**


- **Answer**: SQL operators are symbols or keywords used to perform
operations on data.
- **Types**:
- **Arithmetic operators**: `+`, `-`, `*`, `/`
- **Comparison operators**: `=`, `>`, `<`, `>=`, `<=`, `<>`
- **Logical operators**: `AND`, `OR`, `NOT`
- **Example**:
```sql
SELECT * FROM Employees WHERE Salary > 50000 AND
Department = 'IT';
```

25. **What is an Alias?**


- **Answer**: An alias is a temporary name given to a table or a column
for the purpose of a specific SQL query.
- **Example**:
```sql
SELECT E.Name AS EmployeeName, D.Name AS DepartmentName
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.ID;
```

26. **What is a Clause?**


- **Answer**: A clause is a part of an SQL statement that specifies a
particular condition or operation.
- **Example**:
- **WHERE clause**: Filters records.
```sql
SELECT * FROM Employees WHERE Department = 'HR';
```

27. **How to sort records in a table?**


- **Answer**: You can sort records in a table using the `ORDER BY`
clause.
- **Example**:
```sql
SELECT * FROM Employees ORDER BY Salary DESC;
```
- This sorts the employees by salary in descending order.

28. **What are Entities?**


- **Answer**: Entities are objects or things in the real world that can be
distinctly identified and have attributes. In a database, entities often
correspond to tables.
- **Example**: An `Employee` is an entity with attributes like `Name`,
`ID`, and `Department`.

29. **What are Relationships?**


- **Answer**: Relationships define how entities (tables) relate to each
other. Common types include:
- **One-to-One**
- **One-to-Many**
- **Many-to-Many**
- **Example**: An `Employee` belongs to one `Department`, and a
`Department` can have many `Employees`.

30. **What is NULL?**


- **Answer**: NULL represents the absence of a value or an unknown
value in a column.
- **Example**:
```sql
SELECT * FROM Employees WHERE ManagerID IS NULL;
```
- This query finds employees who do not have a manager.

31. **What are functions and why use functions?**


- **Answer**: Functions are pre-defined operations that perform
calculations, manipulate data, or process strings and dates. They help
simplify complex queries and calculations.
- **Example**:
- **Aggregate functions**: `SUM`, `AVG`, `COUNT`
- **String functions**: `CONCAT`, `SUBSTRING`
- **Date functions**: `NOW`, `DATEADD`

32. **What types of SQL functions do you know?**


- **Answer**: Common types include:
- **Aggregate functions**: `SUM`, `COUNT`, `AVG`, `MIN`, `MAX`
- **Scalar functions**: `UCASE`, `LCASE`, `ROUND`, `NOW`
- **Example**:
```sql
SELECT COUNT(*) FROM Employees;
```

33. **What aggregate functions do you know?**


- **Answer**: Aggregate functions perform a calculation on a set of
values and return a single value.
- **Example**:
- `SUM`: Adds up all values.
```sql
SELECT SUM(Salary) FROM Employees;
```
- `AVG`: Calculates the average.
```sql
SELECT AVG(Salary) FROM Employees;
```

34. **What are case manipulation functions? Give some examples.**


- **Answer**: Case manipulation functions change the case of text.
- **Examples**:
- `UPPER`: Converts text to uppercase.
```sql
SELECT UPPER(Name) FROM Employees;
```
- `LOWER`: Converts text to lowercase.
```sql
SELECT LOWER(Name) FROM Employees;
```

35. **Character manipulation functions**


- **Answer**: Character manipulation functions alter and process
strings.
- **Examples**:
- `CONCAT`: Concatenates two or more strings.
```sql
SELECT CONCAT(FirstName, ' ', LastName) FROM Employees;
```
- `SUBSTRING`: Extracts a substring from a string.
```sql
SELECT SUBSTRING(Name, 1, 3) FROM Employees;
```

Feel free to ask for more details or examples on any specific topic!

You might also like