SQL Basics and Key Concepts

1. What is SQL? Explain its use in databases.

SQL (Structured Query Language) is a standard language used to interact with relational databases. It allows users to perform operations like creating, reading, updating, and deleting data stored in database tables. SQL is essential for:

  • Data Retrieval: Querying and retrieving specific data from databases.
  • Data Management: Adding, modifying, and deleting records.
  • Database Structure: Creating and altering database schemas and tables.
  • Security: Controlling user access and permissions.

2. What are the different types of SQL commands?

SQL commands are categorized into the following types:

  1. DDL (Data Definition Language)

    • Used to define and manage database structure.
    • Common commands: CREATE, ALTER, DROP, TRUNCATE

  2. DML (Data Manipulation Language)

    • Used to manipulate data in tables.
    • Common commands: INSERT, UPDATE, DELETE, SELECT

  3. DCL (Data Control Language)

    • Used to control access to data.
    • Common commands: GRANT, REVOKE

  4. TCL (Transaction Control Language)

    • Used to manage database transactions.
    • Common commands: COMMIT, ROLLBACK, SAVEPOINT

3. What is the difference between DELETE and TRUNCATE?

Feature DELETE TRUNCATE
Operation Type DML DDL
Removes Data Specific rows with conditions All rows in a table
Can Be Rolled Back Yes (if inside a transaction) No
Triggers Execution Yes No
Resets Identity No Yes

4. What are primary keys and foreign keys?

  • Primary Key:

    • Uniquely identifies each record in a table.
    • Cannot contain `` values.
    • Example: 
      CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);

  • Foreign Key:

    • A column in one table that references the primary key of another table.
    • Ensures referential integrity.
    • Example: 
      CREATE TABLE Enrollments (
  EnrollmentID INT PRIMARY KEY,
  StudentID INT,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

5. Explain the difference between WHERE and HAVING clauses.

Feature WHERE HAVING
Applies To Filters rows before grouping Filters groups after grouping
Used With SELECT, UPDATE, DELETE SELECT with GROUP BY
Example 
SELECT * FROM Students
WHERE Age > 18;

|

SELECT Department, AVG(Salary)
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;

6. What is a join? Explain different types of joins.

A JOIN is used to combine rows from two or more tables based on a related column.

  1. INNER JOIN

    • Returns only matching rows from both tables.
    • Example: 
      SELECT A.Name, B.Course
FROM Students A
INNER JOIN Enrollments B ON A.StudentID = B.StudentID;

  2. LEFT JOIN

    • Returns all rows from the left table and matching rows from the right table. Missing matches result in NULL.
    • Example: 
      SELECT A.Name, B.Course
FROM Students A
LEFT JOIN Enrollments B ON A.StudentID = B.StudentID;

  3. RIGHT JOIN

    • Returns all rows from the right table and matching rows from the left table.
    • Example: 
      SELECT A.Name, B.Course
FROM Students A
RIGHT JOIN Enrollments B ON A.StudentID = B.StudentID;

  4. FULL JOIN

    • Returns all rows when there is a match in either table. Non-matching rows will have NULL values.
    • Example: 
      SELECT A.Name, B.Course
FROM Students A
FULL JOIN Enrollments B ON A.StudentID = B.StudentID;

7. What are constraints? Name a few types of constraints in SQL.

Constraints are rules enforced on table columns to maintain data integrity. Types include:

  • NOT NULL: Ensures a column cannot have NULL values.
  • UNIQUE: Ensures all values in a column are unique.
  • PRIMARY KEY: Combines NOT NULL and UNIQUE.
  • FOREIGN KEY: Ensures referential integrity.
  • CHECK: Ensures values in a column meet a specific condition.
  • DEFAULT: Sets a default value for a column.

Example:

CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  Price DECIMAL(10, 2) CHECK (Price > 0)
);

8. How is DISTINCT used in a query? Provide an example.

DISTINCT removes duplicate values from the result set.

  • Example: 
    SELECT DISTINCT Department
FROM Employees;

This query retrieves unique department names.

9. Explain the purpose of the LIKE operator. How do wildcards work?

The LIKE operator is used to search for patterns in a column. Wildcards:

  • %: Matches zero or more characters.
  • _: Matches a single character.

Example:

SELECT Name
FROM Students
WHERE Name LIKE 'A%'; -- Names starting with 'A'

SELECT Name
FROM Students
WHERE Name LIKE '_a%'; -- Names where the second letter is 'a'

10. What is the difference between CHAR and VARCHAR data types?

Feature CHAR VARCHAR
Storage Fixed length Variable length
Performance Faster for fixed-size values More efficient for varying sizes
Padding Padded with spaces to defined length No padding
Example 
CHAR(10) -- Always stores 10 characters

|

VARCHAR(10) -- Stores up to 10 characters