Question 1

Question 1Assertion. The PRIMARY KEY and UNIQUE constraints are the same.Reason. The columns with PRIMARY KEY or UNIQUE constraints have unique values for each row.

Accepted Answer

(d)Assertion is false but Reason is true.ExplanationUNIQUE and PRIMARY KEY constraints are not the same and there are differences between them. Though both ensure unique values for each row in a column, but UNIQUE allows NULL values whereas PRIMARY KEY does not. Also, there can exist multiple columns with UNIQUE constraints in a table, but there can exist only one column or one combination with PRIMARY KEY constraint.

Question 2

Question 2Assertion. The treatment of NULL values is different with PRIMARY KEY and UNIQUE constraints.Reason. The column(s) with PRIMARY KEY do not allow the NULL value even in a single row but UNIQUE constraint allows NULL for one of the rows.

Accepted Answer

(a)Both Assertion and Reason are true and Reason is the correct explanation of Assertion.ExplanationThe treatment of NULL values is different with PRIMARY KEY and UNIQUE constraints. The UNIQUE constraint allows NULL values for one of the rows, while the PRIMARY KEY does not allow the NULL value in any row.

Question 3

Question 3Assertion. There is no restriction on the number of columns that can have PRIMARY KEY constraint or UNIQUE constraints.Reason. There can be multiple columns with UNIQUE constraint but PRIMARY KEY constraint can be defined only once for one or more columns.

Accepted Answer

(d)Assertion is false but Reason is true.ExplanationThere can exist multiple columns with UNIQUE constraints in a table, but there can exist only one column or one combination with PRIMARY KEY constraint.

Question 4

Question 4Assertion. There are different commands for creating and changing table design.Reason. The CREATE TABLE command creates the tables while ALTER TABLE command changes the design of an existing table.

Accepted Answer

(a)Both Assertion and Reason are true and Reason is the correct explanation of Assertion.ExplanationThe CREATE TABLE command is used to create tables in a database, specifying the table's structure, including column names, data types, and constraints. Conversely, the ALTER TABLE command is used to modify the structure of an existing table, such as adding, removing, or modifying columns, constraints, or indexes.

Question 5

Question 5Assertion. Both DELETE and DROP TABLE carry out the same thing — deletion in tables.Reason. The DELETE command deletes the rows and DROP TABLE deletes the whole table.

Accepted Answer

(d)Assertion is false but Reason is true.ExplanationThe DELETE command removes rows from a table while leaving the table structure intact. It does not delete the entire table, instead, it removes specific rows within it. On the other hand, the DROP TABLE command in SQL deletes a table from the database, including its structure and all its data. So, while both commands involve deletion, they operate on different levels: the DELETE command removes rows, while the DROP TABLE command removes the entire table.

Question 6

Question 6Assertion. Both UPDATE and ALTER TABLE commands are similar.Reason. The UPDATE command as well ALTER TABLE command make changes in the table.

Accepted Answer

(e)Both Assertion and Reason are false.ExplanationThe UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. On the other hand, the ALTER TABLE command is used to change the definitions of existing tables. It can add columns, integrity constraints, and redefine columns. While both commands involve making changes, they operate on different aspects of the table - UPDATE command modifies data, while ALTER TABLE command modifies the table structure.

Question 7

Question 1Which command is used for creating tables ?

Accepted Answer

In SQL, CREATE TABLE command is used for creating tables in database.

Question 8

Question 2What is a constraint ? Name some constraints that you can apply to enhance database integrity.

Accepted Answer

A constraint is a condition or check applicable on a field or set of fields.Some constraints that we can apply to enhance database integrity are:Unique constraint.Primary key constraint.Default constraint.Check constraint.Foreign key constraint.

Question 9

Question 3What is the role of UNIQUE constraint ? How is PRIMARY KEY constraint different from UNIQUE constraint ?

Accepted Answer

UNIQUE constraint ensures that no two rows have the same value in the specified column(s).There are differences between UNIQUE and PRIMARY KEY constraints. Though both ensure unique values for each row in a column, but UNIQUE allows NULL values whereas PRIMARY KEY does not. Also, there can exist multiple columns with UNIQUE constraints in a table, but there can exist only one column or one combination with PRIMARY KEY constraint.

Question 10

Question 4What is primary key ? What is PRIMARY KEY constraint ?

Accepted Answer

A primary key is a unique identifier for each record in a table, and it must be unique and not null. A PRIMARY KEY constraint declares a column or one group of columns as the primary key of the table. This constraint must be applied to columns declared as NOT NULL.

Question 11

Question 5What is NOT NULL constraint ? What is DEFAULT constraint ?

Accepted Answer

The NOT NULL constraint is used in SQL to ensure that a column cannot contain NULL (i.e., empty) values.A DEFAULT constraint is used in SQL to specify a default value for a column in a table. When the user does not enter a value for the column (having default value), automatically the defined default value is inserted in the field.

Question 12

Question 6When a column's value is skipped in an INSERT command, which value is inserted in the database ?

Accepted Answer

The columns that are not listed in the INSERT command will have their default value, if it is defined for them, otherwise, NULL value. If any other column (that does not have a default value and is defined NOT NULL) is skipped or omitted, an error message is generated and the row is not added.

Question 13

Question 7Can a column defined with NOT NULL constraint, be skipped in an INSERT command ?

Accepted Answer

If a column defined with a NOT NULL constraint is skipped in an INSERT command, it will result in an error, and the row will not be added to the table.

Question 14

Question 8How would you view the structure of table Dept ?

Accepted Answer

To view the structure of a table in SQL, we use DESC[RIBE] command of MySQL * Plus. The syntax of this command is as follows : DESC[RIBE] <tablename> ;.For example, the command to view the structure of table Dept is DESCRIBE Dept ; or DESC Dept ;.

Question 15

Question 9Table Empl has same structure as that of table EMPL. Write a query statement to insert data from table NewEmpl into EMPL where salary and comm is more than Rs. 4000.

Accepted Answer

INSERT INTO Empl 
SELECT *
FROM NewEmpl
WHERE SAL > 4000 AND COMM > 4000 ;

Question 16

Question 10What is the error in following statement ?UPDATE EMPL ;

Accepted Answer

The error in the statement UPDATE EMPL; is that it is incomplete. The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. Therefore, the statement is incomplete as it lacks both the SET and WHERE clauses.

Question 17

Question 11Identify the error :DELETE ALL FROM TABLE EMPL ;

Accepted Answer

The statement DELETE ALL FROM TABLE EMPL; is in error due to the misuse of the keyword ALL and the unnecessary inclusion of TABLE before the table name. In SQL, the syntax of DELETE statement is :DELETE FROM [ WHERE ] ;According to this syntax, the correct command to remove all the contents of EMPL table is :DELETE FROM EMPL ;

Question 18

Question 12Differentiate between DDL and DML.

Accepted 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 19

Question 1A database can be opened with USE <database> command.

Accepted Answer

Please refer to the full solution above.

Question 20

Question 2To list the names of existing database, you can use SHOW DATABASES command.

Accepted Answer

Please refer to the full solution above.

Table Creation and Data Manipulation Commands

Assertions And Reasons

Checkpoint 141

Fill In The Blanks

Multiple Choice Questions

Truefalse Questions

Type A Short Answer Questionsconceptual Questions

Type B Application Based Questions