Answer:
SQL (Structured Query Language) is a standard programming language used to create, manage, manipulate, and retrieve data from relational databases. It is widely used for performing operations such as querying, inserting, updating, and deleting data.
Answer:
A database is an organized collection of structured data that is stored electronically. It allows users to efficiently store, retrieve, update, and manage information.
Answer:
SQL statements are categorized into the following types:
Answer:
A Primary Key is a column (or combination of columns) that uniquely identifies each record in a table. It cannot contain NULL values and duplicate values are not allowed.
Answer:
A Foreign Key is a column in one table that references the Primary Key of another table. It establishes a relationship between two tables and maintains referential integrity.
Answer:
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It divides large tables into smaller related tables using relationships.
Common Normal Forms:
Answer:
| SQL | MySQL |
|---|---|
| SQL is a language used to interact with databases. | MySQL is an open-source Relational Database Management System (RDBMS). |
| It defines database operations. | It uses SQL to manage databases. |
| SQL is a standard language. | MySQL is software developed by Oracle. |
Answer:
A JOIN is used to retrieve data from two or more related tables based on a common column.
Answer:
Answer:
| Primary Key | Unique Key |
|---|---|
| Only one Primary Key per table. | Multiple Unique Keys are allowed. |
| Cannot contain NULL values. | Can contain one NULL value (DBMS dependent). |
| Uniquely identifies each row. | Ensures uniqueness but is not the main identifier. |
Answer:
A Subquery is a query written inside another SQL query. It is used to retrieve intermediate results that are used by the outer query.
Answer:
| DELETE | TRUNCATE |
|---|---|
| Removes selected rows. | Removes all rows. |
| WHERE clause can be used. | WHERE clause cannot be used. |
| Can be rolled back (if transaction supported). | Usually cannot be rolled back after commit. |
| Slower. | Faster because it deallocates pages. |
Answer:
A View is a virtual table created from the result of an SQL query. It does not store data itself but displays data from one or more tables.
Answer:
A Stored Procedure is a precompiled collection of SQL statements stored in the database. It can be executed repeatedly and helps improve performance and code reusability.
Answer:
| UNION | UNION ALL |
|---|---|
| Removes duplicate records. | Includes duplicate records. |
| Slightly slower. | Faster because duplicates are not removed. |
Answer:
A Transaction is a sequence of SQL operations executed as a single unit of work. It ensures that either all operations are completed successfully or none are applied.
Transaction Commands:
Answer:
ACID properties ensure reliable database transactions.
Answer:
| CHAR | VARCHAR |
|---|---|
| Fixed-length data type. | Variable-length data type. |
| Faster retrieval. | Saves storage space. |
| Wastes space if data is shorter. | Uses only required storage. |
Answer:
| Clustered Index | Non-Clustered Index |
|---|---|
| Stores data physically in sorted order. | Stores pointers to data. |
| Only one per table. | Multiple allowed per table. |
| Faster for range queries. | Faster for specific lookups. |
Answer:
Constraints are rules applied to table columns to ensure data integrity.
Common constraints include:
Answer:
The HAVING clause filters grouped data after the GROUP BY clause. Unlike WHERE, it works with aggregate functions.
Answer:
| Stored Procedure | Function |
|---|---|
| Can perform database operations. | Mainly returns a value. |
| May return multiple values. | Returns a single value. |
| Can contain transactions. | Cannot perform transaction control. |
Answer:
Examples:
Answer:
Both return rows from the first query that are not present in the second query.
Answer:
| UNION | JOIN |
|---|---|
| Combines rows from multiple SELECT statements. | Combines columns from related tables. |
| Number of columns must match. | Uses relationships between tables. |
Answer:
| Database | Schema |
|---|---|
| Collection of related data. | Logical container for database objects. |
| Can contain multiple schemas. | Contains tables, views, procedures, etc. |
Answer:
| EXISTS | IN |
|---|---|
| Checks if subquery returns rows. | Checks if a value exists in a list. |
| Faster for large datasets. | Better for small datasets. |
Answer:
A Self Join joins a table with itself using aliases. It is commonly used to compare rows within the same table.
Answer:
The GROUP BY clause groups rows with the same values into summary rows. It is commonly used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN().
Answer:
| WHERE | HAVING |
|---|---|
| Filters rows before grouping. | Filters groups after grouping. |
| Cannot use aggregate functions. | Can use aggregate functions. |
Answer:
Answer:
| Temporary Table | Table Variable |
|---|---|
| Stored in TempDB. | Stored mainly in memory. |
| Better for large datasets. | Suitable for small datasets. |
| Can have indexes. | Limited indexing support. |
Answer:
| FULL OUTER JOIN | CROSS JOIN |
|---|---|
| Returns matched and unmatched rows. | Returns Cartesian product of both tables. |
| Uses matching condition. | No matching condition required. |
Answer:
The CASE statement performs conditional logic within SQL queries. It works like an IF-ELSE statement.
Answer:
| Candidate Key | Composite Key |
|---|---|
| A single or minimal set of columns that uniquely identifies rows. | Combination of two or more columns used as a unique key. |
Answer:
| LIKE | IN |
|---|---|
Used for pattern matching using % and _. | Used to compare values against a list of values. |
| Best for searching text patterns. | Best for exact value matching. |
