Online Test SQL

Online Test SQL

There are two tables A and B. You are retreiving data from both tables where all rows from B table and only matching rows from A table should be displayed. Which type of join you will apply between A and B tables?

  1. Inner join
  2. Left outer join
  3. Right outer join
  4. Self join
Answer: Answer is c
Further Reading:
Useful resources, Join, Types of Joins

There is a column c1 in the table t to which a primary key pk is to be added. What will be the correct syntax?

  1. Alter table t add primary key(c1);
  2. Alter table t add constraint pk primary key(c1);
  3. Alter table t add (constraint pk primary key(c1));
  4. Alter table t add pk constraint primary key(c1);
Answer: Answer is a
Further Reading:

In which sequence are queries and sub-queries executed by the SQL Engine?

  1. primary query -> sub query -> sub sub query and so on
  2. sub sub query -> sub query -> prime query
  3. the whole query is interpreted at one time
  4. there is no fixed sequence of interpretation, the query parser takes a decision on the fly
Answer: Answer is b
Further Reading:
Primary query results depend on the sub query results, so sub query my execute first then primary query will execute.   Useful resources, Subquery Fundamentals
 0 Comments

What does the term DDL stand for?

  1. Data Description Language
  2. Dynamic Data Language
  3. Data Definition Language
  4. Data Derived Language
  5. Descriptive Data Language
Answer: Answer is c
Further Reading:
Useful resources, DDL
 0 Comments

Consider the following tables:

Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating       (the popularity of the book on a scale of 1 to 10)
Language               (such as French, English, German etc)
Subjects
---------
SubjectId
Subject   (such as History, Geography, Mathematics etc)
Authors
--------
AuthorId
AuthorName
Country
What is the query to determine the names of the Authors who have written more than 1 book?
  1. select AuthorName from Authors where AuthorId in
         (select AuthorId from Books group by AuthorId having count(*)>1)
  2. select AuthorName from Authors, Books
         where Authors.AuthorId=Books.AuthorId and count(BookId)>1
  3. select AuthorName from Authors, Books
         where Authors.AuthorId=Books.AuthorId
         group by AuthorName having count(*)>1
  4. select AuthorName from Authors where AuthorId in
         (select AuthorId from Books having count(BookId)>1)
Answer: Answer is a, c
Further Reading:
 0 Comments

Examine the query:-

select (2/2/4) from tab1; where tab1 is a table with one row.
This would give a result of:
  1. 4
  2. 2
  3. 1
  4. 5
  5. 25
  6. 0
  7. 8
  8. 24
Answer: Answer is f
 0 Comments

The level of abstraction which describes only part of the entire database is called?

  1. Conceptual level
  2. View level
  3. Procedural level
  4. None of the above
Answer: Answer is b
Further Reading:
Useful resources,data abstraction

Which of the following statements are incorrect regarding definition of simple and complex views?

  1. A simple view is one which derives data from only one table
  2. A simple view is one which contains no functions or groups of data
  3. A complex view is one which derives data from many tables
  4. A complex view is one which may contain functions
  5. A complex view cannot contain groups of data
  6. A complex view is a combination of more than one simple view
  7. A table can either have a simple or complex view, not both
Answer: Answer is a, b, c, d

Which operator will be evaluated first in the following statement?

select (age + 3 * 4 / 2 - 8) from emp
  1. +
  2. -
  3. /
  4. *
Answer: Answer is d
Further Reading:
Useful resources, Operator Precedence

What is a rollback of transactions normally used for?

  1. Recover from the transaction failure
  2. Update the transaction
  3. Retrieve old records
  4. None of the above
Answer: Answer is a
Further Reading:
Useful resources,ROLLBACK TRANSACTION


< and > are examples of _________ type of operators

  1. Logical
  2. Arithmetic
  3. Assignment
  4. Ternary
  5. Relational
  6. Numeric
  7. Comparison
  8. None of the above
Answer: Answer is g
Further Reading:
Useful resources,Comparison Operators

Which of the following is not a DDL command?

  1. Drop
  2. Create
  3. Alter
  4. Revoke
  5. Explain plan
  6. Insert
  7. Update
  8. Delete
Answer: Answer is d, e, f, g, h
Further Reading:
Useful resources, DDL Statements

A company has the following departments:

Marketing, Designing, production, Packing
What will be the result of the following query?
select * from table where department < 'marketing';
  1. The query will return "Designing, Packing"
  2. The query will return "Designing, production, Packing"
  3. The query will return "packing"
  4. Strings cannot be compared using < operator
  5. The query will return "Designing"
Answer: Answer is e
 0 Comments

Consider the following tables:

Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating       (the popularity of the book on a scale of 1 to 10)
Language               (such as French, English, German etc)


Subjects
---------
SubjectId
Subject   (such as History, Geography, Mathematics etc)


Authors
--------
AuthorId
AuthorName
Country
What is the query to determine how many books, with a popularity rating of more than 7, have been written on each subject?
  1. select subject,count(*) as Books from books,subjects
           where books.popularityrating > 7
           group by subjects.subject
  2. select subject,count(*) as Books from books,subjects
           where books.authorid=subjects.authorid and books.popularityrating > 7
           group by subjects.subject
  3. select subject,count(*) as Books from books,subjects
           where books.subjectid=subjects.subjectid and books.popularityrating = 7
           group by subjects.subject
  4. select subject,count(*) as Books from books,subjects
           where books.subjectid=subjects.subjectid and books.popularityrating > 7
           group by subjects.subject
Answer: Answer is a

Which of the following statement is correct regarding table creation?

  1. Tables once created cannot be modified to add columns
  2. Constraints can only be given while table creation
  3. One can easily create a table from a given table
  4. When a table is created from another table, all the constraints are copied as well
  5. The width of the columns cannot be modified
  6. Columns cannot be removed from a table
Answer: Answer is c

The overall logical structure of a database can be expressed graphically by

  1. Data Flow Chart
  2. Flow Chart
  3. Directed Chart
  4. Entity-Relationship Diagram
  5. None of the above
Answer: Answer is d

Identify the incorrect statement/s regarding constraints

  1. A primary key constraint can be disabled
  2. Information about the constraints on a table can be retrieved from the data dictionary
  3. Information about the columns on which the constraints have been applied can be retrieved from the data dictionary
  4. There can be only one unique key in a table
  5. If a column has null values then the not null constraint cannot be applied to that column
  6. There can be more than one primary key
Answer: Answer is d, f

What is the collection of information stored in a database at a particular moment called?

  1. Schema
  2. Instance
  3. Table
  4. Cluster
  5. View
  6. Index
  7. None of the above
Answer: Answer is b

View the following Create statement:

1    Create table Pers
2    (EmpNo Number(4) not null,
3    EName Char not null,
4    Join_dt Date not null,
5    Pay Number)
Which line contains an error?
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
Answer: Answer is b

What is the correct order of clauses in the select statement?

  1. Select
  2. order by
  3. where
  4. having
  5. group by
  1. 1,2,3,4,5
  2. 1,3,5,4,2
  3. 1,3,5,2,4
  4. 1,3,2,5,4
  5. 1,3,2,4,5
  6. 1,5,2,3,4
  7. 1,4,2,3,5
  8. 1,4,3,2,5
Answer: Answer is b

Further Reading:
The order should be like this select>where>group by>having>order by. Useful resources, SQL basic queries, query fundamental, HAVING Clause



Select all the appropriate options

  1. A table is a multiset of rows
  2. A table is a two-dimensional array of rows and columns
  3. A table is always dependent on other tables
  4. A third normal form table is a table free of redundant data
  5. A table must have a primary key
Answer: Answer is b, d
 0 Comments

What does MOD() function do?

  1. Returns the remainder after division
  2. Modifies the column definition
  3. Modifies the definition of a table
  4. None of the above
Answer: Answer is a
Further Reading:
Useful resources,Mod

Examine the data in the EMPLOYEES table given below:

LAST_NAME  DEPARTMENT_ID     SALARY
ALLEN                10                      3000
MILLER              20                     1500
King                   20                      2200
Davis                 30                      5000
Which of the following Subqueries will execute without any error?
  1. SELECT distinct department_id FROM employees Where salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
  2. SELECT distinct department_id FROM employees Where salary > (SELECT AVG(salary) FROM employees GROUP BY department_id);
  3. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
  4. SELECT distinct department_id FROM employees Where salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
  5. SELECT distinct department_id FROM employees Where salary < (SELECT AVG(salary) FROM employees GROUP BY department_id);
Answer: Answer is c, d

The STUDENT_GRADES table has these columns:

STUDENT_ID        INT 
SEMESTER_END        DATETIME 
GPA                FLOAT
Which of the following statements finds the highest Grade Point Average (GPA) per semester?
  1. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL
  2. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL
  3. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end
  4. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades
  5. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL
Answer: Answer is c

What does the following Update statement do?

Update OrderTable set OrderDiscount=OrderDiscount*1.10
  1. Increases OrderDiscount of first row by 10%
  2. Increases OrderDiscount of all rows by 10%
  3. Does nothing without where clause
  4. Gives an error due to lack of where clause
Answer: Answer is b

Which of the following is not a set operator?

  1. Union
  2. Union all
  3. Intersect
  4. Minus
  5. Minus all
Answer: Answer is e
Further Reading:
Useful resources,Set Operations

What clause should be used to display the rows of a table in ascending order of a particular column?

  1. Where
  2. Order By
  3. Group By
  4. Having
  5. First Group By and then Having
  6. Like
  7. Between
Answer: Answer is b
Further Reading:
Useful resources,ORDER BY Clause

With regard to statement (a) and statement (b), Which of the following option is correct

a)The FLOOR function returns the smallest integer greater or equal to the argument.
(b)The CEIL function gives the largest integer equal to or less that the argument.
  1. (a) is true
  2. (b) is true
  3. both (a) and (b) are true
  4. both (a) and (b) are false
Answer: Answer is d
Further Reading:
Useful resources, FLOOR, CEILING, Comparing CEILING and FLOOR

The purpose of the domain is to constrain the set of valid values. True or false?

  1. True
  2. False
Answer: Answer is a

What will happen if you query the emp table as shown below:

select empno, DISTINCT ename, Salary from emp;
  1. EMPNO, unique value of ENAME and then SALARY are displayed
  2. EMPNO, unique value ENAME and unique value of SALARY are displayed
  3. DISTINCT is not a valid keyword in SQL
  4. No values will be displayed because the statement will return an error
Answer: Answer is d



A production house needs a report about the sale where total sale of the day is more than $20,000. Which query should be used?

  1. select * from orders where sum(amount) > 20000
  2. select * from orders where sum(amount) > 20000 order by OrderDate
  3. select * from orders group by OrderDate having sum(amount)>20000
  4. select * from orders group by OrderDate where sum(amount) > 20000
Answer: Answer is c
Further Reading:
Useful resources, HAVING clause
 0 Comments

How many foreign key constraints can a table have?

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. None of the above
Answer: Answer is g
Further Reading:

An RDBMS performs the following steps

1)It calculates the results of the group functions of each group
2)It groups those rows together based on the group by clause
3)It orders the groups based on the results of the group functions in the order by clause
4)It chooses and eliminates groups based on the having clause
5)It chooses rows based on the where clause
Arrange the above steps in the correct order of execution:
  1. 4,3,5,1,2
  2. 4,5,3,2,1
  3. 5,2,1,4,3
  4. 5,2,3,4,1
  5. 2,3,1,4,5
  6. 2,3,1,5,4
  7. 1,2,3,4,5
  8. 3,2,1,4,5
Answer: Answer is c
 0 Comments

The Employee table uses alphanumeric characters for their Employee ID field, which contains 7 digits prefixed with 3 characters. The alphanumeric characters refer to the department code. You want to generate a list of all the department codes displayed in capital letters. Which function(s) you will use for this query?

  1. CONCAT()
  2. SUBSTR()
  3. UPPER()
  4. LOWER()
  5. REPLACE()
Answer: Answer is c
Further Reading:
Useful resources, CONCAT, SUBSTR, UPPER, LOWER, REPLACE
 0 Comments

Which component of an RDBMS validates the syntax of the user's query?

  1. Query Parser
  2. The Database Manager
  3. Query Optimization
  4. Database Administrator
Answer: Answer is a
 0 Comments

A production house has two sales outlets. Both outlets are maintaining their data separately in schemas A and B respectively. The Management wants to see the sale of both outlets in one report. Both outlets are using tables called Sales which have identical structure. Which method you will adopt to create the report?

  1. Select * from A.Sales join B.Sales
  2. Select * from A.Sales union all B.Sales
  3. Select * from A.Sales, B.Sales
  4. None of the above
Answer: Answer is b

What is a cluster?

  1. Group of users
  2. Group of indexes
  3. Method of storing tables that are intimately related and often joined together into the same area of the disk
  4. Compressed datafile
  5. Segment of a partitioned table
  6. Group of constraints
  7. Group of databases
  8. Group of roles/p>
Answer: Answer is c
Further Reading:
Useful resources, cluster

What is the java.net.IDN class in 1.6?

  1. Methods to resolve integrated domain names (IDNs), such domain names are special embedded names
  2. Methods to swap bytes between network byte order and host byte order
  3. Methods to convert internationalized domain names (IDNs) between a normal Unicode representation and an ASCII Compatible Encoding (ACE) representation
  4. This class does not exist
  5. None of the above
Answer: Answer is c
Further Reading:
Useful resources,Class IDN

Which class contains a method to create a directory?

  1. File
  2. DataOutput
  3. Directory
  4. FileDescriptor
  5. FileOutputStream
Answer: Answer is c
Further Reading:
Useful resources, Directory Class

Which of the following statements are true?

  1. With DDL, you can create and remove tables, schemas, domains, indexes and views.
  2. SELECT, INSERT and UPDATE are DCL commands.
  3. GRANT and REVOKE are DML commands.
  4. COMMIT and ROLLBACK are DCL commands.
Answer: Answer is a
Further Reading:
Share this article :
Labels:

Post a Comment

 
Copyright © 2014. Mominul's Diary - All Rights Reserved
Developed by Mominul Islam