chapter-Simple Queries in SQL

Important MCQ-Based Questions on Informatics Practices (IP) class 11 chapter-Simple Queries in SQL

This page consists of Important MCQ-Based Questions on Informatics Practices (IP) class 11 chapter-Simple Queries in SQL 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-Simple Queries in SQL

Important Questions for Informatics Practices (IP) class 11 chapter-Simple Queries in SQL set-1

Informatics Practices - MCQ on Simple Queries in SQL

Class XI

Q.1 The means to identify type of data and associated operations for handling it, is known as

a) database.

b) data select.

c) data type.

d) data genre.

Answer:

(c)

Explanation: A value’s data type associates a fixed set of properties with the value. These properties cause different treatment of data types.

Q.2 The fixed data value is known as

a) literal.

b) null.

c) comment.

d) data type.

Answer:

(a)

Explanation: The fixed data value may be of character type or numeric type.

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 The operator with the lowest precedence is

a) &&.

b) XOR.

c) ||

d) :=

Answer:

(d)

Explanation: When an expression has multiple operators, then the evaluation of expression takes place in the order of operator precedence. The operator with the lowest precedence is evaluated at last.

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.

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 QueryBrowser.

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 define the data structures 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 allow us to insert, delete and update 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 downloadable from site www.sql.com .

Q.19 The company which created MySQL is

a) AB.

b) BC.

c) CD.

d) 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 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 ( 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 Standard 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 (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, valuation, 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 update 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 Columns with no value is known as

a) literal.

b) null.

c) comment.

d) data type.

Answer:

(b)

Explanation: If a column in a row has no value, then column is said to be null, or to contain a null.

Q.38 Text that is not executed is known as

a) literal.

b) null.

c) comment.

d) data type.

Answer:

(c)

Explanation: Comments are only for documentation purposes. A comment describes the statement’s purpose within an application.

Q.39 Word having a special meaning is known as

a) literal.

b) null.

c) comment.

d) keyword.

Answer:

(d)

Explanation: Keywords are understood to be instructions. Generally, SQL keywords are printed in capital letters.

Q.40 The number of types of numeric literals is

a) two.

b) three.

c) four.

d) five.

Answer:

(a)

Explanation: There are two types of numeric literals – integer and real literals.

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 or 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

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 as a continuation operator 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 dua

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 The keyword used to select rows containing columns that match a wildcard pattern is

a) LIKE.

b) BETWEEN.

c) DISTINCT.

d) ALL.

Answer:

(a)

Explanation: Patterns are described using two special wildcard characters: % and _ . Example of LIKE keyword:

SELECT name

FROM emp

WHERE name LIKE ‘% y’;

Q.54 The keyword that is used to select rows that do not match the specified pattern of characters is

a) NOT LIKE.

b) NOT BETWEEN.

c) NOT DISTINCT.

d) NOT ALL.

Answer:

(a)

Explanation: The LIKE keyword is used to select rows containing columns that match a wildcard pattern. NOT LIKE operator is the opposite of LIKE operator.

Q.55 The operator with the highest precedence is

a) ^.

b) &.

c) *

d) !

Answer:

(d)

Explanation: When an expression has multiple operators, then the evaluation of expression takes place in the order of operator precedence. The operator with the highest precedence is evaluated at last.

Q.56 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.

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 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 The example of integer literal is

a) 16

b) 0.16

c) 16.0

d) 1.6

Answer:

(a)

Explanation: 16 is an integer literal and others are examples of real literals.

Q.60 If, I want to select all the records from a table named STUDENTS where First Name is “Guneet” and Last name is “Kaur”, then 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 does not include these two names.

Q.62 The example of real literal is

a) 16

b) 16.5

c) 165

d) 15

Answer:

(b)

Explanation: 16.5 is a real literal and others are integer literals.

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 number of categories in which the different data types can be divided is

a) two.

b) three.

c) four.

d) five.

Answer:

(b)

Explanation: MySQL uses many data types, divided into three categories: numeric, date and time and string types.

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 A special type of predefined command set that performs some operation and returns a single value, is

a) parameter.

b) argument.

c) function.

d) command.

Answer:

(c)

Explanation: Functions operate on zero, one, two or more values that are provided to them.

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: The SQL DISTINCT command used along with the SELECT keyword retrieves only unique data entries depending on the column list one has specified after it.

Q.73 The SQL keyword, which is used in ascending or descending order, 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 BLOB stands for

a) Binary Large Objects.

b) Binary Large Oriented Blocks.

c) Binary levelled Blocks.

d) Binary Large oblongata Brand.

Answer:

(a)

Explanation: BLOBs are “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files.

Q.75 If, we 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, we 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 The syntax of using IFNULL() function is

a) IFNULL ( value to be substituted, columnname)

b) IFNULL ( columnname, value to be substituted )

c) IFNULL ( rowname, columnname)

d) IFNULL ( value to be deleted, columnname)

Answer:

(b)

Explanation: For example – IFNULL (death, ‘alive’)

In the death column name, null value is replaced by alive.

Q.78 To compare two values,

a) logical operator is used.

b) conceptual operator is used.

c) relational operator is used.

d) binary operator is used.

Answer:

(c)

Explanation: The result of the comparison is true or false. The SQL recognizes following relational operator: =, >, < ,<=, >=, <>.

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 The symbol of OR operator is

a) ||

b) &&

c) !

d) ^

Answer:

(a)

Explanation: The logical operators OR (||), AND (&&) and NOT (!) are used to connect search conditions in the WHERE clause.

Q.81 The symbol of AND operator is

a) ||

b) &&

c) !

d) ^

Answer:

(b)

Explanation: The logical operators OR (||), AND (&&) and NOT (!) are used to connect search conditions in the WHERE clause.

Q.82 The symbol of NOT operator is

a) ||

b) &&

c) !

d) ^

Answer:

(c)

Explanation: The logical operators OR (||), AND (&&) and NOT (!) are used to connect search conditions in the WHERE clause.

Q.83 To select all the records of the table, the command used is

a) SELECT * FROM <tablename>

b) SELECT & FROM <tablename>

c) SELECT ^ FROM <tablename>

d) SELECT $ FROM <tablename>

Answer:

(a)

Explanation: * sign is used for selecting all the rows of the table.

Q.84 The function of the sql statement: SELECT getdate() FROM Names;

is

a) it will return the current system date.

b) it will return the previous day system date.

c) it will return the following day system date.

d) it will return a compiler error

Answer:

(a)

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

Q.85 The operator that defines a range of values, that the column values must fall in to make the condition true, is known as

a) OR operator.

b) AND operator.

c) NOT operator.

d) BETWEEN operator.

Answer:

(d)

Explanation: The range includes both lower value and upper value. For example:

SELECT icode

FROM items

WHERE icode BETWEEN 30 and 50;

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) if 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 specify a list of values, the operator used is

a) LIKE.

b) IN.

c) ON.

d) BETWEEN.

Answer:

(b)

Explanation: The IN operator selects values that match any value in a given list of values.

Q.88 The operator that finds rows that do not match in the list is known as

a) NOT LIKE.

b) NOT IN

c) NOT ON.

d) NOT BETWEEN.

Answer:

(b)

Explanation: NOT IN is the opposite of IN operator. The NOT IN operator selects values that do not match any value in a given list of values.

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

b) DELETE ALL FROM employee;

c) DELETE * FROM employee;

d) DELETE FROM 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. Example: 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: We will 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.95 To display the average salary of employees 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, the 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.