Thursday, August 29, 2013

MySQL 5.0 Test Question Answers 2013-2014

Q1.Which operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 – 8) from emp
a.   +
b.   -
c.   /
d.   *(answer)
Q2.Can you run multiple MySQL servers on a single machine?
a.   No
b.   Yes
Q3.Which of the following are not String column types?
a.   BLOB
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
Q5.Is the following query valid?
create table foo (id int primary key auto_increment, name varchar);
a.   No
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;
a.   Yes
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
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
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
Q10.What is the correct order of clauses in the select statement?
1        select
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
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?
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
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
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
Q14.Is it possible to insert several rows into a table with a single INSERT statement?
a.   No
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);
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’
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
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
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));
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)
Subjects
———
subjectid
subject (such as History, Geography, Mathematics etc)
authors
——–
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))
Q20.Consider the following select statement and its output:
SELECT * FROM table1 ORDER BY column1;
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
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
Q22.The STUDENT_GRADES table has these columns:
STUDENT_ID INT
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
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
Q24.Which of the following is not a valid Arithmetic operator?
a.   +
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
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
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
Q28.Examine the code given below:
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
Q29.What kind of joins does MySQL support?
a.   dual 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’
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
Q32.Which of the following operators has the highest precedence?
a.   BINARY
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)
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
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
Q36.Which of the following is not a MySQL statement?
a.   ENUMERATE
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”
Q38.State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine
a.   True
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
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
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?
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
Q42.MySQL supports 5 different int types. Which one takes 3 bytes?
a.   TINYINT
b.   MEDIUMINT
c.   SMALLINT
d.   INT
e.   BIGINT

oDesk MySQL 5.0 Test Answers


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

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
5)It chooses rows based on the where clause
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)
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
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);
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

No comments:

Post a Comment

Post a Comment