Mysql考試整理

mysql整理
MySQL_Major_test
1、Which one of the following keyword is used if a sub-query is likely to return a list of values and you need to match a column value with any of the values in this list to obtain the final result?
如果子查詢可能返回值列表，並且您需要將列值與該列表中的任何值進行匹配以獲得最終結果，則使用以下哪個關鍵字？
A. EXIST
B.Any
C.In
D.All
2、You are a database administrator for Hainan University. You have been assigned the task of identifying the courses for which no registration happened in the current year. The course details are stored in the table CourseDetail having attributes CourseID, Title, Duration and Fees. The student details are stored in the table named StudentDetails having attributes StudentID, CourseID, Name and Contact. While the correct statement that you need to use to implement the preceding functionality. B
您是海南大學的資料庫管理員。 您已獲得識別當年未註冊課程的任務。 課程詳細資訊儲存在具有屬性CourseID，標題，持續時間和費用的課程CourseDetail中。 學生詳細資訊儲存在名為StudentDetails的表中，該表具有屬性StudentID，CourseID，姓名和聯絡方式。 同時需要使用正確的語句來實現上述功能.
A. SELECT * FROM courseDetail c WHERE NOT EXIST (SELECT * FROM StudentDetails s where s.CourseID=null);
B. SELECT * FROM CourseDetail c WHERE NOT EXIST (SELECT * FROM StudentDetails s where c.CourseID=s.CourseID);
C. SELECT * FROM CourseDetail c WHERE EXIST course= (SELECT * FROM StudentDetails s where c.CourseID=s.CourseID);
D. SELECT * FROM CourseDetail c WHERE EXIST (SELECT * FROM StudentDetails s where c.CourseID=s.CourseID);
3、You have a table name EmployeeDetails having five columns EmployeeID, EmpName, Designation, DeptNo and city. You need to retrieve the details of all the employees who live in the same city as Martin. For this you have written a following statement. A
您有一個名為EmployeeDetails的表名，該表具有五個列EmployeeID，EmpName，Designation，DeptNo和city。 您需要檢索與馬丁居住在同一城市的所有員工的詳細資訊。 為此，您編寫了以下宣告。
SELECT * FROM EmployeeDetails WHERE city= SELECT city FROM EmployeeDetails WHERE EmpName=’Martin’;
However, the preceding statement generates an error. Identify the correct statement that will you use to resolve the error.
但是，前面的語句生成一個錯誤。 標識將用於解決錯誤的正確語句。
A. SELECT * FROM EmployeeDetails WHERE city IN (SELECT city FROM EmployeeDetails WHERE EmpName=’Martin’);
B. SELECT * FROM EmployeeDetails WHERE city <> ALL (SELECT city FROM EmployeeDetails EmpName=’Martin’);
C. SELECT * FROM EmployeeDetails WHERE EXIST (SELECT * FROM EmployeeDetails WHERE EmpName=’Martin’);
D. SELECT * FROM EmployeeDetails WHERE city NOT IN (SELECT city FROM EmployeeDetails WHERE EmpName=’Martin’);
4、Consider the following query:
SELECT st.name AS territory, sp.SalePersonID FROM
sales.SaleTerritory st RIGHT OUTER JOIN Sales.SalesPerson sp
ON st.TerritoryID= sp.TerritoryID;
What will be the output of the preceding query? D
A. It display the name of the sale persons.
B. It display the territory assigned to each saleperson.
C. It display the id of all the sale person
D. It display the id of all the sales person and the name of the territory that has been assigned to them.
5、You need to select those EMPLOYEE whose AGE is not between 30 and 40. Identify the correct statement that you can use to get the correct output
您需要選擇年齡不在30到40之間的那些僱員。確定可用於獲得正確輸出的正確語句。 A
A. Select * from EMPLOYEE where AGE not between 30 and 40;
B. Select * from EMPLOYEE where AGE in (30,40);
C. Select * from EMPLOYEE where AGE between 30 and 40;
D. Select * from employee where AGE not in (30, 40);
6、Sam has written has written the following statement to display the record of all those employees whose name is starts with A and end with R. But this statement does not give the desire output on execution. Identify the correct statement from the options provided below that Sam should use to get the correct output. D
Sam編寫了以下語句，以顯示所有名稱以A開頭和R結束的所有僱員的記錄。但是該語句在執行時沒有給出期望的輸出。 從下面提供的選項中識別正確的語句，Sam應該使用這些選項來獲得正確的輸出。
A. Select * from employee where EmpName=’A%R’;
B. Select * from employee where EmpName in ‘A%R’;
C. Select * from Employee where EmpName not like ‘A%R’;
D. Select * from Employee where EmpName like ‘A%R’;
7、Which of the following functions can be used to retrieve the record from the EMPLOYEE table where the value of EMPLOYEENAME column begins with ‘tev’? D
A. Select * from EMPLOYEE where EMPLOYEENAME like (‘tev%’);
B. Select * from EMPLOYEE where EMPLOYEENAME=’tev%’;
C. Select * from EMPLOYEE where EMPLOYEENAME in (‘tev%’);
D. Select * from EMPLOYEE where EMPLOYEENAME like ‘tev%’;
8、Consider the EMPLOYEE table with the attributes ECODE, DEPT and DEPHEAD. EMPLOYEE table contains a single value for each cell. The primary key in the EMPLOYEE table is ECODE. The DEPT attribute is functionally dependent on ECODE. DEPTHEAD is also functionally dependent on ECODE. The attribute DEPHEAD is dependent on attribute DEPT also. Which one of the following normal form is applicable for the table?
考慮具有屬性ECODE，DEPT和DEPHEAD的EMPLOYEE表。 EMPLOYEE表包含每個單元格的單個值。 EMPLOYEE表中的主鍵是ECODE。 DEPT屬性在功能上取決於ECODE。 DEPTHEAD在功能上還取決於ECODE。 屬性DEPHEAD也依賴於屬性DEPT。 該表適用以下哪種標準形式？ C
A. 1NF
B. 2NF
C. 3NF
D. BCNF
9、Attribute A is said to be _________________ on B if and only if for each value of B there is exactly one value of A.
當且僅當對於B的每個值都恰好有一個A值時，才將屬性A稱為_________________。B
A. Transitively dependent
B. Functionally dependent
C. Not functionally dependent
D. Partially dependent
10、Consider the scenario of Hainan University. In the university, there are three main department, admission library and payroll. Each department store and maintain data individually. Therefore the data used by admission department, cannot be used by the library and payroll departments. Similarly data used by library department cannot be used by admission and payroll departments. Therefore same data may be defined multiple times. Which one of the following options correctly described the drawbacks associated with this approach of data storage?
考慮海南大學的情況。 在大學中，有三個主要部門，即入學圖書館和工資單。 每個部門分別儲存和維護資料。 因此，入庫部門使用的資料不能由圖書館和工資部門使用。 同樣，圖書館部門使用的資料不能由入學和工資部門使用。 因此，可以多次定義相同的資料。 以下哪個選項正確地描述了與這種資料儲存方法相關的缺點？ D
A. Data Consistency
B. Data independence
C. Less disk-space utilization
D. Data Redundancy
11、Which of the following categories of SQL statement is used to define the database, data types, structures and the constraints on the data? D
A. DQL
B. DML
C. DCL
D. DDL
12、Which is the main difference between object-based logical model and record –based logical model? D
A. There is no significant difference
B. Object-based logical model is focused on specifying the logical structure of the database but record- based logical model is focused on specify the relationship among the data.
C. Object-based logical model is focused on specifying the logical structure of the database but record-based logical model is focused on describing the data, the relationship among the data any constraints defined.
D. Object-based logical model is focused on describing the data, the relationship among the data any constraints defined but record-based logical model is focused on specifying the logical structure of the database
13、Consider an example of an organization in which the employee table as different attributes, such as EmployeeName, EmpID, salary, department and Manager ID. Based on the mode of remuneration the employee table can further be divided into two different tables, salaried_employee and hourly_employee. Which of the following term correctly explain this practice of splitting the table. A
考慮一個組織示例，在該示例中，employee表具有不同的屬性，例如EmployeeName，EmpID，薪水，部門和Manager ID。 根據薪酬模式，員工表可以進一步分為兩個不同的表，salaried_employee和hourly_employee。 以下哪個術語正確解釋了拆分表的這種做法。
A. Specialization
B. Generalization
C. Association
D. Aggregation
14、You are database designer at FIS technologies; you have to create an ER diagram for designing a database. You have identified two entities named student and scorecard. The entity student has the attributes SID, Name, Course and Semester while the entity scorecard has the attributes SID, Mark and grade. A scorecard entity type would always belong to a particular student types. Also the student entity is the main building block of the database. Which of the following statements are correct in the given context? B
您是FIS技術的資料庫設計師； 您必須建立用於設計資料庫的ER圖。 您已經確定了兩個實體，分別稱為Student和Scorecard。 實體學生具有屬性SID，名稱，課程和學期，而實體記分卡具有屬性SID，標記和成績。 計分卡實體型別將始終屬於特定的學生型別。 學生實體也是資料庫的主要組成部分。 在給定的上下文中，以下哪個陳述是正確的？
A. Student is the super type of scorecard.
B. Student is the regular entity while scorecard is a weak entity.
C. Scorecard is the super type of the student.
D. Student is the weak entity while scorecard is the regular entity.
15、Identify the regular entity, weak entity, attributes and relationship in the following diagram.

B
A. Regular entity: EMPLOYEEADDRESS
Weak Entity: EMPLOYEE, PROJECT
Attributes: EMPNO, CITY, ADDRESSID, SALARY, NAME, EMPNO.
Relationship: PROJWORK
B. Regular Entity: EMPLOYEE, PROJECT
Weak Entity: EMPLOYEEADDRESS
Attributes: EMPNO, CITY, ADDRESSID, SALARY, NAME, EMPNO.
Relationship: PROJWORK
C. Regular entity: EMPLOY EE, PROJECT
Attributes: EMPNO, CITY, ADDRESSID, SALARY, NAME, EMPNO.
Relationship: EMPLOYEEADDRESS, PROJWORK
D. Regular Entity: EMPLOYEEADDRESS, EMPLOYEE, PROJECT
Attributes: EMPNO, CITY, ADDRESSID, SALARY, NAME, EMPNO.
Relationship: PROJWORK
16、A student can get more than one book issued from the library, also one book can be issued to many students. Identify the correct statement with respect to the above scenario.
一個學生可以從圖書館獲得多於一本書，也可以向許多學生髮行一本書。 確定有關上述情況的正確陳述。 B
A. There exists a many-to-one relationship between the student and the book entity.
B. There exists a many-to-many relationship between the student and the book entity
C. No relation exists between the student and the book entity.
D. There exists one-to-one relationship between the student and the book entity.
17、Identify the correct statement that needs to be executed to display the top 10 rows from ORDER table. C
A. Select top 10% from ORDER;
B. Select top 10 percent from ORDER;
C. Select * from ORDER limit 10;
D. Select limit 10 from ORDER;

Chapter5
1、A view can be deleted using the command D
A. REMOVE
B. DELETE
C. CLEAR
D. DROP
2、What cannot be done on a view? C
A. display
B. filter
C. Index
D. drop
3、What is abc in the following MySQL statement？B
CREATE VIEW xyz (abc) AS SELECT a FROM t;
A. row name
B. column name
C. view
D. database
4、Which statement is used to remove indexes on tables? A
A. DROP INDEX
B. DELETE INDEX
C. REMOVE INDEX
D. FLUSH INDEX
5、To show index, the sql query will be used is: A
A. show index from table_name;
B. show index inxed_name from table name;
C. view index from table name;
D. view index index_name from table_name
6、John work as a database developer in the newIT, an IT company. He has created a view named GET_Manager_Data to fetch the details of all the managers at NewIT. He wants to fetch the data of all the software developers. To accomplish this he as to modify the definition of the Get_Manager_Data view. Which one of the following statement will allow him to achieve this. B
A. Drop View
B. Alter View
C. Delete from
D. Update
7、Which one of the following clause when used inside a view definition, replace the definition of an existing view? A
A. OR REPLACE
B. ALGORITHM
C. WITH CHECK OPTION
D. WITH LOCAL CHECK OPTION
8、Which one of the following statement is true about the view? D
A. A view can be created on a temporary table.
B. A trigger can be created on a view.
C. An Index can be created on a view.
D. The SELECT statement in a view definition cannot contain subquery in the form FORM clause
9、Joe work as a database Developer in ITSol, an IT company. Currently , he is working on a new project of the NewHopes hospital. While creating a database table that stores the detail about all the diseases , he needs to ensure that each disease should have a unique description. Which one of the following indexes should he create on the Description column of the disease table? B
A. primary key
B. unique
C. foreign key
D. regular
10、Which one of the following indexes can be created only on the column that accept string value? C
A. Primary Key
B. Foreign key
C. Full-text
D. unique

Chapter6

1. Syntax to create stored procedure is/are D
   A. CREATE PROCEDURE procedureName ()
   SQL statements
   B. CREATE PROCEDURE procedureName()
   BEGIN
   SQL statements
   END
   C. CREATE PROC procedureName()
   BEGIN
   SQL statements
   END
   D. B & C
   2、A stored procedure in SQL is a___________ B
   A. Block of functions
   B. Group of SQL statements.
   C. None
   D. all
2. Repeat C
   sequence of statements;

---

end repeat
Fill in the correct option :
A. While Condition
B. Until variable
C. Until boolean expression
E. Until 0
4、Which of the following is used to input the entry and give the result in a variable in a procedure? D
A. Put and get
B. Get and put
C. Out and In
D. In and out
5. Dilimiter // B
Create function dept_count(dept_name varchar(20)) return int
begin
declare d_count integer;
select count() into d_count
from instructor
where instructor.dept_name= dept_name
return d_count;
end //
Dilimiter;
fom the above function, which of the following is a proper select statement ?
A. SELECT dept_name, budget
FROM instructor
WHERE dept COUNT() > 12;
B. SELECT dept_name, budget
FROM instructor
WHERE dept COUNT(dept_name) > 12;
C. SELECT dept name, budget
WHERE dept COUNT(dept_name) > 12;
D. SELECT dept_name, budget
FROM instructor
WHERE dept COUNT(budget) > 12;
6. Q.
Dilimiter // A
Create function dept_count(dept_name varchar(20))
begin
declare d_count integer;
select count() into d_count
from instructor
where instructor.dept_name= dept_name
return d_count;
end //
Dilimiter;
Find the error in the the above statement.
A. Return type missing
B. Dept_name is mismatched
C. Reference relation is not mentioned
D. All of the mentioned
7. Consider the following statements: C
Dilimiter //
create procedure getAvgRate (out rate double)
begin
select avg(rental_rate) into rate from film;
end //
Dilimiter;
Which of the following statement will you use to execute the preceding procedure?
A. call getAvgRate (a);
select a;
B. declare a;
call getAvgRate (a);
select a;
C. call getAvgRate (@a);
select @a;
D. Declare @a;
call getAvgRate (@a);
select @a;
8. Which of the following is the SQL Exception HANDLER actions? D
A. CONTINUE
B. EXIT
C. UNDO
D. Al
9. A CASE SQL statement is which of the following? A
A. A way to establish an IF-THEN-ELSE in SQL.
B. A way to establish a loop in SQL.
C. A way to establish a loop in SQL.
D. A way to establish a data definition in SQL.
10. Which statement exits a labeled flow-control construct? B
A. DESCRIBE
B. LEAVE
C. LOOP
D. RETURN
11. The statement that constructs a branching flow-control construct is _____________ B
A. BEGIN…END
B. CASE
C. ITERATE
D. LEAVE

Chapter7
1、Which isolation level remove dirty read problem? A
A. Read-committed
B. Read-uncommitted
C. repeatable read
D. serializable
2、How many types of Isolation Level are there in mysql? C
A. 2
B. 3
C. 4
D. 5
3、What is ghi in the following MySQL statement? C
CREATE TRIGGER abc (…) (…) ON def FOR EACH ROW ghi;
A. trigger name
B. table name
C. trigger statement
D. update statement
4、What is the default isolation level in innodb? C
A. Read-committed
B. Read-uncommitted
C. repeatable read
D. serializable
5、Which one of the following database objects is automatically executed when a DML statement, such as update, delete, or insert , is performed on the corresponding table? A
A. Trigger
B. View
C. Stored procedure
D. transaction
6、which of the follwing statements will you use to place read lock on the department table defined with the MyISAM engine? D
A. Read lock table Department
B. read lock on table department
C. lock table department read only
D. lock table department read
7、Which one of the following isolation levels is default for innoDB? C
A. read uncommitted
B. read commited
C. repeated read
D. serializable
8、Which one of the following trigger will you create to prevent the insertion of a negative value in a numeric field? A
A. Before Insert
B. Before update
C. After insert
D. After update
9、What are the after triggers? B
A. Triggers generated after a particular operation
B. These triggers run after an insert, update or delete on a table
C. These triggers run after an insert, views, update or delete on a table
D. All of the mentioned

Chapter 8
1、In the following query what is the meaning of --ignore-lines=2 ? A
shell> mysqlimport --ignore-lines=2 test d:\students.txt
A. a. it will ignore first 2 line while reading the text f
B. a. it will ignore the last two line while reading the text file
C. a. it will ignore any two lines
D. a. none
2、In which of the following the imported filename should be exactly same as the table name? B
A. mysql command
B. mysqlimport
C. source command
D. load data infile
3、Using LOAD DATA statement John wants to import the data from file to a table but he does not wants to import the column names. What command he will use to perform the operation? B
A. by using IGNORE
B. by using IGNORE 1LINES
C. by using IGNORE=1
D. any one
4、To import the data of a file into a table , you can use which one of the following statements? A
A. mysqlimport production_management_system d:order_status.txt;
B. mysqlimport d:order_status.txt;
C. mysqlimport production_management_system d:order_status.sql;
D. none
5、Which one of the following commands can be used to import the data into the Student_Details database by executing mysql commands stored in the text file StudentData.sql? A
A. mysql –h localhost –u root –p student_details < “ :studentData.sql”
B. mysql –h localhost –u root –p student_details
C. mysql –h localhost –u root –p < “ :studentData.sql”
D. mysql –h –u root –p student_details < “ :studentData.sql”
6、Which one of the following options can be used to execute SQL statements and mysql commands from within a text file without invoking the command line client? C
A. source
B. LOAD DATA INFILE statement
C. Mysqimport utility
D. select into dumpfile statement
7、Which one of the following options can be used to import data into MySQL table by executing SQL statements stored in a sql file? A
A. Source command
B. LOAD DATA INFILE statement
C. Mysql Import utility
D. select into dump file statement
8、Which of the following is a property of transactions? D
A. Atomicity
B. Durability
C. Isolation
D. All of the mentioned
9、mysqldump is a utility used for backing up databases or specific tables of a database.對
對
錯
10、if no session or global keyword is specified, the isolation is set only for the next transaction to be performed in the current session. 對
對
錯

Chapter 9
1、Which of the following ways A Database Administrator can create user accounts ? C
A. Using the CREATE USER statement
B. Using the INSERT statement
C. both
D. none
2、The initial set of accounts can be categorized as: C
A. Superuser accounts
B. Anonymous accounts
C. both
D. none
3、A user account is defined in terms of : C
A. user name
B. host
C. both
D. none
4、
Which one of the following syntaxes of the mysqldump command is used to tal backup of a few databases and store it in a text file? D
A. mysqldump [–flush-logs] -u <user_name> -p <database_name> >
B. mysqldump [–flush-logs] -u - [

[…]]>
C. mysqldump --all-databases >
D.mysqldump [–flush-logs] -u -p --database [<database_name>…]>
5、Which one of the following commands is used to enable binary logging on the system where the MYSQL server is installed? A
A. mysqld
B. mysqldump
C. mysqlbinlog
D. mysql
6、Which one of the following privilege types can be specified in a GRANT statement to allow a user to create a table and a database? C
A. CREATE ROUTINE
B. CREATE VIEW
C. CREATE
D. CREATE USER
7、Which one of the following statements can be used for setting a new password for a user account as well as changing the current password associated with a user account? A
A. SET PASSWORD
B. UPDATE
C. DELETE
D. CREATE USER
8、The execution of which one of the following statements is not followed by the execution of the FLUSH D
A. INSERT
B. UPDATE
C. DELETE
D. CREATE USER
9、 Which of the following benefits provided by replication? D
A. Load sharing
B. Fault tolerance
C. Data security
D. all
10、When MySQL is installed on a system, some of the user accounts are automatically created in it. These user accounts are known as the initial set of accounts.對
對
錯
11、The source command allow you to specify the name of any database name to be recovered.錯
對
錯
12、To synchronize the data at the slave server with the data at the master server, the relay log files are executed from time to time. 對
對
錯

Major test
1、view does not support D
A. trigger
B. index
C. temporary table
D. all
2、Which one of the following triggers is activated prior to adding new rows in the table? C
A. AFTER UPDATE
B. AFTER INSERT
C. BEFORE UPDAE
D. None
3、Which one of the following options assigns the privileges on all the tables of a specific database to the user? C
A. .
B. *
C. Database_name.*
D. None
4、When multiple users are interacting with the database, the MySQL server needs to ensure independent working of each transaction.
Which one of the following components of the MySQL server helps in achieving this task? A
A. Transaction manager
B. Concurrency control
C. manager Log manager
D. None
5、Callie created a table named Employee that contains four columns:
EmployeeID, EmpName, Salary, and Address. The storage engine for the Employee table is InnoDB. Callie noticed that a large number of query requests are based on the EmpName column, thus he wants to create an index on this column to improve the performance of these queries. Which one of the following indexes should he create on this column? D
A. Unique
B. Primary Key
C. Full-text
D. None
6、Joe is a Database Administrator at ClickToBook.com, a company dealing with online hotel booking. The Customer table stores the details of all the customers. Joe needs to export all the data from the Customer table into an out file in such a way that each value in a row is terminated by a comma symbol. Which one of the following clauses of the SELECT INTO command can be used to achieve this purpose? A
A. FIELDS TERMINATED BY ’,’
B. LINES TERMINATED BY ’,’
C. FIELD TERMINATED BY ‘,’
D. none
7、You work as a Database Administrator in Logix Auto Lid., a company dealing with automation technology. The Database Developer has written a query to display the details of all the customers whose age is more than 50 years from the Customer table
Before executing the query, you need to choose the best plan for executing the query. Which one of the following statements can be used to achieve the desired result? D
A. SHOW SELECT * FROM Customer WHERE age > 50;
B. SELECT * FROM Customer EXPLAIN WHERE age > 50;
C. SELECT * FROM Customer WHERE age > 50 EXPLAIN;
D. None
8、You need to insert details of the employees and their salaries in the Employee and Employee_Account table. After inserting details of each employee in the Employee and Employee_Account tables, you need to mark a point in a transaction up to which the statements can be rolled back. Which one of the following options can be used to achieve this task? B
A. START TRANSACTION;
INSERT INTO Employee VALUES(E01, ‘Binny’);
INSERT INTO Employee Account VALUES(A 01, E01,50,000)；
SAVEPOINT ;
INSERT INTO Employee VALUES(E02, ‘Peter’);
INSERT INTO Employee Account VALUES(A02,E 02, 35,000);
COMMIT
B. START TRANSACTION;
INSERT INTO Employee VALUES(E01, ‘Binny’);
INSERT INTO Employee Account VALUES(A 01, E01,50,000);
INSERT INTO Employee VALUES(E02, ‘Peter’);
INSERT INTO Employee Account VALUES(A02,E 02, 35,000);
SAVEPOINT sav2 ;
rollback
C. START TRANSACTION;
INSERT INTO Employee VALUES(E01, ‘Binny’);
INSERT INTO Employee Account VALUES(A 01, E01,50,000);
SAVEPOINT ;
INSERT INTO Employee VALUES(E02, ‘Peter’);
INSERT INTO Employee Account VALUES(A02,E 02, 35,000);
SAVEPOINT
D. none
9、You are a Database Developer at Ezeemovies.com. which deals with the online booking of movie tickets. You want to ensure that if one operator books ticket(s) a lock is placed on the table and other operators have to wait for viewing the number of tickets available for the given movie. Which isolation level should you set for your transaction? D
A. READ-COMMITTED
B. READ-UNCOMMITED
C. REPEATABLE READ
D. None
10、To print the table of five, you developed the following code:
CREATE PROCEDURE Table_Num(）
BEGIN
DECLARE a, Counter, num INT;
SET a=5, Counter=1;
WHILE Counter >= 10 DO
SET num=aCounter;
SELECT num;
SET Counter=Counter+1;
END WHILE;
END
However, when you executed the preceding code, you did not get the output as expected. Which one of the following code snippet will give the correct output? B
A. BEGIN
DECLARE a, Counter, num INT:
SET a=5,
Counter=1;
WHILE Counter<= 10 DO
SET num= a+Counter;
SELECT num;
SET Counter=Counter+1;
END WHILE;
END
B. BEGIN
DECLARE a, Counter, num INT:
SET a=5,
Counter=1;
WHILE Counter<= 10 DO
SET num= aCounter;
SELECT num;
SET Counter=Counter+1;
END WHILE;
END
C. BEGIN
DECLARE a, Counter, num INT:
SET a=5,
Counter=1;
WHILE Counter<10 DO
SET num= a*Counter;
SELECT num;
SET Counter=Counter+1;
END WHILE;
END
D. none
11、consider the scenario of Showman House, a very large event management company in South America. For storing the data about various events managed by the company, the Database Developer at the company created and populated a table named Events. The Database Developer now wants to delete the record for the event having ID as 2. For this, he created the following statement:
TRUNCATE FROM Events WHERE EventID=2;
However, when the Database Developer executed the preceding statement, he did not get the expected message from the MySQL server. Which one of the following statements should the Database Developer use to accomplish the required task? B
A. DELETE * FROM Events;
B. DELETE FROM Events WHERE EventID=2;
C. DELETE Table Events WHERE EventID=2;
D. None
12、Which is more efficient? A
LOAD DATA, INSERT
A. LOAD DATA
B. INSERT
C. Same
D. Indeterminate
13、To check how MySQL would execute a SELECT query, which statement is used? D
A. TELL
B. SHOW
C. DISPLAY
D. EXPLAIN
14、The clause that filters JOIN results is called _________ A
A. WHERE
B. SORT
C. GROUP
D. GROUP BY
15、Which keyword in the UPDATE statement is used to assign values to columns? B
A. ASSIGN
B. SET
C. MARK
D. GET

1-2_REvision_test
1、view does not supportA. triggerCB. indexOC. temporary tableoD. All
2、Which one of the following triggers is activated prior to adding new rows in the table? D
A. AFTER UPDATE
B. AFTER INSERT
C. BEFORE UPDAE
D. None
3、Which one of the following options assigns the privileges on all the tables of a specific database to the user? C
a.b.c. d
A. .
B. *
C. Database name*
D. None
4、. When multiple users are interacting with the database, the MySQL server needs to ensure independent working of each transaction.Which one of the following components of the MySQL server helps in achieving this task? B
A. Transaction manager
B. Concurrency control
C. manager Log manager
D. None
5、Callie created a table named Employee that contains four columns:EmployeelD, EmpName, Salary, and Address. The storage engine for the Employee table is InnoDB. Callie noticed that a large number of query requests are based on the EmpName column, thus he wants to create an index on this column to improve the performance of these q ueries. Which one of the following indexes should he create on this column? D
A. Unique B. Primary Key C. Full-text D. None
6、Joe is a Database Administrator at ClickToBook.com, a company dealing with online hotel booking. The Customer table stores the details of all the customers. Joe needs to export all the data from the Customer table into an out file in such a way that each value in a row is terminated by a comma symbol. Which one of the following clauses of the SELECT INTO command can be used to achieve this purpose? A
a.
b.
c.FIELD TERMINATED BY ‘,’
D
A. FIELDS TERMINATED BY’,’
B. B. LINES TERMINATED BY‘,’
C. FIELD TERMINATED BY‘,’
D. None
7、You work as a Database Administrator in Logix Auto Lid., a company dealing with automation technology. The Database Developer has writ ten a query to display the details of all the customers whose age is more than 50 years from the Customer table.Before executing the query, you need to choose the best plan for executing the query. Which one of the following statements can be used t oachieve the desired result? D
A. SHOW SELECT * FROM Customer WHERE age > 50;
B. SELECT * FROM Customer EXPLAIN WHERE age > 50;
C. SELECT * FROM Customer WHERE age > 50 EXPLAIN;
D. None
8、You need to insert details of the employees and their salaries in the Employee and Employee Account table. After inserting details of each e . mployee in the Employee and Employee Account tables, you need to mark a point in a transaction up to which the statements can be rolled.back. Which one of the following options can be used to achieve this task? B
A. START TRANSACTIONNSERT INTO Employee VALUES(E01, 'Binny);INSERT INTO Employee Account VALUES(A 01, E01,50,000);SAVEPOINTINSERT INTO Employee VALUES(E02, 'Peter);INSERT INTO Employee Account VALUES(A02,E 02, 35,000);COMMIT
B. START TRANSACTIONINSERT INTO Employee VALUES(E01, 'Binny);INSERT INTO Employee Account VALUES(A 01, E01,50,000);INSERT INTO Employee VALUES(E02, 'Peter).INSERT INTO Employee Account VALUES(A02, E 02, 35,000);SAVEPOINT sav2;rollback
C. START TRANSACTIONINSERT INTO Employee VALUES(E01, 'Binny).INSERT INTO Employee Account VALUES(A 01, E01,50,000);SAVEPOINT;INSERT INTO Employee VALUES(E02, 'Peter);INSERT INTO Employee Account VALUES(A02,E 02, 35,000);SAVEPOINT
D. None
9、You are a Database Developer at Ezeemovies.com. which deals with the online booking of movie tickets. You want to ensure that if one oper ator books ticket(s) a lock is placed on the table and other operators have to wait for viewing the number of tickets available for the given m ovie. Which isolation level should you set for your transaction? D
A. READ-COMMITTED
B. READ-UNCOMMITED
C. REPEATABLE READ
D. None
10、
11、Consider the scenario of Showman House, a very large event management company in South America. For storing the data about various ev ents managed by the company, the Database Developer at the company created and populated a table named Events. The Database Develo per now wants to delete the record for the event having ID as 2. For this, he created the following statement:TRUNCATE FROM Events WHERE EventID-2However, when the Database Developer executed the preceding statement, he did not get the expected message from the MysQL server. Which one of the following statements should the Database Developer use to accomplish the required task? B
A. DELETE * FROM Events;
B. DELETE FROM Events WHERE EventID=2;
C. DELETE Table Events WHERE EventID=2;
D. None
11、Which is more efficient? A
LOAD DATA,INSERT
A. LOAD DATA B. INSERT C. Same D. Indeterminat
12、To check how MySQL would execute a SELECT query, which statement is used? D
A. TELL B. SHOW C. DISPLAY D. EXPLAIN
13、The clause that filters JOIN results is called A
A. WHERE B. SORT C. GROUP D. GROUP BY
14、Which keyword in the UPDATE statement is used to assian values to columns? B
A. ASSIGN
B. SET
C. MARK
D. GET
15、To reload a delimited text data file use C
A. Mysqldump
B. Mysqld
C. Mysqlimport
D. mysqInaive
16、What is the value of val2 in the following MysQL statement? C
UPDATE t SET val1 = val1 + 2, val2 = val1;
A. previous val1
B. updated val
C. Unchanged
D. Val1 +1
17、What is x in the following MySQL statement? B
DELETE FROM x USING x LEFT JOIN y ON x.col =y.col;
A. column name
B. table name
C. server name
D. database name
18、What is abc in the following SQL statement? A
DELETE FROM Xyz WHERE abc =5;
A. column name
B. table name
C. row name
D. database name
19、Replication enables data from one MySQL database server to be copied to one or more MySQL database servers. 對

1、Which one of the following is true about Temporary table? D
A. A temporary table exists as long as the current session of the user exists
B. A temporary table exists when the table is explicitly deleted by the user before terminating t he current session.O
C. A temporary table in the database can have the same name as any existing non-temporary t able.
D. all
2、Which one of the following data types contains a fixed number of characters? B
A. Varchar B. char C. both D. none
3、When the UNSINGED keyword is used with a column, the column can only accept value. B
A. Negative B. Positive C. Any D. None
4、To remove a database production management system which statement is used? B
A. REMOVE DATABASE production management_system;
B. DROP DATABASE production management system;
C. Both can be used
D. None
5、John is a database developer and working in a Paul Furnishers Ltd. He has to create a database name production management system with default character set and collate as Chinese. Which one of the following is the suggested way for creating a database? C
A. CREATE DATABASE IF NOT EXIST production management system DEFAULT CHARACTER S ET bia DEFAULT COLLATE big-5 chinese ci
B. CREATE DATABASE IF NOT EXIST production management system DEFAULT CHARACTER SET big5 chinese ci
C. CREATE DATABASE IF NOT EXISTS production management syetem DEFAULT CHARACTER SET bia5 chinese ci
D. None
6、Which of the following commands is used to see the databases in MysQL server? B
A. Show database
B. Show databases.
C. View database
D. View databases
7、 is a named block of SQL statement and procedural statements that are stored under one name and return a value A
A. Function B. Stored procedure C. Transaction D. none
8、 is a named block of SQL and procedural statements that are stored in the server and executed as a single unit. C
A. Transaction B. Function c. stored procedure D. None
9、 is an internal table structure that MySOL uses to provide access to the rows of a table, based on the value of one or more columns. D
A. View B. Table C. Stored procedure D. Index
10、Which one of the following options stores metadata about a database? C
A. Log file. B. Data file C. Data dictionary D. Indices
11、Which one of the following options deals with the allocation of memory resources to the data? B
A. Transaction Manager B. Buffer Manager C. Storage Manager D. Resource Manager
12、Which one of the following components is used for planning the execution of a query to speed up the processing? C
A. Query parser
B. Query Preprocesso
C. Query Optimizer
D. Execution Engine
13、Which one of the following subsystems deals with the logging of every command in log file? C
A. Query Processor B. Transaction Management
C. Recovery Management D. Storage Management
14、Which one of the following categories of SQL statements can be used to assign and deny access permission on the database object? B
A. DDL B. DCL C. DML D. DTL
15、Which one of the following is the default execution engine C
A. MYISAM B. InnoDB C. Memory D. None
16、Which one of the following commands is used to connect with the sakila database?D
A. Mysal -h localhost -u root-p sakila.
B. Mysql -u root -p
C. Mysql-u root -p sakila
D. Any one of the above
17、Which one of the following is the DCL command? C
A. GRANT, SAVEPOINT
B. REVOKE, ROLLBACK
C. GRANT REVOKE
D. GRANT,COMMIT
18、1. Which one of the following is the DML command? B
A, INSERT, UPDATE, DELETE, CREATE
B. INSERT, UPDATE, DELETE, SELECT
C. UPDATE, INSERT, RENAME, ALTER
D. INSERT, UPDATE, ALTER, DROP
19Which one of the following is the DDL Commands? D
A. CREATE, ALTER,GRANT, REMANE,DROP
B. CREATE, INSERT, UPDATEALTER,DELETE
C. TRUNCATE, UPDATE, DELETE,SELECT,DROP
D. CREATE, ALTER RENAME, DROP, RUNCATE
20、Which one of the following the TCL commands? A
A. COMIT, ROLLBACK. SAVEPOINT
B. B. COMMIT, GRANT, SAVEPOINT
C. C. ROLLBACK, COMMIT, REVOKE
D. D. ROLLBACK, GRANT, REVOKE