Table Creation and Data Manipulation Commands

(d)

Assertion is false but Reason is true.

Explanation
UNIQUE 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.

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The 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.

(d)

Assertion is false but Reason is true.

Explanation
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.

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The 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.

(d)

Assertion is false but Reason is true.

Explanation
The 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.

(e)

Both Assertion and Reason are false.

Explanation
The 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.

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

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:

1. Unique constraint.
2. Primary key constraint.
3. Default constraint.
4. Check constraint.
5. Foreign key constraint.

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.

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.

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.

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.

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.

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 ;.

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.

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  <TABLENAME>
[ WHERE <PREDICATE> ] ;

According to this syntax, the correct command to remove all the contents of EMPL table is :

DELETE FROM EMPL ;

DDL

Reason — The SQL statement CREATE TABLE employee (name VARCHAR, id INTEGER) is a Data Definition Language (DDL) statement. DDL statements are used to define, modify, and delete database objects such as tables, views, indexes, etc. In this case, the statement is creating a new table named employee with columns, name of type VARCHAR and id of type INTEGER.

Fixed, variable

Reason — CHAR datatype specifies a fixed length string. Defining a length is not required, but the default is 1. While VARCHAR datatype specifies a variable length string. Defining a length is required.

3, 20

Reason — For the field Name1 with VARCHAR(20) datatype, storing the value 'Ana' will consume 3 character's space because VARCHAR(n) column can have a maximum size of n bytes and it stores values exactly as specified without adding blanks for shorter lengths. Exceeding n bytes results in an error message. Whereas for the field Name2 with CHAR(20) datatype, storing the value 'Anuj' will consume 20 characters' space because CHAR(n) ensures that all values stored in that column are of length n bytes, padding shorter values with blanks while maintaining a fixed size of n bytes.

DML

Reason — The above SQL statement is Data Manipulation Language (DML) statement. DML statements are used to access and manipulate data in tables. The DML commands include SELECT, LOCK TABLE, UPDATE, INSERT INTO, DELETE. In this case, the INSERT INTO statement is used to insert a new row of data into the instructor table.

Values

Reason — The syntax of INSERT INTO command is :

INSERT INTO <tablename> [<column List>]
Values (<value>, <value> ...............) ;

According to this syntax, Values keyword is used to specify the values that will be inserted into the specified columns of the table.

All of these

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.

ALTER TABLE

Reason — The ALTER TABLE command in SQL is used to change the definitions of existing tables. It allows for various operations such as adding a new column, redefining a column, and adding an integrity constraint. Therefore, it changes the structure of the table.

DROP TABLE

Reason — The DROP TABLE command in SQL will delete the table from the MYSQL database. Once this command is executed, the table and all its associated data are removed from the database. After dropping the table, the table name is no longer recognized within the database system, and no further commands can be executed on that object.

Constraint

Reason — Constraint defines rules regarding the values allowed in columns and is the standard mechanism for enforcing database integrity. Once an integrity constraint is enabled, all data in the table must confirm to the rule that it specifies.

alter

Reason — To remove a primary key constraint from a table the ALTER command is used. The DROP clause of ALTER TABLE command is used with syntax ALTER TABLE <TABLENAME> DROP PRIMARY KEY ;.

Create table command

Reason — The CREATE TABLE command is used to define a new table in SQL, and it allows to define the columns of the table along with any integrity constraints such as primary keys, foreign keys, unique constraints, etc.

Drop table command

Reason — The DROP TABLE command is used to delete a table and all its data from the database. Once this command is given, the table name is no longer recognized and no more commands can be given on the object.

UPDATE

Reason — Data Definition Language (DDL) statements are used to define, modify, and delete database objects such as tables, views, indexes, etc. The DDL commands are CREATE, ALTER, TRUNCATE, DROP etc. But the UPDATE command is Data Manipulation Language (DML) command, used to modify existing data in a table.

Distinct

Reason — The legal constraints for a CREATE TABLE command include the Primary key constraint, Foreign key constraint, Unique constraint, Check constraint, Default constraint. However, the Distinct is not a valid option for a CREATE TABLE command.

True

Reason — In SQL, the CREATE TABLE command is used to define a new table in SQL, and it allows to define the columns of the table along with any integrity constraints such as primary keys, foreign keys, unique constraints, etc.

False

Reason — Constraints can be defined with the CREATE TABLE command in SQL, but they can also be added or altered later using the ALTER TABLE command. The ALTER TABLE command allows to modify an existing table by adding, modifying, or dropping columns, as well as adding an integrity constraints.

False

Reason — UNIQUE 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.

False

Reason — DELETE from <table> command is used to remove all the contents from the table, leaving it empty. On the other hand, the DROP TABLE command is used to delete the table from the database along with all its data and structure.

True

Reason — Conditional updates in table data are possible through the UPDATE command in SQL. The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. This enables to perform updates selectively based on specific conditions defined in the WHERE clause.

SQL commands can be divided into following categories :

1. Data Definition Language (DDL) commands — CREATE, ALTER, DROP, TRUNCATE etc.
2. Data Manipulation Language (DML) commands — INSERT, UPDATE, DELETE etc.
3. Transaction Control Language (TCL) commands — COMMIT, ROLLBACK, SAVEPOINT etc.
4. Session Control Commands
5. System Control Commands

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. Defining a foreign key in a table involves specifying the relationship between the tables and setting up rules for data integrity. When two tables are related by a common column or set of columns, the related column(s) in the parent table (or primary table) should be either declared as a primary key or unique key. Meanwhile, the related column(s) in the child table (or related table) should have a foreign key constraint referencing the primary or unique key in the parent table.

The PRIMARY KEY is a set of one or more attributes that can uniquely identify tuples within the relation. A primary key column cannot contain NULL values, and it must have unique values for each row. Only one primary key constraint can exist per table. Conversely, the FOREIGN KEY command establishes a relationship between two tables by linking a column or set of columns in one table to the primary key or a unique key in another table. It enforces referential integrity, ensuring that values in the foreign key column(s) of the referencing table match values in the referenced table's primary key or unique key column(s). A foreign key can allow NULL values, indicating that the relationship is optional. Multiple foreign key constraints can exist in a table, each referencing a different parent table.

FOREIGN KEY commands establish relationships between tables by linking columns in one table to the PRIMARY KEY or a unique key in another table. This linkage ensures referential integrity, meaning that values in the FOREIGN KEY column(s) of the referencing table must match values in the PRIMARY KEY or unique key column(s) of the referenced table. Therefore, FOREIGN KEY commands are directly related to PRIMARY KEY commands as they rely on the unique identification provided by PRIMARY KEY constraints in other tables.

Database constraints enforce business rules on a database. These include PRIMARY KEY for unique identifiers, FOREIGN KEY for maintaining relationships between tables, UNIQUE for ensuring uniqueness, CHECK constraint limit values that can be inserted into a column of a table, and default constraints are utilized to specify a default value for a column when no value is explicitly provided during an insert operation.

Table constraints are rules or conditions applied to an entire table in a database. They are defined when creating or altering a table's schema.

Column constraints are rules or conditions applied to individual columns within a database table. They are specified at the column level when creating or altering a table's schema.

The difference between the two is that column constraints apply only to individual columns, whereas table constraints apply to groups of one or more columns.

A default value is a predefined value assigned to a column in a database table. It can be specified using the DEFAULT clause when defining the table's schema. If no default value is defined for a column, and a new row is inserted into the table without providing a value for that column, the column's default value will be NULL, unless the column is defined with a NOT NULL constraint. In such cases, an error will occur if a value is not provided.

Table employee

UPDATE employee
SET Salary = (Salary * 0.1) + Salary ;

To view all the details (all columns and rows) of the "employee" table the below query is executed :

SELECT * FROM employee ;

+----+------------+-----------+----------+--------+
| ID | First_Name | Last_Name | User_ID  | Salary |
+----+------------+-----------+----------+--------+
|  1 | Dim        | Joseph    | Jdim     |   5500 |
|  2 | Jaganath   | Mishra    | jnmishra |   4400 |
|  3 | Siddharth  | Mishra    | smishra  |   8800 |
|  4 | Shankar    | Giri      | sgiri    |   7700 |
|  5 | Gautam     | Buddha    | bgautam  |   2200 |
+----+------------+-----------+----------+--------+

Table Empl

UPDATE Empl
SET COMM = 500
WHERE YEAR(HIREDATE) = 1982;

Since there are no employees who joined in the year 1982 according to the data provided in the "Empl" table, executing the given SQL query will result in no changes to the "COMM" column.

The following tables are considered :

Orders (OrdNo, Ord_date, ProdNo#, Qty) 
Product (ProdNo, Descp, Price)
Payment (OrdNo, Pment)

1.

UPDATE product
SET price = (price * 0.1) + price ;

2.

SELECT *
FROM Orders
JOIN Payment ON Orders.OrdNo = Payment.OrdNo
WHERE Payment.Pment = 'Pending';

3.

UPDATE Product
SET Price = Price - (Price * 0.1)
WHERE ProdNo IN (
    SELECT ProdNo#
    FROM Orders
    WHERE YEAR(Ord_date) = YEAR(CURDATE()) - 1 
    AND MONTH(Ord_date) = MONTH(CURDATE()) - 10
);