chapter-Introduction to MySQL

Important MCQ-Based Questions on Informatics Practices (IP) class 11 chapter-Introduction to MySQL

This page consists of Important MCQ-Based Questions on Informatics Practices (IP) class 11 chapter-Introduction to MySQL all the questions are uploaded for practice with detailed explanations of every question. To check the solution, click on the answer. 

Find below Important MCQ-Based Questions on Informatics Practices (IP) class 11 chapter-Introduction to MySQL

Important Questions for Informatics Practices (IP) class 11 chapter-Introduction to MySQL set-1

Informatics Practices - MCQ on Introduction to MySQL

Class XI

Q.1 An object-relational database management system that provides an open, comprehensive, and integrated approach to information management is known as

a) MySQL.

b) MySQL Server.

c) MySQL Instance.

d) MySQL DBMS.

Answer:

(b)

Explanation: MySQL server listens for client requests coming in over the network and accesses database contents according to those requests, and provides that to the client.

Q.2 The software executing on MySQL Server, to provide access to information stored in the database is known as

a) MySQL.

b) MySQL Server.

c) MySQL Instance.

d) MySQL DBMS.

Answer:

(c)

Explanation: MySQL database system refers to the combination of a MySQL server instance and a MySQL database.

Q.3 A file containing metadata i.e., data about data is known as

a) metadata diary.

b) data dairy.

c) data dictionary.

d) metadata dictionary.

Answer:

(c)

Explanation: A data dictionary is a "centralized repository of information" about data, such as meaning, relationships to other data, origin, usage, and format.

Q.4 A language to operate upon RDBMSs is

a) SQL.

b) .NET.

c) Java.

d) Visual Basic.

Answer:

(a)

Explanation: SQL is a database computer language designed for managing data in relational database management systems (RDBMS).

Q.5 In MySQL database, information is stored in

a) tree format.

b) network format.

c) graph format.

d) table format.

Answer:

(d)

Explanation: SQL is a database computer language designed for managing data (in the form of tables) in Relational Database Management Systems (RDBMS).

Q.5 Programs that connect to MySQL server and issue queries in a pre-specified format is known as

a) MySQL client.

b) MySQL programmer.

c) MySQL database.

d) MySQL server.

Answer:

(a)

Explanation: MySQL clients are programs that connect to the database server and issue queries in a pre-specified format.

Q.7 MySQL command line program is

(a) Mysqldrain.

(b) Mysqldrap.

(c) Mysqldump.

(d) Mysqldim.

Answer:

(c)

Explanation: The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server.

Q.8 MySQL command line program is

a) Mysqladdress.

b) Mysqloracle

c) Mysqladmin.

d) Mysqlaroma.

Answer:

(c)

Explanation: Mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more.

Q.9 MySQL GUI program is

a) MySQL Administrator.

b) MySQL Admin.

c) MySQL Oracle.

d) MySQL Aroma.

Answer:

(a)

Explanation: MySQL Administrator is a powerful visual administration console that enables us to easily administer MySQL environment and gain significantly better visibility into how your databases are operating.

Q.10 MySQL GUI program is

a) MySQL Query Browser.

b) MySQL Admin.

c) MySQL Oracle.

d) MySQL Aroma.

Answer:

(a)

Explanation: MySQL Query Browser is a tool that allows you to execute queries and develop SQL scripts.

Q.11 SQL stands for

a) Standard Question Limited.

b) Structured Quinn Language.

c) Structured Query Language.

d) Standard Question Language.

Answer:

(c)

Explanation: SQL is a database computer language designed for managing data in relational database management systems (RDBMS).

Q.12 DDL stands for

a) Data Department Language.

b) Data Definition Language.

c) Data Development Language.

d) Data Document Language.

Answer:

(b)

Explanation: Data Definition Language (DDL) is a computer language for defining data structures.

Q.13 DML stands for

a) Data Manipulation Language.

b) Data Modification Language.

c) Data Marinated Language.

d) Develop Motivated Language.

Answer:

(a)

Explanation: Data Manipulation Language (DML) is a family of computer languages used by computer programs and/or database users to retrieve, insert, delete and update data in a database.

Q.14 TCL stands for

a) Termination Control Language.

b) Truncation Control Language.

c) Transaction Control Language.

d) Table Centralized Locking.

Answer:

(c)

Explanation: A Transaction Control Language (TCL) is a computer language and a subset of SQL, used to control transactional processing in a database.

Q.15 The number of categories in which SQL command can be divided is

a) two.

b) three.

c) four.

d) five.

Answer:

(d)

Explanation: SQL commands can be divided into following categories:

· Data Definition Language (DDL) Commands

· Data Manipulation Language Commands.

· Transaction Control Language Commands.

· Session Control Commands.

· System Control Commands.

Q.15 The set of commands which allow us to perform tasks related to defining data is

a) DDL.

b) DML.

c) TCL.

d) SCC.

Answer:

(a)

Explanation: Data Definition Language (DDL) is a computer language for defining data structures.

Q.17 The set of commands which are used to change data is

a) DDL.

b) DML.

c) TCL.

d) SCC.

Answer:

(b)

Explanation: Data Manipulation Language (DML) is a family of computer languages used by computer programs and/or database users to retrieve, insert, delete and update data in a database.

Q.18 MySQL can be downloaded from site

a) www.mysite.com

b) www.sql.com

c) www.mysql.com

d) www.dolphin.com

Answer:

(c)

Explanation: MySQL is the world popular open source database, which can be downloaded from site www.sql.com.

Q.19 The company which created MySQL is

a) MYSQL AB.

b) MYSQL BC.

c) MYSQL CD.

d) MYSQL DE.

Answer:

(a)

Explanation: MySQL was created and is supported by MySQL AB, a company based in Sweden. This company is now a subsidiary of Sun Microsystems, which holds the copyright to most of the codebase.

Q.20 The chief inventor of MySQL was

a) Michael Warner.

b) Michael Shawn.

c) Michael Roster.

d) Michael Widenius.

Answer:

(d)

Explanation: Michael Widenius (often called Monty), born on March 3, 1962, in Helsinki, Finland, is the main author of the original version of the open-source MySQL database and a founding member of the MySQL AB company.

Q.21 MySQL has been named after

a) Marie’s daughter.

b) Miral’s daughter.

c) Monty’s daughter.

d) Manger’s daughter.

Answer:

(c)

Explanation: The chief inventor of MySQL was Michael Widenius (often called Monty). MySQL has been named after Monty’s daughter.

Q.22 The logo of MySQL is

a) fish.

b) dolphin.

c) cat.

d) tiger.

Answer:

(b)

Explanation: The logo of MySQL, the dolphin, is named as “Sakila”.

Q.23 MySQL database system is a combination of

a) MySQL server instance and MySQL database.

b) data dictionary and MySQL database.

c) DDL and DML.

d) SCC and TCL.

Answer:

(a)

Explanation: MySQL operates using client/server architecture in which, the server runs on the machine containing the databases and clients connect to the server over a network.

Q.24 LAMP stands for

a) Linux, Apache, MySQL, PHP/Perl/Python.

b) Linux, Ardent, MySQL, PHP/Perl/Python.

c) Larsen, Apache, Miracle, Pinacle.

d) Linux, Ardent, SQL, Perl.

Answer:

(a)

Explanation: LAMP (Linux, Apache, MySQL, PHP/Perl/Python) is a fast growing open source enterprise software stack.

Q.25 ANSI stands for

a) Arabian National Standard Institute.

b) African National Standard Institute.

c) American National Standard Institute.

d) Automated National Standard Institute.

Answer:

(c)

Explanation: The American National Standards Institute or ANSI is a private non-profit organization, that oversees the development of voluntary consensus standards for products, services, processes, systems, and personnel in the United States.

Q.25 Metadata is

a) meta of data.

b) meta inside data.

c) data about Meta.

d) data about data.

Answer:

(d)

Explanation: Metadata (meta data, or sometimes meta information) is "data about other data", of any sort in any media.

Q.27 DCL stands for

a) Data Centralized Language.

b) Data Control Language.

c) Data Collection Language.

d) Data Cross Language.

Answer:

(b)

Explanation: A Data Control Language (DCL) is a computer language and a subset of SQL, used to control access to data in a database.

Q.28 Data Manipulation means

a) retrieval, insertion, deletion and modification.

b) retrieval, insertion, deletion and manifestation.

c) retrieval, invaluation, deletion and modification.

d) ramification, insertion, deletion and modification.

Answer:

(a)

Explanation: Data manipulation is the way in which data can be manipulated and changed.

Q.29 The number of types of DMLs are

a) two.

b) three.

c) four.

d) five.

Answer:

(a)

Explanation: There are two types of DMLs: Procedural DMLs and Non-Procedural DMLs.

Q.30 The type of DML that requires a user to specify what data is needed and how to get it, is

a) Transaction DML.

b) Manipulation DML.

c) Procedural DML.

d) Non-procedural DML.

Answer:

(c)

Explanation: A low-level or procedural DML allows the user, i.e. programmer to specify what data is needed and how to obtain it. This type of DML typically retrieves individual records from the database and processes each separately.

Q.31 The type of DML that requires a user to specify what data is needed without specifying how to get it, is

a) Transaction DML.

b) Manipulation DML.

c) Procedural DML

d) Non-procedural DML.

Answer:

(d)

Explanation: A high-level or non-procedural DML allows the user to specify what data is required without specifying how it is to be obtained. Many DBMSs allow high-level DML statements, either to be entered interactively from a terminal or to be embedded in a general-purpose programming language.

Q.32 To insert a tuple in a table, the DML command used is

a) INSERT IN.

b) INSERT ON.

c) INSERT INTO.

d) INSERT ONTO.

Answer:

(c)

Explanation: For e.g., INSERT INTO "table_name" ("column1", "column2", ...)

VALUES ("value1", "value2", ...)

Q.33 To modify a tuple in a table, the DML command used is

a) SELECT

b) MODIFY.

c) UPDATE.

d) MODIFY TO.

Answer:

(c)

Explanation: The UPDATE statement is used to modify records in a table.

Q.34 To delete a tuple in a table, the DML command used is

a) DESTROY.

b) DAMAGE.

c) DELETE.

d) TRUNCATE.

Answer:

(c)

Explanation: The DELETE statement is used to delete records in a table.

Q.35 One complete unit of work is known as

a) database.

b) transaction.

c) commit.

d) rollback.

Answer:

(b)

Explanation: A database transaction comprises a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions.

Q.35 To manage and control the transactions, the commands used are known as

a) DML.

b) DCL.

c) TCL.

d) CSS.

Answer:

(c)

Explanation: The TCL commands are used to manage transactions.

Q.37 The TCL command that makes all the changes made by statements issued, permanent is known as

a) COMMIT.

b) ROLLBACK.

c) SAVEPOINT.

d) SET TRANSACTION.

Answer:

(a)

Explanation: COMMIT statement is used to end the current transaction and make permanent all changes performed in the transaction.

Q.38 The TCL command that undoes all changes since, the beginning of a transaction or since, a savepoint, is

a) COMMIT.

b) ROLLBACK.

c) SAVEPOINT.

d) SET TRANSACTION.

Answer:

(b)

Explanation: In SQL, ROLLBACK is a command that causes all data changes since the last BEGIN WORK, or START TRANSACTION to be discarded by the relational database management system (RDBMS), so that the state of the data is "rolled back" to the way it was before those changes were made.

Q.39 The TCL command that marks a point upto which all earlier statements have been successfully completed, is

a) COMMIT.

b) ROLLBACK.

c) SAVEPOINT.

d) SET TRANSACTION.

Answer:

(c)

Explanation: The SAVEPOINT statement sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.

Q.40 The TCL command that establishes properties for the current transactions, is

a) COMMIT.

b) ROLLBACK.

c) SAVEPOINT.

d) SET TRANSACTION.

Answer:

(d)

Explanation: SET TRANSACTION sets the transaction isolation level for the current transaction block.

Q.41 A logical data definition can specify

a) addressing techniques.

b) indexing techniques.

c) searching techniques.

d) means of checking for errors in the data.

Answer:

(d)

Explanation: A logical data definition should not specify addressing, indexing, or searching techniques, or specify the placement of data on the storage units.

Q.42 The subdivision of SQL, which is used to create tables, is

a) DDL.

b) DDL AND DML

c) DML

d) DDL or DML

Answer:

(a)

Explanation: Data Definition Language (DDL) provides statements for creation and deletion of tables and indexes.

Q.43 If, I create an object without a TABLESPACE clause, then Oracle stores the segment in

a) system tablespace.

b) users tablespace.

c) default tablespace.

d) Admin tablespace.

Answer:

(c)

Explanation: All the objects without a tablespace clause are stored in Default tablespace.

Q.44 The character, which is used to continue a statement in SQL* PLUS, is

a) *

b) /

c) –

d) @

Answer:

(c)

Explanation: – is used to continue statements in SQL* plus.

Q.45 Assuming today is Monday, 10 July 2000, this is returned by this statement: SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual

a) 03 JUL 00.

b) 10 JUL 00.

c) 12 JUL 00.

d) 17 JUL 00.

Answer:

(d)

Explanation: NEXT_DAY returns the date of the next weekday specified in the 2nd argument.

Q.45 The command, which is used to extract data from database, is

a) SELECT.

b) GET.

c) OPEN.

d) READ.

Answer:

(a)

Explanation: SELECT command is used to select or extract data from database table.

Q.47 The SQL statement, which is used to insert new data in the database, is

a) ADD RECORD.

b) ADD NEW.

c) INSERT.

d) INSERT NEW.

Answer:

(c)

Explanation: INSERT command is used to insert values in the database.

Q.48 The SQL statement, which is used to delete particular data in the database, is

a) DELETE.

b) DROP.

c) TRUNCATE.

d) REMOVE.

Answer:

(a)

Explanation: The SQL TRUNCATE TABLE clause deletes all rows from a database table. We use truncate command only when we want to delete all rows in a table.

Q.49 The correct sql query if I want to change "Hansen" into "Nilsen" in the "LastName" column in the Persons table is

a) UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'.

b) MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen.

c) UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen'.

d) MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen'.

Answer:

(a)

Explanation: When we need to change some values in an existing row we can use update command. It specifies the rows to be changed using the where clause.

Q.50 The statement, which is used to update data in the database, is

a) SAVE AS.

b) UPDATE.

c) SAVE.

d) MODIFY.

Answer:

(b)

Explanation: UPDATE is used to change some values in an existing row command. It specifies the rows to be changed using the WHERE clause.

Q.51 The proper syntax of CREATE TABLE command is

a) CREATE TABLE <table-name> (<datatype>[(size)],<data type> <size>);

b) Create <tablename> <datatype > <size>,<column name><datatype > <size>;

c) CREATE TABLE <tablename> (<column name1 ,<datatype>[<size>]),<column name2 >, datatype>[<size>]..).

d) CREATE <table-name> (<datatype>[(size)],<data type> <size>);

Answer:

(c)

Explanation: When a table is created its column names, data types and size are supplied for each column.

Q.52 A constraint is

a) a command in the SQL *plus.

b) the conditions or checks on table only .

c) the conditions or checks on columns of a table only.

d) the condition or check on one or more columns of a table.

Answer:

(d)

Explanation: A constraint is a condition or check applicable on a field or set of fields. Two basic types of constraints are: column constraints and table constraints.

Q.53 NOT NULL constraint ensures that

a) a column can never have empty values.

b) a column cannot have value 0.

c) a column can have empty value.

d) a column can have value 0.

Answer:

(a)

Explanation: NOT NULL constraint ensure that we cannot leave the column blank or empty and must provide a value to it, whether zero or greater than it.

Q.54 UNIQUE constraint ensures that

a) two rows can have same value in the specified column.

b) no two rows can have same value in the specified column.

c) data delicacy can be there.

d) data inconsistency may /may not be there.

Answer:

(b)

Explanation: It ensures the value in a column is unique. It can be applied to one or more columns.

Q.55 Primary key constraint

a) can have null value.

b) is same as unique constraint.

c) declares column as the primary key and cannot have null value.

d) declares column as the primary key and can have null value.

Answer:

(c)

Explanation: Primary key constraint declares the column, as primary key of the table. Primary key cannot have null values. It can be applied to one or more columns.

Q.55 If, I want to select all columns from a table named CLASS, then the correct method, is

a) SELECT * FROM CLASS.

b) SELECT (ALL) FROM CLASS.

c) SELECT CLASS.

d) SELECT *. FROM CLASS.

Answer:

(a)

Explanation: Data is selected from the table using SELECT command and * is used to select all columns.

Q.57 If, I want to select one column named Marks from a table having duplicate rows named Student, then the correct method, is

a) SELECT Marks FROM Student;

b) SELECT DISTINCT Marks from Student;

c) SELECT Marks. Student;

d) SELECT Student “Marks”;

Answer:

(a)

Explanation: DISTINCT eliminates the duplicate rows from the result of SELECT statement.

Q.58 If, I want to create the Customer table specified as Customer (First Name, Last Name, Address, City, Country, Birth Date), then the SQL command, will be

a) CREATE TABLE Customer (First_Name ,Last_Name ,Address ,City,Country char, Birth_Date );

b) CREATE TABLE Customer (First_Name char(25 Last_Name char(25,Address char(50, City char(25,Country char(25,Birth_Date date );

c) CREATE TABLE Customer (First_Name char(25 Last_Name char(25,Address char(50, City char(25,Country char(25,Birth_Date date (21 );

d) CREATE TABLE Customer (First_Name char(25 ||” “|| Last_Name char(25 || “ “ || Address char(50 || “ “|| City char(25 || “ “||Country char(25||” “|| , Birth_Date date “ ADDRESS” );

Answer:

(b)

Explanation: || is a concatenation operator and only one column will be displayed as “ADDRESS” and the syntax of date is date. It cannot hold size of (21. It is yyyy-mm-dd.

Q.59 Aggregate functions are

a) Avg, min, max, sum and count.

b) Select, update, delete.

c) Avg, sum, create and update.

d) Min, Max, Order By, Group By.

Answer:

(a)

Explanation: Aggregate functions operate on a aggregate of tuples. These are max, min, avg, sum, stddev, count, variance etc. The result of aggregate functions is a single value.

Q.60 If, I want to select all the records from a table named STUDENT where First Name is “Guneet” and Last name is “Kaur”, the SQL command will be

a) SELECT First Name =’Guneet’, Last Name=’Kaur’.

b) SELECT * From STUDENT where First Name =’Guneet’ OR Last Name=’Kaur’.

c) SELECT * From STUDENT where First Name =’Guneet’ AND Last Name=’Kaur’.

d) SELECT * From STUDENT where First Name LIKE ’Guneet’ AND Last Name LIKE ‘Kaur’.

Answer:

(c)

Explanation: AND operator displays a record when all the conditions are met where as OR operator displays the record if any of the condition is met.

Q.61 If, I want to select all the records from a table named "STUDENTS" where the "FIRST NAME" is alphabetically between (and including) "Chinky" and "Guneet", then the SQL command, will be

a) SELECT * FROM STUDENTS WHERE First Name>'Chinky” AND First Name<’Guneet’

b) SELECT * FROM STUDENTS WHERE First Name BETWEEN 'Chinky' AND ‘Guneet’

c) SELECT First Name>'Chinky AND First Name< ‘Guneet’ FROM STUDENTS.

d) SELECT (all) FROM STUDENTS WHERE First Name IS 'Chinky' AND ‘Guneet’

Answer:

(b)

Explanation: Between operator includes the name “Chinky” and “Guneet” also. > and < operators do not include these two names.

Q.62 The problem with navigational data access languages was

a) the user had to have knowledge of the table and index structures.

b) navigational data access was far slower than declarative access.

c) navigational access languages required the coder to embed their queries inside a procedural language shell.

d) navigational languages were far slower then SQL.

Answer:

(a)

Explanation: A navigational data language requires knowledge of the internal tables and index structures.

Q.63 The major problem with SQL is

 

a) SQL cannot support object-orientation.

 

b) the same query can be written in many ways, each with vastly different execution plans.

 

c) SQL syntax is too difficult for non-computer professionals to use.

 

d) SQL creates excessive locks within the Oracle database.

 

Answer:

(b)

Explanation: The declarative nature of SQL makes it possible to write an individual query in many different forms, each with identical results. For example, a query can be written with a standard join, a non-correlated sub-query or a correlated sub-query, each producing identical results but with widely varying internal execution plans.

Q.64 The important feature of relational databases and SQL is

a) independence of table relationship.

b) high speed of SQL.

c) powerful GUI front-end.

d) easy to install and use.

Answer:

(a)

Explanation: Prior to the invention of relational databases, it was very difficult to establish relationships with other database objects and systems relied on pointers in order to establish data relationships. With the advent of relational database management, it became possible to establish ad-hoc relationships using SQL joins.

Q.65 The important consideration when tuning an SQL statement is

a) the number of CPUs on the server.

b) the degree of parallelism on the tables.

c) the use of bitmap indexes.

d) the quality of the SQL optimization.

Answer:

(d)

Explanation: SQL coder need not be concerned with the proper work of joining the tables together.

Q.65 The database design feature, which is most important to SQL performance, is

a) removal of data redundancy.

b) the introduction of data redundancy.

c) the introduction of non-first normal form relations.

d) the introduction of SQL*Plus.

Answer:

(b)

Explanation: The degree of normalization in the database design is critical to SQL performance. The ideal candidates for the introduction of redundancy are small data items that are updated very infrequently.

Q.67 The relationship between indexes and SQL performance is that

a) indexes are only used in special cases.

b) indexes are used to make table storage more efficient.

c) indexes rarely make a difference in SQL performance.

d) indexes exist solely to improve query speed.

Answer:

(d)

Explanation: The sole purpose of indexes in any relational database management system is to reduce the access time for SQL statements. If indexes did not exist in a relational database, then the Oracle database SQL optimizer would have no choice except to read every single row.

Q.68 The number of columns that are presented after executing this query SELECT address1||','||address2||','||address3 "Address" FROM employee are

a) 1.

b) 2.

c) 3.

d) 0.

Answer:

(a)

Explanation: Only one column appears, as || is the concatenation operator.

Q.69 The Oracle access method, which is the fastest way for Oracle to retrieve a single row, is

a) primary key access.

b) access through unique index.

c) table access by ROWID.

d) full table scan.

Answer:

(c)

Explanation: Table access by ROWID is the fastest access method.

Q.70 The valid column name is

a) column.

b) 1966_invoice.

c) Catch_#22.

d) #invoices.

Answer:

(c)

Explanation: A column name cannot start with a digit or hash. A column is a reserved word; it is possible only, if it would have been inside quotes like “column”.

Q.71 The command, which will delete all data from a table and will not write to the rollback segment, is

a) DROP. b) DELETE. c) CASCADE. d) TRUNCATE.

Answer:

(d)

Explanation: The truncate command does not write data to the user segment.

Q.72 The sql statement, which will return only different values, duplicate values will be deleted in select statement, is

a) SELECT DIFFERENT.

b) SELECT UNIQUE.

c) SELECT NOT NULL.

d) SELECT DISTINCT.

Answer:

(d)

Explanation: DISTINCT removes duplicate values.

Q.73 The SQL keyword, which is used for ascending or descending i.e., sorting the result set, is

a) ORDER BY.

b) HAVING.

c) GROUP BY.

d) SORT.

Answer:

(a)

Explanation: ORDER BY is used for sorting the result set. By default it will sort in ascending order but if, we want the result set in descending order, then we use ORDER BY DESC.

Q.74 The SELECT statement used in a view definition can include

a) INSERT.

b) ON ORDER BY.

c) IN ORDER BY

d) ORDER BY

Answer:

(a)

Explanation: A view is a virtual table with no data, but can be operated like any other table.

Q.75 If, I want to change Roll No 400 only for those items that have Roll No 300,

then the SQL statement, will be

a) MODIFY items SET Roll No=400 Where Roll No = 300;

b) UPDATE items SET Roll No=400 Where Roll No = 300;

c) UPDATE items Roll No=400 in place of Roll No = 300;

d) UPDATE items Roll No=400 HAVING Roll No = 300;

Answer:

(b)

Explanation: UPDATE command is used when we want to change the values of some specified columns and it is used with WHERE clause.

Q.75 If, I want to update roll no to 300 and marks to 100 of students having icode <I040, then the SQL statement, will be

a) UPDATE items SET Roll No=400; Marks = 100 Where Icode<I040;

b) UPDATE items SET Roll No=400, Marks = 100 Where Icode<I040;

c) UPDATE items SET Roll No=400 And SET Marks = 100 Where Icode<I040

d) UPDATE items SET Roll No=400, Marks = 100 Where Icode>I040

Answer:

(b)

Explanation: To update multiple columns, multiple column assignments can be specified with SET clause, separated by commas.

Q.77 If, I want to create a view named as “ taxpayee” having details of employees from EMPLOYEE Table that have gross more, than 8000 then the sql statement will be

a) CREATE VIEW taxpayee AS SELECT * FROM EMPLOYEE WHERE gross >8000;

b) SELECT VIEW taxpayee AS SELECT * FROM EMPLOYEE WHERE gross >8000;

c) CREATE VIEW taxpayee AND SELECT * FROM EMPLOYEE WHERE gross >8000;

d) SELECT VIEW taxpayee FROM EMPLOYEE WHERE gross >8000;

Answer:

(a)

Explanation: We can create view using CREATE VIEW, the name of the view to be created, the word AS, and then the query.

Q.78 A view is

a) a table where we can see information.

b) a table which exists.

c) a table which does not exists physically but it is like a window where we can view or change information in a table.

d) a virtual table which contains copy of the data.

Answer:

(c)

Explanation: A view only stores its definition. Its data is derived from base table.

Q.79 Various built-in functions provided in SQL are

a) LOWER, UPPER and REPLICATE.

b) ALTER AND DROP.

c) SELECT, CREATE.

d) UPDATE.

Answer:

(a)

Explanation: SQL provides several types of built-in functions, that return different kinds of information from the database. These are lower, upper, replicate, substr, getdate()etc. SELECT, UPDATE, ALTER, DROP are commands in SQl.

Q.80 A view

a) cannot be updatable.

b) is updatable always.

c) may or may not be updatable.

d) does not exists in SQL.

Answer:

(c)

Explanation: We can use update commands (INSERT, DELETE, UPDATE) on updatable views. A view is updatable if, it has been defined from a single relation and the update query can be mapped on to the base table successfully.

Q.81 If, I have a table EMPLOYEE (ecode, ename, sex, egrade, egross) in which e code is the primary key and I create a view

Create view sample AS SELECT ename, gross FROM employee; And use the following command INSERT INTO sample Values (‘guneet’,5000.00; , then

a) it will insert values.

b) the last two statements can not be mapped on to base table employee.

c) insert command cannot be used.

d) instead of Insert, Update command to be used.

Answer:

(b)

Explanation: The last two statements can not be mapped on to base table employee, as the value of the primary code e code is missing and also the sex column does not have any default value and it cannot have null at the same time. Thus this view is not updatable.

Q.82 The function of the following sql statement is

SELECT replicate (“*”,5 ) FROM Names;

a) it will return *5.

b) it will return **55.

c) it will return *****.

d) it will return 55.

Answer:

(c)

Explanation: It repeats the given expression the specified number of times.

Q.83 The function of the following sql statement is SELECT substr (“Guneet”, 3,3 ) FROM Name;

a) it will return eet.

b) it will return e.

c) it will return Gun.

d) it will return eet eet eet.

Answer:

(a)

Explanation: Substr (expn, start position, no of char) returns the given no of chars (integer) from a character string expn starting at the specified startpos (integer).

Q.84 The function of the following sql statement is SELECT getdate() FROM Name;

a) this function does not exists.

b) it will return the current system date.

c) it will return the previous day system date.

d) it will return the following day system date.

Answer:

(b)

Explanation: This function returns the current system date of your computer.

Q.85 When I drop a table and then use the select statement to access its dependent view, it will result

a) in creating the view.

b) in an error.

c) in showing the view table which had been created.

d) in showing the base table which was dropped.

Answer:

(b)

Explanation: As the table is dropped, that means it has been deleted from database. So its dependent view will not generate any table and give an error.

Q.85 DROP table command in sql lets you drop a table

a) if it has rows.

b) if it is not an empty table.

c) it may or may not be an empty table.

d) only if it is an empty table.

Answer:

(d)

Explanation: A table with rows in it cannot be dropped. First we use a DELETE command to remove all the rows then we can drop the empty table so that it is no longer recognized in the database.

Q.87 To delete a view from database when view is called names and base table employee we use the command

a) DROP VIEW names.

b) DELETE VIEW names.

c) DROP VIEW employee.

d) DROP VIEW.

Answer:

(a)

Explanation: DROP command deletes a view from database. Its syntax is DROP VIEW (viewname).

Q.88 If, I use command DROP VIEW names, then

a) only view names will be deleted from database.

b) view names as well as its base table will be deleted from data base.

c) only base table will be deleted.

d) view name will not be deleted.

Answer:

(a)

Explanation: When a view is dropped, only view is deleted. It does not cause any change in the base table and the base table remains intact.

Q.89 To remove the tuples from an employee table where salary >5000 the sql query is

a) DELETE FROM table named employee WHERE salary >5000;

b) DELETE employee WHERE salary >5000;

c) DELETE FROM employee WHERE salary >5000;

d) DELETE employee having salary>5000;

Answer:

(c)

Explanation: Delete command is used to remove some or all of the rows in a table.

Q.90 To remove all the tuples from an employee table the SQL query is

a) DELETE FROM employee;

b) DELETE ALL FROM employee;

c) DELETE * FROM employee;

d) DELETE employee;

Answer:

(a)

Explanation: DELETE <tablename> will delete all the rows in a table and table would be empty.

Q.91 To insert values in the tablename students (name, roll no, sex, grade, salary) the SQL query would be

a) INSERT INTO students VALUES (2,’guneet’, ‘f’,’e1’, 10,000;

b) INSERT INTO students VALUES (2,’guneet’, ‘e1,’f’, 10,000;

c) INSERT INTO students VALUES (‘guneet’, 20,‘f’,’e1’, 10,000;

d) INSERT INTO (name, roll no, sex, grade, salary) VALUES (‘guneet’, ‘20’,‘f’,’e1’, 10,000;

Answer:

(c)

Explanation: To insert values either of these commands can be used. INSERT INTO students VALUES (‘aditi’, ‘20’,‘f’,’e1’, 10,000; or INSERT INTO students (name, roll no, sex, grade, salary) VALUES (‘aditi’, ‘20’,‘f’,’e1’, 10,000;. The data values order should match with the table columns when we create table.

Q.92 If, I want to insert only name and roll no, then I use the command INSERT INTO students VALUES (‘guneet’, 20; the columns that are not listed

a) will have 0 value.

b) will have null value always.

c) will have default value always.

d) will have default value if it is defined otherwise Null value.

Answer:

(d)

Explanation: In an INSERT statement, only those columns can be omitted that have either default value defined or they allow null values.

Q.93 If, I want to select the rows from students table whose salary >2000 and place them in the employee table, then the sql query would be

a) INSERT INTO employee SELECT * FROM student WHERE salary >2000;

b) INSERT INTO employee SELECT * FROM students WHERE salary >2000;

c) INSERT INTO students SELECT * FROM employee WHERE salary >2000;

d) INSERT INTO employee SELECT * FROM student HAVING salary >2000;

Answer:

(b)

Explanation: Here, simply replace the VALUES clause with the sql query selecting the rows from the employee table.

Q.94 ALTER TABLE command

a) add columns in a table only.

b) modify the column size only.

c) delete some columns in a table only.

d) can either add columns, modify the sizes or delete columns in a table.

Answer:

(d)

Explanation: ALTER table command is used to change the definitions of existing tables. It can modify, delete or change sizes of columns.

Q.95 If, I want to calculate the total salary for employees grade ‘A1’, then the sql query would be

a) SELECT total (salary) FROM employee WHERE grade=’A1’;

b) SELECT sum (salary) FROM employee WHERE grade=”A1”;

c) SELECT sum (salary) FROM employee WHERE grade=’A1’;

d) SELECT sum (salary) FROM employee HAVING (grade) =”A1”;

Answer:

(c)

Explanation: Sum is used to calculate the total salary.

Q.96 To display the average salary of employee with grades ‘E2’ or ‘E4’, the sql command is

a) SELECT avg(salary) FROM employee WHERE (grade=’E2’ OR grade=’E4’);

b) SELECT avg(salary WHERE (grade=’E2’ OR grade=’E4’);

c) DISPLAY avg(salary) FROM employee WHERE (grade=’E2’ OR grade=’E4’);

d) SELECT avg(salary) FROM employee HAVING (grade=’E2’ AND grade=’E4’);

Answer:

(a)

Explanation: AVERAGE is an aggregate function used in SQL and is used with WHERE clause.

Q.97 To count the number of employees in employee table, the sql command is

a) SELECT count (*) FROM employee;

b) SELECT count (*) employee;

c) SELECT * FROM employee;

d) SELECT count FROM employee;

Answer:

(a)

Explanation: COUNT is also an aggregate function used in SQL.

Q.98 To count the number of cities, the different members belong to, in employee

table, the SQL command is

a) SELECT count (DISTINCT) FROM employee;

b) SELECT count (DISTINCT city) FROM employee;

c) SELECT count (ALL city) FROM employee;

d) SELECT count (DISTINCT city);

Answer:

(b)

Explanation: DISTINCT keyword ensures that the multiple entries of the same city are ignored.

Q.99 To count the number of non null cities in which different members belong to, in employee table, the SQL command is

a) SELECT count (ALL city) FROM employee;

b) SELECT count (DISTINCT) FROM employee;

c) SELECT count (ALL) FROM employee;

d) SELECT count (DISTINCT city) FROM employee;

Answer:

(a)

Explanation: As we want to count the entries including repeating entries for the city we have to use keyword ALL. The * is the only argument that includes null when it is used only with COUNT.

Q.100 The subdivision of SQL, which is used to put values in tables, is

a) DDL.

b) DDL AND DML.

c) DML.

d) DNL.

Answer:

(c)

Explanation: Data Manipulation Language (DML) provides statements to enter, update, delete data and perform complex queries on these tables.

Frequently Asked Questions on chapter-Introduction to MySQL