Class 12 It Chapter 1 RDBMS Concepts in MYSQL

By Parimol / Class 12 It-ITes

Class 12 It Chapter 1 RDBMS Concepts in MYSQL Solutions English Medium As Per AHSEC New Syllabus to each chapter is provided in the list so that you can easily browse through different chapters Class 12 It Chapter 1 RDBMS Concepts in MYSQL Question Answer and select need one. Class 12 It Chapter 1 RDBMS Concepts in MYSQL Notes Download PDF. AHSEC Class 12 Elective It-ITes Question Answer English Medium.

Class 12 It Chapter 1 RDBMS Concepts in MYSQL

Join Telegram channel

Also, you can read the NCERT book online in these sections Solutions by Expert Teachers as per Central Board of Secondary Education (CBSE) Book guidelines. AHSEC Class 12 Elective It-ITes Textual Solutions are part of All Subject Solutions. Here we have given AHSEC Class 12 It Chapter 1 RDBMS Concepts in MYSQL Textbook Solutions English Medium for All Chapters, You can practice these here.

RDBMS Concepts in MYSQL

Chapter: 1

SESSION 1: RDBMS CONCEPTS

Check Your Progress

A. Multiple choice questions:

1. A database is a_____________.

(a) Organized collection of information that cannot be accessed, updated, and managed.

(b) Collection of data or information without organizing.

(d) Organized collection of data that cannot be updated.

WhatsApp Group Join Now

Telegram Group Join Now

Instagram Join Now

Ans: (c) Organized collection of data or information that can be accessed, updated, and managed.

2. Which of the following is not a valid SQL type?

(a) Float.

(b) Numeric.

(d) Character.

Ans: (c) Decimal.

3. In DBMS, table is known as ___________ and row is known as __________.

(a) Relation, tuple.

(b) Tuple, tuple.

(d) Relation, relation.

Ans: (a) Relation, tuple.

4. In any table, the data types describe the kind of ___________ that it can contain.

(a) Table.

(b) Data.

(d) Column.

Ans: (b) Data.

5. The SQL statement used to select data items from the database is-

(a) SELECT.

(b) USE.

(d) CREATE.

Ans: (b) USE.

6. The database can be renamed using __________ SQL statement.

(a) CREATE DATABASE.

(b) RENAME DATABASE.

(d) SELECT DATABASE.

Ans: (b) RENAME DATABASE.

7. The syntax used to show all databases is-

(a) USE DATABASES.

(b) SELECT DATABASES.

(d) DISPLAY DATABASE.

Ans: (c) SHOW DATABASES.

8. In a database table the field which uniquely identifies each row in the table is known as ________________.

(a) Primary key.

(b) Unique key.

(d) Foreign key.

Ans: (a) Primary key.

9. Foreign key is a ____________ key in another table.

(a) Primary.

(b) Unique.

(d) Candidate key.

Ans: (a) Primary.

10. The multiple columns that are used as primary key is known as-

(a) Unique key.

(b) Composite key.

(d) Candidate key.

Ans: (b) Composite key.

11. Which of the following key is used to link between two tables.

(a) Primary.

(b) Foreign.

(d) Unique.

Ans: (b) Foreign.

12. A primary key cannot be-

(a) Zero.

(b) Foreign key.

(d) NULL.

Ans: (d) NULL.

B. Fill in the blanks:

1. In DBMS, table is known as _________ and row is known as _________.

Ans: Relation, Tuple.

2. Organized collection of data or information for accessing, updating and management is known as __________ .

Ans: DBMS.

3. A relational database consists of a collection of _____________.

Ans: Tables.

4. To see all available databases in MySQL; __________ command is used.

Ans: Show database.

5. Data Definition language is the language which is used to defining the __________ of relation.

Ans: Structure.

6. In order to build a link between two tables, ___________ is used.

Ans: Foreign key.

7. In order to make multiple columns as a Primary Key, ___________ can be used.

Ans: Composite Key.

8. Foreign key is a field in a table that is __________ in another table.

Ans: Primary Key.

9. A Key which uniquely identifies each row in the table is known as __________.

Ans: Primary Key.

10. A foreign key can be ___________ or _______________. (null, duplicate).

Ans: Null, duplicate.

C. State whether True or False:

1. DBMS is an interface between Database application and database.

Ans: True.

2. Using the SQL statement RENAME DATABASE; a database can be renamed.

Ans: True.

3. To see all existing databases; SHOW DATABASES; syntax is used.

Ans: True.

4. A Primary Key is basically a Column or Columns.

Ans: True.

5. To make a link between two tables, we can use foreign key constraints.

Ans: True.

6. A Primary Key can be NULL.

Ans: False.

7. A Foreign Key cannot be Duplicate.

Ans: False.

8. If multiple columns are used as Primary Key, it is known as Composite Key.

Ans: True.

9. There could be two Primary keys constraints in a single table.

Ans: False.

10. A Foreign Key cannot have NULL value.

Ans: False.

D. Short answer questions.

1. What is file system? Write down limitations of file system.

Ans: In computers, any contents are stored in the form of a file, which is opened and viewed in the respective software. In a computer, a file is a container to store data or information. These files are stored on the storage device of the computer, such as hard disk drive or pen drive. The student data can be stored in the document file or spreadsheet file. These files stored on the computer can be accessed quickly. To process or manipulate this data, it is required to write the program in computer programming languages. The various operations can be performed through computer programming. It includes searching, sorting, computing the percentage of marks, number of days attendance, retrieving the data.

Limitations of a File System: There are certain limitations to maintain and manipulate such type of data when there are several hundreds or thousands of students. It also becomes difficult to maintain the number of files as it increases the volume when data grows. There are certain limitations of file system to maintain such type of data.

The limitations of file system are:

(i) Difficulty in Access: Files themselves do not provide any mechanism to retrieve data. Data maintained in a file system are accessed through application programs. While writing such programs, the developer may not anticipate all the possible ways in which data may be accessed. So, sometimes it is difficult to access data in the required format and one has to write application program to access data.

(ii) Data Redundancy: Redundancy means same data are duplicated in different files. For example, if we are maintaining students data for the various purpose then data such as student names are maintained in different files. The common data in all such files are required to be maintained number of times. This may cause the data redundancy which is difficult to avoid in a file system. Redundancy leads to excess storage use and may cause data inconsistency also.

(iii) Data Inconsistency: Data inconsistency occurs when same data maintained in different places do not match. If a student wants to get changed the spelling in name, it needs to be changed in the number of files where it appears. Likewise, if a student leaves school, the details need to be deleted from these files. As the files are being maintained by different people, the changes may not happen in one of the files. In that case, the student name will be different (inconsistent) in both the files.

(iv) Data Isolation: Although these files are maintained for the students of the same class, but there is no link or mapping between these files. The school will have to write separate programs to access these files. This is because data mapping is not supported in file system. In a more complex system where data files are generated by different person at different times, files being created in isolation may be of different formats. In such case, it is difficult to write new application programs to retrieve data from different files.

(v) Data Dependence: Data are stored in a specific format or structure in a file. If the structure or format itself is changed, all the existing application programs accessing that file also need to be changed. Otherwise, the programs may not work correctly. This is data dependency. Hence, updating the structure of a data file requires modification in all the application programs accessing that file.

(vi) Controlled Data Sharing: There can be different category of users like teacher, office staff and parents. Ideally, not every user should be able to access all the data. It means different types of users should be given different types of access, such as read only. It is very difficult to enforce this kind of access control in a file system while accessing files through application programs.

2. Why foreign keys are allowed to have NULL values? Explain with an example.

Ans: A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation. This means that any attribute of a relation (referencing), which is used to refer contents from another (referenced) relation, becomes foreign key if it refers to the primary key of referenced relation.

In some cases, foreign key can take NULL value if it is not the part of primary key of the foreign table. The relation in which the referenced primary key is defined is called primary relation or master relation.

3. What are the limitations of file system and how that are overcome by DBMS?

Ans: There are certain limitations of file system to maintain such type of data. The limitations of file system are:

(i) Difficulty in Access.

(ii) Data Redundancy.

(iii) Data Inconsistency.

(iv) Data Isolation.

(v) Data Dependence.

(vi) Controlled Data Sharing.

Limitations faced in file system can be overcome by storing the data in a database where data are logically related. A database management systems (DBMSs) is used as an interface to manage databases. DBMS allows to create a database, store, manage, update/modify and retrieve data from that database by users or application programs. DBMS is used to provide an effective method of performing database operations, troubleshooting database issues, and restricting data access.

4. What is database schema?

Ans: A database schema is a set of schema for a database’s relations. It consists of table with all attributes with their data types and constraints if any. It also represents the relationships among the tables. It is also used to visualize the logical architecture of database and how the data are organized in a database.

5. What is data redundancy and its associated problems?

Ans: Data Redundancy: Redundancy means same data are duplicated in different files. For example, if we are maintaining students data for the various purpose then data such as student names are maintained in different files. The common data in all such files are required to be maintained number of times. This may cause the data redundancy which is difficult to avoid in a file system. Redundancy leads to excess storage use and may cause data inconsistency also.

6. How data redundancy problem is solved in DBMS?

Ans: Relational DBMS (RDBMS) is used to store data in related tables. Rows and columns of a table are called tuples and attributes respectively. A table is referred to as a relation. Restrictions on data stored in a RDBMS is applied by use of keys such as Candidate Key, Primary Key, Composite Primary Key, Foreign Key. Primary key in a relation is used for unique identification of tuples. Foreign key is used to relate two tables or relations.

A tuple is a unique entity whereas attribute values can be duplicate in the table.

7. What is MYSQL and its features?

Ans: MySQL is an open source RDBMS software which can be easily downloaded from its official website https://dev.mysql.com/downloads. After installing MySQL, start MySQL service. It is shown in Figure 2.2. The appearance of mysql> prompt as shown below. MySQL is ready to accept SQL statements on this prompt.

Following are some important features to be kept in mind while using SQL:

(i) SQL is not case insensitive. For example, the column names ‘salary’ and ‘SALARY’ are the same for SQL.

(ii) SQL statements terminates with a semicolon (;). In multi-line SQL statements, the “;” is not required after the first line. Just press the Enter key to continue on the next line. The prompt mysql> then changes to “->”, indicating that statement is continued to the next line. Only at the end of SQL statement, put “;” and press Enter.

(iii) Only at the end of SQL statement, put “;” and press Enter.

(iv) It is possible to manage multiple databases on a single computer. USE command is used to select the specific database. After selecting the database, it is possible to create tables or querying data from this database.

(v) In LINUX OS environment, names for database and tables are case-sensitive whereas in WINDOWS OS, there is no such differentiation. However, as a good practice, it is suggested to write database or table name in the same letter cases that were used at the time of their creation.

8. What are various data types available in MYSQL?

Ans: Commonly used data types in MySQL are numeric types, date and time types, and string types:

(i) HAR (n): Specifies character type data of length n where n could be any value from 0 to 255. CHAR is of fixed length, means, declaring CHAR (10) implies to reserve spaces for 10 characters. If data does not have 10 characters (for example, ‘city’ has four characters), MySQL fills the remaining 6 characters with spaces padded on the right.

(ii) VARCHAR (n): Specifies character type data of length ‘n’ where n could be any value from 0 to 65535. But unlike CHAR, VARCHAR is a variable-length data type. That is, declaring VARCHAR (30) means a maximum of 30 characters can be stored but the actual allocated bytes will depend on the length of entered string. So ‘city’ in VARCHAR (30) will occupy the space needed to store 4 characters only.

(iii) INT: INT specifies an integer value. Each INT value occupies 4 bytes of storage. The range of values allowed in integer type are -2147483648 to 2147483647. For values larger than that, we have to use BIGINT, which occupies 8 bytes.

(iv) FLOAT: Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.

(v) DATE: The DATE type is used for dates in ‘YYYY-MM-DD’ format. YYYY is the 4 digits year, MM is the 2 digits month and DD is the 2 digits date. The supported range is ‘1000-01-01’ to ‘9999-12-31’.

9. Differentiate between:

(a) Database state and database schema.

Ans: The database schema along with various constraints on the data is stored by DBMS in a database catalog or dictionary, called meta-data. A meta-data is data about the data.

When we define database structure or schema, state of database is empty. After loading data, the state or snapshot of the database at any given time is the database instance. We may then retrieve data through queries or manipulate data through updation, modification or deletion. Thus, the state of database can change, and thus a database schema can have many instances at different times.

(b) Primary key and foreign key.

Ans: Primary Key: Out of one or more candidate keys, the attribute chosen by the database designer to uniquely identify the tuples in a relation is called the primary key of that relation.

Foreign Key: A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation.

(c) Degree and cardinality of a relation.

Ans: Degree: The number of attributes in a relation is called the Degree of the relation.

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

10. Explain the terms:

(a) Relation.

Ans: In relational model, tables are called relations that store data for different entities. Each relation in a relational model represents a specific type of entity.

(b) Domain.

Ans: Domain: It is a set of values from which an attribute can take a value in each row. Usually, a data type is used to specify domain for an attribute.

(c) Tuple.

Ans: Each row of data in a relation (table) is called a tuple. In a table with n columns, a tuple is a relationship between the n related values.

(d) Attribute.

Ans: Attribute: Characteristic or parameters for which data are to be stored in a relation. Simply stated, the columns of a relation are the attributes which are also referred to as fields.

(e) Degree.

Ans: The number of attributes in a relation is called the Degree of the relation.

(f) Cardinality.

Ans: The number of tuples in a relation is called the Cardinality of the relation.

(g) Primary Key.

Ans: Out of one or more candidate keys, the attribute chosen by the database designer to uniquely identify the tuples in a relation is called the primary key of that relation.

(h) Foreign Key.

Ans: A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation.

11. Describe the various integrity constraints?

Ans: Constraints are the certain types of restrictions on the data values that an attribute can have. They are used to ensure correctness of data. However, it is not mandatory to define constraints for each attribute of a table.

Commonly used SQL Constraints are:

(i) NOT NULL: Ensures that a column cannot have NULL values where NULL means missing/unknown/not applicable value.

(ii) UNIQUE: Ensures that all the values in a column are distinct/unique.

(iii) DEFAULT: A default value specified for the column if no value is provided.

(iv) PRIMARY KEY: The column which can uniquely identify each row or record in a table.

(v) FOREIGN KEY: The column which refers to value of an attribute defined as primary key in another table.

SESSION 2: STRUCTURED QUERY LANGUAGE (SQL)

Check Your Progress

A. Multiple Choice Questions:

1. Which of the following is not a valid aggregate function?

(a) Count.

(b) Compute.

(d) Max.

Ans: (b) Compute.

2. DDL stands for____________.

(a) Data Describe Language.

(b) Definition Data Language.

(d) Data Distinct Language.

Ans: (c) Data Definition Language.

3. Which of the following SQL command is used to remove data from table.

(a) Collapse.

(b) Remove.

(d) Delete.

Ans: (d) Delete.

4. The records and structure of a table may be removed or deleted from the database using which command?

(a) Remove.

(b) Delete.

(d) Truncate.

Ans: (c) Drop.

5. SQL ___________ statement can be used to delete or drop existing databases in a SQL schema.

(a) Create Database.

(b) Rename Database.

(d) Select Database.

Ans: (c) Drop Database.

6. Using DROP TABLE command in SQL.

(a) Drop the table structure.

(b) Drop the integrity constraints.

(d) All of the above.

Ans: (d) All of the above.

7. Using DROP TABLE command in SQL.

(a) Drop the table structure.

(b) Drop the integrity constraints.

(d) None of the above.

Ans: (d) None of the above.

8. TRUNCATE TABLE requires_____________.

(a) Where clause.

(b) Having clause.

(d) None of the above.

Ans: (d) None of the above.

9. Which of the following clause is used to add a Primary Key constraint after creating table.

(a) Update.

(b) Add.

(d) Join.

Ans: (c) Alter.

10. Which of the following clause is used to remove a primary key constraint.

(a) Delete.

(b) Drop.

(d) Remove.

Ans: (b) Drop.

11. Which of the following SQL statement is used to give result in sorted order.

(a) Sort by.

(b) Order.

(d) Sort.

Ans: (c) Order by.

12. Commands under DCL are____________.

(a) Grant.

(b) Revoke.

(d) None of the above.

Ans: (c) Both a and b.

13. The SQL command to retrieve table records is-

(a) Retrieve.

(b) Select.

(d) Alter.

Ans: (b) Select.

14. Which of the following operator is used for pattern matching in SQL?

(a) Between operator.

(b) Like operator.

(d) None of these.

Ans: (b) Like operator.

15. Which operator is used to check the absence of data in any column.

(a) Exists operator.

(b) Not operator.

(d) None of these.

Ans: (c) Is Null operator.

16. Which of the following keyword is used to select only unique values from any column.

(a) Distinctive.

(b) Unique.

(d) Different.

Ans: (c) Distinct.

B. Fill in the blanks:

1. SQL is divided in ___________ category.

Ans: Five.

2. The _______ command is used to see the structure of table.

Ans: Desc.

3. The _______ command is used to remove all records.

Ans: Delete.

4. The _______ command is used to add an attribute in an existing table.

Ans: Alter.

5. The _______ command is used to remove all records only from a table.

Ans: Truncate.

6. The _______ command is used to remove a attribute from a table.

Ans: Drop.

7. A view is a special kind of _____________ table.

Ans: Virtual.

8. Views can be created form _________ or more tables.

Ans: One.

9. Grant and Revoke are part of _____________ in SQL.

Ans: DCL.

10. Commit and Savepoint are part of ___________ in SQL.

Ans: TCL.

11. To sort the result of a query in descending order, we can use clause _____________.

Ans: ORDER BY DESC.

12. To extract unique values from a column, user can use ____________ clause.

Ans: DISTINCT.

C. State whether True or False:

1. INSERT clause is used to add a Foreign key constraint.

Ans: False.

2. ALTER clause is used to add a Primary key constraint after table is created.

Ans: True.

3. DROP command is used to delete the structure of a table from the database.

Ans: True.

4. Updation and deletion of records are part of DDL.

Ans: False.

5. Insert into statement is useful to insert a new field in any table.

Ans: False.

6. Aggregate functions are used to perform calculations on multiple values and returns a single value.

Ans: True.

7. Aggregate functions are mostly used with the SELECT statement.

Ans: True.

8. DML is used to create a new database objects like table and view.

Ans: False.

9. A new table can be created from existing table(s).

Ans: True.

10. The name of any tables once its created and records are inserted cannot be change.

Ans: False.

D. Short answers questions:

1. What do you understand by SQL?

Ans: Structured Query Language (SQL), is a special kind of query language used to access and manipulate data from the database. SQL is the most popular query language used by major relational database management systems (RDBMS), such as MySQL, Oracle, Informix, PostGre SQL, SQL server, MS Access, and Sybase. SQL is easy to learn as the statements comprise of descriptive English words. It is possible to interact with a database using SQL very easily. It is simply required to specify what is to be retrieved rather than how to retrieve data from the database.

2. SQL Statements are classified in how many ways?

Ans: SQL is divided into five types like DDL, DML, DQL, TCL and DCL.

3. Differentiate between DDL and DML?

Ans: DDL (Data Definition Language) includes SQL statements such as, Create table, Alter table and Drop table. Create command is used to create database and its further objects like Table, View.

DML (Data Manipulation Language) includes SQL statements such as, insert, select, update and delete.

4. Differentiate between DCL and TCL?

Ans: DCL (Data Control Language) includes the commands GRANT and REVOKE, which are used to provide rights & permissions to user.

TCL (Transaction Control Language) allows to permanently change the databases or undo the databases transactions. The COMMIT, ROLLBACK and SAVEPOINT statements come under this category.

5. What is the difference between ALTER and UPDATE command.

Ans: ALTER TABLE statement is used to make changes in the structure of a table like adding, removing column and changing datatype of column(s). It is also used to apply/remove any constraints like Primary Key, Foreign Key etc.

The update statement is used to update the existing records in a table.

6. Differentiate between DELETE and DROP command.

Ans: DELETE command is used to remove records from a table.

DROP command is used to delete the structure of a table from the database.

7. What is create statement? How many database objects can be created using this?

Ans: Database is a collection of database object such as tables, queries and views. The CREATE statement is used to create a database and its tables (relations). Before creating a database, it should be clear about the number of tables in the database, the columns (attributes) in each table along with the data type of each column.

8. Write the CREATE statement to create the following relations with given constraints. Book(ISBN (Text), Title (Text), Author (Text), PubID(Text), Price (Numeric), Pages (Numeric)).

Ans: CREATE TABLE Book

(ISBN CHAR(12) PRIMARY KEY,

Title VARCHAR(100) NOT NULL,

Author VARCHAR(50) NOT NULL,

PubID CHAR(10) NOT NULL,

Price INT NOT NULL,

Pages INT NOT NULL)

9. Modify the Book table in previous question and add one more new field Discount (Numeric).

Ans: ALTER TABLE statement is used to make changes in the structure of a table like adding, removing column and changing datatype of column(s).

Applying this rule, the exact statement is:

ALTER TABLE Book

ADD Discount INT;

10. Shyam has created one database name Mycontacts but he is not able to create new table in this database. What command should Shyam be used before creating the table?

Ans: USE command is used to select the specific database. After selecting the database, it is possible to create tables or querying data from this database.

Correct Command:

USE Mycontacts;

11. Mr. Sachin Agrawal created two tables with Course as Primary Key in Table1 and Foreign key in Table2 while inserting new row in second Table2 Mr Agrawal is not able to insert new value in the column City. What could be the possible reason for this?

Ans: Caution: While populating records in a table with foreign key, ensure that records in referenced tables are already populated.

SESSION 3: FUNCTIONS IN SQL

Check Your Progress

A. Multiple choice questions:

1. Which of the following is not an example of single row function.

(a) MATH.

(b) STRING.

(d) COUNT.

Ans: (d) COUNT.

2. Which of the following is not an example of multiple row function.

(a) MAX().

(b) MIN().

(d) COUNT(*).

Ans: (c) STRING.

3. What is the functionality of SQL COUNT?

(a) It returns the no of record of table.

(b) It returns the no of record of database.

(d) It returns the no of record of column.

Ans: (a) It returns the no of record of table.

4. Date and Time functions accept date and time value as input and return output as-

(a) Numeric.

(b) String.

(d) Any of the above.

Ans: (c) Date and Time.

5. String Functions accept character value as input and return output as-

(a) Either character or numeric values.

(b) String values.

(d) Character values.

Ans: (a) Either character or numeric values.

6. Which of the following is aggregate function in SQL.

(a) LEFT.

(b) AVG.

(d) LEN.

Ans: (b) AVG.

7. The SQL statement Select Round (47.956,-1) from Dual;

(a) Is illegal in SQL.

(b) Prints a garbage value.

(d) Prints 50.

Ans: (d) Prints 50.

8. Which of the following SQL operation cannot be performed on relations.

(a) Union.

(b) Intersection.

(d) Merge.

Ans: (d) Merge.

9. Which of the following is used to join two tables on equality condition on the common attribute.

(a) Join with ON clause.

(b) Natural Join in FROM clause.

(d) Natural Join.

Ans: (c) Any of a or b.

10. What will be the Cartesian product of the two relations having 4 rows and 3 columns for first relation and 3 rows and 4 columns in second relation.

(a) Degree 7 cardinality 12.

(b) Degree 6 cardinality 16.

(d) Degree 9 cardinality 16.

Ans: (d) Degree 9 cardinality 16.

B. Fill in the blanks:

1. Single row functions are applied on a single __________ and return a single value.

Ans: Value.

2. Aggregate functions work on a __________ as a whole and return a single value.)

Ans: Table.

3. Math Functions accept numeric value as input and return a _________ value as a result.

Ans: Numeric.

4. MONTH (date) returns the month in ___________ form from the date.

Ans: Numeric.

5. By default, the order by clause lists items in __________ order.

Ans: Ascending.

6. INSTR (string, substring) returns the position of the _________ of the substring in the given string.)

Ans: First occurrence.

7. MID (string, pos, n) returns a substring of size __________ starting from the specified position _________ of the string. (n, pos).

Ans: n, pos.

8. LTRIM (string) returns the given string after removing __________ white space characters.

Ans: Leading.

9. TRIM (string) returns the given string after removing both __________ and _________ white space characters.

Ans: Leading, Trailing.

10. The _________ operation is used to get common tuples from two tables.

Ans: Intersection.

C. State True or False:

1. Aggregate functions are also called Scalar functions.

Ans: False.

2. A function always returns a single value.

Ans: True.

3. Functions can be applied to work on single or multiple records of a table.

Ans: True.

4. INSTR (string, substring) returns 0, if the substring is not present in the string.

Ans: True.

5. If n is not specified MID (string, pos, n), it returns the substring from the position 1 till end of the string.

Ans: False.

6. RTRIM (string) returns the given string after removing leading white space characters.

Ans: False.

7. NOW() returns the current system date and time.

Ans: True.

8. Union operation eliminates the duplicate rows.

Ans: True.

9. Cartesian product operation combines tuples from two relations.

Ans: True.

10. Join statement is used to combine two tables on a specified condition.

Ans: True.

C. Short answer questions:

1. Differentiate between single row functions and aggregate functions.

Ans: Single Row Functions: Single Row Functions are also known as Scalar functions. Single row functions are applied on a single value and return a single value. These are used in SELECT, WHERE, and ORDER BY clause. MATH, STRING and DATE functions are examples of single row functions.

Aggregate functions: Aggregate functions are also called Multiple Row functions. These functions work on a set of records as a whole and return a single value for each column of the records on which the function is applied. These are used with SELECT clause only. MAX( ), MIN( ), AVG( ), SUM( ), COUNT( ) and COUNT() are examples of multiple row functions.

2. List the single row functions with example.

Ans: String functions can perform various operations on alphanumeric data … Some of the string functions with examples are given below:

(i) UCASE(string) OR UPPER(string): Converts string into uppercase.

(ii) LCASE(string) OR LOWER(string): Converts string into lowercase.

(iii) MID(string, pos, n) OR SUBSTRING(string, pos, n) OR SUBSTR(string, pos, n) – Returns a substring of size n starting from the specified position (pos) of the string. If n is not specified, it returns the substring from the position pos till end of the string.

(iv) LENGTH(string): Returns the number of characters in the specified string.

(v) LEFT(string, N): Returns N number of characters from the left side of the string.

(vi) RIGHT(string, N): Returns N number of characters from the right side of the string.

(vii) INSTR(string, substring): Returns the position of the first occurrence of the substring in the given string. Returns 0 if the substring is not present.

(viii) LTRIM(string): Returns the given string after removing leading white space characters.

(ix) RTRIM(string): Returns the given string after removing trailing white space characters.

(x) TRIM(string): Returns the given string after removing both leading and trailing white space characters.

3. Differentiate between TRIM( ), LTRIM( ) and RTRIM( ) functions.

Ans: LTRIM(string): Returns the given string after removing leading white space characters.

RTRIM(string): Returns the given string after removing trailing white space characters.

TRIM(string): Returns the given string after removing both leading and trailing white space characters.

4. Demonstrate the use of LCASE( ) and UCASE( ) function with example.

Ans: UCASE(string) OR UPPER(string) – Converts string into uppercase.

LCASE(string) OR LOWER(string) – Converts string into lowercase.

Example:

SELECT UCASE(‘computer’); — Output: COMPUTER

SELECT LCASE(‘SQL’); — Output: sql

5. List the date functions with example.

Ans: Some of the date and time functions with examples are given below:

(i) NOW(): It returns the current system date and time.

(ii) DATE(): It returns the date part from the given date/time expression.

(iii) MONTH(date): It returns the month in numeric form from the date.

(iv) MONTHNAME(date): It returns the month name from the specified date.

(v) YEAR(date): It returns the year from the date.

(vi) DAY(date): It returns the day part from the date.

(vii) DAYNAME(date): It returns the name of the day from the date.

6. What is the difference between NOW( ) and DATE( ) function?

Ans: NOW(): It returns the current system date and time.

DATE(): It returns the date part from the given date/time expression.

7. Demonstrate the difference between SUM( ) and AVG( ) function?

Ans: SUM( ) function returns the total sum of a numeric column.

AVG( ) function returns the average value of a numeric column.

8. A table Student has 4 rows and 2 column and another table has 3 rows and 4 columns. How many rows and columns will be there if we obtain the Cartesian product of these two tables?

Ans: Cartesian product of two relations with m rows and n rows results in m × n rows and (a + b) columns where a and b are the number of attributes in both tables.

So, Cartesian product will have 12 rows and 6 columns.

9. What will be the output of following SQL functions.

(a) Select pow (3,2);

(b) Select round (342.9234, 2);

(c) Select length (‘Vocational Education’);

(d) Select year (‘1978/08/17’), month (‘1978/08/17’), day (‘1978/08/17’), monthname (‘1978/08/17’);

(e) Select left (‘Central’, 3), right (‘Institute’, 4), mid (‘Vocational’, 3, 4), substr (‘Education’, 3);

Ans: (a) Select pow(3,2); → 9

(b) Select round(342.9234, 2); → 342.92

(d) Select year(‘1978/08/17’), month(‘1978/08/17’), day(‘1978/08/17’), monthname(‘1978/08/17’); → 1978, 8, 17, August

(e) Select left(‘Central’,3), right(‘Institute’,4), mid(‘Vocational’,3,4), substr(‘Education’,3); → Cen, tute, cati, ucation.

10. Write the SQL functions to perform the following operations.

(a) To display the day like “Monday”, “Tuesday”, from the date when India got independence.

Ans: To display the day like “Monday”, “Tuesday”, from the date when India got independence.

SELECT DAYNAME(‘1947-08-15’);

(b) To display the specified number of characters from a particular position of the given string.

Ans: To display the specified number of characters from a particular position of the given string.

SELECT MID(‘Vocational’, 3, 4);

(c) To display the name of the month in which you were born.

Ans: To display the name of the month in which you were born.

SELECT MONTHNAME(‘YYYY-MM-DD’);

(d) To display your name in capital letters.

Ans: To display your name in capital letters.

SELECT UCASE(‘yourname’);

Parimol

Hi! my Name is Parimal Roy. I have completed my Bachelor’s degree in Philosophy (B.A.) from Silapathar General College. Currently, I am working as an HR Manager at Dev Library. It is a website that provides study materials for students from Class 3 to 12, including SCERT and NCERT notes. It also offers resources for BA, B.Com, B.Sc, and Computer Science, along with postgraduate notes. Besides study materials, the website has novels, eBooks, health and finance articles, biographies, quotes, and more.

Class 12 It Chapter 1 RDBMS Concepts in MYSQL

RDBMS Concepts in MYSQL

Related Posts

Leave a Comment Cancel Reply

WhatsApp Channel

DEV STORE MARKETING