Q1.Which operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 – 8) from emp
select (age + 3 * 4 / 2 – 8) from emp
a. +
b. -
c. /
d. *(answer)
b. -
c. /
d. *(answer)
Q2.Can you run multiple MySQL servers on a single machine?
a. No
b. Yes
b. Yes
Q3.Which of the following are not String column types?
a. BLOB
b. ENUM
c. SET
d. TEXT
e. LONGCHAR
b. ENUM
c. SET
d. TEXT
e. LONGCHAR
Q4.What is the maximum size of a row in a MyISAM table?
a. No limit
b. OS specific
c. 65,534
d. 2’147’483’648
e. 128
b. OS specific
c. 65,534
d. 2’147’483’648
e. 128
Q5.Is the following query valid?
create table foo (id int primary key auto_increment, name varchar);
create table foo (id int primary key auto_increment, name varchar);
a. No
b. Yes
b. Yes
Q6.Considering table foo has been created with:
create table foo (id int primary key auto_increment, name varchar(100));
Is the following query syntactically valid?
delete from foo where id = id-1;
Is the following query syntactically valid?
delete from foo where id = id-1;
a. Yes
b. No
b. No
Q7.Which one of the following must be specified in every DELETE statement?
a. Table Name
b. Database name
c. LIMIT clause
d. WHERE clause
a. Table Name
b. Database name
c. LIMIT clause
d. WHERE clause
Q8.Which of the following formats does the date field accept by default?
a. DD-MM-YYYY
b. YYYY-DD-MM
c. YYYY-MM-DD
d. MM-DD-YY
e. MMDDYYYY
b. YYYY-DD-MM
c. YYYY-MM-DD
d. MM-DD-YY
e. MMDDYYYY
Q9.Which of the following are aggregate functions in SQL?
a. Avg
b. Select
c. Order By
d. Sum
e. Union
f. Group by
g. Having
b. Select
c. Order By
d. Sum
e. Union
f. Group by
g. Having
Q10.What is the correct order of clauses in the select statement?
1 select
2 order by
3 where
4 having
5 group by
2 order by
3 where
4 having
5 group by
a. 1,2,3,4,5
b. 1,3,5,4,2
c. 1,3,5,2,4
d. 1,3,2,5,4
e. 1,3,2,4,5
f. 1,5,2,3,4
g. 1,4,2,3,5
h. 1,4,3,2,5
b. 1,3,5,4,2
c. 1,3,5,2,4
d. 1,3,2,5,4
e. 1,3,2,4,5
f. 1,5,2,3,4
g. 1,4,2,3,5
h. 1,4,3,2,5
Q11.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
Which is the query to determine the number of Authors who have written books on more than 2 subjects?
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
Which is the query to determine the number of Authors who have written books on more than 2 subjects?
Possible answers:
a. select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId having count(*)>1)
b. select AuthorName from Authors where BookId in (select BookId from Books group by BookId having count(*)>1)
c. select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId,Authorid having count(*)>1)
d. select AuthorName from Authors where Authorid in (select Authorid from Books group by Authorid having count(*)>1)
e. None of the above
a. select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId having count(*)>1)
b. select AuthorName from Authors where BookId in (select BookId from Books group by BookId having count(*)>1)
c. select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId,Authorid having count(*)>1)
d. select AuthorName from Authors where Authorid in (select Authorid from Books group by Authorid having count(*)>1)
e. None of the above
Q12.The Flush statement cannot be used for:
a. Closing any open tables in the table cache
b. Closing open connections
c. Flushing the log file
d. Flushing the host cache
b. Closing open connections
c. Flushing the log file
d. Flushing the host cache
Q13.Which of the following statements are true?
a. Names of databases, tables and columns can be up to 64 characters in length
b. Alias names can be up to 255 characters in length
c. Names of databases, tables and columns can be up to 256 characters in length
d. Alias names can be up to 64 characters in length
b. Alias names can be up to 255 characters in length
c. Names of databases, tables and columns can be up to 256 characters in length
d. Alias names can be up to 64 characters in length
Q14.Is it possible to insert several rows into a table with a single INSERT statement?
a. No
b. Yes
b. Yes
Q15.What is wrong with the following statement?
create table foo (id int auto_increment, name int);
a. Nothing
b. The id column cannot be auto incremented because it has not been defined as a primary key
c. It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int);
b. The id column cannot be auto incremented because it has not been defined as a primary key
c. It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int);
Q16.Which of the following statements grants permission to Peter with password Software?
a. GRANT ALL ON testdb.* TO peter PASSWORD ‘Software’
b. GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
c. GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
d. GRANT ALL OF testdb.* TO peter IDENTIFIED by ‘Software’
b. GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
c. GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
d. GRANT ALL OF testdb.* TO peter IDENTIFIED by ‘Software’
Q17.Which one of the following correctly selects rows from the table myTable that have NULL in column column1?
a. SELECT * FROM myTable WHERE column1 IS NULL
b. SELECT * FROM myTable WHERE column1 = NULL
c. SELECT * FROM myTable WHERE column1 EQUALS NULL
d. SELECT * FROM myTable WHERE column1 NOT NULL
e. SELECT * FROM myTable WHERE column1 CONTAINS NULL
b. SELECT * FROM myTable WHERE column1 = NULL
c. SELECT * FROM myTable WHERE column1 EQUALS NULL
d. SELECT * FROM myTable WHERE column1 NOT NULL
e. SELECT * FROM myTable WHERE column1 CONTAINS NULL
Q18.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
ALLEN 10 3000
MILLER 20 1500
King 20 2200
Davis 30 5000
Which of the following Subqueries will execute well?
a. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
b. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
c. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
d. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
e. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
b. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
c. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
d. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
e. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
Q19.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)
——
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)
———
subjectid
subject (such as History, Geography, Mathematics etc)
authors
——–
authorid
authorname
country
——–
authorid
authorname
country
Which is the query to determine the Authors who have written at least 1 book with a popularity rating of less than 5?
a. select authorname from authors where authorid in (select authorid from books where popularityrating<5)
b. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
c. select authorname from authors where authorid in (select bookid from books where popularityrating<5)
d. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))
b. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
c. select authorname from authors where authorid in (select bookid from books where popularityrating<5)
d. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))
Q20.Consider the following select statement and its output:
SELECT * FROM table1 ORDER BY column1;
Column1
——–
1
2
2
2
2
2
3
Column1
——–
1
2
2
2
2
2
3
Given the above output, which one of the following commands deletes 3 of the 5 rows where column1 equals 2?
a. DELETE FIRST 4 FROM table1 WHERE column1=2
b. DELETE 4 FROM table1 WHERE column1=2
c. DELETE WHERE column1=2 LIMIT 4
d. DELETE FROM table1 WHERE column1=2 LIMIT 3
e. DELETE FROM table1 WHERE column1=2 LEAVING 1
b. DELETE 4 FROM table1 WHERE column1=2
c. DELETE WHERE column1=2 LIMIT 4
d. DELETE FROM table1 WHERE column1=2 LIMIT 3
e. DELETE FROM table1 WHERE column1=2 LEAVING 1
Q21.Which of the following statements relating to Alias names is true?
a. Alias names are case sensitive
b. Alias names are case in-sensitive
c. Alias names are case sensitive on UNIX and not on Windows
d. Alias names are case sensitive on Windows and not on UNIX<
e. Alias names case sensitivity depends on lower_case_table_names system setting
b. Alias names are case in-sensitive
c. Alias names are case sensitive on UNIX and not on Windows
d. Alias names are case sensitive on Windows and not on UNIX<
e. Alias names case sensitivity depends on lower_case_table_names system setting
Q22.The STUDENT_GRADES table has these columns:
STUDENT_ID INT
SEMESTER_END DATE
GPA FLOAT
SEMESTER_END DATE
GPA FLOAT
Which of the following statements finds the highest Grade Point Average (GPA) per semester?
a. SELECT MAX(GPA) FROM STUDENT_GRADES WHERE GPA IS NOT NULL
b. SELECT GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
c. SELECT MAX(GPA) FROM STUDENT_GRADES GROUP BY SEMESTER_END
d. SELECT TOP 1 GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
e. None of the above
b. SELECT GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
c. SELECT MAX(GPA) FROM STUDENT_GRADES GROUP BY SEMESTER_END
d. SELECT TOP 1 GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
e. None of the above
Q23.What is true about the ENUM data type?
a. An enum value may be a user variable
b. An enum may contain number enclosed in quotes
c. An enum cannot contain an empty string
d. An enum value may be NULL
e. None of the above is true
b. An enum may contain number enclosed in quotes
c. An enum cannot contain an empty string
d. An enum value may be NULL
e. None of the above is true
Q24.Which of the following is not a valid Arithmetic operator?
a. +
b. -
c. *
d. \
e. %
f. All are valid
b. -
c. *
d. \
e. %
f. All are valid
Q25.Which of the following is not a valid Logical operator?
a. &
b. &&
c. AND
d. !
e. NOT
b. &&
c. AND
d. !
e. NOT
Q26.Which of the following queries is valid?
a. Select * from students where marks > avg(marks);
b. Select * from students order by marks where subject = ‘SQL’;
c. Select * from students having subject =’SQL’;
d. Select name from students group by subject, name;
e. Select group(*) from students;
f. Select name,avg(marks) from students;
g. None of the above
b. Select * from students order by marks where subject = ‘SQL’;
c. Select * from students having subject =’SQL’;
d. Select name from students group by subject, name;
e. Select group(*) from students;
f. Select name,avg(marks) from students;
g. None of the above
Q27.Which of the following statements are true?
a. BLOB and TEXT columns cannot have DEFAULT values
b. BLOB columns are treated as binary strings (byte strings)
c. BLOB columns have a charset
d. TEXT columns cannot be indexed
e. None of the above is true
b. BLOB columns are treated as binary strings (byte strings)
c. BLOB columns have a charset
d. TEXT columns cannot be indexed
e. None of the above is true
Q28.Examine the code given below:
SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000
SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000
Which of the following statements is correct with regard to this code?
a. It returns employees whose salary is 50% more than $23,000
b. It returns employees who have 50% commission rate or salary greater than $23,000
c. It returns employees whose salary is 50% less than $23,000
d. None of the above
b. It returns employees who have 50% commission rate or salary greater than $23,000
c. It returns employees whose salary is 50% less than $23,000
d. None of the above
Q29.What kind of joins does MySQL support?
a. dual join
b. right join
c. natural join
d. middle join
e. STRAIGHT_JOIN
b. right join
c. natural join
d. middle join
e. STRAIGHT_JOIN
Q30.What is the correct SQL syntax for selecting all
the columns from the table Persons where the LastName is alphabetically
between (and including) “Hansen” and “Pettersen”?
a. SELECT * FROM Persons WHERE LastName > ‘Hansen’, LastName < ‘Pettersen’
b. SELECT LastName > ‘Hansen’ AND LastName < ‘Pettersen’ FROM Persons
c. SELECT * FROM persons WHERE LastName > ‘Hansen’ AND LastName > ‘Pettersen’
d. SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’
b. SELECT LastName > ‘Hansen’ AND LastName < ‘Pettersen’ FROM Persons
c. SELECT * FROM persons WHERE LastName > ‘Hansen’ AND LastName > ‘Pettersen’
d. SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’
Q31.Which query will display data from the Pers
table relating to Analysts, Clerks and Salesmen who joined between
1/1/2005 and 1/2/2005 ?
a. select * from Pers where joining_date from #1/1/2005# to #1/2/2005#, job=Analyst or clerk or salesman
b. select * from Pers where joining_date between #1/1/2005# to #1/2/2005#, job=Analyst or job=clerk or job=salesman
c. select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and (job=Analyst or clerk or salesman)
d. None of the above
b. select * from Pers where joining_date between #1/1/2005# to #1/2/2005#, job=Analyst or job=clerk or job=salesman
c. select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and (job=Analyst or clerk or salesman)
d. None of the above
Q32.Which of the following operators has the highest precedence?
a. BINARY
b. NOT
c. <<
d. %
b. NOT
c. <<
d. %
Q33.Which of the following results in 0 (false)?
a. “EXPERTRATING” LIKE “EXP%”
b. “EXPERTRATING” LIKE “Exp%”
c. BINARY “EXPERTRATING” LIKE “EXP%”
d. BINARY “EXPERTRATING” LIKE “Exp%”
e. All will result in 1 (true)
b. “EXPERTRATING” LIKE “Exp%”
c. BINARY “EXPERTRATING” LIKE “EXP%”
d. BINARY “EXPERTRATING” LIKE “Exp%”
e. All will result in 1 (true)
Q34.What is true regarding the SET data type?
a. A SET can have zero or more values
b. A SET value may contain a comma
c. A SET can have a maximum of 64 different members
d. MySQL stores SET values as strings
e. None of the above is true
b. A SET value may contain a comma
c. A SET can have a maximum of 64 different members
d. MySQL stores SET values as strings
e. None of the above is true
Q35.If you try to perform an arithmetic operation on a column containing NULL values, the output will be:
a. 0
b. NULL
c. An error will be generated
d. Cannot be determined
b. NULL
c. An error will be generated
d. Cannot be determined
Q36.Which of the following is not a MySQL statement?
a. ENUMERATE
b. EXPLAIN
c. KILL
d. LOAD DATA
e. SET
b. EXPLAIN
c. KILL
d. LOAD DATA
e. SET
Q37.To quote a string within a string, which of the following can you use?
a. “This is the “quoted” message”
b. “This is the “”quoted”” message”
c. ‘This is the “quoted” message’
d. “This is the \”quoted\” message”
b. “This is the “”quoted”” message”
c. ‘This is the “quoted” message’
d. “This is the \”quoted\” message”
Q38.State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine
Transactions and commit/rollback are supported by MySQL using the MyISAM engine
a. True
b. False
b. False
Q39.What will happen if some of the columns in a table are of char datatype and others are of varchar datatype?
a. Nothing will happen
b. MySQL will generate an error
c. MySQL will convert all varchar datatypes into char
d. MySQL will convert all char datatypes into varchar
b. MySQL will generate an error
c. MySQL will convert all varchar datatypes into char
d. MySQL will convert all char datatypes into varchar
Q40.Examine the query:- select (2/2/4) from tab1; where tab1 is a table with one row. This would give a result of:
a. 4
b. 2
c. 1
d. .5
e. .25
f. 0
g. 8
h. 24
b. 2
c. 1
d. .5
e. .25
f. 0
g. 8
h. 24
Q41.Examine the two SQL statements given below:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
What is true about them?
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
What is true about them?
a. The two statements produce identical results
b. The second statement returns an error
c. There is no need to specify DESC because the results are sorted in descending order by default
d. None of the above statments is correct
b. The second statement returns an error
c. There is no need to specify DESC because the results are sorted in descending order by default
d. None of the above statments is correct
Q42.MySQL supports 5 different int types. Which one takes 3 bytes?
a. TINYINT
b. MEDIUMINT
c. SMALLINT
d. INT
e. BIGINT
b. MEDIUMINT
c. SMALLINT
d. INT
e. BIGINT
oDesk MySQL 5.0 Test Answers
1) What does SQL stand for?
c) Structured Query Language
c) Structured Query Language
2) Which SQL statement is used to extract data from a database?
d) SELECT
3) Which SQL statement is used to update data in a database?
a) UPDATE
4) Which SQL statement is used to delete data from a database?
b) DELETE
5) Which SQL statement is used to insert new data in a database?
c) INSERT
6) With SQL, how do you select a column named "FirstName" from a table named "Persons"?
b) SELECT FirstName FROM Persons
7) With SQL, how do you select all the columns from a table named "Persons"?
d) SELECT * FROM Persons
8) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?
eter'
d)SELECT * FROM Persons WHERE FirstName='Peter'
9) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?
d) SELECT * FROM Persons WHERE FirstName LIKE 'a%'
10) The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
a) True
11) With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?
b) SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'
12) With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
b) SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
13) Which SQL statement is used to return only different values?
d) SELECT DISTINCT
14) Which SQL keyword is used to sort the result-set?
c) ORDER BY
15) With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?
b) SELECT * FROM Persons ORDER BY FirstName DESC
16) With SQL, how can you insert a new record into the "Persons" table?
a) INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
17) With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
a) INSERT INTO Persons (LastName) VALUES ('Olsen')
18) How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
b) UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
19) With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?
a) DELETE FROM Persons WHERE FirstName = 'Peter'
20) With SQL, how can you return the number of records in the "Persons" table?
b) SELECT COUNT(*) FROM Persons
21) Given an employees table as follows: empid name managerid a1 bob NULL b1 jim a1 B2 tom a1 What value will select count(*) from employees return?
c) 3
22) The result of a SELECT statement can contain duplicate rows.
a) True
23) Sometimes the expression "select count(*)" will return fewer rows than the expression "select count(value)".
b) False
24) What type of lock will deny users any access to a table?
c) EXCLUSIVE
25) Which of the following is the correct SQL statement to use to remove rows from a table?
c) DELETE
26) The only way to join two tables is by using standard, ANSI syntax.
b) False
27) A NULL value is treated as a blank or 0.
b) False
28) The left outer join is one type of outer join. Another one is the.
e)all of the above
Question 1
Examine the two SQL statements given below:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
The second statement returns an error
Question 2
Which of the following is not a numeric group function?
Highest
Question 3
Examine the data in the EMPLOYEES table given below:
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
Which of the following Subqueries work?
SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
Question 4
Which of the following statements are true?
With DDL you can create and remove tables, schemas, domains, indexes and views
Question 5
Which of the following clauses are not allowed in a single row sub-query?
Order by
Question 6
What is the collection of information stored in a database at a particular moment called?
Instance
Question 7
The overall logical structure of a database can be expressed graphically by:
Entity-Relationship Diagram
Question 8
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which German books(if any) are more popular than all the French?
select bookname from books where language='German' and popularityrating> (select max(popularityrating) from books where language='French')
Question 9
Which statements are true for views?
The definition of a view is stored in data dictionary
Views provide a more secure way of retrieving data
Question 10
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which Authors have written books on two or more subjects?
select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId having count(*)>1)
Question 11
What does the term DDL stand for?
Data Definition Language
Question 12
The concept of data independence is similar to the concept of ________
Abstract data type
Question 13
What are the programs that execute automatically whenever DML operations are performed on tablds called?
Triggers
Question 14
What clause should be used to display the rows of a table in ascending order of a particular column?
Order By
Question 15
What is the error in the following query if the Students table contains several records?
SELECT name FROM students WHERE name =
(SELECT name FROM students ORDER BY name);
= should be replace by in operator
An order by clause is not allowed in a subquery
Question 16
How can data be accessed by users who do not have direct access to the tables?
By creating views
Question 17
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which Authors have written at least 1 book with a popularity rating of less than 5?
select authorname from authors where authorid in (select authorid from books where popularityrating<5)
d) SELECT
3) Which SQL statement is used to update data in a database?
a) UPDATE
4) Which SQL statement is used to delete data from a database?
b) DELETE
5) Which SQL statement is used to insert new data in a database?
c) INSERT
6) With SQL, how do you select a column named "FirstName" from a table named "Persons"?
b) SELECT FirstName FROM Persons
7) With SQL, how do you select all the columns from a table named "Persons"?
d) SELECT * FROM Persons
8) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?
eter'
d)SELECT * FROM Persons WHERE FirstName='Peter'
9) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?
d) SELECT * FROM Persons WHERE FirstName LIKE 'a%'
10) The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
a) True
11) With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?
b) SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'
12) With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
b) SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
13) Which SQL statement is used to return only different values?
d) SELECT DISTINCT
14) Which SQL keyword is used to sort the result-set?
c) ORDER BY
15) With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?
b) SELECT * FROM Persons ORDER BY FirstName DESC
16) With SQL, how can you insert a new record into the "Persons" table?
a) INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
17) With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
a) INSERT INTO Persons (LastName) VALUES ('Olsen')
18) How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
b) UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
19) With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?
a) DELETE FROM Persons WHERE FirstName = 'Peter'
20) With SQL, how can you return the number of records in the "Persons" table?
b) SELECT COUNT(*) FROM Persons
21) Given an employees table as follows: empid name managerid a1 bob NULL b1 jim a1 B2 tom a1 What value will select count(*) from employees return?
c) 3
22) The result of a SELECT statement can contain duplicate rows.
a) True
23) Sometimes the expression "select count(*)" will return fewer rows than the expression "select count(value)".
b) False
24) What type of lock will deny users any access to a table?
c) EXCLUSIVE
25) Which of the following is the correct SQL statement to use to remove rows from a table?
c) DELETE
26) The only way to join two tables is by using standard, ANSI syntax.
b) False
27) A NULL value is treated as a blank or 0.
b) False
28) The left outer join is one type of outer join. Another one is the.
e)all of the above
Question 1
Examine the two SQL statements given below:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
The second statement returns an error
Question 2
Which of the following is not a numeric group function?
Highest
Question 3
Examine the data in the EMPLOYEES table given below:
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
Which of the following Subqueries work?
SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
Question 4
Which of the following statements are true?
With DDL you can create and remove tables, schemas, domains, indexes and views
Question 5
Which of the following clauses are not allowed in a single row sub-query?
Order by
Question 6
What is the collection of information stored in a database at a particular moment called?
Instance
Question 7
The overall logical structure of a database can be expressed graphically by:
Entity-Relationship Diagram
Question 8
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which German books(if any) are more popular than all the French?
select bookname from books where language='German' and popularityrating> (select max(popularityrating) from books where language='French')
Question 9
Which statements are true for views?
The definition of a view is stored in data dictionary
Views provide a more secure way of retrieving data
Question 10
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which Authors have written books on two or more subjects?
select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId having count(*)>1)
Question 11
What does the term DDL stand for?
Data Definition Language
Question 12
The concept of data independence is similar to the concept of ________
Abstract data type
Question 13
What are the programs that execute automatically whenever DML operations are performed on tablds called?
Triggers
Question 14
What clause should be used to display the rows of a table in ascending order of a particular column?
Order By
Question 15
What is the error in the following query if the Students table contains several records?
SELECT name FROM students WHERE name =
(SELECT name FROM students ORDER BY name);
= should be replace by in operator
An order by clause is not allowed in a subquery
Question 16
How can data be accessed by users who do not have direct access to the tables?
By creating views
Question 17
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which Authors have written at least 1 book with a popularity rating of less than 5?
select authorname from authors where authorid in (select authorid from books where popularityrating<5)
Question 19 A
table Students has a column called name which stores the names of the
students. What will be the correct query to display the names of the
students in reverse order?
Select name from students order by name desc;
Question 20
_________ is the operation that displays certain columns from the table.
Selection
Select name from students order by name desc;
Question 20
_________ is the operation that displays certain columns from the table.
Selection
Question 21 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?
Alter table t add primary key(c1);
Alter table t add primary key(c1);
Question 22 The primary key index does not allow ________ data in a field.
Null
Duplicate
Question 23
Which of the following are aggregate functions in SQL?
Avg
Sum
Null
Duplicate
Question 23
Which of the following are aggregate functions in SQL?
Avg
Sum
Question : 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
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:
5,2,1,4,3
Arrange the above steps in the correct order of execution:
5,2,1,4,3
1) What does SQL stand for?
c) Structured Query Language
2) Which SQL statement is used to extract data from a database?
d) SELECT
3) Which SQL statement is used to update data in a database?
a) UPDATE
4) Which SQL statement is used to delete data from a database?
b) DELETE
5) Which SQL statement is used to insert new data in a database?
c) INSERT
6) With SQL, how do you select a column named "FirstName" from a table named "Persons"?
b) SELECT FirstName FROM Persons
7) With SQL, how do you select all the columns from a table named "Persons"?
d) SELECT * FROM Persons
8) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?
eter'
d)SELECT * FROM Persons WHERE FirstName='Peter'
9) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?
d) SELECT * FROM Persons WHERE FirstName LIKE 'a%'
10) The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
a) True
11) With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?
b) SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'
12) With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
b) SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
13) Which SQL statement is used to return only different values?
d) SELECT DISTINCT
14) Which SQL keyword is used to sort the result-set?
c) ORDER BY
15) With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?
b) SELECT * FROM Persons ORDER BY FirstName DESC
16) With SQL, how can you insert a new record into the "Persons" table?
a) INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
17) With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
a) INSERT INTO Persons (LastName) VALUES ('Olsen')
18) How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
b) UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
19) With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?
a) DELETE FROM Persons WHERE FirstName = 'Peter'
20) With SQL, how can you return the number of records in the "Persons" table?
b) SELECT COUNT(*) FROM Persons
21) Given an employees table as follows: empid name managerid a1 bob NULL b1 jim a1 B2 tom a1 What value will select count(*) from employees return?
c) 3
22) The result of a SELECT statement can contain duplicate rows.
a) True
23) Sometimes the expression "select count(*)" will return fewer rows than the expression "select count(value)".
b) False
24) What type of lock will deny users any access to a table?
c) EXCLUSIVE
25) Which of the following is the correct SQL statement to use to remove rows from a table?
c) DELETE
26) The only way to join two tables is by using standard, ANSI syntax.
b) False
27) A NULL value is treated as a blank or 0.
b) False
28) The left outer join is one type of outer join. Another one is the.
e)all of the above
Question 1
Examine the two SQL statements given below:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
The second statement returns an error
Question 2
Which of the following is not a numeric group function?
Highest
Question 3
Examine the data in the EMPLOYEES table given below:
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
Which of the following Subqueries work?
SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
Question 4
Which of the following statements are true?
With DDL you can create and remove tables, schemas, domains, indexes and views
Question 5
Which of the following clauses are not allowed in a single row sub-query?
Order by
Question 6
What is the collection of information stored in a database at a particular moment called?
Instance
Question 7
The overall logical structure of a database can be expressed graphically by:
Entity-Relationship Diagram
Question 8
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which German books(if any) are more popular than all the French?
select bookname from books where language='German' and popularityrating> (select max(popularityrating) from books where language='French')
Question 9
Which statements are true for views?
The definition of a view is stored in data dictionary
Views provide a more secure way of retrieving data
Question 10
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which Authors have written books on two or more subjects?
select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId having count(*)>1)
Question 11
What does the term DDL stand for?
Data Definition Language
Question 12
The concept of data independence is similar to the concept of ________
Abstract data type
Question 13
What are the programs that execute automatically whenever DML operations are performed on tablds called?
Triggers
Question 14
What clause should be used to display the rows of a table in ascending order of a particular column?
Order By
Question 15
What is the error in the following query if the Students table contains several records?
SELECT name FROM students WHERE name =
(SELECT name FROM students ORDER BY name);
= should be replace by in operator
An order by clause is not allowed in a subquery
Question 16
How can data be accessed by users who do not have direct access to the tables?
By creating views
Question 17
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which Authors have written at least 1 book with a popularity rating of less than 5?
select authorname from authors where authorid in (select authorid from books where popularityrating<5)
c) Structured Query Language
2) Which SQL statement is used to extract data from a database?
d) SELECT
3) Which SQL statement is used to update data in a database?
a) UPDATE
4) Which SQL statement is used to delete data from a database?
b) DELETE
5) Which SQL statement is used to insert new data in a database?
c) INSERT
6) With SQL, how do you select a column named "FirstName" from a table named "Persons"?
b) SELECT FirstName FROM Persons
7) With SQL, how do you select all the columns from a table named "Persons"?
d) SELECT * FROM Persons
8) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?
eter'
d)SELECT * FROM Persons WHERE FirstName='Peter'
9) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?
d) SELECT * FROM Persons WHERE FirstName LIKE 'a%'
10) The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
a) True
11) With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?
b) SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'
12) With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
b) SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
13) Which SQL statement is used to return only different values?
d) SELECT DISTINCT
14) Which SQL keyword is used to sort the result-set?
c) ORDER BY
15) With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?
b) SELECT * FROM Persons ORDER BY FirstName DESC
16) With SQL, how can you insert a new record into the "Persons" table?
a) INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
17) With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
a) INSERT INTO Persons (LastName) VALUES ('Olsen')
18) How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
b) UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
19) With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?
a) DELETE FROM Persons WHERE FirstName = 'Peter'
20) With SQL, how can you return the number of records in the "Persons" table?
b) SELECT COUNT(*) FROM Persons
21) Given an employees table as follows: empid name managerid a1 bob NULL b1 jim a1 B2 tom a1 What value will select count(*) from employees return?
c) 3
22) The result of a SELECT statement can contain duplicate rows.
a) True
23) Sometimes the expression "select count(*)" will return fewer rows than the expression "select count(value)".
b) False
24) What type of lock will deny users any access to a table?
c) EXCLUSIVE
25) Which of the following is the correct SQL statement to use to remove rows from a table?
c) DELETE
26) The only way to join two tables is by using standard, ANSI syntax.
b) False
27) A NULL value is treated as a blank or 0.
b) False
28) The left outer join is one type of outer join. Another one is the.
e)all of the above
Question 1
Examine the two SQL statements given below:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
The second statement returns an error
Question 2
Which of the following is not a numeric group function?
Highest
Question 3
Examine the data in the EMPLOYEES table given below:
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
Which of the following Subqueries work?
SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
Question 4
Which of the following statements are true?
With DDL you can create and remove tables, schemas, domains, indexes and views
Question 5
Which of the following clauses are not allowed in a single row sub-query?
Order by
Question 6
What is the collection of information stored in a database at a particular moment called?
Instance
Question 7
The overall logical structure of a database can be expressed graphically by:
Entity-Relationship Diagram
Question 8
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which German books(if any) are more popular than all the French?
select bookname from books where language='German' and popularityrating> (select max(popularityrating) from books where language='French')
Question 9
Which statements are true for views?
The definition of a view is stored in data dictionary
Views provide a more secure way of retrieving data
Question 10
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which Authors have written books on two or more subjects?
select AuthorName from Authors where Authorid in (select Authorid from Books group by SubjectId having count(*)>1)
Question 11
What does the term DDL stand for?
Data Definition Language
Question 12
The concept of data independence is similar to the concept of ________
Abstract data type
Question 13
What are the programs that execute automatically whenever DML operations are performed on tablds called?
Triggers
Question 14
What clause should be used to display the rows of a table in ascending order of a particular column?
Order By
Question 15
What is the error in the following query if the Students table contains several records?
SELECT name FROM students WHERE name =
(SELECT name FROM students ORDER BY name);
= should be replace by in operator
An order by clause is not allowed in a subquery
Question 16
How can data be accessed by users who do not have direct access to the tables?
By creating views
Question 17
Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book ON a scale of 1 TO 10)
Languag? (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 which Authors have written at least 1 book with a popularity rating of less than 5?
select authorname from authors where authorid in (select authorid from books where popularityrating<5)
Question 19
A table Students has a column called name which stores the names of the students. What will be the correct query to display the names of the students in reverse order?
Select name from students order by name desc;
Question 20
_________ is the operation that displays certain columns from the table.
Selection
A table Students has a column called name which stores the names of the students. What will be the correct query to display the names of the students in reverse order?
Select name from students order by name desc;
Question 20
_________ is the operation that displays certain columns from the table.
Selection
Question 21
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?
Alter table t add primary key(c1);
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?
Alter table t add primary key(c1);
Question 22
The primary key index does not allow ________ data in a field.
Null
Duplicate
Question 23
Which of the following are aggregate functions in SQL?
Avg
Sum
The primary key index does not allow ________ data in a field.
Null
Duplicate
Question 23
Which of the following are aggregate functions in SQL?
Avg
Sum
No comments:
Post a Comment