MySQL-Database Management

CBSE BOARD

Important Questions

Fundamentals of Database

Question 1.Write two examples of DML commands to SQL.

Answer:Two examples of DML commands are:
SELECT, INSERT

 

Question 2.When using the LIKE clause, which wildcard symbol represents any sequence of none, one or more characters? (All India 2014c)

Answer:The % wildcard symbol is used in LIKE clause to represent any sequence of none, one or more characters.

 

Question 3.Write the UPDATE statement in MySQL to increase commission by 100.00 in the “Commission” column in the ‘EMP’ table.

Answer:UPDATE Emp SET Commission = Commission +100;

 

Question 4.Rewrite the following SQL statement after correcting the error(s). Underline the corrections made.

            INSERT IN STUDENT(RNO, MARKS) VALUEC5, 78.5);

Answer:INSERT INTO STUDENT (RNO, MARKS) VALUES (5, 78.5);

 

Question 5.What happens when auto-commit is set on?

Answer:When AUTOCOMMIT is set on then we do not need to start a transaction explicity using a BEGIN command but every statement is treated as an independent transaction and will COMMIT automatically immediate after its completion.

 

Question 6.Table ‘CLUB’ has 4 rows and 3 columns. Table ‘MEMBER’ has 2 rows and 5 columns. What will be the cardinality of the Cartesian product of them?

Answer:The cardinality of the cartesian product of Table ‘CLUB’ and Table ‘MEMBER’ = 8.

 

Question 7.Distinguish between ALTER TABLE and UPDATE commands of MySQL.

Answer:ALTER TABLE command is a DDL (Data Definition Language) statement which is used to modify the table structure. On the other hand UPDATE command is a DML (Data Manipulation Language) statement which is used to modify the data stored in a table.

 

Question 8.Mention two categories in which MySQL commands are broadly classified.

Answer:Two categories of Mysql commands are:

·                     DDL (Data Definition Language)

·                     DML (Data Manipulation Language)

 

Question 9.Give two characteristics of Primary Key.
Answer:Two characteristics of Primary Key are:

·                     All the values in the Primary Key field are unique.

·                     Does not allow NULL values.

 

Question 10.Write two examples of DBMS software.

Answer:Two examples of DBMS Software are:

·                     MySQL

·                     Oracle

 

Question 11.Write SQL command to create a SAVEPOINT called All

Answer:Create Table CLASS (Rollno INTEGER, Name VARCHAR(20));

            INSERT INTO CLASS(10, ‘CS’);

            SAVEPOINT Al;

 

Question 12.Define a foreign key with reference to RDBMS.  

Answer:Foreign Key It is primary key of another table. It’s used to join two tables. Name of the foreign key and the primary key may or may not be same but their data type has to be same.

Question 13.Write the command to display the list of existing databases. (Delhi 2012)

Answer: The command to display list of existing databases are as follows:

            Show databases;

 

Question 14.Mr. William wants to remove all the rows from INVENTORY table to release the storage space, but he does not want to remove the structure of the table. What MySQL statement should be used? (Delhi 2012)

Answer:The command to delete all rows from the Inventory table without deleting the structure of the table will be as follows:

            DELETE FROM INVENTORY;

 

Question 15.Write MySQL command to open an existing database.

Answer:The command to open an existing database is as follows:

            USE database_name;

 

Question 16.Give one difference between COMMIT and ROLLBACK commands used in MySQL. (HOTS; Delhi 2012; All India 2012)

Answer:The COMMIT command is used to save the changes made during the execution of a transaction permanentely on the disk. After executing the COMMIT command, we cannot undo or cancel the changes made thereupon. Whereas the ROLLBACK command is used to undo the changes made during the execution of a transaction.

 

Question 17.Write MySQL command, which will be used to open an already existing database “LIBRARY”.

Answer:The SQL command to open an already existing database LIBRARY will be as follows:

USE LIBRARY;

 

Question 18.Differentiate between alternate key and candidate key

Answer:The attribute or a combination of attributes that have unique values for each record known as candidate key whereas a candidate key that is not the primary key is known as an alternate key.

 

Question 19.In MySQL Reena and Zebi are getting the following output of SELECT statement on a table EMPLOYEE (HOTS; Delhi2011c)

Which keyword has Zebi used with a select statement to get the above output?

Answer:Zebi has used DISTINCT clause with the SELECT command.

 

Question 20.Write MySQL command, which will open an already existing database “CONTACTS

Answer:The SQL command to open an already existing database CONTACTS will be as follows:

Use CONTACTS;

 

Question 21.Write SQL command used to display the structure of a table.

Answer:The command to display the structure of a table is as follows:

DESCRIBE table_name;

or DESC table_name;

 

Question 22.Mr. lames created a table CLIENT with 2 rows and 4 columns. He added 2 more rows to it and deleted one column. What is the Cardinality and Degree of the Table CLIENT?
Answer:Cardinality = 4
Degree = 3
Cardinality are the number of rows and degree is number of the columns in a table.

 

Question 23.(i) If you have not executed the COMMIT command, executing which command will reverse all updates made during the current work session in MySQL?
(ii) What effect does SET AUTOCOMMIT have in transactions?

Answer:(i) ROLLBACK command can only be used to undo transactions.
(ii) Autocommit simply means that each statement in its own transaction which commits immediately. When autocommit is set ON, the SET IMLICITTRANSACTION sets the connection to implicit transaction mode. When set OFF, it returns the connection to autocommit transaction mode.

 

Question 24.A table FUN FOOD has 13 rows and 17 columns. What is the cardinality and degree of the table? (All India 2014)

Answer: Cardinality of FUN FOOD = 13

            Degree of FUN FOOD = 17

 

Question 25.What happens when “ROLLBACK” command is issued in a transaction process?
Answer:When a ROLLBACK command is issued in a transaction process then all the progress made by a transaction process upto that point will be lost i.e. all the changes made by that transaction process are undone.

 

Question 26.Gopi Krishna is using a table EMPLOYEE. It has the following columns:
Code, Name, Salary, Dept code He wants to display maximum salary Departmentwise.
He wrote the following command:

            SELECT Dept code, Max(Salary) FROM EMPLOYEE:

But he did not get desired result.
Rewrite the above query with necessary change to help him get the desired output.

Answer:SELECT Deptcode, Max(Salary) FROM EMPLOYEE GROUP BY Deptcode;

 

Question 27.What is the use of COMMIT statement in SQL? How is it different from ROLLBACK statement?
Answer:The use of the COMMIT statement in MySQL is to end your current transaction and make all changes permanent performed in the transaction. ROLLBACK statement roll back the present transaction, which means canceling a transaction’s changes.

 

Question 28.Name two categories into which SQL commands may be categorized. Also, give one example of SQL commands in each category.
Answer:The two categories of SQL commands are as follow:
(i) DDL DDL stands for Data Definition Language. The DDL commands are used to Create, Modify or Destroy the structure of the database objects,
E.g., CREATE TABLE, ALTER TABLE are of this category.

(ii) DML DML stands for Data Manipulation Language. The DML commands are used to insert, display, delete or change data in tables,
E.g.,
SELECT, INSERT, UPDATE, DELETE, etc., commands are of this category.

Question 29.The Mname Column of a table Members is given below

Based on information, find the output of the following queries

(i) SELECT Mname FROM Members WHERE Mname ' “%\v";

(ii) SELECT Mname FROM Members WHERE Mname LIKE "%e%";

Answer:The output of given commands will be as follows
(i) Hirav, Rajeev
(ii) Sheetal, Rajeev.

 

Question 30.What is the purpose of ALTER TABLE command in MySQL? How it is different from UPDATE commands? (All India 2011)
Answer:The ALTER TABLE command is used to change the structure of an existing table. It can be used to add a new column, to change the size or type of a column or to remove a column from a table. But update commands cannot effect the table structure.

 

Question 31.The Doc_name column of a table HOSPITAL is given below

Based on information, find the output of the following queries

(i) SELECT Doc_name FROM HOSPITAL WHERE Doc_name LIKE ‘%v’;.

(ii) SELECT Doc_name FROM HOSPITAL WHERE Doc_name LIKE

Answer:The output of given commands will be as follows:
(i) Sanjeev
(ii) Deepak, Sanjeev.

 

Question 32.A table “TRAINS” in a database has degree 3 and cardinality 8. What is the number of rows and columns in it? (Delhi 2011; All India 2011)
Answer:There will be 8 rows and 3 columns.

Question 33.Sarthak, a student of class XI created a table “CLASS”. Grade is one of the column of this table. To find the details of students whose Grades have not been entered, he wrote the following MySQL query, which did not give the desired result

            SELECT * FROM CLASS WHERE GRADE = "NULL”

Help Sarthak to run the query by removing the errors from the query and write the correct query.

Answer:The correct query is

            SELECT * FROM CLASS WHERE GRADE IS NULL;

 

Question 34.Anurag, a student of class XI created a table “PLAYER SCORE”. Current score is one of the column of this table. To find the details of players whose current score is more than 100, he wrote the following MySQL query, which did not give the desired result

            SELECT * FROM PLAYER_SCORE WHERE Current_score>“100”;

Help Anurag to run the query by removing the errors from the query and write the correct query.

Answer:As score is a numeric data type, so double quotes are not allowed.

            SELECT * FROM PLAYER_SC0RE WHERE Current_Score > 100;

 

Question 35.Differentiate between COMMIT and SAVEPOINT.

Answer:

S.No.	Commit	Save Point
1	Ends the current transaction	Define the breakpoints for the transaction
2	No rollback after the commit	Partial rollback is allowed up to the save point

 

Question 36.Amit, a salesman in an Outlet, created a table TRANSACTIONS an Amount is one of the column of this Table. To find the details of customer whose transaction amount is more than 800, he wrote the following MySQL query, which did not give the desired result:

            SELECT * FROM TRANSACTIONS WHERE Amount > “800”;

Help Amit to run the query by removing the errors from the query and write the correct query.

Answer:Correct query is

            SELECT * FROM TRANSACTIONS WHERE Amount > 800;

 

Question 37.Anushka, a salesman in an Outlet, created a table, CUSTOMERS. PhoneNumber is one of the column of this table, which is character type. To find the details of customers whose PhoneNumber is 259624, she wrote the following MySQL query, which did not give the desired result:

            SELECT * FROM CUSTOMERS WHERE PhoneNumber = 259624;

Help Anushka to run the query by removing the errors from the query and write the correct query.
Answer:As PhoneNumber is character type so it must be enclosed in single quotes So, correct query is

            SELECT * FROM CUSTOMERS WHERE PhoneNumber = ‘259624’;

 

Question 38.Natasha, a salesgirl in an outlet, created table SUPPLIERS. PhoneNumber is one of the column of this table, which is numeric type. To find the details of customers whose number is 345941, she wrote the following MySQL query, which did not give the desired result:

            SELECT * FROM SUPPLIERS WHERE PhoneNumber = “345941”;

Answer:As PhoneNumber is numeric type so it should not be enclosed in double quotes. The correct query is

            SELECT * FROM SUPPLIERS WHERE PhoneNumber = 345941;

 

Question 39.Shammi, a student of class XII created a table FRIENDS. Pincode is one of the column of this table, which is character type. To find the details of friend whose pin code number is 262122, he wrote the following MySQL query, which did not give desired result:

            SELECT * FROM FRIENDS WHERE PinCode = 262122;

Help Shammi to run the query by removing the errors from the query and write the correct query.

Answer:The correct query is

            SELECT * FROM FRIENDS WHERE PinCode = ‘262122’;