Explore Exams
Login Signup
Sign in Open App
All Exams  >   Software Development  >   Database Management System (DBMS)  >   All Questions

All questions of Relational Databases for Software Development Exam

Clear all your doubts with EduRev

Consider the following SQL code:
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50),
  salary DECIMAL(10, 2)
);
INSERT INTO employees (emp_id, emp_name, salary)
VALUES (1, 'John Doe', 5000.00);
SELECT emp_name FROM employees;
What will be the output of the SELECT statement?
  • a)
    John Doe
  • b)
    emp_name
  • c)
    5000.00
  • d)
    Error: Table 'employees' doesn't exist.
Correct answer is option 'A'. Can you explain this answer?

Rithika Saha answered
Understanding the SQL Code
The SQL code provided performs a series of operations related to a database table named `employees`. Let's break it down for clarity.
Table Creation
- The first line of code creates a new table:
- `CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), salary DECIMAL(10, 2));`
- This defines a table with three columns:
- `emp_id`: an integer that serves as the primary key.
- `emp_name`: a string (up to 50 characters) for employee names.
- `salary`: a decimal number for employee salaries.
Data Insertion
- The second line inserts a record into the `employees` table:
- `INSERT INTO employees (emp_id, emp_name, salary) VALUES (1, John Doe, 5000.00);`
- Note: This line will actually throw an error due to incorrect syntax. The name "John Doe" should be enclosed in single quotes.
SELECT Statement
- The third line retrieves data:
- `SELECT emp_name FROM employees;`
- This statement is designed to fetch the `emp_name` column from the `employees` table.
Expected Output
- If the insertion had been successful (which it won't due to the missing quotes), the output of the `SELECT` statement would be:
- The name of the employee, which is "John Doe".
Conclusion
- Since the correct answer is option 'A', it suggests that the assumption is made that the insert would not have syntax errors. However, due to the syntax error, the actual execution would fail.
- Therefore, while the expected output based on the corrected syntax would be option 'A', the original code will result in an insertion error.

Which SQL keyword is used to retrieve data from a relational database table?
  • a)
    SELECT
  • b)
    INSERT
  • c)
    UPDATE
  • d)
    DELETE
Correct answer is option 'A'. Can you explain this answer?

Ritesh Kumar Shivhare answered
The SELECT keyword is used to retrieve data from a relational database table. It allows you to specify the columns you want to retrieve and apply filtering conditions.

Consider the following table "employees":
+----+----------+-----------+
| id | name | salary |
+----+----------+-----------+
| 1 | John | 50000 |
| 2 | Alice | 60000 |
| 3 | Bob | 45000 |
+----+----------+-----------+
Which SQL query will retrieve the names of employees whose names contain the letter "o"?
  • a)
    SELECT name FROM employees WHERE name LIKE '%o%';
  • b)
    SELECT name FROM employees WHERE name LIKE 'o%';
  • c)
    SELECT name FROM employees WHERE name LIKE '%o';
  • d)
    SELECT name FROM employees WHERE name = 'o';
Correct answer is option 'A'. Can you explain this answer?

Arshiya Iyer answered

Explanation:

SQL Query Explanation:
The SQL query retrieves the names of employees whose names contain the letter "o" by using the LIKE operator with the wildcard symbol %.

Query Breakdown:
- SELECT name: Specifies the column to be retrieved from the table.
- FROM employees: Specifies the table from which to retrieve the data.
- WHERE name LIKE %o%: Filters the results to only include rows where the name column contains the letter "o" anywhere in the name.

Why Option A is Correct:
- Option A correctly uses the LIKE operator with the wildcard symbols % before and after the letter "o" to match any occurrence of the letter "o" in the name column.
- Options B, C, and D do not use the wildcard symbols correctly or do not include them at all, which would not provide the desired result of retrieving names containing the letter "o".

Therefore, the correct SQL query to retrieve the names of employees whose names contain the letter "o" is SELECT name FROM employees WHERE name LIKE %o%;.

Consider the following table "customers":
+----+----------+-----------+
| id | name | city |
+----+----------+-----------+
| 1 | John | New York |
| 2 | Alice | Chicago |
| 3 | Bob | New York |
+----+----------+-----------+
Which SQL query will retrieve the unique cities from which customers belong?
  • a)
    SELECT DISTINCT city FROM customers;
  • b)
    SELECT UNIQUE city FROM customers;
  • c)
    SELECT city FROM customers WHERE DISTINCT;
  • d)
    SELECT city FROM customers GROUP BY city;
Correct answer is option 'A'. Can you explain this answer?

Janani Basak answered
Explanation:

SELECT DISTINCT city FROM customers;
- The query retrieves unique cities from the "customers" table.
- The keyword "DISTINCT" ensures that only unique values are returned.
- The query specifically selects the "city" column from the "customers" table.
Therefore, option 'A' is the correct SQL query to retrieve the unique cities from which customers belong.

Which of the following is not a valid data type in a relational database?
  • a)
    Integer
  • b)
    Text
  • c)
    Boolean
  • d)
    Array
Correct answer is option 'D'. Can you explain this answer?

Ashish Chauhan answered
Invalid Data Type in a Relational Database

In a relational database, data is organized into tables consisting of rows and columns. Each column in a table has a specific data type that defines the kind of data it can store. The most commonly used data types in a relational database include integer, text, and boolean. However, arrays are not a valid data type in a relational database.

Explanation:

1. Integer:
- Integer is a valid data type in a relational database.
- It represents whole numbers without decimal places.
- It can store both positive and negative values.
- Examples: 1, 2, -10, 100, etc.

2. Text:
- Text is another valid data type in a relational database.
- It is used to store alphanumeric characters and textual data.
- It can hold a variable length of characters.
- Examples: "Hello", "World", "123abc", etc.

3. Boolean:
- Boolean is also a valid data type in a relational database.
- It represents logical values of either true or false.
- It is often used to store binary information or as a flag to indicate the state of a condition.
- Examples: true, false.

4. Array:
- Arrays are not a valid data type in a relational database.
- An array is a collection of elements that can be of different data types.
- Arrays are typically used in programming languages to store multiple values under a single variable.
- However, in a relational database, each column in a table should have a single data type.
- Instead of using arrays, relational databases use multiple tables and relationships to store and retrieve data efficiently.

Conclusion:
In a relational database, arrays are not a valid data type. The valid data types include integer, text, and boolean, which are commonly used to store different kinds of data in the tables.

Which of the following is not a part of the database design process?
  • a)
    Conceptual design
  • b)
    Logical design
  • c)
    Physical design
  • d)
    Transaction design
Correct answer is option 'D'. Can you explain this answer?

Code Nation answered
Transaction design is not a part of the database design process. It focuses on managing and ensuring the atomicity, consistency, isolation, and durability of database transactions.

Consider the following SQL code:
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  total_amount DECIMAL(10, 2)
);
INSERT INTO orders (order_id, order_date, total_amount)
VALUES (1, '2023-01-01', 100.00),
       (2, '2023-01-02', 200.00),
       (3, '2023-01-03', 300.00);
SELECT SUM(total_amount) FROM orders;
What will be the output of the SELECT statement?
  • a)
    100.00
  • b)
    600.00
  • c)
    500.00
  • d)
    Error: Table 'orders' doesn't exist.
Correct answer is option 'B'. Can you explain this answer?

Hiral Banerjee answered
Explanation:
The given SQL code creates a table called "orders" with three columns: order_id, order_date, and total_amount. The order_id column is defined as the primary key. The order_date column is of type DATE, and the total_amount column is of type DECIMAL with a precision of 10 and a scale of 2.

The INSERT statement is used to populate the orders table with three rows of data. Each row represents an order and includes values for the order_id, order_date, and total_amount columns.

The SELECT statement is used to retrieve the sum of the total_amount column from the orders table. The SUM function is applied to the total_amount column to calculate the total amount of all orders.

Output:
The output of the SELECT statement will be 600.00. This is because the SUM function calculates the sum of all values in the total_amount column, which are 100.00, 200.00, and 300.00.

Therefore, the sum of 100.00 + 200.00 + 300.00 is equal to 600.00.

Consider the following table "employees":
+----+----------+-----------+
| id | name | salary |
+----+----------+-----------+
| 1 | John | 50000 |
| 2 | Alice | 60000 |
| 3 | Bob | 45000 |
+----+----------+-----------+
Which SQL query will retrieve the names of employees with a salary between 50000 and 60000?
  • a)
    SELECT name FROM employees WHERE salary > 50000 AND salary < 60000;
  • b)
    SELECT name FROM employees WHERE salary BETWEEN 50000 AND 60000;
  • c)
    SELECT name FROM employees WHERE salary > 50000 OR salary < 60000;
  • d)
    SELECT name FROM employees WHERE salary = 50000 OR salary = 60000;
Correct answer is option 'B'. Can you explain this answer?

Abhiram Chaudhary answered
Understanding the SQL Query
To retrieve the names of employees with a salary between 50000 and 60000, option 'B' is the correct choice. Here's a breakdown of why this option is appropriate.
What Option B Does
- SQL Syntax: The query `SELECT name FROM employees WHERE salary BETWEEN 50000 AND 60000;` utilizes the `BETWEEN` operator.
- Inclusive Range: The `BETWEEN` operator includes both endpoints, meaning it retrieves employees earning exactly 50000 or 60000 as well as those whose salaries fall in between.
Why Other Options Are Incorrect
- Option A: `SELECT name FROM employees WHERE salary > 50000 AND salary < />
- This query excludes employees with salaries of 50000 and 60000, which is not desired since we need to include these values.
- Option C: `SELECT name FROM employees WHERE salary > 50000 OR salary < />
- This query is too broad, as it will return all employees with salaries above 50000 and also those below 60000, including many who do not fall within the specified range.
- Option D: `SELECT name FROM employees WHERE salary = 50000 OR salary = 60000;`
- This only retrieves employees whose salaries are exactly 50000 or 60000, completely missing out on those with salaries in between.
Conclusion
The correct SQL query is option 'B' because it accurately captures all employees with salaries from 50000 to 60000, inclusive. Understanding the functionality of SQL operators like `BETWEEN` is essential for effective data retrieval.

Consider the following table "orders":
+----+------------+------------+
| id | order_date | total |
+----+------------+------------+
| 1 | 2022-05-01 | 100 |
| 2 | 2022-06-15 | 250 |
| 3 | 2022-07-03 | 75 |
+----+------------+------------+
Which SQL query will retrieve the total revenue earned in June 2022?
  • a)
    SELECT SUM(total) FROM orders WHERE order_date LIKE '2022-06%';
  • b)
    SELECT SUM(total) FROM orders WHERE order_date = '2022-06%';
  • c)
    SELECT SUM(total) FROM orders WHERE order_date LIKE ' 22-06%';
  • d)
    SELECT SUM(total) FROM orders WHERE order_date = ' 22-06%';
Correct answer is option 'A'. Can you explain this answer?

Ritesh Kumar Shivhare answered
This query retrieves the sum of the "total" column from the "orders" table where the order_date is in June 2022. The LIKE operator is used for pattern matching.

Consider the following table "orders":
+----+------------+------------+
| id | order_date | total |
+----+------------+------------+
| 1 | 2022-05-01 | 100 |
| 2 | 2022-06-15 | 250 |
| 3 | 2022-07-03 | 75 |
+----+------------+------------+
Which SQL query will retrieve the number of orders placed in each month?
  • a)
    SELECT MONTH(order_date), COUNT(id) FROM orders GROUP BY MONTH(order_date);
  • b)
    SELECT YEAR(order_date), COUNT(id) FROM orders GROUP BY YEAR(order_date);
  • c)
    SELECT DAY(order_date), COUNT(id) FROM orders GROUP BY DAY(order_date);
  • d)
    SELECT COUNT(id) FROM orders;
Correct answer is option 'A'. Can you explain this answer?

Tanuj Arora answered
This query retrieves the number of orders placed in each month by extracting the month from the "order_date" column and grouping the results by the month.

Chapter doubts & questions for Relational Databases - Database Management System (DBMS) 2025 is part of Software Development exam preparation. The chapters have been prepared according to the Software Development exam syllabus. The Chapter doubts & questions, notes, tests & MCQs are made for Software Development 2025 Exam. Find important definitions, questions, notes, meanings, examples, exercises, MCQs and online tests here.

Chapter doubts & questions of Relational Databases - Database Management System (DBMS) in English & Hindi are available as part of Software Development exam. Download more important topics, notes, lectures and mock test series for Software Development Exam by signing up for free.

Database Management System (DBMS)

75 videos|44 docs|12 tests

Top Courses Software Development

© EduRev
Education Revolution
Signup on EduRev and stay on top of your study goals
10M+ students crushing their study goals daily