Concepts

Defining a Database

  • Question: Explain what a database is in your own words. Highlight the key characteristics that differentiate it from a simple collection of files.
  • Answer: A database is a structured collection of data, meaning it’s organized in a specific way to ensure efficient storage and retrieval. Unlike a simple collection of files, a database is managed by a Database Management System (DBMS) which provides tools for managing, accessing, and ensuring the integrity of the data. Key characteristics include organization for efficiency, persistence (data remains after power off), controlled access, and support for data integrity and consistency.

Purpose of Databases

  • Question: Describe three key purposes of using databases in modern systems. For each purpose, briefly explain why it is important.
  • Answer:
    1. Organized and Persistent Storage: Databases provide a structured and lasting way to store data. This is important because it allows for efficient management and retrieval of information over time, even when systems are restarted or powered down.
    2. Efficient Data Retrieval and Manipulation: Databases are designed for quick access and modification of data. This is crucial for applications that need to rapidly find, update, or analyze information, ensuring responsiveness and efficiency.
    3. Maintaining Data Integrity and Consistency: Databases enforce rules and constraints to ensure data accuracy and reliability. This is vital for trust in the data and for applications that depend on correct information for their operations.

Data vs. Information

  • Question: What is the difference between “data” and “information”? Give a brief example to illustrate your explanation.

  • Answer: Data refers to raw, unorganized facts and figures. It’s like raw material without context or meaning. Information, on the other hand, is processed, organized, and structured data that provides context and meaning, making it useful for decision-making or understanding.

    Example: The number “25” is data. “The age of the student is 25” is information because it provides context and meaning to the number.

Schema and Instance

  • Question: Explain the difference between a database “schema” and a database “instance.” Use an analogy to help illustrate the difference.
  • Answer: A database schema is the blueprint or logical structure of the database. It defines how the data is organized, including tables, columns, data types, and relationships. It’s like the design of a house. A database instance is the actual data stored in the database at a particular moment in time. It is a snapshot of the database content that conforms to the schema. Using the house analogy, the instance is the house with furniture, people, and everything inside it at a specific time, built according to the house design (schema).

Metadata

  • Question: What is “metadata” in the context of databases, and where is it typically stored? Provide two examples of metadata.

  • Answer: Metadata is “data about data.” It is descriptive information about the database structure itself. It’s stored in the database catalog or data dictionary.

    Examples: 1. Table names within a database. 2. Data types of columns in a table (e.g., INTEGER, VARCHAR).

Database Administrator (DBA) Responsibilities

  • Question: Describe three key responsibilities of a Database Administrator (DBA). Explain why each responsibility is important for the successful operation of a database system.
  • Answer:
    1. Ensuring Database Availability and Reliability: DBAs are responsible for making sure the database is operational and accessible when needed, and that it functions correctly. This is crucial because users and applications rely on the database to be consistently available to perform their tasks.
    2. Maintaining Data Integrity and Security: DBAs protect data from corruption, unauthorized access, and breaches. This is vital for maintaining trust in the data and for complying with security and privacy regulations.
    3. Optimizing Database Performance: DBAs tune and monitor the database to ensure it runs efficiently and responds quickly to queries. This is important for user satisfaction and for the overall performance of applications that rely on the database.

Database Classification by Data Model

  • Question: Databases can be classified based on their data model. List and briefly describe three different data models discussed in the lecture. For each model, provide an example of a database system that uses it.
  • Answer:
    1. Relational Model: Organizes data in tables with rows and columns, focusing on relationships between tables. Example: MySQL, PostgreSQL, Oracle.
    2. Object-Oriented Model: Integrates object-oriented programming concepts directly into the database, allowing storage of complex objects. Example: ObjectDB, GemFire.
    3. NoSQL Databases: A broad category of databases designed for flexibility and scalability, often diverging from the relational model. Includes various types like Document, Key-value, Column-family, and Graph databases. Examples: MongoDB (Document), Redis (Key-value), Cassandra (Column-family), Neo4j (Graph).

Relational Model Terminology

  • Question: Define the following terms in the context of the relational model and give a brief example for each using a “Products” table with attributes (ProductID, ProductName, Price, Category):
    • Relation
    • Attribute
    • Tuple
    • Domain
  • Answer:
    • Relation: A relation is a table with rows and columns. In our “Products” example, the entire “Products” table itself is a relation.
    • Attribute: An attribute is a named column of a relation. Examples in the “Products” table are: ProductID, ProductName, Price, and Category.
    • Tuple: A tuple is a row in a relation, representing a single record. An example tuple in the “Products” table would be a single product entry, like (1, ‘Laptop’, 1200, ‘Electronics’).
    • Domain: A domain is the set of permissible values for an attribute. For the “Price” attribute in “Products,” the domain might be positive decimal numbers. For “Category,” the domain could be a set of strings like {‘Electronics’, ‘Books’, ‘Clothing’, …}.

Degree and Cardinality

  • Question: Consider a “Sales” relation with attributes (SaleID, ProductID, CustomerID, SaleDate, Quantity, Price). What is the degree and what does the cardinality represent for this “Sales” relation?
  • Answer:
    • Degree: The degree of the “Sales” relation is 6 because it has six attributes: SaleID, ProductID, CustomerID, SaleDate, Quantity, and Price.
    • Cardinality: The cardinality of the “Sales” relation represents the number of sales records currently stored in the table. It is the number of tuples (rows) in the “Sales” relation at any given time.

Properties of Relations

  • Question: Describe two key properties that differentiate relations in the relational model from simple tables. Explain why each property is important for data management.
  • Answer:
    1. No Duplicate Tuples: Each tuple (row) in a relation must be unique. This is important for data integrity and consistency. It ensures that each record represents a distinct entity or event, preventing redundancy and ambiguity in the data.
    2. Atomic Attribute Values: Each attribute value must be atomic (indivisible). This property simplifies data manipulation and querying. It ensures that each value in a column is a single, basic unit, making it easier to perform operations like comparisons and aggregations and maintaining data consistency and simplifying queries.

Relational Algebra vs. Relational Calculus

  • Question: Explain the fundamental difference between Relational Algebra and Relational Calculus in terms of how they specify queries. Which one is considered procedural and which one is declarative?
  • Answer: Relational Algebra is a procedural query language. It specifies how to retrieve data by outlining a sequence of operations to be performed on relations. Relational Calculus is a declarative query language. It specifies what data to retrieve by describing the properties that the data should satisfy, without detailing the steps to retrieve it.

Database Lifecycle

  • Question: Outline the stages of the database lifecycle in a Waterfall model. Briefly describe the primary activity in each stage.
  • Answer:
    • Planning: Initial stage defining project scope, goals, and feasibility.
    • Requirements Gathering: Collecting and documenting user needs and data requirements through interviews, questionnaires, etc.
    • Conceptual Design: Creating a high-level, abstract model (like an ER diagram) of the database structure.
    • Logical Design: Translating the conceptual model into a specific database schema, often using the relational model (defining tables, columns, data types).
    • Physical Design: Determining physical storage structures, indexing strategies, and access paths for optimal performance.
    • Implementation: Building the database using a DBMS, creating the database objects and loading initial data.
    • Testing: Verifying the database functionality, performance, and data integrity.
    • Deployment: Making the database operational and accessible to users.
    • Maintenance and Evolution: Ongoing monitoring, performance tuning, updates, and adapting to changing requirements.

DBA Skills

  • Question: Briefly describe three key skills that are essential for a Database Administrator (DBA) to be effective in their role.
  • Answer:
    1. Technical Skills: DBAs require strong technical skills in Database Management Systems (DBMS), SQL, operating systems, and related technologies. These technical skills are fundamental for tasks like database installation, configuration, performance tuning, security implementation, and troubleshooting.
    2. Problem-Solving and Analytical Abilities: DBAs frequently encounter complex technical issues and performance bottlenecks. Strong problem-solving and analytical skills are essential to diagnose problems, identify root causes, and develop effective solutions to ensure database stability and optimal performance.
    3. Communication and Collaboration Skills: DBAs need to communicate effectively with various stakeholders, including developers, users, and management. They must be able to explain technical concepts clearly, gather requirements, and collaborate with teams to ensure the database meets organizational needs.

Relational Algebra vs. Relational Calculus

  • Question: Briefly explain the key difference between Relational Algebra and Relational Calculus in the context of querying databases. Which one is considered procedural and which is declarative?

  • Answer: The key difference lies in how they specify data retrieval. Relational Algebra is procedural, meaning it specifies the sequence of operations needed to retrieve the desired data. It describes how to get the result. Relational Calculus, on the other hand, is declarative, meaning it specifies what data to retrieve without detailing the exact steps. It describes what the result should look like, leaving the DBMS to figure out the most efficient way to retrieve it.

    • Relational Algebra: Procedural (specifies how)
    • Relational Calculus: Declarative (specifies what)

Indexes in Databases

  • Question: Describe the purpose of using indexes in databases. Explain the difference between a primary index and a secondary index.
  • Answer: Indexes in databases are used to improve the efficiency of data retrieval. They are separate structures that map data values to their physical locations, allowing the database to quickly locate records without scanning the entire table.
    • Primary Index: An index based on the primary key of a table. It is used to enforce uniqueness and provide fast access to records based on the primary key value. There is typically only one primary index per table.
    • Secondary Index: An index created on non-primary key attributes (columns). Secondary indexes provide faster access to records based on these attributes, enabling efficient querying on different criteria beyond the primary key. A table can have multiple secondary indexes.

Foreign Key Actions: ON DELETE RESTRICT

  • Question: Consider two tables, Customers and Orders, where Orders.CustomerID is a foreign key referencing Customers.CustomerID with the action ON DELETE RESTRICT. Explain what happens and why if you attempt to delete a customer record from the Customers table that has associated orders in the Orders table.
  • Answer: If you attempt to delete a customer record from the Customers table that has associated orders in the Orders table, and the foreign key Orders.CustomerID is set to ON DELETE RESTRICT, the deletion will be prevented (or restricted). The database system will generate an error and the customer record will not be deleted. This is because RESTRICT is designed to maintain referential integrity by ensuring that a primary key record is not deleted if there are still related foreign key records referencing it. Deleting the customer would leave orders pointing to a non-existent customer, violating referential integrity.

Database Security: CIA Triad

  • Question: Describe the CIA Triad in the context of database security. For each component (Confidentiality, Integrity, Availability), explain its meaning and importance for securing a database system.
  • Answer: The CIA Triad is a fundamental model that guides information security policies and practices, including database security. It stands for:
    • Confidentiality: Ensuring that data is accessible only to authorized users and preventing unauthorized disclosure. Importance: Protects sensitive information from being leaked or exposed to those who should not have access, maintaining privacy and trust.
    • Integrity: Maintaining the accuracy and completeness of data, and preventing unauthorized modification or corruption. Importance: Ensures that data is reliable and trustworthy for decision-making and application functionality. Data corruption can lead to incorrect operations and loss of confidence in the system.
    • Availability: Ensuring that authorized users have reliable and timely access to data and resources when needed. Importance: Guarantees that the database system is operational and accessible for legitimate users to perform their tasks. Loss of availability can disrupt business operations and services.

Authentication and Authorization

  • Question: Explain the difference between authentication and authorization in database security.
  • Answer:
    • Authentication: The process of verifying the identity of a user attempting to access the database system. It answers the question “Who are you?”. Common methods include username/password and multi-factor authentication.
    • Authorization: The process of determining what an authenticated user is permitted to do within the database system. It answers the question “What are you allowed to do?”. Authorization is typically managed using privileges and roles, defining what actions a user can perform on specific database objects.

Database Normalization

  • Question: Define database normalization. Explain its primary goal and briefly describe why it is an important step in database design.
  • Answer: Database normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity. The primary goal is to structure relations in a way that minimizes data anomalies (insertion, update, and deletion anomalies). It is important because it saves storage space, reduces data inconsistencies, and makes databases more efficient and maintainable in the long run.

Data Anomalies

  • Question: Describe the three types of data anomalies (insertion, update, and deletion anomalies) that can occur in unnormalized databases. Provide a brief example for each anomaly type based on the ‘StudentCourses’ table example from the lecture:

StudentCourses Table:

StudentID StudentName CourseID CourseName CourseCredits
101 Alice CS101 Intro to CS 3
101 Alice MA101 Calculus I 4
102 Bob CS101 Intro to CS 3
  • Answer:
    • Insertion Anomaly: Occurs when you cannot insert data about one entity without including data about another entity. Example: In the ‘StudentCourses’ table, you cannot add a new course (e.g., ‘Database Systems’) without enrolling a student in it.
    • Update Anomaly: Occurs when updating a single piece of information requires updating multiple rows. Example: If the ‘CourseCredits’ for ‘Intro to CS’ changes from 3 to 4, you must update every row where ‘CourseName’ is ‘Intro to CS’.
    • Deletion Anomaly: Occurs when deleting data about one entity unintentionally removes data about another entity. Example: If you delete the last student enrolled in ‘MA101’ (Calculus I), you might lose the information about the ‘MA101’ course itself (CourseName and CourseCredits) if it’s only stored in this table.

Functional Dependency

  • Question: Explain the concept of functional dependency. In the context of functional dependency A -> B, identify and define the terms “determinant” and “dependent”. Give an example of a functional dependency that might exist in a University database, different from the examples given in the lecture.

  • Answer: A functional dependency exists when the value of one attribute (or set of attributes), the determinant (A), uniquely determines the value of another attribute (or set of attributes), the dependent (B). In A -> B, A is the determinant, the attribute that determines the value, and B is the dependent, the attribute whose value is determined by A.

    Example of Functional Dependency in a University Database: CourseCode -> CourseTitle. The ‘CourseCode’ (e.g., ‘CS201’) uniquely determines the ‘CourseTitle’ (e.g., ‘Data Structures’). Here, ‘CourseCode’ is the determinant and ‘CourseTitle’ is the dependent.

What is SQL

  • Question: Explain what SQL stands for and briefly describe its primary purpose in the context of databases.
  • Answer: SQL stands for Structured Query Language. Its primary purpose is to be the standard language for interacting with Relational Database Management Systems (RDBMS). It allows users to create, modify, query, and manage data stored in relational databases.

Core Functions of SQL

  • Question: SQL is categorized into four core functions: DDL, DML, DCL, and DQL. Briefly define each of these and state their main purpose.
  • Answer:
    • DDL (Data Definition Language): Used to define and modify the database structure. It includes commands for creating, altering, and dropping database objects like tables.
    • DML (Data Manipulation Language): Used to manipulate the data within the database. It includes commands for inserting, updating, deleting, and retrieving data.
    • DCL (Data Control Language): Used to control access and permissions within the database. It includes commands for granting and revoking privileges to users.
    • DQL (Data Query Language): Focused on the retrieval of data from the database. The primary command is SELECT, used to query and fetch data based on specified criteria.

Interactive vs. Embedded SQL

  • Question: What is the difference between Interactive SQL and Embedded SQL? Give an example scenario where each would be typically used.
  • Answer:
    • Interactive SQL: SQL statements are executed directly through a database client (like a command-line tool or GUI). It’s used for ad-hoc queries, database administration tasks, and data exploration.
    • Example Scenario: A database administrator using a command-line tool to check the current number of records in a table.
    • Embedded SQL: SQL statements are integrated within the source code of an application (e.g., Java, Python). The application code executes these SQL queries to interact with the database. It is used for building database-driven applications and dynamic data retrieval.
    • Example Scenario: A web application written in Python that retrieves product information from a database to display on a webpage.

DML Statements

  • Question: Briefly explain the purpose of Data Manipulation Language (DML) in SQL. List and describe the three fundamental DML statements discussed in the lecture, providing a simple use case for each.
  • Answer: DML (Data Manipulation Language) in SQL is used to modify data within a database. The three fundamental DML statements are:
    1. INSERT: Used to add new rows of data into a table. Use Case: Adding a new student record to a Students table.
    2. UPDATE: Used to modify existing rows in a table. Use Case: Changing a student’s major in the Students table.
    3. DELETE: Used to remove rows from a table. Use Case: Removing a student record from the Students table if they withdraw from the university.

JOIN Operations: INNER JOIN vs. LEFT JOIN

  • Question: Explain the key difference between INNER JOIN and LEFT JOIN in SQL.
  • Answer:
    • INNER JOIN returns only the rows where there is a match in both tables based on the join condition. If a row in either table does not have a corresponding match in the other, it is excluded from the result.
    • LEFT JOIN returns all rows from the left table (the table listed first in the JOIN clause) and the matching rows from the right table. If there is no match in the right table for a row in the left table, the columns from the right table will contain NULL values for that row.

Views

  • Question: Explain what a database “view” is and describe two key purposes of using views in a database system.
  • Answer: A database view is a virtual table based on the result-set of a SQL SELECT statement. It is a stored query and does not store data itself. Two key purposes of using views are:
    1. Simplification: Views can hide complex queries (e.g., involving multiple joins) and present data in a simpler, more understandable format to users. Users can query the view as if it were a regular table, without needing to know the underlying complexity.
    2. Security: Views can restrict access to specific columns or rows of underlying tables. By granting users access to a view instead of the base tables, administrators can control what data users can see and manipulate, enhancing data security and privacy.
Design

Many-to-Many Relationship

  • Question: A university wants to design a database to track students and courses. Students can enroll in multiple courses, and each course can have multiple students.

    a) What type of relationship exists between Students and Courses?

    b) Explain why a junction table is needed to represent this relationship in a relational database.

    c) Design the schema for the “Students”, “Courses”, and the junction table, including primary and foreign keys for each table.

  • Answer:

    a) The relationship between Students and Courses is a many-to-many relationship.

    b) A junction table is needed because many-to-many relationships cannot be directly represented by simply adding a foreign key to one of the tables. We need a separate table to link students and courses because a student can enroll in multiple courses, and a course can have multiple students. Without a junction table, we would have to either repeat student data for each course or course data for each student, leading to redundancy and violating relational principles.

    c) Schema Design:

    • Students Table:
      • StudentID (INT, Primary Key)
      • Name (VARCHAR)
      • Major (VARCHAR)
    • Courses Table:
      • CourseID (VARCHAR, Primary Key)
      • CourseName (VARCHAR)
      • Credits (INT)
    • Enrollments (Junction Table):
      • StudentID (INT, Foreign Key referencing Students.StudentID, Part of Primary Key)
      • CourseID (VARCHAR, Foreign Key referencing Courses.CourseID, Part of Primary Key)
      • EnrollmentDate (DATE)
      • Primary Key of Enrollments: {StudentID, CourseID} (composite primary key)

Library Database

  • Question: Design a relational database schema for a library system. The library needs to keep track of books, borrowers, and borrowing history. Consider the following requirements:
    • Each book has a unique ISBN, title, author, and publication year.
    • Each borrower has a unique BorrowerID, name, and address.
    • A borrower can borrow multiple books, and a book can be borrowed by multiple borrowers over time.
    • The system needs to record the date when a book is borrowed and the date when it is returned.

    Sketch the table schemas, including attributes, primary keys, foreign keys, and the relationships between the tables.

  • Answer:
    • Books Table:
      • ISBN (VARCHAR, Primary Key)
      • Title (VARCHAR)
      • Author (VARCHAR)
      • PublicationYear (INT)
    • Borrowers Table:
      • BorrowerID (INT, Primary Key)
      • Name (VARCHAR)
      • Address (VARCHAR)
    • Loans (Junction Table for Borrowing History):
      • LoanID (INT, Primary Key - to uniquely identify each loan record)
      • ISBN (VARCHAR, Foreign Key referencing Books.ISBN)
      • BorrowerID (INT, Foreign Key referencing Borrowers.BorrowerID)
      • BorrowDate (DATE)
      • ReturnDate (DATE, can be NULL if not returned yet)

Normalizing to 1NF

  • Question: Consider the following ‘Books’ table which is NOT in 1NF because the ‘Authors’ column contains multiple values:

Books Table (Not in 1NF):

BookID Title Authors
B1 Database Systems John Smith, Jane Doe
B2 Data Mining Peter Jones
B3 Web Development John Smith, David Lee

Normalize this ‘Books’ table to First Normal Form (1NF). Show the resulting table(s). You can choose either Option 1 (repeating rows) or Option 2 (creating a separate table) as discussed in the lecture, but clearly indicate which option you are using.

  • Answer: Option 1: Repeating Rows Books Table (in 1NF - Option 1):
BookID Title Author
B1 Database Systems John Smith
B1 Database Systems Jane Doe
B2 Data Mining Peter Jones
B3 Web Development John Smith
B3 Web Development David Lee

Option 2: Separate Table Books Table (in 1NF - Option 2):

BookID Title
B1 Database Systems
B2 Data Mining
B3 Web Development

BookAuthors Table (in 1NF - Option 2):

BookID AuthorName
B1 John Smith
B1 Jane Doe
B2 Peter Jones
B3 John Smith
B3 David Lee

Normalizing to 2NF

  • Question: The following ‘EmployeeProject’ table is in 1NF but not in 2NF. The primary key is a composite key: (EmployeeID, ProjectID).

EmployeeProject Table (in 1NF, Not in 2NF):

EmployeeID ProjectID EmployeeName ProjectName HoursWorked
101 P1 Alice Smith Project Alpha 20
101 P2 Alice Smith Project Beta 15
102 P1 Bob Johnson Project Alpha 25
  1. Identify the partial dependency(s) in this table.
  2. Normalize this table to Second Normal Form (2NF) by decomposing it into multiple tables. Show the structure of the resulting tables and indicate the primary keys.
  • Answer:
  1. Partial Dependencies:
    • EmployeeID -> EmployeeName (EmployeeName is dependent only on EmployeeID, part of the composite key).
    • ProjectID -> ProjectName (ProjectName is dependent only on ProjectID, part of the composite key).
  2. Tables in 2NF:

Employees Table (in 2NF):

EmployeeID EmployeeName
101 Alice Smith
102 Bob Johnson

Primary Key: EmployeeID

Projects Table (in 2NF):

ProjectID ProjectName
P1 Project Alpha
P2 Project Beta

Primary Key: ProjectID

EmployeeProjectDetails Table (in 2NF):

EmployeeID ProjectID HoursWorked
101 P1 20
101 P2 15
102 P1 25

Primary Key: (EmployeeID, ProjectID) (Composite Primary Key) Foreign Keys: EmployeeID references Employees, ProjectID references Projects

Normalizing to 3NF

  • Question: The following ‘CustomerOrders’ table is in 2NF but not in 3NF. The primary key is ‘OrderID’.

CustomerOrders Table (in 2NF, Not in 3NF):

OrderID CustomerID CustomerCity CityZipCode
O1 C1 London LDN123
O2 C2 Paris PRS456
O3 C1 London LDN123
  1. Identify the transitive dependency(s) in this table.
  2. Normalize this table to Third Normal Form (3NF) by decomposing it into multiple tables. Show the structure of the resulting tables and indicate the primary keys.
  • Answer:
  1. Transitive Dependency:
    • OrderID -> CustomerID -> CustomerCity, CityZipCode
    • More specifically, CustomerID -> CustomerCity and CustomerCity -> CityZipCode. However, the most direct transitive dependency violating 3NF is CustomerID -> CityZipCode via CustomerCity. (Although in this example, it is more naturally CustomerCity -> CityZipCode and CustomerID -> CustomerCity). Let’s assume CustomerCity -> CityZipCode.
  2. Tables in 3NF:

CustomerOrders Table (in 3NF):

OrderID CustomerID CustomerCity
O1 C1 London
O2 C2 Paris
O3 C1 London

Primary Key: OrderID Foreign Key: CustomerID references Customers, CustomerCity references Cities

Cities Table (in 3NF):

CustomerCity CityZipCode
London LDN123
Paris PRS456

Primary Key: CustomerCity

Customers Table (Assuming it exists and contains CustomerID and CustomerCity):

CustomerID … (other customer attributes) CustomerCity
C1 … London
C2 … Paris

Primary Key: CustomerID

Foreign Key: CustomerCity references Cities

Query

Relational Algebra

  • Question: Consider a table named Products with attributes ProductID, ProductName, and Category. Write a Relational Algebra expression to find the names of all products that belong to the ‘Electronics’ category.
  • Answer: π ProductName (σ Category = 'Electronics' (Products))

Relational Algebra - Combined Operations

  • Question: Using the Students table (StudentID, Name, Major, GPA) and Courses table (CourseID, CourseName, Credits), write a Relational Algebra expression to find the names of students who have a GPA greater than 3.5 AND are majoring in ‘CS’.
  • Answer: π Name (σ GPA > 3.5 ∧ Major = 'CS' (Students))

Tuple Relational Calculus (TRC)

  • Question: Using the Students table (StudentID, Name, Major, GPA), write a Tuple Relational Calculus (TRC) expression to find the StudentIDs of all students with a GPA less than 3.0.
  • Answer: {t.StudentID | t ∈ Students ∧ t.GPA < 3.0}

TRC - Combined Conditions

  • Question: Using the Employees table (EmpID, EmpName, DeptID) and Departments table (DeptID, DeptName), write a Tuple Relational Calculus (TRC) expression to find the names of employees who work in the ‘Sales’ department.
  • Answer: {e.EmpName | e ∈ Employees ∧ ∃d ∈ Departments (d.DeptID = e.DeptID ∧ d.DeptName = 'Sales')}

Basic SELECT Statement

  • Question: Assume you have a table named “Employees” with columns: EmployeeID, FirstName, LastName, Department. Write an SQL query to retrieve only the FirstName and LastName of all employees from the “Employees” table.
  • Answer: 
    SELECT FirstName, LastName
FROM Employees;

SELECT with WHERE Clause

  • Question: Using the same “Employees” table ( EmployeeID, FirstName, LastName, Department), write an SQL query to retrieve the FirstName, LastName, and Department of all employees who belong to the ‘Sales’ department.
  • Answer: 
    SELECT FirstName, LastName, Department
FROM Employees
WHERE Department = 'Sales';

SELECT with ORDER BY Clause

  • Question: Using the “Courses” table from the hands-on exercise (columns: CourseID, CourseName, Credits, Department), write an SQL query to retrieve the CourseName and Credits of all courses, ordered from the highest number of credits to the lowest.
  • Answer: 
    SELECT CourseName, Credits
FROM Courses
ORDER BY Credits DESC;

SELECT DISTINCT

Question: Assume the “Employees” table has a ‘City’ column. Write an SQL query to retrieve a list of all the unique cities where employees are located, without any duplicate city names in the result.

  • Answer: 
    SELECT DISTINCT City
FROM Employees;

INSERT Statement

  • Question: Assume you have a table named Courses with columns course_id (TEXT), course_name (TEXT), and credits (INTEGER). Write an SQL INSERT statement to add a new course with the following details: course_id: ‘INFO301’, course_name: ‘Database Systems’, credits: 3.
  • Answer: 
    INSERT INTO Courses (course_id, course_name, credits)
VALUES ('INFO301', 'Database Systems', 3);

UPDATE Statement

  • Question: Using the same Courses table from Question 2, write an SQL UPDATE statement to change the credits of the course with course_id ‘CS202’ to 4 credits.
  • Answer: 
    UPDATE Courses
SET credits = 4
WHERE course_id = 'CS202';

DELETE Statement

  • Question: Again, using the Courses table, write an SQL DELETE statement to remove the course with course_id ‘CS101’ from the table.
  • Answer: 
    DELETE FROM Courses
WHERE course_id = 'CS101';

INNER JOIN

  • Question: Write an SQL query using INNER JOIN to retrieve the first name and last name of students from the Students table and the course_name from the Courses table for all students enrolled in any course. Assume Students table has columns id, first_name, last_name and Enrollments table has student_id, course_id. Assume Courses table has course_id, course_name. The join should be on student_id and course_id respectively.
  • Answer: 
    SELECT s.first_name, s.last_name, c.course_name
FROM Students s
INNER JOIN Enrollments e ON s.id = e.student_id
INNER JOIN Courses c ON e.course_id = c.course_id;

LEFT JOIN

  • Question: Write an SQL query using LEFT JOIN to retrieve the first name and last name of all students from the Students table and their enrolled course_id from the Enrollments table. If a student is not enrolled in any course, the course_id should be displayed as NULL.
  • Answer: 
    SELECT s.first_name, s.last_name, e.course_id
FROM Students s
LEFT JOIN Enrollments e ON s.id = e.student_id;

Substitution Function (CONCAT)

  • Question: Write an SQL query using the CONCAT() function to display the full name of each student from the Students table, combining first_name and last_name and separated by a space. Alias the resulting column as full_name.
  • Answer: 
    SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM Students;

GROUP BY and COUNT

  • Question: Write an SQL query to count the number of enrollments for each course_id in the Enrollments table. Display the course_id and the number of enrollments for each course, aliased as enrollment_count.
  • Answer: 
    SELECT course_id, COUNT(*) AS enrollment_count
FROM Enrollments
GROUP BY course_id;

GROUP BY and AVG

  • Question: Write an SQL query to calculate the average grade for each course_id in the Enrollments table. Display the course_id and the average grade, aliased as average_grade.
  • Answer: 
    SELECT course_id, AVG(grade) AS average_grade
FROM Enrollments
GROUP BY course_id;

CREATE VIEW

  • Question: Write an SQL statement to create a view named CourseEnrollmentCounts that shows the course_id and its enrollment_count (as calculated in Question 9) from the Enrollments table.
  • Answer: 
    CREATE VIEW CourseEnrollmentCounts AS
SELECT course_id, COUNT(*) AS enrollment_count
FROM Enrollments
GROUP BY course_id;