1. SQL Basics and Fundamentals (25 Questions)
Q1. What is SQL and what are its main uses?
SQL stands for Structured Query Language. It is a standard language used to manage relational databases. Its main uses are creating tables, inserting, updating, retrieving, and deleting data, and controlling user access.
Q2. What is the difference between a DBMS and an RDBMS?
A DBMS is software used to create and manage databases. An RDBMS is a type of DBMS that stores data in tables with rows and columns and supports relationships between tables using keys. RDBMS follows the relational model and uses SQL. Most modern databases like MySQL and PostgreSQL are RDBMS.
Q3. Explain what a relational database is with a simple example.
A relational database stores data in tables that are linked using common fields called keys.
For example, an Employees table (with emp_id, name, dept_id) is linked to a Departments table (with dept_id, dept_name) through the dept_id column.
Q4. What is a primary key and why is it important?
A primary key is a column (or set of columns) that uniquely identifies each record in a table. It cannot contain NULL values and must be unique. It is important because it ensures data integrity and allows fast retrieval of specific rows. For example, emp_id in an Employees table can be the primary key.
Q5. What is a foreign key and how does it work?
A foreign key is a column in one table that refers to the primary key in another table. It creates a relationship between the tables and maintains referential integrity. For example, dept_id in the Employees table is a foreign key referencing the dept_id primary key in the Departments table.
Q6. Write a basic SQL query to select all columns from a table named Employees.
//SQL
SELECT * FROM Employees;
This query retrieves every row and every column from the Employees table. It is the simplest way to view all data when you are starting with SQL.
Q7. How do you use the WHERE clause to filter data? Give an example.
The WHERE clause filters rows based on a condition.
For example:
SELECT name, salary FROM Employees WHERE salary > 50000;
This returns only the name and salary of employees who earn more than 50,000. The condition can use operators like =, >, <, !=, etc.
Q8. What is the purpose of the ORDER BY clause?
ORDER BY sorts the result set by one or more columns in ascending (ASC) or descending (DESC) order.
For example:
SELECT name, hire_date FROM Employees ORDER BY hire_date DESC;
This lists employees from the most recently hired to the oldest.
Q9. Explain the LIMIT or TOP clause with an example.
LIMIT (in MySQL/PostgreSQL) or TOP (in SQL Server) restricts the number of rows returned.
For example: SELECT * FROM Employees LIMIT 5;
This returns only the first 5 rows from the Employees table, useful for pagination or quick previews.
Q10. What is the difference between DELETE and TRUNCATE?
DELETE removes specific rows based on a condition and can be rolled back. TRUNCATE removes all rows from the table at once, is faster, and usually cannot be rolled back.
Example of DELETE:
DELETE FROM Employees WHERE emp_id = 101;
Q11. How do you use the LIKE operator for pattern matching?
LIKE is used with wildcards (% for any characters, _ for single character) to search for patterns.
For example:
SELECT name FROM Employees WHERE name LIKE ‘J%’;
This finds all employees whose names start with ‘J’.
Q12. What are aggregate functions? Name any three with a short example.
Aggregate functions perform calculations on a set of values and return a single value.
Examples: COUNT, SUM, AVG.
//SQL
SELECT COUNT(*) AS total_employees, AVG(salary) AS avg_salary FROM Employees;
Q13. What is the difference between COUNT(*) and COUNT(column_name)?
COUNT(*) counts all rows including those with NULL values. COUNT(column_name) counts only non-NULL values in that column.
For example, COUNT(salary) ignores employees without a salary.
Q14. Explain the concept of NULL in SQL.
NULL represents missing or unknown data. It is not the same as 0 or an empty string. You cannot use = to check for NULL; instead use IS NULL or IS NOT NULL.
For example:
SELECT name FROM Employees WHERE salary IS NULL;
Q15. How do you rename a column in the result set using AS?
The AS keyword gives an alias to a column or table for better readability.
For example:
SELECT name AS employee_name, salary AS monthly_pay FROM Employees;
Q16. What is a candidate key?
A candidate key is any column or set of columns that could uniquely identify each row. Only one is chosen as the primary key; the others become alternate keys.
Q17. What is normalization and why is it used?
Normalization is the process of organizing data to reduce redundancy and improve data integrity by dividing large tables into smaller related tables. It follows rules called normal forms (1NF, 2NF, 3NF, etc.).
Q18. Write a query to find unique department names from an Employees table.
SELECT DISTINCT dept_name FROM Employees;
DISTINCT removes duplicate rows from the result.
Q19. What is the difference between UNION and UNION ALL?
UNION combines results from two queries and removes duplicates. UNION ALL keeps all rows including duplicates, making it faster.
Q20. What is a schema in a database?
A schema is a logical container that holds database objects like tables, views, indexes, and procedures. It helps organize and secure data within a database.
Q21. How do you comment in SQL?
Single-line comments use — (two hyphens).
Multi-line comments use /* */.
For example: — This is a single line comment
/* This is a
multi-line comment */
Q22. Explain the SELECT statement execution order.
The logical order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Understanding this helps write correct queries.
Q23. How do you combine data from two tables without a join (Cartesian product)?
By using CROSS JOIN or simply listing tables with a comma.
For example: SELECT * FROM Employees, Departments;
This returns every row from Employees paired with every row from Departments.
Q24. What is the purpose of the DISTINCT keyword?
DISTINCT eliminates duplicate rows in the result set. It is applied after the SELECT clause processes all columns listed.
Q25. Write a simple query to count how many employees were hired after January 1, 2023.
SELECT COUNT(*) AS new_hires FROM Employees
WHERE hire_date > ‘2023-01-01’;
This uses a date comparison in the WHERE clause to filter and count records.
