MYSQL QUESTION ANSWERS

Very Short answers types questions 1. Which of the following will give the same answer irrespective of the NULL values in the specified column: a. MIN() b. MAX() c. SUM() d. None of the above 2. An aggregate function: a. Takes a column name as its arguments b. May take an expression as its argument c. Both (a) and (b) d. None of (a) and (b) 3. HAVING is used in conjunction with a. WHERE b. GROUP BY clause c. Aggregate functions d. None of the above 4. In the FROM clause of a SELECT statement a. Multiple Column Names are specified. b. Multiple table names are specified. c. Multiple Column Names may be specified. d. Multiple table names may be specified. 5. JOIN in RDBMS refers to a. Combination of multiple columns b. Combination of multiple rows c. Combination of multiple tables d. Combination of multiple databases 6. Equi-join is formed by equating a. Foreign key with Primary key b. Each row with all other rows c. Primary key with Primary key d. Two tables 7. Referential integrity a. Must be maintained b. Cannot be maintained c. Is automatically maintained by databases d. Should not be maintained 8. A Primary key column a. Can have NULL values b. Can have duplicate values c. Both (a) and (b) d. Neither (a) nor (b) 9. Primary Key of a table can be a. Defined at the time of table creation only. b. Defined after table creation only. c. Can be changed after table creation d. Cannot be changed after table creation 10. Two SELECT commands in a UNION a. Should select same number of columns. b. Should have different number of columns c. Both (a) and (b) d. Neither (a) nor (b) Answers : 1-c, 2-c, 3-b, 4-a, 5-c, 6-a, 7-a, 8-d, 9-a, 10-c :: 83 :: Q1. What do you mean by a Database Management System? Ans. Database Management is a collection of programs and files that allow a user to define structure of a database, store data into it, modify the structure and manipulate the data. Q2. What do you mean by Relational database? Ans. Relational Database is a type of database in which relation (table) is used to store data. Q3. What is a foreign key? Ans. A non-key attribute or column of a table whose value is derived from the primary key of another table. Q4. What is primary key? Ans. Primary key is a unique key in a relation which can uniquely identify a tuple (row) in a given relation. Q5. What is SQL? Ans. SQL is stands for structured query language. This language is used to create, manage table and manipulate stored records in a table. Q6. What is referential integrity? Ans. This is a rule which ensures that in DBMS relationships between records in related tables are valid. And that user don’t accidently delete or change related data. Q7. What is MySQL? Ans. MySQL is an open source RDBMS which uses SQL. Q8. What is DDL? Ans. DDL provides commands to define or redefine the schema of a table. Table is created, altered and dropped using DDL. Q9. What are DML commands? Ans- DML commands are used to manipulate data stored in a table.Insertion, deletion and modifications are possible using DML commands. Q11. What is null value in MySql? Ans-If a column in a row has no value, then column is said to be null. Q12. Which keyword eliminates redundant data in from a query result? Ans- DISTINCT Q13. How would you display system date as the result of a query? Ans- CURDATE() Q14. What is NOW() function in MySql? Ans- It returns the current date and time. Q15. What is NOT NULL constraint? Ans- NOT NULL constraints impose a condition that value of a row cannot be left blank. Q16. Identify the error? DELETE ALL FROM TABLE EMP; Ans:There is no need to write ALL and TABLE word in above query. Correct form is-DELETE FROM EMP; Q17. Differentiate WHERE and HAVING clause? Ans- Where clause is used to select particular rows that satisfy condition whereas having clause is used in connection with aggregate function, group by clause. Q 18. Why is it not allowed to give String and Date type arguments for SUM() and AVG() functions? Ans:String and dates are not real numbers that we calculate so sum() or avg() functions are not valid for them. Q 19. What is default, Autocommit mode in MySQL? Ans:By default, Autocommit mode is on in MySQL. :: 84 :: Q 20. Can where be added a savepoint in a transaction? Ans :We can add a savepoint anywhere in a transaction. Q 21. How are NULL values treated by aggregate functions? Ans:None of the aggregate functions takes NULL into consideration. NULL is simply ignored by all the aggregate functions. Q22. There is a column C1 in a table T1. The following two statements: SELECT COUNT(*) FROM T1; and SELECT COUNT(C1) from T1; are giving different outputs. What may be the possible reason? Ans :There may be a null value. Q23. What is the purpose of GROUP BY clause? Ans: GROUP BY: GROUP BY clause is used in a SELECT statement in conjunction with aggregate functions to group the result based on distinct values in a column. Q 24. What is the difference between HAVING and WHERE clauses? Explain with the help of an example. Ans:WHERE Vs HAVING: WHERE is used to put a condition on individual row of a table whereas HAVING is used to put condition on individual group formed by GROUP BY clause in a SELECT statement. Q24. What is a foreign key? What is its importance? Ans:Foreign Key: It is a column of a table which is the primary key of another table in the same database. It is used to enforce referential integrity of the data. Q 25. What are constraints? Are constraints useful or are they hindrances to effective management of databases? Ans:These are the rules which are applied on the columns of tables to ensure data integrity and consistency. These play very important role for tables so are not hindrances. Q26. In a database there is a table Cabinet. The data entry operator is not able to put NULL in a column of Cabinet? What may be the possible reason(s)? Ans :Not NULL or Primary key constraints used. Q 27. In a database there is a table Cabinet. The data entry operator is not able to put duplicate values in a column of Cabinet? What may be the possible reason(s)? Ans:Primary key constraint used. Q28. Do Primary Key column(s) of a table accept NULL values? Ans:No. Q29. What are the differences between DELETE and DROP commands of SQL? Ans:Delete is used for row removing while drop is used for removing complete table. Q 30. What is HAVING clause? Ans:HAVING clause is used in conjunction with GROUP BY clause in a SELECT statement to put condition on groups. Q31. What is Referential Integrity? Ans:The property of a relational database which ensures that no entry in a foreign key column of a table can be made unless it matches a primary key value in the corresponding column of the related table. Q32. What is Union used for? Ans:Union is an operation of combining the output of two SELECT statements. Q33.What is ALTER TABLE? Ans:ALTER TABLE command can be used to Add, Remove, and Modify columns of a table. It can also be used to add and Remove constraints. Q 34. What is DROP TABLE? Ans:DROP TABLE command is used to delete tables. Q35. What function is used whenever a condition involves an aggregate function? Ans:whenever a condition involves an aggregate function, then we use HAVING clause in conjunction with GROUP BY clause. :: 85 :: Short answers types questions Q1. How SQL commands are classified? Ans-SQL Commands are classified into three categories (i) Data Definition Language (DDL)-Commands that allow us to perform tasks related to data definition. E.g. creating, altering and dropping (ii) Data Manipulation Language (DML) - Commands that allows us to perform data manipulation e.g retrieval, insertion, and modification of data stored in a database. (iii) Transaction Control Language (TCL) - Commands that manages and controls the transactions. Q2. What is difference between char and varchar? Ans-The CHAR datatypes specifies a fixed length character string.When a column is given datatype as CHAR(n) then MySQL ensures that all values stored in that column have this length.But on other hand when a column is given datatype as VARCHAR(n) ,then the maximum size of a value in this column stores exactly what we specify. Q3. What do you understand by degree and cardinality of a relation in relational data base? Ans- The number of attributes in a relation is called Degree of arelation and number of records in a table is called cardinality in relational data base. Q4. What do you understand by the candidate key ? And- Candidate Key: All attribute combinations inside a relation that can serve as primary key(uniquely identifies a row in a relation) are Candidate Keys as they are candidates for the primary key position. Q 5. Why do understand by transaction COMMIT and ROLLBACK? Ans- COMMITing a transaction means all the steps of a transaction are carried out successfully and all data changes are made permanent in the database. Transaction ROLLBACK means transaction has not been finished completely and hence all data changes made by the transaction in the database if any, are undone and the database returns to the state as it was before this transaction execution started. Q 6. What do you understand by ACID properties of database transaction? Ans. -To ensure the data-integrity, the database system maintains the following properties of transaction. The properties given below are termed as ACID properties-an acronym derived from the first letter of each of the properties.  Atomicity - This property ensures that either all operations of the transactionsare reflected properly in the database, none are. Atomicity ensures either all-or- none operations of a transaction are carried out.  Consistency - This property ensures that database remains in a consistent state beforethe start of transaction and after the transaction is over.  Isolation - Isolation ensures that executing transaction execution in isolation i.e. is unaware ofother transactions executing concurrently in the system.  Durability - This property ensures that after the successful completion of a transaction i.e. when a transaction COMMITs, the changes made by it to the database are permanently persist. Q 7.What TCL commands are supported by SQL? Ans. -SQL supports following TCL commands • BEGIN Or START TRANSACTION-Marks the beginning of a transaction • COMMIT-Ends the current transaction by saving database changes and starts a newtransaction. • ROLLBACK-Ends the current transaction by discarding changes and starts a newtransaction. • SAVEPOINT-Defines breakpoints for the transactions to allow partial rollbacks. • SET AUTOCOMMIT-Enables or disable the default autocommit mode. Q8. What is transaction? How multiple transactions are executed? Ans. -A transaction is a logical unit of a work that must succeed or fail in its entirely. It is an atomic operation which can be divided unto smaller operations.Multiple transactions can be executed in one of the following two ways: Serial execution (one by one) or Concurrent execution.