KnowledgeBoat Logo
OPEN IN APP

Chapter 3

Review of Database Concepts & SQL

Class 12 - Informatics Practices Preeti Arora



Fill in the Blanks

Question 1

A Database is an organized collection of records.

Question 2

DBMS stands for Database Management System.

Question 3

Data Redundancy means duplication of data.

Question 4

Tuples are known as rows and Attributes are known as columns in RDBMS.

Question 5

MySQL is a freely available open-source RDBMS that implements SQL.

Question 6

MySQL provides a dummy table called Dual.

Question 7

The Distinct keyword eliminates duplicate records from the results of a SELECT statement.

Question 8

The Describe or Desc statement is used to view the structure of a table.

Question 9

The Where clause is used to select specific rows.

Question 10

Select is used to fetch data from one or more database tables.

Question 11

Select * statement displays all Columns of a table.

Question 12

The rows of the table (relation) are referred to as Tuples.

Question 13

Insert into command is used to add a new record to a table.

Question 14

The non-key attribute which helps to make relationship between two tables is known as Foreign key.

Question 15

Alter table command is used to change the data type of an existing column.

State True or False

Question 1

In relational data model a table is called relation.

Answer

True

Reason — In the relational data model, the data is organized into tables (i.e., rows and columns). These tables are called relation.

Question 2

A row in a table will not represent a relationship among a set of values.

Answer

False

Reason — In the relational data model, a row in a table represents a relationship among a set of values.

Question 3

Data Item is the smallest unit of named data.

Answer

True

Reason — In the relational data model, a data item is the smallest unit of named data. It is an atomic value that is the basic building block of a relational database.

Question 4

Data Item represents multi-type of information in a field.

Answer

False

Reason — A data item, in the relational data model, represents a single value or a single type of information in a field, not multi-type.

Question 5

Duplication of data is known as Data Redundancy.

Answer

True

Reason — Data redundancy refers to the duplication of data in a database.

Question 6

An attribute is a set of values of a dissimilar type of data.

Answer

False

Reason — An attribute (Column) is a set of values of a similar type of data.

Question 7

MySQL supports different platforms like UNIX and Windows.

Answer

True

Reason — MySQL supports different platforms like UNIX and Windows because it is an open-source and freely-available Relational Database Management System (RDBMS).

Question 8

UPDATE TABLE command is used to create table in a database.

Answer

False

Reason — The CREATE TABLE command is used to create table in a database.

Question 9

Null (Unavailable and unknown) values are entered by the following command:

INSERT INTO TABLE NAME VALUES ("NULL") ;

Answer

False

Reason — The correct syntax for inserting a NULL value is : INSERT INTO TABLE_NAME VALUES (NULL, NULL, ...); or INSERT INTO TABLE_NAME (COLUMN_NAMES) VALUES (NULL);.

Question 10

ALTER TABLE command is used to modify the structure of the table.

Answer

True

Reason — The ALTER TABLE command in SQL is used to modify the structure of a table.

Question 11

All primary keys are candidate keys but all candidate keys are not primary keys.

Answer

True

Reason — A primary key is a candidate key chosen to uniquely identify rows in a table. All primary keys are candidate keys, but not all candidate keys become primary keys, as only one is selected for that role.

Multiple Choice Questions

Question 1

............... is a named collection of data items which represents a complete unit of information.

  1. Field
  2. Record
  3. Table
  4. Database

Answer

Record

Reason — Record is a named collection of data items which represents a complete unit of information.

Question 2

A ............... is a named collection of all occurrences of a given type of logical record.

  1. Field
  2. Record
  3. Relation
  4. Database

Answer

Relation

Reason — A relation (table) is a named collection of all occurrences of a given type of logical record.

Question 3

The number of attributes in a relation determines the ............... of a relation.

  1. Degree
  2. Tuples
  3. Attributes
  4. Cardinality

Answer

Degree

Reason — The total number of columns or attributes in a relation is known as degree of a relation.

Question 4

The rows of the relations are generally referred to as ............... .

  1. Degree
  2. Tuples
  3. Attributes
  4. Cardinality

Answer

Tuples

Reason — The rows of the relations are referred to as tuples.

Question 5

What is a database?

  1. Organized collection of information that cannot be accessed, updated and managed
  2. Collection of data or information without organizing
  3. Organized collection of data or information that can be accessed, updated and managed
  4. Organized collection of data that cannot be updated

Answer

Organized collection of data or information that can be accessed, updated and managed

Reason — Database is an organized collection of interrelated data or information that can be accessed, updated and managed.

Question 6

............... is a set of one or more attributes that can uniquely identify tuples within the relation.

  1. Primary Key
  2. Cardinality
  3. Attributes
  4. Foreign Key

Answer

Primary Key

Reason — A primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table.

Question 7

A candidate key that is not the primary key is called a/an ............... .

  1. Primary Key
  2. Alternate Key
  3. Candidate Key
  4. Foreign Key

Answer

Alternate Key

Reason — A candidate key that is not selected as the primary key is called an alternate key.

Question 8

The ............... allows you to perform tasks related to data definition.

  1. DDL
  2. DML
  3. TCL
  4. None of these

Answer

DDL

Reason — The DDL (Data Definition Language) commands allow us to perform tasks related to data definition, i.e., related to the structure of the database objects.

Question 9

The ............... allows you to perform tasks related to data manipulation.

  1. DDL
  2. DML
  3. TCL
  4. None of these

Answer

DML

Reason — The DML (Data Manipulation Language) commands are used to manipulate data, i.e., records or rows in a table or relation.

Question 10

A ............... is a text that is not executed.

  1. Statement
  2. Query
  3. Comment
  4. Clause

Answer

Comment

Reason — A comment is a text which is ignored by the SQL compiler and is not executed at all. It is given for documentation purpose only.

Question 11

............... are words that have a special meaning in SQL.

  1. Keywords
  2. Literals
  3. Variables
  4. Tables

Answer

Keywords

Reason — A keyword refers to an individual SQL element that has a special meaning in SQL.

Question 12

Which of the following are DDL commands?

(A) Delete

(B) Create

(C) Update

(D) Alter

(E) Drop

  1. (B), (D) and (E)
  2. (A), (B) and (D)
  3. (B), (C) and (D)
  4. (A), (B) and (C)

Answer

(B), (D) and (E)

Reason — DDL (Data Definition Language) commands are used to create and define tables and other database objects in SQL (Structured Query Language). DDL commands such as CREATE, ALTER, and DROP, are used to create, define, change and delete objects like tables, indexes, views, and constraints.

Question 13

Identify the correct statement(s):

Statement 1 (S1): Char data type in MySQL stores fixed length strings.

Statement 2 (S2): Char data type stores string smaller than the maximum field size.

  1. (S1) : Correct, (S2) : Correct
  2. (S1): Incorrect, (S2) : Correct
  3. (S1): Correct, (S2) : Incorrect
  4. (S1) : Incorrect, (S2) : Incorrect

Answer

(S1): Correct, (S2) : Incorrect

Reason — The CHAR data type provides fixed-length memory storage. It specifies a fixed-length character string. If the input string is shorter, MySQL pads it with spaces to fill the fixed length. If the input string is longer, it is truncated to fit the fixed length.

Question 14

Which of the following keywords is used to display non-repeated values in MySQL?

  1. Unique
  2. All
  3. Order by
  4. Distinct

Answer

Distinct

Reason — The DISTINCT keyword is used to display the unique values of the column in MySQL.

Question 15

The SQL statements always end with ............... .

  1. ,
  2. :
  3. ;
  4. "

Answer

;

Reason — The SQL statements always ends with semicolon (;) to indicate the end of the statement.

Question 16

Shivam wants to see the table structure in MySQL. Select an appropriate command to help him.

  1. Use
  2. Show
  3. Desc
  4. Display

Answer

Desc

Reason — The DESCRIBE or DESC command is used to view a table structure in MySQL.

Question 17

Rajat wants to delete a primary key constraint from the table. Select an appropriate command to do so.

  1. Create
  2. Alter
  3. Drop
  4. Delete

Answer

Alter

Reason — The ALTER TABLE command is used to delete a primary key constraint from the table.

Question 18

Rajveer wants to rename column in display result for his query. Select the appropriate query for the same:

  1. Select Ename, Salary*12 Annual Salary From Emp;
  2. Select Ename, Salary*12 Rename "Annual Salary" From Emp;
  3. Select Ename, Salary*12 Change "Annual Salary" From Emp;
  4. Select Ename, Salary*12 as "Annual Salary" From Emp;

Answer

Select Ename, Salary*12 as "Annual Salary" From Emp;

Reason — The AS keyword is used to give an alias to a column or expression in the SELECT statement. In this case, AS "Annual Salary" is used to rename the column Salary*12 to "Annual Salary" in the display result.

Question 19

The symbol Asterisk (*) in a select query retrieves ............... .

  1. All data from the table
  2. Data of primary key only
  3. NULL data
  4. None of these

Answer

All data from the table

Reason — The asterisk symbol (*) is a wildcard character in SQL that retrieves all columns or fields from a table. When used in a SELECT statement, it returns all columns and rows from the specified table.

Question 20

Consider the attributes ( RollNumber, SName, SDateofBirth, GUID ) of the table Student. According to you, which of the following options is the correct representation of the table after executing the following query?

Insert Into Student (RollNumber, SName, SDateofBirth) 
Values(2, 'Sudha', '2002-02-28') ;

1.

RollNumberSNameSDateofBirthGUID
1Atharv2003-05-1512354899
2Sudha2002-02-28NULL

2.

RollNumberSNameSDateofBirthGUID
1Atharv2003-05-1512354899
2Sudha2002-02-2800000000

3.

RollNumberSNameSDateofBirthGUID
1Atharv2003-05-1512354899
2Sudha2002-02-280

4.

RollNumberSNameSDateofBirthGUID
1Atharv2003-05-1512354899
2Sudha2002-02-28

Answer

RollNumberSNameSDateofBirthGUID
1Atharv2003-05-1512354899
2Sudha2002-02-28NULL

Reason — In the above code, a new row is inserted into the 'Student' table, values are provided for RollNumber, SName, and SDateofBirth, but not for GUID. Since GUID is not specified, it will take on its default value, which may be NULL if no default value is defined for the column.

Question 21

Consider a table Student having two fields—FName varchar(20) and LName char(20). If in a record, value stored in Fname is 'Anuj' and LName is 'Batra', then FName and LName will consume ............... and ............... character space respectively.

  1. 4, 5
  2. 4, 20
  3. 20, 4
  4. 20, 20

Answer

4, 20

Reason — FName is a varchar(20) field, which means it can store a variable-length string up to a maximum of 20 characters. Since the value stored in FName is 'Anuj', it will consume 4 character spaces (A-n-u-j). LName is a char(20) field, which means it is a fixed-length string that always occupies 20 character spaces, regardless of the actual length of the string. Since the value stored in LName is 'Batra', it will still consume 20 character spaces, with the remaining 15 characters being padded with spaces.

Assertions and Reasons

Question 1

Assertion (A): Database management system is an application software which arranges data in a well-organized manner in the form of tables.

Reasoning (R): DBMS acts as an interface between the database stored in the computer memory and the user.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
Database Management System (DBMS) is an application software that acts as an interface between the database stored in the computer memory and the user. It enables users to interact with the database and store data in a well-organized manner in the form of tables. The purpose of the DBMS software is to allow the user to create, access, modify, and control a database.

Question 2

Assertion (A): A database consists of multiple tables.

Reasoning (R): A foreign key is used to represent the relationship between two tables.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
In a database system, data is organized into multiple tables for better organization, management, and query efficiency. A foreign key is a non-key attribute whose value is derived from the primary key of another table in a database. It references the primary key in another table, thereby establishing a relationship between the two tables.

Question 3

Assertion (A): The limitations of traditional file system are overcome by storing data in a database.

Reasoning (R): We can organize related data logically in a database.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
Traditional file systems have limitations such as data redundancy, inconsistency, and difficulties in data sharing and security. In contrast, databases store data in a structured and organized manner, which helps overcome these limitations. They allow us to logically organize related data, making data management, retrieval, and manipulation more efficient.

Question 4

Assertion (A): The number of attributes in a relation is called the degree of the relation.

Reasoning (R): The number of tuples in a relation is called the cardinality of the relation.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

Both A and R are true but R is not the correct explanation of A.

Explanation
The degree of a relation and the cardinality of a relation are two separate concepts in database management. The number of attributes or columns in a relation is called the degree of the relation, while the number of tuples or records in a relation is called the cardinality of the relation.

Question 5

Assertion (A): Each table must have one primary key.

Reasoning (R): Primary key is a set of one or more attributes that uniquely identifies a tuple in a relation.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
In a database, each table can have only one primary key, which is unique and non-redundant in nature. The primary key is a set of one or more attributes or fields that uniquely identify a tuple or row in a relation.

Question 6

Assertion (A): A database can have only one table.

Reasoning (R): If a piece of data is stored in two places in a database, then it leads to wastage of storage space.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

A is false but R is true.

Explanation
A database can have multiple tables. Data redundancy, which occurs when the same data is repeated in multiple places within a database, leads to wastage of storage space. A DBMS eliminates data redundancy by integrating files, ensuring that multiple copies of the same data are not stored.

Question 7

Assertion (A): A database constraint can be added or removed any time in/from the database tables.

Reasoning (R): Alter table command is used to change the structure of the table.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

Both A and R are true but R is not the correct explanation of A.

Explanation
A database constraint can be added or removed from database tables using the ALTER TABLE command, even after the table has already been created. This command is used to modify the structure of a table by altering the definition of its columns.

Question 8

Assertion (A): SQL has efficient mechanisms to retrieve data stored in multiple tables in a MySQL database.

Reasoning (R): The SQL statement CREATE is used to retrieve data from the tables in a database and is also called query statement.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

A is true but R is false.

Explanation
SQL provides efficient mechanisms, such as JOIN operations, to retrieve data from multiple tables in a MySQL database. The SQL statement CREATE is used to create new database objects such as tables, indexes, or views. The SELECT statement is used to retrieve data from tables in a database and is known as a query statement.

Question 9

Assertion (A): The SQL keyword Like is used with wildcards only.

Reasoning (R): '_' underscore and "%" per cent are the two wildcard characters used with LIKE clause.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
The SQL LIKE keyword allows the use of wildcard characters to perform pattern matching. SQL provides two wildcard characters to use with the LIKE operator: the percent sign (%) which matches any string, and the underscore ("_") which matches any single character.

Question 10

Assertion (A): DISTINCT clause must be used in an SQL statement to eliminate duplicate rows.

Reasoning (R): DISTINCT only works with numeric data type only.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

A is true but R is false.

Explanation
The DISTINCT clause is used to remove duplicate rows from the results of a SELECT statement. It retrieves only unique values for a column in the table. The DISTINCT keyword in SQL can be used with any data type.

Question 11

Assertion (A): FLOAT and DOUBLE are data types.

Reasoning (R): Both can hold any number up to 23 digits.

  1. Both A and R are true and R is the correct explanation of A.
  2. Both A and R are true but R is not the correct explanation of A.
  3. A is true but R is false.
  4. A is false but R is true.

Answer

A is true but R is false.

Explanation
FLOAT and DOUBLE are data types in SQL, used to store decimal numbers. FLOAT can store values with a precision of around 6-7 digits, while DOUBLE can store values with a precision of around 15-16 digits.

Case/Source Based Questions

Question 1

Kunal has entered the following SQL command in the table 'STUDENT' that has TotalMarks as one of the columns:

SELECT * FROM Student;                         #Statement-1

The total number of rows displayed is 20.

Then Kunal enters the following command:

SELECT * FROM STUDENT WHERE TotalMarks < 100;  #Statement-2 

The number of rows displayed is 15.

Kunal then enters the following command:

SELECT * FROM STUDENT WHERE TotalMarks >= 100; #Statement-3

He predicts the output of the above query as 5. Do you agree with Kunal? Give reasons for your answer.

Answer

I disagree with Kunal's prediction. Since Statement-1 returns all the rows and columns of the table Student i.e., 20, and Statement-2 returns 15 rows with TotalMarks less than 100, it means 20 - 15 = 5 students have TotalMarks greater than 100. However, Statement-3 returns rows where TotalMarks is greater than or equal to 100, which includes students who scored exactly 100, and we don't know how many students scored exactly 100, so the number of rows returned by Statement-3 will be greater than or equal to 5, but not necessarily exactly 5.

Question 2(a)

Mr. Shivaya is using a table 'COURSE' with the following columns: COURSE_ID, COURSE_NAME. He needs to display the names of all the courses which end with "SCIENCE". He has written the query mentioned below, which is not giving the desired result.

SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE COURSE_NAME = '_SCIENCE';

Help Mr. Shivaya to write the correct query.

Answer

SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE COURSE_NAME LIKE '%SCIENCE';
Explanation

The "=" operator is used for exact matching and the "_" wildcard character in SQL is used to match a single character, not a sequence of characters. To achieve the desired result, Mr. Shivaya should use the LIKE operator with the "%" wildcard character, which matches any sequence of characters.

Question 2(b)

Ms. Manisha, a veterinarian, created a table 'VETERINARY' with the following columns:

ANIMAL_ID, VACCINATION_DATE, ANIMAL, OWNER_NAME

She wants to see the details of all the animals other than Dog and Cat which she has vaccinated.
She has written the following query:

SELECT * FROM VETERINARY WHERE ANIMAL NOT IN ('DOG', 'CAT');

Write a suitable alternate query for producing the same result.

Answer

SELECT * FROM VETERINARY 
WHERE ANIMAL != 'DOG' AND ANIMAL != 'CAT';

Question 3

Your school management has decided to organize cricket matches between students of Classes XI and XII. All the students are divided into four teams—Team Rockstars, Team BigGamers, Team Magnet and Team Current. During the summer vacations, various matches are to be held between these teams. Help your sports teacher do the following:

(a) Create a database "Sports" and open it for creating table.

(b) Create a table "Team" with the following considerations:

  1. It should have a column TeamID for storing an integer value between 1 and 9, which refers to unique identification of a team.
  2. Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.
  3. Give the statement to make TeamID the primary key.

(c) Show the structure of the table Team using SQL command.

(d) As per the preferences of the students, four teams were formed as given below.

Insert these four rows in Team table:

Row 1: (1, Team Rockstars)
Row 2: (2, Team BigGamers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Current)

(e) Display the table Team.

Answer

(a)

CREATE DATABASE SPORTS;
USE SPORTS;

(b)

CREATE TABLE TEAM(TEAMID INT(9) Primary key, TEAMNAME VARCHAR(30));

(c)

DESC TEAM;
Output
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| TEAMID   | int         | NO   | PRI | NULL    |       |
| TEAMNAME | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

(d)

INSERT INTO TEAM VALUES(1, "TEAM ROCKSTARS");
INSERT INTO TEAM VALUES(2, "TEAM BIGGAMERS");
INSERT INTO TEAM VALUES(3, "TEAM MAGNET");
INSERT INTO TEAM VALUES(4, "TEAM CURRENT");

(e)

SELECT * FROM TEAM;
Output
+--------+----------------+
| TEAMID | TEAMNAME       |
+--------+----------------+
|      1 | TEAM ROCKSTARS |
|      2 | TEAM BIGGAMERS |
|      3 | TEAM MAGNET    |
|      4 | TEAM CURRENT   |
+--------+----------------+

Solutions to Unsolved Questions

Question 1

What is data?

Answer

Data is the smallest unit of file organization which is represented in the form of a bit that may either be 0 or 1.

Question 2

What do you mean by information?

Answer

Information is processed, organized, and meaningful data that has been analyzed, interpreted, and structured to provide context, relevance, and value. It is derived from data through analysis and is used to make decisions or gain insights.

Question 3

What is the difference between data and information?

Answer

DataInformation
Data refers to raw facts, figures, or values that are collected and stored without any specific context or interpretation. It is unprocessed and lacks meaning on its own.Information is processed, organized, and meaningful data that has been analyzed, interpreted, and structured to provide context, relevance, and value. It is derived from data through analysis and is used to make decisions or gain insights.
Data is unstructured, discrete.Information is structured, organized.

Question 4

What is database and database system? What are the elements of database system?

Answer

Database is an organized collection of interpreted data that serves many applications.

A Database Management System is a general-purpose software system that facilitates the process of defining, constructing and manipulating databases for various applications.

A database system in SQL consists of tables with rows and columns, a schema defining structure and relationships, SQL for interaction, indexes for performance, constraints for data integrity.

Question 5

Why do we need a database?

Answer

We need a database because it provides a structured and organized way to store, manage, and retrieve large amounts of data efficiently and effectively.

Question 6

What is database management system? Why do we need a DBMS?

Answer

A Database Management System (DBMS) is a general-purpose software system that facilitates the process of defining, constructing and manipulating databases for various applications.

The database system is used to eliminate the problems of data redundancy and data inconsistency.

Question 7(i)

Explain the difference between database and file.

Answer

DatabaseFile
Database is an organized collection of interpreted data that serves many applications.A file is a collection of unorganized data stored in a single location.
Data retrieval is fast and efficient, as databases use indexing and querying mechanisms.Data retrieval is slow and inefficient, as the entire file needs to be searched.
Data sharing is easy, as multiple users and applications can access the database simultaneously.Data sharing is limited, as files are typically accessed by a single user or application.
Data integrity is ensured through constraints, triggers, and transactions.Data integrity is not ensured, as data can be corrupted or lost easily.
Databases support relationships between data entities.Files do not support relationships between data entities.

Question 7(ii)

Explain the difference between data and file.

Answer

DataFile
Data refers to raw facts, figures, or values that are collected and stored without any specific context or interpretation.A file is a collection of unorganized data stored in a single location.
The purpose of data is to convey information, provide insights, or support decision-making.The purpose of a file is to store, manage, and provide access to the data.
Data can exist in various formats, such as numbers, text, images, or audio, and can be structured or unstructured.A file has a specific format and structure, defined by its file type (e.g., .txt, .jpg, .mp3), which determines how the data is stored and retrieved.

Question 8

Suppose all customers of a particular business live in states for which the city name is unique. Given the following description for customer data:

CUST-ID, CUST-NAME, STREET, CITY, STATE, PHONE

(i) List the most likely key for the primary key.
(ii) List all the candidate keys and alternate keys.

Answer

(i) The most likely primary key is CUST-ID because it is a unique identifier assigned to each customer.

(ii) CUST-ID, CITY attributes can be considered as candidate keys as both are unique to each customer.

CITY can serve as an alternate key because all customers live in states where the city names are unique.

Question 9(i)

Define Relation.

Answer

A relation is a table i.e., data arranged in rows and columns.

Question 9(ii)

Define Domain.

Answer

A domain is a pool of values from which the actual values appearing in a given column are drawn.

Question 9(iii)

Define Tuple.

Answer

The rows of tables (relations) are called tuples.

Question 9(iv)

Define Attribute.

Answer

The columns of tables (relations) are called attributes.

Question 9(v)

Define Degree.

Answer

The number of attributes in a relation is called degree of a relation.

Question 9(vi)

Define Cardinality.

Answer

The number of rows in a relation is known as cardinality of the relation.

Question 10

Summarize the major differences between a relation and a traditional file.

Answer

Relation fileTraditional file
Data organized in tables with rows and columns.Data stored in unstructured formats.
Supports structured querying with SQL.Lacks standardized querying abilities.
Allows for defining relationships between tables.No inherent support for relationships.
Offers flexibility in data storage and retrieval.Limited flexibility in data organisation.
Examples : MySQL, PostgreSQLExamples : Text files, CSV files, Excel spreadsheets

Question 11

What is the relationship between a database and a table?

Answer

A database is an organized collection of interrelated data that serves many applications, while a table is a collection of logically related records. In other words, it is a named collection of data items that represent a complete unit of information within a database.

Question 12

What is DBMS? Write names of any two DBMSs.

Answer

A Database Management System (DBMS) is a general-purpose software system that facilitates the process of defining, constructing and manipulating databases for various applications.

Examples of DBMS are MS Access, MySQL.

Question 13

How is data organized in a table?

Answer

Data in a table is organized into rows and columns. Each row represents a record or tuple, while each column represents an attribute or field of the data.

Question 14

What is a primary key? What is its function in a table?

Answer

A primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table.

Its function in a table is to uniquely identify tuples or rows and prevent the entry of duplicate rows, thus ensuring data integrity.

Question 15

Write SQL queries to perform the following based on the table Product having fields as (prod_id, prod_name, quantity, unit_rate, price, city)

(a) Display those records from table Product where prod_id is more than 100.

(b) List records from table Product where prod_name is 'Almirah'.

(c) List all those records whose price is between 200 and 500.

(d) Display the product names whose quantity is not given.

(e) Show the detailed records in the table Product.

Answer

(a)

SELECT * FROM PRODUCT
WHERE prod_id > 100;

(b)

SELECT * FROM PRODUCT
WHERE prod_name = 'Almirah';

(c)

SELECT * FROM PRODUCT
WHERE price BETWEEN 200 AND 500; 

(d)

SELECT prod_name
FROM PRODUCT
WHERE quantity IS NULL;

(e)

SELECT * FROM PRODUCT;

Question 16(a)

Define Database.

Answer

A database is defined as a collection of interrelated data stored together to serve multiple applications.

Question 16(b)

Define Data inconsistency.

Answer

Mismatched multiple copies of same data is known as data inconsistency.

Question 16(c)

Define Primary Key.

Answer

A primary key is a set of one or more attributes that can uniquely identify tuples within the relation.

Question 16(d)

Define Candidate Key.

Answer

All attribute combinations inside a relation that can serve as primary key are candidate keys as they are candidates for the primary key position.

Question 16(e)

Define Alternate Key.

Answer

A candidate key that is not the primary key is called an alternate key.

Question 16(f)

Define Foreign Key.

Answer

A non-key attribute, whose values are derived from the primary key of some other table, is known as foreign key in its current table.

Question 17

Match the following clauses with their respective functions.

Column 1Column 2
ALTERInsert the values in a table
UPDATERestrictions on columns
DELETETable definition
INSERT INTOChange the name of a column
CONSTRAINTSUpdate existing information in a table
DESCRIBEDelete an existing row from a table
CREATECreate a database

Answer

Column 1Column 2
ALTERChange the name of a column
UPDATEUpdate existing information in a table
DELETEDelete an existing row from a table
INSERT INTOInsert the values in a table
CONSTRAINTSRestrictions on columns
DESCRIBETable definition
CREATECreate a database

Question 18

Write SQL commands for (a) to (e) on the basis of PRODUCTS relation given below:

Table: PRODUCTS

PCODEPNAMECOMPANYPRICESTOCKMANUFACTUREWARRANTY
P001TVBPL100002002018-01-123
P002TVSONY120001502017-03-234
P003PCLENOVO390001002018-04-092
P004PCCOMPAQ380001202019-06-202
P005HANDYCAMSONY180002502017-03-233

(a) To show details of all Products with stock more than 110.

(b) To list the company which gives warranty of more than 2 years.

(c) To find stock value of the BPL company where stock value is sum of the price and stock of the products.

(d) To show products from products table.

(e) To display the details of those products whose Name either ends with 'Y' or 'O'.

Answer

(a)

SELECT *
FROM PRODUCTS
WHERE STOCK > 110;
Output
+-------+----------+---------+----------+-------+-------------+----------+
| PCODE | PNAME    | COMPANY | PRICE    | STOCK | MANUFACTURE | WARRANTY |
+-------+----------+---------+----------+-------+-------------+----------+
| P001  | TV       | BPL     | 10000.00 |   200 | 2018-01-12  |        3 |
| P002  | TV       | SONY    | 12000.00 |   150 | 2017-03-23  |        4 |
| P004  | PC       | COMPAQ  | 38000.00 |   120 | 2019-06-20  |        2 |
| P005  | HANDYCAM | SONY    | 18000.00 |   250 | 2017-03-23  |        3 |
+-------+----------+---------+----------+-------+-------------+----------+

(b)

SELECT DISTINCT COMPANY
FROM PRODUCTS
WHERE WARRANTY > 2;
Output
+---------+
| COMPANY |
+---------+
| BPL     |
| SONY    |
+---------+

(c)

SELECT COMPANY, (PRICE + STOCK) AS STOCK_VALUE 
FROM PRODUCTS WHERE COMPANY = 'BPL';
Output
+---------+-------------+
| COMPANY | STOCK_VALUE |
+---------+-------------+
| BPL     |       10200 |
+---------+-------------+

(d)

SELECT * FROM PRODUCTS;
Output
+-------+----------+---------+-------+-------+-------------+----------+
| PCODE | PNAME    | COMPANY | PRICE | STOCK | MANUFACTURE | WARRANTY |
+-------+----------+---------+-------+-------+-------------+----------+
| P001  | TV       | BPL     | 10000 |   200 | 2018-01-12  |        3 |
| P002  | TV       | SONY    | 12000 |   150 | 2017-03-23  |        4 |
| P003  | PC       | LENOVO  | 39000 |   100 | 2018-04-09  |        2 |
| P004  | PC       | COMPAQ  | 38000 |   120 | 2019-06-20  |        2 |
| P005  | HANDYCAM | SONY    | 18000 |   250 | 2017-03-23  |        3 |
+-------+----------+---------+-------+-------+-------------+----------+

(e)

SELECT * FROM PRODUCTS 
WHERE PNAME LIKE '%Y' OR PNAME LIKE '%O';

Question 19

What are DDL and DML?

Answer

The Data Definition Language (DDL) part of SQL permits the creation or deletion of database tables. It also defines indices (keys), specifies links between tables, and imposes constraints on tables. DDL contains necessary statements for creating, manipulating, altering, and deleting tables. Data Manipulation Language (DML) is a part of SQL that helps users manipulate data. It contains necessary statements for inserting, updating, and deleting data.

Question 20

Differentiate between primary key and candidate key in a relation.

Answer

Primary keyCandidate key
A primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table.A candidate key refers to all the attributes in a relation that are candidates or are capable of becoming a primary key.
There can be only one primary key per table.A table can have multiple candidate keys. Only one of them is chosen as the primary key.

Question 21

What do you understand by the terms Cardinality and Degree of a relation in relational database?

Answer

The number of tuples/rows in a relation is called the Cardinality of the relation.

The number of attributes/columns in a relation is called the Degree of the relation.

Question 22

Differentiate between DDL and DML. Mention the two commands for each category.

Answer

Data Definition Language (DDL)Data Manipulation Language (DML)
DDL provides a set of definitions to specify the storage structure and access methods used by the database system.DML is a language that enables users to access or manipulate data as organized by the appropriate data model.
DDL commands are used to perform tasks such as creating, altering, and dropping schema objects. They are also used to grant and revoke privileges and roles, as well as for maintenance commands related to tables.DML commands are used to retrieve, insert, delete, modify data stored in the database.
Examples of DDL commands are CREATE, ALTER, DROP, GRANT, ANALYZE etc.Examples of DML commands are INSERT, UPDATE, DELETE, SELECT etc.

Question 23

Consider the given table and answer the questions.

Table: SchoolBus

RtnoArea_CoveredCapacityNoofstudentsDistanceTransporterCharges
1Vasant Kunj10012010Shivam Travels3500
2Hauz Khas808010Anand Travels3000
3Pitampura605530Anand Travels4500
4Rohini1009035Anand Travels5000
5Yamuna Vihar506020Bhalla Travels3800
6Krishna Nagar708030Yadav Travels4000
7Vasundhara10011020Yadav Travels3500
8Paschim Vihar404020Speed Travels3200
9Saket12012010Speed Travels3500
10Janakpuri10010020Kisan Tours3500

(a) To show all information of schoolbus where capacity is more than 70.

(b) To show area_covered for buses covering more than 20 km., but charges less than 4000.

(c) To display the details of school Bus having no. of students less than 50.

(d) To show Rtno, Area_Covered and Average cost per student for all routes where average cost per student is—Charges/Noofstudents.

(e) Add a new record with the following data:

(11, "Motibagh", 35, 32, 10, "Kisan Tours", 3500)

Answer

(a)

SELECT * FROM SCHOOLBUS WHERE CAPACITY > 70;
Output
+------+--------------+----------+--------------+----------+----------------+---------+
| RTNO | AREA_COVERED | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER    | CHARGES |
+------+--------------+----------+--------------+----------+----------------+---------+
|    1 | VASANT KUNJ  |      100 |          120 |       10 | SHIVAM TRAVELS |    3500 |
|    2 | HAUZ KHAS    |       80 |           80 |       10 | ANAND TRAVELS  |    3000 |
|    4 | ROHINI       |      100 |           90 |       35 | ANAND TRAVELS  |    5000 |
|    7 | VASUNDHARA   |      100 |          110 |       20 | YADAV TRAVELS  |    3500 |
|    9 | SAKET        |      120 |          120 |       10 | SPEED TRAVELS  |    3500 |
|   10 | JANAKPURI    |      100 |          100 |       20 | KISAN TOURS    |    3500 |
+------+--------------+----------+--------------+----------+----------------+---------+

(b)

SELECT AREA_COVERED FROM SCHOOLBUS WHERE DISTANCE > 20 AND CHARGES < 4000;

(c)

SELECT * FROM SCHOOLBUS WHERE NOOFSTUDENTS < 50;
Output
+------+---------------+----------+--------------+----------+---------------+---------+
| RTNO | AREA_COVERED  | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER   | CHARGES |
+------+---------------+----------+--------------+----------+---------------+---------+
|    8 | PASCHIM VIHAR |       40 |           40 |       20 | SPEED TRAVELS |    3200 |
+------+---------------+----------+--------------+----------+---------------+---------+

(d)

SELECT RTNO, AREA_COVERED, (CHARGES/NOOFSTUDENTS) AS AVERAEG_COST 
FROM SCHOOLBUS;
Output
+------+---------------+--------------+
| RTNO | AREA_COVERED  | AVERAEG_COST |
+------+---------------+--------------+
|    1 | VASANT KUNJ   |      29.1667 |
|    2 | HAUZ KHAS     |      37.5000 |
|    3 | PITAMPURA     |      81.8182 |
|    4 | ROHINI        |      55.5556 |
|    5 | YAMUNA VIHAR  |      63.3333 |
|    6 | KRISHNA NAGAR |      50.0000 |
|    7 | VASUNDHARA    |      31.8182 |
|    8 | PASCHIM VIHAR |      80.0000 |
|    9 | SAKET         |      29.1667 |
|   10 | JANAKPURI     |      35.0000 |
+------+---------------+--------------+

(e)

INSERT INTO SCHOOLBUS 
VALUES(11, "MOTIBAGH", 35, 32, 10, "KISAN TOURS", 3500);

Question 24

Write SQL commands for (a) to (d) and write the output for (e) on the basis of the following table:

Table: FURNITURE

S NOITEMTYPEDATEOFSTOCKPRICEDISCOUNT
1WhiteLotusDoubleBed2002-02-23300025
2PinkfeathersBabyCot2002-01-29700020
3DolphinBabyCot2002-02-19950020
4DecentOfficeTable2002-02-012500030
5ComfortzoneDoubleBed2002-02-122500030
6DonaldBabyCot2002-02-24650015

(a) To list the details of furniture whose price is more than 10000.

(b) To list the Item name and Price of furniture whose discount is between 10 and 20.

(c) To delete the record of all items where discount is 30.

(d) To display the price of 'Babycot'.

(e) Select Distinct Type from Furniture;

Answer

(a)

SELECT * FROM FURNITURE
WHERE PRICE > 10000;
Output
+----+-------------+-------------+-------------+-------+----------+
| NO | ITEM        | TYPE        | DATEOFSTOCK | PRICE | DISCOUNT |
+----+-------------+-------------+-------------+-------+----------+
|  4 | Decent      | OfficeTable | 2002-02-01  | 25000 |       30 |
|  5 | Comfortzone | DoubleBed   | 2002-02-12  | 25000 |       30 |
+----+-------------+-------------+-------------+-------+----------+

(b)

SELECT ITEM, PRICE
FROM FURNITURE
WHERE DISCOUNT BETWEEN 10 AND 20;
Output
+--------------+-------+
| ITEM         | PRICE |
+--------------+-------+
| Pinkfeathers |  7000 |
| Dolphin      |  9500 |
| Donald       |  6500 |
+--------------+-------+

(c)

DELETE FROM FURNITURE WHERE DISCOUNT = 30;

(d)

SELECT PRICE
FROM FURNITURE
WHERE TYPE = 'BabyCot';
Output
+-------+
| PRICE |
+-------+
|  7000 |
|  9500 |
|  6500 |
+-------+

(e)

SELECT DISTINCT Type FROM Furniture;
Output
+-------------+
| Type        |
+-------------+
| DoubleBed   |
| BabyCot     |
| OfficeTable |
+-------------+

Question 25

Write SQL commands for (a) to (d) and write the output for (e) and (f) on the basis of given table GRADUATE:

Table: GRADUATE

S NONAMESTIPENDSUBJECTAVERAGERANK
1KARAN400PHYSICS681
2RAJ450CHEMISTRY681
3DEEP300MATHS622
4DIVYA350CHEMISTRY631
5GAURAV500PHYSICS701
6MANAV400CHEMISTRY552
7VARUN250MATHS641
8LIZA450COMPUTER681
9PUJA500PHYSICS621
10NISHA300COMPUTER572

(a) List the names of those students who have obtained rank 1.

(b) Display a list of all those names whose average is greater than 65.

(c) Display the names of those students who have opted for computer as a subject with average of more than 60.

(d) List the names of all students whose name ends with 'a'.

(e) SELECT * FROM GRADUATE WHERE Subject = "Physics";

(f) SELECT DISTINCT RANK FROM GRADUATE;

Answer

(a)

SELECT NAME FROM GRADUATE 
WHERE `RANK` = 1;
Output
+--------+
| NAME   |
+--------+
| KARAN  |
| RAJ    |
| DIVYA  |
| GAURAV |
| VARUN  |
| LIZA   |
| PUJA   |
+--------+

(b)

SELECT NAME
FROM GRADUATE
WHERE AVERAGE > 65;
Output
+--------+
| NAME   |
+--------+
| KARAN  |
| RAJ    |
| GAURAV |
| LIZA   |
+--------+

(c)

SELECT NAME
FROM GRADUATE
WHERE SUBJECT = 'COMPUTER' AND AVERAGE > 60;
Output
+------+
| NAME |
+------+
| LIZA |
+------+

(d)

SELECT NAME FROM GRADUATE 
WHERE NAME LIKE "%a";
Output
+-------+
| NAME  |
+-------+
| DIVYA |
| LIZA  |
| PUJA  |
| NISHA |
+-------+

(e)

SELECT * FROM GRADUATE WHERE Subject = "Physics";
Output
+-------+--------+---------+---------+---------+------+
| S.No. | name   | stipend | subject | average | RANK |
+-------+--------+---------+---------+---------+------+
|     1 | KARAN  |     400 | PHYSICS |      68 |    1 |
|     5 | GAURAV |     500 | PHYSICS |      70 |    1 |
|     9 | PUJA   |     500 | PHYSICS |      62 |    1 |
+-------+--------+---------+---------+---------+------+

(f) Since 'RANK' is a reserved keyword in SQL, we encounter an error while running this query. To avoid such errors, we can enclose the column name 'RANK' in backticks to treat it as a literal identifier.

The corrected query is :

SELECT DISTINCT `RANK` FROM GRADUATE;
Output
+------+
| RANK |
+------+
|    1 |
|    2 |
+------+

Question 26(a)

What is the difference between Candidate key and Alternate key?

Answer

Alternate KeyCandidate Key
A candidate key refers to all the attributes in a relation that are candidates or are capable of becoming a primary key.Any attribute which is capable of becoming a primary key but is not a primary key is called an alternate key.

Question 26(b)

What is the degree and cardinality of a table having 10 rows and 5 columns?

Answer

The degree of a table is 5 and the cardinality of a table is 10.

Question 26(c)

For the given table, do as directed:

Table: STUDENT

Column nameData typeSizeConstraint
ROLLNOInteger4Primary Key
SNAMEVarchar25Not Null
GENDERChar1Not Null
DOBDateNot Null
FEESInteger4Not Null
HOBBYVarchar15Null

(i) Write SQL query to create the table.

(ii) Write SQL query to increase the size of SNAME to hold 30 characters.

(iii) Write SQL query to remove the column HOBBY.

(iv) Write SQL query to insert a row in the table with any values of your choice that can be accommodated there.

Answer

(i)

CREATE TABLE STUDENT(
    ROLLNO INT(4) PRIMARY KEY,
    SNAME VARCHAR(25) NOT NULL,
    GENDER CHAR(1) NOT NULL,
    DOB DATE NOT NULL,
    FEES INT(4) NOT NULL,
    HOBBY VARCHAR(15)
);

(ii)

ALTER TABLE STUDENT MODIFY SNAME VARCHAR(30);

(iii)

ALTER TABLE STUDENT DROP HOBBY;

(iv)

INSERT INTO STUDENT(ROLLNO, SNAME, GENDER, DOB, FEES, HOBBY)
VALUES (1, 'ANANYA', 'F', '2000-01-01', 5000, 'COOKING');

Question 27

Write SQL queries based on the following tables:

Table: PRODUCT

P_IDProductNameManufacturerPriceDiscount
TP01Talcum PowderLAK40Null
FW05Face WashABC455
BS01Bath SoapABC55Null
SH06ShampooXYZ12010
FW12Face WashXYZ95Null

Table: CLIENT

C_IDClientNameCityP_ID
01Cosmetic ShopDelhiTP01
02Total HealthMumbaiFW0S
03Live LifeDelhiBS01
04Pretty WomanDelhiSH06
05DreamsDelhiFW12

(i) Write SQL query to display ProductName and Price for all products whose Price is in the range 50 to 150.

(ii) Write SQL Query to display details of products whose manufacturer is either XYZ or ABC.

(iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are not given any discount.

(iv) Write SQL query to display ProductName and price for all products.

(v) Which column is used as Foreign Key and name the table where it has been used as Foreign key?

Answer

(i)

SELECT ProductName, Price 
FROM PRODUCT
WHERE Price BETWEEN 50 AND 150;
Output
+-------------+-------+
| ProductName | Price |
+-------------+-------+
| Bath Soap   |    55 |
| Face Wash   |    95 |
| Shampoo     |   120 |
+-------------+-------+

(ii)

SELECT * FROM PRODUCT
WHERE Manufacturer = 'XYZ' OR Manufacturer = 'ABC';
Output
+------+-------------+--------------+-------+----------+
| P_ID | ProductName | Manufacturer | Price | Discount |
+------+-------------+--------------+-------+----------+
| BS01 | Bath Soap   | ABC          |    55 |     NULL |
| FW05 | Face Wash   | ABC          |    45 |        5 |
| FW12 | Face Wash   | XYZ          |    95 |     NULL |
| SH06 | Shampoo     | XYZ          |   120 |       10 |
+------+-------------+--------------+-------+----------+

(iii)

SELECT ProductName, Manufacturer, Price
FROM PRODUCT
WHERE Discount IS NULL;
Output
+---------------+--------------+-------+
| ProductName   | Manufacturer | Price |
+---------------+--------------+-------+
| Bath Soap     | ABC          |    55 |
| Face Wash     | XYZ          |    95 |
| Talcum Powder | LAK          |    40 |
+---------------+--------------+-------+

(iv)

SELECT ProductName, Price FROM PRODUCT;
Output
+---------------+-------+
| PRODUCTNAME   | PRICE |
+---------------+-------+
| BATH SOAP     |    40 |
| FACE WASH     |    45 |
| FACE WASH     |    55 |
| SHAMPOO       |   120 |
| TALCUM POWDER |    95 |
+---------------+-------+

(v) The column used as a Foreign Key is P_ID in the CLIENT table, and it references the P_ID column in the PRODUCT table.

Question 28

Write SQL queries based on the table given below:

Table: HOSPITAL

S NONameAgeDepartmentDatofadmChargesSex
1Arpit62Surgery1998-01-21300M
2Zareena22ENT1997-12-12250F
3Kareem32Orthopaedic1998-02-19200M
4Arun12Surgery1998-01-11300M
5Zubin30ENT1998-01-12250M
6Ketaki16ENT1998-02-24250F
7Ankita29Cardiology1998-02-20800F
8Zareen45Gynaecology1998-02-22300F
9Kush19Cardiology1998-01-13800M
10Shilpa23Nuclear Medicine1998-02-21400F

(i) To list the names of all the patients admitted after 15/01/98.

(ii) To list the names of female patients who are in ENT department.

(iii) To list names of all patients with their date of admission.

(iv) To display Patient’s Name, Charges, Age for only female patients.

Answer

(i)

SELECT NAME
FROM HOSPITAL
WHERE DATEOFADM > '1998-01-15';
Output
+--------+
| NAME   |
+--------+
| Arpit  |
| Kareem |
| Ketaki |
| Ankit  |
| Zareen |
| Shilpa |
+--------+

(ii)

SELECT NAME
FROM HOSPITAL
WHERE SEX = 'F' AND DEPARTMENT = 'ENT';
Output
+---------+
| NAME    |
+---------+
| Zareena |
| Ketaki  |
+---------+

(iii)

SELECT NAME, DATEOFADM FROM HOSPITAL;
Output
+---------+------------+
| NAME    | DATEOFADM  |
+---------+------------+
| Arpit   | 1998-01-21 |
| Zareena | 1997-12-12 |
| Kareem  | 1998-02-19 |
| Arun    | 1998-01-11 |
| Zubin   | 1998-01-12 |
| Ketaki  | 1998-02-24 |
| Ankit   | 1998-02-20 |
| Zareen  | 1998-02-22 |
| Kush    | 1998-01-13 |
| Shilpa  | 1998-02-21 |
+---------+------------+

(iv)

SELECT NAME, CHARGES, AGE
FROM HOSPITAL
WHERE SEX = 'F';
Output
+---------+---------+-----+
| NAME    | CHARGES | AGE |
+---------+---------+-----+
| Zareena |     250 |  22 |
| Ketaki  |     250 |  16 |
| Ankit   |     800 |  29 |
| Zareen  |     300 |  45 |
| Shilpa  |     400 |  23 |
+---------+---------+-----+
PrevNext