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<