CBSE Class 12 Informatics Practices Revision Assignment Set 04

Q1: 
(a) Which commands in MySql is used to make any Database current Database and see the list of table in that data base?
Answer: To make a database current, the command is USE database_name; and to see the list of tables in that database, the command is SHOW TABLES;.
In simple words: USE selects the database to work on, and SHOW TABLES lists all tables inside it.

Exam Tip: Remember to put a semicolon (;) at the end of every MySQL command.

Q1: 
(b) What do you mean by table level constraint and column level constraints? Give example for that.
Answer: 1. Column-level constraints are applied only to a single column and are defined while declaring that specific column.
Example: RollNo INT PRIMARY KEY
2. Table-level constraints are applied to one or more columns and are defined at the end of the table creation statement, separate from column definitions.
Example: PRIMARY KEY (RollNo, Regno)
In simple words: Column constraints check one column at definition time; table constraints can check multiple columns at the end.

Exam Tip: Composite primary keys (keys made of two or more columns) must always be declared as table-level constraints.

Q1: 
(c) How primary key in a table is different from unique key constraint?
Answer: - A Primary Key uniquely identifies each record in a table, does not allow NULL values, and only one primary key can exist per table.
- A Unique Key constraint also ensures all values in a column are different, but it allows one NULL value per column, and a table can have multiple unique constraints.
In simple words: Primary key cannot be null and can be only one, while Unique key allows a single null and can be multiple.

Exam Tip: Both ensure uniqueness, but Primary Key's restriction on NULL is the main distinguishing feature.

Q1: 
(d) What is the purpose of following query statements. Write output.
Select round(4325.7869,2);
Select truncate(4325.7869,2);
Answer: - ROUND(4325.7869, 2) rounds the given number to 2 decimal places. Output: \(4325.79\)
- TRUNCATE(4325.7869, 2) truncates (cuts off) the number to 2 decimal places without rounding. Output: \(4325.78\)
In simple words: Round rounds up or down based on the next digit; truncate simply chops off extra digits.

Exam Tip: Be careful with Round and Truncate; positive arguments specify decimal places, negative specify integer rounding.

Q1: 
(e) What is the difference between Group by and Order by clause?
Answer: - GROUP BY is used to group rows that have the same values into summary rows (often used with aggregate functions like SUM, COUNT, AVG).
- ORDER BY is used to sort the result set in ascending or descending order based on one or more columns.
In simple words: Group by collects similar data together, whereas Order by arranges data in a specific sequence.

Exam Tip: GROUP BY reduces the number of rows in output, while ORDER BY keeps the number of rows same and only changes their display sequence.

Q1: 
(f) A table Alpha contains 4 records and table Beta contains 7 records what will be total number of records in their Cartesian product?
Answer: Total number of records = \(4 \times 7 = 28\).
In simple words: The Cartesian product multiplies the number of rows of both tables together.

Exam Tip: Cartesian product (CROSS JOIN) combines every row of the first table with every row of the second table.

Q1: 
(g) Write SQL statement to add a column regno of data type varchar in a table Candidate which is primary key?
Answer:
ALTER TABLE Candidate ADD regno VARCHAR(20) PRIMARY KEY;
In simple words: Use ALTER TABLE with ADD to insert a new primary key column.

Exam Tip: Always specify the size for VARCHAR columns.

Q1: 
(h) Mrs Hetal, a database administrator created a table Voters in which she has used nationality as column name and by mistake she has entered nationality as Gujarat. Now she wants to change all the values from Gujarati to Indians using single statement. Help her to do the following by writing statement.
Answer:
UPDATE Voters SET nationality = 'Indians' WHERE nationality = 'Gujarati';
In simple words: Use the UPDATE command with SET and WHERE clauses to modify existing records.

Exam Tip: Omitting the WHERE clause in an UPDATE statement will change the column value for every single row in the table.

Q1: 
(i) What will be the query output for the following statement. Write in words/sentence.
(a) SELECT * from SCHOOL where sname like “_ _ _ SURAT”;
(b) SELECT * from SCHOOL where sname like “DC%”;
Answer:
(a) It will display all school records where the school name ends with 'SURAT' and has exactly three characters before 'SURAT' (since there are three underscores).
(b) It will display all school records where the school name starts with the letters 'DC' followed by any sequence of characters.
In simple words: Underscore (_) matches a single character, and percent (%) matches zero or more characters.

Exam Tip: LIKE operator is always used with wildcard characters like % and _.

Q2: 
(a) What is the difference between drop and delete table command?
Answer: - DROP TABLE is a DDL (Data Definition Language) command that deletes the entire table structure along with all its data permanently from the database.
- DELETE FROM table_name is a DML (Data Manipulation Language) command that deletes only the rows (records) from the table, while the table structure remains intact.
In simple words: Drop deletes the whole table structure and data; Delete only removes the rows inside the table.

Exam Tip: DROP cannot be rolled back easily in most SQL implementations, whereas DELETE can be rolled back if transactions are used.

Q2: 
(b) What are different commands used in Transaction in MySql?
Answer: The primary commands used in MySQL transactions are:
1. START TRANSACTION or BEGIN: Marks the beginning of a transaction block.
2. COMMIT: Saves all changes made during the transaction permanently.
3. ROLLBACK: Reverts changes made during the current transaction in case an error occurs.
4. SAVEPOINT: Sets a point within a transaction to which you can roll back later.
In simple words: Transactions let you save or cancel a group of database actions together.

Exam Tip: Transactions are mainly used with InnoDB storage engines in MySQL to ensure data integrity.

Q2: 
(c) Consider the tables DOCTORS and PATIENTS given below:
With reference to these tables, write commands in SQL for (i) and (ii) and output for (iii) given below:
(i) Display the Patno, PatName and corresponding DocName for each Patient.
(ii) Display the list of all patients whose OPD_Days are MWF.
(iii) Select OPD_DAYS, count(*) from DOCTORS, PATIENTS Where Patients.Department=Doctors.Department Group by OPD_DAYS;
Answer:
(i) SELECT PATIENTS.PATNO, PATIENTS.PATNAME, DOCTORS.DOCNAME FROM PATIENTS, DOCTORS WHERE PATIENTS.DOCID = DOCTORS.DOCID;
(ii) SELECT PATIENTS.* FROM PATIENTS, DOCTORS WHERE PATIENTS.DOCID = DOCTORS.DOCID AND DOCTORS.OPD_DAYS = 'MWF';
(iii) Output:
 

In simple words: Join tables using common columns to combine patient and doctor details.

Exam Tip: Always include the join condition (Foreign Key = Primary Key) when querying across multiple tables to avoid Cartesian products.

Q2: 
(d) Write SQL statement to create the table Film given below as per specification:
Column Name: Code, Name, Price, City
Data type: Varchar, Varchar, Number, Varchar
Size: 6, 30, -, 30
Constraint: Primary key, -, -, Default Delhi

(d) (i) Write SQL statement to add a column Director of data type Varchar(20) in the above table.
(ii) Write SQL statement to change size of the column City from 40 to 80.
(iii) Write SQL statement to display structure of the table.
Answer:
Table Creation:
CREATE TABLE Film (Code VARCHAR(6) PRIMARY KEY, Name VARCHAR(30), Price NUMBER, City VARCHAR(30) DEFAULT 'Delhi');
(i) ALTER TABLE Film ADD Director VARCHAR(20);
(ii) ALTER TABLE Film MODIFY City VARCHAR(80);
(iii) DESC Film; (or DESCRIBE Film;)
In simple words: Use CREATE TABLE for new tables, ALTER TABLE for modifying structure, and DESC for viewing structure.

Q3: 
Give the output for the following MySql queries:
a) SELECT ROUND(20009.111,-2);
b) SELECT SQRT(81)+SQRT(49)+SQRT(121);
c) SELECT MID(‘APS Public School’,11,8), TRIM(LEADING ‘!’ FROM ‘!!!!!WELCOME!!!!!’);
d) SELECT SUBSTR( RTRIM(‘INDIA IS GREAT ‘),3,9);
e) SELECT CONCAT(UPPER (‘xiHum’), LOWER(’xiSc’), UPPER(SUBSTR(‘xiCom’,2,3)));
Answer:
a) \(20000\)
b) \(9 + 7 + 11 = 27\)
c) School, WELCOME!!!!!
d) DIA IS GR
e) XIHUMxiscCOM
In simple words: Apply mathematical and string manipulation functions step-by-step.

Exam Tip: Negative rounding parameter rounds to tens (\(-1\)), hundreds (\(-2\)), thousands (\(-3\)), etc.

Q4: 
a) Which MySql command helps you to see existing databases?
b) Hemant created a table in MySql. Later he found that table is wrongly created and he wants to remove it. Name the command by which Hemant can do it.
c) Aditi created a table named student, she wants to see those students whose name ending with p. She wrote a query-
SELECT name.* FROM student WHERE name = ”%p”;
Help her to run the query by removing the errors from the query and rewriting it.
d) Aadhar is not able to set Empid of EMPL table to NULL. Which constraint has he used while creating table?
Answer:
a) SHOW DATABASES;
b) DROP TABLE table_name;
c) SELECT * FROM student WHERE name LIKE "%p";
d) PRIMARY KEY (or NOT NULL constraint)
In simple words: Fix syntax errors by using correct operators like LIKE instead of equality and removing invalid table qualifiers in SELECT.

Exam Tip: Wildcard searches require the LIKE operator, not the = operator.

Q5: 
a) Write a SQL command to create a table LIBRARY with following structure:
Field: Book_no, Title, Author, Publisher, Pages, Date_of_pub
Type: Integer(4), Varchar(40), Varchar(30), Varchar(30), Integer(4), Date
Constraint: Primary key, Not Null, -, -, -, -
Answer:
CREATE TABLE LIBRARY (Book_no INT(4) PRIMARY KEY, Title VARCHAR(40) NOT NULL, Author VARCHAR(30), Publisher VARCHAR(30), Pages INT(4), Date_of_pub DATE);
In simple words: Define each column name followed by its data type and constraints inside CREATE TABLE.

Exam Tip: Ensure data types match standard MySQL definitions.

Q5: 
b) In a database there are two tables ‘BRAND’ and ‘ITEM’ as shown below:
Write MySql queries for the following:
(i) To display ICode, IName and corresponding Brand of those items, whose price is between 20000 and 45000 (both values inclusive)
(ii) To display ICode, Price and BName of the item which has IName as “Television”
(iii) To increase the Price of all items by Rs. 15%.
Answer:
(i) SELECT ITEM.ICODE, ITEM.INAME, BRAND.BRAND FROM ITEM, BRAND WHERE ITEM.ICODE = BRAND.ICODE AND ITEM.PRICE BETWEEN 20000 AND 45000;
(ii) SELECT ITEM.ICODE, ITEM.PRICE, BRAND.BRAND FROM ITEM, BRAND WHERE ITEM.ICODE = BRAND.ICODE AND ITEM.INAME = 'TELEVISION';
(iii) UPDATE ITEM SET PRICE = PRICE + (PRICE * 0.15);
In simple words: Use BETWEEN for ranges, joins for combining tables, and UPDATE with percentage math for price hikes.

Exam Tip: Percentage increase can be written as PRICE = PRICE * 1.15 or PRICE = PRICE + PRICE * 0.15.

Q5: 
c) Given below is a Table: Customer
(i) Identify Primary key in the table given above.
(ii) Write MySql query to add a column Price with data type Integer and size 6 in the table Customer.
Answer:
(i) C_ID
(ii) ALTER TABLE Customer ADD Price INT(6);
In simple words: C_ID is unique for every row, making it the primary key.