1. Single choice (18 questions in total)
1. (Single choice)SQL statements are divided into DDL, DML, DQL, TCL, and DCL. The following DML statements are ____, whether ____ the transaction.
A. d. rop is not supported
B.i nsert support
C.a. lter support
D.s well does not support
B
【答案 】
DDL Data definition language: CREATE DROP ALTER TRUNCATE Does not support transaction DML data manipulation language: INSERT UPDATE DELETE Supports transaction DQL data query language: Select does not support transactions TCL transaction control languages such as COMMIT ROLLBACK DCL data control languages: assign user rights
B.
2. (Single choice) Transactions are the basic unit of work for database execution logic. If one transaction executes successfully, all updates are committed; If a transaction fails, the updates made are restored as if the entire transaction never had those updates, leaving the database in _____ state. A. integrity b. persistence c. consistency d. security 【正确答案】C 【答案 】
This topic examines four characteristics of transactions:
1.atomicA transaction is a unit of work in which the database executes logic. All operations involved in a transaction are either done or not done.
2,consistencyThe result of the transaction execution is to change the database from one consistent state to another. Consistency is closely related to atomicity.
3,Isolation,The execution of a transaction cannot be interfered with by other transactions.
4,persistenceOnce a transaction is committed, its changes to the data in the database should be permanent.
3, the conditions in the select statement (single choice), age between 22 and 26, said the age between 22 to 26, and () A. B. Ages 22 and 26 b. ages 22 and 26 C. ages 22 but not 26 D. 26 years old is included but not 22 years old.
Between… and… Between two values, including and, choice A is correct.
4, (single choice) For paging query, the following statement is incorrect. It can reduce the pressure on the server. B. It has great overhead for the program. C. It improves the user experience. There are different SQL instructions for different databases
Select * from person having age=(25,28,30,22) select * from person having age=(25,28,30,22); B.elect * from person where age=(25,28,30,22); C.elect * from person where age in(25,28,30,22); D.elect * from person haveing age in(25,28,30,22); 【答案 】C
Select * from where (in) where (in); select * from where (in);
6. (Single choice) Database DB, database system DBS, () A database management system (DBMS) B database management system (DBMS) C database management system (DBMS) D database management system (DBMS)
DBS includes both DB and DBMS
- DBS(DataBase System) : DataBase System, is the use of DataBase technology of the computer System, is a practical operation, according to the DataBase method storage, maintenance and provide data support to the application System of the System, it is a DataBase, hardware and software, as well as the DataBase administrator of the collection.
- DBDataBase: A DataBase is actually a collection of files, the essence is a file system, data in accordance with a specific format stored in the file, using SQL language to add, delete, change and check the data operations.
- DBMS(DataBase Management System) : DataBase Management System, refers to the data Management software System in the DataBase System, used to establish, use and maintain the DataBase, the unified Management and control of data, users access the data in the DataBase through DBMS.
Choice A is correct.
A. table tableName remove columnName; a. table tableName remove columnName; B.alter table tableName drop columnName; C.alter table tableName from columnName; D.alter table tableName delete columnName; 【答案 】B
Alter table alter table alter table alter table alter table alter table alter table alter table B.
8. (single choice) If the ‘name’ column in A table is defined as name varchar(10), the following statement is correct. Char (10); char(10); char(10); char(10) D. If the input character length is less than 10 bytes, fill it with blank characters.
Varchar (m): vARCHar (m): vARCHar (m): varchar(m): vARCHar (m): vARCHar (m): vARCHar (m): vARCHar (m): vARCHar (m): vARCHar (m): vARCHar (m): vARCHar (m): vARCHar (m
B.
9. (Single choice) Know the table
student(
sname varchar(20), --' Student name '
cname varchar(20), --' Course name '
grade int - 'results'
)
Copy the codeThe following () is true. A. values into student values(‘ 张三 ‘,98, ‘张三’); B. nsert into student values(‘ 张三 ‘, ‘张三’,98); C.insert into student values(‘ 张三 ‘,98, mathematics); D. set into student values(‘ 张三 ‘, math,98); 【答案 】B
Insert into table name values(1, 2, 3…) insert into table name values(1, 2, 3…) insert into table name values(1, 2, 3…) ; B.
10. (Single choice) It is known that A company can employ more than one employee to work for it, and each employee can only enter into A labor relationship with one company. From the perspective of the company, which of the following is the relationship between the company and its employees? A. one to many b. many to many c. one to one d. many to one 【答案 】A
There are two tables A and B, and one of the data in table A corresponds to one of the data in table B. In both tables AB, one data in table A corresponds to multiple data in table B, and one data in table B corresponds to one data in table A. The relationship between two tables is called one-to-many or many-to-one many-to-many: There are two tables A and B. One data in table A corresponds to multiple data in table B, and one data in table B corresponds to multiple data in table A. This relationship is called many-to-many.
Select * from emp where sal(select * from emP where sal(select * from emP where sal(select * from EMP where sal(select * from EMP)); B.elect name,concat(sal) from emp; Select name,sal from emp; D.select name,concat(‘ sal ‘, ‘yuan’) from emp; 【答案 】B
This topic examines string concatenation functionsconcat(a,b) Concatenates a and B. If A is a field, the value of A is concatenated with b; if a is a constant value, the constant value is directly concatenated with B. B.
12. (Single choice) List of known commodities
t_item(
title varchar(20), --' Trade Name '
price double - 'price'
)
Copy the codeSelect () a.title,price from t_item where title like ‘_ %’; select () a.title,price from t_item where title like ‘_ %’; B.elect title,price from t_item where title like ‘_ give %’; C. elect title,price from t_item where title like ‘give _%’; D.select title,price from t_item where title like ‘% give _’; 【答案 】A
Example: start with a a a% end with a%a The second character is a _A % contains a% A % the third character from the bottom is a% a__ The second and last characters are a _A %a
Id: student_name Course: ID: course_name Score :student_id: course_id: Score Select * from student id, student name, student average score (60), student average score (60), student average score (60) A.select student_id,student_name,avg(score) av from student s join score sc on s.id=sc.student_id; B.select student_id,student_name,avg(score) av from student s join score sc on s.id=sc.student_id where av>60; C.select student_id,student_name,avg(score) av from student s join score sc on s.id=sc.student_id where av>60 group by student_id; D.select student_id,student_name,avg(score) av from student s join score sc on s.id=sc.student_id group by student_id having av>60; D.
Subject: The average score of each student should be calculated first, that is, the average score of each student should be counted according to the group of students. The SQL statement is select student_id,student_name,avg(score) av from student s join score sc on S.id =sc.student_id group by Student_id; Then select the average score of each student and select those with more than 60 points. Then add having at the end and select D
Price (float), category (varchar); Now query the average price of each category, category name, the following statement is correct? A.select avg(price),type from book group by type B.select count(price),type from book group by price C.select Avg (price),type from book group by price d.elect count(price),type from book group by type 【正确答案】A 【答案解析】
The function to calculate the average value is AVg (), and the average price is avg(price). By analyzing the topic, we know that classification needs to be based on category, so group by type is used
15, 【 single choice 】 analyze the following SQL statement, which part caused the error? Select empno,sum(sal) ‘salary’ from emp where sal>1500 group by empno by 2; A.order by 2 b. ROM emp C. Here sal>1500 D. Group by empno
The ==order by clause should be followed by the column name, sorted by the specified column
Select ENAME FROM EMP WHERE MGR=NULL; select ENAME FROM EMP WHERE MGR=NULL; B.SELECT ENAME FROM EMP WHERE MGR IS NULL; C.SELECT ENAME FROM EMP WHERE MGR=0; D.SELECT ENAME FROM EMP WHERE MGR=”; 【答案 】B
If the value is null, is not null is used to check whether the value is null
SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL
The Emp table | emp_id | name | gender | |1| zhang li female | | |2| wang ke male | | |3| Zhao Qi | the | | emp_dept table emp_id | name | dept_name | |1| zhang li | department | |3| | Zhao Qi | r&dCopy the codeselect e.*,ed.dept_name from emp e left join emp_dept ed on e.emp_id=ed.emp_id and e.name=ed.name;
A.
| emp_id | name | gender | dept_name
| 1| zhang li female | | department | |2| wang ke | |null |
| 3| | Zhao Qi female | | r&dCopy the codeB.
| emp_id | name | gender | dept_name |
| 1| zhang li female | | department | |2| wang ke | | | technology departmentCopy the codeC.
| emp_id | name | gender |
| 1| zhang li female | | |2| wang ke male | | |3| Zhao Qi | |Copy the codeD.
| emp_id | name | gender | dept_name |
| 1| zhang li female | | department | |3| | Zhao Qi female | | r&dCopy the code【答案 】A
In the left join query, all data in the left table is displayed. If no data in the right table is matched, null is used for matching
18, 【 single choice 】 analyze the following SQL statement, under what circumstances causes this statement to fail?
SELECT * FROM student WHERE id=
(SELECT id FROM student WHERE first_name='kate' AND last_name='henry');
Copy the codeA. There is no student whose name is Kate Henry in the data table. B. There is more than one student whose name is Kate in the data table. Mysql > select * from student whose name is Kate Henry; select * from student whose name is Kate Henry;
Select * from subquery where id=(subquery) where id=(subquery) where id=(subquery) where ID =(subquery
19, (multiple choice) a. constraint B. constraint C. constraint D. constraint SQL > alter table create table create table create table create table create table create table create table create table create table create table create table create table create table create table create table create table create table create table create table create table create table D. set the default value for the field. When the field is not assigned, the default value takes effect.
The constraint is not null
- Add a field that is not null constraint. The value cannot be NULL unique
- Add a unique constraint on the primary key. The primary key can be null
- Fields with primary key constraints added cannot be null and cannot duplicate the default constraint default
- Set default values for fields. When the field is not assigned a value, the default values take effect
Choice ACD is correct.
20. (Multiple choice) There are two tables in the database, Select * from emp(id int,ename varchar(10),sal int, depTID int) and dept(id int,dname varchar(10)) () a.elect e.name, d.name from emp e left join dept d on e.deptid=d.id; B.select e.ename,d.dname from emp e right join dept d on e.deptid=d.id; C.select ename,dname from emp e right join dept d on e.deptid=d.id; D.select ename,dname from emp e left join dept d on e.deptid = d.id; A, b, C,D
This topic examines the basic syntax of outer concatenation:
Left /right join Right table on left table. Field = right table. Field;
There are two types of left outer connection and right outer connection:
Left Join —- Join the left table to query all data in the primary table, and query only related data in the right table
—- Join the right table to query all data in the primary table, and query only related data in the left table.
Emp (id int,ename varchar(10),sal int,deptid int) dept(id int,dname varchar(10)) emP (id int,ename varchar(10),sal int,deptid int) Select dept. ename,dept.dname from emp,dept where dept. id= deptid; select dept. ename,dept.dname from emp where dept. id= deptid; B.select e.ename,d.dname from emp e join dept d on e.id=d.deptid; C.select emp.ename,dept.dname from emp,dept where emp.deptid=dept.id; D.select e.ename,d.dname from emp e join dept d on e.deptid=d.id; C) It is necessary to have a good conversation. D) It is necessary to have a good conversation.
This question examines the equivalent join format:
sql select * from A,B where A.x=B.x and A.y=abcInternal connection format:
sql select * from A join B on A.x=B.x where A.y=abcFor example, query the name of the staff in the teaching and Research Department
Select dept. ename,dept.dname from emp where deptid=dept.id and dept.dname=’ dname ‘; Select * from emp e join dept d on e.deptid=d.id where d.name =’ d ‘;
Choice CD is correct.
22. (MULTIPLE choice) The following statements about views are true () A. Tables and views are objects B in A database. C. View is A virtual table. D. Data in the view changes when the original table changes.
This question examines the understanding of views
The name of a view cannot be the same as the name of a table. A view is actually a mapping of an SQL query statement. It can be interpreted as a virtual table
Choice ACD is correct.
Select deptno where sal > 2000 from deptno where sal > 2000 from DEPtno where SAL > 2000 from deptno where SAL > 2000 () a.select deptno,avg(sal) a from emp group by deptno having a>2000; B.select deptno,avg(sal) a from emp having a>2000 group by deptno; C.select deptno,avg(sal) a from emp group by deptno where a>2000; D.select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; A, b, C,D
Group by group by group by group by group by group by group by
Having after group by is usually used in combination with group by to filter grouped query results. General field conditions are written after WHERE, aggregate function conditions are written after HAVING, and having is written after group by.
Select * from student whose name is the highest in math. () a.name from student where math is (select Max (math) from student); B.select name from student where math (select max(math) from student); C.select name from student where math=(select max(math) from student); D.select name from student where math in (select max(math) from student); C) it is a good idea to have a child. D) It is a good idea to have a child
Select Max (math) from student; select Max (math) from student; Select name from student where math=99; select name from student where math=99; Select name from student where math in(99);
Choice CD is correct.
25. (Multiple choice) List of known commodities
t_item(
title varchar(20), --' Trade Name '
price double - 'price'
)
Copy the code() a.title,price from t_item order by price desc limit 3,6; () a.title,price from t_item order by price desc limit 3,6; Select title,price from t_item order by price limit 3,6; C. elect title,price from t_item order by price limit 6,3; D.select title,price from t_item order by price asc limit 6,3; C) It is necessary to have a good conversation. D) It is necessary to have a good conversation.
This topic examines a – limit paging query
Limit Skips the number of entries to query the number of entries
Order by column name default ascending order,
The ascending order is asC descending order is DESC
Choice CD is correct.
Emp set Bonus =ifnull(bonus,0) A.u date emp set Bonus =ifnull(bonus,0) B.update emp set bonus=ifnull(null,bonus); C.update emp set bonus=0 where bonus is null; D.update emp set bonus=0 where bonus=null; 【答案 】 C
Bonus =ifnull(x,y) bonus=ifnull(x,y)
Check whether x is null if bonus=y, if not bonus=x;
Choice AC is correct.
27. (MULTIPLE choice) Which of the following statements about indexes is true () a. The more indexes are created, the better B. C. An index is a technique used to speed up queries, similar to a directory D. B, create index no matter how much data there is in the table.
What is an index
- An index is a technique used to speed up queries, similar to a directory
- Indexes take up disk space, so be careful when creating them. Determine which indexes to create based on query requirements and table structure
- Indexes need to be created in tables with a large amount of data. If the data amount is not large enough, the query efficiency may be reduced
Option BC is correct.
To be continued >>>