Quiz Time
SQL -Structured Query Language

Which one of the following is used to define the structure of the relation, deleting relations and relating schemas?

• 1]

  DML(Data Manipulation Langauge)

• 2]

  DDL(Data Definition Langauge)

• 3]

  Query

• 4]

  Relational Schema

Updates that violate __________ are disallowed.

• 1]

  Integrity constraints

• 2]

  Transaction control

• 3]

  Authorization

• 4]

  DDL constraints

INSERT INTO instructor VALUES (10211, ’Smith’, ’Biology’, 66000);

What type of statement is this?

• 1]

  Query

• 2]

  DML

• 3]

  Relational

• 4]

  DDL

DELETE FROM r;   /* r - relation  */

This command performs which of the following action?

• 1]

  Remove relation

• 2]

  Clear relation entries

• 3]

  Delete fields

• 4]

  Delete rows

To remove a relation from an SQL database, we use the ______ command.

• 1]

  Delete

• 2]

  Purge

• 3]

  Remove

• 4]

  Drop table

An attribute A of datatype varchar(20) has the value “Avi”. The attribute B of datatype char(20) has value ”Reed”. Here attribute A has ____ spaces and attribute B has ____ spaces.

• 1]

  3, 20

• 2]

  20, 4

• 3]

  20, 20

• 4]

  3, 4

The basic datatype char (n) is a _______ length character string and varchar (n) is _______ length character.

• 1]

  Fixed, equal

• 2]

  Equal, variable

• 3]

  Fixed, variable

• 4]

  Variable, equal

SELECT * FROM employee

What type of statement is this?

• 1]

  DML

• 2]

  DDL

• 3]

  View

• 4]

  Integrity constraint

CREATE TABLE employee (name VARCHAR, id INTEGER)

What type of statement is this?

• 1]

  DML

• 2]

  DDL

• 3]

  View

• 4]

  Integrity constraint

Which one of the following provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database?

• 1]

  DML(Data Manipulation Langauge)

• 2]

  DDL(Data Definition Langauge)

• 3]

  Query

• 4]

  Relational Schema

The database administrator who authorizes all the new users, modifies the database and takes grants privilege is

• 1]

  Operator of operating system

• 2]

  Administrator

• 3]

  Super user

• 4]

  All of the mentioned

Which of the following is used to provide privilege to only a particular attribute?

• 1]

  Grant update(budget,salary,Rate) on department to Raj

• 2]

  Grant update(budget) on department to Raj

• 3]

  Grant select on employee to Amit

• 4]

  Grant delete to Amit

Which of the following statement is used to remove the privilege from the user Amir?

• 1]

  Remove update on department from Amir

• 2]

  Revoke update on employee from Amir

• 3]

  Delete select on department from Raj

• 4]

  Grant update on employee from Amir

Which of the following is used to provide delete authorization to instructor?

• 1]

  CREATE ROLE instructor ;
  GRANT DELETE TO instructor;

   

• 2]

  CREATE ROLE instructor;
  GRANT SELECT ON takes
  TO instructor;

   

• 3]

  CREATE ROLE instructor;
  GRANT DELETE ON takes
  TO instructor;

   

• 4]

  All of the mentioned

Which of the following is true regarding views?

• 1]

  The user who creates a view cannot be given update authorization on a view without having update authorization on the relations used to define the view

• 2]

  The user who creates a view cannot be given update authorization on a view without having update authorization on the relations used to define the view

• 3]

  If a user creates a view on which no authorization can be granted, the system will allow the view creation request

• 4]

  A user who creates a view receives all privileges on that view

If we wish to grant a privilege and to allow the recipient to pass the privilege on to other users, we append the __________ clause to the appropriate grant command.

• 1]

  With grant

• 2]

  Grant user

• 3]

  Grant pass privilege

• 4]

  With grant option

In the authorization graph, if DBA provides authorization to u1 which in turn gives to u2 which of the following is correct?

• 1]

  If DBA revokes authorization from u1 then u2 authorization is also revoked

• 2]

  If u1 revokes authorization from u2 then u2 authorization is revoked

• 3]

  If DBA & u1 revokes authorization from u1 then u2 authorization is also revoked

• 4]

  If u2 revokes authorization then u1 authorization is revoked

Which of the following is used to avoid cascading of authorizations from the user?

• 1]

  Granted by the current role

• 2]

  Revoke select on the department from Amit, Satoshi restrict;

• 3]

  Revoke grant option for select on the department from Amit;

• 4]

  Revoke select on the department from Amit, Satoshi cascade;

Which of the following statements creates a new table temp instructor that has the same schema as an instructor?

• 1]

  Create table temp_instructor;

   

• 2]

  Create table temp_instructor like instructor;

   

• 3]

  Create Table as temp_instructor;

   

• 4]

  Create table like temp_instructor;

   

In contemporary databases, the top level of the hierarchy consists of _______ each of which can contain ______

• 1]

  Catalogs, schemas

• 2]

  Schemas, catalogs

• 3]

  Environment, schemas

• 4]

  Schemas, Environment

Which of the following closely resembles Create view?

• 1]

  Create table . . .like

• 2]

  Create table . . . as

• 3]

  With data

• 4]

  Create view as

In order to ensure that an instructor’s salary domain allows only values greater than a specified value use:

CREATE DOMAIN YearlySalary NUMERIC(8,2)
CONSTRAINT salary VALUE test __________;

• 1]

  Value>=30000.00

• 2]

  Not null;

• 3]

  Check(value >= 29000.00);

• 4]

  Check(value)

Values of one type can be converted to another domain using which of the following?

• 1]

  Cast

• 2]

  Drop type

• 3]

  Alter type

• 4]

  Convert

The user-defined data type can be created using

• 1]

  Create datatype

• 2]

  Create data

• 3]

  Create definetype

• 4]

  Create type

Which of the following is used to store movie and image files?

• 1]

  Clob

• 2]

  Blob

• 3]

  Binary

• 4]

  Image

Here which one denotes the relation for which index is created?

Create index studentID_index on student(ID);

• 1]

  StudentID_index

• 2]

  ID

• 3]

  StudentID

• 4]

  Student

An ________ on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently, without scanning through all the tuples of the relation.

• 1]

  Index

• 2]

  Reference

• 3]

  Assertion

• 4]

  Timestamp

Dates must be specified in the format

• 1]

  mm/dd/yy

• 2]

  yyyy/mm/dd

• 3]

  dd/mm/yy

• 4]

  yy/dd/mm

The SQL database language includes statements for:

• 1]

  Database definition

• 2]

  Database manipulation

• 3]

  Database control

• 4]

  All of the above

Which SQL Query is used to remove a table and all its data from the database?

• 1]

  Create Table

• 2]

  Alter Table

• 3]

  Drop Table

• 4]

  None of these

A type of query that is placed within a WHERE or HAVING clause of another query is called

• 1]

  Super query

• 2]

  Multi-query

• 3]

  Subquery

• 4]

  Master query

To relate one table to another, a _____ in one table is used to point to the primary key in another table

• 1]

  primary key

• 2]

  foreign key

What does SQL stand for?

• 1]

  Structured Query Language

• 2]

  Sequential Query Language

• 3]

  Structured Question Language

• 4]

  Sequential Question Language

Fact tables are which of the following?

• 1]

  Completely demoralized

• 2]

  Completely normalized

• 3]

  Partially demoralized

• 4]

  Partially normalized

A snowflake schema is which of the following types of tables?

• 1]

  Fact

• 2]

  Dimension

• 3]

  Helper

• 4]

  All of the above

A goal of data mining includes which of the following?

• 1]

  To explain some observed event or condition

• 2]

  To confirm that data exists

• 3]

  To analyze data for expected relationships

• 4]

  To create a new data warehouse

Primitive operations common to all record management systems include

• 1]

  Print

• 2]

  Sort

• 3]

  Look-up

• 4]

  All of the above

Each of the data files has a ______ that describe the way the data is stored in the file

• 1]

  File structure

• 2]

  Records

• 3]

  Database

• 4]

  Fields

After you ______ a record, many data management environments require you to issue a command to save the changes you made

• 1]

  Delete

• 2]

  Update

• 3]

  Sort key

• 4]

  Index

Sort/report generators

• 1]

  are faster than index/report generators

• 2]

  require more disk space than index/report generators

• 3]

  do not need to sort before generating a report

• 4]

  both (a) and (b)

An-owner-member set in the CODASYL specifications may have

• 1]

  only one owner but many owner occurrences

• 2]

  only one member but many member occurrences

• 3]

  more than one member but only one occurrence per member

• 4]

  All of the above

Which of the following hardware components is the most important to the operation of a database management system?

• 1]

  high-resolution video display

• 2]

  printer

• 3]

  high speed, large-capacity disk

• 4]

  plotter

A database application can perform which of the following activities?

• 1]

  Add records

• 2]

  Update records

• 3]

  Read records

• 4]

  All of the above

An enterprise database is which of the following?

• 1]

  A database designed to support only users external to an organization

• 2]

  A database designed to support an entire organization

• 3]

  A database designed to support a single PC

• 4]

  A database designed to support a relatively small group

Legacy data is which of the following?

• 1]

  Data contained in a newly-installed system

• 2]

  Data contained in a file system

• 3]

  Data rejected during the installation of a new system

• 4]

  Data contained by a system used prior to the installation of a new system

Which of the following is true for a relational database?

• 1]

  Data is represented by tables

• 2]

  Complex programs have to be written for simple queries

• 3]

  It is difficult to access data

• 4]

  All of the above

A shared database should

• 1]

  be accurate and available

• 2]

  be secure

• 3]

  provide for backup and recovery

• 4]

  All of the above

A data warehouse uses

• 1]

  partial operational data

• 2]

  future operational data

• 3]

  historical operational data

• 4]

  health care data

A database management system (DBMS) is a

• 1]

  hardware system used to create, maintain and provide controlled access to a database

• 2]

  a software system used to create, maintain and provide controlled access to a database

• 3]

  hardware system used to create, maintain, and provide uncontrolled access to a database

• 4]

  a software system used to create, maintain, and provide uncontrolled access to a database

An enterprise data model is

• 1]

  a graphical model that shows the high-level entities for an organization

• 2]

  a graphical model that shows all entities for an organization

• 3]

  a non-graphical model that shows the high-level entities for an organization

• 4]

  a non-graphical model that shows all entities for an organization

A data warehouse is which of the following?

• 1]

  Can be updated by end users

• 2]

  Contains numerous naming conventions and formats

• 3]

  Organized around important subject areas

• 4]

  Contains only current data

An operational system is which of the following?

• 1]

  A system that is used to run the business in real-time and is based on historical data

• 2]

  A system that is used to run the business in real-time and is based on current data

• 3]

  A system that is used to support decision making and is based on current data

• 4]

  A system that is used to support decision making and is based on historical data

The generic two-level data warehouse architecture includes which of the following?

• 1]

  At least one data mart

• 2]

  Data that can be extracted from numerous internal and external sources

• 3]

  Near real-time updates

• 4]

  All of the above

The active data warehouse architecture includes which of the following?

• 1]

  At least one data mart

• 2]

  Data that can extracted from numerous internal and external sources

• 3]

  Near real-time updates

• 4]

  All of the above

Reconciled data is which of the following?

• 1]

  Data stored in the various operational systems throughout the organization

• 2]

  Current data intended to be the single source for all decision support systems

• 3]

  Data stored in one operational system in the organization

• 4]

  Data that has been selected and formatted for end-user support applications

Transient data is which of the following?

• 1]

  Data in which changes to existing records cause the previous version of the records to be eliminated

• 2]

  Data in which changes to existing records do not cause the previous version of the records to be eliminated

• 3]

  Data that are never altered or deleted once they have been added

• 4]

  Data that are never deleted once they have been added

The extract process is which of the following?

• 1]

  Capturing all of the data contained in various operational systems

• 2]

  Capturing a subset of the data contained in various operational systems

• 3]

  Capturing a subset of the data contained in various decision support systems

• 4]

  Capturing all of the data contained in various decision support systems

Data scrubbing is which of the following?

• 1]

  A process to reject data from the data warehouse and to create the necessary indexes

• 2]

  A process to load the data in the data warehouse and to create the necessary indexes

• 3]

  A process to upgrade the quality of data after it is moved into a data warehouse

• 4]

  A process to upgrade the quality of data before it is moved into a data warehouse

The load and index are which of the following?

• 1]

  A process to reject data from the data warehouse and to create the necessary indexes

• 2]

  A process to load the data in the data warehouse and to create the necessary indexes

• 3]

  A process to upgrade the quality of data before it is moved into a data warehouse

• 4]

  A process to upgrade the quality of data after it is moved into a data warehouse

Data transformation includes which of the following?

• 1]

  A process to change data from a detailed level to a summary level

• 2]

  Joining data from one source into various sources of data

• 3]

  A process to change data from a summary level to a detailed level

• 4]

  Separating data from one source into various sources of data

A multifield transformation does which of the following?

• 1]

  Converts data from one field into multiple fields

• 2]

  Converts data from multiple fields into one field

• 3]

  Converts data from multiple fields into multiple fields

• 4]

  All of the above

A star schema has what type of relationship between a dimension and fact table?

• 1]

  One-to-one

• 2]

  Many-to-many

• 3]

  One-to-many

• 4]

  All of the above

Which of these queries will display the table given above?

• 1]

  Select employee from name

• 2]

  Select name

• 3]

  Select name from employee

• 4]

  Select employee

Here which of the following displays the unique values of the column?

SELECT ________ dept_name 
FROM instructor;

• 1]

  All

• 2]

  From

• 3]

  Distinct

• 4]

  Name

The query given below will not give an error. Which one of the following has to be replaced to get the desired output?

SELECT ID, name, dept name, salary * 1.1
WHERE instructor;

• 1]

  Salary*1.1

• 2]

  ID

• 3]

  Where

• 4]

  Instructor

This Query can be replaced by which one of the following?

SELECT name, course_id
FROM instructor, teaches
WHERE instructor_ID= teaches_ID;

• 1]

  Select name,course_id from teaches,instructor where instructor_id=course_id;

• 2]

  Select name, course_id from instructor natural join teaches;

• 3]

  Select name, course_id from instructor;

• 4]

  Select course_id from instructor join teaches;

SELECT * FROM employee WHERE salary>10000 AND dept_id=101;

Which of the following fields are displayed as output?

• 1]

  Salary, dept_id

• 2]

  Employee

• 3]

  Salary

• 4]

  All the field of employee relation

This is the Employee table.
Which of the following employee_id will be displayed for the given query?

SELECT * FROM employee WHERE employee_id>1009;

• 1]

  1009, 1001, 1018

• 2]

  1009, 1018

• 3]

  1001

• 4]

  1018

Which of the following statements contains an error?

• 1]

  Select * from emp where empid = 10003;

• 2]

  Select empid from emp where empid = 10006;

• 3]

  Select empid from emp;

• 4]

  Select empid where empid = 1009 and lastname = ‘GELLER’;

In the given query which of the keyword has to be inserted?

INSERT INTO employee _____ (1002,Joey,2000);

• 1]

  Table

• 2]

  Values

• 3]

  Relation

• 4]

  Field

A Delete command operates on ______ relation.

• 1]

  One

• 2]

  Two

• 3]

  Several

• 4]

  Null

Delete from r where P;

The above command

• 1]

  Deletes a particular tuple from the relation

• 2]

  Deletes the relation

• 3]

  Clears all entries from the relation

• 4]

  All of the mentioned

Which one of the following deletes all the entries but keeps the structure of the relation.

• 1]

  Delete from r where P;

   

• 2]

  Delete from instructor where dept name= ’Finance’;

   

• 3]

  Delete from instructor where salary between 13000 and 15000;

   

• 4]

  Delete from instructor;

   

Which of the following is used to insert a tuple from another relation?

• 1]

  INSERT INTO course (course id, title, dept name, credits)
  VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4);

   

• 2]

  INSERT INTO instructor
  SELECT ID, name, dept name, 18000
  FROM student
  WHERE dept name = ’Music’ AND tot cred > 144;

   

• 3]

  INSERT INTO course VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4);

   

• 4]

  Not possible

Which of the following deletes all tuples in the instructor relation for those instructors associated with a department located in the Watson building which is in department relation.

• 1]

  DELETE FROM instructor
  WHERE dept_name IN 'Watson';

   

• 2]

  DELETE FROM department 
  WHERE building='Watson';

   

• 3]

  DELETE FROM instructor
  WHERE dept_name IN (
  SELECT dept name FROM department
  WHERE building = ’Watson’);

   

• 4]

   None of the mentioned

Fill in with correct keyword to update the instructor relation.

UPDATE instructor
_____ salary= salary * 1.05;

• 1]

  Where

• 2]

  Set

• 3]

  In

• 4]

  Select

 _________ are useful in SQL update statements, where they can be used in the set clause.

• 1]

  Multiple queries

• 2]

  Sub queries

• 3]

  Update

• 4]

  Scalar subqueries

The problem of ordering the update in multiple updates is avoided using

• 1]

  Set

• 2]

  Where

• 3]

  Case

• 4]

  When

Which of the following is the correct format for case statements.

• 1]

  CASE
  WHEN pred1 ... result1
  WHEN pred2 ... result2
  . . .
  WHEN predn ... resultn
  ELSE result0
  END

   

• 2]

  CASE
  WHEN pred1 THEN result1
  WHEN pred2 THEN result2
  . . .
  WHEN predn THEN resultn
  ELSE result0
  END

   

• 3]

  CASE
  WHEN pred1 THEN result1
  WHEN pred2 THEN result2
  . . .
  WHEN predn THEN resultn
  ELSE result0

   

• 4]

  All of the mentioned

Which of the following relation updates all instructors with salary over $100,000 receive a 3 percent raise, whereas all others receive a 5 percent raise.

• 1]

  UPDATE instructor
  SET salary = salary * 1.03
  WHERE salary > 100000;
  UPDATE instructor
  SET salary = salary * 1.05
  WHERE salary <= 100000;

   

• 2]

  UPDATE instructor
  SET salary = salary * 1.05
  WHERE salary < (SELECT avg (salary)
  FROM instructor);

   

• 3]

  UPDATE instructor
  SET salary = CASE
  WHEN salary <= 100000 THEN salary * 1.03
  ELSE salary * 1.05
  END

   

• 4]

  None of the mentioned

What is not an advantage of stored procedures?

• 1]

  Greater security

• 2]

  SQL can be optimized

• 3]

  Code sharing

• 4]

  Increased network traffic

A reason for using an SQL view to hide columns is

• 1]

  to simplify a result only

• 2]

  to prevent the display of sensitive data only

• 3]

  to accomplish both of the above

• 4]

  None of the above

Which of the following is an SQL trigger supported by Oracle?

• 1]

  BEFORE

• 2]

  AFTER

• 3]

  INSTEAD OF

• 4]

  All of the above

The SQL ALTER statement can be used to

• 1]

  Change the table structure

• 2]

  Change the table data

• 3]

  Add rows to the table

• 4]

  Delete rows from the table

What SQL structure is used to limit column values of a table?

• 1]

  The LIMIT constraint

• 2]

  The CHECK constraint

• 3]

  The VALUE constraint

• 4]

  None of the above

Which is NOT one of the most common types of SQL CHECK constraints?

• 1]

  System date

• 2]

  Range checks

• 3]

  Lists of values

• 4]

  Comparing one column value to another within the same table

What is an advantage of placing computations in SQL views

• 1]

  To save users from having to write an expression

• 2]

  To ensure that the results are consistent

• 3]

  To accomplish both of the above

• 4]

  None of the above

Views constructed from SQL SELECT statements that conform to the SQL-92 standard may not contain

• 1]

  GROUP BY

• 2]

  WHERE

• 3]

  ORDER BY

• 4]

  FROM

‘AS’ clause is used in SQL for

• 1]

  Selection operation

• 2]

  Rename operation

• 3]

  Join operation

• 4]

  Projection operation

The language which has recently become the defacto standard for interfacing application programs with relational database system is

• 1]

  Oracle

• 2]

  SQL

• 3]

  DBase

• 4]

  4GL

A report generator is used to

• 1]

  update files

• 2]

  print files on paper

• 3]

  data entry

• 4]

  delete files

Count function in SQL returns the number of

• 1]

  Values

• 2]

  Distinct values

• 3]

  Groups

• 4]

  Columns

The statement in SQL which allows changing the definition of a table is

• 1]

  Alter

• 2]

  Update

• 3]

  Create

• 4]

  Select

Which of the following is correct

• 1]

  a SQL query automatically eliminates duplicates

• 2]

  SQL permits attribute names to be repeated in the same relation

• 3]

  a SQL query will not work if there are no indexes on the relations

• 4]

  None of these

Which of the following is a valid SQL type?

• 1]

  CHARACTER

• 2]

  NUMERIC

• 3]

  FLOAT

• 4]

  All of the above

Which of the following is an advantage of view?

• 1]

  Data security

• 2]

  Derived columns

• 3]

  Hiding of complex queries

• 4]

  All of the above

Which of the following is a legal expression in SQL?

• 1]

  SELECT NULL FROM EMPLOYEE;

   

• 2]

  SELECT NAME FROM EMPLOYEE;

   

• 3]

  SELECT NAME FROM EMPLOYEE WHERE SALARY = NULL;

   

• 4]

  None of the above

   

Which of the following is a comparison operator in SQL?

• 1]

  =

• 2]

  LIKE

• 3]

  BETWEEN

• 4]

  All of the above

Which one of the following is not true for a view

• 1]

  View is derived from other tables

• 2]

  View is a virtual table

• 3]

  A view definition is permanently stored as part of the database

• 4]

  View never contains derived columns

To delete a particular column in a relation the command used is

• 1]

  UPDATE

• 2]

  DROP

• 3]

  ALTER

• 4]

  DELETE

The granting and revoking of roles by the user may cause some confusion when that user role is revoked. To overcome the above situation

• 1]

  By restricting the user access to the roles

• 2]

  The privilege must be granted only by roles

• 3]

  The privilege is granted by roles and users

• 4]

  The user role cannot be removed once given

Which join condition contains an equality operator:

• 1]

  Equijoins

• 2]

  Cartesian

• 3]

  Natural

• 4]

  Left

A __________ is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data.

• 1]

  Procedures

• 2]

  Triggers

• 3]

  Functions

• 4]

  None of the mentioned

The CREATE TRIGGER statement is used to create the trigger. THE _____ clause specifies the table name on which the trigger is to be attached. The ______ specifies that this is an AFTER INSERT trigger.

• 1]

  For insert, on

• 2]

  Both a and c

• 3]

  For, insert

• 4]

  On, for insert

What are the after triggers?

• 1]

  Triggers generated after a particular operation

• 2]

  These triggers run after an insert, update or delete on a table

• 3]

  These triggers run after an insert, views, update, or delete on a table

• 4]

  Both b and c

The variables in the triggers are declared using

• 1]

  -

• 2]

  @

• 3]

  /

• 4]

  /@

The ______ clause allows us to select only those rows in the result relation of the ____ clause that satisfy a specified predicate.

• 1]

  Where, from

• 2]

  From, select

• 3]

  Select, from

• 4]

  From, where

The ________ clause is used to list the attributes desired in the result of a query.

• 1]

  Where

• 2]

  Select

• 3]

  From

• 4]

  Distinct

A relational database consists of a collection of

• 1]

  Tables

• 2]

  Fields

• 3]

  Records

• 4]

  Keys

A ________ in a table represents a relationship among a set of values.

• 1]

  Column

• 2]

  Key

• 3]

  Row

• 4]

  Entry

The term _______ is used to refer to a row.

• 1]

  Attribute

• 2]

  Tuple

• 3]

  Field

• 4]

  Instance

The term attribute refers to a ___________ of a table.

• 1]

  Record

• 2]

  Column

• 3]

  Tuple

• 4]

  Key

For each attribute of a relation, there is a set of permitted values, called the ________ of that attribute.

• 1]

  Domain

• 2]

  Relation

• 3]

  Set

• 4]

  Schema

Database __________ which is the logical design of the database, and the database _______ which is a snapshot of the data in the database at a given instant in time.

• 1]

  Instance, Schema

• 2]

  Relation, Schema

• 3]

  Relation, Domain

• 4]

  Schema, Instance

Course(course_id,sec_id,semester)

Here the course_id,sec_id and semester are __________ and course is a _________

• 1]

  Relations, Attribute

• 2]

  Attributes, Relation

• 3]

  Tuple, Relation

• 4]

  Tuple, Attributes

Department (dept name, building, budget) and Employee (employee_id, name, dept name, salary)
Here the dept_name attribute appears in both the relations. Here using common attributes in relation schema is one way of relating ___________ relations.

• 1]

  Attributes of common

• 2]

  Tuple of common

• 3]

  Tuple of distinct

• 4]

  Attributes of distinct

A domain is atomic if elements of the domain are considered to be ____________ units.

• 1]

  Different

• 2]

  Indivisbile

• 3]

  Constant

• 4]

  Divisible

The tuples of the relations can be of ________ order.

• 1]

  Any

• 2]

  Same

• 3]

  Sorted

• 4]

  Constant

The _________ provides a set of operations that take one or more relations as input and return a relation as an output.

• 1]

  Schematic representation

• 2]

  Relational algebra

• 3]

  Scheme diagram

• 4]

  Relation flow

A ________ is a pictorial depiction of the schema of a database that shows the relations in the database, their attributes, and primary keys and foreign keys.

• 1]

  Schema diagram

• 2]

  Relational algebra

• 3]

  Database diagram

• 4]

  Schema flow

The _______ operator takes the results of two queries and returns only rows that appear in both result sets.

• 1]

  Union

• 2]

  Intersect

• 3]

  Difference

• 4]

  Projection

The most commonly used operation in relational algebra for projecting a set of tuple from a relation is

• 1]

  Join

• 2]

  Projection

• 3]

  Select

• 4]

  Union

The _______operation performs a set union of two “similarly structured” tables

• 1]

  Union

• 2]

  Join

• 3]

  Product

• 4]

  Intersect

The result which the operation contains all pairs of tuples from the two relations, regardless of whether their attribute values match.

• 1]

  Join

• 2]

  Cartesian product

• 3]

  Intersection

• 4]

  Set difference

The_____ operation allows the combining of two relations by merging pairs of tuples, one from each relation, into a single tuple.

• 1]

  Select

• 2]

  Join

• 3]

  Union

• 4]

  Intersection

Which one of the following is procedural language?

• 1]

  Domain relational calculus

• 2]

  Tuple relational calculus

• 3]

  Relational algebra

• 4]

  Query language

Student(ID, name, dept name, tot_cred)
In this query which attributes form the primary key?

• 1]

  Name

• 2]

  Dept

• 3]

  Tot_cred

• 4]

  ID

Using which language can a user request information from a database?

• 1]

  Query

• 2]

  Relational

• 3]

  Structural

• 4]

  Compiler

SELECT name ____ instructor name, course id
FROM instructor, teaches
WHERE instructor.ID= teaches.ID;

Which keyword must be used here to rename the field name?

• 1]

  From

• 2]

  Rename

• 3]

  As

• 4]

  Join

SELECT * FROM employee WHERE dept_name="Comp Sci";

In the SQL given above there is an error . Identify the error.

• 1]

  Dept_name

• 2]

  Employee

• 3]

  “Comp Sci”

• 4]

  From

SELECT emp_name
FROM department
WHERE dept_name LIKE ’ _____ Computer Science’;

Which one of the following has to be added into the blank to select the dept_name which has Computer Science as its ending string?

• 1]

  %

• 2]

  _

• 3]

  ||

• 4]

  $

’_____’ matches any string of ______ three characters. ’_____%’ matches any string of at ______ three characters.

• 1]

  Atleast, Exactly

• 2]

  Exactly, Atleast

• 3]

  Atleast, All

• 4]

  All, Exactly

SELECT name
FROM instructor
WHERE dept name = ’Physics’
ORDER BY name;

By default, the order by clause lists items in ______ order.

• 1]

  Descending

• 2]

  Any

• 3]

  Same

• 4]

  Ascending

SELECT *
FROM instructor
ORDER BY salary ____, name ___;

To display the salary from greater to smaller and name in ascending order which of the following options should be used?

• 1]

  Ascending, Descending

• 2]

  Asc, Desc

• 3]

  Desc, Asc

• 4]

  Descending, Ascending

SELECT name
FROM instructor
WHERE salary <= 100000 AND salary >= 90000;

This query can be replaced by which of the following ?

• 1]

  SELECT name
  FROM instructor
  WHERE salary BETWEEN 90000 AND 100000;

   

• 2]

  SELECT name
  FROM employee
  WHERE salary <= 90000 AND salary>=100000;

   

• 3]

  SELECT name
  FROM employee
  WHERE salary BETWEEN 90000 AND 100000;

   

• 4]

  SELECT name
  FROM instructor
  WHERE salary BETWEEN 100000 AND 90000;

   

SELECT instructor.*
FROM instructor, teaches
WHERE instructor.ID= teaches.ID;

This query does which of the following operation?

• 1]

  All attributes of instructor and teaches are selected

• 2]

  All attributes of instructor are selected on the given condition

• 3]

  All attributes of teaches are selected on given condition

• 4]

  Only the some attributes from instructed and teaches are selected

In SQL the spaces at the end of the string are removed by _______ function.

• 1]

  Upper

• 2]

  String

• 3]

  Trim

• 4]

  Lower

_____ operator is used for appending two strings.

• 1]

  &

• 2]

  %

• 3]

  ||

• 4]

  _

Which SQL function is used to count the total number of rows in a SQL query?

• 1]

  SUM()

• 2]

  COUNT()

• 3]

  AVG()

• 4]

  COUNT(*)

Aggregate functions are functions that take a ___________ as input and return a single value.

• 1]

  Collection of values

• 2]

  Single value

• 3]

  Aggregate value

• 4]

  Both Collection of values & Single value

SELECT __________
FROM instructor
WHERE dept name= ’Comp. Sci.’;

Which of the following should be used to find the mean of the salary ?

• 1]

  Mean(salary)

• 2]

  Avg(salary)

• 3]

  Sum(salary)

• 4]

  Count(salary)

SELECT COUNT (____ ID)
FROM teaches
WHERE semester = ’Spring’ AND YEAR = 2010;

If we do want to eliminate duplicates, we use the keyword ______in the aggregate expression.

• 1]

  Distinct

• 2]

  Count

• 3]

  Avg

• 4]

  Primary key

All aggregate functions except _____ ignore null values in their input collection.

• 1]

  Count(attribute)

• 2]

  Count(*)

• 3]

  Avg

• 4]

  Sum

A Boolean data type that can take values true, false, and________

• 1]

  1

• 2]

  0

• 3]

  Null

• 4]

  Unknown

The ____ connective tests for set membership, where the set is a collection of values produced by a select clause. The ____ connective tests for the absence of set membership.

• 1]

  Or, in

• 2]

  Not in, in

• 3]

  In, not in

• 4]

  In, or

Which of the following should be used to find all the courses taught in the Fall 2009 semester but not in the Spring 2010 semester.

• 1]

  SELECT DISTINCT course id
  FROM SECTION
  WHERE semester = ’Fall’ AND YEAR= 2009 AND
  course id NOT IN (SELECT course id
  FROM SECTION
  WHERE semester = ’Spring’ AND YEAR= 2010);

   

• 2]

  SELECT DISTINCT course_id
  FROM instructor
  WHERE name NOT IN (’Fall’, ’Spring’);

   

• 3]

  (SELECT course id
  FROM SECTION
  WHERE semester = ’Spring’ AND YEAR= 2010)

   

• 4]

  SELECT COUNT (DISTINCT ID)
  FROM takes
  WHERE (course id, sec id, semester, YEAR) IN (SELECT course id, sec id, semester, YEAR
  FROM teaches
  WHERE teaches.ID= 10101);

   

The phrase “greater than at least one” is represented in SQL by _____

• 1]

  < all

• 2]

  < some

• 3]

  > all

• 4]

  > some

Which of the following is used to find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester .

• 1]

  SELECT course id
  FROM SECTION AS S
  WHERE semester = ’Fall’ AND YEAR= 2009 AND
  EXISTS (SELECT *
  FROM SECTION AS T
  WHERE semester = ’Spring’ AND YEAR= 2010 AND
  S.course id= T.course id);

   

• 2]

  SELECT name
  FROM instructor
  WHERE salary > SOME (SELECT salary
  FROM instructor
  WHERE dept name = ’Biology’);

   

• 3]

  SELECT COUNT (DISTINCT ID)
  FROM takes
  WHERE (course id, sec id, semester, YEAR) IN (SELECT course id, sec id, semester, YEAR
  FROM teaches
  WHERE teaches.ID= 10101);

   

• 4]

  (SELECT course id
  FROM SECTION
  WHERE semester = ’Spring’ AND YEAR= 2010)

   

We can test for the nonexistence of tuples in a subquery by using the _____ construct.

• 1]

  Not exist

• 2]

  Not exists

• 3]

  Exists

• 4]

  Exist

The union operation is represented by

• 1]

  ∩

• 2]

  U

• 3]

  -

• 4]

  *

The intersection operator is used to get the _____ tuples.

• 1]

  Different

• 2]

  Common

• 3]

  All

• 4]

  Repeating

The union operation automatically __________ unlike the select clause.

• 1]

  Adds tuples

• 2]

  Eliminates unique tuples

• 3]

  Adds common tuples

• 4]

  Eliminates duplicate

If we want to retain all duplicates, we must write ________ in place of union.

• 1]

  Union all

• 2]

  Union some

• 3]

  Intersect all

• 4]

  Intersect some

(SELECT course id
FROM SECTION
WHERE semester = ’Fall’ AND YEAR= 2009)
EXCEPT
(SELECT course id
FROM SECTION
WHERE semester = ’Spring’ AND YEAR= 2010);

This query display

• 1]

  Only tuples from second part

• 2]

  Only tuples from the first part which has the tuples from second part

• 3]

  Tuples from both the parts

• 4]

  Tuples from first part which do not have second part

For like predicate which of the following is true.

i) % matches zero OF more characters.
ii) _ matches exactly one CHARACTER.

• 1]

  i-only

• 2]

  ii-only

• 3]

  i & ii

• 4]

  None of the mentioned

The number of attributes in relation is called as its

• 1]

  Cardinality

• 2]

  Degree

• 3]

  Tuples

• 4]

  Entity

_____ clause is an additional filter that is applied to the result.

• 1]

  Select

• 2]

  Group-by

• 3]

  Having

• 4]

  Order by