Question 1

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 2

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 3

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 4

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 5

Question 1Consider the following SQL statement. What type of statement is this ?CREATE TABLE employee (name VARCHAR, id INTEGER)DMLDDLDCLIntegrity constraint

Accepted Answer

DDLReason — 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.

Question 6

Question 5In the given query which keyword has to be inserted ?INSERT INTO employee ............... (1002, Kausar, 2000) ;TableValuesRelationField

Accepted Answer

ValuesReason — The syntax of INSERT INTO command is :INSERT INTO [] Values (, ...............) ;According to this syntax, Values keyword is used to specify the values that will be inserted into the specified columns of the table.

Question 7

Question 13Which of the following is not a DDL command ?UPDATETRUNCATEALTERNone of these

Accepted Answer

UPDATEReason — 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.

Question 8

Question 4How is FOREIGN KEY commands related to the PRIMARY KEY ?

Accepted Answer

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.

Question 9

Question 8(i)Differentiate between DROP TABLE, DROP DATABASE.

Accepted Answer

DROP TABLEDROP DATABASEThis command is used to delete a specific table from the database along with all its data, indexes, triggers, and constraints.This command is used to delete an entire database including all its tables, views, stored procedures, triggers, and other objects.The syntax is : DROP TABLE table_name;.The syntax is : DROP DATABASE database_name;.

Question 10

Question 2Increase salary of employee records by 10% (table employee).

Accepted Answer

Table employeeIDFirst_NameLast_NameUser_IDSalary1DimJosephJdim50002JaganathMishrajnmishra40003SiddharthMishrasmishra80004ShankarGirisgiri70005GautamBuddhabgautam2000UPDATE employee
SET Salary = (Salary * 0.1) + Salary ;OutputTo 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 |
+----+------------+-----------+----------+--------+

Question 11

Question 3Give commission of Rs.500 to all employees who joined in year 1982 (table Empl).

Accepted Answer

Table EmplEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO8369SMITHCLERK89021990-12-18800NULL208499ANYASALESMAN86981991-02-201600300308521SETHSALESMAN86981991-02-221250500308566MAHADEVANMANAGER88391991-04-022985NULL208654MOMINSALESMAN86981991-09-2812501400308698BINAMANAGER88391991-05-012850NULL308839AMIRPRESIDENTNULL1991-11-185000NULL108844KULDEEPSALESMAN86981991-09-0815000308882SHIAVNSHMANAGER88391991-06-092450NULL108886ANOOPCLERK88881993-01-121100NULL208888SCOTTANALYST85661992-12-093000NULL208900JATINCLERK86981991-12-03950NULL308902FAKIRANALYST85661991-12-033000NULL208934MITACLERK88821992-01-231300NULL10UPDATE Empl
SET COMM = 500
WHERE YEAR(HIREDATE) = 1982;ExplanationSince 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.

Question 12

Question 7Write Query statements for following transaction : (Consider tables of question 12)Increase price of all products by 10%.List the details of all orders whose payment is pending as per increased price.Decrease prices by 10% for all those products for which orders were placed 10 months before.

Accepted Answer

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

Question 13

Question 9In the added column Grade, assign grades as follows :if sal is in range 700 — 1500, Grade is 1 ;if sal is in range 1500 — 2200, Grade is 2 ;if sal is in range 2200 — 3000, Grade is 3 ;if sal is in range 3000 — Grade is 4 ;

Accepted Answer

UPDATE Empl SET Grade = '1' WHERE Sal >= 700 AND Sal <= 1500; UPDATE Empl SET Grade = '2' WHERE Sal > 1500 AND Sal <= 2200; UPDATE Empl SET Grade = '3' WHERE Sal > 2200 AND Sal <= 3000; UPDATE Empl SET Grade = '4' WHERE Sal > 3000;

Question 14

Question 14Create the table Department table based on the following table instance chart.Column NameIDNameData TypeNUMBERVARCHARLength825

Accepted Answer

CREATE TABLE Department (
ID NUMBER(8),
Name VARCHAR(25)
);

Question 15

Question 15Populate the table Department with data from table dept. Including only required columns.

Accepted Answer

INSERT INTO Department (ID, Name)
SELECT ID, Name
FROM dept ;

Question 16

Question 16Create the table Employee based on the following table instance chart.Column NameData TypeLengthIDNUMBER8First_NameVARCHAR25Last_NameVARCHAR25Dept_IDNUMBER8

Accepted Answer

CREATE TABLE Employee (
ID NUMBER(8),
First_Name VARCHAR(25),
Last_Name VARCHAR(25),
Dept_ID NUMBER(8)
);

Question 17

Question 17Drop table Employee and Department.

Accepted Answer

DROP TABLE IF EXISTS Employee ;
DROP TABLE IF EXISTS Department ;

Question 18

Question 18Create table Customer as per following Table Instance Chart.Column NameData TypeLengthCust_IDNUMBER7Cust_NameVARCHAR30Cust_Address1VARCHAR20Cust_Address2VARCHAR30PincodeNUMBER6Cust_PhoneVARCHAR10

Accepted Answer

CREATE TABLE Customer (
Cust_ID NUMBER(7),
Cust_Name VARCHAR(30),
Cust_Address1 VARCHAR(20),
Cust_Address2 VARCHAR(30),
Pincode NUMBER(6),
Cust_Phone VARCHAR(10)
);

Question 19

Question 19Add one column Email of data type VARCHAR and size 30 to the table Customer.

Accepted Answer

ALTER TABLE Customer
ADD (Email VARCHAR(30)) ;

Question 20

Question 20Add one more column CustomerIncomeGroup of datatype VARCHAR(10).

Accepted Answer

ALTER TABLE Customer
ADD (CustomerIncomeGroup VARCHAR(10));

Table Creation and Data Manipulation Commands

Assertions And Reasons

Checkpoint 141

Multiple Choice Questions

Type A Short Answer Questionsconceptual Questions

Type B Application Based Questions