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
- 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 - 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 < “
B. mysql –h localhost –u root –p student_details
C. mysql –h localhost –u root –p < “
D. mysql –h –u root –p student_details < “
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