DEV Community

Cover image for 13 Basic Terms In SQL That Basically Ask in Interview.
Manoj Gayakwad
Manoj Gayakwad

Posted on

13 Basic Terms In SQL That Basically Ask in Interview.

1. Table
A table is a collection of related data organized into rows and columns. It’s like a spreadsheet where each row represents a data entry (record), and each column represents a field (attribute) of that record.
Example: A table of Employees might have columns like EmployeeID, Name, Age, Department, etc.

*2.Row *(also called a Record or Tuple)
A row represents a single, unique record in a table. Each row contains values for each of the table's columns.
Example: A row in an Employees table might contain the values 1, John Doe, 28, Marketing in the respective columns for EmployeeID, Name, Age, and Department.

3. Column (also called a Field or Attribute)
A column is a vertical set of data values in a table. Each column is dedicated to a specific type of data, such as EmployeeID, Name, Age, Salary, etc.
Example: The Name column in a Students table might contain values like Alice, Bob, Charlie.

4. Primary Key
A primary key is a unique identifier for each row in a table. It ensures that no two rows have the same value in this column. The primary key cannot contain NULL values.
Example: In a Users table, the UserID might be the primary key, ensuring that each user has a unique ID.
Key Characteristics:
Uniqueness: No two rows can have the same primary key value.
Not NULL: Every row must have a value in the primary key column.

5. Foreign Key
A foreign key is a column (or a set of columns) in one table that links to the primary key of another table. It establishes a relationship between the two tables.
Example: In an Orders table, the CustomerID column might be a foreign key that refers to the CustomerID primary key in a Customers table. This shows which customer placed each order.

Key Characteristics:
It creates a relationship between two tables.
A foreign key in the child table corresponds to a primary key in the parent table.
A foreign key can have NULL values, unlike a primary key.

6. Index
An index is a database object that improves the speed of data retrieval operations on a table. It’s like a table of contents for a book—it allows quick access to rows based on the values in one or more columns.
Example: You might create an index on the Email column of a Users table so that queries that search by email are faster.

7. NULL
NULL is a special marker used in databases to represent missing or undefined data. It’s different from an empty string or zero.
Example: If an employee doesn’t have a phone number, the PhoneNumber column for that employee’s row might contain NULL.

8. Relationship
A relationship is a connection between two tables. Relationships are established using foreign keys and can be:
One-to-One: One record in the first table is related to one record in the second table.
One-to-Many: One record in the first table can be related to many records in the second table.
Many-to-Many: Many records in the first table can be related to many records in the second table. This is typically implemented using a junction table.

Example: In an Employee table and a Departments table, an employee can belong to one department, but a department can have many employees—this is a one-to-many relationship.

9. Schema
A schema is the structure that defines the organization of data in a database. It includes tables, relationships, views, indexes, and other database objects. The schema defines how data is organized and how relationships between data are handled.
Example: A schema for a school database might include tables like Students, Courses, and Enrollments with relationships between them.

10. View
A view is a virtual table that provides a way to look at data from one or more tables. It’s a stored query that can be treated like a table. Views do not store data themselves but display data from the underlying tables.
Example: A view could display a list of employees with their department names by joining the Employees table and the Departments table.

11. Constraint
A constraint is a rule applied to a column or table to ensure data integrity. Common constraints include:
NOT NULL: Ensures a column cannot have NULL values.
UNIQUE: Ensures all values in a column are unique.
CHECK: Ensures that values in a column meet a specified condition.
DEFAULT: Sets a default value for a column when no value is provided.
FOREIGN KEY: Ensures that a column’s value matches a primary key in another table.
Example: A constraint could ensure that the Age column only allows values greater than 18.

12. Join
A join is an SQL operation used to combine rows from two or more tables based on a related column. Common types of joins include:
INNER JOIN: Returns records that have matching values in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and the matched records from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table.
FULL JOIN (or FULL OUTER JOIN): Returns records when there is a match in either table.
Example: If you have a Customers table and an Orders table, you can use a join to get all orders for a specific customer.

13. Transaction
A transaction is a unit of work that is performed in a database. Transactions ensure that database operations are completed in a reliable and consistent way, following the ACID properties (Atomicity, Consistency, Isolation, Durability).
Example: In a banking system, transferring money from one account to another involves multiple steps, and a transaction ensures that all steps either complete successfully or none at all (i.e., rollback if there's an error).

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay