SQL Model Test(1st)

Which of the following is the original purpose of SQL?

1 Mark(s)

All of the above.
To specify the syntax and semantics of SQL manipulation language
To define the data structures
To specify the syntax and semantics of SQL data definition language

Count function in SQL returns the number of

1 Mark(s)

distinct values.
columns.
groups.
values.

What is the meaning of LIKE '%0%0%'

1 Mark(s)

Feature ends with two 0's
Feature begins with two 0's
Feature has two 0's in it, at any position
Feature has more than two 0's

Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?

1 Mark(s)

Data Manipulation Language(DML)
Both of above
None
Data Definition Language(DDL)

Table employee has 10 records. It has a non-NULL SALARY column which is also UNIQUE.
The SQL statement
SELECT COUNT(*) FROM employee WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE);
prints

1 Mark(s)

5
10
9
0

The SQL ALTER statement can be used to:

1 Mark(s)

change the table data.
add rows to the table.
change the table structure.
delete rows from the table.

Which of the following query is correct for using comparison operators in SQL?

1 Mark(s)

SELECT name, course_name FROM student WHERE age>50 and <80;
SELECT name, course_name FROM student WHERE age>50 and WHERE age<80;
None of these
SELECT name, course_name FROM student WHERE age>50 and age <80;

SQL can be used to:

1 Mark(s)

create database structures only.
query database data only.
modify database data only.
All of the above can be done by SQL.

What is the full form of SQL?

1 Mark(s)

Structured Query List
Structured Query Language
Simple Query Language
None of these

Which SQL keyword is used to sort the result-set?

1 Mark(s)

SORT
ORDER
ORDER BY
SORT BY

Find the name of all cities with their temperature, humidity and countries.

1 Mark(s)

SELECT weather.city, temperature, humidity, country FROM weather, location WHERE weather.city = location.city;
SELECT city, temperature, humidity, country FROM location;
SELECT weather.city, temperature, humidity FROM weather SELECT country FROM location WHERE weather.city = location.city;
SELECT weather.city, temperature, humidity, country FROM weather, location;

The SQL statement
SELECT SUBSTR('abcdefghij', INSTR('123321234', '2', 3, 2), 2) FROM DUAL;
prints

1 Mark(s)

23
ab
bc
gh

Select the right statement to insert values to the student table.

1 Mark(s)

INSERT student VALUES (
INSERT INTO student VALUES (
INSERT VALUES (
INSERT VALUES INTO student (

Find the name of cities with all entries whose temperature is in the range of 71 and 89

1 Mark(s)

SELECT * FROM weather WHERE temperature NOT IN (71 to 89);
SELECT * FROM weather WHERE temperature NOT BETWEEN 71 to 89;
SELECT * FROM weather WHERE temperature NOT IN (71 and 89);
SELECT * FROM weather WHERE temperature BETWEEN 71 AND 89;

In SQL, which command is used to change a table's storage characteristics?

1 Mark(s)

ALTER TABLE
CHANGE TABLE
None of these
MODIFY TABLE

Find the names of the countries whose condition is sunny.

1 Mark(s)

SELECT country FROM location WHERE condition = 'sunny';
SELECT country FROM location WHERE city UNION (SELECT city FROM weather WHERE condition = 'sunny');
SELECT country FROM location WHERE city IN (SELECT city FROM weather WHERE condition = sunny');
SELECT country FROM location WHERE city NOT IN (SELECT city FROM weather WHERE condition = 'sunny');

Find the temperature in increasing order of all cities

1 Mark(s)

SELECT city, temperature FROM weather ORDER BY city;
SELECT city FROM weather ORDER BY temperature;
SELECT city, temperature FROM weather;
SELECT city, temperature FROM weather ORDER BY temperature;

What does the following query find?
(SELECT DISTINCT r.sid
FROM boats b, reserves r
WHERE b.bid = r.bid
AND b.color = 'red')
MINUS
(SELECT DISTINCT r.sid
FROM boats b, reserves r
WHERE b.bid = r.bid
AND b.color = 'green')

1 Mark(s)

Find the sailor IDs of at least one sailor who have reserved red boats but not green boats
None of These
Find the sailor Ids of atmost one sailor who have reserved red boats but not green boats
Find the sailor IDs of all sailors who have reserved red boats but not green boats

What operator tests column for the absence of data?

1 Mark(s)

NOT operator
IS NULL operator
EXISTS operator
None of these

The SQL keyword(s) ________ is used with wildcards.

1 Mark(s)

NOT IN only
LIKE only
IN and NOT IN
IN only

What is an SQL virtual table that is constructed from other tables?

1 Mark(s)

view
A relation
Query results
Just another table

The SELECT statement SELECT 'Hi' FROM DUAL WHERE NULL = NULL; Outputs

1 Mark(s)

TRUE
Nothing
Hi
FLASE

'AS' clause is used in SQL for

1 Mark(s)

Projection operation.
Join operation.
Rename operation.
Selection operation.

A subquery in an SQL SELECT statement is enclosed in:

1 Mark(s)

parenthesis -- (...).
brackets -- [...].
braces -- {...}.
CAPITAL LETTERS.

In SQL, which of the following is not a data definition language commands?

1 Mark(s)

UPDATE
REVOKE
GRANT
RENAME

When three or more AND and OR conditions are combined, it is easier to use the SQL keyword(s):

1 Mark(s)

NOT IN only.
IN only.
Both IN and NOT IN.
LIKE only.

Which SQL keyword is used to retrieve only unique values?

1 Mark(s)

DISTINCTIVE
DIFFERENT
UNIQUE
DISTINCT

The wildcard in a WHERE clause is useful when?

1 Mark(s)

An exact match is necessary in a SELECT statement.
An exact match is not possible in a CREATE statement.
An exact match is not possible in a SELECT statement.
An exact match is necessary in a CREATE statement.

Which of the SQL statements is correct?

1 Mark(s)

None of these
SELECT Username AND Password FROM Users
SELECT Username, Password WHERE Username = 'user1'
SELECT Username, Password FROM Users

What is a view?

1 Mark(s)

A view is a special stored procedure executed when certain event occurs.
A view is a virtual table which results of executing a pre-compiled query. A view is not part of the physical database schema, while the regular tables are.
A view is a database diagram.
None of these

Which of the following SQL query is correct for selecting the name of staffs from 'staffinfo' table where salary is 10,000 or 25,000?

1 Mark(s)

Both A and B
None of the above
SELECT name FROM staffinfo WHERE salary BETWEEN 10000 AND 25000;
SELECT name FROM staffinfo WHERE salary IN (10000, 25000);

Which of the following group functions ignore NULL values?

1 Mark(s)

All of the above
SUM
MAX
COUNT

Which of the following query finds the names of the sailors who have reserved at least one boat?

1 Mark(s)

SELECT DISTINCT s.sname FROM sailors s, reserves r WHERE s.sid = r.sid;
None of These
SELECT DISTINCT s.sname FROM sailors, reserves WHERE s.sid = r.sid;
SELECT s.sname FROM sailors s, reserves r WHERE s.sid = r.sid;

Which of the following is a valid SQL type?

1 Mark(s)

CHARACTER
All of the above
NUMERIC
FLOAT

Let the statement
SELECT column1 FROM myTable;
return 10 rows. The statement
SELECT ALL column1 FROM myTable;
will return

1 Mark(s)

None of these
less than 10 rows
more than 10 rows
exactly 10 rows

The SQL keyword BETWEEN is used:

1 Mark(s)

to limit the columns displayed.
as a wildcard.
for ranges.
None of these is correct.

Which of the following query finds colors of boats reserved by "Dustin"?

1 Mark(s)

SELECT DISTINCT b.color FROM boats b, reserves r, sailors s WHERE s.sname = 'Dustin' AND r.bid = b.bid
SELECT DISTINCT b.color FROM boats b, sailors s WHERE s.sname = 'Dustin' AND s.sid = b.sid
SELECT DISTINCT b.color FROM boats b, reserves r, sailors s WHERE s.sname = 'Dustin' AND s.sid = r.sid AND r.bid = b.bid;
SELECT DISTINCT b.color FROM boats b, reserves r, sailors s WHERE s.sname = 'Dustin' AND s.sid = r.sid

Which of the following do you need to consider when you make a table in SQL?

1 Mark(s)

Data types
Default values
Primary keys
All of the above.

When using the SQL INSERT statement:

1 Mark(s)

rows can be inserted into a table only one at a time only.
rows cannot be copied in mass from one table to another only.
rows can be modified according to criteria only.
rows can either be inserted into a table one at a time or in groups.

The SQL WHERE clause:

1 Mark(s)

limits the row data are returned.
limits the column data that are returned.
Neither A nor B are correct.
Both A and B are correct.

Find the names of these cities with temperature and condition whose condition is neither sunny nor cloudy

1 Mark(s)

SELECT city, temperature, condition FROM weather WHERE condition NOT BETWEEN ('sunny', 'cloudy');
SELECT city, temperature, condition FROM weather WHERE condition NOT IN ('sunny', 'cloudy');
SELECT city, temperature, condition FROM weather WHERE condition IN ('sunny', 'cloudy');
SELECT city, temperature, condition FROM weather WHERE condition BETWEEN ('sunny', 'cloudy');

In SQL, which command(s) is(are) used to change a table's storage characteristics?

1 Mark(s)

All of the above
MODIFY TABLE
CHANGE TABLE
ALTER TABLE

What SQL command can be used to delete columns from a table?

1 Mark(s)

ALTER TABLE TableName DROP COLUMN ColumnName
ALTER TABLE TableName DROP ColumnName
MODIFY TABLE TableName DROP ColumnName
MODIFY TABLE TableName DROP COLUMN ColumnName

Find all the cities with temperature, condition and humidity whose humidity is in the range of 63 to 79

1 Mark(s)

SELECT * FROM weather WHERE humidity BETWEEN 63 AND 79
SELECT * FROM weather WHERE humidity IN (63 to 79)
SELECT * FROM weather WHERE humidity NOT BETWEEN 63 AND 79
SELECT * FROM weather WHERE humidity NOT IN (63 AND 79)

Which of the following command makes the updates performed by the transaction permanent in the database?

1 Mark(s)

ROLLBACK
TRUNCATE
DELETE
COMMIT

The command to eliminate a table from a database is:

1 Mark(s)

DELETE TABLE CUSTOMER;
UPDATE TABLE CUSTOMER;
DROP TABLE CUSTOMER;
REMOVE TABLE CUSTOMER;

The command to remove rows from a table 'CUSTOMER' is:

1 Mark(s)

REMOVE FROM CUSTOMER ...
DELETE FROM CUSTOMER WHERE ...
UPDATE FROM CUSTOMER ...
DROP FROM CUSTOMER ...

Table Employee has 10 records. It has a non-NULL SALARY column which is also UNIQUE.
The SQL statement
SELECT COUNT(*) FROM Employee WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEE);
prints

1 Mark(s)

0
5
10
9

Find all the tuples having temperature greater than 'Paris'.

1 Mark(s)

SELECT * FROM weather WHERE temperature > (SELECT city FROM weather WHERE city = 'Paris')
SELECT * FROM weather WHERE temperature > (SELECT temperature FROM weather WHERE city = 'Paris')
SELECT * FROM weather WHERE temperature > 'Paris' temperature
SELECT * FROM weather WHERE temperature > (SELECT * FROM weather WHERE city = 'Paris')

Which SQL keyword is used to retrieve a maximum value?

1 Mark(s)

TOP
MOST
MAX
UPPER

Find all the cities whose humidity is 89

1 Mark(s)

SELECT city WHERE humidity = 89;
SELECT city FROM weather;
SELECT city FROM weather WHERE humidity = 89;
SELECT humidity = 89 FROM weather;

The FROM SQL clause is used to...

1 Mark(s)

specify search condition
None of these
specify what table we are selecting or deleting data FROM
specify range for search condition

Which of the following is illegal?

1 Mark(s)

None of these
SELECT SYSDATE - (SYSDATE - 2) FROM DUAL;
SELECT SYSDATE - (SYSDATE + 2) FROM DUAL;
SELECT SYSDATE - SYSDATE FROM DUAL;

If a query involves NOT, AND, OR with no parenthesis

1 Mark(s)

NOT will be evaluated first; AND will be evaluated second; OR will be evaluated last.
AND will be evaluated first; OR will be evaluated second; NOT will be evaluated last.
The order of occurrence determines the order of evaluation.
NOT will be evaluated first; OR will be evaluated second; AND will be evaluated last.

A command that lets you change one or more fields in a record is

1 Mark(s)

Look-up
Insert
Modify
All of the above

Which of the following query finds the name of the sailors who have reserved at least two boats?

1 Mark(s)

SELECT DISTINCT s.sname FROM sailors s, reserves r1, reserves r2 WHERE s.sid = r1.sid AND r1.sid = r2.sid AND r1.bid <> r2.bid
All of these
SELECT DISTINCT s.sname FROM sailors s, reserves r1, reserves r2 WHERE s.sid = r1.sid AND COUNT(r1.bid) > r2.bid
SELECT DISTINCT s.sname FROM sailors s, reserves r1, reserves r2 WHERE s.sid = r1.sid AND r1.sid = r2.sid AND r1.bid ≠ r2.bid

In SQL, which command is used to SELECT only one copy of each set of duplicable rows

1 Mark(s)

SELECT DISTINCT
SELECT UNIQUE
SELECT DIFFERENT
All of the above

Which command undo all the updates performed by the SQL in the transaction?

1 Mark(s)

ROLLBACK
COMMIT
DELETE
TRUNCATE

The result of a SQL SELECT statement is a ________ .

1 Mark(s)

form
table
file
report

............. joins two or more tables based on a specified column value not equaling a specified column value in another table.

1 Mark(s)

NON-EQUIJOIN
OUTER JOIN
EQUIJOIN
NATURAL JOIN

The SQL statement
SELECT SUBSTR('123456789', INSTR('abcabcabc', 'b'), 4) FROM DUAL;

1 Mark(s)

2345
COUNT
1234
SUM
6789
All of the above
MAX
456789

In SQL, which of the following is not a data definition language commands?

1 Mark(s)

UPDATE
REVOKE
RENAME
GRANT

Which of the following join is also called as an 'inner-join'?

1 Mark(s)

Equijoin
None of these
Self-Join
Non-Equijoin

The HAVING clause does which of the following?

1 Mark(s)

Acts like a WHERE clause but is used for rows rather than columns.
Acts like a WHERE clause but is used for columns rather than groups.
Acts like a WHERE clause but is used for groups rather than rows.
Acts EXACTLY like a WHERE clause.

Which of the following SQL commands is used to retrieve data?

1 Mark(s)

JOIN
SELECT
INSERT
DELETE

In an SQL SELECT statement querying a single table, according to the SQL-92 standard the asterisk (*) means that:

1 Mark(s)

None of the above is correct.
all records meeting the full criteria are to be returned.
all columns of the table are to be returned.
all records with even partial criteria met are to be returned.

Which of the following is a SQL aggregate function?

1 Mark(s)

JOIN
LEFT
AVG
LEN

Which of the following must be enclosed in double quotes?

1 Mark(s)

Column Alias
Strings
All of the above
Dates

The SQL statement
SELECT ROUND(45.926, -1) FROM DUAL;

1 Mark(s)

prints 50
prints 045.926
is illegal
prints garbage

Which operator performs pattern matching?

1 Mark(s)

LIKE operator
BETWEEN operator
None of these
EXISTS operator

Which of the following query finds the total rating of the sailors who have reserved boat "103"?

1 Mark(s)

SELECT COUNT(s.rating) FROM sailors s, reserves r WHERE s.sid = r.sid AND r.bid = 103
SELECT s.rating FROM sailors s, reserves r WHERE s.sid = r.sid AND r.bid = 103
SELECT SUM(s.rating) FROM sailors s, reserves r WHERE s.sid = r.sid AND r.bid = 103
SELECT SUM(s.rating) FROM sailors s, reserves r AND r.bid = 103;

Find the name of those cities with temperature and condition whose condition is either sunny or cloudy but temperature must be greater than 70^oF.

1 Mark(s)

SELECT city, temperature, condition FROM weather WHERE condition = 'sunny' OR condition = 'cloudy' AND temperature > 70;
SELECT city, temperature, condition FROM weather WHERE condition = 'sunny' AND condition = 'cloudy' OR temperature > 70;
SELECT city, temperature, condition FROM weather WHERE condition = 'sunny' AND condition = 'cloudy' AND temperature > 70;
SELECT city, temperature, condition FROM weather WHERE condition = 'sunny' OR condition = 'cloudy' OR temperature > 70;

What SQL command can be used to add columns to a table?

1 Mark(s)

ALTER TABLE TableName ADD ColumnName
MODIFY TABLE TableName ADD COLUMN ColumnName
ALTER TABLE TableName ADD COLUMN ColumnName
MODIFY TABLE TableName ADD ColumnName

Which SQL statement is used to update data in a database?

1 Mark(s)

MODIFY
SAVE
UPDATE
SAVE AS

How to select all data from student table starting the name from letter 'r'?

1 Mark(s)

SELECT * FROM student WHERE name LIKE 'r%';
SELECT * FROM student WHERE name LIKE '%r%';
SELECT * FROM student WHERE name LIKE '_r%';
SELECT * FROM student WHERE name LIKE '%r';

Which of the following is NOT a type of SQL constraint?

1 Mark(s)

FOREIGN KEY
ALTERNATE KEY
UNIQUE
PRIMARY KEY

Which SQL statement is used to delete data FROM a database?

1 Mark(s)

COLLAPSE
ALTER
REMOVE
DELETE

NULL is

1 Mark(s)

the same as 0 for integer
the same as blank for character
the same as 0 for integer and blank for character
not a value

Which of the following SQL commands can be used to add data to a database table?

1 Mark(s)

UPDATE
ADD
APPEND
INSERT

SQL Model Test(1st) : Question 1 of 79